C# OPENXML XLSX Custom Column width

Question

C# newbie here!

I need to create a small console application to convert CSV files into XLSX files.

I have all my styles and data working, but I want to set a different (from default) width on some columns. And after a day of searching and reading I still can't figure out how to get it to work.

As an example I want to

• set columns A and C to a width of 30
• Set column D to a width of 20

Any help or tips would be great. My code right now below

using System;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using Microsoft.VisualBasic.FileIO;

namespace xml_test
{
    class Program
    {
        static void Main(string[] args)
        {          
            string xlsx_path = @"c:\test\test.xlsx";
            string CSV_Path = @"c:\test\test.csv";

            // Skal nok ha en try her i tilfellet et dolument er åpent eller noe slikt...
            using (var spreadsheet = SpreadsheetDocument.Create(xlsx_path, SpreadsheetDocumentType.Workbook))
            {
                spreadsheet.AddWorkbookPart();
                spreadsheet.WorkbookPart.Workbook = new Workbook();
                var wsPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
                wsPart.Worksheet = new Worksheet();                            
                SheetFormatProperties sheetFormatProperties = new SheetFormatProperties()
                {
                    DefaultColumnWidth = 15,
                    DefaultRowHeight = 15D                 
                };

                wsPart.Worksheet.Append(sheetFormatProperties);               
                var stylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                stylesPart.Stylesheet = new Stylesheet();

                // Font list
                // Create a bold font
                stylesPart.Stylesheet.Fonts = new Fonts();
                Font bold_font = new Font();         // Bold font
                Bold bold = new Bold();
                bold_font.Append(bold);

                // Add fonts to list
                stylesPart.Stylesheet.Fonts.AppendChild(new Font());
                stylesPart.Stylesheet.Fonts.AppendChild(bold_font); // Bold gets fontid = 1
                stylesPart.Stylesheet.Fonts.Count = 2;

                // Create fills list
                stylesPart.Stylesheet.Fills = new Fills();

                // create red fill for failed tests
                var formatRed = new PatternFill() { PatternType = PatternValues.Solid };
                formatRed.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FF6600") }; // red fill
                formatRed.BackgroundColor = new BackgroundColor { Indexed = 64 };

                // Create green fill for passed tests
                var formatGreen = new PatternFill() { PatternType = PatternValues.Solid };
                formatGreen.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("99CC00") }; // green fill
                formatGreen.BackgroundColor = new BackgroundColor { Indexed = 64 };

                // Create blue fill
                var formatBlue = new PatternFill() { PatternType = PatternValues.Solid };
                formatBlue.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("81DAF5") };
                formatBlue.BackgroundColor = new BackgroundColor { Indexed = 64 };

                // Create Light Green fill
                var formatLightGreen = new PatternFill() { PatternType = PatternValues.Solid };
                formatLightGreen.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("F1F8E0") };
                formatLightGreen.BackgroundColor = new BackgroundColor { Indexed = 64 };

