Harbour-J commented 2 years ago

... loXL.SetCellValue(lnWb, lnSheet, 1, 1, 'Header') loXL.SetCellValue(lnWb, lnSheet, 2, 1, 2) ... works fine to create a 2-cell spreadsheet. ... loXL.SetCellValue(lnWb, lnSheet, 1, 1, 1) loXL.SetCellValue(lnWb, lnSheet, 2, 1, 2) ... gives as error message from Excel "We found a problem with some content in ..."

ggreen86 commented 2 years ago

Hello--

I cannot reproduce the error that you are seeing. Below is my code:

Test 1:

lnWb = loExcel.CreateWorkbook("FirstRowTest.xlsx") lnSh = loExcel.AddSheet(lnWb, "Sheet1")

loExcel.SetCellValue(lnWb, lnSh, 2, 1, 20) && Sheet1, Row 2, column A loExcel.SaveWorkbook(lnWb)

Test 2:

lnWb = loExcel.CreateWorkbook("FirstRowTest.xlsx") lnSh = loExcel.AddSheet(lnWb, "Sheet1")

loExcel.SetCellValue(lnWb, lnSh, 2, 2, 20) && Sheet1, Row 2, column B loExcel.SaveWorkbook(lnWb)

Test 3:

lnWb = loExcel.CreateWorkbook("FirstRowTest.xlsx") lnSh = loExcel.AddSheet(lnWb, "Sheet1") lnSh = loExcel.AddSheet(lnWb, "Sheet2")

loExcel.SetCellValue(lnWb, lnSh, 2, 2, 20) && Sheet2, Row 2, column B loExcel.SaveWorkbook(lnWb)

In the above tests, I am able to open the spreadsheet successfully using Excel 2016. If you are still experiencing the problem, please send me the spreadsheet file that is created by your program code and the spreadsheet file that is opened by Excel with you applying the fix. I believe that Excel 2010 might not work as another user has experienced issues trying to open spreadsheets. If you are also using Excel 2010 (or any of your users will be) please send me the two files so that I can analyze the difference between the generated file and the Excel corrected file. Hopefully, I can identify the underlying issue with Excel 2010.

Thank you.

Greg

From: Harbour-J @.> Sent: Thursday, August 4, 2022 10:45 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

... loXL.SetCellValue(lnWb, lnSheet, 1, 1, 'Header') loXL.SetCellValue(lnWb, lnSheet, 2, 1, 2) ... works fine to create a 2-cell spreadsheet. ... loXL.SetCellValue(lnWb, lnSheet, 1, 1, 1) loXL.SetCellValue(lnWb, lnSheet, 2, 1, 2) ... gives as error message from Excel "We found a problem with some content in ..."

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

Harbour-J commented 2 years ago

Hi, Greg It's not 2010 - Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20292) 32-bit

Sorry, I think I confused you by using 1 & 2 as my example numbers as well as the row numbers. Here's some clearer code.

loXL = CREATEOBJECT('VFPxWorkbookXLSX') lnWb = loXL.CreateWorkbook('good.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('good.xlsx')

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('bad.xlsx')

This fails to open bad.xlsx. Curiously, if I uncomment the commented line it works. Also, if I change it to write to A1 and B1, it fails in the same way. Files attached.

Many thanks

John

bad.xlsx good.xlsx

ggreen86 commented 2 years ago

John--

I just tried your code below and it successfully created a spreadsheet that I could open.

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb)

(note the commented line). Please open the 'bad' spreadsheet with your version of Excel and then allow it to 'fix' the spreadsheet. Then send the 'fixed' version to me so that I can compare with the original. Thank you.

Greg

From: Harbour-J @.> Sent: Friday, August 5, 2022 10:05 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg It's not 2010 - Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20292) 32-bit

Sorry, I think I confused you by using 1 & 2 as my example numbers as well as the row numbers. Here's some clearer code.

loXL = CREATEOBJECT('VFPxWorkbookXLSX') lnWb = loXL.CreateWorkbook('good.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('good.xlsx')

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('bad.xlsx')

This fails to open bad.xlsx. Curiously, if I uncomment the commented line it works. Also, if I change it to write to A1 and B1, it fails in the same way. Files attached.

Many thanks

John

bad.xlsxhttps://github.com/ggreen86/XLSX-Workbook-Class/files/9269308/bad.xlsx good.xlsxhttps://github.com/ggreen86/XLSX-Workbook-Class/files/9269309/good.xlsx

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

Harbour-J commented 2 years ago

Hi,

Were you able to open the bad.xlsx that I sent you?

This is the “log file listing repairs” that Excel creates:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

error153160_01.xml

Errors were detected in file 'C:\dev\Sm\bad - Copy.xlsx'

Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

The “fixed” s/s (attached) is empty, to the untrained eye at least.

Regards

John

P.S. I’m in the UK where it’s late Friday p.m. Am about to disappear till Monday.

From: ggreen86 @.> Sent: 05 August 2022 16:03 To: ggreen86/XLSX-Workbook-Class @.> Cc: HOITJohnHarbour @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I just tried your code below and it successfully created a spreadsheet that I could open.

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb)

(note the commented line). Please open the 'bad' spreadsheet with your version of Excel and then allow it to 'fix' the spreadsheet. Then send the 'fixed' version to me so that I can compare with the original. Thank you.

Greg

From: Harbour-J @.<mailto:@.>> Sent: Friday, August 5, 2022 10:05 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg It's not 2010 - Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20292) 32-bit

Sorry, I think I confused you by using 1 & 2 as my example numbers as well as the row numbers. Here's some clearer code.

loXL = CREATEOBJECT('VFPxWorkbookXLSX') lnWb = loXL.CreateWorkbook('good.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('good.xlsx')

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('bad.xlsx')

This fails to open bad.xlsx. Curiously, if I uncomment the commented line it works. Also, if I change it to write to A1 and B1, it fails in the same way. Files attached.

Many thanks

John

bad.xlsxhttps://github.com/ggreen86/XLSX-Workbook-Class/files/9269308/bad.xlsx<https://github.com/ggreen86/XLSX-Workbook-Class/files/9269308/bad.xlsx> good.xlsxhttps://github.com/ggreen86/XLSX-Workbook-Class/files/9269309/good.xlsx<https://github.com/ggreen86/XLSX-Workbook-Class/files/9269309/good.xlsx>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206496252<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206496252>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33PK2SL6CPONE7TILRTVXUNT3ANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33PK2SL6CPONE7TILRTVXUNT3ANCNFSM55S2RCKQ>. 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/69#issuecomment-1206557382, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ. You are receiving this because you authored the thread.Message ID: @.**@.>>

ggreen86 commented 2 years ago

John--

I found the problem. Your 'bad.xlxs' file is missing the internal file sheet1.xml which defines the sheet values. Not sure why it is missing. Please try the below code and let me know the results. Also, make sure you are using the most current version of the class from VFPx.

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "..\VFPxWorkbookXLSX.vcx") lnWb = loExcel.CreateWorkbook("FirstRowTest.xlsx")

