Error saving excelPackage

Question

I am carrying a excel file with two WorkSheet, the first data to feed and the second WorkSheet containing a PivotTable. Insert data in the first WorkSheet and try to save throws exception.

Exception: InneException {"The cachesource is not a worksheet"} Message "Error saving file 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.
                }
            }
        }

Answer 1

Did a Google search for the error message, and saw some of the source code for ExcelPackage (you may see it here) where the exception is thrown. It seems that if you modify source data for a pivot table, you might need to refresh/recreate the cache source. Check for the PivotTable inside your ExcelWorkSheet object - try to debug its values previous to saving the file

Answer 2

You should add ExcelWorksheet to the ExcelWorkbook before writing to cells. Take a look at their samples