[2021/05/28 更新]:
感谢提醒。发现如果不设置ctTable的id,name和displayName会导致以下错误:Removed Part: /xl/tables/table1.xml part with XML error. (Table) Load error. Line 1, column 247.
(下面示例代码已修复)
根据评论和 @Gian Paolo 提供的链接,使用 NPOI 实现“格式化为表格”的 C# 代码如下:
Install-Package NPOI -Version 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);
}
结果:
ctTable.autoFilter = new CT_AutoFilter(); ctTable.autoFilter.@ref = myDataRange.FormatAsString();
然后结果看起来与问题截图完全相同。 - Kamil SpisakctTableCatStat.autoFilter = new CT_AutoFilter();
ctTableCatStat.autoFilter.filterColumn = new List<CT_FilterColumn>();
ctTableCatStat.autoFilter.filterColumn.Add(new CT_FilterColumn() { colId = 1, showButton = true });
最后一个步骤需要为每个想要进行过滤的列重复执行。 - Disco Globeulon如果有人看起来一切正常但仍然遇到Excel错误,由于权限不够无法添加评论,我想提供帮助。我曾经遇到过将一个对象列表迭代成tableColumn的问题。通过首先构建一个数组,解决了我的所有问题:
var headerNames = _headers.Select(x => x.Name).ToArray();
for (uint i = 0; i < headerNames.Count; i++)
{
ctTable.tableColumns.tableColumn.Add(new CT_TableColumn() { id = i + 1, name = headerNames[i] });
}