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

Answer 1

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