C# Open XML：我们发现某些内容存在问题。新添加的工作表不显示任何数据

Question

下面是iam用于将新工作表添加到现有电子表格中的代码。

我正在传递一个列表<T> 在输入中，这个T只有两个属性"code"one_answers"description"。我循环遍历每个T属性，并将它们放入sheetdata中，最后保存spreadhseet。

private static void PutInExcel(List<RulesEngineOutput> output)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"C:ATPSprintsPAREIO.xlsx", true))
{

// Add a blank WorksheetPart.
WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = document.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
string sheetName = "NewRole" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Code", CellValues.String),
ConstructCell("Description", CellValues.String));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);

foreach (var reItem in output)
{
row = new Row();
row.Append(
ConstructCell(reItem.Code.ToString(), CellValues.Number),
ConstructCell(reItem.Description, CellValues.String)
);
sheetData.AppendChild(row);
}

newWorksheetPart.Worksheet.Save();
document.WorkbookPart.Workbook.Save();
document.Save();
}
}

问题是一切都没有错误，我的意思是，我可以在调试窗口中看到添加的工作表，我也保存了所有内容，但当我打开spreadhseet时，我看到一个错误消息

我们发现一些内容有问题

最后，纸张显示为没有任何内容，如下所示：

空白页，带页名

Answer 1

我认为问题在于您在哪里声明sheetdata，因为sheetdata应该已经存在。

尝试

SheetData sheetData = newWorksheetPart.Worksheet.GetFirstChild<SheetData>();

Answer 2

后来我做了两个更改(请参阅下面代码中的注释或将其与原始代码进行比较(——我这样做是因为xml出错了，所以我发现将代码转换为数字就是出错的情况。此外，Alan H建议尝试消除工作表中传递的new SheetData((，因此我使用了默认的空构造函数。

private static void PutInExcel(List<RulesEngineOutput> output)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"C:ATPSprintsPAREIO.xlsx", true))
{

// Add a blank WorksheetPart.
WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(); // Change 1
Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = document.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
string sheetName = "NewRole" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Code", CellValues.String), // Change 2
ConstructCell("Description", CellValues.String));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);

foreach (var reItem in output)
{
row = new Row();
row.Append(
ConstructCell(reItem.Code.ToString(), **CellValues.String**),
ConstructCell(reItem.Description, CellValues.String)
);
sheetData.AppendChild(row);
}

newWorksheetPart.Worksheet.Save();
document.WorkbookPart.Workbook.Save();
document.Save();

}
//string csv = String.Join(",", output.Select(x => x.ToString()).ToArray());

}