ggreen86/xlsx-workbook-class

VFP Class to Create an XLSX Workbook without Excel Automation or Installed

xlsx-workbook-class's Issues

SaveGridToWorkbookEx returns true even if error occurs

SaveGridToWorkbookEx has this code near the end:

IF llError
	llSuccess = False
ENDIF

*-*	Create the XLSX file
this.CreateExcelFile(lcTempPath, JUSTPATH(tcFileName), JUSTSTEM(tcFileName))
this.RemoveWorkingDirectories(lcTempPath)
llSuccess = True

If an error occurred earlier, llSuccess is set to false. However, it's then set to true after calling CreateExcelFile and RemoveWorkingDirectories. I think the code should look like this instead:

IF llError
	llSuccess = False
else
*-*	Create the XLSX file
	this.CreateExcelFile(lcTempPath, JUSTPATH(tcFileName), JUSTSTEM(tcFileName))
	llSuccess = True
ENDIF
this.RemoveWorkingDirectories(lcTempPath)

Doug

About hidden columns

Hi, Greg and friends.
I have found something, a possible bug, that I want to share.

There is an XLSX file that has several sheets, as a template; the first one is empty in the template, that must be filled with data. The rest of the sheets are different reports that take data from first one according to formulas.

I use VFPxWorkbookXLSX to open the template (OpenXlsxWorkbook), fill the first sheet and save the result file (SaveWorkbookAs). I confirm that I only touch the first sheet.

The problem is that the columns that are hidden --in the other sheets in the template-- appear visible in the resulting XLSX.

If someone could give me some workaround, I'll appreciate.

HERNAN CANO
Systems Analyst

Ease of use in creating a workbook , renaming and retyping columns

i have used oExcel.SaveTableToWorkBook("BuildTrend", filenam,.t.,.t.) to create the xlsx file but, i want a couple of the field (column) names to be longer and have spaces in them. i also want all the numeric fields (columns) to be currency.

would you modify the output xlsx or go the route of creating the file from scratch and dumping the data into it? Which is easier for a novice to this class?
if it is to modify the output, i dont know how to get the sheet name with lnsh1 = oExcel.GetSheetName(lnwb,1). What is the sheet id parameter?

The real problem i have is that there are no examples for the novice in the documentation for the class s of there is a clear line of thought for a process.

Charting features

Is it possible to embed charts into the workbooks being created with this routine? Just a thought.

Permissions Issue when trying to SaveWorkbookAs

Not sure what's happening, but SaveWorkBookAs doesn't want to save. It returns .t. but no file appears. I'm sending it the full path name.

oXLSX = CREATEOBJECT("VFPWorkBookXSLX.VFPWorkBookXSLX")
iWorkbook = oxlsx.openxlsxworkbook(cActionPriceSheet)
IF lRename
 cActionPriceSheet = ADDBS(JUSTPATH(cActionPriceSheet))+"Action Price Sheet " +  ;
   TRANSFORM(DTOC(DATE(),1),'@R 9999-99-99')+".xlsx"
ENDIF 
iResult = oxlsx.saveworkbookas(iWorkBook,cActionPriceSheet)

SaveGridToWorkbookEx creates incomplete file

The Excel zip file created by SaveGridToWorkbookEx doesn't contain sharedStrings.xml so later opening it with OpenXLSXWorkbook fails. I didn't try it but I suspect SaveTableToWorkbookEx has the same issue.

Question about creating a link as a cell value

I recently came across this class and really appreciate it - Thanks!

Something I don't see in the demo and haven't been able to figure out is how do I create a cell which is a clickable link to display data on another sheet?

So for example if I have a workbook with 4 sheets captioned 'Summary', 'Red', 'Green', 'Blue' the user could click on a cell in the Summary page and be taken to the corresponding sheet? I'm guessing it would go into .SetCellValue?

Major performance improvements using ChilkatXML

First off, thanks to all involved in developing this class. It's very cool, and the work you've done is anything but trivial. It's amazing.

