I'm finding that when I export Zip codes with a initial zero, that the zero is removed by excel when I load in the XLS file.

Is there a way around this issue?

Would be brilliant to have a real XLS file output from VDE, but until then, is there anything I can do?

Some sort of field-preprocess hack to make excel think it's a text field?...

CommentFileSizeAuthor
#9 views_data_export-2133935-process-msoffice.patch913 bytessteven jones
#4 leading_zeros_and-2133935-4.patch577 byteserlendoos

Comments

robearls’s picture

robearls commented

I have worked out I need to add style='mso-number-format:"\@"' to the TD

i.e.

<td style='mso-number-format:"\@"'>01234</td>

But I still don't know where to put this..

TBarina’s picture

TBarina commented

I'm facing the same issue. Anyone can help?

Many thanks

Würden’s picture

Würden commented

I know this is not a solution, but you can work around the problem by rewriting the result of the field in the view and add some sort of character such as "." or " " at the end of the field output.

erlendoos’s picture

erlendoos commented
StatusFileSize
new leading_zeros_and-2133935-4.patch577 bytes

Apply this patch and you can create a new hook (in a seperate custom module):

function HOOK_views_data_export_xls_cells_alter(&$cells, $view) {
  // alter cell content here.

  if (isset($cells['field_phonenumber'])) {
    $value = $cells['field_phonenumber'];
  
    $cells['field_phonenumber'] = array(
      'data' => $value,
      'style' => 'mso-number-format:"\@"',
    );
  }
}
erlendoos’s picture

erlendoos commented
Status: Active » Needs review
plopesc’s picture

plopesc
he/him
Primary language Spanish
commented
Status: Needs review » Reviewed & tested by the community

Nice patch! Works for me :)

steven jones’s picture

steven jones commented
Issue tags: +ComputerMinds
steven jones’s picture

steven jones commented
Status: Reviewed & tested by the community » Needs work

We should just split out the final rendering of the cells into a theme process function, and then other modules/themes could just preprocess the data as they liked.

In fact, looking at the code, we should everything except the the first two lines of template_preprocess_views_data_export_msoffice_body into a template_process_views_data_export_msoffice_body

steven jones’s picture

steven jones commented
Status: Needs work » Needs review
StatusFileSize
new views_data_export-2133935-process-msoffice.patch913 bytes

Something like this.

  • Steven Jones committed f7c8124 on 7.x-3.x 
    Issue #2133935 by Steven Jones, erlendoos: Leading Zeros and exporting "...
steven jones’s picture

steven jones commented
Status: Needs review » Fixed

Thanks everyone, committed the patch from #9.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

Amit Dwivedi’s picture

Amit Dwivedi commented

Hi @Steven Jones,

Can you please suggest me how to achieve it in Drupal 8 ?

I am facing a similar issue with Excel while exporting data in CSV file. (https://www.drupal.org/project/views_data_export/issues/2933032)

Thanks.

tjtj’s picture

tjtj commented

I have this same problem in Drupal 8 and 9. It needs a fix in the module.