Geek Answers Handbook

Modifying an Excel spreadsheet using .NET

Here's the problem:

I need to modify an existing Excel spreadsheet using .NET. The spreadsheet is extremely complex and I just need to add data to some predefined areas.

I am researching my options and Excel Automation / InterOp is out of the question since I am implementing an ASP.NET website and Excel is probably not installed on the server. From what I find on the Internet, InterOp is also a very expensive performance solution.

CSV file creation is also excluded due to the complex nature of the original spreadsheet.

I am currently leaning towards the ADO.NET OleDb solution, but I find this to be mentioned very rarely (Google and Stackoverflow.com), so I'm a little worried: what's the trick with OldDb for Excel? The only drawback I can find on MSDN so far is that I cannot create cells with formulas, but this is really not a problem in my case.

I also looked at SSIS, but this is only based on my assumption that you can use existing Excel files when creating spreadsheets. I do not know if this is possible or not.

Then there is OpenXml. This seems overly complicated compared to OldDb, plus it has not yet determined which of the older versions of Excel I should support.

Am I missing something? Are there any more alternatives?

+2
excel
source share
8 answers

In this situation, I usually prefer not to interfere in a complex spreadsheet, but to configure a separate data file to which I would bind data cells in a complex spreadsheet. This is much cleaner and safer than modifying a complex spreadsheet from the outside.

If this is not easier than the alternatives you are currently facing, it would surprise me. But then I did not see your table.

+1
source share

Give Up MyXls .NET Solution for Reading and Writing XLS Binary Files.

+3
source share

I used Aspose.Cells for these kinds of things with good success. The documentation may be a little dumb, but the product seems to work. It does not require Excel on the server and works with the latest versions of Excel (and previous versions).

+1
source share

The problem of using Excel as an ISAM database using any driver (ODBC, OLEDB., Etc.) is the column data type based on the first 8 rows of the column - this is pure evil; for example: if a column is something like part #, and the first 8 parts are only numeric, then you get NULL for any cell in this column where part # is not numeric. (110,000 vs 111000-1 or 111000 A). It doesn’t depend on how you format the column in excel (even if you set the column as text).

I will try to find the document and update it. Here is one:

http://support.microsoft.com/kb/194124

http://msdn.microsoft.com/en-us/library/bb177610.aspx

+1
source share

You still have few solutions. 1) a third-party component for excel files. (I think this is the most painful and reliable solution) 2) Using an html table.

1) Just take a look at the internet :) 2) Works great with html tables. If you create 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>

excel will show it as a sheet when you created it. I think this is the most universal way to create an excel rich worksheet in an online environment without using excel on the server.

0
source share

Check out Excel Web Access , which is part of Excel Services at MOSS, the server-side version of Excel that allows you to compute and display Excel information through a web browser. The MOSS Overview page describes the sharepoint structure that can provide Excel functionality on the server. You can use the VSTO API to programmatically access spreadsheets.

0
source share

Not sure if this is exactly what you need, but it certainly can be: 4 Editing in Excel tutorials (in .NET # 5-8)

0
source share

You can take a look at http://www.gemboxsoftware.com/GBSpreadsheetFree.htm .

They have a free version with all the features, but it is limited to 150 lines per sheet and 5 sheets per book, if that applies to your needs, otherwise the paid version starts at $ 425 for 1 developer license.

There are additional answers that may be applicable to your question in https://stackoverflow.com/a/312960/ .

0
source share

More articles:


All Articles