C#OPENXML XLSX自定义列宽

概述C#新手在这里！ 我需要创建一个小型控制台应用程序来将CSV文件转换为XLSX文件. 我有我的所有样式和数据,但我想在某些列上设置不同的(默认)宽度.经过一天的搜索和阅读后,我仍然无法弄清楚如何让它发挥作用. 作为一个例子,我想 >将列A和C设置为宽度30 >将列D设置为宽度20 任何帮助或提示都会很棒. 我的代码现在在下面 using System;using System.Linq;usi 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#OPENXML XLSX自定义列宽全部内容，希望文章能够帮你解决C#OPENXML XLSX自定义列宽所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。