I need to create a program that can read from/write to an Excel file. Should I use OleDB or use an Excel Object? What are the advantages of one over another, and where can I read up to get started on implementing this?
A:
Possibly the features of the free component "CarlosAg" is enough for you? Needs no installed Excel on the machine. I use it and am happy with it.
ur
2009-11-09 17:19:16
Once upon a time I would have agreed - but that was prior to office 2007, now one should use the office 2007 formats and the Packaging APIs
Murph
2009-11-09 18:06:55
+4
A:
I had to do this recently and found NPOI (http://npoi.codeplex.com/) the best bet, especially as I needed to generate binary .xls files that didn't throw up any confusing errors for a client about filetypes.
See this blog post for more info. You'll also find that as it's a port of an existing framework there's plenty of help available for the original framework that is applicable to the port.
Graphain
2009-11-09 17:21:25
+2
A:
Excel Automation can be messy, and isn't going to help if your program is intended to run on a web server, but it will give you full access to read, modify, and create Excel files.
Using the OLEDB driver only works with Excel 1998-2003 files, not Excel 2007, has only a limited subset of functionality (treats sheets and named regions as "tables" that you can read and write to like a database--no control over formatting, formulas, etc.), and has some significant limitations, but will work just fine in an ASP.NET application.
(There is a separate driver you can download to access Excel 2007's OOXML files (xlsx) via OLEDB. I don't know what its capabilities or limitations are compared to the older driver.)
Another option, if the application is running in ASP.NET, is to get a third-party library for reading and writing Excel files. Some are open-source (generally those that only read/write Excel XML files from Excel 2002-2007, NPOI being one notable exception), some are quite expensive.
About the only thing you don't want to do is try to implement your own Excel BIFF-format (standard 1997-2003 file format) reader/writer. The file format has evolved over time and is too complicated for mere mortals.
richardtallent
2009-11-09 17:22:05
+1
A:
Hello,
For writing, there is an easy workaround you might consider, makes your life easier.... Write everything into a comma separated text file and save it as csv. Then launch Excel through a shell command with your csv as the argument. Voilla, instant export to Excel.
Regards
BeMeCollective
2009-11-09 17:23:08
Very creative, but not particularly elegant and very limiting in terms of what you can write to excel, doesn't address reading from excel at all... so I like the thinking but not the answer (-:
Murph
2009-11-09 18:09:58
You could also create HTML documents that contain just a table and start Excel with it... more formating options, but in my experience problems with decimal separators (at least here in Austria, where we use ,)
ammoQ
2009-11-12 08:48:08
+2
A:
SpreadsheetGear for .NET is an Excel compatible spreadsheet component which can read and write Excel workbooks (xls and xlsx) among other things and has no dependency on Excel, OleDb or anything other than the .NET libraries.
You can see some live samples here and download the free trial here.
Disclaimer: I own SpreadsheetGear LLC
Joe Erickson
2009-11-09 20:22:18