return null dataset, open xls file only have 1 sheet

Jan 18, 2013 at 2:49 AM

There is a bug occur  if open xls file with 1 sheet,

but if there more then one sheet,

it is working normally.

 

Regard,

Rudi_Nugroho

Developer
Jan 18, 2013 at 6:29 AM

I'll take a look now, if you are able to share the exact Excel sheet that is causing this issue it would help a lot.

Developer
Jan 18, 2013 at 6:43 AM

Having checked this it all seems to work, it might be a problem with your file in which case I'd really appreciate it if you could share it. If it is confidential, you can email me direct and I'll create a new test file with junk data after we figure the problem out.

Also, it could be that you are opening the file with the wrong method, for example, opening a binary file (xls) with the open XML reader. Check the following code out;

 

        [TestMethod]
        public void Issue_xxxx_single_sheet_in_workbook()
        {
            // Use the appropriate method here to open the file.
            IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader("path_to_your_file.xlsx");
            //IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader("path_to_your_file.xls");

            Assert.AreEqual(1, reader.ResultsCount);

            DataSet dataset = reader.AsDataSet();

            reader.Close();

            Assert.IsTrue(dataset != null);
            Assert.AreEqual(1, dataset.Tables.Count);
            Assert.AreEqual(11, dataset.Tables["Sheet1"].Rows.Count);
            Assert.AreEqual(4, dataset.Tables["Sheet1"].Columns.Count);
        }

Jan 22, 2013 at 5:16 PM

I have a workbook with the same problem:

    private void Test()
    {
      using (FileStream streamIn = File.Open(@"Z:\SIW\Pass1 Data\dms_vendor.extract.xlsx", FileMode.Open, FileAccess.Read))
      using (IExcelDataReader execlReader = ExcelReaderFactory.CreateOpenXmlReader(streamIn))
      {
        int r = execlReader.ResultsCount;

        int x = r;

        DataSet dataset = execlReader.AsDataSet();

        foreach (DataTable sheet in dataset.Tables)
        {
          var a = sheet;
          var b = a;
        }
      }
    }

When I break at the foreach and view the dataset it says the Tables count is 0 although the xlsx file has 260 lines and columns out to AC.

Developer
Jan 22, 2013 at 6:11 PM

Any chance of a copy of that spreadsheet so I can see it too? You can email me using r at ryanoneill dot com .

Developer
Jan 23, 2013 at 10:42 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.