Excel Reader .xls files + Reporting Services

Nov 22, 2011 at 1:11 PM

Hi

I have a report rendered from Reporting Services 2005 in Excel format.

I am able to stream the report but when try to get the data in a dataset it fails.Exception Thrown. Unable to cast object of type 'Excel.Core.BinaryFormat.XlsBiffRecord' to type 'Excel.Core.BinaryFormat.XlsBiffDbCell'.

If i however open the excel report and save it, then run my code, everything works fine. i've tried the recommended version v2 as well as the latest but to no avail.

 FileStream inStream = File.OpenRead(Path.Combine(path, "Report.xls"));

 

 //1. Reading from a binary Excel file ('97-2003 format; *.xls) 

 IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(inStream);

 

 //3. DataSet - Create column names from first row 

 DataSet result = excelReader.AsDataSet(); //Exception Thrown here

 

Regards,

Avesh

Feb 13, 2012 at 8:52 AM

ahahah..i think you should change another report rendered from Reporting Services in your excel format.

try this: http://www.keepautomation.com/products/net_barcode_reporting_service/

i used this one for month, it works fine.

Feb 13, 2012 at 9:08 AM

Is there any other way of solving this issue without splurging out cash

Jan 14, 2013 at 9:46 AM

Based on what I read here (http://exceldatareader.codeplex.com/workitem/10688) you can fix it by replacing this in the ExcelBinaryReader class:

XlsBiffDbCell startCell = (XlsBiffDbCell)m_stream.ReadAt(startOffset);

With this:

// Some malformed .xls files can have BIFFRECORDTYPE
// entries before the first XlsBiffDbCell, so it's
// important to loop over these untill we actually
// find the first XlsBiffDbCell.
XlsBiffRecord record = m_stream.ReadAt(startOffset);
while (!(record is XlsBiffDbCell))
{
    startOffset += record.Size;
    record = m_stream.ReadAt(startOffset);
}

This hasn't fixed all my SSRS-related problems however. The value of my cells is still garbled, and the types of cells are wrong.

Jan 14, 2013 at 10:17 AM

I think this is probably why there are issues with openoffice generated files as well. I'll look into it further. Are you able to attach an example SSRS file for testing?

Jan 14, 2013 at 3:22 PM

Hm, that might be a little tricky as this is company data and confidential. And I can't edit the data out, because that would cause me to save it in Excel and solve the problem... I'll see what I can do.

Jan 17, 2013 at 12:19 PM

I've checked in a change that should resolve this. Can you test it with your xls?

Jan 17, 2013 at 2:38 PM

Well, it no longer crashes, but as mentioned here, I'm still having some problems:

  • The headers of my tables are just Column1, Column2, etc, instead of the actual headers
  • The DataTable starts with a large amount of empty rows
  • The cells containg data like "\0MyConten" instead of "MyContent"

For us, this is no longer a priority, as the end user will always edit the file from SSRS and save it in Excel. So feel free to work on other features first, as you see fit. Thanks for the effort!

Jan 17, 2013 at 3:02 PM
Thanks for the update. I think I need to get hold of some sample SSRS reports from somewhere to test it properly.


On Thu, Jan 17, 2013 at 2:38 PM, petermorlion <notifications@codeplex.com> wrote:

From: petermorlion

Well, it no longer crashes, but as mentioned here, I'm still having some problems:

  • The headers of my tables are just Column1, Column2, etc, instead of the actual headers
  • The DataTable starts with a large amount of empty rows
  • The cells containg data like "\0MyConten" instead of "MyContent"

For us, this is no longer a priority, as the end user will always edit the file from SSRS and save it in Excel. So feel free to work on other features first, as you see fit. Thanks for the effort!

Read the full discussion online.

To add a post to this discussion, reply to this email (ExcelDataReader@discussions.codeplex.com)

To start a new discussion for this project, email ExcelDataReader@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com