How to retrieve column names from a excel sheet?

Question

Using EPPlus I'm writing data to multiple sheets. If a sheet is not created I'm adding a sheet else I'm retrieving the used rows and adding data from that row and saving it

 FileInfo newFile = new FileInfo("Excel.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
  var ws = xlPackage.Workbook.Worksheets.FirstOrDefault(x => x.Name == language.Culture);
  if (ws == null)
  {
     worksheet = xlPackage.Workbook.Worksheets.Add(language.Culture);
     //writing data
  }
  else
  {
    worksheet = xlPackage.Workbook.Worksheets[language.Culture];
    colCount = worksheet.Dimension.End.Column;
    rowCount = worksheet.Dimension.End.Row;
    //write data
   }
   worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
   xlPackage.Save();

And it is working great.

Now I want to retrieve the column names of each sheet in the excel using LinqToExcel and this is my code

  string sheetName = language.Culture;
  var excelFile = new ExcelQueryFactory(excelPath);
  IQueryable<Row> excelSheetValues = from workingSheet in            excelFile.Worksheet(sheetName) select workingSheet;
  string[] headerRow = excelFile.GetColumnNames(sheetName).ToArray();

At header row it is throwing me an exception

An OleDbException exception was caught
External table is not in the expected format.

But I don't want to use Oledb and want to work with Linq To Excel.

Note: When I'm working with single sheet rather than multiple sheets it is working fine and retrieving all columns. Where am I going wrong.

Answer 1

(Based on OP's Comments)

The AutoFitColumn function has always been a little touchy. The important thing to remember is to call it AFTER you load the cell data.

But if you want a use a minimum width (when columns are very narrow and you want to use a minimum) I find EPP to be unreliable. It seems to always use DefualtColWidth of the worksheet even if you pass in a minimumWidth to one of the function overloads.

Here is how I get around it:

[TestMethod]
public void Autofit_Column_Range_Test()
{
    //http://stackoverflow.com/questions/31165959/how-to-retrieve-column-names-from-a-excel-sheet

    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.Add(new DataColumn("Nar", typeof(int))); //This would not be autofitted without the workaround since the default width of a new ws, usually 8.43
    datatable.Columns.Add(new DataColumn("Wide Column", typeof(int)));
    datatable.Columns.Add(new DataColumn("Really Wide Column", typeof(int)));

    for (var i = 0; i < 20; i++)
    {
        var row = datatable.NewRow();
        row[0] = i;
        row[1] = i * 10;
        row[2] = i * 100;
        datatable.Rows.Add(row);
    }

    var existingFile2 = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile2.Exists)
        existingFile2.Delete();

    using (var package = new ExcelPackage(existingFile2))
    {
        //Add the data
        var ws = package.Workbook.Worksheets.Add("Sheet1");
        ws.Cells.LoadFromDataTable(datatable, true);

        //Keep track of the original default of 8.43 (excel default unless the user has changed it in their local Excel install)
        var orginaldefault = ws.DefaultColWidth;
        ws.DefaultColWidth = 15;

        //Even if you pass in a miniumWidth as the first parameter like '.AutoFitColumns(15)' EPPlus usually ignores it and goes with DefaultColWidth
        ws.Cells[ws.Dimension.Address].AutoFitColumns();

        //Set it back to what it was so it respects the user's local setting
        ws.DefaultColWidth = orginaldefault;

        package.Save();
    }
}