C#, Write XLSX using OpenXmlWriter and Open XML SDK

Question

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 the OpenXmlWriter according to this http://blogs.msdn.com/b/brian_jones/archive/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk.aspx

class 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?

Can you share your code to close the reader and writer and replace the original worksheet part with the replacement part.? — Joshua Drake
– Joshua Drake, Commented May 8, 2012 at 20:44
I stripped that from the original source as I couldn't make sense as to why it was there. — Snæbjørn
– Snæbjørn, Commented May 8, 2012 at 21:13
I don't want to update values, I want to write values. But I got it working by adding this writer.WriteStartElement(new Worksheet()); above new SheetData() — Snæbjørn
– Snæbjørn, Commented May 9, 2012 at 17:56
This tutorial shows you how to use openxmlwriter from scratch, it include writing number type and sharedstring type codeproject.com/Articles/877791/… — gavin
– gavin, Commented Feb 21, 2015 at 1:58

EJoshuaS - Stand with Ukraine · Accepted Answer · 2019-06-18 16:35:04Z

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>.

Daniel A. White · Accepted Answer · 2017-08-30 18:38:28Z

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();
}

edited Aug 30, 2017 at 18:38

Daniel A. White

192k49 gold badges389 silver badges473 bronze badges

answered Apr 29, 2015 at 11:44

pblack

8081 gold badge7 silver badges14 bronze badges

Collectives™ on Stack Overflow

C#, Write XLSX using OpenXmlWriter and Open XML SDK

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related