For what it's worth, I began investigating this class because I needed to consume a fairly data intensive .xlsx file containing product profiles for ecommerce. The manufacturer provides the data to merchants in this format only. The are currently about 4000 rows and 76 columns with very heavy string content in many columns.

In my initial test, with the class as provided, .openXLSXWorkbook() took at least an hour and 30 minutes on a quad core i7 with 16gb RAM and a 1TB SSD drive.

So I started poking around. I subclassed and replaced a fairly small amount of code in .readSharedStringsXML() and .readSheetXML(). I replaced the built-in XML parsing method calls (that use STREXTRACT() to parse the XML) with calls to the excellent and speedy Chilkat XML parser.

I'm now able the process the identical .openXLSXWorkbook() call in an average of 52 seconds. Yes...that's correct...1:30:00 to 00:00:52. Identical results.

Happy to share the code changes with anyone that's interested.

Git project has a typo in the name

This might not really be a big deal, but the Git project should be XLSX-Workbook-Class rather than XLXS-Workbook-Class.

Protection

There are methods to protect / unprotect objects (workbook / sheet.
This sets a bunch of props,
I see stuff like AlgorithmName
Does this encrypt / decrypt or is this just a setting for Excel to not touch the cells? IOW, cann I alter the cells ignoring the protection?

Please help with query export

I've been using VFP for many years, but I'm still a novice. Right now I use it to run reports against a database, then export to an Excel file. I'd like to save to XLSX using your class, but I really have no idea where to start.

Essentially, this is the basics of my programs, but I have no idea how to change it to XLSX instead. Can you help? I'm sorry for such a rudimentary question, especially since you've included so many examples, but I'm just completely lost. Thanks.

OPEN DATABASE dbname
USE queryname
EXPORT TO filename TYPE XL5

Init doesn't save/restore work area

Init should use code like this:

LPARAMETERS tnCodePage
LOCAL lnSelect, loException
lnSelect = select()
... rest of code
this.OnInit()
select (lnSelect)

so the current work area is saved and restored.

Doug

how create excel from table

hey Im trying to get luck with this and dont works
could you gime some advices, thank you in advance.

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx")
lnWb = loExcel.CreateWorkbook("ArabicTest.xlsx")

always I have this message "Invalid key lenght"
im new in foxpro Im jus triyng to create simple program,I have three files dbf on my folder
and my finally objecty is run somthing like this
---folder
your files from zip (WorkbookXLSX R31.zip)

table1.dbf
table2.dbf
table3.dbf

set defa to "folder"
modi command testprogram

(inside testprogram)
loExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx")
loExcel.SaveTabletoWorkbook(table1.dbf, "myexcel.xlsx", .T., .T.)
loExcel.SaveTabletoWorkbook(table2.dbf, "myexcel.xlsx", .T., .T.)
loExcel.SaveTabletoWorkbook(table3.dbf, "myexcel.xlsx", .T., .T.)

GetNamedRanges DataType

Is there any benefit in using an ARRAY LIST to an empty object instead of simple using a collection? If there is EMPTY then there is COLLECTION - and it would be much more easy to parse?

Please guide how to simply import xlsx into cursor

Dear Sir,
Please guide how to simply import xlsx into cursor using xlsx-workbook class.
I've tried my level best by reading given documentation but failed.

Thanking you,
Best regards,
Irfan

Excel cannot open the generated file

Good morning!
I'm trying to include the lates workbook class into our application. But the file that was created could not be red by Excel. No message from Excel, just an empty sheet. Your example is working fine. The contence of the file seems to be ok. So what goes wrong? May be I should send you the file?

Thanks for your help in advance.
Karsten

PS: My English is really poor. So please talk to me like to a 5 year old boy ;o).

Support column InputMask and Format

Have SaveGridToWorkbook and SaveGridToWorkbookEx support the InputMask and Format properties of each grid column, at least for numeric values. For example, with Format = "$" and InputMask = "999,999.99", use the appropriate format in Excel.

Support Visual FoxPro Advanced

