OpenXML writer column

Question

I have a question to ask using OpenXMLWriter.

I am currently using the code below to create my excel file, but I want to set up width of the columns. How should I do it?

Because I tried to write new Columns in between Worksheet() and SheetData() and i have no success so far.

Example would be very helpful. Appreciate it and Thanks!

using (SpreadsheetDocument xl = SpreadsheetDocument.Create("LargeFile.xlsx", SpreadsheetDocumentType.Workbook))
{
    List<OpenXmlAttribute> oxa;
    OpenXmlWriter oxw;

    xl.AddWorkbookPart();
    WorksheetPart wsp = xl.WorkbookPart.AddNewPart<WorksheetPart>();

    oxw = OpenXmlWriter.Create(wsp);
    oxw.WriteStartElement(new Worksheet());
    oxw.WriteStartElement(new SheetData());

    for (int i = 1; i <= 50000; ++i)
    {
        oxa = new List<OpenXmlAttribute>();
        // this is the row index
        oxa.Add(new OpenXmlAttribute("r", null, i.ToString()));

        oxw.WriteStartElement(new Row(), oxa);

        for (int j = 1; j <= 100; ++j)
        {
            oxa = new List<OpenXmlAttribute>();
            // this is the data type ("t"), with CellValues.String ("str")
            oxa.Add(new OpenXmlAttribute("t", null, "str"));

            // it's suggested you also have the cell reference, but
            // you'll have to calculate the correct cell reference yourself.
            // Here's an example:
            //oxa.Add(new OpenXmlAttribute("r", null, "A1"));

            oxw.WriteStartElement(new Cell(), oxa);

            oxw.WriteElement(new CellValue(string.Format("R{0}C{1}", i, j)));

            // this is for Cell
            oxw.WriteEndElement();
        }

        // this is for Row
        oxw.WriteEndElement();
    }

score 10 · Answer 1 · answered Jun 03 '13 at 02:55

Between these lines of code:

oxw.WriteStartElement(new Worksheet());
oxw.WriteStartElement(new SheetData());

Add the extra code parts:

oxw.WriteStartElement(new Worksheet());

oxw.WriteStartElement(new Columns());

oxa = new List<OpenXmlAttribute>();
// min and max are required attributes
// This means from columns 2 to 4, both inclusive
oxa.Add(new OpenXmlAttribute("min", null, "2"));
oxa.Add(new OpenXmlAttribute("max", null, "4"));
oxa.Add(new OpenXmlAttribute("width", null, "25"));
oxw.WriteStartElement(new Column(), oxa);
oxw.WriteEndElement();

oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("min", null, "6"));
oxa.Add(new OpenXmlAttribute("max", null, "6"));
oxa.Add(new OpenXmlAttribute("width", null, "40"));
oxw.WriteStartElement(new Column(), oxa);
oxw.WriteEndElement();

oxw.WriteEndElement();

oxw.WriteStartElement(new SheetData());

Thx, this works. Note that "min", "max" and "width" are case sensitive... It won't work if you use, for example, "Min". — Torben Kohlmeier, Nov 02 '15 at 15:38

score 0 · Answer 2 · answered May 31 '13 at 22:26

The easy way will be to use an excel template and changes the width according to your need. Later when you write data the column width will be preserved automatically.

Another way will be: Using OpenXML productivity tool reflect the code of your desired excel file. Then use the style information from the file to programmatically set the cell/column width.

OpenXML writer column

2 Answers2