tags:

views:

2501

answers:

8

I'm trying to create Excel 2007 Documents programatically. Now, there are two ways I've found:

Currently, I use ExcelPackage, which has some really serious drawbacks and issues. As I do not need to create overly complex Excel sheets (the most "complicated" thing is that I explicitely need to set a cell type to numeric or text), I 'm looking towards Option 1 next, but I just wonder if there are any other good and supported ways to generate Excel 2007 Sheets? Bonus Points if they can be created without having to save them to the harddrive, i.e. generate and directly output them into a stream.

.net 3.0 is the target here, no 3.5 goodness :(

Edit: Thanks so far. The XML SDK is indeed 3.5 only, but Russian Roulet... erm... COM Interop is also something I want to avoid whenever possible, especially since Excel 2007 has a somewhat complicated but still rather easy to create document format. I'll have a look at the various links and hints posted.

+8  A: 

You could try using the Office Open XML SDK. This will allow you to create Excel files in memory using say a MemoryStream and much more easily than generating all the XML by hand.

As Brian Kim pointed out, version 2.0 of the SDK requires .NET 3.5 which you stated wasn't available. Version 1 of the SDK is also available which supports .NET 3.0. It isn't as complete, but will at least help you manage the XML parts of the document and the relationships between them.

Sarcastic
It seems like .net 3.5 is required for this.
Brian Kim
Good point, corrected.
Sarcastic
A: 

I used to generate Wordprocessing Markup documents using a library of functions for creating the correct XML for tables and paragraphs.

I then just changed the MIME type for the Response header to be a word document and sent the XML as the response to the client. It either opens in their browser or saves as a file.

There doesn't seem to be the same 2003 SDK for Excel although there is MS Open XML SDK

Dave Anderson
A: 

Excel is very scriptable through COM objects. I haven't tried it, but this should be a solid solution, though neither fast or lightweight.

Whatever you end up doing, check if your solution supports Unicode. I've run into some serious problems using existing solutions for writing xls files (that is, pre 2007 files).

COM automation of Excel on the server can be pretty flaky. Microsoft only support it on the desktop.
Sarcastic
A: 

You can use the Primary Interop Assemblies (the Office API) to programmatically generate Excel docs -- I wouldn't want to do anything overly complex using them, but it doesn't sound like that is your requirement.

This approach should work for generating the docs in memory as well.

Here are some resources:

Guy Starbuck
AVOID PIA... Yes, it's not a coincidence between Pain In the .ss and Primary Interop Ass-emblies. Trust me, I can still feel the hurt.
Andrei Rinea
A: 

Every code-snob alive hates vbscript, but it's simple and it works:

Set myxlapp = CreateObject("Excel.Application")
Set mywb = myxlapp.Workbooks.Add
myxlapp.Visible = true

Put that in a text file, append a ".vbs" at the end and presto.

BKimmel
+1  A: 

I was going to comment on BKimmel's post, but apparently I don't have enough SO cool points yet.

If Excel is already open, you can get that annoying "personal.xls is already open" message. I try getting the app first. If that fails, then create it:

On Error Resume Next
Set myxlApp  = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
  Err.Clear
  Set myxlApp = CreateObject("Excel.Application")
  If Err.Number <> 0 Then
    ' return False, show message, etc.
  End If
End If
Nick Hebb
+2  A: 

I'm just generating HTML table, which can be opened by Excel:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN"
        "http://www.w3.org/TR/html4/strict.dtd"&gt;

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Wnioski</title>
<style type="text/css">
<!--
br { mso-data-placement: same-cell; }
td { mso-number-format: \@; mso-displayed-decimal-separator: "."; }
-->
</style>

<table>
<tr style="height:15.0pt">
<td style="mso-number-format: general">474</td>
<td>474</td>

<tr>
<td>data2</td>
<td>data3</td>

<tr>
<td style="mso-number-format: &quot;yyyy-mm-dd&quot;">2008-10-01</td>
<td style="mso-number-format: standard">0.00</td>

<tr>
<td style="mso-number-format: general">line1<br>line2<br>line3</td>
<td></td>

</table>

This works well also as a data source for serial letters in Word, works with OpenOffice calc etc. And it is dead simple to generate.

Tometzky
looks like you might be missing a few </tr> tags in that example...;-)
Mike Woodhouse
</tr> is not required. Paste this code to http://validator.w3.org/#validate_by_input so see for yourself. </td> is also not required but Excel works better with them.
Tometzky
+1  A: 

I did most of the Open XML (xlsx) work in SpreadsheetGear for .NET and it is far from a trivial exercise. I recommend using the Office Open XML SDK or another 3rd party library like SpreadsheetGear or one of our competitors (just Google "SpreadsheetGear" to see who our competitors are).

SpreadsheetGear for .NET would seem to meet your needs:

  • Works with .NET 2.0, .NET 3.0 and .NET 3.5.
  • Supports writing to a stream with IWorkbook.SaveToStream or writing to a byte array with IWorkbook.SaveToMemory.
  • Supports Excel 97-2003 (xls) workbooks which is likely to be the next thing you are asked for if you need to support very many users.

You can download a free, fully-functional evaluation here.

Joe Erickson