CODEWITHSUNDEEP
c#excelopenxml-sdk
Syst3m_NZ
Syst3m_NZ

Reputation: 56

OpenXML - Rows getting written out in the wrong order resulting in lost data

I am using openxml-sdk to generate an excel file and upon opening of the file it tells me that the file is corrupt and offers to repair it. The repair does the following:

Removed Records: Cell information from /xl/worksheets/sheet.xml part
Repaired Records: Cell information from /xl/worksheets/sheet.xml part

This repair results in the loss of some data, I have looked into the XML and discovered that the data seems to be removed because the row has been written out of order when a merged cell is encountered. 

<x:sheetData>
    <x:row r="8">
         ...
    </x:row>
    <x:row r="16">
         ...
    </x:row>
    <x:row r="9">
         ...
    </x:row>
    <x:row r="10">
         ...
    </x:row>
    ...
</x:sheetData>
<x:mergeCells>
    ...
<x:mergeCell ref="C8:C16" />
    ...
</x:mergeCells>

So as the above shows, there is a merge of C8:C16, wherever a merge like this happens it seems to write the row for the first and last then continue will all in the middle. However, when opening this excel doesn't seem to be able to cope and removes all out of order rows (9-15).

I have confirmed this by manually rearranging the rows in the XML then rezipping. It will then display all the data.

I am guessing this happens because I create row 16 earlier then row 9 so that I can do the merge. Is there a way to reorder the rows or get excel to handle this without having to create all the rows in advance?

Upvotes: 2

Views: 1047

Answers (1)

Eric White
Eric White

Reputation: 1891

As far as Excel is concerned - there is nothing that you can do to make Excel accept rows that are written out in the wrong order. You must write your code in some fashion so that the rows are written out in the right order.

-Eric

Upvotes: 2

Related Questions