31

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.

Cœur
  • 37,241
  • 25
  • 195
  • 267
eviljack
  • 3,696
  • 8
  • 39
  • 52
  • Which versions do you need to support? – Nader Shirazie Sep 02 '09 at 18:29
  • This depends on what features of MS Excel you need. Do you need any fancy feature? – Esteban Küber Sep 02 '09 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 Sep 02 '09 at 18:30
  • 1
    I think this has been asked before, just cant find it now. – Zaki Sep 02 '09 at 18:40
  • Look at the "Related" links on the right, especially http://stackoverflow.com/questions/236926/how-do-i-programatically-interface-an-excel-spreadsheet and http://stackoverflow.com/questions/153079/how-can-i-read-ms-office-files-in-a-server-without-installing-ms-office-and-witho – harpo Sep 02 '09 at 19:26
  • How are you planning to use the new files on a computer that doesn't have Excel installed? – Andy Mikula Sep 02 '09 at 22:00
  • @Andy Mikula - I'm guessing the files are for onward distribution. And anyway, you can open files with Excel Viewer. – Lunatik Sep 04 '09 at 15:39

11 Answers11

48

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

Gregoire
  • 24,219
  • 6
  • 46
  • 73
  • 24
    you're freaking kidding me. – eviljack Sep 02 '09 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 Sep 02 '09 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 Sep 02 '09 at 19:35
  • 1
    I use this all the time to export xls files from web apps. – recursive Sep 02 '09 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 Oct 22 '09 at 15:34
  • Thanks! Save my day!!! Googling "OOXML Excel" with "mso-number-format" can be formatted any cells like a html with css. – equiman Nov 20 '15 at 19:33
  • Keep in mind that since Excel 2007 extension hardening has been implemented by Microsoft which gives a warning message about content not matching the file extension. [2007 Extension Warning](https://learn.microsoft.com/en-us/archive/blogs/vsofficedeveloper/excel-2007-extension-warning-on-opening-excel-workbook-from-a-web-site-2) – Blurry Sterk Aug 12 '21 at 07:43
8

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)

David
  • 24,700
  • 8
  • 63
  • 83
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.

RHicke
  • 3,494
  • 3
  • 23
  • 23
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.

Ahmad Mageed
  • 94,561
  • 19
  • 163
  • 174
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

Crispy
  • 5,557
  • 3
  • 30
  • 35
  • 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 Sep 03 '09 at 01:31
1

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.

Daniel Brückner
  • 59,031
  • 16
  • 99
  • 143
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?

annakata
  • 74,572
  • 17
  • 113
  • 180
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.

Kyle Rosendo
  • 25,001
  • 7
  • 80
  • 118
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.

Community
  • 1
  • 1
John MacIntyre
  • 12,910
  • 13
  • 67
  • 106
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.

Josh
  • 590
  • 3
  • 4
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.

Sunil Singhal
  • 593
  • 3
  • 11