Asked by:
OpenXML file validates but Excel (2010) reports an error and won't open it.
Question

The only files different from a version which opens are sheet1.xml and sharedStrings.xml.
This is sheet1.xml:
<?xml version="1.0" encoding="utf8" standalone="yes"?> <x:worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markupcompatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <x:dimension ref="B2:B33" /> <x:sheetViews> <x:sheetView tabSelected="1" workbookViewId="0"> <x:selection activeCell="C28" sqref="C28" /> </x:sheetView> </x:sheetViews> <x:sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25" /> <x:cols> <x:col min="2" max="2" width="10.5703125" bestFit="1" customWidth="1" /> <x:col min="3" max="3" width="18.5703125" customWidth="1" /> </x:cols> <x:sheetData> <x:row r="2" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B2" t="s"> <x:v>0</x:v> </x:c> </x:row> <x:row r="28" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B28" t="s"> <x:v>1</x:v> </x:c> <x:c r="C28" t="s"> <x:v>7</x:v> </x:c> </x:row> <x:row r="29" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B29" t="s"> <x:v>2</x:v> </x:c> <x:c r="C29" t="s"> <x:v>7</x:v> </x:c> </x:row> <x:row r="30" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B30" t="s"> <x:v>3</x:v> </x:c> <x:c r="C30" t="s"> <x:v>7</x:v> </x:c> </x:row> <x:row r="31" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B31" t="s"> <x:v>4</x:v> </x:c> <x:c r="C31" t="s"> <x:v>7</x:v> </x:c> </x:row> <x:row r="32" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B32" t="s"> <x:v>5</x:v> </x:c> <x:c r="C32" t="s"> <x:v>7</x:v> </x:c> </x:row> <x:row r="33" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B33" t="s"> <x:v>6</x:v> </x:c> <x:c r="C33" t="s"> <x:v>7</x:v> </x:c> </x:row> <x:row r="1"> <x:c r="A1" t="s"> <x:v>10</x:v> </x:c> <x:c r="B1" t="s"> <x:v>11</x:v> </x:c> <x:c r="C1" t="s"> <x:v>12</x:v> </x:c> <x:c r="D1" t="s"> <x:v>13</x:v> </x:c> <x:c r="E1" t="s"> <x:v>14</x:v> </x:c> <x:c r="F1" t="s"> <x:v>15</x:v> </x:c> <x:c r="G1" t="s"> <x:v>16</x:v> </x:c> <x:c r="H1" t="s"> <x:v>17</x:v> </x:c> <x:c r="I1" t="s"> <x:v>18</x:v> </x:c> <x:c r="J1" t="s"> <x:v>9</x:v> </x:c> </x:row> </x:sheetData> <x:pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" /> </x:worksheet>
And this is sharedStrings.xml:
<?xml version="1.0" encoding="utf8" standalone="yes"?> <x:sst count="18" uniqueCount="18" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <x:si> <x:t>Order for:</x:t> </x:si> <x:si> <x:t>First Name</x:t> </x:si> <x:si> <x:t>Last Name</x:t> </x:si> <x:si> <x:t>Street</x:t> </x:si> <x:si> <x:t>City</x:t> </x:si> <x:si> <x:t>State</x:t> </x:si> <x:si> <x:t>Zip</x:t> </x:si> <x:si> <x:t>FileVersion</x:t> </x:si> <x:si> <x:t>Acronyms_Id</x:t> </x:si> <x:si> <x:t>Term</x:t> </x:si> <x:si> <x:t>Definition</x:t> </x:si> <x:si> <x:t>Key</x:t> </x:si> <x:si> <x:t>Description</x:t> </x:si> <x:si> <x:t>Link</x:t> </x:si> <x:si> <x:t>AddedBy</x:t> </x:si> <x:si> <x:t>AddedDate</x:t> </x:si> <x:si> <x:t>ModifiedBy</x:t> </x:si> <x:si> <x:t>ModifiedDate</x:t> </x:si> </x:sst>
Any ideas why this fails?
TIA
All replies

