• Welcome to PlanetSquires Forums.
 

Creating Excel WorkBooks without Automation

Started by Paul Squires, October 15, 2012, 10:50:42 PM

Previous topic - Next topic

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





Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

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 )
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
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Paul Squires

I just used the class in a real world application of mine. Works very well however the method I am using for creating styles and applying them to cells is a little cumbersome in practice. I need to change that stuff in order to make it easier and more intuitive for the programmer to use. Will a need a few days on this one.
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Paul Squires

Quick update: The code is working great now. I am very happy with the internal workings and generated xml output. It mirrors the Microsoft generated xml pretty well.

Here is what the code looks like:

#Include "clsWorkBook.inc"

Function PBMain() As Long

   Local wb       As iExcelWorkBook
   Local ws       As iExcelWorkSheet
   Local iCell    As iExcelCell
   

   Let wb = Class "clsExcelWorkBook"

   wb.filename = "test.xml"
   wb.author   = "Paul Squires"
   wb.company  = "PlanetSquires"

   ' Set some default font info that will apply to the
   ' entire workbook. The defaults are:
   '   .FontName      = "Calibri"
   '   .FontSize      = 11
   '   .FontColor     = "#000000"   ' black
   ' Change our default font to Arial 10pt.
   wb.DefaultFontName = "Arial"
   wb.DefaultFontSize = 10


   ' Add a worksheet and call it "Sheet1"
   Let ws = wb.AddWorkSheet("Sheet1")

   iCell = ws.AddCell( 1, 2, "Right" )
   iCell.AlignRight = %TRUE

   iCell = ws.AddCell( 3, 2, "Yellow!" )
   iCell.FontSize  = 10
   iCell.CellColor = "#FFFF00"

   iCell = ws.AddCell( 5, 2, "Box" )
   iCell.BorderLeft   = %TRUE
   iCell.BorderTop    = %TRUE
   iCell.BorderRight  = %TRUE
   iCell.BorderBottom = %TRUE


   ' Merge/Span across 3 columns (4 total)
   iCell = ws.AddCell( 5, 5, "Span and Center" )
   iCell.MergeAcross = 3     ' cols 5,6,7,8
   iCell.AlignCenter = %TRUE         
   iCell.FontColor   = "#FF0000"   'red
   iCell.CellColor   = "#AFEEEE"   'blue

   
   ' Add 3 values and then use a SUM formula to total them
   iCell = ws.AddCell( 8, 1, 100 )
   iCell = ws.AddCell( 8, 2, 200 )
   iCell = ws.AddCell( 8, 3, 300 )
   iCell = ws.AddCell( 8, 4, 600 )
   iCell.Formula = "=SUM(RC[-3]:RC[-1])"

   ' Add 3 values and then use a + formula to total them
   iCell = ws.AddCell( 9, 1, 100 )
   iCell = ws.AddCell( 9, 2, 200 )
   iCell = ws.AddCell( 9, 3, 300 )
   iCell = ws.AddCell( 9, 4, 600 )
   iCell.Formula = "=RC[-3]+RC[-2]+RC[-1]"


   ' Add another worksheet...
   Let ws = wb.AddWorkSheet("Sheet2")

   ' Add another worksheet...
   Let ws = wb.AddWorkSheet("Sheet3")

   ' Write our workbook to disk
   wb.WriteXML

   ' Clean up after ourselves
   Set ws = Nothing
   Set wb = Nothing

   ? "Done."

End Function


I have attached a screenshot of the resulting spreadsheet.

Here is what the resulting xml code looks like:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Paul Squires</Author>
<LastAuthor>Paul Squires</LastAuthor>
<Created>2012-10-18T00:00:00Z</Created>
<LastSaved>2012-10-18T00:00:00Z</LastSaved>
<Company>PlanetSquires</Company>
<Version>14.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>7416</WindowHeight>
<WindowWidth>17220</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>48</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
  <Alignment ss:Vertical="Bottom"/>
  <Borders/>
  <Font ss:FontName="Arial" ss:Size="10" ss:Color="#000000"/>
  <Interior/>
  <NumberFormat/>
  <Protection/>
</Style>
<Style ss:ID="s1">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
</Style>
<Style ss:ID="s2">
  <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s3">
  <Borders>
   <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
   <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
   <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
  </Borders>
</Style>
<Style ss:ID="s4">
  <Font ss:FontName="Arial" ss:Size="10" ss:Color="#FF0000"/>
  <Interior ss:Color="#AFEEEE" ss:Pattern="Solid"/>
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
</Style>
</Styles>