lnSh = loExcel.AddSheet(lnWb, "Sheet1") *loExcel.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loExcel.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loExcel.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2

loExcel.SaveWorkbook(lnWb)

Greg

From: Harbour-J @.> Sent: Friday, August 5, 2022 11:32 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi,

Were you able to open the bad.xlsx that I sent you?

This is the “log file listing repairs” that Excel creates:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

error153160_01.xml

Errors were detected in file 'C:\dev\Sm\bad - Copy.xlsx'

Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

The “fixed” s/s (attached) is empty, to the untrained eye at least.

Regards

John

P.S. I’m in the UK where it’s late Friday p.m. Am about to disappear till Monday.

From: ggreen86 @.> Sent: 05 August 2022 16:03 To: ggreen86/XLSX-Workbook-Class @.> Cc: HOITJohnHarbour @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I just tried your code below and it successfully created a spreadsheet that I could open.

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb)

(note the commented line). Please open the 'bad' spreadsheet with your version of Excel and then allow it to 'fix' the spreadsheet. Then send the 'fixed' version to me so that I can compare with the original. Thank you.

Greg

From: Harbour-J @.<mailto:@.>> Sent: Friday, August 5, 2022 10:05 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg It's not 2010 - Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20292) 32-bit

Sorry, I think I confused you by using 1 & 2 as my example numbers as well as the row numbers. Here's some clearer code.

loXL = CREATEOBJECT('VFPxWorkbookXLSX') lnWb = loXL.CreateWorkbook('good.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('good.xlsx')

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('bad.xlsx')

This fails to open bad.xlsx. Curiously, if I uncomment the commented line it works. Also, if I change it to write to A1 and B1, it fails in the same way. Files attached.

Many thanks

John

bad.xlsxhttps://github.com/ggreen86/XLSX-Workbook-Class/files/9269308/bad.xlsx<https://github.com/ggreen86/XLSX-Workbook-Class/files/9269308/bad.xlsx> good.xlsxhttps://github.com/ggreen86/XLSX-Workbook-Class/files/9269309/good.xlsx<https://github.com/ggreen86/XLSX-Workbook-Class/files/9269309/good.xlsx>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206496252<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206496252>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33PK2SL6CPONE7TILRTVXUNT3ANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33PK2SL6CPONE7TILRTVXUNT3ANCNFSM55S2RCKQ>. 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/69#issuecomment-1206557382, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ. 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/69#issuecomment-1206587635, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ. You are receiving this because you commented.Message ID: @.***>

Harbour-J commented 2 years ago

Hi, Greg

I’m running R32. I’ve recopied the .vcx, .vct and .h files from the .zip just in case.

I ran the code as written, except I needed "..\ClassLib\VFPxWorkbookXLSX.vcx".

@.***

John

From: ggreen86 @.> Sent: 05 August 2022 17:18 To: ggreen86/XLSX-Workbook-Class @.> Cc: HOITJohnHarbour @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I found the problem. Your 'bad.xlxs' file is missing the internal file sheet1.xml which defines the sheet values. Not sure why it is missing. Please try the below code and let me know the results. Also, make sure you are using the most current version of the class from VFPx.

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "..\VFPxWorkbookXLSX.vcx") lnWb = loExcel.CreateWorkbook("FirstRowTest.xlsx")

lnSh = loExcel.AddSheet(lnWb, "Sheet1") *loExcel.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loExcel.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loExcel.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2

loExcel.SaveWorkbook(lnWb)

Greg

From: Harbour-J @.<mailto:@.>> Sent: Friday, August 5, 2022 11:32 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi,

Were you able to open the bad.xlsx that I sent you?

This is the “log file listing repairs” that Excel creates:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <recoveryLog xmlns=http://schemas.openxmlformats.org/spreadsheetml/2006/main http://schemas.openxmlformats.org/spreadsheetml/2006/main>

error153160_01.xml

Errors were detected in file 'C:\dev\Sm\bad - Copy.xlsx'

Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

The “fixed” s/s (attached) is empty, to the untrained eye at least.

Regards

John

P.S. I’m in the UK where it’s late Friday p.m. Am about to disappear till Monday.

From: ggreen86 @.<mailto:@.>> Sent: 05 August 2022 16:03 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: HOITJohnHarbour @.<mailto:@.>>; Author @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I just tried your code below and it successfully created a spreadsheet that I could open.

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb)

(note the commented line). Please open the 'bad' spreadsheet with your version of Excel and then allow it to 'fix' the spreadsheet. Then send the 'fixed' version to me so that I can compare with the original. Thank you.

Greg

From: Harbour-J @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: Friday, August 5, 2022 10:05 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Comment @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg It's not 2010 - Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20292) 32-bit

Sorry, I think I confused you by using 1 & 2 as my example numbers as well as the row numbers. Here's some clearer code.

loXL = CREATEOBJECT('VFPxWorkbookXLSX') lnWb = loXL.CreateWorkbook('good.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('good.xlsx')

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('bad.xlsx')

This fails to open bad.xlsx. Curiously, if I uncomment the commented line it works. Also, if I change it to write to A1 and B1, it fails in the same way. Files attached.

Many thanks

John

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206496252<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206496252 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206496252<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206496252>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33PK2SL6CPONE7TILRTVXUNT3ANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33PK2SL6CPONE7TILRTVXUNT3ANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/AGWB33PK2SL6CPONE7TILRTVXUNT3ANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33PK2SL6CPONE7TILRTVXUNT3ANCNFSM55S2RCKQ>>. You are receiving this because you commented.Message ID: @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206557382<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206557382>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ>. 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/69#issuecomment-1206587635<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206587635>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ>. 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/69#issuecomment-1206627137, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ. You are receiving this because you authored the thread.Message ID: @.**@.>>

ggreen86 commented 2 years ago

John--

Just to clarify, did the code that I sent work for you and create a correct workbook file? Thank you.

Greg

From: Harbour-J @.> Sent: Monday, August 8, 2022 6:41 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg

I’m running R32. I’ve recopied the .vcx, .vct and .h files from the .zip just in case.

I ran the code as written, except I needed "..\ClassLib\VFPxWorkbookXLSX.vcx".

@.***

John

From: ggreen86 @.> Sent: 05 August 2022 17:18 To: ggreen86/XLSX-Workbook-Class @.> Cc: HOITJohnHarbour @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I found the problem. Your 'bad.xlxs' file is missing the internal file sheet1.xml which defines the sheet values. Not sure why it is missing. Please try the below code and let me know the results. Also, make sure you are using the most current version of the class from VFPx.

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "..\VFPxWorkbookXLSX.vcx") lnWb = loExcel.CreateWorkbook("FirstRowTest.xlsx")

