PlanetSquires Forums

Support Forums => General Board => Topic started by: Paul Squires on October 15, 2012, 10:50:42 PM

Title: Creating Excel WorkBooks without Automation
Post by: Paul Squires 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 (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...





Title: Re: Creating Excel WorkBooks without Automation
Post by: Wilko Verweij on October 16, 2012, 05:55:09 AM
Sounds exciting. I certainly will try this!
Wilko
Title: Re: Creating Excel WorkBooks without Automation
Post by: 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 )
Title: Re: Creating Excel WorkBooks without Automation
Post by: David Warner on October 16, 2012, 09:52:31 AM
Sounds interesting Paul (as your projects usually do), I'll look forward to testing it.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires 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
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires 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.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires 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.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires 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
Title: Re: Creating Excel WorkBooks without Automation
Post by: Rudolf Furstauer 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
Title: Re: Creating Excel WorkBooks without Automation
Post by: Pat Dooley on October 19, 2012, 04:18:08 AM
Very nice, Paul.  MLG or SQLite straight to Excel.  No tab-delimited in between.  What fun!
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires 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).
Title: Re: Creating Excel WorkBooks without Automation
Post by: 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...

: )
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires 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 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.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires 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)
Title: Re: Creating Excel WorkBooks without Automation
Post by: Haakon Birkeland on November 04, 2012, 10:41:46 AM
This will be a useful class Paul!
Title: Re: Creating Excel WorkBooks without Automation
Post by: Klaas Holland on March 16, 2013, 08:03:21 AM
This is a very useful tool Paul.

Just two questions:
a. How can one change the default RowHeight e.g with Fontsize > 16?
b. How can one save the file to a different folder e.g. on the server?

Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires on March 18, 2013, 02:59:36 PM
Hi Klaas,

I have updated the xmlExcel file in the link found in the first post of this thread.

You can now specify a default row height for each spreadsheet in a workbook (ws.DefaultRowHeight).

If you want to save the file to a different folder then all you need to do is add path information to the the filename that you specify in wb.filename

Hope that helps!

Title: Re: Creating Excel WorkBooks without Automation
Post by: Klaas Holland on March 19, 2013, 12:52:09 PM
Thanks Paul,

This works fine for me.

However I wonder why the Rowheight does not expand automatically when one chooses a large Fontsize?
I think this is the normal procedure in Excel.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Nathan Durland on September 04, 2013, 11:25:59 AM
Paul -- if you are still tinkering with this project, a nice addition would be the ability to  set the "Wrap Text" property on a cell.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires on September 04, 2013, 07:46:43 PM
Hi Nathan, the reference that I used is located here:  http://msdn.microsoft.com/en-us/library/office/aa140066(v=office.10).aspx
It is the XML Spreadsheet Reference.

I have added the WrapText attribute. I have updated the download in the link in the first post of this thread. Try the new package to see if it works for you.

:)
Title: Re: Creating Excel WorkBooks without Automation
Post by: John Montenigro on September 06, 2013, 06:08:15 PM
An update to the classes and SLL.

. . .

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.

Hi Paul, thanks for this capability... I'm going to try writing that dump-a-ListView-to-XML function that someone else wrote about. Should be a healthy way of getting back into PB/FF coding...

Question about the row and column numbers you mentioned. Are they hard limits?

One project I'm working on has datasets with 300 columns. Is that going to be a problem?

Also, if I'm not mistaken, this is a write-only technique; the .SLL object is not able to read an .XML file?

Thanks,
-John
Title: Re: Creating Excel WorkBooks without Automation
Post by: Wilko Verweij on September 06, 2013, 06:14:37 PM
Quote
the .SLL object is not able to read an .XML file?

That's right, John. The idea is that your (or my) program can create xml-files to enable users (or yourself) to read them with Excel.

