Libxlsxwriter problem

1

Hello All
I’m try to use Libxlswriter DLL example from here but it does not work correctly when using formula.
Here is very simple code:

            xlsx &=NEW(xlsxwriter)
            FName=path()&'\Report.xlsx'
            xlsx.NewWorkbook(FName)
            worksheet#=xlsx.AddSheet('Sheet1')
            xlsx.WriteNumber(1,1,10)
            xlsx.WriteNumber(2,1,20)
            err#=xlsx.WriteFormula(3,1,'=SUM(A1:A2)')
            err#=xlsx.CloseWorkbook()
            dispose(xlsx)

Excel file is generated (Report.xlsx),
Report.zip (4.4 KB)
but when I open it and click on X to close Excel and file (without any change), it always ask me “Want to save your changes…”
Any idea?

2

Hi Daniel,

Not seeing the same as you - Excel doesn’t ask me about saving changes when I open then close your Report.xlsx

Microsoft® Excel® 2019 MSO (Version 2410 Build 16.0.18129.20158) 32-bit

Simon

3

Hello @SimonKemp
Thank you for checking this. My version is 2016 (16.0.4266.1003).
It looks like some MS problem :frowning:

4

A good opportunity to thank Rafael for sharing his work (being his ‘xlsx’ clarion class wrapper here):

Discovered it a few weeks ago, works great out of the box if you are careful to ensure both lbxlsxw.dll and zlib1.dll are available to your app. Rafael didn’t go as far as providing a template - probably a wise decision, he left anyone interested in it to work it out from his example code - and that’s not hard.

Personally, this has been a great find. Blindingly fast compared to interacting with an instance of Excel, perfect for a service generating spreadsheets, etc etc and I’ve derived his class a bit, adding things I needed.

Total fan boy, thanks Rafael.

5

Hi Simon,

I had put that on the back burner, because I thought I would have to build my own DLLs from the Github source, but was happy to see they are included in Rafael’s zip. In my case building the DLLs would have meant doing stuff on my home computer and copying it over…glad I didn’t have to do that.

There is something I will use it for immediately. I have a program that has to produce source for mailmerge. The main file can be a CSV, but there is a subsidiary file that needs to be an XLSX, and I have to manually create that from the CSV, including setting the format for a numeric column to text, because otherwise Word cannot match things up.

However, looking at the example, I still ask myself – is this a sensible thing to be getting into? Writing code like: chart2.SetTrendlineLine(series2,color:purple,5.25,XLSX:DASH_SOLID,10)?

To figure out what sort of line you wanted you would already have done the point and click for the trendline in Excel, and then you have to translate that into a sort of VBA command and put it in your Clarion program. In the real world, presumably, this would be a situation where you create the graph once and then it is run many times with new months of data or whatever. Maybe the rational approach to that is to have your spreadsheet all set up with your graphs and your formats and all (using Excel), and the only thing you have to do each month is push the new data set into the workbook.

And, yes, it is good to be able to do that data push from Clarion…sometimes. What I’ve been playing with over the last couple of weeks is Power Automate, where the vanilla version comes with Windows 11. I can use that to send a query to the database and write the result to a particular location in an Excel workbook that already has all the bells and whistles in (you just provide the top left corner for where to write the data). In general, a lot easier than writeString/writeNumber commands, one for each cell.

I have an Excel report that is about 10 pages with 20 queries, and a bunch of graphs running off the imported data, and mostly it is three commands per dataset: set the worksheet, run the query, write to the worksheet. Seems like a good tool for the job.

Jon

6

Having just written what I needed to, I’ll echo Simon that it works great out of the box. Just in case anyone needs a simple little recipe for writing data in a process procedure in an app, here it is:

  • In the global embed, after global includes: INCLUDE(‘xlsxwriter.inc’)

  • In your procedure in the data section (starting in the first column):
    xlsx &xlsxwriter
    FName CString(256)
    rowNumber short

  • In the WindowManager.Init, before “Open Files” (this is creating a new excel file called tbi2.xlsx)

