Menu
I'm trying to create Excel 2007 Documents programmatically. 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.
434
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.
72
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">
<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: "yyyy-mm-dd"">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.
33
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
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:
You can download a free, fully-functional evaluation here.
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