Wilko
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires on September 06, 2013, 06:25:11 PM
There are no hard limits on rows or columns. You are only limited by whatever the limits are for Excel itself. The SLL only does checks on a certain range of rows and columns for performance reasons (it attempts to detect duplicate defined cells). If you add cells outside that range then it is your responsibility to ensure that it is not a duplicate cell that was previously defined. Duplicate cells will cause a corrupted xml spreadsheet file that Excel will not load.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Petrus Vorster on September 07, 2013, 03:47:45 PM
This tool had saved me eons of work in my last project.
I am SO thankful for it!
Title: Re: Creating Excel WorkBooks without Automation
Post by: Nathan Durland on October 04, 2013, 02:06:42 PM
I have added the WrapText attribute. I have updated the download in the link in the first post of this thread. Try the new package to see if it works for you.

Thanks very much!  This tool is a very cool addition to our arsenal.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Jean-pierre Leroy on January 16, 2014, 01:43:21 PM
Hi Paul,

Today I used your "xmlExcel.sll" to replace old .csv extraction I had.

Your classes are very flexible; thanks a lot.

I have small issue with the WrapText attribute; with your sample code, I can see in the xml file a style with the WrapText attribute.
Code: [Select]
<Style ss:ID="s3">
  <Font ss:FontName="Calibri" ss:Size="10" ss:Color="#000000"/>
  <Alignment ss:WrapText="1"/>
 </Style>

In my source code, I put this code after adding a cell value :

Code: [Select]
iCell.WrapText  = %TRUE
But in the generated xml file there is no reference to a WrapText attribute.

Any ideas ?

Thanks
Jean-Pierre
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires on January 16, 2014, 07:01:10 PM
Can you post the generated xml for the cell that you set the WrapText = %TRUE. For example, in the sample program included with the SLL, the generated xml is:
Code: [Select]
  <Row ss:AutoFitHeight="0">
    <Cell ss:Index="2" ss:StyleID="s3"><Data ss:Type="String">This is a very long string of text that should wrap.</Data></Cell>

What is it for the data you are using in your program?

That cell generates xml code that refers back to StyleID "s3". That style is defined as:\
Code: [Select]
<Style ss:ID="s3">
  <Font ss:FontName="Calibri" ss:Size="10" ss:Color="#000000"/>
  <Alignment ss:WrapText="1"/>
 </Style>
Title: Re: Creating Excel WorkBooks without Automation
Post by: Jean-pierre Leroy on January 17, 2014, 12:28:26 PM
Quote
Can you post the generated xml for the cell that you set the WrapText = %TRUE.

Hi Paul,

You will find enclosed the generated xml and the source code of a test program in which I generated the first line of the file (the header).

As you will see in the generated xml file, there is no reference to WrapText in the style. In my source code I put the attribute iCell.WrapText  = %TRUE for three cells.

I hope that you can find something. Thanks for your support.
Jean-Pierre
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires on January 17, 2014, 01:38:43 PM
Thanks Jean-Pierre, looks like I was missing an IF/THEN test in my code when comparing the WrapText attribute of a cell to previously defined styles. I fixed the code and now it is generating the new style (in your sample case, you should be getting a "s3" style generated). Hopefully the new generated code will produced the desired results in your spreadsheet.

Please let me know if the problem is fixed or if I need to do more on it. If it is okay then I will update the general download zip file.

(see attached zip file for the new test SLL).
Title: Re: Creating Excel WorkBooks without Automation
Post by: Jean-pierre Leroy on January 17, 2014, 06:20:41 PM
Thanks Paul,

Now it works as expected; The new style "s3" with WrapText="1" is correctly generated in the xml file.

Thanks for the quick fix.
Jean-Pierre
Title: Re: Creating Excel WorkBooks without Automation
Post by: Nathan Durland on March 25, 2014, 12:07:28 PM
I've come across a difficulty that as far as I can tell, looks like I'm bumping up against something in the SLL, although I'm probably doing something dumb.  I'm attempting to create a pretty wide spreadsheet (211 columns in all), but get a crash not too far in.  Here's relevant code; if the bits between the #IF 0/#ENDIF are added, it runs OK, otherwise it crashes after 50 iterations of the xLoop.  The SLL file I'm using is dated 1/17/2014.

