search

ggreen86 / XLSX-Workbook-Class

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

SetCellFormula #67

Closed Ajitweb closed 2 years ago

Ajitweb commented 2 years ago

Hi Greg,

Everything works fine when the the following code is run

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx")
lnWb = loExcel.CreateWorkbook("D:\T.XLSX")
lnSh = loExcel.AddSheet(lnWb, TTOC(DATETIME()))

loExcel.SetCellValue(lnWb, lnSh, 1, 1, 10)  &&ColA
loExcel.SetCellValue(lnWb, lnSh, 2, 1, 20)  &&ColA

loExcel.SetCellValue(lnWb, lnSh, 1, 2, 100)  &&ColB
loExcel.SetCellValue(lnWb, lnSh, 2, 2, 200)  &&ColB

loExcel.SetCellFormula(lnWb, lnSh, 3, 1, "=SUM(A1:A2)")
*loExcel.SetCellFormula(lnWb, lnSh, 3, 2, "=SUM(B1:B2)")
loExcel.SaveWorkbook(lnWb)

The third row, first column will give the correct total - 30

But when the second last line is uncommented (which is a total of the second column), I see that the totals are getting interchanged - that is B column is totalled in A column ??? Are you also seeing the same behaviour - or am I doing something wrong ?

Regards, Ajit

ggreen86 commented 2 years ago

Ajit--

I am not able to duplicate the problem that you are having. I tried your code below and I get the correct results for the sum() command as shown:

[cid:70397687-c126-4975-a1df-3ba71a154432]

Greg

From: Ajit Abraham @.> Sent: Monday, June 13, 2022 3:17 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] SetCellFormula (Issue #67)

Hi Greg,

Everything works fine when the the following code is run

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx") lnWb = loExcel.CreateWorkbook("D:\T.XLSX") lnSh = loExcel.AddSheet(lnWb, TTOC(DATETIME()))

loExcel.SetCellValue(lnWb, lnSh, 1, 1, 10) &&ColA loExcel.SetCellValue(lnWb, lnSh, 2, 1, 20) &&ColA

loExcel.SetCellValue(lnWb, lnSh, 1, 2, 100) &&ColB loExcel.SetCellValue(lnWb, lnSh, 2, 2, 200) &&ColB

loExcel.SetCellFormula(lnWb, lnSh, 3, 1, "=SUM(A1:A2)") *loExcel.SetCellFormula(lnWb, lnSh, 3, 2, "=SUM(B1:B2)") loExcel.SaveWorkbook(lnWb)

The third row, first column will give the correct total - 30

But when the second last line is uncommented (which is a total of the second column), I see that the totals are getting interchanged - that is B column is totalled in A column ??? Are you also seeing the same behaviour - or am I doing something wrong ?

Regards, Ajit

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

plborker commented 2 years ago

Hi Greg I got the same problem he did. Maybe it’s the excel version ? I’m using 2019 32 bit

-Philip

On Wed, Jun 15, 2022 at 6:20 PM ggreen86 @.***> wrote:

Ajit--

I am not able to duplicate the problem that you are having. I tried your code below and I get the correct results for the sum() command as shown:

[cid:70397687-c126-4975-a1df-3ba71a154432]

Greg

From: Ajit Abraham @.> Sent: Monday, June 13, 2022 3:17 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] SetCellFormula (Issue #67)

Hi Greg,

Everything works fine when the the following code is run

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx") lnWb = loExcel.CreateWorkbook("D:\T.XLSX") lnSh = loExcel.AddSheet(lnWb, TTOC(DATETIME()))

loExcel.SetCellValue(lnWb, lnSh, 1, 1, 10) &&ColA loExcel.SetCellValue(lnWb, lnSh, 2, 1, 20) &&ColA

loExcel.SetCellValue(lnWb, lnSh, 1, 2, 100) &&ColB loExcel.SetCellValue(lnWb, lnSh, 2, 2, 200) &&ColB

loExcel.SetCellFormula(lnWb, lnSh, 3, 1, "=SUM(A1:A2)") *loExcel.SetCellFormula(lnWb, lnSh, 3, 2, "=SUM(B1:B2)") loExcel.SaveWorkbook(lnWb)

The third row, first column will give the correct total - 30

But when the second last line is uncommented (which is a total of the second column), I see that the totals are getting interchanged - that is B column is totalled in A column ??? Are you also seeing the same behaviour - or am I doing something wrong ?

Regards, Ajit

— Reply to this email directly, view it on GitHub< https://github.com/ggreen86/XLSX-Workbook-Class/issues/67>, or unsubscribe< https://github.com/notifications/unsubscribe-auth/AGWB33JY5QTT6YAOGGGJUB3VO3OB3ANCNFSM5YTGHOYQ

. You are receiving this because you are subscribed to this thread.Message ID: @.***>

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

-- Philip Borkholder (Sent from mobile phone)

ggreen86 commented 2 years ago

Can you send the generated spreadsheet so that I can see it directly. Thank you.

From: PHILIP BORKHOLDER @.> Sent: Wednesday, June 15, 2022 6:28 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] SetCellFormula (Issue #67)

Hi Greg I got the same problem he did. Maybe it’s the excel version ? I’m using 2019 32 bit

-Philip

On Wed, Jun 15, 2022 at 6:20 PM ggreen86 @.***> wrote:

Ajit--

I am not able to duplicate the problem that you are having. I tried your code below and I get the correct results for the sum() command as shown:

[cid:70397687-c126-4975-a1df-3ba71a154432]

Greg

From: Ajit Abraham @.> Sent: Monday, June 13, 2022 3:17 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] SetCellFormula (Issue #67)

Hi Greg,

Everything works fine when the the following code is run

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx") lnWb = loExcel.CreateWorkbook("D:\T.XLSX") lnSh = loExcel.AddSheet(lnWb, TTOC(DATETIME()))

loExcel.SetCellValue(lnWb, lnSh, 1, 1, 10) &&ColA loExcel.SetCellValue(lnWb, lnSh, 2, 1, 20) &&ColA

loExcel.SetCellValue(lnWb, lnSh, 1, 2, 100) &&ColB loExcel.SetCellValue(lnWb, lnSh, 2, 2, 200) &&ColB

loExcel.SetCellFormula(lnWb, lnSh, 3, 1, "=SUM(A1:A2)") *loExcel.SetCellFormula(lnWb, lnSh, 3, 2, "=SUM(B1:B2)") loExcel.SaveWorkbook(lnWb)

The third row, first column will give the correct total - 30

But when the second last line is uncommented (which is a total of the second column), I see that the totals are getting interchanged - that is B column is totalled in A column ??? Are you also seeing the same behaviour - or am I doing something wrong ?

Regards, Ajit

— Reply to this email directly, view it on GitHub< https://github.com/ggreen86/XLSX-Workbook-Class/issues/67>, or unsubscribe< https://github.com/notifications/unsubscribe-auth/AGWB33JY5QTT6YAOGGGJUB3VO3OB3ANCNFSM5YTGHOYQ

. You are receiving this because you are subscribed to this thread.Message ID: @.***>

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

-- Philip Borkholder (Sent from mobile phone)

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/67#issuecomment-1157012771, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33OUWT6PUSHU3YG2MNLVPJKINANCNFSM5YTGHOYQ. You are receiving this because you commented.Message ID: @.***>

Ajitweb commented 2 years ago

Hi Greg,

T.xlsx - > 2 cols T1.xlsx - > 3 cols T2.xlsx -> 4 cols

I am using VFP Advanced 10.1

The code for T2.xlsx is :

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "VFPxWorkbookXLSX.vcx")
lnWb = loExcel.CreateWorkbook("D:\T2.XLSX")
lnSh = loExcel.AddSheet(lnWb, TTOC(DATETIME()))

