Support Forums > General Board

Creating Excel WorkBooks without Automation

<< < (2/9) > >>

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:
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: ---#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

--- End code ---

I have attached a screenshot of the resulting spreadsheet.

Here is what the resulting xml code looks like:

--- Code: ---<?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>

--- End code ---

Now I need to work on the numeric and date formatting.

Paul Squires:
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

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!

Navigation

[0] Message Index

[#] Next page

[*] Previous page

Go to full version