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

Question

I created a table using the Open XML SDK in C # and successfully completed two sheets.

When I try to fill in the third, I get an error message "Unreadable content" when I open the completed document, and it seems to occur when I try to fill in more than 25 cells in the row on the third.

I use the same piece of code that 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); } 

Single filled cells: A and AA through AH.

I am right in thinking that I am pushing some kind of limit, and if so, how can it reset?

I saw several reports of an unreadable content error, and I looked through the documentation, but so far I have not been able to find anything that is applicable.

Help will be appreciated!

thanks

Answer 1

Please see the "InsertCellInWorksheet (...)" method. If you use this construct inside -

 ... row.InsertBefore(newCell, refCell); ... 

it will not work correctly if you want to fill in the columns "AZ" and "AA -...", even if you want to fill in only two columns (for example), "Z" and "AA". So try using this method instead:

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

Good luck

Answer 2

The accepted answer is definitely correct, but is a workaround. It will only work if you insert cells in order.

If you use the InsertCellInWorksheet method from MSDN , there is an error when comparing a cell when comparing cell references of different lengths, 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 line. Pay attention 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)); } 

Answer 3

Edit

 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)