lnSh = loExcel.AddSheet(lnWb, "Sheet1") *loExcel.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loExcel.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loExcel.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2

loExcel.SaveWorkbook(lnWb)

Greg

From: Harbour-J @.<mailto:@.>> Sent: Friday, August 5, 2022 11:32 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi,

Were you able to open the bad.xlsx that I sent you?

This is the “log file listing repairs” that Excel creates:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <recoveryLog xmlns=http://schemas.openxmlformats.org/spreadsheetml/2006/main http://schemas.openxmlformats.org/spreadsheetml/2006/main>

error153160_01.xml

Errors were detected in file 'C:\dev\Sm\bad - Copy.xlsx'

Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

The “fixed” s/s (attached) is empty, to the untrained eye at least.

Regards

John

P.S. I’m in the UK where it’s late Friday p.m. Am about to disappear till Monday.

From: ggreen86 @.<mailto:@.>> Sent: 05 August 2022 16:03 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: HOITJohnHarbour @.<mailto:@.>>; Author @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I just tried your code below and it successfully created a spreadsheet that I could open.

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb)

(note the commented line). Please open the 'bad' spreadsheet with your version of Excel and then allow it to 'fix' the spreadsheet. Then send the 'fixed' version to me so that I can compare with the original. Thank you.

Greg

From: Harbour-J @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: Friday, August 5, 2022 10:05 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Comment @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg It's not 2010 - Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20292) 32-bit

Sorry, I think I confused you by using 1 & 2 as my example numbers as well as the row numbers. Here's some clearer code.

loXL = CREATEOBJECT('VFPxWorkbookXLSX') lnWb = loXL.CreateWorkbook('good.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('good.xlsx')

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('bad.xlsx')

This fails to open bad.xlsx. Curiously, if I uncomment the commented line it works. Also, if I change it to write to A1 and B1, it fails in the same way. Files attached.

Many thanks

John

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206496252<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206496252 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206496252<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206496252>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33PK2SL6CPONE7TILRTVXUNT3ANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33PK2SL6CPONE7TILRTVXUNT3ANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/AGWB33PK2SL6CPONE7TILRTVXUNT3ANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33PK2SL6CPONE7TILRTVXUNT3ANCNFSM55S2RCKQ>>. You are receiving this because you commented.Message ID: @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206557382<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206557382>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ>. 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/69#issuecomment-1206587635<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206587635>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ>. 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/69#issuecomment-1206627137, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ. 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/69#issuecomment-1207956981, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ. You are receiving this because you commented.Message ID: @.***>

Harbour-J commented 2 years ago

Sorry to be unclear. No it didn’t. My email contained a snip of the "We found a problem with some content in FirstRowTest.xlsx …" message, which presumably didn’t reach you.

John

From: ggreen86 @.> Sent: 08 August 2022 14:27 To: ggreen86/XLSX-Workbook-Class @.> Cc: HOITJohnHarbour @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

Just to clarify, did the code that I sent work for you and create a correct workbook file? Thank you.

Greg

From: Harbour-J @.<mailto:@.>> Sent: Monday, August 8, 2022 6:41 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg

I’m running R32. I’ve recopied the .vcx, .vct and .h files from the .zip just in case.

I ran the code as written, except I needed "..\ClassLib\VFPxWorkbookXLSX.vcx".

@.<mailto:@.>

John

From: ggreen86 @.<mailto:@.>> Sent: 05 August 2022 17:18 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: HOITJohnHarbour @.<mailto:@.>>; Author @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I found the problem. Your 'bad.xlxs' file is missing the internal file sheet1.xml which defines the sheet values. Not sure why it is missing. Please try the below code and let me know the results. Also, make sure you are using the most current version of the class from VFPx.

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "..\VFPxWorkbookXLSX.vcx") lnWb = loExcel.CreateWorkbook("FirstRowTest.xlsx")

lnSh = loExcel.AddSheet(lnWb, "Sheet1") *loExcel.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loExcel.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loExcel.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2

loExcel.SaveWorkbook(lnWb)

Greg

From: Harbour-J @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: Friday, August 5, 2022 11:32 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Comment @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi,

Were you able to open the bad.xlsx that I sent you?

This is the “log file listing repairs” that Excel creates:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <recoveryLog xmlns=http://schemas.openxmlformats.org/spreadsheetml/2006/main http://schemas.openxmlformats.org/spreadsheetml/2006/main http://schemas.openxmlformats.org/spreadsheetml/2006/main<http://schemas.openxmlformats.org/spreadsheetml/2006/main>>

error153160_01.xml

Errors were detected in file 'C:\dev\Sm\bad - Copy.xlsx'

Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

The “fixed” s/s (attached) is empty, to the untrained eye at least.

Regards

John

P.S. I’m in the UK where it’s late Friday p.m. Am about to disappear till Monday.

From: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: 05 August 2022 16:03 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: HOITJohnHarbour @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Author @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I just tried your code below and it successfully created a spreadsheet that I could open.

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb)

(note the commented line). Please open the 'bad' spreadsheet with your version of Excel and then allow it to 'fix' the spreadsheet. Then send the 'fixed' version to me so that I can compare with the original. Thank you.

Greg

Hi, Greg It's not 2010 - Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20292) 32-bit

Sorry, I think I confused you by using 1 & 2 as my example numbers as well as the row numbers. Here's some clearer code.