                // Append fills to list
                stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); // required, reserved by Excel
                stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }); // required, reserved by Excel
                stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = formatRed }); // Red gets fillid = 2
                stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = formatGreen }); // Green gets fillid = 3
                stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = formatBlue }); // Blue gets fillid = 4, old format1
                stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = formatLightGreen }); // LightGreen gets fillid = 5, old format2
                stylesPart.Stylesheet.Fills.Count = 6;

                // Create border list
                stylesPart.Stylesheet.Borders = new Borders();

                // Create thin borders for passed/failed tests and default cells
                LeftBorder leftThin = new LeftBorder() { Style = BorderStyleValues.Thin };
                RightBorder rightThin = new RightBorder() { Style = BorderStyleValues.Thin };
                TopBorder topThin = new TopBorder() { Style = BorderStyleValues.Thin };
                BottomBorder bottomThin = new BottomBorder() { Style = BorderStyleValues.Thin };

                Border borderThin = new Border();
                borderThin.Append(leftThin);
                borderThin.Append(rightThin);
                borderThin.Append(topThin);
                borderThin.Append(bottomThin);

                // Create thick borders for headings
                LeftBorder leftThick = new LeftBorder() { Style = BorderStyleValues.Thick };
                RightBorder rightThick = new RightBorder() { Style = BorderStyleValues.Thick };
                TopBorder topThick = new TopBorder() { Style = BorderStyleValues.Thick };
                BottomBorder bottomThick = new BottomBorder() { Style = BorderStyleValues.Thick };

                Border borderThick = new Border();
                borderThick.Append(leftThick);
                borderThick.Append(rightThick);
                borderThick.Append(topThick);
                borderThick.Append(bottomThick);

                // Add borders to list
                stylesPart.Stylesheet.Borders.AppendChild(new Border());
                stylesPart.Stylesheet.Borders.AppendChild(borderThin);
                stylesPart.Stylesheet.Borders.AppendChild(borderThick);
                stylesPart.Stylesheet.Borders.Count = 3;

                // Create blank cell format list
                stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
                stylesPart.Stylesheet.CellStyleFormats.Count = 1;
                stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());

                // Create cell format list
                stylesPart.Stylesheet.CellFormats = new CellFormats();
                // empty one for index 0, seems to be required
                stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());


                // cell format for failed tests, Styleindex = 1, Red fill and bold text
                stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 1, BorderId = 2, FillId = 2, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });

                // cell format for passed tests, Styleindex = 2, Green fill and bold text
                stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 1, BorderId = 2, FillId = 3, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });

                // cell format for blue background, Styleindex = 3, blue fill and bold text
                stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 1, BorderId = 1, FillId = 4, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });

                // cell format for light green background, Styleindex = 4, light green fill and bold text
                stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 1, BorderId = 1, FillId = 5, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });

                // default cell style, thin border and rest default
                stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 1, FillId = 0, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });

                stylesPart.Stylesheet.CellFormats.Count = 6;
                stylesPart.Stylesheet.Save();
                var sheetData = wsPart.Worksheet.AppendChild(new SheetData());                
                TextFieldParser parser = new TextFieldParser(CSV_Path);
                parser.TextFieldType = FieldType.Delimited;
                parser.SetDelimiters(";");
                while (!parser.EndOfData)
                {
                    string line = parser.ReadLine();                    
                    string[] elements = line.Split(';');
                    var row = sheetData.AppendChild(new Row());
                    if (parser.LineNumber == 2)
                    {                        
                        foreach (string element in elements)
                        {
                            row.AppendChild(new Cell() { CellValue = new CellValue(element), DataType = CellValues.String, StyleIndex = 3 });                            
                        }
                    }
                    if (parser.LineNumber == 3)
                    {
                        foreach (string element in elements)
                        {                            
                            if (elements.First() == element && element == "Pass")
                            {
                                row.AppendChild(new Cell() { CellValue = new CellValue(element), DataType = CellValues.String, StyleIndex = 2 });
                            }
                            else if (elements.First() == element && element == "Fail")
                            {
                                row.AppendChild(new Cell() { CellValue = new CellValue(element), DataType = CellValues.String, StyleIndex = 1 });
                            }
                            else 
                            {
                                row.AppendChild(new Cell() { CellValue = new CellValue(element), DataType = CellValues.String, StyleIndex = 5 });
                            }
                        }
                    }
                    if (parser.LineNumber == 4)
                    {
                        foreach (string element in elements)
                        {
                            row.AppendChild(new Cell() { CellValue = new CellValue(element), DataType = CellValues.String, StyleIndex = 4 });                            
                        }
                    }
                    if (parser.LineNumber > 4 || parser.LineNumber == -1)
                    {
                        int i = 0;
                        foreach (string element in elements)
                        {
                            if (i == 1 && element == "Pass")
                            {
                                row.AppendChild(new Cell() { CellValue = new CellValue(element), DataType = CellValues.String, StyleIndex = 2 });
                            }
                            else if (i == 1 && element == "Fail")
                            {
                                row.AppendChild(new Cell() { CellValue = new CellValue(element), DataType = CellValues.String, StyleIndex = 1 });
                            }
                            else
                            {
                                row.AppendChild(new Cell() { CellValue = new CellValue(element), DataType = CellValues.String, StyleIndex = 5 });
                            }
                            i++;
                        }
                    }
                }                
                var sheets = spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets());
                sheets.AppendChild(new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(wsPart), SheetId = 1, Name = "sheet1" });             
                spreadsheet.WorkbookPart.Workbook.Save();                
            }
        }    
    }
}

Answer 1

In order to set the column widths you need to create a Columns element which can contain one or more Column children.

Each Column class can apply to more than one column in the Excel file. The Min and Max properties define the first and last column that the Column applies to (inclusive).

In your example, you'll need to define two Column instances, one with Min=1 and Max=2 and the other with both Min and Max set to 4 (the Min and Max are numeric with A=1, B =2 etc).

The Columns collection needs to be added to the Worksheet before the SheetData element.

Adding the following code after stylesPart.Stylesheet.Save(); but before var sheetData = wsPart.Worksheet.AppendChild(new SheetData()); should achieve what you are after:

Columns columns = new Columns();

columns.Append(new Column() { Min = 1, Max = 3, Width = 20, CustomWidth = true });
columns.Append(new Column() { Min = 4, Max = 4, Width = 30, CustomWidth = true });

wsPart.Worksheet.Append(columns);

Note1: Any columns not covered by the Column classes will have a default width.

Note2: All properties of the column (Min, Max, Width, CustomWidth) should be specified. Otherwise Excel will decide that the file is corrupted.