Dates not displayed correctly in newer Excel versions

[tfunderburg]

This may also apply to other data types. This is not a bug with excel-writer-xlsx, but the documentation needs updated. I have a date field as my X axis. It was originally set like this:

      $chart->set_x_axis( num_font => { name => 'Arial', size => 20, 
                                        rotation => -45 } );

When switching from Office 2011 (Mac) to Excel for Mac 15.30, the dates went from displaying correctly to just being the numbers 1..n. I thought this might fix it:

      $chart->set_x_axis( num_font => { name => 'Arial', size => 20, 
                                        rotation => -45, date_axis => 'True' } );

But no luck. In fact, that turns out to make no difference in the solution I found. After much guessing and close inspection, I've discovered that the new version requires that ticks (') are needed around the name of the sheet when referencing the category data (and perhaps other places). Original code:

      ...
      $chart->add_series(
         name       => $series_name,
         categories => "=$sheet_name!\$A\$2:\$A\$" . scalar($days + 1),
         values     => "=$sheet_name!\$C\$2:\$C\$" . scalar($days + 1),
         line       => { color => 'blue', width => 5 },
         marker     => { type => 'circle', size => 7 },
      );
     ...

Working code:

      ...
      $chart->add_series(
         name       => $series_name,
         categories => "='$sheet_name'!\$A\$2:\$A\$" . scalar($days + 1),
         values     => "='$sheet_name'!\$C\$2:\$C\$" . scalar($days + 1),
         line       => { color => 'blue', width => 5 },
         marker     => { type => 'circle', size => 7 },
      );
      ...

Note the added single quotes in the lines with categories and values. It's really the categories line that fixed my problem, but I was being thorough. I can't find this need discussed anyplace, and none of the fantastic examples provided show this form. I won't guarantee that this fix is backward compatible with older version of excel because I don't have one handy. If it is, updating the examples would be great. If not, maybe just a note that the format may need to be different for newer/older versions of excel.

-Todd

[jmcnamara]

Hi Todd,

Apologies, I'm only getting to this now.

Excel requires that sheet names with spaces (and a few special characters) are single quoted.

This should be:

1. Documented.
2. Have a check and warning in the code.

The Python version has both of these but the Perl version is missing them. I'll fix that.

Thanks,

John