<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="8" ss:ExpandedRowCount="9" x:FullColumns="1"
  x:FullRows="1" ss:DefaultRowHeight="14.55">
  <Row ss:AutoFitHeight="0">
    <Cell ss:Index="2" ss:StyleID="s1"><Data ss:Type="String">Right</Data></Cell>
  </Row>
  <Row ss:Index="3" ss:AutoFitHeight="0">
    <Cell ss:Index="2" ss:StyleID="s2"><Data ss:Type="String">Yellow!</Data></Cell>
  </Row>
  <Row ss:Index="5" ss:AutoFitHeight="0">
    <Cell ss:Index="2" ss:StyleID="s3"><Data ss:Type="String">Box</Data></Cell>
    <Cell ss:Index="5" ss:MergeAcross="3" ss:StyleID="s4"><Data ss:Type="String">Span and Center</Data></Cell>
  </Row>
  <Row ss:Index="8" ss:AutoFitHeight="0">
    <Cell><Data ss:Type="Number">100</Data></Cell>
    <Cell><Data ss:Type="Number">200</Data></Cell>
    <Cell><Data ss:Type="Number">300</Data></Cell>
    <Cell ss:Formula="=SUM(RC[-3]:RC[-1])"><Data ss:Type="Number">600</Data></Cell>
  </Row>
  <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="Number">100</Data></Cell>
    <Cell><Data ss:Type="Number">200</Data></Cell>
    <Cell><Data ss:Type="Number">300</Data></Cell>
    <Cell ss:Formula="=RC[-3]+RC[-2]+RC[-1]"><Data ss:Type="Number">600</Data></Cell>
  </Row>
</Table>
</Worksheet>

<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
  x:FullRows="1" ss:DefaultRowHeight="14.55">
</Table>
</Worksheet>

<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
  x:FullRows="1" ss:DefaultRowHeight="14.55">
</Table>
</Worksheet>

</Workbook>


Now I need to work on the numeric and date formatting.
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Paul Squires

#7
Okay guys, here it is. The first SLL for you to play with. I will be using it tomorrow to test Excel xml generation in one of my applications. If I encounter any issues with the classes or make any changes then I will upload the changes/updates here.

If you use the SLL then please let me know what you like or hate about it. Can only make it better through feedback from everyone.

Please do not share this SLL outside of this forum. I am not ready to unleash this stuff on the PowerBASIC masses yet.

Obviously you need PB 10+ to use this SLL.

Do not create multiple cells at the same row/col. The class does not check to see if a cell already exists at a given row/column. This is done for performance reasons.

EDIT: Download link in first post of this thread)

Thanks,

Paul
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Rudolf Furstauer

I like it already!
With your example with BIFF2 was the problem that
openoffice could not open this Format.

More and more people are using openoffice or libreoffice,
so I had to change the exportformat in some of my Programs from xls to csv.

The use of your class is very simple, I will definitely use it.

Thank you for your great work!

Rudolf Fürstauer

Pat Dooley

Very nice, Paul.  MLG or SQLite straight to Excel.  No tab-delimited in between.  What fun!

Paul Squires

I just finished using the classes in my application to output a perfectly formatted, formula enabled, Excel Workbook. Works perfectly and coding it was a breeze.

The only gotcha at this point is a couple of times I inadvertantly created more than one cell at the same row/col. That ended up causing a "can not load, corrupted file" message from Excel. I plan to build in a couple of bit-arrays into the class to determine if a row/col already exists thus preventing a second one from being added (may simply return the existing cell).
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Jim Dunn

Very nice... of course... the only drawback of .SLL is that we can't look-up the methods...

And, if we have UNICODE or not... will we hiccup?

You know me, a fan of Jose's "includes" so I can scan that source...

: )
3.14159265358979323846264338327950
"Ok, yes... I like pie... um, I meant, pi."

Paul Squires

Quote from: Jim Dunn on October 19, 2012, 02:23:49 PM
Very nice... of course... the only drawback of .SLL is that we can't look-up the methods...

And, if we have UNICODE or not... will we hiccup?

You know me, a fan of Jose's "includes" so I can scan that source...

: )
Hi Jim,

All of the methods/properties are shown in the xmlExcelTest.bas sample code. The code is built using ANSI methods.
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Paul Squires

#13
An update to the classes and SLL.

New properties added to the Workbook class to better customize print properties:

PageMarginHeader, Single
PageMarginFooter, Single
PageMarginTop, Single
PageMarginLeft, Single
PageMarginRight, Single
PageMarginBottom, Single
PageCenterVertical, Long     ' true/false
PageCenterHorizontal, Long   ' true/false
PageLandscape, Long     ' true/false
PagePortrait, Long      ' true/false
FitToPage, Long       ' true/false


The class will now check for the case where you try to create a duplicate cell at a row/column location. For performance reasons, only rows up to 20,000 and columns up to 100 are checked (via an efficient bit array). This should be sufficient for the majority of workbooks you create. No checking outside that range so if you inadvertently add a duplicate cell then you will get a "corrupted file" from Excel when loading.

Added a property called "Version" to the Workbook class so you can return the version of this library.

(EDIT: Download link in first post of this thread)
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Haakon Birkeland

Haakon 8o)