loXL = CREATEOBJECT('VFPxWorkbookXLSX') lnWb = loXL.CreateWorkbook('good.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('good.xlsx')

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('bad.xlsx')

This fails to open bad.xlsx. Curiously, if I uncomment the commented line it works. Also, if I change it to write to A1 and B1, it fails in the same way. Files attached.

Many thanks

John

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206557382<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206557382 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206557382<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206557382>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ>>. You are receiving this because you authored the thread.Message ID: @.**@.mailto:***@***.******@***.***<mailto:***@***.******@***.***%3cmailto:***@***.******@***.***>>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206587635<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206587635 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206587635<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206587635>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ>>. You are receiving this because you commented.Message ID: @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ>. 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/69#issuecomment-1207956981<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ>. 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/69#issuecomment-1208129749, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOTBHMCJH7T353SMXMTVYEDLPANCNFSM55S2RCKQ. You are receiving this because you authored the thread.Message ID: @.**@.>>

ggreen86 commented 2 years ago

John--

I am not able to reproduce the error on my side. Can you check the xl_cells table to ensure that there is data contained? Also, set a breakpoints in the method WriteSheetXMLs() at line 149 and at line 748. Verify at line 149 that the cell data is written via FWRITE() commands. At line 748 make sure that the file is actually created in the temp folder. Please let me know the results.

Greg

From: Harbour-J @.> Sent: Monday, August 8, 2022 9:46 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Sorry to be unclear. No it didn’t. My email contained a snip of the "We found a problem with some content in FirstRowTest.xlsx …" message, which presumably didn’t reach you.

John

From: ggreen86 @.> Sent: 08 August 2022 14:27 To: ggreen86/XLSX-Workbook-Class @.> Cc: HOITJohnHarbour @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

Just to clarify, did the code that I sent work for you and create a correct workbook file? Thank you.

Greg

From: Harbour-J @.<mailto:@.>> Sent: Monday, August 8, 2022 6:41 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg

I’m running R32. I’ve recopied the .vcx, .vct and .h files from the .zip just in case.

I ran the code as written, except I needed "..\ClassLib\VFPxWorkbookXLSX.vcx".

@.<mailto:@.>

John

From: ggreen86 @.<mailto:@.>> Sent: 05 August 2022 17:18 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: HOITJohnHarbour @.<mailto:@.>>; Author @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I found the problem. Your 'bad.xlxs' file is missing the internal file sheet1.xml which defines the sheet values. Not sure why it is missing. Please try the below code and let me know the results. Also, make sure you are using the most current version of the class from VFPx.

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "..\VFPxWorkbookXLSX.vcx") lnWb = loExcel.CreateWorkbook("FirstRowTest.xlsx")

lnSh = loExcel.AddSheet(lnWb, "Sheet1") *loExcel.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loExcel.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loExcel.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2

loExcel.SaveWorkbook(lnWb)

Greg

From: Harbour-J @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: Friday, August 5, 2022 11:32 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Comment @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi,

Were you able to open the bad.xlsx that I sent you?

This is the “log file listing repairs” that Excel creates:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <recoveryLog xmlns=http://schemas.openxmlformats.org/spreadsheetml/2006/main http://schemas.openxmlformats.org/spreadsheetml/2006/main http://schemas.openxmlformats.org/spreadsheetml/2006/main<http://schemas.openxmlformats.org/spreadsheetml/2006/main>>

error153160_01.xml

Errors were detected in file 'C:\dev\Sm\bad - Copy.xlsx'

Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

The “fixed” s/s (attached) is empty, to the untrained eye at least.

Regards

John

P.S. I’m in the UK where it’s late Friday p.m. Am about to disappear till Monday.

From: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: 05 August 2022 16:03 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: HOITJohnHarbour @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Author @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I just tried your code below and it successfully created a spreadsheet that I could open.

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb)

(note the commented line). Please open the 'bad' spreadsheet with your version of Excel and then allow it to 'fix' the spreadsheet. Then send the 'fixed' version to me so that I can compare with the original. Thank you.

Greg

Hi, Greg It's not 2010 - Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20292) 32-bit

Sorry, I think I confused you by using 1 & 2 as my example numbers as well as the row numbers. Here's some clearer code.

loXL = CREATEOBJECT('VFPxWorkbookXLSX') lnWb = loXL.CreateWorkbook('good.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('good.xlsx')

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('bad.xlsx')

This fails to open bad.xlsx. Curiously, if I uncomment the commented line it works. Also, if I change it to write to A1 and B1, it fails in the same way. Files attached.

Many thanks

John

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206557382<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206557382 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206557382<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206557382>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOTUYWZPIZJFCMT443TVXUUJVANCNFSM55S2RCKQ>>. You are receiving this because you authored the thread.Message ID: @.**@.mailto:***@***.******@***.***<mailto:***@***.******@***.***%3cmailto:***@***.******@***.***>>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206587635<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206587635 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206587635<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206587635>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33ITWQD2SOVF53HD2ETVXUXZTANCNFSM55S2RCKQ>>. You are receiving this because you commented.Message ID: @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ>. 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/69#issuecomment-1207956981<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ>. 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/69#issuecomment-1208129749, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOTBHMCJH7T353SMXMTVYEDLPANCNFSM55S2RCKQ. 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/69#issuecomment-1208153267, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33K2YXYHXD2QHCP6F6LVYEFTJANCNFSM55S2RCKQ. You are receiving this because you commented.Message ID: @.***>

Harbour-J commented 2 years ago

Hmm. My WriteSheetXMLs is only 688 lines long, so your reference to line 748 is interesting!

I’ve now installed R34 beta 4 and my problem is fixed.

Thanks.

John

From: ggreen86 @.> Sent: 08 August 2022 15:04 To: ggreen86/XLSX-Workbook-Class @.> Cc: HOITJohnHarbour @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I am not able to reproduce the error on my side. Can you check the xl_cells table to ensure that there is data contained? Also, set a breakpoints in the method WriteSheetXMLs() at line 149 and at line 748. Verify at line 149 that the cell data is written via FWRITE() commands. At line 748 make sure that the file is actually created in the temp folder. Please let me know the results.

Greg

From: Harbour-J @.<mailto:@.>> Sent: Monday, August 8, 2022 9:46 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Sorry to be unclear. No it didn’t. My email contained a snip of the "We found a problem with some content in FirstRowTest.xlsx …" message, which presumably didn’t reach you.

John

From: ggreen86 @.<mailto:@.>> Sent: 08 August 2022 14:27 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: HOITJohnHarbour @.<mailto:@.>>; Author @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

Just to clarify, did the code that I sent work for you and create a correct workbook file? Thank you.

Greg

From: Harbour-J @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: Monday, August 8, 2022 6:41 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Comment @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg

I’m running R32. I’ve recopied the .vcx, .vct and .h files from the .zip just in case.

I ran the code as written, except I needed "..\ClassLib\VFPxWorkbookXLSX.vcx".

@.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>

John

From: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: 05 August 2022 17:18 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: HOITJohnHarbour @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Author @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I found the problem. Your 'bad.xlxs' file is missing the internal file sheet1.xml which defines the sheet values. Not sure why it is missing. Please try the below code and let me know the results. Also, make sure you are using the most current version of the class from VFPx.

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "..\VFPxWorkbookXLSX.vcx") lnWb = loExcel.CreateWorkbook("FirstRowTest.xlsx")

lnSh = loExcel.AddSheet(lnWb, "Sheet1") *loExcel.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loExcel.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loExcel.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2

loExcel.SaveWorkbook(lnWb)

Greg

Hi,

Were you able to open the bad.xlsx that I sent you?

This is the “log file listing repairs” that Excel creates:

error153160_01.xml

Errors were detected in file 'C:\dev\Sm\bad - Copy.xlsx'

Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

The “fixed” s/s (attached) is empty, to the untrained eye at least.

Regards

John

P.S. I’m in the UK where it’s late Friday p.m. Am about to disappear till Monday.

John--

