Problem

An ExcelWriter-generated file with charts displays correctly when opened in Excel.  When the same file is opened in an application that does not have the full Excel functionality, such as Outlook preview or in a mobile application, the charts are missing data or have the wrong data.

When a workbook is opened in Excel, all of the cell values are updated based on the current state of the file. Excel caches these most recent cell values and writes them out to the file. Many mobile apps or applications with a “preview” functionality where the file is locked from being updated, do not have the ability to recalculate the cell values and rely on those cached values to render the charts. When the cached values are not available, the charts may be rendered incorrectly so they may appear to have no data, wrong data, or may not appear in the file at all.

Below are common scenarios where the workbook cannot be fully updated:

• Mobile applications
• Applications with “preview,” such as Outlook’s attachment preview
• Excel’s protected mode, which is activated when files are downloaded from the internet and opened in Excel before saving to disk

Starting in v8.3, ExcelWriter writes out the cached cell values to integrate better with applications that don’t have the ability to update the workbook. However, there are still some limitations that prevent charts from being rendered correctly. This article describes the limitations and expected behavior of rendering charts in files that were generated in ExcelWriter v8.3 or later. 

If you are experiencing issues

Jump to:

• iOS Applications (charts are missing or data appears to be incorrect)
• Charts that use data that contains formulas (data is incorrect)
• Charts that use data with number formats applied (data is incorrect)
• PivotCharts (data is missing)

Details

iOS Applications

Charts are missing – Certain charts are not supported by iOS, regardless of whether they are in files generated by Excel, ExcelWriter, or some other application. An example of this is the bubble chart. These charts may not be rendered at all, depending on the application. More information is available in this article: Charts in ExcelWriter files are missing when viewed on iOS devices.

Charts are visible, but incorrect – If the chart is visible, but the data appears to be incorrect, then the chart may have data that contains formulas, formatted numbers, or it may be a PivotChart (see below for more info).

Chart data contains formulas

As of v9.0, ExcelWriter has the ability to calculate formulas with the Workbook.CalculateFormulas method. However, for the initial release, only a limited number of formulas are supported.

If ExcelWriter is unable to evaluate formulas (i.e. the formulas you are using are not yet supported or you are using a version prior to v9.0), or Workbook.CalculateFormulas is not called, then the most up-to-date values will not be available. Instead, if there is a formula in the cell, we will write a “0” to the cached chart field. Despite not being an accurate value, having the cached value reduces the risk of the chart not being rendered or the the application from crashing due to the missing data.

Chart data contains values with number formats applied

ExcelWriter does not have the ability to render numbers based on number formats (e.g. percentage). In the case that a number has a number format applied, the underlying value will be used for the cached field. For example, 6.5% has actual numerical value 0.065, which is the value that ExcelWriter will write to the cached field. This also applies to dates, which are stored as serial numbers in the file and then Excel uses the number format to render the formatted date. For more information about dates in Excel, please see this article: http://www.cpearson.com/excel/datetime.htm.

PivotCharts

PivotCharts have a different structure than regular charts and depend on the entire PivotCache being refreshed to be updated.  Updating the PivotCache is an operation that must be performed in Excel or an application that has the ability to do so. ExcelWriter does not have the ability to refresh the PivotCache and update the PivotCharts. The file will need to be opened in Excel (not in protected mode) in order to update the PivotCache.

Follow

Related posts: