Error after creating a file with Savetableworkbookex

[tpenninckx]

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')

---

• Ouverture d'un fichier
  ni = loExcel.OpenXlsxWorkbook(cfile,.f.,.t.)

IF ni > 0

• On insère une ligne en ligne 1
  loExcel.InsertRow(ni,1,1,INSERT_BEFORE)

• Met le numéro de la colonne dans chaque cellule de cette ligne (jusque 20)
  nLigne = 1
  FOR nCol = 1 TO 20
  nVal = nCol && juste pour la lisibilité
  loExcel.SetCellValue(ni,1, nLigne, nCol, nVal)

  • formatte le texte en rouge
    loexcel.SetCellFont(ni,1,nLigne,nCol,'Calibri',10,.f.,.f.,RGB(255,0,0),.f.,.f.,FONT_VERTICAL_BASELINE)
    NEXT

loExcel.SaveWorkBook(ni)

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

---

loExcel=null

Thierry

[ggreen86]

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')

…

________________________________ * Ouverture d'un fichier ni = loExcel.OpenXlsxWorkbook(cfile,.f.,.t.) IF ni > 0 * On insère une ligne en ligne 1 loExcel.InsertRow(ni,1,1,INSERT_BEFORE) * Met le numéro de la colonne dans chaque cellule de cette ligne (jusque 20) nLigne = 1 FOR nCol = 1 TO 20 nVal = nCol && juste pour la lisibilité loExcel.SetCellValue(ni,1, nLigne, nCol, nVal) * formatte le texte en rouge loexcel.SetCellFont(ni,1,nLigne,nCol,'Calibri',10,.f.,.f.,RGB(255,0,0),.f.,.f.,FONT_VERTICAL_BASELINE) NEXT loExcel.SaveWorkBook(ni) ELSE MESSAGEBOX("Impossible d'ouvrir le fichier") ENDIF

________________________________ loExcel=null Thierry — Reply to this email directly, view it on GitHub<#81>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AGWB33NFDILHNATXF5NSR5TW5R6EVANCNFSM6AAAAAAWFPFTCQ>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.******@***.***>>

[tpenninckx]

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]

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) * loexcel.SetCellFont(ni,1,nLigne,nCol,'Calibri',12,.f.,.f.,RGB(255,0,0),.f.,.f.,FONT_VERTICAL_BASELINE) && Causes an error in the workbook due to styles base not defined since it was not defined in the save method above ENDFOR lnStyle1 = loExcel.CreateFormatStyle(ni) && Create the base style definition loExcel.AddStyleFont(ni, lnStyle1, "Calibri", 12, False, False, RGB(255,0,0)) loExcel.SetCellStyleRange(ni, 1, 1, 1, 1, 20, lnStyle1) lnStyle2 = loExcel.CreateFormatStyle(ni) && Create the base style definition loExcel.AddStyleFont(ni, lnStyle2, "Times New Roman", 12, False, False, RGB(0,255,0)) loExcel.SetCellStyleRange(ni, 1, 2, 1, 3, 3, lnStyle2) loExcel.SaveWorkBook(ni) ENDIF

[ggreen86]

Bug fixed in Release 37.