大佬教程收集整理的这篇文章主要介绍了C#OPENXML XLSX自定义列宽,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
我需要创建一个小型控制台应用程序来将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(@R_616_10495@ng[] args) { @R_616_10495@ng xLSX_path = @"c:\test\test.xLSX"; @R_616_10495@ng 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.From@R_616_10495@ng("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.From@R_616_10495@ng("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.From@R_616_10495@ng("81DAF5") }; formatBlue.BACkgroundColor = new BACkgroundColor { Indexed = 64 }; // Create Light Green fill var formatLightGreen = new PatternFill() { PatternType = PatternValues.solid }; formatLightGreen.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.From@R_616_10495@ng("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) { @R_616_10495@ng line = parser.ReadLine(); @R_616_10495@ng[] elements = line.Split(';'); var row = sheetData.AppendChild(new Row()); if (parser.Linenumber == 2) { foreach (@R_616_10495@ng element in elements) { row.AppendChild(new Cell() { CellValue = new CellValue(element),Datatype = cellValues.@R_616_10495@ng,StyleIndex = 3 }); } } if (parser.Linenumber == 3) { foreach (@R_616_10495@ng 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 (@R_616_10495@ng element in elements) { row.AppendChild(new Cell() { CellValue = new CellValue(element),StyleIndex = 4 }); } } if (parser.Linenumber > 4 || parser.Linenumber == -1) { int i = 0; foreach (@R_616_10495@ng 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实例,@L_535_1@min = 1,Max = 2,另@L_535_1@min和Max都设置为4(Min和Max是数字,A = 1,B = 2等).
需要将column集合@L_674_33@到SheetData元素之前的Worksheet中.
在stylesPart.Stylesheet.Save()之后@L_674_33@以下代码;但在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);
以上是大佬教程为你收集整理的C#OPENXML XLSX自定义列宽全部内容,希望文章能够帮你解决C#OPENXML XLSX自定义列宽所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。