C# OPENXML XLSX Custom Column width
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
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.