none
OpenXML file validates but Excel (2010) reports an error and won't open it. RRS feed

  • 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="utf-8" standalone="yes"?>
    <x:worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/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="utf-8" 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

    Thursday, August 23, 2012 8:14 PM

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

    Friday, August 24, 2012 7:11 AM
    Moderator
  • Here is the link to the file:

    http://sdrv.ms/Qzfp1O

    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/en-us/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!

    Rick Spiewak

    Friday, August 24, 2012 5:13 PM
  • 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.

    http://sdrv.ms/P9gy5y

    Maybe this will be easier to explain...

    Rick

    Friday, August 24, 2012 7:54 PM
  • 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

    Monday, August 27, 2012 2:58 AM
    Moderator
  • 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

    Monday, August 27, 2012 2:24 PM
  • Hi Rick,

    Please pay attentantion to the difference of the sheet1.xml:

    <?xml version="1.0" encoding="utf-8" standalone="yes"?>
    <x:worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/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


    Tuesday, August 28, 2012 7:23 AM
    Moderator
  • 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.

    Tuesday, August 28, 2012 1:12 PM
  • 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

    Wednesday, August 29, 2012 1:50 AM
    Moderator
  • 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

    Thursday, August 30, 2012 8:51 PM
  • 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

    Friday, August 31, 2012 6:43 AM
    Moderator
  • 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

    Friday, August 31, 2012 3:07 PM
  • 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

    Monday, September 3, 2012 6:42 AM
    Moderator
  • 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.

    Monday, September 3, 2012 1:03 PM
  • 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

    Tuesday, September 4, 2012 3:02 AM
    Moderator
  • Here is the latest file:

    http://sdrv.ms/OWInuK

    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!

    Friday, September 7, 2012 8:18 PM