loExcel.SetCellValue(lnWb, lnSh, 1, 1, 10)  &&ColA
loExcel.SetCellValue(lnWb, lnSh, 2, 1, 20)  &&ColA

loExcel.SetCellValue(lnWb, lnSh, 1, 2, 100)  &&ColB
loExcel.SetCellValue(lnWb, lnSh, 2, 2, 200)  &&ColB

loExcel.SetCellValue(lnWb, lnSh, 1, 3, 1000)  &&ColC
loExcel.SetCellValue(lnWb, lnSh, 2, 3, 2000)  &&ColC

loExcel.SetCellValue(lnWb, lnSh, 1, 4, 10000)  &&ColD
loExcel.SetCellValue(lnWb, lnSh, 2, 4, 20000)  &&ColD

loExcel.SetCellFormula(lnWb, lnSh, 3, 1, "=SUM(A1:A2)")
loExcel.SetCellFormula(lnWb, lnSh, 3, 2, "=SUM(B1:B2)")
loExcel.SetCellFormula(lnWb, lnSh, 3, 3, "=SUM(C1:C2)")
loExcel.SetCellFormula(lnWb, lnSh, 3, 4, "=SUM(D1:D2)")
loExcel.SaveWorkbook(lnWb)
ggreen86 commented 2 years ago

Ajit--

Please run your example code with a breakpoint just before the last line "loExcel.SaveWorkbook(lnWb)". Then browse the cursor XL_FORMULA; you should see the following contents:

image

image

image

image

This shows the contents of the formula being assigned to the correct column, row in the spreadsheet. Please check this to see if this is in fact what you are seeing. If not, then set a breakpoint at each call to the SetCellFormula() and see what is happening as the formula is being added to the cursor. In particular, what is being passed/assigned as the columns.

Greg

Ajitweb commented 2 years ago

Hi Greg, The Xl_formulas cursor is being populated exactly like you have shown above. So I checked the code when the xml is being created for the formulae.

The writesheetxmls method: CASE xl_cells.datatype = DATA_TYPE_FORMULA The lcCellFormula variable is being populated before the SEEK. I moved the line after the SEEKing in Xl_formulas cursor and all is well I am working with your latest Beta 10

Thank you Greg for giving us this utility. For me it is a huge time saver.

Regards, Ajit

ggreen86 commented 2 years ago

Ajit--

I have just uploaded a new version. I thought I had uploaded this version earlier as it adds new features; it is at beta 3. I have not had time to work on the documentation for the changes (and corrections to the documentation), so I have not yet moved it from beta to production.

Please test with the new version and let me know the results. I am sorry that I did not get this released sooner. Thank you.

Greg

From: Ajit Abraham @.> Sent: Thursday, June 16, 2022 10:43 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; State change @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] SetCellFormula (Issue #67)

Hi Greg, The Xl_formulas cursor is being populated exactly like you have shown above. So I checked the code when the xml is being created for the formulae.

The writesheetxmls method: CASE xl_cells.datatype = DATA_TYPE_FORMULA The lcCellFormula variable is being populated before the SEEK. I moved the line after the SEEKing in Xl_formulas cursor and all is well I am working with your latest Beta 10

Thank you Greg for giving us this utility. For me it is a huge time saver.

Regards, Ajit

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/67#issuecomment-1157742623, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33MPUDTC5I7VVKE4PEDVPM4SDANCNFSM5YTGHOYQ. You are receiving this because you modified the open/close state.Message ID: @.***>