Problem with excels from Reporting Services

Oct 25, 2010 at 4:26 PM

Hi,

I have a problem with a very particular case:

I am working with Reporting Services. When I export a report to Excel and try to read it with ExcelBinaryReader.Read(), the method throws the following exception:

Error Message:

System.InvalidCastException: Unable to cast object of type 'Excel.Core.BinaryFormat.XlsBiffRecord' to type 'Excel.Core.BinaryFormat.XlsBiffDbCell'.

Stack Trace:

Excel.ExcelBinaryReader.findFirstDataCellOffset(Int32 startOffset) : line 104
Excel.ExcelBinaryReader.initializeSheetRead() : line 499
Excel.ExcelBinaryReader.Read() : line 667

If I open the excel with Microsoft Excel and save it without making any changes then ExcelBinaryReader.Read() works.

Any help or idea is welcome.

Thanks!

Oct 27, 2010 at 10:49 PM
Edited Oct 27, 2010 at 10:49 PM

I am experiencing a similar problem as well.  I have a file from a vendor; when I try to import the file using the AsDataSet() method, I get the exact same exception.  If I manually open the .xls file, save it (making no changes) and then re-execute my code, there are no problems.  Odd...

Developer
Nov 5, 2010 at 12:19 AM
Edited Nov 5, 2010 at 12:20 AM

Dear Roche, Dear Mills,

Can you provide a small sample file? Send me at negrijp at gmail . com

Thanks.

JP Negri

Developer

Nov 5, 2010 at 9:01 AM

I have sent you a sample file.

Thank you!

Nov 24, 2010 at 9:46 AM

Did you get the mail?

Jan 20, 2011 at 5:50 PM

I am having the exact same problem.  Do you have any thoughts on a solution?

 

Apart from that - its a great tool.  Thanks

Apr 20, 2011 at 8:37 PM

Compiling the application with the most recent source did not fix this problem.  Does anyone have any other thoughts.  I took a look at the source and the solution is beyond me.

 

Great tool though.  Just wish this would work.

Oct 19, 2012 at 2:34 PM

Did anyone find a solution to this? I'm having the same problem. I'm using the example code, and opening an Excel spreadsheet output from SQL Server 2008 R2 Reporting Services and get the above error message on excelReader.AsDataSet().

Oct 22, 2012 at 10:21 AM

any updates on this ?

Jan 14, 2013 at 8:27 AM

I've been looking into this and can't seem to find a solution. It occurs when using an Excel that came from SQL Server Reporting Services. This is an Excel in the binary format (97-2003 worksheet). However, when you open the file, and save it (in the same format, 97-2003 worksheet), it will be read correctly. I've also noticed the saved file will be smaller in size than the original file. This is after just opening it and saving it immediately. No edits whatsoever.

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 (for example cells with dates are regarded as cells with numbers).