I just tried your code below and it successfully created a spreadsheet that I could open.

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb)

(note the commented line). Please open the 'bad' spreadsheet with your version of Excel and then allow it to 'fix' the spreadsheet. Then send the 'fixed' version to me so that I can compare with the original. Thank you.

Greg

Hi, Greg It's not 2010 - Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20292) 32-bit

Sorry, I think I confused you by using 1 & 2 as my example numbers as well as the row numbers. Here's some clearer code.

loXL = CREATEOBJECT('VFPxWorkbookXLSX') lnWb = loXL.CreateWorkbook('good.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('good.xlsx')

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('bad.xlsx')

This fails to open bad.xlsx. Curiously, if I uncomment the commented line it works. Also, if I change it to write to A1 and B1, it fails in the same way. Files attached.

Many thanks

John

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ>>. You are receiving this because you authored the thread.Message ID: @.**@.mailto:***@***.******@***.***<mailto:***@***.******@***.***%3cmailto:***@***.******@***.***>>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ>>. You are receiving this because you commented.Message ID: @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208129749<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208129749>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOTBHMCJH7T353SMXMTVYEDLPANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOTBHMCJH7T353SMXMTVYEDLPANCNFSM55S2RCKQ>. 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/69#issuecomment-1208153267<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208153267>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33K2YXYHXD2QHCP6F6LVYEFTJANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33K2YXYHXD2QHCP6F6LVYEFTJANCNFSM55S2RCKQ>. 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/69#issuecomment-1208173457, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOQ7EHDC3L5LILS6T4TVYEHT3ANCNFSM55S2RCKQ. You are receiving this because you authored the thread.Message ID: @.**@.>>

Harbour-J commented 2 years ago

Hi, Greg

I just spotted this in the Release 33 Beta notes:

“I have just added Beta 7 which fixes a couple of bugs that was found. In particular, I found a bug in the previous releases that occurred when no text was assigned to any cells (only numeric values). The save method would remove the strings.xml from being written, but in doing so, the record pointer in an internal cursor would get moved causing the skipping of writing the first sheet.xml. If only one sheet was added, then the resulting workbook would be in error as well.”

Sounds like my problem.

John

P.S. My code failed in R34 beta 4, due to SetCellAlignment() stomping on the work area. I’ve worked around it by changing luData = EVALUATE(FIELD(lnCol)) to luData = EVALUATE(lcCursor + '.' + FIELD(lnCol, lcCursor)) but I do believe that class methods should restore the original work area before returning.

From: ggreen86 @.> Sent: 08 August 2022 15:04 To: ggreen86/XLSX-Workbook-Class @.> Cc: HOITJohnHarbour @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I am not able to reproduce the error on my side. Can you check the xl_cells table to ensure that there is data contained? Also, set a breakpoints in the method WriteSheetXMLs() at line 149 and at line 748. Verify at line 149 that the cell data is written via FWRITE() commands. At line 748 make sure that the file is actually created in the temp folder. Please let me know the results.

Greg

From: Harbour-J @.<mailto:@.>> Sent: Monday, August 8, 2022 9:46 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Sorry to be unclear. No it didn’t. My email contained a snip of the "We found a problem with some content in FirstRowTest.xlsx …" message, which presumably didn’t reach you.

John

From: ggreen86 @.<mailto:@.>> Sent: 08 August 2022 14:27 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: HOITJohnHarbour @.<mailto:@.>>; Author @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

Just to clarify, did the code that I sent work for you and create a correct workbook file? Thank you.

Greg

From: Harbour-J @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: Monday, August 8, 2022 6:41 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Comment @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg

I’m running R32. I’ve recopied the .vcx, .vct and .h files from the .zip just in case.

I ran the code as written, except I needed "..\ClassLib\VFPxWorkbookXLSX.vcx".

@.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>

John

From: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: 05 August 2022 17:18 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: HOITJohnHarbour @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Author @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I found the problem. Your 'bad.xlxs' file is missing the internal file sheet1.xml which defines the sheet values. Not sure why it is missing. Please try the below code and let me know the results. Also, make sure you are using the most current version of the class from VFPx.

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "..\VFPxWorkbookXLSX.vcx") lnWb = loExcel.CreateWorkbook("FirstRowTest.xlsx")

lnSh = loExcel.AddSheet(lnWb, "Sheet1") *loExcel.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loExcel.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loExcel.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2

loExcel.SaveWorkbook(lnWb)

Greg

Hi,

Were you able to open the bad.xlsx that I sent you?

This is the “log file listing repairs” that Excel creates:

error153160_01.xml

Errors were detected in file 'C:\dev\Sm\bad - Copy.xlsx'

Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

The “fixed” s/s (attached) is empty, to the untrained eye at least.

Regards

John

P.S. I’m in the UK where it’s late Friday p.m. Am about to disappear till Monday.

John--

I just tried your code below and it successfully created a spreadsheet that I could open.

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb)

(note the commented line). Please open the 'bad' spreadsheet with your version of Excel and then allow it to 'fix' the spreadsheet. Then send the 'fixed' version to me so that I can compare with the original. Thank you.

Greg

Hi, Greg It's not 2010 - Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20292) 32-bit

Sorry, I think I confused you by using 1 & 2 as my example numbers as well as the row numbers. Here's some clearer code.