Code: [Select]

  local xLoop   as long
  local xlRow, xlCol   as long
  local FirstDate   as string

  For xLoop = 104 To 1 Step -1

#IF 0
    If xLoop > 55 Then
      xlRow += 2
      xlCol = 3
    End If

    If xLoop > 5 Then
      xlRow += 2
      xlCol = 3
    End If
#endif

    FF_StatusBar_SetText HWND_MAINFORM_STATUSBAR, 1, "XLoop: " + Format$(xLoop) + " Row: " + Format$(xlRow) + " Col: " + Format$(XLCol)
    FF_DoEvents
    Sleep 10
 
    xlCol += 1
    iCell = ws.AddCell( xlRow, xlCol, FirstDate)
    iCell.AlignCenter = %True
    iCell.MergeAcross = 1
    iCell.CellColor = fnMakeExcelColor(%RGB_KHAKI)
   
    iCell = ws.AddCell( xlRow+1, xlCol, "Orders")
    iCell.AlignRight = %True
    iCell.CellColor = fnMakeExcelColor(%RGB_KHAKI)

    xlCol += 1
    iCell = ws.AddCell( xlRow+1, xlCol, "Qty")
    iCell.AlignRight = %True
    iCell.CellColor = fnMakeExcelColor(%RGB_KHAKI)
   
    FirstDate = SQLDateMath(FirstDate, 7)   
 
  Next xLoop

Any insight appreciated.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires on March 25, 2014, 02:47:15 PM
I am having a hard time following why you are using a STEP -1 loop and then using that value to determine how to set the row value. If you comment out the "Orders" and "Qty" lines then it will compile and run with no problems. That would lead me to believe that the problem would be the xlRow + 1 being used. Maybe the loop counting backwards and then adding to the rows is causing some type of overflow error or something. Not sure at this point.

The library is designed to build the spreadsheet from low row/col numbers to high. Any reason why you are using a backwards stepping loop with incrementing rows? Could the logic be reversed?
Title: Re: Creating Excel WorkBooks without Automation
Post by: Nathan Durland on March 25, 2014, 03:09:31 PM
I am having a hard time following why you are using a STEP -1 loop and then using that value to determine how to set the row value. If you comment out the "Orders" and "Qty" lines then it will compile and run with no problems. That would lead me to believe that the problem would be the xlRow + 1 being used. Maybe the loop counting backwards and then adding to the rows is causing some type of overflow error or something. Not sure at this point.

The library is designed to build the spreadsheet from low row/col numbers to high. Any reason why you are using a backwards stepping loop with incrementing rows? Could the logic be reversed?

The backward loop is because the objective is a rolling 2 year weekly data dump (i.e. the previous 104 weeks).  I used the negative step planning on adding some other code once I have this sorted out.  The variable 'FirstDate' comes in seeded with the oldest date, then as the loop progresses gets closer and closer to today. The loop variable (xLoop) value is not used to calculate the rows or columns; that's what xlRow and xlCol are for.   Each of those starts with a small value and are then incremented as needed.  I've been tinkering, as it occurred to me also that the problem was with writing a cell will the (xlRow+1).  I changed the code to the following (including losing the negative step loop).  This code will go successfully through the first loop, then fail on the first pass of the second.  It fails on the ws.AddCell call.

