OpenXML Selecting a specific column

Question

I'm attempting to select a specific column so that I can set it's width automatically but am having little success. Should I not be able to use MSDN's version of GetRow to select the column from the columns collection instead?

return worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).First();

return worksheet.GetFirstChild<SheetData>().Elements<Column>().Where(r => r.Min== columnNumber && r.Max == columnNumber).First();

Am I taking the right approach? FWIW I'm aware of how to create the column from scratch and set the width, but that isn't feasible as I don't know what data is going to be inside the columns when I create them in my constructor.

score 1 · Answer 1 · answered Apr 16 '12 at 21:17

1

You can append a columns Object to a Worksheet. Then I made a method that sets the width.

Worksheet ws = new Worksheet();
Columns columns = new Columns();

columns.Append(CreateColumnData(1, 1, 14.87));

ws.append(columns);


private static Column CreateColumnData(UInt32 StartColumnIndex, UInt32 EndColumnIndex, double ColumnWidth)
    {
        Column column;
        column = new Column();
        column.Min = StartColumnIndex;
        column.Max = EndColumnIndex;
        column.Width = ColumnWidth;
        column.CustomWidth = true;
        return column;
    }

answered Apr 16 '12 at 21:17

broguyman

1,386
4
19
36

This way no matter what goes into Column 1, the width will be set to 14.87 – broguyman Apr 16 '12 at 21:20
You're exactly right... and I could already do this before... What I need to do is Dynamically select the width of the column based on what is inside of it. :( This is no help. :( @broguyman – Kulingar Apr 19 '12 at 17:14

score 0 · Answer 2 · answered Jun 01 '15 at 18:12

I have had the same problem, needed to set width for different columns, but was unable to find solution on the web. Answers provided so far are just showing how to create column with specified width, but not how to update dynamically width of specified column. May be I'm not much familiar with .net's openxml library but DocumentFormat.OpenXml seams not documented well and not intuitive as Office.Interop. As my application was running as azure service I did not have a chance to use Office.Interop. Solution for me was to use closedxml library which simplifies work with openxml documents much like in Office.Interop. You can google it.

score -1 · Answer 3 · answered Nov 14 '12 at 13:49

He did not write that method. The method comes from a complete article that is able to set the width of a column, based on the length of the cell-value: http://polymathprogrammer.com/2010/01/11/custom-column-widths-in-excel-open-xml/

Hope this helps! ;)

OpenXML Selecting a specific column

3 Answers3