ansaurus

Question

Programatically creating Excel 2007 Sheets

Answer 1

+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 2008-09-30 14:25:43

It seems like .net 3.5 is required for this.

Brian Kim 2008-09-30 17:42:00

Good point, corrected.

Sarcastic 2008-09-30 21:08:12

Answer 2

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 2008-09-30 14:27:11

Answer 3

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).

2008-09-30 14:27:40

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

Sarcastic 2008-09-30 14:28:51

Answer 4

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:

Installing the Primary Interop Assemblies
How to create new Excel workbooks (from MSDN)

Guy Starbuck 2008-09-30 14:29:18

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 2008-09-30 22:23:32

Answer 5

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 2008-09-30 21:00:06

Answer 6

+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 2008-09-30 21:32:15

Answer 7

+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 2008-10-01 08:30:15

looks like you might be missing a few </tr> tags in that example...;-)

Mike Woodhouse 2008-10-01 08:43:27

</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 2008-10-02 06:42:01

Answer 8

+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 2009-01-28 07:26:52

ansaurus

tags:

views:

answers:

Programatically creating Excel 2007 Sheets

related questions