保存excelPackage时出错
本文关键字:出错 excelPackage 保存 | 更新日期: 2023-09-27 17:59:55
我带着一个excel文件,里面有两个工作表,第一个要输入的数据和第二个包含数据透视表的工作表。在第一个工作表中插入数据并尝试保存throws异常。
异常:InneException{"缓存源不是工作表"}消息"保存文件C:''Users''idias''Desktop''Modelo.xlsx时出错"
using (FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog())
{
if (folderBrowserDialog.ShowDialog() == DialogResult.OK)
{
FileInfo fileInfo = new FileInfo(@"C:'Users'idias'Desktop'teste'Modelo.xlsx");
if (!fileInfo.Exists)
throw new Exception("Arquivo Modelo não encontrado");
string filename = string.Format("{0}''{1}", folderBrowserDialog.SelectedPath, fileInfo.Name);
fileInfo.CopyTo(filename, true);
fileInfo = new FileInfo(filename);
using (ExcelPackage excelPackage = new ExcelPackage(fileInfo))
{
ExcelWorkbook excelWorkBook = excelPackage.Workbook;
DataTable dtPlanoConta = Negocio.Financeiro.Relatorio.RecuperarPlanoConta();
if (dtPlanoConta.Rows.Count > 0)
{
ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets[1];
//Add some items in the cells...
int row = 3;
foreach (DataRow dr in dtPlanoConta.Rows)
{
row++;
//Campos
excelWorksheet.SetValue(row, 1, dr["ID"]);
excelWorksheet.SetValue(row, 2, dr["FATHER_ID"]);
excelWorksheet.SetValue(row, 3, dr["DESCRICAO_FORMATADA"]);
}
row = 1;
for (int i = 0; i < dtPlanoConta.Rows.Count; i++)
{
row++;
//Campos
excelWorksheet.Cells[row, 1].Style.Numberformat.Format = "@";
excelWorksheet.Cells[row, 2].Style.Numberformat.Format = "@";
excelWorksheet.Cells[row, 3].Style.Numberformat.Format = "@";
}
excelWorksheet.Cells[excelWorksheet.Dimension.Address].AutoFitColumns();
}
DataTable dtDemonstrativo = Negocio.Financeiro.Relatorio.RecuperarDemonstrativo(1, 3, "2015");
if (dtDemonstrativo.Rows.Count > 0)
{
ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets[2];
//Add some items in the cells...
int row = 1;
foreach (DataRow dr in dtDemonstrativo.Rows)
{
row++;
//Campos
excelWorksheet.Cells[row, 1].Value = dr["ID"];
excelWorksheet.Cells[row, 2].Value = dr["OPERACAO"];
excelWorksheet.Cells[row, 3].Value = dr["MES_ANO"];
excelWorksheet.Cells[row, 4].Value = dr["VALOR_PLANEJADO"];
excelWorksheet.Cells[row, 5].Value = dr["VALOR_REALIZADO"];
excelWorksheet.Cells[row, 6].Value = dr["DIFERENCA_REAIS"];
excelWorksheet.Cells[row, 7].Value = dr["DIFERENCA_PERCENTUAL"];
}
row = 1;
for (int i = 0; i < dtDemonstrativo.Rows.Count; i++)
{
row++;
//Campos
excelWorksheet.Cells[row, 1].Style.Numberformat.Format = "@";
excelWorksheet.Cells[row, 2].Style.Numberformat.Format = "@";
excelWorksheet.Cells[row, 3].Style.Numberformat.Format = "DD/yyyy";
excelWorksheet.Cells[row, 3].Style.Numberformat.Format = "#,##0.000";
excelWorksheet.Cells[row, 4].Style.Numberformat.Format = "#,##0.000";
excelWorksheet.Cells[row, 5].Style.Numberformat.Format = "#,##0.000";
excelWorksheet.Cells[row, 6].Style.Numberformat.Format = "#,##0.000";
}
excelWorksheet.Cells[excelWorksheet.Dimension.Address].AutoFitColumns();
}
excelPackage.Save(); // This is the important part.
}
}
}
在写入单元格之前,应将ExcelWorksheet添加到ExcelWorkbook中。看看他们的样品
在谷歌上搜索了错误消息,并看到了引发异常的ExcelPackage的一些源代码(您可以在这里看到)。如果修改透视表的源数据,则可能需要刷新/重新创建缓存源。检查ExcelWorkSheet对象中的数据透视表-在保存文件之前尝试调试其值