search

ggreen86 / XLSX-Workbook-Class

VFP Class to Create an XLSX Workbook without Excel Automation or Installed
45 stars 16 forks source link

Error after creating a file with Savetableworkbookex #81

Closed tpenninckx closed 1 year ago

tpenninckx commented 1 year ago

Hello, When I create an XLSX file from a cursor, using savetableworkbookex, if I re-open the file (in this example to add one line), the process is working (the line is well added and filled with the values), but when I open the file with Excel, I get an error : (Sorry, we found an error....) The xml given by Excel is : <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

error068760_01.xmlDes erreurs ont été détectées dans le fichier « C:\temp\XLSTEST.xlsx »Enregistrements réparés: Information de cellule dans la partie /xl/worksheets/sheet1.xml

Code Sample :

SET DEFAULT TO "C:\dev\Foxpro\VFPX - GitHub\WorkbookXLSX R36"

include "C:\dev\Foxpro\VFPX - GitHub\WorkbookXLSX R36\VFPxWorkbookXLSX.h"

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "C:\dev\Foxpro\VFPX - GitHub\WorkbookXLSX R36\VFPxWorkbookXLSX.vcx")

cfile = 'c:\temp\XLSTEST.xlsx'

*** EXPORT D UN CURSEUR CREATE CURSOR TEST (ID c(20), descr c(100), valeur n(10,2)) INSERT INTO test VALUES ("ID1", "DESC1", 100.15) INSERT INTO test VALUES ("ID2", "DESC2", 200.24)

loExcel.Savetabletoworkbookex('TEST', cFile, .NULL., .f., 'Sheet1')

IF ni > 0

loExcel.SaveWorkBook(ni)

ELSE MESSAGEBOX("Impossible d'ouvrir le fichier") ENDIF

loExcel=null

Thierry

ggreen86 commented 1 year ago

Thierry—

In the example code below, you are setting the cell formatting using the original methods that I developed which are deprecated (I think this is causing the problem). Please look at the documentation for creating a format style and then setting the style to the cell to be formatted (you can also look at the demo method to see examples). Please let me know if this works.

Greg

From: Thierry Penninckx @.> Sent: Thursday, March 23, 2023 12:57 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] Error after creating a file with Savetableworkbookex (Issue #81)

Hello, When I create an XLSX file from a cursor, using savetableworkbookex, if I re-open the file (in this example to add one line), the process is working (the line is well added and filled with the values), but when I open the file with Excel, I get an error : (Sorry, we found an error....) The xml given by Excel is :

error068760_01.xml Des erreurs ont été détectées dans le fichier « C:\temp\XLSTEST.xlsx »Enregistrements réparés: Information de cellule dans la partie /xl/worksheets/sheet1.xml

Code Sample :

SET DEFAULT TO "C:\dev\Foxpro\VFPX - GitHub\WorkbookXLSX R36"

include "C:\dev\Foxpro\VFPX - GitHub\WorkbookXLSX R36\VFPxWorkbookXLSX.h"

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "C:\dev\Foxpro\VFPX - GitHub\WorkbookXLSX R36\VFPxWorkbookXLSX.vcx")

cfile = 'c:\temp\XLSTEST.xlsx'

*** EXPORT D UN CURSEUR CREATE CURSOR TEST (ID c(20), descr c(100), valeur n(10,2)) INSERT INTO test VALUES ("ID1", "DESC1", 100.15) INSERT INTO test VALUES ("ID2", "DESC2", 200.24)

loExcel.Savetabletoworkbookex('TEST', cFile, .NULL., .f., 'Sheet1')

IF ni > 0

loExcel.SaveWorkBook(ni)

ELSE MESSAGEBOX("Impossible d'ouvrir le fichier") ENDIF

loExcel=null

Thierry

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/81, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33NFDILHNATXF5NSR5TW5R6EVANCNFSM6AAAAAAWFPFTCQ. You are receiving this because you are subscribed to this thread.Message ID: @.**@.>>

tpenninckx commented 1 year ago

Hello Greg, I tried to remove the line with SetCellFont, but had the same problem. It's really strange, It seems that as soon as I insert the line in row 1 and set a value in it, the file become corrupted.

ggreen86 commented 1 year ago

Thierry—

Finally found the problem. Since the saving of the table to a workbook uses in-line formatting of the text for speed reasons, the method was not adding the styles.xml file to the workbook content. So when the workbook was now read-in to the class, no basic styles definition was defined. The inserting of the row and assignment of the cell values made this apparent due to my defaulting of the format index. This index points to the styles.xml file which did not exist. So Excel raised the error and removed the style information from the affected cells during the repair. So I made several corrections – the checking for the styles based definition when creating a new style to be applied and how the format index is assigned when the values are assigned.

In my testing, the workbooks are now correctly being processed. See below the code that I used (note the use of the styles methods). I will be updating the class on VFPx as soon as I update the Release Notes today.

Greg

SET DEFAULT TO "E:\My Work\FoxPro\Projects\WorkbookXLSX"

include "VFPxWorkbookXLSX.h"

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx")

cfile = 'XLSDEBUGTEST2.xlsx'

CREATE CURSOR TEST (ID c(20), descr c(100), valeur n(10,2)) INSERT INTO test VALUES ("ID1", "DESC1", 100.15) INSERT INTO test VALUES ("ID2", "DESC2", 200.24) loExcel.Savetabletoworkbookex('TEST', cFile, .NULL., .f., 'Sheet1')

ni = loExcel.OpenXlsxWorkbook(cfile, .f., .t.) IF ni > 0 loExcel.InsertRow(ni, 1, 1, INSERT_BEFORE) nLigne = 1 FOR nCol = 1 TO 20 nVal = nCol && juste pour la lisibilité loExcel.SetCellValue(ni, 1, nLigne, nCol, nVal)

ggreen86 commented 1 year ago

Bug fixed in Release 37.