C#OPENXML XLSX自定义列宽度 [英] C# OPENXML XLSX Custom Column width
本文介绍了C#OPENXML XLSX自定义列宽度的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
C#新手在这里！
我需要创建一个小型控制台应用程序，以将CSV文件转换为XLSX文件.
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.
我想举个例子
- 将A和C列的宽度设置为30
- 将D列的宽度设置为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();
}
}
}
}
推荐答案
In order to set the column widths you need to create a Columns element which can contain one or more Column children.
每个Column类都可以应用于Excel文件中的多个列. Min和Max属性定义Column应用于(包括)的第一列和最后一列.
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).
在您的示例中，您需要定义两个Column实例，一个实例具有Min=1和Max=2，另一个实例将Min和Max都设置为4(Min和Max是A = 1，B = 2等的数字.
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).
Columns集合需要在SheetData元素之前添加到Worksheet.
The Columns collection needs to be added to the Worksheet before the SheetData element.
在stylesPart.Stylesheet.Save();之后但在var sheetData = wsPart.Worksheet.AppendChild(new SheetData());之前添加以下代码应该可以实现以下目的:
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);
注意1:Column类未涵盖的所有列都将具有默认宽度.
Note1: Any columns not covered by the Column classes will have a default width.
注2:应指定列的所有属性(最小"，最大"，宽度"，自定义宽度").否则，Excel将确定文件已损坏.
Note2: All properties of the column (Min, Max, Width, CustomWidth) should be specified. Otherwise Excel will decide that the file is corrupted.
这篇关于C#OPENXML XLSX自定义列宽度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
