Sign in to follow this  
Grain

Read / Write XLS library? Preferably .net

Recommended Posts

Does anyone know of a free library to read and write XLS files? That I can use from .net languages? I was using SmartXLS http://www.smartxls.com/ but I didn't realize it was only a trial and now I have a hand full of apps that are going to break on Jan 5.

Share this post


Link to post
Share on other sites
Hi, you can handle excel files directly with the .net framework without anything extra using the excel file as a OleDB data source. Here's some references:

http://www.dotnetspider.com/resources/15076-Import-Excel-Sheet-Data-To-Data-Grid-View.aspx
http://codehill.com/2009/01/reading-excel-2003-and-2007-files-using-oledb/

Share this post


Link to post
Share on other sites
Quote:
Original post by Axiverse
Hi, you can handle excel files directly with the .net framework without anything extra using the excel file as a OleDB data source. Here's some references:

http://www.dotnetspider.com/resources/15076-Import-Excel-Sheet-Data-To-Data-Grid-View.aspx
http://codehill.com/2009/01/reading-excel-2003-and-2007-files-using-oledb/


There is one problem with this approach. Microsoft has no intentions on porting the Jet drivers to 64-bit, thus it will not work on any 64-bit version of windows, unless you explicitly compile your application with the CPU target set to x86.

Share this post


Link to post
Share on other sites
Hmm... Okay I got that sort of working... I'm using the code in that second link.

However on some of my excel files with multiple sheets there are some sheets that are titled like "whateverSheetName$Printarea" or "whateverSheetName$PrintTitle" but these are not visible from within excel, and when trying the program itterats over them and tries to load them I throws an exception saying the sheet is not found.

So for example I have a excel file which is a log of printed barcodes. This log has been kept for several years and each sheet is a year. So in the file I have sheets : "2005", "2006", "2007", "2008", "2009". But when the program opens the XLS and reads the sheet names....

workSheetNames = new String[dataSet.Rows.Count];
int i = 0;
foreach (DataRow row in dataSet.Rows)
{
//insert the sheet's name in the current element of the array
//and remove the $ sign at the end
workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { '$' });
i++;
}




workSheetNames is filled up like this:
"2005"
"2005$Printarea"
"2006"
"2006$Printarea"
"2007"
"2007$Printarea"
"2008"
"2009"

And when I run the GetWorksheet function it throws an exception when trying to retrieve those sheets that say $printarea or $printtitle. on the line : cmd.Fill(excelDataSet);

What to do? Obviously I could put some check in place and ignore those strings, but I really want to understand where those are coming from and is there a way of handling them with out ignoring them outright.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this