Creating Excel WorkBooks without Automation

[Nathan Durland]

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 Expand



  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.

[Paul Squires]

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?

[Nathan Durland]

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 Expand


  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


[Wilko Verweij]

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

[Paul Squires]

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.

[Wilko Verweij]

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

[Paul Squires]

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.

[Jean-pierre Leroy]

Thank you Paul for this update I use your SLL every day in my projects.

Regards,
JEan-Pierre

[Petrus Vorster]

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.

[Ian Vincent]

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.

[Paul Squires]

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! 

[Ian Vincent]

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! 

[On Top Systems LLC]

Paul, I know this is resurrecting an old topic, but the link to your xmlExcel.sll is no longer valid. I've apparently misplaced my previous download. Is the zip file still available somewhere?

Thanks!

[Paul Squires]

Paul, I know this is resurrecting an old topic, but the link to your xmlExcel.sll is no longer valid. I've apparently misplaced my previous download. Is the zip file still available somewhere?

Thanks!

Link still seems to work for me. You could try the https secure link. Maybe that is the problem?
https://www.planetsquires.com/files/xmlExcel.zip

BTW, I have just finished a FreeBasic version of this code. I am waiting for Peter to download and try it. I sent it to his email but he'll probably not see it until he returns to work after the weekend.

[On Top Systems LLC]

Thanks, Paul. The https link works fine.