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
How to Get Only Date from a Datetime Value in Razor Page
How to Get the Display Name Attribute of an Enum Member Via MVC Razor Code
Formatting Date in Linq-To-Entities Query Causes Exception
Smtpexception: Unable to Read Data from the Transport Connection: Net_Io_Connectionclosed
Discord.Net C# 1.0.2 How to Send Messages to Specific Channels
How to Set Shadow Effect on Imageview
Post Json Array to MVC Controller
How to Save Mailmessage Object to Disk as *.Eml or *.Msg File
How to Create a 2D Array from a CSV File
Console.Writeline Does Not Show Up in Output Window
Deserialize Json String in to Multiple C# Objects
Convert Time With Milliseconds to Datetime in C#
Regex to Match Alphanumeric and Spaces