search

ggreen86 / XLSX-Workbook-Class

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

Document created by SaveTabletoWorkbookEx cannot be reopened and formatted in xlsx-workbook-class #66

Closed rodneyea closed 1 year ago

rodneyea commented 2 years ago

Hi

I am using version 33

  1. If I create a document using SaveTabletoWorkbookEx I can open it in excel and edit reformat at will.

  2. If I re-open the document just created with OpenXLSXWorkbook, apply some formatting, and column width changes, and the save it with SaveWorkbook(1), the document is corrupted and can no longer be opened in excel.

If I open a document created in 1 in excel and save it as something else, I can do step 2 without an issue.

Many Thanks Rodney

ggreen86 commented 2 years ago

Rodney--

Thank you for the bug report. Can you please provide a working example that I can debug with?

Greg

From: rodneyea @.> Sent: Tuesday, March 29, 2022 12:42 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] Document created by SaveTabletoWorkbookEx cannot be reopened and formatted in xlsx-workbook-class (Issue #66)

Hi

I am using version 33

  1. If I create a document using SaveTabletoWorkbookEx I can open it in excel and edit reformat at will.

  2. If I re-open the document just created with OpenXLSXWorkbook, apply some formatting, and column width changes, and the save it with SaveWorkbook(1), the document is corrupted and can no longer be opened in excel.

If I open a document created in 1 in excel and save it as something else, I can do step 2 without an issue.

Many Thanks Rodney

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

rodneyea commented 2 years ago

Hi Greg

Many thanks for your response.

Code as follows…

Best Regards Rodney

INCLUDE "vfpxworkbookxlsx.h"

oXl=NEWOBJECT("VFPxWorkbookXLSX","VFPxWorkbookXLSX.Vcx") lcExcel = "C:\temp\mtest.xlsx" &&+ SYS(2015)+".xlsx" lcTable = "Tquery"

CREATE CURSOR TQuery (InvoiceNo C(10),LineItemType C(10),caccount C(10),; Name C(20),EnteredBy C(20),cReference C(10),LineItemDate D,; LineItemDateEntered D,LineTotal N(12,2),nVat N(12,2)) INSERT INTO TQuery VALUES ("123213","Pay","3213123","Test 1 ","Me","123443",DATE(),DATE(),1234,11) INSERT INTO TQuery VALUES ("123213","Pay","3213123","Test 1 ","Me","123443",DATE(),DATE(),1234,11) INSERT INTO TQuery VALUES ("123213","Pay","3213123","Test 1 ","Me","123443",DATE(),DATE(),1234,11)

 DIMENSION acol[10,2]
 acol[1,1]='InvoiceNo'
 acol[2,1]='LineItemType'
 acol[3,1]='caccount'
 acol[4,1]='Name'
 acol[5,1]='EnteredBy'
 acol[6,1]='cReference'
 acol[7,1]='LineItemDate'
 acol[8,1]='LineItemDateEntered'
 acol[9,1]='LineTotal'
 acol[10,1]='nVat'
 acol[1,2]='Invoice No'
 acol[2,2]='Type'
 acol[3,2]='Account'
 acol[4,2]='Name'
 acol[5,2]='Entered By'
 acol[6,2]='Audit'
 acol[7,2]='Date'
 acol[8,2]='Date Entered'
 acol[9,2]='Total'
 acol[10,2]='Vat'

oXl=NEWOBJECT("VFPxWorkbookXLSX","VFPxWorkbookXLSX.Vcx") luret=oXl.SaveTabletoWorkbookEx(lcTable, @.***) USE IN Tquery oXl=NULL

oXl=NEWOBJECT("VFPxWorkbookXLSX","VFPxWorkbookXLSX.Vcx") lnwb=oXl.OpenXLSXWorkbook(lcExcel) IF lnWb=0 MESSAGEBOX("Could not open Excel Report") RETURN .F. ENDIF lnRows=oXl.GetLastRowNumber(lnWb,1) oXl.SetCellAlignmentRange(lnWb, 1, 1, 9, lnRows,10, CELL_HORIZ_ALIGN_RIGHT, CELL_VERT_ALIGN_CENTER) oXl.SetCellnumberformatRange(lnWb, 1, 1, 9, lnRows,10, CELL_FORMAT_CURRENCY_RED_PAREN) oXl.SetColumnwidth(lnWb,1,1,12) oXl.SetColumnwidth(lnWb,1,4,30) oXl.SetColumnwidth(lnWb,1,5,15) oXl.SetColumnwidth(lnWb,1,7,12) oXl.SetColumnwidth(lnWb,1,8,12) oXl.SetColumnwidth(lnWb,1,9,12)

IF !oXl.SaveWorkbook(1) MESSAGEBOX("Could not save Excel Report") ENDIF

From: ggreen86 @.> Sent: 29 Mar 2022 18:24 To: ggreen86/XLSX-Workbook-Class @.> Cc: rodneyea @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Document created by SaveTabletoWorkbookEx cannot be reopened and formatted in xlsx-workbook-class (Issue #66)

Rodney--

Thank you for the bug report. Can you please provide a working example that I can debug with?

Greg

From: rodneyea @.<mailto:@.>> Sent: Tuesday, March 29, 2022 12:42 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: Subscribed @.<mailto:@.>> Subject: [ggreen86/XLSX-Workbook-Class] Document created by SaveTabletoWorkbookEx cannot be reopened and formatted in xlsx-workbook-class (Issue #66)

Hi

I am using version 33

  1. If I create a document using SaveTabletoWorkbookEx I can open it in excel and edit reformat at will.

  2. If I re-open the document just created with OpenXLSXWorkbook, apply some formatting, and column width changes, and the save it with SaveWorkbook(1), the document is corrupted and can no longer be opened in excel.

If I open a document created in 1 in excel and save it as something else, I can do step 2 without an issue.

Many Thanks Rodney

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

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

ggreen86 commented 2 years ago

Rodney--

I believe I have fixed the issue that you are reporting. This I think was also tied to another issue that was previously reported and fixed (but not yet released as I was making other changes that needed to be finalized and tested). I will be uploading the new class as beta in a couple of days; or you can send me your email and I will forward to you the fix now for you to test.

Greg

From: rodneyea @.> Sent: Wednesday, March 30, 2022 3:54 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Document created by SaveTabletoWorkbookEx cannot be reopened and formatted in xlsx-workbook-class (Issue #66)

Hi Greg

Many thanks for your response.

Code as follows…

Best Regards Rodney

INCLUDE "vfpxworkbookxlsx.h"

oXl=NEWOBJECT("VFPxWorkbookXLSX","VFPxWorkbookXLSX.Vcx") lcExcel = "C:\temp\mtest.xlsx" &&+ SYS(2015)+".xlsx" lcTable = "Tquery"

CREATE CURSOR TQuery (InvoiceNo C(10),LineItemType C(10),caccount C(10),; Name C(20),EnteredBy C(20),cReference C(10),LineItemDate D,; LineItemDateEntered D,LineTotal N(12,2),nVat N(12,2)) INSERT INTO TQuery VALUES ("123213","Pay","3213123","Test 1 ","Me","123443",DATE(),DATE(),1234,11) INSERT INTO TQuery VALUES ("123213","Pay","3213123","Test 1 ","Me","123443",DATE(),DATE(),1234,11) INSERT INTO TQuery VALUES ("123213","Pay","3213123","Test 1 ","Me","123443",DATE(),DATE(),1234,11)

DIMENSION acol[10,2] acol[1,1]='InvoiceNo' acol[2,1]='LineItemType' acol[3,1]='caccount' acol[4,1]='Name' acol[5,1]='EnteredBy' acol[6,1]='cReference' acol[7,1]='LineItemDate' acol[8,1]='LineItemDateEntered' acol[9,1]='LineTotal' acol[10,1]='nVat' acol[1,2]='Invoice No' acol[2,2]='Type' acol[3,2]='Account' acol[4,2]='Name' acol[5,2]='Entered By' acol[6,2]='Audit' acol[7,2]='Date' acol[8,2]='Date Entered' acol[9,2]='Total' acol[10,2]='Vat'

oXl=NEWOBJECT("VFPxWorkbookXLSX","VFPxWorkbookXLSX.Vcx") luret=oXl.SaveTabletoWorkbookEx(lcTable, @.***) USE IN Tquery oXl=NULL

oXl=NEWOBJECT("VFPxWorkbookXLSX","VFPxWorkbookXLSX.Vcx") lnwb=oXl.OpenXLSXWorkbook(lcExcel) IF lnWb=0 MESSAGEBOX("Could not open Excel Report") RETURN .F. ENDIF lnRows=oXl.GetLastRowNumber(lnWb,1) oXl.SetCellAlignmentRange(lnWb, 1, 1, 9, lnRows,10, CELL_HORIZ_ALIGN_RIGHT, CELL_VERT_ALIGN_CENTER) oXl.SetCellnumberformatRange(lnWb, 1, 1, 9, lnRows,10, CELL_FORMAT_CURRENCY_RED_PAREN) oXl.SetColumnwidth(lnWb,1,1,12) oXl.SetColumnwidth(lnWb,1,4,30) oXl.SetColumnwidth(lnWb,1,5,15) oXl.SetColumnwidth(lnWb,1,7,12) oXl.SetColumnwidth(lnWb,1,8,12) oXl.SetColumnwidth(lnWb,1,9,12)

IF !oXl.SaveWorkbook(1) MESSAGEBOX("Could not save Excel Report") ENDIF

From: ggreen86 @.> Sent: 29 Mar 2022 18:24 To: ggreen86/XLSX-Workbook-Class @.> Cc: rodneyea @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Document created by SaveTabletoWorkbookEx cannot be reopened and formatted in xlsx-workbook-class (Issue #66)

Rodney--

Thank you for the bug report. Can you please provide a working example that I can debug with?

Greg

From: rodneyea @.<mailto:@.>> Sent: Tuesday, March 29, 2022 12:42 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: Subscribed @.<mailto:@.>> Subject: [ggreen86/XLSX-Workbook-Class] Document created by SaveTabletoWorkbookEx cannot be reopened and formatted in xlsx-workbook-class (Issue #66)

Hi

I am using version 33

  1. If I create a document using SaveTabletoWorkbookEx I can open it in excel and edit reformat at will.

  2. If I re-open the document just created with OpenXLSXWorkbook, apply some formatting, and column width changes, and the save it with SaveWorkbook(1), the document is corrupted and can no longer be opened in excel.

If I open a document created in 1 in excel and save it as something else, I can do step 2 without an issue.

Many Thanks Rodney

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

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

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

rodneyea commented 2 years ago

Hi Greg

Many Thanks for fixing.

My mail is @.**@.>

Rodney

From: ggreen86 @.> Sent: 31 Mar 2022 15:58 To: ggreen86/XLSX-Workbook-Class @.> Cc: rodneyea @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Document created by SaveTabletoWorkbookEx cannot be reopened and formatted in xlsx-workbook-class (Issue #66)

Rodney--

I believe I have fixed the issue that you are reporting. This I think was also tied to another issue that was previously reported and fixed (but not yet released as I was making other changes that needed to be finalized and tested). I will be uploading the new class as beta in a couple of days; or you can send me your email and I will forward to you the fix now for you to test.

Greg

From: rodneyea @.<mailto:@.>> Sent: Wednesday, March 30, 2022 3:54 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Document created by SaveTabletoWorkbookEx cannot be reopened and formatted in xlsx-workbook-class (Issue #66)

Hi Greg

Many thanks for your response.

Code as follows…

Best Regards Rodney

INCLUDE "vfpxworkbookxlsx.h"

oXl=NEWOBJECT("VFPxWorkbookXLSX","VFPxWorkbookXLSX.Vcx") lcExcel = "C:\temp\mtest.xlsx" &&+ SYS(2015)+".xlsx" lcTable = "Tquery"

CREATE CURSOR TQuery (InvoiceNo C(10),LineItemType C(10),caccount C(10),; Name C(20),EnteredBy C(20),cReference C(10),LineItemDate D,; LineItemDateEntered D,LineTotal N(12,2),nVat N(12,2)) INSERT INTO TQuery VALUES ("123213","Pay","3213123","Test 1 ","Me","123443",DATE(),DATE(),1234,11) INSERT INTO TQuery VALUES ("123213","Pay","3213123","Test 1 ","Me","123443",DATE(),DATE(),1234,11) INSERT INTO TQuery VALUES ("123213","Pay","3213123","Test 1 ","Me","123443",DATE(),DATE(),1234,11)

DIMENSION acol[10,2] acol[1,1]='InvoiceNo' acol[2,1]='LineItemType' acol[3,1]='caccount' acol[4,1]='Name' acol[5,1]='EnteredBy' acol[6,1]='cReference' acol[7,1]='LineItemDate' acol[8,1]='LineItemDateEntered' acol[9,1]='LineTotal' acol[10,1]='nVat' acol[1,2]='Invoice No' acol[2,2]='Type' acol[3,2]='Account' acol[4,2]='Name' acol[5,2]='Entered By' acol[6,2]='Audit' acol[7,2]='Date' acol[8,2]='Date Entered' acol[9,2]='Total' acol[10,2]='Vat'

oXl=NEWOBJECT("VFPxWorkbookXLSX","VFPxWorkbookXLSX.Vcx") luret=oXl.SaveTabletoWorkbookEx(lcTable, @.<mailto:@.>) USE IN Tquery oXl=NULL

oXl=NEWOBJECT("VFPxWorkbookXLSX","VFPxWorkbookXLSX.Vcx") lnwb=oXl.OpenXLSXWorkbook(lcExcel) IF lnWb=0 MESSAGEBOX("Could not open Excel Report") RETURN .F. ENDIF lnRows=oXl.GetLastRowNumber(lnWb,1) oXl.SetCellAlignmentRange(lnWb, 1, 1, 9, lnRows,10, CELL_HORIZ_ALIGN_RIGHT, CELL_VERT_ALIGN_CENTER) oXl.SetCellnumberformatRange(lnWb, 1, 1, 9, lnRows,10, CELL_FORMAT_CURRENCY_RED_PAREN) oXl.SetColumnwidth(lnWb,1,1,12) oXl.SetColumnwidth(lnWb,1,4,30) oXl.SetColumnwidth(lnWb,1,5,15) oXl.SetColumnwidth(lnWb,1,7,12) oXl.SetColumnwidth(lnWb,1,8,12) oXl.SetColumnwidth(lnWb,1,9,12)

IF !oXl.SaveWorkbook(1) MESSAGEBOX("Could not save Excel Report") ENDIF

From: ggreen86 @.<mailto:@.>> Sent: 29 Mar 2022 18:24 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: rodneyea @.<mailto:@.>>; Author @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Document created by SaveTabletoWorkbookEx cannot be reopened and formatted in xlsx-workbook-class (Issue #66)

Rodney--

Thank you for the bug report. Can you please provide a working example that I can debug with?

Greg

From: rodneyea @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: Tuesday, March 29, 2022 12:42 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: Subscribed @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: [ggreen86/XLSX-Workbook-Class] Document created by SaveTabletoWorkbookEx cannot be reopened and formatted in xlsx-workbook-class (Issue #66)

Hi

I am using version 33

  1. If I create a document using SaveTabletoWorkbookEx I can open it in excel and edit reformat at will.

  2. If I re-open the document just created with OpenXLSXWorkbook, apply some formatting, and column width changes, and the save it with SaveWorkbook(1), the document is corrupted and can no longer be opened in excel.

If I open a document created in 1 in excel and save it as something else, I can do step 2 without an issue.

Many Thanks Rodney

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

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/66#issuecomment-1082163924, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AI6ORIZZXR6MYYE6WWHEFG3VCM4CVANCNFSM5R7AJ2YA. You are receiving this because you authored the thread.Message ID: @.**@.mailto:***@***.******@***.***>>

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

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

ggreen86 commented 1 year ago

Issue fixed.