33

I'm confused as hell with all the bazillion ways to read/write/create excel files. VSTO, OLEDB, etc, but they all seem to have the requirement that office must be installed.

Here is my situation: I need to develop an app which will take an excel file as input, do some calculations and create a new excel file which will basically be a modification of the first excel file. All with the constraint that the machine that runs this may not have office installed. (Don't ask why...)

I need to support all excel formats. The only saving grace is that the formats spreadsheets themselves are really simple. Just a bunch of columns and values, nothing fancy. And unfortunately no CSV as the end user might not even know what a CSV file is.

Improve this question
7
  • Which versions do you need to support?
    – Nader Shirazie
    Commented Sep 2, 2009 at 18:29
  • This depends on what features of MS Excel you need. Do you need any fancy feature?
    – Esteban Küber
    Commented Sep 2, 2009 at 18:30
  • 3
    How tolerant do you need to be of "lost features". Eg Do you need to maintain formulas, Pivot tables, charts, etc? Or is it just data in these files?
    – Nader Shirazie
    Commented Sep 2, 2009 at 18:30
  • 1
    I think this has been asked before, just cant find it now.
    – Zak
    Commented Sep 2, 2009 at 18:40
  • Look at the "Related" links on the right, especially stackoverflow.com/questions/236926/… and stackoverflow.com/questions/153079/…
    – harpo
    Commented Sep 2, 2009 at 19:26

11 Answers 11

Reset to default
50

write your excel in HTML table format:

<html>
<body>
  <table>
    <tr>
    <td style="background-color:#acc3ff">Cell1</td>
    <td style="font-weight:bold">Cell2</td>
    </tr>
  </table>
</body>
</html>

and give your file an xls extension. Excel will convert it automatically

Improve this answer
7
  • 25
    you're freaking kidding me.
    – eviljack
    Commented Sep 2, 2009 at 19:25
  • 3
    I've used this method several times, simple, works great. But in the case of web apps, just set the content type/mime type to excel (cant remember the exact mime type off the top of my head)
    – Neil N
    Commented Sep 2, 2009 at 19:33
  • 1
    And, depending on your version of excel, it usually picks up formatting as well. Bolded table cell? Excel carries it over.. Background color? copies that too...
    – Neil N
    Commented Sep 2, 2009 at 19:35
  • 1
    I use this all the time to export xls files from web apps.
    – recursive
    Commented Sep 2, 2009 at 19:37
  • 5
    That is the coolest thing i've seen this week. I've spent hours trying to find a nice way to export XLS from PHP
    – Neil Aitken
    Commented Oct 22, 2009 at 15:34
9

Without Office installed you'll need something designed to understand the Excel binary file format (unless you only want to open Office 2007 .xlsx files).

The best I've found (and that I use) is SpreadsheetGear, which in addition to being .NET native, is much faster and more stable then the COM/OLE solutions (which I've used in the past)

Improve this answer
6

read and write csv files instead. Excel reads them just fine and they're easier to use. If you need to work against .xls files then try having support for OpenOffice as well as Excel. OpenOffice can read and write excel files.

Improve this answer
0
2

Did you consider way number bazillion and one: using the Open XML SDK? You can retain styles and tweak it to your liking. Anything you can do in an actual file is possible to achieve programatically. The SDK comes with a tool called Document Reflector that shows the underlying XML and even shows LINQ statements that can be used to generate them. That is key to playing around with it, seeing how the changes are made, then recreating that in code.

The only caveat is this will work for the new XML based formats (*.xlsx) not the older versions. There's a slight learning curve but more material is making its way on blogs and other sites.

Improve this answer
2

If cost is not an issue, I'd suggest looking in Aspose's Excel product. I use their Word product and I've been satisfied.

Aspose.Cells

Improve this answer
1
  • 2
    After trying out some of the free alternatives, my team decided on using Aspose.Cells as well. It's very intuitive to use and the code is easy to maintain.
    – dawntrader
    Commented Sep 3, 2009 at 1:31
0

Excel files are in a proprietary format so (afaik) you're not going to be able to do this without having the office interop available. Some third party tools exist (which presumably licence the format from MS?) but I've not used them myself to comment on their usefulness.

I assume that you can't control the base file format, i.e. simple CSV or XML formats aren't going to be possible?

Improve this answer
0

I used to use a very nice library called CarlosAg, which uses Excel XML format. It was great (and Excel recognizes the format), and also incredibly fast. Check it out here.

Oh, as a side note, we used to use this for the very same reason you need it. The servers that generated these files were not able to have Excel installed.

Improve this answer
0

Excel XLSX files "just" XML files - more precisely ZIP files containing several XML files. Just rename a Excel file Test.xslx to Test.zip and open it with your favourit ZIP program. XML schemas are, afaik, standardized and availiable. But I think it might not be that easy to manipulate them only using primitive XML processiing tools and frameworks.

Improve this answer
0

If you cannot work with CSV files as per @RHicke's suggestion, and assuming you are working on a web app, since a desktop app would be guaranteed to have XL installed as per requirements.

I'd say, create your processing app as a webservice, and build an XL addin which will interact with your webservice directly from XL.

Improve this answer
0

For XLSX files, look at using http://www.codeplex.com/ExcelPackage. Otherwise, some paid 3rd party solutions are out there, like the one David suggested.

Improve this answer
0

I can understand the requirement of not having office installed on a server machine. There are many libraries like aspose being available, some of them requiring license though. If you are targeting MS Excel formats, then a native, Interoperability library, ACE OLEDB data provider, from Microsoft is available which you can install on a machine and start reading, writing programmatically. You need to define a connection string and commands as per you needs. (Ref: This article @yoursandmyideas)talks about using this library along with setup and troubleshooting information.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.