loXL = CREATEOBJECT('VFPxWorkbookXLSX') lnWb = loXL.CreateWorkbook('good.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('good.xlsx')

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('bad.xlsx')

This fails to open bad.xlsx. Curiously, if I uncomment the commented line it works. Also, if I change it to write to A1 and B1, it fails in the same way. Files attached.

Many thanks

John

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ>>. You are receiving this because you authored the thread.Message ID: @.**@.mailto:***@***.******@***.***<mailto:***@***.******@***.***%3cmailto:***@***.******@***.***>>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ>>. You are receiving this because you commented.Message ID: @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208129749<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208129749>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOTBHMCJH7T353SMXMTVYEDLPANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOTBHMCJH7T353SMXMTVYEDLPANCNFSM55S2RCKQ>. 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/69#issuecomment-1208153267<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208153267>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33K2YXYHXD2QHCP6F6LVYEFTJANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33K2YXYHXD2QHCP6F6LVYEFTJANCNFSM55S2RCKQ>. 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/69#issuecomment-1208173457, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOQ7EHDC3L5LILS6T4TVYEHT3ANCNFSM55S2RCKQ. You are receiving this because you authored the thread.Message ID: @.**@.>>

ggreen86 commented 2 years ago

John--

Where is the code line that you referenced below? I did a search and do not find it in the class library.

Greg

From: Harbour-J @.> Sent: Tuesday, August 9, 2022 5:22 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg

I just spotted this in the Release 33 Beta notes:

“I have just added Beta 7 which fixes a couple of bugs that was found. In particular, I found a bug in the previous releases that occurred when no text was assigned to any cells (only numeric values). The save method would remove the strings.xml from being written, but in doing so, the record pointer in an internal cursor would get moved causing the skipping of writing the first sheet.xml. If only one sheet was added, then the resulting workbook would be in error as well.”

Sounds like my problem.

John

P.S. My code failed in R34 beta 4, due to SetCellAlignment() stomping on the work area. I’ve worked around it by changing luData = EVALUATE(FIELD(lnCol)) to luData = EVALUATE(lcCursor + '.' + FIELD(lnCol, lcCursor)) but I do believe that class methods should restore the original work area before returning.

From: ggreen86 @.> Sent: 08 August 2022 15:04 To: ggreen86/XLSX-Workbook-Class @.> Cc: HOITJohnHarbour @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I am not able to reproduce the error on my side. Can you check the xl_cells table to ensure that there is data contained? Also, set a breakpoints in the method WriteSheetXMLs() at line 149 and at line 748. Verify at line 149 that the cell data is written via FWRITE() commands. At line 748 make sure that the file is actually created in the temp folder. Please let me know the results.

Greg

From: Harbour-J @.<mailto:@.>> Sent: Monday, August 8, 2022 9:46 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Sorry to be unclear. No it didn’t. My email contained a snip of the "We found a problem with some content in FirstRowTest.xlsx …" message, which presumably didn’t reach you.

John

From: ggreen86 @.<mailto:@.>> Sent: 08 August 2022 14:27 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: HOITJohnHarbour @.<mailto:@.>>; Author @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

Just to clarify, did the code that I sent work for you and create a correct workbook file? Thank you.

Greg

From: Harbour-J @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: Monday, August 8, 2022 6:41 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Comment @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg

I’m running R32. I’ve recopied the .vcx, .vct and .h files from the .zip just in case.

I ran the code as written, except I needed "..\ClassLib\VFPxWorkbookXLSX.vcx".

@.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>

John

From: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: 05 August 2022 17:18 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: HOITJohnHarbour @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Author @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I found the problem. Your 'bad.xlxs' file is missing the internal file sheet1.xml which defines the sheet values. Not sure why it is missing. Please try the below code and let me know the results. Also, make sure you are using the most current version of the class from VFPx.

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "..\VFPxWorkbookXLSX.vcx") lnWb = loExcel.CreateWorkbook("FirstRowTest.xlsx")

lnSh = loExcel.AddSheet(lnWb, "Sheet1") *loExcel.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loExcel.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loExcel.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2

loExcel.SaveWorkbook(lnWb)

Greg

Hi,

Were you able to open the bad.xlsx that I sent you?

This is the “log file listing repairs” that Excel creates:

error153160_01.xml

Errors were detected in file 'C:\dev\Sm\bad - Copy.xlsx'

Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

The “fixed” s/s (attached) is empty, to the untrained eye at least.

Regards

John

P.S. I’m in the UK where it’s late Friday p.m. Am about to disappear till Monday.

John--

I just tried your code below and it successfully created a spreadsheet that I could open.

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb)

(note the commented line). Please open the 'bad' spreadsheet with your version of Excel and then allow it to 'fix' the spreadsheet. Then send the 'fixed' version to me so that I can compare with the original. Thank you.

Greg

Hi, Greg It's not 2010 - Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20292) 32-bit

Sorry, I think I confused you by using 1 & 2 as my example numbers as well as the row numbers. Here's some clearer code.

loXL = CREATEOBJECT('VFPxWorkbookXLSX') lnWb = loXL.CreateWorkbook('good.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('good.xlsx')

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('bad.xlsx')

This fails to open bad.xlsx. Curiously, if I uncomment the commented line it works. Also, if I change it to write to A1 and B1, it fails in the same way. Files attached.

Many thanks

John

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1206627137>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOX6TNSIRKWWRD3AMA3VXU5DXANCNFSM55S2RCKQ>>. You are receiving this because you authored the thread.Message ID: @.**@.mailto:***@***.******@***.***<mailto:***@***.******@***.***%3cmailto:***@***.******@***.***>>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1207956981>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33LWCIOKNBDOMSBFUTLVYDP4DANCNFSM55S2RCKQ>>. You are receiving this because you commented.Message ID: @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208129749<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208129749>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOTBHMCJH7T353SMXMTVYEDLPANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOTBHMCJH7T353SMXMTVYEDLPANCNFSM55S2RCKQ>. 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/69#issuecomment-1208153267<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208153267>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33K2YXYHXD2QHCP6F6LVYEFTJANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33K2YXYHXD2QHCP6F6LVYEFTJANCNFSM55S2RCKQ>. 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/69#issuecomment-1208173457, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOQ7EHDC3L5LILS6T4TVYEHT3ANCNFSM55S2RCKQ. 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/69#issuecomment-1209132300, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33IKEHC52IGYDA662YTVYIPM3ANCNFSM55S2RCKQ. You are receiving this because you commented.Message ID: @.***>

Harbour-J commented 2 years ago

Hi, Greg

That’s my code, not yours.

luData = EVALUATE(FIELD(lnCol)) was written on the (dangerous) assumption that within a tight SCAN loop through a cursor the work area is stable. It was until I moved to R34 beta 4. But in that release, SetCellAlignment() changes the work area.

SELECT workbook FROM xl_cells WHERE workbook = tnWB AND cellxfs = lnCellXfsId INTO CURSOR xl_temp … USE IN SELECT('xl_temp')

is what’s doing this. This is a philosophical question. I would argue that any externally exposed method in a class library should always preserve the work area. I do this with a simple lnSelect = SELECT() … SELECT (lnSelect) RETURN …

It’s a PITA if the calling code has to constantly defend against the rug being pulled from under it.

Regards

John

From: ggreen86 @.> Sent: 09 August 2022 19:07 To: ggreen86/XLSX-Workbook-Class @.> Cc: HOITJohnHarbour @.>; Author @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

Where is the code line that you referenced below? I did a search and do not find it in the class library.

Greg

From: Harbour-J @.<mailto:@.>> Sent: Tuesday, August 9, 2022 5:22 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi, Greg

I just spotted this in the Release 33 Beta notes:

“I have just added Beta 7 which fixes a couple of bugs that was found. In particular, I found a bug in the previous releases that occurred when no text was assigned to any cells (only numeric values). The save method would remove the strings.xml from being written, but in doing so, the record pointer in an internal cursor would get moved causing the skipping of writing the first sheet.xml. If only one sheet was added, then the resulting workbook would be in error as well.”

Sounds like my problem.

John

P.S. My code failed in R34 beta 4, due to SetCellAlignment() stomping on the work area. I’ve worked around it by changing luData = EVALUATE(FIELD(lnCol)) to luData = EVALUATE(lcCursor + '.' + FIELD(lnCol, lcCursor)) but I do believe that class methods should restore the original work area before returning.

From: ggreen86 @.<mailto:@.>> Sent: 08 August 2022 15:04 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: HOITJohnHarbour @.<mailto:@.>>; Author @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

I am not able to reproduce the error on my side. Can you check the xl_cells table to ensure that there is data contained? Also, set a breakpoints in the method WriteSheetXMLs() at line 149 and at line 748. Verify at line 149 that the cell data is written via FWRITE() commands. At line 748 make sure that the file is actually created in the temp folder. Please let me know the results.

Greg

From: Harbour-J @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: Monday, August 8, 2022 9:46 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Comment @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Sorry to be unclear. No it didn’t. My email contained a snip of the "We found a problem with some content in FirstRowTest.xlsx …" message, which presumably didn’t reach you.

John

From: ggreen86 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: 08 August 2022 14:27 To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: HOITJohnHarbour @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Author @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

John--

Just to clarify, did the code that I sent work for you and create a correct workbook file? Thank you.

Greg

Hi, Greg

I’m running R32. I’ve recopied the .vcx, .vct and .h files from the .zip just in case.

I ran the code as written, except I needed "..\ClassLib\VFPxWorkbookXLSX.vcx".

@.<mailto:@.mailto:***@***.***%3cmailto:***@***.***<mailto:***@***.***%3cmailto:***@***.***%3cmailto:***@***.***%3cmailto:***@***.***>>

John

John--

I found the problem. Your 'bad.xlxs' file is missing the internal file sheet1.xml which defines the sheet values. Not sure why it is missing. Please try the below code and let me know the results. Also, make sure you are using the most current version of the class from VFPx.

loExcel = NEWOBJECT("VFPxWorkbookXLSX", "..\VFPxWorkbookXLSX.vcx") lnWb = loExcel.CreateWorkbook("FirstRowTest.xlsx")

lnSh = loExcel.AddSheet(lnWb, "Sheet1") *loExcel.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loExcel.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loExcel.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2

loExcel.SaveWorkbook(lnWb)

Greg

Hi,

Were you able to open the bad.xlsx that I sent you?

This is the “log file listing repairs” that Excel creates:

error153160_01.xml

Errors were detected in file 'C:\dev\Sm\bad - Copy.xlsx'

Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

The “fixed” s/s (attached) is empty, to the untrained eye at least.

Regards

John

P.S. I’m in the UK where it’s late Friday p.m. Am about to disappear till Monday.

John--

I just tried your code below and it successfully created a spreadsheet that I could open.

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb)

