Open XML SDK: get "Unreadable content" error when trying to populate more than 25 columns

Question

I have created a spreadsheet using Open XML SDK in C#, and successfully populated two worksheets.

When trying to populate a third, I get an "Unreadable content" error when opening the completed document, and it appears to occur when I attempt to populate more than 25 cells in a row on the third.

I'm using the same code fragment as has worked successfully elsewhere in the document:

string[] headers2 = {
    "Reference", "Raised", "Priority", "Affected", "Linked incidents",
    "Points", "SLA", "Stopped", "Target" };
// remaining headers are month/years created on the fly
string[] headerCells = {
    "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M",
    "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", 
    "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH" };
...
// headers
// once we've finished the presets, the month/year pairs take over
cellNo = (uint)0;
foreach (string h in headers2)
{
    cell = InsertCellInWorksheet(headerCells[cellNo++], 2, worksheetPart);
    cell.CellValue = new CellValue(h);
    cell.DataType = new EnumValue<CellValues>(CellValues.String);
}

string[] monthYears = 
    GetData_month_years(currentReportingPeriod - 1);
for (int j = 0; j < monthYears.Count(); j++)
{
    cell = InsertCellInWorksheet(headerCells[cellNo++], 2, worksheetPart);
    cell.CellValue = new CellValue(monthYears[j].ToString());
    cell.DataType = new EnumValue<CellValues>(CellValues.String);
}

The only cells populated are A and AA through AH.

Am I right in thinking I'm hitting some sort of limit, and if so, what's the way to reset it?

I've seen several posts about the unreadable content error, and I've looked through the documentation, but I've so far been unable to find anything that applies.

Help would be appreciated!

Thanks

Caesura

Answer 1

Please check out the "InsertCellInWorksheet(...)" method. If you use this construction inside -

...

row.InsertBefore(newCell, refCell);

...

it won't work correctly if you want to fill in "A - Z" AND "AA - ..." columns, even if you want to fill in only two columns (for example), - "Z" and "AA". So, try to use this method instead:

...
row.Append(newCell);
...

Good luck!

Fults

Answer 2

Accepted answer is definetely correct but is a workaround. It will only work if you are inserting the cells in order.

If you are using the InsertCellInWorksheet method from MSDN, there is a bug on the cell reference comparing when comparing a cell references of different length such as 'Z' and 'AA'.

// Cells must be in sequential order according to CellReference. 
// Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
    {
        refCell = cell;
        break;
    }
}

You might want to change the string.Compare to:

if (ColumnNameParse(cell.CellReference.Value) > ColumnNameParse(cellReference))
{
    refCell = cell;
    break;
}

using the ColumnNameParse method taken from this answer:

public int ColumnNameParse(string cellReference)
{
    var value = cellReference.TrimEnd("1234567890".ToCharArray());

    // assumes value.Length is [1,3]
    // assumes value is uppercase
    var digits = value.PadLeft(3).Select(x => "ABCDEFGHIJKLMNOPQRSTUVWXYZ".IndexOf(x));
    return digits.Aggregate(0, (current, index) => (current * 26) + (index + 1));
}

Bauer

This one costed me 2 nights. Damn, I hate openxml. Thanks for the answer! – Intussusception

Took me a lot of hours then i realize this is the create cell positioning error. I don't understand why OpenXML not supporting R1C1 cell address name which would make everyone's job much easier. – Quasi

Answer 3

Change

if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) 

to

if (string.Compare(cell.CellReference.Value, cellReference, true) > 0
                        && cell.CellReference.Value.Length >= cellReference.Length)

Gareri

as B7 does not follows AA7 :-) – Gareri

Have to agree converting to integer is so far the simplest solution – Quasi