ggreen86 / xlsx-workbook-class Goto Github PK
View Code? Open in Web Editor NEWVFP Class to Create an XLSX Workbook without Excel Automation or Installed
VFP Class to Create an XLSX Workbook without Excel Automation or Installed
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
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
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.
Is it possible to embed charts into the workbooks being created with this routine? Just a thought.
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)
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.
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?
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.
This might not really be a big deal, but the Git project should be XLSX-Workbook-Class rather than XLXS-Workbook-Class.
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?
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 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
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)
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.)
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?
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
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).
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.
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.
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
*
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
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
…
There is a bunch of workbook properties listed on page 8 of the doc. Is it right
Just check the boxes if true.
Thanks
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.
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
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<
How to create a new workbook from template? Load the template and save ander different name or si there a different way?
TIA
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.
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
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
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
****
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.
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.
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
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.
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
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?
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.
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.
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
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.
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.
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:
Parameters like:
LPARAMETERS tcAlias, tnWB, tlFreeze, tlSaveWB,;
tvSheet, toHeaderLoc, toDataLoc
where
as existing tcSheetName
number (Id) of existing sheet to write to
toHeaderLoc and toDataLoc could be an empty object like
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.
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.
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:
In general my objective is:
What I am getting is:
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!!!)?
The MergeCells table selects a table xl_cells...but...upon completion does not reset the correct table.
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 is now being released as production and no longer BETA. It has been several months now without any issues being reported.
The following line occurs several times in the method:
tcSheetName = "Sheet" + TRANSFORM(lnShId) + 1
It should be changed to:
tcSheetName = "Sheet" + TRANSFORM(lnShId + 1)
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(
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.