Only a minor change is required to support VFP Advanced. Since VFPA returns a proper value for OS(3) (10 in Windows 10, for example), change this statement in the AddFilesToZip method:

*** 2020-02-19 DH: handle VFP Advanced
*** IF OS(3)<'6' .OR. OS(3)='6' .AND. OS(4)<'1'
IF val(version(4)) <= 9 and (OS(3)<'6' .OR. (OS(3)='6' .AND. OS(4)<'1'))

In VFPA, VERSION(4) returns "10" so VAL(VERSION(4)) <= 9 means this isn't VFPA.

xlxs workbook with column totals

Hi Greg,

Not an issue but just some code that others may find useful. I have been using your routine in some of our local programs to export details from a grid to a working XLSX format spreadsheet and have found it to be a fantastic tool.

In our own locally developed FoxPro programs we have the ability to set automatic column totals 'on' for all the entries in the grid that are numeric/currency/integers etc. When we export the contents of the grid to XLSX, it is handy to be able to have those same totals appear in the resulting spreadsheet.

I have updated the code in our local copy of your class in the 'savegridtoworkbook()' method (see below towards the end of the pasted code below).

The code will automatically build up a '=sum(x2:x99:)' type formula and place it into the matching cell at the end of the spreadsheet using the same 'style' as the normal values in the spreadsheet except it will put a double line top border in the total cell. The method is also passed the 'tlsum' parameter which controls whether the totals are to be built.

Anyway, others may find it useful.

LPARAMETERS togrid, txwb, tlfreeze, tlsavewb, tcsheetname, tlinclhiddencols, tlsum
LOCAL lcalias, lncol, lnwb, lnsh, lnrow, lcfield, loreturn, locolumn, lnstyle, lncolcount
LOCAL ARRAY larowcount[1], lacolorder[1]
loreturn = CREATEOBJECT("Empty")
ADDPROPERTY(loreturn, "Workbook", 0)
ADDPROPERTY(loreturn, "Sheet", 0)
DO CASE
CASE PCOUNT() = 0
RETURN loreturn

CASE PCOUNT() = 1
RETURN loreturn

CASE PCOUNT() = 2
tlfreeze = true
tlsavewb = true
tcsheetname = THIS.derivesheetname(txwb)
tlinclhiddencols = true
tlsum = true

CASE PCOUNT() = 3
tlsavewb = true
tcsheetname = THIS.derivesheetname(txwb)
tlinclhiddencols = true
tlsum = true

CASE PCOUNT() = 4
tcsheetname = THIS.derivesheetname(txwb)
tlinclhiddencols = true
tlsum = true

CASE PCOUNT() = 5
tlinclhiddencols = true
tlsum = true

CASE PCOUNT() = 6
tlsum = tlsum

ENDCASE

IF VARTYPE(tcsheetname) != 'C' .OR. EMPTY(tcsheetname)
tcsheetname = THIS.derivesheetname(txwb)
ENDIF
IF VARTYPE(togrid) != "O" .AND. PEMSTATUS(togrid, "BaseClass", 5) .AND. LOWER(togrid.BASECLASS) != "grid"
RETURN loreturn
ENDIF
DO CASE
CASE VARTYPE(txwb) = "C"
lnwb = THIS.createworkbook(txwb)
IF lnwb = 0
lnwb = THIS.getworkbook(txwb)
ENDIF

CASE VARTYPE(txwb) = "N"
IF SEEK(BINTOC(txwb), "xl_workbooks", "workbook")
lnwb = txwb
ELSE
RETURN loreturn
ENDIF

OTHERWISE
RETURN loreturn
ENDCASE
IF lnwb > 0
lnsh = THIS.addsheet(lnwb, tcsheetname)
IF lnsh > 0
lcalias = JUSTSTEM(togrid.RECORDSOURCE)

- Get the record count and display a status window
SELECT COUNT(*) FROM &lcalias INTO ARRAY larowcount
RAISEEVENT(THIS, "OnShowStatusMessage", 3, 0, larowcount[1])

