Generate Excel File (without Excel.exe)

[John Bailo]

I want to write an Excel file (.xls format) from some database data.

I don't want to use Excel.exe because of all the automation and security
issues.

Does Microsoft document the .xls file format anywhere?

Is there any c# class that will let me do this?

 

[Mark Rae]

Does Microsoft document the .xls file format anywhere?

Not as far as I know...

Is there any c# class that will let me do this?

http://www.aspose.com/Products/Aspose.Cells/Default.aspx

 

[Willy Denoyette [MVP]]

I want to write an Excel file (.xls format) from some database data.

I don't want to use Excel.exe because of all the automation and security issues.

Does Microsoft document the .xls file format anywhere?

Is there any c# class that will let me do this?

See: http://support.microsoft.com/default.aspx/kb/257757 for third party support products.
Office 2007 uses the Office Open XML format for all Office files, V3 of the framework offers
support for these.
Check these for detailed info on OOXML:
http://openxmldeveloper.com/
http://blogs.msdn.com/brian_jones/archive/2006/10/09/Office-Open-XML-final-draft_210021002100_.aspx

Willy.

 

[Rad [Visual C# MVP]]

I want to write an Excel file (.xls format) from some database data.

I don't want to use Excel.exe because of all the automation and security
issues.

Does Microsoft document the .xls file format anywhere?

Is there any c# class that will let me do this?

I don't think documenting the format would be of much use, because
'recreating excel is just not worth the bother. You do though have a couple
of options:

- You could use the well documented XML Excel format, if your clients have
a version of Excel capable of opening the same i.e Excel 2003 or Excel
2007. You code would thus spit out Excel XML

- Even easier, You could use a third party library to generate the files.
Aspose make a pretty fully functional library for this. The benefit of this
approach is that the document is the 'plain old' excel format and can be
opened by older excel

 

[John Bailo]

- Even easier, You could use a third party library to generate the files.

The whole point of .NET is to put this kind of control in the developers
hands.

If the answer is always, "buy this component" then what's the point.

..NET is then yet another baby language like VB6

 

[gregarican]

The whole point of .NET is to put this kind of control in the developers
hands.

If the answer is always, "buy this component" then what's the point.

.NET is then yet another baby language like VB6

I would have to disagree. To compare .NET (C# in the case of this
newsgroup) to VB6 is ludicrous. To generate an Excel file
programmatically the host PC running the code would typically have
Excel installed and then it could involve a series of OLE calls. Excel
wouldn't even have to be visible. Otherwise you could create an Excel
XML file, in which case it wouldn't even require Excel to be installed
on the host PC running the code. I can do either even using lesser
known third party languages such as Python, Ruby, Smalltalk, etc.
What's the big deal? Once you get the XML structure down pat for
writing an Excel XML output file it's really not too bad.

 

[Willy Denoyette [MVP]]

The whole point of .NET is to put this kind of control in the developers hands.

If the answer is always, "buy this component" then what's the point.

.NET is then yet another baby language like VB6

You don't have to buy anything, you can develop your own solution , but if you think this is
going to be cheaper than buying a third party component, you are in for a big surprise.

Willy.

 

[Rad [Visual C# MVP]]

The whole point of .NET is to put this kind of control in the developers
hands.

If the answer is always, "buy this component" then what's the point.

.NET is then yet another baby language like VB6

Trying to write code that can read and write the binary excel format is not
a trivial task by any means!

 

[John Bailo]

Trying to write code that can read and write the binary excel format is not
a trivial task by any means!

It would help if Microsoft made the specification clearly available and
offered some starter code in a MSDN article.

 

[Willy Denoyette [MVP]]

It would help if Microsoft made the specification clearly available and offered some
starter code in a MSDN article.

As I said in another tread, this specification is available for Office 2007, older version
of office are not publically available, you have to sign up as an ISV to get access to the
internals.
Note that having the file specifications doesn't buy you anything, you have to write a hell
of a lot of code in order to correctly format and fill a single cell in a xls sheet.

Willy.

 

[Guest]

You can create an XLS file using Jet Provider 4.0 and use the same provider
to populate the workbook, which will be recognised by the Excel.Application
object. So, the Jet Provider must exist on your PC.

1. However, an XLS file, a workbook CANNOT exist without at least one (macro
or chart) sheet or worksheet; the Provider does NOT let you drop worksheets.

2. You can use the Provider to populate ranges within a worksheet or whole
worksheets but you would need to know the shape (rows & columns) and map that
to Excel ranges i.e R1C1 .... in practice, not so easy.

3. If Excel is unavailable, then you cannot use automation (easier with such
methods as CopyFromRecordSet) to populate the workbook.

I cannot remember much about the detail but if you need to follow this
through, I an easily lookup my notes and write back. Also, please be aware
that my solution is in VB and use ADO rather than ADO.NET, which may or may
not be portable into C#; I do not know the intricacies of C# well enough to
be sure but the folks here do.

It might help if you detailed your requirements for this XLS file that you
want to create.

For instance, if your requirement is fairly static, you might start with a
workbook with the required number of sheets and simply populate copies of it
at runtime. etc.

I'll look up my solution, in case you need it.

 

[John Bailo]

I want to write an Excel file (.xls format) from some database data.

I don't want to use Excel.exe because of all the automation and security
issues.

Does Microsoft document the .xls file format anywhere?

Is there any c# class that will let me do this?

Well, it looks like OleDb coupled with SQL Insert statements will let me
add data to an xls file quite nicely.

And, while I can't create the spreadsheet, if I use an existing one, it
will preserve formatting when I add data.

I'm looking at the XML options as well.

 

[Guest]

I omitted to mention some more options:

1. The Provider does not let you format the content of your workbook; if you
are not interested in this, a simpler option is to write your stuff to a CSV
file which Excel can open and save as an XLS.

2. You can use the OWC (Office Web Component) Spreadsheet component to
populate your worksheets and save them as XLS, which Excel can open. OWC11
(for Office 2003) is available as a free download but 1. requires EULA for
distribution. 2. its size (rows and columns) is bigger than Excel 2003 can
accommodate, you you will need to take care not to exceed them. I haven't
got Office 2007 and do not know whether a version of OWC exists for it.

 

[Guest]

I translated the VB stuff into JavaScript; open NotePad, copy what follows
and paste into the NotePad session and save it under any name but with an
extension JS.

0 /*AA: Create an XLS file without Excel.Application */
1 var ADO = WScript.CreateObject('ADODB.Connection');
2 /* Note \\ */
3 ADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\zXLSCreateJSx.XLS;Jet OLEDB:Engine Type=23;Extended
Properties=Excel 8.0");
4 ADO.Execute("CREATE TABLE [APL DATA] (fldText Char,fldDate Date,fldDouble
Double,fldCurrency Currency,fldBoolean Bit,fldMemo LongChar)");
5 ADO.Execute("INSERT INTO [APL DATA] VALUES('Ajay','01/01/2004',
32.45,90,1,'Long Text')");
6 ADO.Close;
7 ADO=null;

NOTES:
1. You need to remove the line numbering in the first column; I've added
them so avoid confusion with line wraps in the newsgroup window.

2. Engine Type and Excel 8.0 refers to a version of Excel; this is fine for
2003.

3. Once you have saved the JS file, locate it within Windows Explorer and
double click on it. If you have not disabled WScript, the file
C:\zXLSCreateJSx.XLS should appear; you should also open it with Excel and
confirm for yourself that Excel can open it.

4. Line 4.0 is necessary; otherwise you may end uo with an XLS that Excel
cannot open. Obviously, the worksheet is not required BUT you may add
specific amd relevant information to it and just leave it in.

I'd like to know how you got on!

 

[John Bailo]

Thanks!

I found the MSDN support article and was able to implement.

What's cool is that it will preserve existing formatting for the cells,
so I can create a base .xls, copy it, and add data for that particular
run of the data.

 

[Guest]

You mentioned security earlier on; I believe you can store the base xls in a
resource file buried in your EXE and keep it away from prying eyes too!

 

[John Bailo]

Security...not so much for my application, but as far as using
Automation there are issues in security which is why MS warns against
doing it in code. The Excel server is designed to be used by a person.

 

[Rad [Visual C# MVP]]

The whole point of .NET is to put this kind of control in the developers
hands.

If the answer is always, "buy this component" then what's the point.

.NET is then yet another baby language like VB6

I guess one has to decide on a number of factors. If you need the
functionality and don't have the time, energy or skill to create it, buying
is a perfectly acceptable way out, wouldn't you think?

If fact carrying your argument still further, the .NET framework is an
abstraction of many things in itself...

 

[PS]

The whole point of .NET is to put this kind of control in the developers
hands.

If the answer is always, "buy this component" then what's the point.

.NET is then yet another baby language like VB6

You have to weigh your time and how much it is worth against buying a
component. In another post I told you about a TMS Software component which
is 125 Euros and comes with the source code. Seems like a winner to me. Let
us know how many hours you end up spending to "do it yourself" to save $200.

PS