In /includes/exporters/webform_exporter_excel_xlsx.inc:

      elseif (is_numeric($value) && $value[0] !== '+') {
        $output .= '<c r="' . $cell_position . '"><v>';
        $output .= $value;
        $output .= '</v></c>';
      }

is assuming that all numeric values should be exported as numbers to Excel. This is a problem because for big numbers Excel rounds it to the nearest value it can store, causing data loss. Also, values that contain leading zeros cannot be stored, even if the webform field is configured as a textfield.

CommentFileSizeAuthor
#2 2860487-2.patch803 bytesmercepedraza
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

mercepedraza created an issue. See original summary.

mercepedraza’s picture

Assigned: mercepedraza » Unassigned
Status: Active » Needs review
FileSize
803 bytes
mercepedraza’s picture

Title: Allways export to excel numeric values as text » Always export to excel numeric values as text
joelpittet’s picture

Status: Needs review » Reviewed & tested by the community

That seems reasonable

Liam Morland’s picture

I am concerned that this will break workflows. What happens if someone relies on opening a spreadsheet and doing math on the values?

Pere Orga’s picture

I just checked on MS Excel 2013 and patch in #2 does not break doing math on these cells.

Also, the description is a bit misleading, patch in #2 is changing the cell style from "Number" to "General", not "Text" (as seen in Excel -note, I have Excel in Spanish so that was my translation of the style names, names in English may be a bit different). FWIW, I just checked Libreoffice and still considers/displays these cells to be numbers, but I guess that's OK because LibreOffice, unlike Excel, does not try to convert the value of these cells automatically.

I don't know much about OOXML, but this LGTM.

Liam Morland’s picture

Title: Always export to excel numeric values as text » Set numeric values as "general" in Excel export
Status: Reviewed & tested by the community » Fixed

Status: Fixed » Closed (fixed)

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

StijnStroobants’s picture

One of our clients want to export certain webform-data as number-format.
Isn't is better to set this option as an option for the export?

Liam Morland’s picture

I understand from #6 that they still work as numbers for doing math. If it doesn't do what you client needs, that could be a feature request. If you want to do that, please make a child ticket.