I have an application which prepares XML data for import into Excel, using Microsoft's schema for XML-based spreadsheet data:
Code:
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
Generally this works just fine. However, when cells contain rather large strings, Excel does not import the cell data correctly. When it is imported into a non-XML Excel workbook, the data is incorrect or truncated, as described below.
The problems occur when cell data exceeds a certain character count: one problem occurs at about 2000 characters, the next problem occurs at about 10,000.
First problem:
I routinely embed string data for a cell within CDATA tags
(
and
)
delimiters, and this normally works fine: the embedded strings appear in the XLS cells (and the CDATA delimiters are not shown as part of the actual data). However, when the length of the cell string reaches about 2000 characters, the XLS starts to include the
in the cell, as though its part of the literal data.
In my tests, a cell worked fine at length=1523 but failed at length=2194 (and all lengths larger than that), so the cutoff point is apparently somewhere between those lengths.
Second problem:
The largest cell in my XML file got truncated when displayed as XLS. Its length was 10,767 characters; the first few leading characters were truncated so that its length in the XLS file was 10,733. (In addition, the CDATA was left on, though only the ending delimiter remained -- since the leading characters were stripped off.)
I am using Excel 2002 SP3. I've searched extensively for similar problem reports, but haven't seen any.
Problem importing XML data to Excel
