Codeplex Excel Data Reader give empty Data set for Excel 2010

Question

I’m using Codeplex Excel Data Reader to read an excel. The problem that I face is It reads Excel 97-2003 documents without any difficulty, but when reading Excel 207-2010 documents using ExcelReaderFactory.CreateOpenXmlReader(stream), it output’s an empty data set. Did anyone faced this problem. And is any one has any solution for this?

The read method is as follows

private DataSet ReadExcel(string fileName, string extention)

{

    DataSet dsData = null;
    FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
    IExcelDataReader excelReader = null;

    try
    {

        if (extention.Equals("xls"))
        {
            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
        }
        else
        {
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
           // excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
        }

        excelReader.IsFirstRowAsColumnNames = false;
        dsData = excelReader.AsDataSet();
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (excelReader != null)
        {
            excelReader.Close();
        }
    }
    return dsData;
}

It was last updated in Aug 25 2009 , why dont you just reference the `Microsoft.Office.Interop.Excel.Application` and read it that way? — Jeremy Thompson, Aug 09 '12 at 04:23
Initially I did that way, but I got following error "Message: Sys.WebForms.PageRequestManagerServerErrorException: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 8000401a.". It seems to fix this at least user needs to logged in to the server to initialize the excel service. But that is impossible, since server was located in a server farm and due to security issues client do not like to do that. — Thanushka, Aug 09 '12 at 04:26

score 1 · Accepted Answer · answered Aug 09 '12 at 04:34

8000401a indicates it was something to do with Run As Logon Failure.

Steer clear of server-side automation of office. Or use XML to work with Excel spreadsheets on the server.

According to the support issues with the Excel Data Reader:

Design and usage are great. So far only issue I've had is with certain XLSX file not parsing correctly (reading in wrong sheets, missind cell values, etc). To resolve these issues, I had to rebuild Excel.dll using latest SharpZipLib from http://www.icsharpcode.net/OpenSource/SharpZipLib/Download.aspx. As others have said, project needs an update, but is still good.

Or just use the standard micrsoft way:

Microsoft.Office.Interop.Excel.Application xlApp;
Workbook wb = null;
try
{
wb = xlApp.Workbooks.Open(filePath, false, true,5,null,"WrongPAssword");
}

foreach (object possibleSheet in wb.Sheets)
   {
   var aSheet = possibleSheet as Worksheet;
     if (aSheet != null)
     {
....

Codeplex Excel Data Reader give empty Data set for Excel 2010

1 Answers1