Code: [Select]
  xlCol = 4
  LOGIT "--> row " + Format$(xlRow)
  For xLoop = 1 to 104

    FF_StatusBar_SetText HWND_MAINFORM_STATUSBAR, 1, "XLoop: " + Format$(xLoop) + " Row: " + Format$(xlRow) + " Col: " + Format$(XLCol)
    FF_DoEvents
    Sleep 10
    If gUserCancel Then GoTo ExitFunc

    iCell = ws.AddCell( xlRow, xlCol, FirstDate)
    iCell.AlignCenter = %True
    iCell.MergeAcross = 1
    xlCol += 2

    FirstDate = SQLDateMath(FirstDate, 7)   
  Next xLoop
 
  xlRow += 1
  LOGIT "--> row " + Format$(xlRow)
 
  '//-  We really created 208 columns in the first loop
  '     So, now we do 208; even numbered are for orders, odd ar for quantity for that week
  For xLoop = 1 To 208 

    xlCol = 3 + xLoop
    tLong1 = (xlCol Mod 2)
    tStr1 = IIF$(tLong1 = 0, "Ords", "Qty")
    If gUserCancel Then GoTo ExitFunc
    LOGIT "XLoop: " + Format$(xLoop) + " Row: " + Format$(xlRow) + " Col: " + Format$(xlCol) + " " + tStr1
    LOGIT "--> add cell"
    iCell = ws.AddCell( xlRow, xlCol, tStr1)
 
  Next xLoop
Title: Re: Creating Excel WorkBooks without Automation
Post by: Wilko Verweij on December 07, 2014, 08:19:04 AM
As John noticed some time ago, this class is for writing XML-files. But there is a GetCell option. I tried to use that to read data from an .XML-file but could not get it working. Does anybody have a simple example? Or is it just impossible?

Thanks for help
Wilko
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires on December 07, 2014, 09:57:36 AM
The GetCell option is just for retrieving data that you have already created in memory prior to writing the file to disk.
GetCell does not read from an existing xml file. Sorry.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Wilko Verweij on December 07, 2014, 01:26:51 PM
Thank for your quick reply. It's a pity, but the option to create XML-files was very useful in other projects already, so thanks.
Wilko
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires on May 19, 2015, 06:53:13 PM
Original post in thread has been updated with a new download to address a memory load issue. Internally, the class destructor for the spreadsheet class was not ERASE'ing an array allocated that held the cell data. Simply setting the Spreadsheet class to NOTHING was not enough. The array held references to the cells as well.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Jean-pierre Leroy on May 20, 2015, 08:29:54 AM
Thank you Paul for this update I use your SLL every day in my projects.

Regards,
JEan-Pierre
Title: Re: Creating Excel WorkBooks without Automation
Post by: Petrus Vorster on May 22, 2015, 04:44:44 PM
I use this tool extensively but haven't used the function to draw borders between cells.
This function :  iCell = ws.AddCell( 5, 2, "Box" )
   iCell.BorderLeft   = %TRUE
   iCell.BorderTop    = %TRUE
   iCell.BorderRight  = %TRUE
   iCell.BorderBottom = %TRUE
I assume it draws a border around a cell? I haven't tried this yet.
Can the Sll help me change line thickness and color of borders as well?
It would make my reports look SO MUCH better!!!

Finally, i want the Excel sheet have CUSTOM FILTER on the page so the user can only select the dropdowns in the sheet without having to go through the whole process of setting up the Custom Filter. Some of the users are not really Excel Savvy.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Ian Vincent on July 19, 2017, 10:47:09 AM
Just started using this today. It is going to be very useful.

Paul, I know you have moved on from PB, but if you ever get the urge to look at this class again:

If the requested output file is already open when writing the file with wb.WriteXML an error 70 occurs that
I can't seem to trap, presumably because it occurs in code in the SLL.
I can of course check if the file is open before using the class, but it is not exactly elegant.

It would be nice if there was something like wb.GetLastError that could be examined and any errors trapped within the SLL and reported by wb.GetLastError.

And, if you have the time and energy, could you include the NumberFormat tag?

As always, thanks for your great work.
Title: Re: Creating Excel WorkBooks without Automation
Post by: Paul Squires on July 19, 2017, 09:40:33 PM
Hi Ian,

I have attached the source code the first post in this thread. It's been a very long time since I looked at the code so it may be good or it may be able to be improved on a lot.

Good luck!  :)
Title: Re: Creating Excel WorkBooks without Automation
Post by: Ian Vincent on July 19, 2017, 11:19:06 PM
Thanks Paul.


Hi Ian,

I have attached the source code the first post in this thread. It's been a very long time since I looked at the code so it may be good or it may be able to be improved on a lot.

Good luck!  :)