WorkbookXLSX R30 2

[gbcpastor]

I'm currently learning how to use a class called WorkbookXLSX and I know some of you are quite familiar with it. The problem I'm having is:

I'm trying to post a field to a cell and it has 15 leading spaces but every time I post it, it trims both leading and trailing spaces. This data must be posted exactly as original. Please help with any ideas you may have.

Thanks in advance!

 

[gbcpastor]

gbcpastor--

See the above reply for a download of the updated class. You should be able to now have leading spaces and the class automatically assign to preserve the spaces. However, if you are trying to indent the text over an amount, you should use the following methods to create a style with the desired indent. The concept of styles is used to be able to define all the formatting for a cell via a style. See the section "Cell Styles" on page 20 of the documentation and the Demo method for examples.

LOCAL loExcel, lnWb, lnSh, lnStyle
loExcel = NEWOBJECT("VFPxWorkbookXLSX", "vfpxworkbookxlsx.vcx")
lnWb =loExcel.CreateWorkbook("testgg.xlsx")
lnSh = loExcel.AddSheet(lnWb, "Test")
lnStyle = loExcel.CreateFormatStyle(lnWb)
loExcel.AddStyleIndent(lnSh, lnStyle, 4)             && indents by 4 spaces
loExcel.SetCellValue(lnWb, lnSh, 1, 1, "Hello!")
loExcel.SetCellStyle(lnWb, lnSh, 1, 1, lnStyle)
loExcel.SaveWorkbook(lnWb)

Greg, thank you so much for all your hard work that I now get to use. This is a great tool for so many that need xlsx reports for clients these days.

 

[Chris Miller]

Greg, the big advantage is - correct me, if I'm wrong - that you generate the (Office Open standard) XML and zip it up as XLSX file, which needs no Excel installed for OLE automation.
If you already have Excel workbook and shet generation based on OLE automation it still also works for XLSX, that's not depending on the Excel file format like EXPORT or COPY TO do, gbcpastor.

For sake of a more complete overview there are also methods using an Office/Excel OLEDB Provider, you find code by Cetin Basoz, for example here:

VFP using VFP2EXCEL - Is there a way to easily trim character fields that are sent to Excel

I am using Cetin Basoz's VFP2Excel to copy/export a subset of data created by a VFP SQL statement to Excel. What I am trying to figure out: is there a way to TRIM the character fields that are sent...

stackoverflow.com

And then there are classes from Vilhelm-Ion Praisach at his blog, also for docx, append from xlsx, exporting to xlsx etc.

Export to xlsx

Versions for VFP 6.0 Both for class (BRAND NEW) and procedure (improved version) 1) Class exportxlsx a) Small correction in method TheF...

praisachion.blogspot.com

 

[ggreen61]

Chriss--

Yes, the intention was to provide a means to create and control cell formatting without automation. Some of my users do not have MS Office and instead use other open source alternatives which does not support the automation.

What sets my class apart from Cetin's or Vilhelm's solutions is the ability to assign cell formatting, image support, and multi-sheet support. Also, Cetin's and Vilhelm's classes only output from a cursor or table; mine does not depend on this. Also, my class provides for the ability to read an existing xlsx file, make changes, and then save the changes, either to the existing file or a new file. In the case where one of the sheet#.xml files could exceed the 2G limit, then my class will fail (also true for Wilhelm's); the only solution I know is Cetin's in this case. In general, I have tried to provide a complete solution for creating xlsx files with most common formatting and formula support. The class includes direct write methods for creating a xlsx file from a table/cursor or grid. In the case of the grid, it creates the cell formatting based on the grid formatting. These methods are very optimized for very fast output.

I also added a class for writing a report to a xlsx format retaining all the report formatting.

 

[ggreen61]

gbcpastor-

Also, is there a way to silence the status windows as it does the build?

The cause of the status window is a bug in Windows that is ignoring the flag FOF_SILENT. This has been reported to MS. I think the status window was not shown in Win7; somewhere in the updates of Win10 the bug appeared.

 

[gbcpastor]

Thanks to everyone, especially Greg "ggreen61", you have been such a great help and all is working perfectly.

God bless;