PlanetSquires Forums

Please login or register.

Login with username, password and session length
Advanced search  
Pages: [1] 2 3

Author Topic: Creating Excel WorkBooks without Automation  (Read 6861 times)

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Creating Excel WorkBooks without Automation
« on: October 15, 2012, 10:50:42 PM »

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





« Last Edit: July 19, 2017, 09:39:10 PM by TechSupport »
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Wilko Verweij

  • FireFly3 Registered User
  • Junior FireFly Member
  • *
  • Posts: 148
  • PB10 - FF3.7 User; Win 10
    • CHEAQS
Re: Creating Excel WorkBooks without Automation
« Reply #1 on: October 16, 2012, 05:55:09 AM »

Sounds exciting. I certainly will try this!
Wilko
Logged

Robert Eaton

  • FireFly3 Registered User
  • Junior FireFly Member
  • *
  • Posts: 125
Re: Creating Excel WorkBooks without Automation
« Reply #2 on: October 16, 2012, 08:28:57 AM »

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

David Warner

  • FireFly3 Registered User
  • Junior FireFly Member
  • *
  • Posts: 110
Re: Creating Excel WorkBooks without Automation
« Reply #3 on: October 16, 2012, 09:52:31 AM »

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

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Re: Creating Excel WorkBooks without Automation
« Reply #4 on: October 16, 2012, 10:02:08 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
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Re: Creating Excel WorkBooks without Automation
« Reply #5 on: October 17, 2012, 12:23:19 PM »

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.
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Re: Creating Excel WorkBooks without Automation
« Reply #6 on: October 18, 2012, 06:11:46 PM »

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:
Code: [Select]
#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:
Code: [Select]
<?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.
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Re: Creating Excel WorkBooks without Automation
« Reply #7 on: October 18, 2012, 10:32:52 PM »

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
« Last Edit: March 09, 2013, 11:17:19 AM by TechSupport »
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Rudolf Furstauer

  • FireFly3 Registered User
  • Junior FireFly Member
  • *
  • Posts: 109
Re: Creating Excel WorkBooks without Automation
« Reply #8 on: October 19, 2012, 03:21:14 AM »

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
Logged

Pat Dooley

  • FireFly3 Registered User
  • Junior FireFly Member
  • *
  • Posts: 91
Re: Creating Excel WorkBooks without Automation
« Reply #9 on: October 19, 2012, 04:18:08 AM »

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

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Re: Creating Excel WorkBooks without Automation
« Reply #10 on: October 19, 2012, 09:58:48 AM »

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).
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Jim Dunn

  • FireFly3 Registered User
  • Junior FireFly Member
  • *
  • Posts: 109
  • Jim Dunn
Re: Creating Excel WorkBooks without Automation
« Reply #11 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 José's "includes" so I can scan that source...

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

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Re: Creating Excel WorkBooks without Automation
« Reply #12 on: October 21, 2012, 01:04:38 AM »

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 José'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.
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Re: Creating Excel WorkBooks without Automation
« Reply #13 on: October 21, 2012, 01:12:51 AM »

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)
« Last Edit: March 09, 2013, 11:18:38 AM by TechSupport »
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Haakon Birkeland

  • FireFly3 Registered User
  • Super FireFly Member
  • *
  • Posts: 681
  • XP Pro x64 SP2, (Win 7 Pro x64), Win 8 Pro x64
Re: Creating Excel WorkBooks without Automation
« Reply #14 on: November 04, 2012, 10:41:46 AM »

This will be a useful class Paul!
Logged
Haakon 8o)
Pages: [1] 2 3