Repaired Records:Cell Information from Worksheet Created from Scratch

Repaired Records : Cell information from worksheet created from scratch

If you are adding a string to a cell rather than a number (or a string that can be converted to a number) then you should use an inline string or a shared string instead of the CellValue. You can only use CellValue if the value is numeric.

The XML generated when using CellValue looks something like:

<x:row>
  <x:c>
    <x:v>12345</x:v>
  </x:c>
</x:row>

when you use an inline string it looks like:

<x:row>
  <x:c t="inlineStr">
    <x:is>
      <x:t>Foo</x:t>
    </x:is>
  </x:c>
</x:row>

note the "is" node for inline string and that the cell type attribute is set to "inlineStr".

Here is C# code to generate correct XML for a cell containing text:

cell.DataType = CellValues.InlineString;
cell.InlineString = new InlineString() { Text = new Text(textToInsert) };

From what I have read using shared strings is preferable but using inline strings avoids the error and looks just fine when you open the file in Excel.

Repaired Records : Cell information from worksheet created

There are 2 issues here that I can see. The first is that your use of Columns is incorrect. You should use Columns if you wish to control things such as the width of a column. To use Columns correctly, you'll need to add child Column elements. For example (taken from here):

Columns columns = new Columns();

columns.Append(new Column() { Min = 1, Max = 3, Width = 20, CustomWidth = true });
columns.Append(new Column() { Min = 4, Max = 4, Width = 30, CustomWidth = true });

In your sample you could just remove the following two lines

Columns columns = new Columns();
worksheetPart.Worksheet.AppendChild(columns);

The second issue is the StyleIndex you are using; the style doesn't exist in your document because you haven't added it. The easiest thing to do here is to just remove the StyleIndex altogether.

When debugging files like this, it's always worth looking at the OpenXml Productivity Tool. You can open a generated file in the tool and validate it to see what errors you have in your file.

OpenXML file needs repair when opened in Excel

You are outputting the wrong cell.DataType. In your headers you are outputting CellValues.SharedString but then adding the string directly to the cell. You need to set it to CellValues.String.

It's hard to tell what you're outputting for the rest of the cells as you're calling GetCellValueType(val.GetType()) which isn't listed but again, you will need to avoid using CellValues.SharedString if you are writing out a string.

The CellValues.SharedString should only be used if you write the value itself to the SharedStringTable.

Corrupt records from OpenXML Spreadsheet creation

If you're inserting a string value, you should be using CellValues.InlineString

  foreach (var header in headers)
        row.AppendChild(new Cell (new InlineString(new Text(header))) {
            CellReference = GetColumnLetter(column++) + "1",
            DataType = CellValues.InlineString
        });

---

Related Topics