"External table is not in the expected format"

Question

I try to fill DataSet with Excel data (via OpenXML library to get sheet name), but some times i get error: "External table is not in the expected format".

So, i use the same file= *.xlsx (i converted it from *.xls to *.xlsx via Excel 2010).

Yesterday it works fine, but now- it do not work:

    public DataTable CreateTable(string sheetName)
    {
        sheetName = sheetName + "$";
        bool hasHeaders = false;
        string HDR = hasHeaders ? "Yes" : "No";
        string strConn;
        if (_filePath.Substring(_filePath.LastIndexOf('.')).ToLower() == ".xlsx")
             strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                + _filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
      //      strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _filePath + ";Extended Properties=Excel 12.0;";
        else
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";

        try
        {
            OleDbConnection conn = new OleDbConnection(strConn);


            System.Data.DataSet dtSet;
            System.Data.OleDb.OleDbDataAdapter oleCommand;
            oleCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "]", conn);
            oleCommand.TableMappings.Add("Table", sheetName);
            dtSet = new System.Data.DataSet();

            oleCommand.Fill(dtSet);
            oleCommand.Dispose();
            conn.Close();
            return dtSet.Tables[0];
        }
        catch (Exception ex)
        {
           //log here
        }

        throw new NullReferenceException();

    }

I get sheet name from Excel file ,adds $ to it and try Fill it in data set.

But at line:

oleCommand.Fill(dtSet); it throw exception.

But some times it go to next lines. I try to re-copy this file from other source, but it now works.

Please, tell me how to fix me!

P.S. may be when i open this file by OpenXML i corrupt it?

OpenXml part (class ExcelHelper):

    public ExcelHelper(String filePath, bool isEditable)
    {
        _filePath = filePath;
        _isEditable = isEditable;
    }

public List<String> GetSheetNameColl()
    {
        if (_spreadSheetDoc == null)
            throw new NullReferenceException("_spreadSheetDoc is null!");

        List<String> sheetNameColl=new List<string>();

       int sheetIndex = 0;
       WorkbookPart workbookPart = _spreadSheetDoc.WorkbookPart;
       foreach (WorksheetPart worksheetpart in _spreadSheetDoc.WorkbookPart.WorksheetParts)
       {
           Worksheet worksheet = worksheetpart.Worksheet;

           // Grab the sheet logFileName each time through your loop
           string sheetName = workbookPart.Workbook.Descendants<Sheet>().ElementAt(sheetIndex).Name;
           sheetNameColl.Add(sheetName);
           Console.WriteLine(sheetName+" "+sheetIndex);

           sheetIndex++;
       }
        return sheetNameColl;
    }

public SpreadsheetDocument Open()
    {
        try
        {
            _spreadSheetDoc = SpreadsheetDocument.Open(_filePath, _isEditable);

            isLoaded = true;
            return _spreadSheetDoc;
        }
        catch (Exception ex)
        {
            //log here
        }
        throw new NullReferenceException("Error at Open() method");
    }

  public void Close()
    {
        if (_spreadSheetDoc != null)
        {
            _spreadSheetDoc.Close();
            isLoaded = false;
        }

    }

Program.cs:

 ExcelHelper excel = 
                new ExcelHelper(@"MyFile.xlsx", false);
            excel.Open();
            var sheetNameColl = excel.GetSheetNameColl();

            List<DataTable> dtColl = new List<DataTable>   (sheetNameColl.Count);

            foreach (var sheetName in sheetNameColl)
            {
                var table = excel.CreateTable(sheetName);
                DataTableHelper dtHelper = new DataTableHelper(table);
                table = dtHelper.RenameColumns();
                dtColl.Add(table);
            }
            excel.Close();

Answer 1

My solution for this:

Switch to EPPlus API - it is free, easier to use, and does not have this issue.

/// <summary>
/// Manipulate an excel file using the EPPlus API, free from http://epplus.codeplex.com/
/// License terms (Public) on http://epplus.codeplex.com/license
/// 
/// </summary>
public class ExcelWriter : IDisposable
{
    ExcelPackage _pck;

    /// <summary>
    /// Open a new or existing file for editing.
    /// </summary>
    /// <param name="fileName"></param>
    public ExcelWriter(string fileName)
    {
        _pck = new ExcelPackage(new FileInfo(fileName));
    }

    /// <summary>
    /// Open a new or existing file for editing, and add a new worksheet with the data
    /// </summary>
    /// <param name="fileName"></param>
    /// <param name="newWorksheetName">new sheet to add</param>
    /// <param name="dtData">data to add</param>
    public ExcelWriter(string fileName, string newWorksheetName, DataTable dtData):this(fileName)
    {
        var ws = this.AddWorksheet(newWorksheetName);
        this.SetValues(ws, dtData);
    }

    /// <summary>
    /// Add a new worksheet.  Names must be unique.
    /// </summary>
    /// <param name="name"></param>
    /// <returns></returns>
    public ExcelWorksheet AddWorksheet(string name)
    {
        return _pck.Workbook.Worksheets.Add(name);
    }

    /// <summary>
    /// Add a new picture.  Names must be unique.
    /// </summary>
    /// <param name="image"></param>
    /// <param name="imageName"></param>
    /// <param name="ws"></param>
    /// <param name="row"></param>
    /// <param name="col"></param>
    public ExcelPicture AddPicture(Image image, string imageName, ExcelWorksheet ws, int row, int col)
    {
        var pic = ws.Drawings.AddPicture(imageName, image);
        pic.SetPosition(row, 0, col, 0);
        pic.Border.LineStyle = eLineStyle.Solid;
        return pic;
    }

    /// <summary>
    /// Note: this will only perform as well as 'SetValues' if you load/write data from left to right, top to bottom.  Otherwise it may perform poorly.
    /// </summary>
    /// <param name="ws"></param>
    /// <param name="row"></param>
    /// <param name="col"></param>
    /// <param name="value"></param>
    public void SetValue(ExcelWorksheet ws, int row, int col, object value)
    {
        ws.Cells[row, col].Value = value;
    }

    /// <summary>
    /// Populate a large number of cells at once
    /// </summary>
    /// <param name="ws"></param>
    /// <param name="value">Data to load</param>
    public void SetValues(ExcelWorksheet ws, List<object[]> value)
    {
        ws.Cells.LoadFromArrays(value);
    }

    /// <summary>
    /// Populate a large number of cells at once
    /// </summary>
    /// <param name="ws"></param>
    /// <param name="value">Data to load</param>
    public void SetValues(ExcelWorksheet ws, DataTable dt)
    {
        ws.Cells.LoadFromDataTable(dt, true);
    }

    public void SetDefaultRowHeight(ExcelWorksheet ws, int rowHeight)
    {
        ws.DefaultRowHeight = rowHeight;
    }

    /// <summary>
    /// Saves to file and dispos
    /// </summary>
    public void Save()
    {
        _pck.Save();
    }

    /// <summary>
    /// Release all resources
    /// </summary>
    public void Dispose()
    {
        if (_pck != null)
            _pck.Dispose();
    }
}   // class