Getting null value against .xlsx file

May 28, 2010 at 10:59 PM
I am having problem when I use a .xlsx file, it seems like cannot read field from excel sheet and getting null value even if there is value in the spreadsheet. My code snippet: HttpPostedFile postedFile = file_Selector.PostedFile; System.IO.Stream filestrem = postedFile.InputStream; IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(filestrem); excelReader.IsFirstRowAsColumnNames = true; result = excelReader.AsDataSet(); DataTableCollection dts = result.Tables; foreach (DataTable dt in dts){ foreach (DataRow row in dt.Rows){ foreach (DataColumn col in dt.Columns){ string value = row[col].ToString(); // some times get null value, even if there is value in the spreadsheet field } } } Does any one has any idea what is wrong with .xlsx file? I will appretiate if any one can help me in here. Thank you in advance for your help.
Aug 10, 2010 at 10:07 PM
I believe I have the same issue. I am reading a .xlsx file, which I have verified is correctly in the excelReader from the CreateOpenXmlReader method. However, when I try to use the excelReader.AsDataSet() method, there are no tables to access. Sample code is below: //Get an Excel file from a SharePoint List - I have verified this works excelStream = properties.ListItem.File.OpenBinaryStream(); IExcelDataReader excelReader = null; // Reading from a OpenXml Excel file (2007 format; *.xlsx) excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelStream); // Convert the excelReader object to a dataset and save it as "result" excelReader.IsFirstRowAsColumnNames = true; DataSet modelDS = excelReader.AsDataSet(); DataTable summaryTable = modelDS.Tables[0]; ABOVE CODE RESULTS IN: "Exception: Cannot find table 0." Any help is greatly appreciated!
Developer
Sep 12, 2010 at 10:33 AM

If you download the latest source and look through the patches you should be able to fix this. I am working on a new release as we speak incorporating the submitted patches...

Sep 16, 2010 at 10:08 PM
Edited Sep 17, 2010 at 4:21 PM

Can you provide any more info on how to patch this myself?  I have the latest 55029 but I'm not sure what I need to look for to allow .xlsx files to work.  I'm having the same problem as the guys above...  Tnx in advance!

It turns out my issue was related to the ExcelReaderFactory method I was using.  The below switches in my code did the trick for me and .xlsx files.  Hope this helps somebody some time.

        IExcelDataReader excelReader = null;
        DataSet ds = new DataSet();
        if (myFile.FileName.ToLower().Contains(".xlsx"))
        {
            Stream excelStream = myFile.InputStream;
            excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelStream);
            excelReader.IsFirstRowAsColumnNames = true;
            excelReader.IsFirstRowAsColumnNames = true;  
            ds = excelReader.AsDataSet();
        }
        else
        {
            //regular .xls files
            excelReader = ExcelReaderFactory.CreateBinaryReader(myFile.InputStream);
            excelReader.IsFirstRowAsColumnNames = true;
            ds = excelReader.AsDataSet();
        }

 

Oct 14, 2010 at 3:53 AM
robalexclark wrote:

If you download the latest source and look through the patches you should be able to fix this. I am working on a new release as we speak incorporating the submitted patches...

 robalexclark  -  thank you for the updates to source control.  Would it be possible for you to post new DLLs based on rev 55029?  I am still in the process of upgrading to VS2010 and cannot open the solution file from the latest source code download.  Thanks!

Mar 17, 2014 at 11:19 AM
I found the same issue, and it was solved for me by changing the Page's Culture and UICulture to 'en-US'; I was using a different culture (bn-BD), that seems to be cause of the issue.