Closed Esox61 closed 12 months ago
Hello—
You can open the workbook, change the cells that you want, and then save the workbook. You would use the following methods:
OpenXlsxWorkbook() Opens the workbook and loads into the working cursors GetWorkbookSheets() Returns a list of the sheets to select the appropriate sheet SetCellValue() Set the appropriate cell values SaveWorkbook() Saves the workbook (use SaveWorkbookAs() if you want a different name)
Greg
From: Esox61 @.> Sent: Friday, September 8, 2023 3:34 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] Export data to XLSX wihtout headers (Issue #99)
Hi Greg!
There is an excel spreadsheet to enter data. The excel sheet has 2 rows of headers and I don't want to overwrite the headers. Is there a way to get data from a table into the excel sheet without headers?
Thanks!
— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/99, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33LPE6WBUW5VRQOTGKLXZLC4VANCNFSM6AAAAAA4P66IX4. You are receiving this because you are subscribed to this thread.Message ID: @.**@.>>
Thanks for your reply! I opened sheet 2 of the Excel file ( goExcel.OpenXlsxWorkbookSheet(lcXLS, 2), entered the values, issued the goExcel.SaveWorkbookAs(lnWb,lcXlsFile2) statement, but it does not create the new Excel file. I also tried goExcel.SaveWorkbook(lnWb), but that doesn't save the changes to the original Excel file either. What am I doing wrong?
Can you provide your code? Thank you.
From: Esox61 @.> Sent: Friday, September 8, 2023 1:22 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Export data to XLSX wihtout headers (Issue #99)
Thanks for your reply! I opened sheet 2 of the Excel file ( goExcel.OpenXlsxWorkbookSheet(lcXLS, 2), entered the values, issued the goExcel.SaveWorkbookAs(lnWb,lcXlsFile2) statement, but it does not create the new Excel file. I also tried goExcel.SaveWorkbook(lnWb), but that doesn't save the changes to the original Excel file either. What am I doing wrong?
— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/99#issuecomment-1711998077, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33LE65DGPDERHUBJFPDXZNH5FANCNFSM6AAAAAA4P66IX4. You are receiving this because you commented.Message ID: @.**@.>>
CLOSE TABLES all
SET DEFAULT TO "c:\fox\XLSX-Workbook-Class-master"
lcXls="proba.xlsx"
goExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx")
lnWb = goExcel.OpenXlsxWorkbookSheet(lcXLS, 2)
IF lnWb=0
WAIT WINDOW "Open error!"
RETURN
endif
USE curTmp
GO top
PRIVATE lnRow,lnCol
lnRow=3
lnCol=1
SCAN
nextCol(mohu_rsz)
nextCol(rendszam)
nextCol(hull_term)
nextCol(komp_term)
nextCol(ag_nsuly)
lnRow=lnRow+1
lnCol=1
endscan
IF goExcel.SaveWorkbookAs(lnWb,"proba2.xlsx" ) &&Saves the workbook (use SaveWorkbookAs() if you want a different name)
WAIT WINDOW "Ok"
ELSE
WAIT WINDOW "Save error!"
endif
FUNCTION NextCol(lValue) goExcel.SetCellValue(lnWb,1,lnRow,lnCol,lValue) && Set the appropriate cell values lnCol=lnCol+1 RETURN table_and_xlsx.zip
I think I have a bug in OpenXlsxWorkbookSheet() method; I don’t seem to be using the sheet index passed as a parameter. I will have to debug this; however, I do not have the time to do so right now. Please try to use the method OpenXlsxWorkbook() which will open the entire workbook. Then use sheet index 2 as shown:
goExcel.SetCellValue(lnWb,2,lnRow,lnCol,lValue)
Please let me know if this works.
Greg
From: Esox61 @.> Sent: Friday, September 8, 2023 3:15 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Export data to XLSX wihtout headers (Issue #99)
CLOSE TABLES all SET DEFAULT TO "c:\fox\XLSX-Workbook-Class-master" lcXls="proba.xlsx" goExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx") lnWb = goExcel.OpenXlsxWorkbookSheet(lcXLS, 2) IF lnWb=0 WAIT WINDOW "Open error!" RETURN endif
USE curTmp GO top PRIVATE lnRow,lnCol lnRow=3 lnCol=1 SCAN nextCol(mohu_rsz) nextCol(rendszam) nextCol(hull_term) nextCol(komp_term) nextCol(ag_nsuly) lnRow=lnRow+1 lnCol=1 endscan IF goExcel.SaveWorkbookAs(lnWb,"proba2.xlsx" ) &&Saves the workbook (use SaveWorkbookAs() if you want a different name) WAIT WINDOW "Ok" ELSE WAIT WINDOW "Save error!" endif
FUNCTION NextCol(lValue) goExcel.SetCellValue(lnWb,1,lnRow,lnCol,lValue) && Set the appropriate cell values lnCol=lnCol+1 RETURN table_and_xlsx.ziphttps://github.com/ggreen86/XLSX-Workbook-Class/files/12562996/table_and_xlsx.zip
— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/99#issuecomment-1712117346, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33IEIVHSE5KNUBFCRO3XZNVDZANCNFSM6AAAAAA4P66IX4. You are receiving this because you commented.Message ID: @.**@.>>
Hello—
I did a quick test with the following code:
LOCAL lcFile, lnWb, loSheets, lnSh, lnRow, lnCol, lcOutPath, lnSec lcFile = GETFILE("xlsx", "Workbook", "Load", 0, "Select Workbook to load into Class") IF !EMPTY(lcFile) lcOutPath = ADDBS(JUSTPATH(lcFile)) goExcel = NEWOBJECT("VFPxWorkbookXLSX", "..\VFPxWorkbookXLSX.vcx", "", 1250) && Set codepage 1250 lnWb = goExcel.OpenXlsxWorkbook(lcFile)
FOR lnRow=3 TO 6
FOR lnCol=1 TO 6
goExcel.SetCellValue(lnWB, 2, lnRow, lnCol, lnRow*lnCol)
ENDFOR
ENDFOR
goExcel.SaveWorkbookAs(lnWb, lcOutPath + JUSTSTEM(lcFile) + "_2.xlsx")
ENDIF
The above worked for adding to the second sheet. However, the tab names did not translate the characters correctly which I will look into (see below). Note that I used codepage=1250 above to set the cursor’s codepage value correctly.
The correct (original page tab names):
@.***
The saved page tab names:
@.***
Greg
From: Esox61 @.> Sent: Friday, September 8, 2023 3:15 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Export data to XLSX wihtout headers (Issue #99)
CLOSE TABLES all SET DEFAULT TO "c:\fox\XLSX-Workbook-Class-master" lcXls="proba.xlsx" goExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx") lnWb = goExcel.OpenXlsxWorkbookSheet(lcXLS, 2) IF lnWb=0 WAIT WINDOW "Open error!" RETURN endif
USE curTmp GO top PRIVATE lnRow,lnCol lnRow=3 lnCol=1 SCAN nextCol(mohu_rsz) nextCol(rendszam) nextCol(hull_term) nextCol(komp_term) nextCol(ag_nsuly) lnRow=lnRow+1 lnCol=1 endscan IF goExcel.SaveWorkbookAs(lnWb,"proba2.xlsx" ) &&Saves the workbook (use SaveWorkbookAs() if you want a different name) WAIT WINDOW "Ok" ELSE WAIT WINDOW "Save error!" endif
FUNCTION NextCol(lValue) goExcel.SetCellValue(lnWb,1,lnRow,lnCol,lValue) && Set the appropriate cell values lnCol=lnCol+1 RETURN table_and_xlsx.ziphttps://github.com/ggreen86/XLSX-Workbook-Class/files/12562996/table_and_xlsx.zip
— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/99#issuecomment-1712117346, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33IEIVHSE5KNUBFCRO3XZNVDZANCNFSM6AAAAAA4P66IX4. You are receiving this because you commented.Message ID: @.**@.>>
I have modified the code to process the curTmp table with your solution . So it does not save the XLSX. :(
Close Tables All Local lcFile, lnWb, loSheets, lnSh, lnRow, lnCol, lcOutPath, lnSec lcFile = Getfile("xlsx", "Workbook", "Load", 0, "Select Workbook to load into Class") If !Empty(lcFile) lcOutPath = Addbs(Justpath(lcFile)) goExcel = Newobject("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx", "", 1250) && Set codepage 1250 lnWb = goExcel.OpenXlsxWorkbook(lcFile) USE "curTmp" lnMaxRow=RECCOUNT() =AFIELDS(laFields,"curTmp") FOR lnRow=1 TO lnMaxRow GOTO lnRow For lnCol=1 To 6 lcNameOfField=laFields[lnCol,1] xValueOfField= &lcNameOfField. goExcel.SetCellValue(lnWb, 2, 2+lnRow, lnCol, xValueOfField) ENDFOR Next lcFile=lcOutPath + Juststem(lcFile) + "_2.xlsx" If File(lcFile) Erase &lcFile Endif goExcel.SaveWorkbookAs(lnWb,lcFile ) Wait Window "Save "+Iif(File(lcFile),"ok","no") Endif
Hi, Esox61. I have this modification to your last proposal and it runs ok (this means: it runs and "_2.xlsx" is generated, but tab names need to be revised):
Explaining: I have put "SELECT 0" before "USEing curTemp".
If we don´t do it so, then the XL_STRINGS is closed, because it is in use when .OpenXlsxWorkbook() ends.
Hi, jhernancanom! You are my hero!!!! :) It really is the solution! Many thanks!!!
The tab name is corrected in the latest upload that is now in production status.
Hi Greg! Thank you very match for your work and your help!
Hi Greg!
There is an excel spreadsheet to enter data. The excel sheet has 2 rows of headers and I don't want to overwrite the headers. Is there a way to get data from a table into the excel sheet without headers?
Thanks!