Here's the problem:
I have to modify an existing Excel spreadsheet using .NET. The spreadsheet is hugely complex, and I just have to add data in some predefined areas.
I'm investigating my options, and Excel Automation/InterOp is out of the question, as I'm implementing an ASP.NET website, and Excel probably isn't installed on the server. From what I find online, InterOp is also a very expensive solution performancewise.
Creating a CSV file is also ruled out because of the complex nature of the original spreadsheet.
Currently I'm leaning towards an ADO.NET OleDb solution, but I find that mentioned very rarely (Google and Stackoverflow.com) so I'm kinda worried: What's the catch with OldDb for Excel? The only drawback I can find on MSDN so far, is that I can't create cells with formulas, but that's really not an issue in my case.
I've also considered SSIS, but that's only based on my assumption that you can use existing Excel files when you generate spreadsheets. I don't know if that possible or not.
Then there's OpenXml. It seems overly complicated compared to OldDb, plus it's still undetermined which of the older Excel versions I have to support.
Am I missing something? Are there more alternatives?
-
You have still few solutions. 1) Third party component for excel files. ( i think this is most pain-les & reliable solution ) 2) Using html-table.
1) Simply look at internet :) 2) The excel works pretty fine with html-tables. If you generate this html:
<html><body> <table> <tr><td><b>Column A</b></td><td><b>Column B</b></td></tr> <tr><td><font color="red">-154</b></td><td><font size="5">hello world</font></td></tr> </table> </body></html>the excel will show it as worksheet as you formated it. I think, this is most universal way, how to create "rich" excel worksheet in internet enviroment without having excel instaled on server.
-
Check out MyXls a .NET solution for reading and writing binary XLS files.
-
Have a look at
Excel Web Access, which is part of Excel Services in MOSS, a server-side version of Excel that lets you calculate and display your Excel information through a Web browser. The MOSS overview page describes the sharepoint framework that can provide Excel functionality on the server. You can use the VSTO API to programmatically access spreadsheets. -
I've used Aspose.Cells for this sort of thing with good success. The documentation can be a little obtuse, but the product seems to work. It won't require you to have Excel on the server and it works with the latest versions of Excel (and previous versions).
-
not sure if this is exactly what you need, but certainly could be: 4 Editing In Excel Tutorials (under .NET #5-8)
-
The problem with using Excel as an ISAM database using any driver (ODBC, OLEDB., etc.) is the datatype of the column is based on the first 8 rows in the column - this is pure evil; for example: if a column is something like a part# and the first 8 parts happen to be only numeric then you get get NULL for any cell in that column where the part# isn't numeric. (110000 vs 111000-1 or 111000A). This is reguardless of how you format the column in excel (even if you set the column as text).
I'll try and find a doc on this and update. Here's one:
-
You may want to take a look at http://www.gemboxsoftware.com/GBSpreadsheetFree.htm.
They have a free version with all features but limited to 150 rows per sheet and 5 sheets per workbook, if that falls within your needs, otherwise the paid version starts at $425 for a 1 Developer License.
There are additional answers that may apply to your question at StackOverflow: Create Excel (.XLS and .XLSX) file from C#.
-
In that situation, I would normally favour not messing with the complex spreadsheet, but setting up a separate data file, to which I would link the data cells in the complex spreadsheet. This is much cleaner and safer than changing a complex spreadsheet from outside.
If this is not easier than the alternatives you are currently facing,it would surprise me. But then I haven't seen your spreadsheet.
0 comments:
Post a Comment