(note the commented line). Please open the 'bad' spreadsheet with your version of Excel and then allow it to 'fix' the spreadsheet. Then send the 'fixed' version to me so that I can compare with the original. Thank you.

Greg

Hi, Greg It's not 2010 - Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20292) 32-bit

Sorry, I think I confused you by using 1 & 2 as my example numbers as well as the row numbers. Here's some clearer code.

loXL = CREATEOBJECT('VFPxWorkbookXLSX') lnWb = loXL.CreateWorkbook('good.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('good.xlsx')

lnWb = loXL.CreateWorkbook('bad.xlsx') lnSh = loXL.AddSheet(lnWb, "Sheet1") *loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Data') loXL.SetCellValue(lnWb, lnSh, 1, 1, 123) && A1 loXL.SetCellValue(lnWb, lnSh, 2, 1, 99) && A2 loXL.SaveWorkbook(lnWb) ShellEx('bad.xlsx')

This fails to open bad.xlsx. Curiously, if I uncomment the commented line it works. Also, if I change it to write to A1 and B1, it fails in the same way. Files attached.

Many thanks

John

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208129749<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208129749 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208129749<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208129749>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOTBHMCJH7T353SMXMTVYEDLPANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOTBHMCJH7T353SMXMTVYEDLPANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/A2L6IOTBHMCJH7T353SMXMTVYEDLPANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOTBHMCJH7T353SMXMTVYEDLPANCNFSM55S2RCKQ>>. You are receiving this because you authored the thread.Message ID: @.**@.mailto:***@***.******@***.***<mailto:***@***.******@***.***%3cmailto:***@***.******@***.***>>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208153267<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208153267 https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208153267<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208153267>>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33K2YXYHXD2QHCP6F6LVYEFTJANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33K2YXYHXD2QHCP6F6LVYEFTJANCNFSM55S2RCKQ https://github.com/notifications/unsubscribe-auth/AGWB33K2YXYHXD2QHCP6F6LVYEFTJANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33K2YXYHXD2QHCP6F6LVYEFTJANCNFSM55S2RCKQ>>. You are receiving this because you commented.Message ID: @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208173457<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1208173457>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOQ7EHDC3L5LILS6T4TVYEHT3ANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/A2L6IOQ7EHDC3L5LILS6T4TVYEHT3ANCNFSM55S2RCKQ>. 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/69#issuecomment-1209132300<https://github.com/ggreen86/XLSX-Workbook-Class/issues/69#issuecomment-1209132300>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33IKEHC52IGYDA662YTVYIPM3ANCNFSM55S2RCKQ<https://github.com/notifications/unsubscribe-auth/AGWB33IKEHC52IGYDA662YTVYIPM3ANCNFSM55S2RCKQ>. 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/69#issuecomment-1209707840, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2L6IOQI6SD6QC25L4D4MW3VYKM5ZANCNFSM55S2RCKQ. You are receiving this because you authored the thread.Message ID: @.**@.>>

Harbour-J commented 2 years ago

Hi Greg

The program below creates the attached spreadsheet. There seem to be a few issues with DateTime formatting.

Regards

John

P.S. Let me know if you’d prefer me to put this on GitHub as a new query.

*

DateTest.prg

LOCAL loXL, lnWb, lnSh, lnStyle, lcFormat, lnFormat, lnRow

INCLUDE VFPxWorkbookXLSX.h

loXL = CREATEOBJECT('VFPxWorkbookXLSX')

lnWb = loXL.CreateWorkbook('DateTest.xlsx')

lnSh = loXL.AddSheet(lnWb, 'Sheet1')

loXL.SetColumnWidth(lnWb, lnSh, 1, 15)

loXL.SetColumnWidth(lnWb, lnSh, 2, 40)

loXL.SetColumnWidth(lnWb, lnSh, 3, 75)

loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Value')

loXL.SetCellValue(lnWb, lnSh, 1, 2, 'Format')

loXL.SetCellValue(lnWb, lnSh, 1, 3, 'Notes')

lnRow = 2

loXL.SetCellValue(lnWb, lnSh, 2, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = '###0.0"kg";[Red]-###0.0"kg";"";General'

lnFormat = loXL.AddNumericFormat(lnWb, lcFormat)

loXL.AddStyleNumericFormat(lnWb, lnStyle, lnFormat)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, 'A custom numeric format. Works perfectly.')

lnRow = 3

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = 'dd mmm yy hh:mm'

lnFormat = loXL.AddNumericFormat(lnWb, lcFormat)

loXL.AddStyleNumericFormat(lnWb, lnStyle, lnFormat)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, 'A custom datetime format. Works when used manually in excel, but not in code.')