Hi Rick,
Thanks for posting in the MSDN Forum.
Would you please share your problem spreadsheetdocument on Skydrive? It's have to trouble shooting if we only get some parts of the document.
Have a good day,
Tom
Tom Xu [MSFT]
MSDN Community Support  Feedback to us

Here is the link to the file:
I'm trying to replicate some of what Excel does in importing XML. I'm still successful in filling in named ranges, using basically the same code as in my article here: http://msdn.microsoft.com/enus/magazine/hh882449.aspx
Apparently something is going wrong when I try to just insert new data into cells.
Here is the code snippet, using the same pieces of the OpenXML SDK:
For Each xmlColumn As DataColumn In xmlTable.Columns Dim xmlCell = InsertCellInWorksheet(currentColumn, startingRow, activeWorksheetPart) Dim index As Integer = InsertSharedStringItem(xmlColumn.ColumnName, SpreadSheetDocument) xmlCell.CellValue = New CellValue(index.ToString) xmlCell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString) currentColumn = GetNextColumn(currentColumn) Next xmlColumn
I've been staring at differences in the productivity tool without much success. It does look like the shared string counts aren't incrementing, but that also seems to be true in the the case that works (just filling in named ranges).
Since validation with the tool or APIs succeeds, I'm down to trial and error looking at differences to figure this out.
Thanks!

Well, I thought I knew what worked. Here's a simple example I produced to see if using named ranges for the top row worked. It doesn't! Now I'm really puzzled.
Maybe this will be easier to explain...
Rick

Hi Rick,
I think you must defined the row in order. In your document which you share I fond the first row you declared at the end of the worksheet.xml. It's based on my experience that will is incorrect. I tried modify the sheet1.xml and let the row in the correct order. Then compress the files and changed extend name as "*.xlsx". All works fine at the end. I hope this scenario will help you.
Have a good day,
Tom
Tom Xu [MSFT]
MSDN Community Support  Feedback to us

