ggreen86
commented
1 year ago It might be due to the method of calculation for best fit. I am setting a minimum width of 20 pixels and a maximum based on the value of the property MaxColWidth (150 pixels).
From: ARuddOS @.> Sent: Friday, June 16, 2023 5:25 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] SetColumnBestFit not working on numbers (Issue #90)
I am finding that SetColumnBestFit works well on text, but not so well on numbers, I have tested this on 39 beta 8. The code below is rough and ready but does create an example. I am not specifying the fonts to use in the cells, Excel is displaying in Calibri size 11
m.oVFPWorkBook = NEWOBJECT("vfpxworkbookxlsx","vfpxworkbookxlsx.vcx") m.lnWb = m.oVFPWorkBook.CreateWorkbook(GETFILE("xlsx")) m.lnSheet = m.oVFPWorkBook.AddSheet(m.lnWb)
m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 1, -10000) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 2, -1000) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 3, -100) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 4, -10) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 5, -1) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 6, 1) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 7, 10) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 8, 100) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 9, 1000) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 10, 10000) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 11, "A Long string has been entered in this cell") m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 12, "A Long string has been entered in this cell")
m.lnNumberFormat = m.oVFPWorkBook.AddNumericFormat(m.lnWb, "0.00;") m.oVFPWorkBook.SetCellNumberFormatRange(m.lnWb, m.lnSheet, 1, 1, 1, 10, m.lnNumberFormat)
m.oVFPWorkBook.SetCellNumberFormatRange(m.lnWb, m.lnSheet, 1, 11, 1, 12, 49)
FOR m.nN=1 TO 11 m.oVFPWorkBook.SetColumnBestFit(m.lnWb, m.lnSheet, m.nN, .T.) ENDFOR
— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/90, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33KF3HZZLQWXJAA3A7DXLQQ5LANCNFSM6AAAAAAZI7UOGQ. You are receiving this because you are subscribed to this thread.Message ID: @.**@.>>
I am finding that SetColumnBestFit works well on text, but not so well on numbers, I have tested this on 39 beta 8. The code below is rough and ready but does create an example. I am not specifying the fonts to use in the cells, Excel is displaying in Calibri size 11
m.oVFPWorkBook = NEWOBJECT("vfpxworkbookxlsx","vfpxworkbookxlsx.vcx") m.lnWb = m.oVFPWorkBook.CreateWorkbook(GETFILE("xlsx")) m.lnSheet = m.oVFPWorkBook.AddSheet(m.lnWb)
m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 1, -10000) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 2, -1000) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 3, -100) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 4, -10) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 5, -1) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 6, 1) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 7, 10) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 8, 100) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 9, 1000) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 10, 10000) m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 11, "A Long string has been entered in this cell") m.oVFPWorkBook.SetCellValue(m.lnWb, m.lnSheet, 1, 12, "A Long string has been entered in this cell")
m.lnNumberFormat = m.oVFPWorkBook.AddNumericFormat(m.lnWb, "0.00;") m.oVFPWorkBook.SetCellNumberFormatRange(m.lnWb, m.lnSheet, 1, 1, 1, 10, m.lnNumberFormat)
m.oVFPWorkBook.SetCellNumberFormatRange(m.lnWb, m.lnSheet, 1, 11, 1, 12, 49)
FOR m.nN=1 TO 11 m.oVFPWorkBook.SetColumnBestFit(m.lnWb, m.lnSheet, m.nN, .T.) ENDFOR