lnRow = 4

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = 'dd mmm yy'

lnFormat = loXL.AddNumericFormat(lnWb, lcFormat)

loXL.AddStyleNumericFormat(lnWb, lnStyle, lnFormat)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, 'A custom date format. Works when used manually in excel, but not in code.')

lnRow = 5

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = 'CELL_FORMAT_DATETIME_DDMMMYYYY_TT24'

loXL.AddStyleNumericFormat(lnWb, lnStyle, CELL_FORMAT_DATETIME_DDMMMYYYY_TT24)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, "Not sure what's happened to the TT24 part of this, or why MMM doesn't show Jul.")

lnRow = 6

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = 'CELL_FORMAT_DATETIME_MDYYHMM'

loXL.AddStyleNumericFormat(lnWb, lnStyle, CELL_FORMAT_DATETIME_MDYYHMM)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, "Correct, except that YY implies 20 not 2020.")

lnRow = 7

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = 'CELL_FORMAT_DATE_DMMMYY'

loXL.AddStyleNumericFormat(lnWb, lnStyle, CELL_FORMAT_DATE_DMMMYY)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, "I can live with this, though I don't like the dashes.")

lnRow = 8

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = 'CELL_FORMAT_DATETIME_MMMDDYYYY_TT24'

loXL.AddStyleNumericFormat(lnWb, lnStyle, CELL_FORMAT_DATETIME_MMMDDYYYY_TT24)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, "Well, that didn't work!")

lnRow = 9

lnStyle = loXL.CreateFormatStyle(lnWb)

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, "CELL_FORMAT_DATE_DMMMYYHMM")

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, "This would do, but it doesn't exist.")

loXL.SaveWorkbook(lnWb)

ShellEx('DateTest.xlsx')

ggreen86 commented 2 years ago

John--

Yes, please open a new issue to track this. This way others can find a similar issue if they have it as well. In the meantime, I will check into this. Thank you.

Greg

From: Harbour-J @.> Sent: Tuesday, August 16, 2022 7:50 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] Xlsx file is invalid if first row is not text (Issue #69)

Hi Greg

The program below creates the attached spreadsheet. There seem to be a few issues with DateTime formatting.

Regards

John

P.S. Let me know if you’d prefer me to put this on GitHub as a new query.

*

DateTest.prg

LOCAL loXL, lnWb, lnSh, lnStyle, lcFormat, lnFormat, lnRow

INCLUDE VFPxWorkbookXLSX.h

loXL = CREATEOBJECT('VFPxWorkbookXLSX')

lnWb = loXL.CreateWorkbook('DateTest.xlsx')

lnSh = loXL.AddSheet(lnWb, 'Sheet1')

loXL.SetColumnWidth(lnWb, lnSh, 1, 15)

loXL.SetColumnWidth(lnWb, lnSh, 2, 40)

loXL.SetColumnWidth(lnWb, lnSh, 3, 75)

loXL.SetCellValue(lnWb, lnSh, 1, 1, 'Value')

loXL.SetCellValue(lnWb, lnSh, 1, 2, 'Format')

loXL.SetCellValue(lnWb, lnSh, 1, 3, 'Notes')

lnRow = 2

loXL.SetCellValue(lnWb, lnSh, 2, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = '###0.0"kg";[Red]-###0.0"kg";"";General'

lnFormat = loXL.AddNumericFormat(lnWb, lcFormat)

loXL.AddStyleNumericFormat(lnWb, lnStyle, lnFormat)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, 'A custom numeric format. Works perfectly.')

lnRow = 3

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = 'dd mmm yy hh:mm'

lnFormat = loXL.AddNumericFormat(lnWb, lcFormat)

loXL.AddStyleNumericFormat(lnWb, lnStyle, lnFormat)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, 'A custom datetime format. Works when used manually in excel, but not in code.')

lnRow = 4

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = 'dd mmm yy'

lnFormat = loXL.AddNumericFormat(lnWb, lcFormat)

loXL.AddStyleNumericFormat(lnWb, lnStyle, lnFormat)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, 'A custom date format. Works when used manually in excel, but not in code.')

lnRow = 5

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = 'CELL_FORMAT_DATETIME_DDMMMYYYY_TT24'

loXL.AddStyleNumericFormat(lnWb, lnStyle, CELL_FORMAT_DATETIME_DDMMMYYYY_TT24)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, "Not sure what's happened to the TT24 part of this, or why MMM doesn't show Jul.")

lnRow = 6

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = 'CELL_FORMAT_DATETIME_MDYYHMM'

loXL.AddStyleNumericFormat(lnWb, lnStyle, CELL_FORMAT_DATETIME_MDYYHMM)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, "Correct, except that YY implies 20 not 2020.")

lnRow = 7

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = 'CELL_FORMAT_DATE_DMMMYY'

loXL.AddStyleNumericFormat(lnWb, lnStyle, CELL_FORMAT_DATE_DMMMYY)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, "I can live with this, though I don't like the dashes.")

lnRow = 8

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

lnStyle = loXL.CreateFormatStyle(lnWb)

lcFormat = 'CELL_FORMAT_DATETIME_MMMDDYYYY_TT24'

loXL.AddStyleNumericFormat(lnWb, lnStyle, CELL_FORMAT_DATETIME_MMMDDYYYY_TT24)

loXL.SetCellStyleRange(lnWb, lnSh, lnRow, 1, lnRow, 1, lnStyle)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, lcFormat)

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, "Well, that didn't work!")

lnRow = 9

lnStyle = loXL.CreateFormatStyle(lnWb)

loXL.SetCellValue(lnWb, lnSh, lnRow, 1, 44015.338889)

loXL.SetCellValue(lnWb, lnSh, lnRow, 2, "CELL_FORMAT_DATE_DMMMYYHMM")

loXL.SetCellValue(lnWb, lnSh, lnRow, 3, "This would do, but it doesn't exist.")

loXL.SaveWorkbook(lnWb)

ShellEx('DateTest.xlsx')

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

ggreen86 commented 1 year ago

This issue has been fixed.

ggreen86 / XLSX-Workbook-Class

Xlsx file is invalid if first row is not text #69

INCLUDE VFPxWorkbookXLSX.h

INCLUDE VFPxWorkbookXLSX.h