CODEWITHSUNDEEP
c#excelopenxml
user2438754
user2438754

Reputation: 71

OpenXML writer column

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

Upvotes: 7

Views: 6731

Answers (2)

Vincent Tan
Vincent Tan

Reputation: 3166

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

Upvotes: 10

Zabed Akbar
Zabed Akbar

Reputation: 537

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.

Upvotes: 0

Related Questions