c# - 使用 openxml 编辑文件后,我们发现“filename.xls”中的某些内容存在问题
问题描述
我编写了一段从数据库中获取数据的代码,然后我尝试按区域对其进行排序并将其导出到 Excel 文件中。excel中的每张表都有每个区域的数据。我不断收到此错误:
我们发现“filename.xls”中的某些内容存在问题。你想让我们尽可能多地恢复吗?
我似乎无法弄清楚我在这里编码错误的地方导致了这种情况。这是我的代码:
private void AddSheet(SpreadsheetDocument document, WorkbookPart workbookPart, Sheets sheets, string sheetName, UInt32 sheetNumber, List<RentalFeeReportingRecord> currentRecordList)
{
Dictionary<int, string> listofCoreBankingIds = new Dictionary<int, string>();
listofCoreBankingIds = currentRecordList.Select(x => new { id = x.CoreBankingId, regionName = x.RegionName }).Distinct().ToDictionary(y => y.id, y => y.regionName);
foreach (var coreBankingId in listofCoreBankingIds.OrderBy(x => x.Value))
{
WorksheetPart worksheetPart1 = workbookPart.AddNewPart<WorksheetPart>();
string previousCurrency = null;
string previousRegionName = null;
int recordCounter = 1;
double previousTotal = 0;
Worksheet worksheet1 = new Worksheet();
SheetData sheetData1 = new SheetData();
Row rowInSheet1 = new Row();
Row firstRow = new Row();
Row lastRow = new Row();
rowInSheet1.Append(
excelController.ConstructCell("Region", CellValues.String, 3), excelController.ConstructCell("Merchant - Terminal", CellValues.String, 3),
excelController.ConstructCell("Fee Charged", CellValues.String, 3),
excelController.ConstructCell("Currency", CellValues.String, 3),
excelController.ConstructCell("Processing Date", CellValues.String, 3),
excelController.ConstructCell("Description", CellValues.String, 3)
);
sheetData1.Append(rowInSheet1);
foreach (var currentRecord in currentRecordList.OrderBy(x => x.RegionName).ThenBy(z => z.Currency).ThenBy(y => y.DateProcessed))
{
if (coreBankingId.Key == currentRecord.CoreBankingId)
{
string regionName = currentRecord.RegionName;
string currentCurrency = currentRecord.Currency;
double currentTotal = currentRecord.TotalFeeAmount;
string EmptySpace = string.Empty;
string merchant = currentRecord.MerchantRecord.MerchantID + " - " + currentRecord.MerchantTerminalRecord.terminalID;
if (currentRecord.IsPinPad)
{
merchant = merchant + " - PIN Pad";
}
string description = currentRecord.TerminalRecord.description;
if (currentRecord.IsProRated)
{
int days = DateTime.DaysInMonth(currentRecord.DeployedDate.Year, currentRecord.DeployedDate.Month);
description = currentRecord.TerminalRecord.description + " (" + currentRecord.MonthlyFee + " Fee \\ " + days.ToString() + " Days) * " + currentRecord.DaysActive.ToString() + " Days Active";
}
rowInSheet1 = new Row();
firstRow = new Row();
lastRow = new Row();
if (recordCounter == currentRecordList.Count() && previousRegionName == regionName)
{
firstRow.Append(
excelController.ConstructCell(regionName, CellValues.String, 2),
excelController.ConstructCell(merchant, CellValues.String, 2),
excelController.ConstructCell(currentRecord.TotalFee.ToString("N2"), CellValues.String, 1),
excelController.ConstructCell(currentRecord.Currency, CellValues.String, 2),
excelController.ConstructCell(currentRecord.DateProcessed.ToString("MMM dd, yyyy"), CellValues.String, 2),
excelController.ConstructCell(description, CellValues.String, 2)
);
sheetData1.Append(firstRow);
rowInSheet1.Append(
excelController.ConstructCell("TOTALS", CellValues.String, 6),
excelController.ConstructCell(" ", CellValues.String, 2),
excelController.ConstructCell(currentRecord.TotalFeeAmount.ToString("N2"), CellValues.String, 6),
excelController.ConstructCell(currentRecord.Currency, CellValues.String, 6),
excelController.ConstructCell(" ", CellValues.String, 2),
excelController.ConstructCell(" ", CellValues.String, 2)
);
sheetData1.Append(rowInSheet1);
}
else if (recordCounter == currentRecordList.Count())
{
rowInSheet1.Append(
excelController.ConstructCell(regionName, CellValues.String, 2),
excelController.ConstructCell(merchant, CellValues.String, 2),
excelController.ConstructCell(currentRecord.TotalFee.ToString("N2"), CellValues.String, 1),
excelController.ConstructCell(currentRecord.Currency, CellValues.String, 2),
excelController.ConstructCell(currentRecord.DateProcessed.ToString("MMM dd, yyyy"), CellValues.String, 2),
excelController.ConstructCell(description, CellValues.String, 2)
);
sheetData1.Append(rowInSheet1);
lastRow.Append(
excelController.ConstructCell("TOTALS", CellValues.String, 6),
excelController.ConstructCell(" ", CellValues.String, 2),
excelController.ConstructCell(currentRecord.TotalFeeAmount.ToString("N2"), CellValues.String, 6),
excelController.ConstructCell(currentRecord.Currency, CellValues.String, 6),
excelController.ConstructCell(" ", CellValues.String, 2),
excelController.ConstructCell(" ", CellValues.String, 2)
);
sheetData1.Append(lastRow);
}
else
{
if ((previousCurrency == null && previousRegionName == null) || (previousCurrency == currentCurrency && previousRegionName == regionName))
{
rowInSheet1.Append(
excelController.ConstructCell(regionName, CellValues.String, 2),
excelController.ConstructCell(merchant, CellValues.String, 2),
excelController.ConstructCell(currentRecord.TotalFee.ToString("N2"), CellValues.String, 1),
excelController.ConstructCell(currentRecord.Currency, CellValues.String, 2),
excelController.ConstructCell(currentRecord.DateProcessed.ToString("MMM dd, yyyy"), CellValues.String, 2),
excelController.ConstructCell(description, CellValues.String, 2)
);
sheetData1.Append(rowInSheet1);
previousCurrency = currentCurrency;
previousTotal = currentTotal;
previousRegionName = regionName;
}
else
{
rowInSheet1.Append(
excelController.ConstructCell("TOTALS", CellValues.String, 6),
excelController.ConstructCell(" ", CellValues.String, 2),
excelController.ConstructCell(previousTotal.ToString("N2"), CellValues.String, 6),
excelController.ConstructCell(previousCurrency, CellValues.String, 6),
excelController.ConstructCell(" ", CellValues.String, 2),
excelController.ConstructCell(" ", CellValues.String, 2)
);
sheetData1.Append(rowInSheet1);
Row emptyRow;
emptyRow = new Row();
emptyRow.Append(
excelController.ConstructCell(" ", CellValues.String, 2),
excelController.ConstructCell(" ", CellValues.String, 2),
excelController.ConstructCell(" ", CellValues.String, 2),
excelController.ConstructCell(" ", CellValues.String, 2),
excelController.ConstructCell(" ", CellValues.String, 2),
excelController.ConstructCell(" ", CellValues.String, 2)
);
sheetData1.Append(emptyRow);
Row row;
row = new Row();
row.Append(
excelController.ConstructCell(regionName, CellValues.String, 2),
excelController.ConstructCell(merchant, CellValues.String, 2),
excelController.ConstructCell(currentRecord.TotalFee.ToString("N2"), CellValues.String, 1),
excelController.ConstructCell(currentRecord.Currency, CellValues.String, 2),
excelController.ConstructCell(currentRecord.DateProcessed.ToString("MMM dd, yyyy"), CellValues.String, 2),
excelController.ConstructCell(description, CellValues.String, 2)
);
sheetData1.Append(row);
previousCurrency = currentCurrency;
previousTotal = currentTotal;
previousRegionName = regionName;
}
recordCounter++;
}
}
}
Columns columns = excelController.AutoSize(sheetData1);
worksheet1.AppendChild(columns);
worksheet1.AppendChild(sheetData1);
worksheetPart1.Worksheet = worksheet1;
Sheet sheet1 = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart1),
SheetId = sheetNumber,
Name = previousRegionName
};
sheets.Append(sheet1);
}
}
我已经尽可能多地尝试调试,但似乎无法弄清楚问题出在哪里。
任何帮助,将不胜感激。
解决方案
问题在于sheetNumber
它必须递增,否则 Excel 认为所有工作表都具有相同的工作表编号。这是通过sheetNumber++
在附加每张纸后添加来解决的。
推荐阅读
- python - 如何根据条件为 df 的列赋值?
- java - Spring 异常处理无法使用 ExceptionHandler 类
- javascript - 为什么 useReducer 状态没有成功渲染 API 请求的新数据?
- visual-studio-code - vscode Python中只需要1个运行选项
- java - Java 中的 SnakeYAML\Jackson 对象列表
- amazon-web-services - 如何根据其他变量有效统计对 API 和 Lambda 函数的请求?
- python - Numpy 1-dim 数组与 2-dim 数组,其中一个维度的长度为 1
- linux - sudo ./pg_ctl -D /Library/PostgreSQL/9.5/data start,错误:库未加载:@loader_path/../lib/libpq.5.dylib
- python - 如何在 Python 中调用列表中的字符串以传递给函数
- java - 使用 Google Vision 进行文本识别后处理