search

ggreen86 / XLSX-Workbook-Class

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

A sample from GSheets #107

Closed jhernancanom closed 7 months ago

jhernancanom commented 7 months ago

A sample from GSheets

Good morning, Greg.

I am attaching an XLSx that we got from downloading some sheet from a Google Sheet.

My problem is that when I run

M.cData = nvl(loExcel.GetCellValue(lnWb, loSheets.List[lnSh, 1], lnRow, 1),'')

I got an empty value for all the cells in all the rows and cols of the sheet.

I change some data for sensitive info.

Preamble: There is a (complex) sheet that is maintained by another colleage for receiving several data via an AppSheet; it consists of another several complex worksheets, all in one book.

For we could read that data, it was necessary to create one sheet more, with somethig similar in Excel to In the cell A1 of the new sheet there is this formula:

+Main_Sheet_01!A1

And this formula was replicated for near one hundred cols and one thousand rows (normally the real size is 50 cols and 150-200 rows, every day).

The new sheet was created because getting the data directly from the real main sheet was problematic giving near seven cols with garbage data, three of them calculated and necessary for importing to my app.

I am reading the data via standard Excel automation, but all we know that this is not a good solution, because standard automation gives colateral issues that we are receiving.

Now that I am trying to change to using VFPxWorkbookXLSX, I am getting empty values in all cells.

I can see that the sheet has an strange formula in all cells, but I am not sure if this is a problem or not, and/or if this need to be resolved, and/or how to resolve.

If I could do something with VFPxWorkbookXLSX when OpenXlsxWorkbook, I will do it.

Thanks for sharing.

Hoja_de_Prueba_2024_01_14_11_03_03.XLSX

ggreen86 commented 7 months ago

Thank you for submitting the workbook file. I will look into this over the next several days to see where the problem is occurring. First attempt at reading the workbook appears to be correct - the cells are being populated with a value in the cursors. Will let you know my progress.

ggreen86 commented 7 months ago

The current method for opening an existing spreadsheet does not store the cell value when the cell has a formula definition assigned. Instead, the formula is stored into the cursors and the value is ignored. I will look into also storing the cell value when it is in the xml file.

At this time, there is not any errors with the current implementation of the class; it is working as designed.

ggreen86 commented 7 months ago

I have updated the class method GetCellValue() to now also get the cell value that is calculated from a formula. The class does not calculate the formula; rather, the formula must be calculated by another spreadsheet program first. All values are returned as a string type. See Release 40.

jhernancanom commented 7 months ago

Many-many thanks, Greg.

It sounds fine. I will download and see.

By.

El mié, 17 ene 2024 a la(s) 10:16 a.m., ggreen86 escribió:

I have updated the class method GetCellValue() to now also get the cell value that is calculated from a formula. The class does not calculate the formula; rather, the formula must be calculated by another spreadsheet program first. All values are returned as a string type. See Release 40.

— Reply to this email directly, view it on GitHub https://github.com/ggreen86/XLSX-Workbook-Class/issues/107#issuecomment-1896023559, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABMMTXJOVD5VUA4MC24AJV3YO7TNPAVCNFSM6AAAAABB2KZE3KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQOJWGAZDGNJVHE . You are receiving this because you authored the thread.Message ID: @.***>