我正在使用 NPOI 来操作 Excel(.xlsx) 文件数据和格式。我想知道是否有一种方法可以将单元格范围格式化为表格。
// something like.
ITable table = worksheet.FormatAsTable("A1:C4");
在互联网上做了一些研究,但还没有运气。任何帮助将非常感激!
[2021/05/28更新]:
谢谢提醒。发现没有设置ctTable的id、名称和显示名称会得到这个错误Removed Part: /xl/tables/table1.xml part with XML error. (Table) Load error. Line 1, column 247.
(以下示例代码已修复。)
根据评论和link https://thinktibits.blogspot.com/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html由 @Gian Paolo 提供,使用 NPOI 实现“格式为表”的 C# 方法如下:
安装包 NPOI -版本 2.5.3
// NPOI dependencies
using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
IWorkbook workbook = new XSSFWorkbook();
XSSFSheet worksheet = workbook.CreateSheet("Grades") as XSSFSheet;
InsertTestData(worksheet);
// Format Cell Range As Table
XSSFTable xssfTable = worksheet.CreateTable();
CT_Table ctTable = xssfTable.GetCTTable();
AreaReference myDataRange = new AreaReference(new CellReference(0, 0), new CellReference(3, 2));
ctTable.@ref = myDataRange.FormatAsString();
ctTable.id = 1;
ctTable.name = "Table1";
ctTable.displayName = "Table1";
ctTable.tableStyleInfo = new CT_TableStyleInfo();
ctTable.tableStyleInfo.name = "TableStyleMedium2"; // TableStyleMedium2 is one of XSSFBuiltinTableStyle
ctTable.tableStyleInfo.showRowStripes = true;
ctTable.tableColumns = new CT_TableColumns();
ctTable.tableColumns.tableColumn = new List<CT_TableColumn>();
ctTable.tableColumns.tableColumn.Add(new CT_TableColumn() { id = 1, name = "ID" });
ctTable.tableColumns.tableColumn.Add(new CT_TableColumn() { id = 2, name = "Name" });
ctTable.tableColumns.tableColumn.Add(new CT_TableColumn() { id = 3, name = "Score" });
using (FileStream file = new FileStream(@"test.xlsx", FileMode.Create))
{
workbook.Write(file);
}
// Function to Populate Test Data
private void InsertTestData(XSSFSheet worksheet)
{
worksheet.CreateRow(0);
worksheet.GetRow(0).CreateCell(0).SetCellValue("ID");
worksheet.GetRow(0).CreateCell(1).SetCellValue("Name");
worksheet.GetRow(0).CreateCell(2).SetCellValue("Score");
worksheet.CreateRow(1);
worksheet.GetRow(1).CreateCell(0).SetCellValue(1);
worksheet.GetRow(1).CreateCell(1).SetCellValue("John");
worksheet.GetRow(1).CreateCell(2).SetCellValue(82);
worksheet.CreateRow(2);
worksheet.GetRow(2).CreateCell(0).SetCellValue(2);
worksheet.GetRow(2).CreateCell(1).SetCellValue("Sam");
worksheet.GetRow(2).CreateCell(2).SetCellValue(90);
worksheet.CreateRow(3);
worksheet.GetRow(3).CreateCell(0).SetCellValue(3);
worksheet.GetRow(3).CreateCell(1).SetCellValue("Amy");
worksheet.GetRow(3).CreateCell(2).SetCellValue(88);
}
Result:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)