保存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.
                }
            }
        }

保存excelPackage时出错

在写入单元格之前,应将ExcelWorksheet添加到ExcelWorkbook中。看看他们的样品

在谷歌上搜索了错误消息,并看到了引发异常的ExcelPackage的一些源代码(您可以在这里看到)。如果修改透视表的源数据,则可能需要刷新/重新创建缓存源。检查ExcelWorkSheet对象中的数据透视表-在保存文件之前尝试调试其值