xlsx &=NEW(xlsxwriter)
FName=path()&‘\TBI2.xlsx’
xlsx.NewWorkbook(FName)
worksheet#=xlsx.AddSheet(‘Sheet1’) ! Could be any name
err#=xlsx.WriteString(1,1,‘FileNo’) ! These are the column headings
err#=xlsx.WriteString(1,2,‘Dept’)
err#=xlsx.WriteString(1,3,‘Benefit’)
err#=xlsx.WriteString(1,4,‘Full_Amount’)
err#=xlsx.WriteString(1,5,‘After_Deductions’)
err#=xlsx.WriteString(1,6,‘Keep’)
RowNumber = 1 !Data will be written starting in row #2

  • In TakeRecord (i.e. for each row in the source data)
    rowNumber += 1 !bump down a row
    xlsx.writestring(rownumber,1,clip(tbo:file_no)) !write a row of data
    xlsx.writestring(rownumber,2,clip(tbo:dept))
    xlsx.writestring(rownumber,3,tbo:benefit)
    xlsx.writenumber(rownumber,4,tbo:full_amount)
    xlsx.writenumber(rownumber,5,tbo:after_deductions)
    xlsx.writestring(rownumber,6,tbo:keep)

  • In ThisWindow.Kill
    err#=xlsx.CloseWorkbook()

Totally painless.

7

I just tried Libxlswriter.
The ease of use is just like my current OLE code.
But the speed is impressive!

My test procedure loops through the complete customer file and exports 10 columns (all Strings).
Including the header libxlswriter has processed 9978 lines in just 1.5 seconds!
That’s really impressive compared to my current OLE-based code.
The same customer file, exporting the same 10 columns based on my OLE code takes 90 seconds!

But because libxlswriter can only write Excel files I can not use it.
I looked at LibXl in the past. It’s a commercial tool but it is not expensive.
And it can read and write Excel files.

Because of the extreme gaining in speed compared to my OLE code I will now consider switching to LibXl faster than planed.

Jens

8

It’s been a lot of years since I used LibXL, but if you use XLS it is much much faster than XLSX. Maybe that is different now, but important to know if you notice a speed problem.

9

Yep. For reading, libxl.dll (not free, not expensive) is the bee’s knees. Definitely best to go via Leonid http://www.klarisoft.com/KSNews_191.htm and get supporting clarion stuff. And (Jeff) it’s pretty damn fast reading whether xls or xlsx these days. His stuff does offer a writing side of things but that has always seemed rather limited to me.

But no matter. Because the free libxlsxw.dll (don’t miss that dependency on zlib1.dll) along with Rafeal’s generous shared wrapper, zip with dlls etc will get you started quickly - Jon W and Jens had the same experience as me.

Then you can build on Rafael’s stuff if you want. My immediate application was “send a queue to xlsx” - a simple ‘listing’ type of output. Wasn’t hard to add methods that used who/what and get something quite presentable. I take Jon W’s point that it won’t be the correct hammer in all cases but am still loving it. In my case, the combination of libxl and libxlsxw gives me an “out” from reliance on Excel being installed and the machinations of getting it to do stuff for me.

10

I have been using LibXL for years and it is extremely fast even when used with Excel’s limit (1,000,000 rows)

Please note, what purpose do you use libxlsxw for or what functionality does it provide that you do not get with LibXL?

11

Also, FWIW, it might be worth the time for folks to ask their favorite chatbot “what issues can arise for a company or corporation that integrates open-source libraries into their commercial application, but those libraries use the FreeBSD license?”

1 Like
12

LibXL has been my go-to for reading for several years. But on the writing front, it never seemed to offer what I needed. I based that on Leonid’s wrapper - it seemed a bit limited to me. Very likely I was wrong and you have had a different experience?

So for me, libxlsw was bit of a revelation. Divide and conquer - use one for reading, the other for writing.

13

I asked Copilot and didn’t see anything that frightened me. But doesn’t mean it’s right for everyone.

Thanks.

14

I wrote my own classes to libxl. You can do pretty much anything you want.

15

On the writing front? Is that stuff you could/would share?

Thanks

16

Hi Simon,
Unfortunately, it isn’t code that I can release.
It’s not that hard.
Here’s a method example for adding a sheet. If you have the Karisoft product then you have the prototypes for all of the procedure in the libxl dll. LibXl’s documentation is decent.

!-----------------------------------------------------------------------
!!! <summary>Add a sheet to current workbook. </summary>
!!! <param name"argSheetName">New Sheet Name</param>  
!-----------------------------------------------------------------------
up_libxl.AddSheet procedure(string argSheetName)!, long, proc
RetVal                long(false)
  code
  if self.BookHandle
    self.SheetName = self.CleanSheetName(argSheetName)
    if self.FindSheetByName(self.SheetName) <> Level:Benign
      self.SheetHandle  =  xlBookAddSheet(self.BookHandle,self.SheetName)
      if self.SheetHandle
        RetVal = true
      else
        self.SetError('AddSheet')
      end
    else
      RetVal = true
    end
  else
    self.SetError('AddSheet','No Current Workbook')
  end
  return RetVal
1 Like