Geek Answers Handbook

C # OPENXML XLSX Custom Column Width

C # newbie here!

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

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

As an example, I want

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

Any help or advice would be wonderful. My code is now lower

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();                
            }
        }    
    }
}
+4
source share
1 answer

To set the width of the columns, you need to create an element Columnsthat can contain one or more Column.

Column Excel. Min Max , Column ().

Column, Min=1 Max=2, Min Max, 4 (Min Max A = 1, B = 2 ..).

Columns Worksheet SheetData.

stylesPart.Stylesheet.Save();, , var sheetData = wsPart.Worksheet.AppendChild(new SheetData()); , :

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

1: , Column, .

2: (Min, Max, Width, CustomWidth). Excel , .

+7

More articles:


All Articles