I modified my code (which I copied originally from the OpenXML SDK. BTW, I can't seem to find this source code any more! Is is available?) to ensure that rows are inserted in order. I still can't open the result. I've posted two files  one is done by hand, the other by code with the change to ensure the correct order.
Here are the links:
http://sdrv.ms/PhpsIG Output by hand With Named Column Cells.xlsx
http://sdrv.ms/P9gy5y Output With Named Column Cells.xlsx
When I compare these with the Open XML SDK 2.0 Productivity Tool, I don't see any difference which seems to indicate why the "Output by hand..." file opens but the "Output..." file does not.
Thank you for your help!
Rick

Hi Rick,
Please pay attentantion to the difference of the sheet1.xml:
<?xml version="1.0" encoding="utf8" standalone="yes"?> <x:worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markupcompatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <x:dimension ref="B2:B33" /> <x:sheetViews> <x:sheetView tabSelected="1" workbookViewId="0"> <x:selection activeCell="C28" sqref="C28" /> </x:sheetView> </x:sheetViews> <x:sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25" /> <x:cols> <x:col min="2" max="2" width="10.5703125" bestFit="1" customWidth="1" /> <x:col min="3" max="3" width="18.5703125" customWidth="1" /> </x:cols> <x:sheetData> <x:row r="1"> <x:c r="A1" t="s"> <x:v>10</x:v> </x:c> <x:c r="B1" t="s"> <x:v>11</x:v> </x:c> <x:c r="C1" t="s"> <x:v>12</x:v> </x:c> <x:c r="D1" t="s"> <x:v>13</x:v> </x:c> <x:c r="E1" t="s"> <x:v>14</x:v> </x:c> <x:c r="F1" t="s"> <x:v>15</x:v> </x:c> <x:c r="G1" t="s"> <x:v>16</x:v> </x:c> <x:c r="H1" t="s"> <x:v>17</x:v> </x:c> <x:c r="I1" t="s"> <x:v>18</x:v> </x:c> <x:c r="J1" t="s"> <x:v>9</x:v> </x:c> </x:row> <x:row r="2" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B2" t="s"> <x:v>0</x:v> </x:c> </x:row> <x:row r="28" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B28" t="s"> <x:v>1</x:v> </x:c> <x:c r="C28" t="s"> <x:v>7</x:v> </x:c></x:row> <x:row r="29" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B29" t="s"> <x:v>2</x:v></x:c> <x:c r="C29" t="s"> <x:v>7</x:v> </x:c> </x:row> <x:row r="30" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B30" t="s"> <x:v>3</x:v> </x:c> <x:c r="C30" t="s"> <x:v>7</x:v> </x:c> </x:row> <x:row r="31" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B31" t="s"> <x:v>4</x:v> </x:c> <x:c r="C31" t="s"> <x:v>7</x:v> </x:c></x:row> <x:row r="32" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B32" t="s"> <x:v>5</x:v> </x:c> <x:c r="C32" t="s"> <x:v>7</x:v> </x:c> </x:row> <x:row r="33" spans="2:2" x14ac:dyDescent="0.25"> <x:c r="B33" t="s"> <x:v>6</x:v> </x:c> <x:c r="C33" t="s"> <x:v>7</x:v> </x:c> </x:row> </x:sheetData> <x:pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" /> </x:worksheet>
You can visit https://skydrive.live.com/redir?resid=BFDF05E934413519!381 to get the correct Spreaddocument which I modified via manner.
Have a good day,
Tom
Tom Xu [MSFT]
MSDN Community Support  Feedback to us
 Edited by 许阳(无锡)Moderator Tuesday, August 28, 2012 7:39 AM

I can see this difference, which has to do with the order of rows as you mentioned.
I tried taking your advice as to the order of rows. Please see the next set of two files I uploaded, and see if you can tell what difference in them is causing Excel to reject the one but not the other.
Thank you.

Hi Rick,
I found that there missed SharedStringTablePart in your "Output With Named Column Cells" document. So it will not work.
Have a good day,
Tom
Tom Xu [MSFT]
MSDN Community Support  Feedback to us

Hi, Tom. It looks like the comparison function of the Open XML SDK 2.0 Productivity tool must be broken. It shows missing pieces as if they were there.
I can't seem to figure out how to get a new SharedStringTablePart to end up in the output.
Here's the code:
Private Function InsertSharedStringItem(ByVal text As String, ByVal spreadSheetDocument As SpreadsheetDocument) As Integer Dim sharedStringPart As SharedStringTablePart If (spreadSheetDocument.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).Count() > 0) Then sharedStringPart = spreadSheetDocument.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).First() Else sharedStringPart = spreadSheetDocument.WorkbookPart.AddNewPart(Of SharedStringTablePart)() End If ' If the part does not contain a SharedStringTable, create one. If (sharedStringPart.SharedStringTable Is Nothing) Then sharedStringPart.SharedStringTable = New SharedStringTable sharedStringPart.SharedStringTable.Save() spreadSheetDocument.WorkbookPart.Workbook.Save() End If . . .
Can you see what's wrong?
Thanks.
Rick

Hi Rick,
You seem didn't define any SharedStringItem in your snippet, however you use sharedstring index in your cellvalue. Would you double check it?
Have a good day,
Tom
Tom Xu [MSFT]
MSDN Community Support  Feedback to us

I didn't include the entire routine, which is adapted from the OpenXML SDK:
Private Function InsertSharedStringItem(ByVal text As String, ByVal spreadSheetDocument As SpreadsheetDocument) As Integer Dim sharedStringPart As SharedStringTablePart If (spreadSheetDocument.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).Count() > 0) Then sharedStringPart = spreadSheetDocument.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).First() Else sharedStringPart = spreadSheetDocument.WorkbookPart.AddNewPart(Of SharedStringTablePart)() End If ' If the part does not contain a SharedStringTable, create one. If (sharedStringPart.SharedStringTable Is Nothing) Then sharedStringPart.SharedStringTable = New SharedStringTable sharedStringPart.SharedStringTable.Save() spreadSheetDocument.WorkbookPart.Workbook.Save() End If Dim i As Integer = 0 ' Iterate through all the items in the SharedStringTable. If the text already exists, return its index. For Each item As SharedStringItem In sharedStringPart.SharedStringTable.Elements(Of SharedStringItem)() If (item.InnerText = text) Then Return i End If i = (i + 1) Next ' The text does not exist in the part. Create the SharedStringItem and return its index. sharedStringPart.SharedStringTable.AppendChild(New SharedStringItem(New DocumentFormat.OpenXml.Spreadsheet.Text(text))) sharedStringPart.SharedStringTable.Save() spreadSheetDocument.WorkbookPart.Workbook.Save() Return i End Function
The problem is that apparently if there is no existing SharedStringTable this does not create one in the output document.
Thank you.
Rick

