Support Forums > General Board

Creating Excel WorkBooks without Automation

(1/9) > >>

Paul Squires:
EDIT:  2017-07-19, I have attached the source code to this post. I haven't touched the code in two years (at least) so I am unsure of its quality

EDIT:  The latest version of the library can always be downloaded from this link:    http://www.planetsquires.com/files/xmlExcel.zip

Hi Guys,

I have been busy lately writing an application for my work. A requirement is to output Excel compatible workbooks. As you may know, I wrote old school BIFF2 Excel files years and years ago. That file type is now considered a security risk by Excel so I can't use it.

I thought about using COM automation but is seems so slow and at times unreliable if an instance fails or goes screwy under Windows.

I looked at the current Excel Open XML format. Very powerful but somewhat complicated and requires the resulting files to be compressed (zip format) into an .xlsx file.

My research allowed me to stumble on the Excel 2003 XML format (SpreadsheetML). I can't believe that we as a PowerBasic community have not written code for this before. It is pretty easy to create the XML files and incredibly fast.

The reason for this post is to let you know that I have a class to handle all of this stuff just about complete. It will allow us to output our data into Excel ready files.... no more .CSV files!

Here is what the class does so far:

- Create Workbooks with as many Worksheets in them that you wish.
- Rows up to 1,000,000 and columns to 16,000 (that is the current Excel limits).
- Create default style for the entire Workbook but still allow individual cells to have their own styles.
- Styles include things like: Font names, font size, font color, bold, underline, italic, background cell colors, borders (left/right/top/bottom), text and number alignment (left/right/center).
- Cells can be spanned across as many columns as you wish. For example, you can span four columns and center the text.
- Formulas. That's right, embed formulas directly into your file. Stuff like =SUM(A1:A3) is easy.
- Fast!  Create 100,000 cells in 0.28 seconds  and then write them to the XML file in 2.56 seconds... and that is not even optimized fully yet.

I still have a few things left to write like allowing setting the printer and print preview settings directly in your file. I will be testing the class this week in my application and then I'll post it here for you guys. Maybe not the source code but at least an SLL.

I usually don't post much about the code I write but this code is pretty exciting because it solves a long standing issue that many of us face everyday when trying to get our reports into Excel.

Stay tuned...





Wilko Verweij:
Sounds exciting. I certainly will try this!
Wilko

Robert Eaton:
Wow. Sounds very cool! Would it handle images too? (Always need the company logo  :D )

David Warner:
Sounds interesting Paul (as your projects usually do), I'll look forward to testing it.

Paul Squires:

--- Quote from: Robert Eaton on October 16, 2012, 08:28:57 AM ---Wow. Sounds very cool! Would it handle images too? (Always need the company logo  :D )

--- End quote ---
Unfortunately not. I am pretty sure (not 100% though) that the specification can not handle binary data. I'd have to check into that. Would be cool though to be able to display logos and stuff.

BTW, here is the specification: http://msdn.microsoft.com/en-us/library/office/aa140066(v=office.10).aspx

Navigation

[0] Message Index

[#] Next page

Go to full version