Exception occurs reading spreadsheet after saving ExcelPackage (EPPlus)

Question

I am manipulating data in a .xlsx spreadsheet using EPPlus (4.0.4) and saving the file, immediately afterwards I am loading parts of the data into datatables.

After I call the .Save() method the spreadsheet is saved but then I get a 'External table is not in the expected format.' exception when trying to fill a DataAdapter.

I've created a console app to minimise the amount of code required to demonstrate the issue, here is the code:

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EPPlusSaveIssue
{
    class Program
    {
        static void Main(string[] args)
        {
            string pathToFile = "C:\\Users\\Peter\\Documents\\test.xlsx";
            FileInfo newFile = new FileInfo(pathToFile);
            using (var pck = new ExcelPackage(newFile))
            {
                pck.Save();
            }
            string connectionStringFormat = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";
            using (OleDbConnection con = new OleDbConnection(string.Format(connectionStringFormat, pathToFile)))
            {
                using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$] ", con))
                {
                    DataTable dataTable = new DataTable();
                    dataAdapter.Fill(dataTable);
                    Console.WriteLine(dataTable.Rows.Count.ToString());
                }
            }

        }
    }
}

Steps to reproduce:

Run the application against a newly created spreadsheet, the exception occurs
Comment out the pck.Save(); line and the exception still occurs (the spreadsheet is in some way mangled at this point)
Open the spreadsheet in Excel and save it
Run the application again (with the pck.Save() still commented out) and it runs
Put the pck.Save(); line back in and the exception occurs.

So, there's a workaround which is impractical, i.e. do the manipulation save the file using EPPlus then save it in Excel then run the process without saving it using EPPlus.

It seems the .Save() method is putting the file in an unusual state, I don't know what else to try, any advice would be greatly appreciated.

Han Han · Accepted Answer · 2015-06-30T19:00:23

You cannot call save() because your workbook doesn't have any worksheet bro. At least 1 sheet is required for a normal excel file.

Try to add var ws = pck.Workbook.Worksheets.Add("Sheet1"); before the save().

I used EPPlus long time ago. I don't know why the Document is removed but the dev team may put it back any time, below link.

http://epplus.codeplex.com/documentation

Exception occurs reading spreadsheet after saving ExcelPackage (EPPlus)

2 Answers