C#OPENXML XLSX自定义列宽

C#新手在这里!

我需要创建一个小型控制台应用程序来将CSV文件转换为XLSX文件.

我有我的所有样式和数据,但我想在某些列上设置不同的(默认)宽度.经过一天的搜索和阅读后,我仍然无法弄清楚如何让它发挥作用.

作为一个例子,我想

>将列A和C设置为宽度30
>将列D设置为宽度20

任何帮助或提示都会很棒.
我的代码现在在下面

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,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,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,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,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),StyleIndex = 2 });
                            }
                            else if (elements.First() == element && element == "Fail")
                            {
                                row.AppendChild(new Cell() { CellValue = new CellValue(element),StyleIndex = 1 });
                            }
                            else 
                            {
                                row.AppendChild(new Cell() { CellValue = new CellValue(element),StyleIndex = 5 });
                            }
                        }
                    }
                    if (parser.LineNumber == 4)
                    {
                        foreach (string element in elements)
                        {
                            row.AppendChild(new Cell() { CellValue = new CellValue(element),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),StyleIndex = 2 });
                            }
                            else if (i == 1 && element == "Fail")
                            {
                                row.AppendChild(new Cell() { CellValue = new CellValue(element),StyleIndex = 1 });
                            }
                            else
                            {
                                row.AppendChild(new Cell() { CellValue = new CellValue(element),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();                
            }
        }    
    }
}

解决方法

要设置列宽,需要创建一个 Columns元素,该元素可以包含一个或多个 Column子元素.

每个Column类都可以应用于Excel文件中的多个列. Min和Max属性定义Column适用的第一列和最后一列(包括).

在您的示例中,您需要定义两个Column实例,一个Min = 1,Max = 2,另一个Min和Max都设置为4(Min和Max是数字,A = 1,B = 2等).

需要将Column集合添加到SheetData元素之前的Worksheet中.

在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将确定该文件已损坏.

相关文章

/** C+⬑ * 默认成员函数 原来C++类中,有6个默认成员函数: 构造函数 析构函数 拷贝...
#pragma once // 1. 设计一个不能被拷贝的类/* 解析:拷贝只会放生在两个场景中:拷贝构造函数以及赋值运...
C类型转换 C语言:显式和隐式类型转换 隐式类型转化:编译器在编译阶段自动进行,能转就转,不能转就编译...
//异常的概念/*抛出异常后必须要捕获,否则终止程序(到最外层后会交给main管理,main的行为就是终止) try...
#pragma once /*Smart pointer 智能指针;灵巧指针 智能指针三大件//1.RAII//2.像指针一样使用//3.拷贝问...
目录&lt;future&gt;future模板类成员函数:promise类promise的使用例程:packaged_task模板类例程...