- Get the number of columns to process and
- Get the column output order based on grid display order && Change requested by Matt Slay to output by grid display order and visible setting
IF tlinclhiddencols
lncolcount = togrid.COLUMNCOUNT
DIMENSION lacolorder[lnColCount, 2]
FOR lncol=1 TO togrid.COLUMNCOUNT
lacolorder[lnCol, 1] = togrid.COLUMNS(lncol).COLUMNORDER && Grid display order
lacolorder[lnCol, 2] = lncol && Column property order
ENDFOR
ELSE
lncolcount = 0
FOR lncol=1 TO togrid.COLUMNCOUNT
IF togrid.COLUMNS(lncol).VISIBLE
lncolcount = lncolcount + 1
DIMENSION lacolorder[lnColCount, 2]
lacolorder[lnColCount, 1] = togrid.COLUMNS(lncol).COLUMNORDER && Grid display order
lacolorder[lnColCount, 2] = lncol && Column property order
ENDIF
ENDFOR
ENDIF
IF lncolcount = 0
RETURN false
ENDIF
ASORT(lacolorder)

- Add the header row if defined and set the column widths; get the column font info
IF togrid.HEADERHEIGHT > 0 && Change recommendation by Doug Hennig (if no headers, start in first row)
lnrow = 1
FOR lncol=1 TO lncolcount
locolumn = togrid.COLUMNS(lacolorder[lnCol, 2])
THIS.setcellvalue(lnwb, lnsh, 1, lncol, locolumn.header1.CAPTION)
THIS.setcolumnwidth(lnwb, lnsh, lncol, THIS.convertpixelstoexcelunits(locolumn.WIDTH))
ENDFOR
ELSE
lnrow = 0
FOR lncol=1 TO lncolcount
locolumn = togrid.COLUMNS(lacolorder[lnCol, 2])
THIS.setcolumnwidth(lnwb, lnsh, lncol, THIS.convertpixelstoexcelunits(locolumn.WIDTH))
ENDFOR
ENDIF

- Add the cell data values
SELECT &lcalias
SCAN
lnrow = lnrow + 1
RAISEEVENT(THIS, "OnShowStatusMessage", 3, lnrow)
FOR lncol=1 TO lncolcount
locolumn = togrid.COLUMNS(lacolorder[lnCol, 2])
lcfield = locolumn.CONTROLSOURCE
THIS.setcellvalue(lnwb, lnsh, lnrow, lncol, &lcfield)
ENDFOR
ENDSCAN

- Set the cell style formatting
FOR lncol=1 TO lncolcount
locolumn = togrid.COLUMNS(lacolorder[lnCol, 2])
lnstyle = THIS.createformatstyle(lnwb)
IF THIS.defaultfont != locolumn.FONTNAME .OR. THIS.defaultfontsize != locolumn.FONTSIZE && Change recommended by Doug Hennig; 2017-06-12
THIS.addstylefont(lnwb, lnstyle, locolumn.FONTNAME, locolumn.FONTSIZE)
ELSE
THIS.addstylefont(lnwb, lnstyle, THIS.defaultfont, THIS.defaultfontsize)
ENDIF
THIS.setcellstylerange(lnwb, lnsh, 1, lncol, larowcount[1]+1, lncol, lnstyle)
ENDFOR

- Freeze the first row if specified
IF tlfreeze
THIS.freezepanes(lnwb, lnsh, 1, 0)
ENDIF
*

---

---

---

• bch added 8.11.2017 To add in a total for the numeric type columns. Also added code for tlsum flag setting.

•   IF tlsum
    	FOR lncol = 1 TO lncolcount
    		lcdatatype = THIS.getcelldatatype(lnwb, lnsh, 2, lncol)
    		IF INLIST(lcdatatype, data_type_currency, data_type_float, data_type_int)
    			lccolumn = THIS.columnindextoascii(lncol)
    			THIS.setcellformula(lnwb, lnsh, lnrow + 2, lncol, "=SUM(" + lccolumn + "2:" + lccolumn + ALLTRIM(STR(lnrow))+")")
    			lnstylecell = THIS.getcellstyle(lnwb, lnsh, lnrow, lncol)
    			THIS.setcellstyle(lnwb, lnsh, lnrow + 2, lncol, lnstylecell)
    			THIS.setcellborder(lnwb, lnsh, lnrow + 2, lncol, border_top, border_style_double)
    		ENDIF
    	ENDFOR
    ENDIF
  

