C#, Write XLSX using OpenXmlWriter and Open XML SDK
Solution 1
I don't understand this well enough yet, but I got it working. Here's what I ended up with.
class Program
{
static void Main(string[] args)
{
File.Copy("book.xlsx", "output.xlsx", true);
WriteRandomValuesSAX("output.xlsx", 10, 10);
}
static void WriteRandomValuesSAX(string filename, int numRows, int numCols)
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last();
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
Row r = new Row();
Cell c = new Cell();
CellValue v = new CellValue("Test");
c.AppendChild(v);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
for (int row = 0; row < numRows; row++)
{
writer.WriteStartElement(r);
for (int col = 0; col < numCols; col++)
{
writer.WriteElement(c);
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
}
}
}
Notice I added writer.WriteStartElement(new Worksheet());
and another writer.WriteEndElement();
I found the correct xml format here: http://msdn.microsoft.com/en-us/library/gg278309.aspx
Which is
<?xml version="1.0" encoding="utf-8"?> <x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:sheetData>
<x:row r="1">
<x:c r="A1" t="n">
<x:v>100</x:v>
</x:c>
</x:row>
</x:sheetData> </x:worksheet>
So I opened (unzipped) the xlsx file and navigated to output.xlsx\xl\worksheets\sheet1.xml and saw that I was missing the <x:worksheet>
.
Solution 2
Using the code above gives me the error message "Excel found unreadable content" in Excel 2007. Using information from here how-to-properly-use-openxmlwriter-to-write-large-excel-files and changing the code when adding the columns to the following removed the error.
for(int col = 0; col < numCols; col++)
{
var oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("t",null,"str"));
writer.WriteStartElement(c,oxa);
writer.WriteElement(new CellValue(string.Format("R{0}C{1}", row, col)));
writer.WriteEndElement();
}
Snæbjørn
Updated on June 05, 2022Comments
-
Snæbjørn almost 2 years
In C#, the following program compiles and runs, but it doesn't write anything in the excel output file.
I got it working without the
OpenXmlWriter
but I started running out of memory so I have to switch to theOpenXmlWriter
according to this http://blogs.msdn.com/b/brian_jones/archive/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk.aspxclass Program { static void Main(string[] args) { File.Copy("book.xlsx", "output.xlsx", true); WriteValuesSAX("output.xlsx", 10, 10); } static void WriteValuesSAX(string filename, int numRows, int numCols) { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart); Row r = new Row(); Cell c = new Cell(); CellValue v = new CellValue("Test"); c.AppendChild(v); writer.WriteStartElement(new SheetData()); for (int row = 0; row < numRows; row++) { writer.WriteStartElement(r); for (int col = 0; col < numCols; col++) { writer.WriteElement(c); } writer.WriteEndElement(); } writer.WriteEndElement(); writer.Close(); } } }
Why doesn't it write anything to output?