Hi Rick,
The following screen shooting is "Output With Named Column Cells.xlsx" which I can see on my side via "Open Xml SDK 2.0 Productivity Tool for Microsoft Office". I think you might check whether this has some logic in your code which will omit SharedStringTablePart.
Tom Xu [MSFT]
MSDN Community Support  Feedback to us

I understand, this is the problem. As long as the SharedStringTablePart already exists, the code works. As this generally exists for the real cases I'm dealing with, I can work around this. If I simply ensure that there is at least one string contained in a cell when I start, there is no problem.
However, I would be grateful if you can show me how to properly create the SharedStringTablePart when it doesn't previously exist, and successfully save it into the document. I'm using code originally copied from the OpenXML SDK, and yet you have shown me that it needed to be modified to ensure that rows are created in the correct order. So it is clear that there are omissions in the original code, but I can't see a way to correct this one.
Thank you.

Hi Rick,
The snippet which your create shared string table part seems OK. I think it will make more sense if you can provide more integral code for further trouble shooting.
Have a good day,
Tom
Tom Xu [MSFT]
MSDN Community Support  Feedback to us

Here is the latest file:
It does seem to have a shared string table part.
This is the code which creates the shared string table:
Private Function InsertSharedStringItem(ByVal text As String, ByVal spreadSheetDocument As SpreadsheetDocument) As Integer Static sharedStringPart As SharedStringTablePart Static sharedStrings As New Dictionary(Of String, Integer) If sharedStringPart Is Nothing Then If (spreadSheetDocument.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).Count() > 0) Then sharedStringPart = spreadSheetDocument.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).First() Else sharedStringPart = spreadSheetDocument.WorkbookPart.AddNewPart(Of SharedStringTablePart)() End If End If ' If the part does not contain a SharedStringTable, create one. If (sharedStringPart.SharedStringTable Is Nothing) Then sharedStringPart.SharedStringTable = New SharedStringTable sharedStringPart.SharedStringTable.Save() End If ' Iterate through all the items in the SharedStringTable. If the text already exists, return its index. If sharedStrings.Count = 0 Then 'Initialize the dictionary Dim i As Integer = 0 For Each item As SharedStringItem In sharedStringPart.SharedStringTable.Elements(Of SharedStringItem)() sharedStrings.Add(item.InnerText, i) i += 1 Next End If If sharedStrings.ContainsKey(text) Then Return sharedStrings(text) ' The text does not exist in the part. Create the SharedStringItem and return its index. sharedStringPart.SharedStringTable.AppendChild(New SharedStringItem(New DocumentFormat.OpenXml.Spreadsheet.Text(text))) Dim newIndex As Integer = sharedStrings.Count sharedStrings.Add(text, newIndex) Return newIndex End Function
I've modified it for significantly increased performance over the original SDK snippet.
This is the code which saves it:
Dim sharedStringPart As SharedStringTablePart = SpreadSheetDocument.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).First() sharedStringPart.SharedStringTable.Save() activeWorksheetPart.Worksheet.Save() 'Update the contents
The output is still unreadable by Excel unless I start by putting an arbitrary string into an arbitrary cell in the input file.
This program always works by reading an input file into a memory stream, working with the memory stream, then saving the result to a new file.
I now make sure that rows are always defined in order. When I open this file in the utility, it validates. I compare it with a similar file (which started with a string in it), and I see no obvious difference.
Thanks for any help!