---

---

---

	RAISEEVENT(THIS, "OnShowStatusMessage", 3, -1)
	IF tlsavewb
		THIS.saveworkbook(lnwb)
	ENDIF
	loreturn.SHEET    = lnsh
	loreturn.workbook = lnwb
ENDIF

ENDIF
RETURN loreturn

SaveGridToWorkbookEx

Procedure SaveGridToWorkbookEx
I got an error in defining the type of the calculated field and I quickly fixed it for myself like this
…

IF ISNULL(lxCellValue)
    lxCellValue = this.NullValue
ENDIF
…
On
…
IF ISNULL(lxCellValue)
    lxCellValue = this.NullValue
ELSE
   IF loGrid.Columns(lnCol).FieldType<>TYPE('m.lxCellValue')	
  			loGrid.Columns(lnCol).FieldType=TYPE('m.lxCellValue')	
   ENDIF
ENDIF

…

Workbook Properties

There is a bunch of workbook properties listed on page 8 of the doc. Is it right

• in difference to anything else they are not related to a workbook like the methods
• one can not retrieve the properties of a workbook loaded?
• properties once set will be written on saving workbooks until changed

Just check the boxes if true.
Thanks

Error setCellBorderex

Hi Greg,
I am using google translate
Using setcellborderex an error was generated, "Too many arguments"
Compare version 31 with version 32 and version 32 has a value of more, I show you in the images.

Version 31

Version 32

It is an excellent tool, thank you.

About FastStrings.vcx

Good evening.
The class FastStrings.vcx sounds interesting and promising. Will it be available separately?
Speaking about Workbook class - will try it later this week.

About SetSheetVisibility

Hi, Greg and friends.
I have found something that I want to share.

When I use SetSheetVisibility with 1 to hide a sheet, this doesnot run.

When I use SetSheetVisibility with 2 to very-hide a sheet, it does run, yes, but in the resulting XLSX I cannot make it visible, because it doesnt appear in the list (to Show hidden sheets).

If someone could give me some workaround, I'll appreciate.

HERNAN CANO
Systems Analyst

readSharedStringsXML method sets wrong last ID

This method is storing the actual count of shared strings as the last ID, but string IDs are zero based in the XLSX format. This doesn't cause a problem if you don't add any strings before writing out a new .XLSX. If you do, it's not good.

Proposed change:

* 04/11/18:DDL> Shared string IDs are zero based, so we need to store one less here
*this.SetLastId(tnWB, lnNode-1, "xl_strings") && old code
this.SetLastId(tnWB, lnNode-2, "xl_strings")
* 04/11/18:DDL<

Create Workbook from template

How to create a new workbook from template? Load the template and save ander different name or si there a different way?

TIA

Illegal xml character using .Savetabletoworkbookex()

I've been using the .Savetabletoworkbookex() to extract large tables. It's all been working fine except for some tables with memo fields. I've tried to find what the "illegal xml character" is but could not. I scanned the table and replaced all ASCII characters 0 - 13 with blanks or spaces and still get this error. I have attached a zip file with the data set and resulting errant XLSX file that gives this error:
Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Illegal xml character. Line 1605, column 1.

Interesting enough, this table with 82749 records errors out. I extracted it from a larger table that erorred out and and figured out that when we reach 82749 records, I get this error. When I take this same data set and remove the first 50000 records, it no longer errors out. When I take this table and extract only 82748, it does not error out. It just errors out with these 82749 and higher from the original table.

Thank you,
Philip B.

Rewriting index prompt

Testing new version of the class. Found this bug:

CREATE CURSOR xl_sheets (_long line of fields here_ )
INDEX ON sheet TAG sheet
INDEX ON workbook TAG workbook
INDEX ON BINTOC(workbook)+UPPER(ALLTRIM(shname)) TAG shname
**INDEX ON BINTOC(workbook)+BINTOC(sheet) TAG sheet FOR !DELETED()**
SET INDEX TO

During testing found out that every time it asks me to rewrite index on the bold line. The name of the index is the same. Renamed to wbsheet as in the next cases.

Otherwise it works much faster. After fixing this I see no other issues

Goto Bookmark

Hi,

I do not understand how to gather the Cell coordinates of a bookmark / named range I see I can manipulate it via AddNamedRange / ClearNamedRange.
My problem is that I have XLSX with custom defined headers where the location of (data) column header und start of data is defined via bookmark and I need to figure out this cell(s).
EXCEL command would be GOTO(cTarget)

So what I look for is something like GetNamedRange

Enumerate Bookmarks

While changing my Excel handler I found, thaz az a certain point I just run through all bookmarks.
I need to enumerate the bookmarks
Excel exposes a collection like

FOR EACH loNames IN THIS.goExcel.activeWorkbook.Names
**** 

can you please clarify?

Hello.
Now I try running my FoxPro program under WINE in Linux. Practically everything works fine but I encounter problem (OLE error 0x80004001) when using SavetabletoworkbookEx function. During debugging I found that everything works until the program calls removeworkingdirectories procedure at the line :
llReturn = IIF(apiRemoveDirectory(tcDir) != 0, True, False)
Can you please clarify, this procedure deletes some temporary folders? This clarification will be very helpful so I will know where to start digging.

Support displaying grid lines

You can turn off or on gridlines by adding showGridLines="0" or showGridLines="1" attributes in the <sheetViews> element of a sheet XML file. I suggest adding a property such as lDisplayGridLines to support that and handle it in all mechanisms that output to an XLSX file, including the SaveGrid* and SaveTable* methods.

changing the cell format to date

Hi,
how can I format cells on a certain column according to the data type in the grid I am exporting. I would like to export an date column but in Excel it ist formatted asn 'standard'.

Best regards

CreateExcelFile does not always work properly

This is on a Windows 10 Pro x64. For small/simple spreadsheets I do not appear to have any issues. For larger workbooks with multiple sheets...it does not always complete properly so sometimes it leaves it with .ZIP extension...other times not all of the files have been added properly...etc. Just seems to have many issues with this routine.

Appending new sheet to existing documents creates damaged file

I created an Excel document using SaveGridToWorkbook, then later appended to it using code like this:

lnWB = loExcel.OpenXLSXWorkbook(lcOutputFileName)
lcSheet = 'My New Sheet'
loReturn = loExcel.SaveGridToWorkbook(loGrid, lnWB, .T, .T., lcSheet)

When I open the document in Excel, I get a warning that there's unreadable content. If I tell Excel to repair it, it opens it just fine and displays this:

Repaired Records: Format from /xl/styles.xml part (Styles)
Repaired Records: Cell information from /xl/worksheets/sheet2.xml part

I've attached the created document if that helps.
ExcelTest.xlsx

Variable 'tcstring' not found in readsharedstringsxml

Greg,

I'm having a play with reading an Excel doc and running into this error in readsharedstringsxml when the code shown below executes:

INSERT INTO xl_strings (id, workbook, checksum, stringxml, stringval, presvspace, formatted) ;
			VALUES (lnNode-1, tnWB, this.GetCheckSum(tcString), lcText, this.GetStringXML(lcText), llPrsvSp, llFormatted)

My wild guess is that the tcString ref should be lcString?

When exporting from grid, ColumnOrder of columns in grid is not honored...

Notice the ColumnOrder of each column in this grid (see screenshot). They are not in the same order as the actual columns collection in the grid, because at design time of the grid, I have re-arranged the column order. For instance, Column5 may well have ColumnOrder of 2, and so on.

Yet when exported using the SaveGridToWorkbookEx() method, the columns are ordered in the sequential Column1, Column2, Column3, etc order, but that is not the visual order of the grid, which is what the user will expect when they export to a spreadsheet.

UI Grid:

Excel spreadsheet:

Support XLSM file

I tried to output to a file using XLSM as the extension and XLSXWorkbook forced the extension to XLS. The reason I want to do this is because I have a customer with an existing XLSM file they want to append another worksheet to.

Suggested Changes

Greg,
Hope you are having a great day. Here are a couple of changes I made to your great VFP Class.
Added to VFPxWorkbookXLSX.h to support text format for cell:
#DEFINE CELL_FORMAT_TEXT 49 && Text

Changed SaveTableToWorkbook method allow support for both tables and cursors
Instead of
SELECT COUNT(*) FROM &lcAlias INTO ARRAY laRowCount
Changed to:
laRowCount[1] = RECCOUNT(lcAlias)

Thanks again for the great VFP Class!

Chad

VFPxWorkbookXLSX

Dear Gregory Green,

How to remove the display process
as shown in the red circle

Thanks,

Maskur

Comparison of char and memo fields may result in values not being added to spreadsheet.

Here’s the scenario.
I am using the SaveTableTWorkbook method.
My temp table (a cursor actually) has 2 fields – a char(135) and a memo.
The char field data is like an abstract. It contains the first 135 characters of the memo field, which contains the entire description.
Because getStringRecord is only comparing the first 120 characters of the string, AddStringValue is not recognizing the contents of the memo field as a unique value to store.
The final output in the workbook is then essentially the contents of the char field.

Prevent sheet name from being too long

If you name a sheet within an Excel document with a string that's more than 31 characters, Excel reports that the file damaged and the "repair" it does is to truncate the sheet name to 31 characters. I suggest not allowing a sheet name to be more than 31 characters: either give an error or truncate the sheet name.

feature request / SaveTablesToWorkBook

SaveTablesToWorkBook and SaveTablesToWorkBookEx do a great job.
Except I can not adress the location.
It will create a new sheet and start on A1. What I like would the possibility to (optionally) address:

• an existing sheet
• a starting cell for header
• a starting cell for data
  The calling code must be responsible to place the stuff on something meaningful, so data should not write over header line.

---

Parameters like:

LPARAMETERS tcAlias, tnWB, tlFreeze, tlSaveWB,;
tvSheet, toHeaderLoc, toDataLoc

where

tvSheet

Type Char

as existing tcSheetName

Type Num

number (Id) of existing sheet to write to

Locs

toHeaderLoc and toDataLoc could be an empty object like

• toHeaderLoc.BegCol
• toHeaderLoc.BegRow

toHeaderLoc and toDataLoc are used for tvSheet as number only, only for existing tnWB too.

---

In this way I could fill data into an existing workbook / template and should be able to keep existing formats etc. As far as I see the write would work with existing sheets/cells.

Invalid key length when creating INDEX TAG fullname of cursor "xl_workbooks"

When I try to use this class withint the example form () the class works flawlessly. But when I paste the class onto a form of my project app and then run it, it shows me the "Invalid key length" error message. This happens in the "createWorkingCursor()" class method. Although I tested that at the moment of creating the "xl_workbooks" cursor and the index (INDEX ON UPPER(fullname) TAG fullname), the COLLATE= "MACHINE" and codepage=1252.

Asking-about-filling-a-template

Hi, Greg.
I have tried to "update" my script for copying a DBF to a XLSx-template.

I "developed" a script based in r31, and I made several modifications.
Now I have tried to update it to r32, but I continue getting a wrong worksheet, wrong in the sense I am going to explain...

I send you:

• Main.prg (simplified with respect to my scenario), and CopyToHoja1_CxS.prg that I am using to "fill" the XLSx-template with DBF-data.
• My TEMPLATE.xlsx worksheet.
• My VENTAS_ENERO_2021.DBF data file.
• The VENTAS_ENERO_2021.xlsx worksheet I get when running my script.

In general my objective is:

• Copy file TEMPLATE.xlsx to VENTAS_ENERO_2021.xlsx
• With CopyToHoja1.prg, take VENTAS_ENERO_2021.xlsx and fill the cells with the data in VENTAS_ENERO_2021.DBF.

What I am getting is:

• The formulas in the first row (columns SUBTOTAL, Descuentos, and TOTAL) are respected. The rest of the rows does not respect the formulas and the cells are filled with the values in the template.
• The formula in the first col of the row 15 is respected. The rest of the columns does not respect the formulas and the cells are filled with the original-values in the template.
• The VFP-"inputmask" (XLS-FormatNumber) in the cells are not respected.
• In r31 the hidden attribute of the H-column is not respected. In r32 the hidden attribute of the H-column do is respected.
• The word "Período:" in cell E4 dissapears.

I consider the script I got (based in R31) can be very complex for you to adjust.

But my goal is asking you:
What could be the best "instructions"/commands for me to get the result: take a template and fill with data..... (and saving of course!!!)?

Thanks.
Asking-about-filling-a-template.zip

MergeCells method selecting table

The MergeCells table selects a table xl_cells...but...upon completion does not reset the correct table.

Add release date on the readme file...

May I suggest adding the release date on the readme page? When these kind of projects have timestamps in their release history, I just always find that helpful.

See example below:

Release 32

• Released 2021-02-24

Release 32 is now being released as production and no longer BETA. It has been several months now without any issues being reported.

addSheet() bug

The following line occurs several times in the method:

		tcSheetName = "Sheet" + TRANSFORM(lnShId) + 1

It should be changed to:

		tcSheetName = "Sheet" + TRANSFORM(lnShId + 1)

SaveTableToWorkbookEx

Good evening.
I try to save cursor to xlsx file. I need to set headers different from field names in cursor.
I try to do it like in example provided with the release:

I create an array with field names and headers.

lcTable = GETFILE("dbf", "table", "Export", 0, "Select Table to Export")
IF !EMPTY(lcTable)
	lcAlias = CHRTRAN(JUSTSTEM(lcTable), " ", "")
	IF !USED(lcAlias)
		USE (lcTable) IN 0 EXCLUSIVE ALIAS &lcAlias
	ENDIF
	SELECT &lcAlias
	DIMENSION fld_list(4,2)
	fld_list(1,1) = lcAlias+".fam"
	fld_list(1,2) = "Surname"
	fld_list(2,1) = lcAlias+".nam"
	fld_list(2,2) = "Name"
	fld_list(3,1) = lcAlias+".snam"
	fld_list(3,2) = "Middle name"
	fld_list(4,1) = lcAlias+".drpc"
	fld_list(4,2) = "Birthday"

	loExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx")
	loExcel.Savetabletoworkbookex(lcAlias, lcAlias + "_test2.xlsx", fld_list, .T., lcAlias)

	USE IN SELECT(lcAlias)
ENDIF

But I get the error tafields.prg does not exist. What's wrong with the code? It seems (checked in debugger) that fld_list[1,1] value is passed in class but not the whole array. Also tried even this variants:

loExcel.Savetabletoworkbookex(lcAlias, lcAlias + "_test2.xlsx", &fld_list, .T., lcAlias)
loExcel.Savetabletoworkbookex(lcAlias, lcAlias + "_test2.xlsx", "fld_list", .T., lcAlias)

...but still no luck(

getCellValue() does not handle large integer values properly

If an .xlsx cell contains an integer value in excess of the VFP max of 2,147,483,647, the value returned is incorrect. My workaround in a subclass:

	function getCellValue(tnWB, tnSheet, tnCellRow, tnCellCol)

		lxValue = dodefault(tnWB, tnSheet, tnCellRow, tnCellCol)
		
		if xl_cells.datatype = DATA_TYPE_INT and lxValue <> val(xl_cells.cellvalue)
			lxValue = cast(xl_cells.cellvalue as n(20,0))
		endif xl_cells.datatype = DATA_TYPE_INT and lxValue <> val(xl_cells.cellvalue)
		
		return lxValue

	endfunc &&getCellValue