search

ggreen86 / XLSX-Workbook-Class

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

VFPX WORKBOOKS Column totals #93

Open brett39 opened 1 year ago

brett39 commented 1 year ago

Hi Greg,

I love using this class as it is a powerful tool. Congratulation on a great piece of coding.

I have made some amendments to your copy (Release 39) that I downloaded so that we can do a couple of additional things. One of them is to add another passed parameter to the SAVEGRIDTOWORKBOOK and SAVETABLETOWORKBOOK methods called TLSUM. If we call these methods in the class and set the TLSUM field to true, it indicates that we want to automatically sum the numeric columns that may exist in the grid and create a total line in the exported grid. I have copied in some code snippets that show this amendment. (see Snippet 1 and 2).

The other change was to put in an option to have the actual fields names of the grid put in as the column headers rather than the column titles. We used another passed parameter called TLHEADERNAME. (see Snippet 1 and 3).

Anyway, if you think that these are worthy additions to your existing code, please feel free to add them in.

Snippet 1. The passed parameters to the methods showing the TLSUM parameter.

LPARAMETERS togrid, txwb, tlfreeze, tlsavewb, tcsheetname, tlinclhiddencols, tlshowgridlines, tnsheet, tnbegrow, tnbegcol, tlsum, tlheadername

Snippet 2. Showing the code to build up the summed columns using TLSUM..

- Freeze the first row if specified IF tlfreeze THIS.freezepanes(lnwb, lnsh, 1, 0) ENDIF *

- Add the header row if defined and set the column widths; get the column font info IF togrid.HEADERHEIGHT > 0 && Change recommendation by Doug Hennig (if no headers, start in first row) lnrow = tnbegrow FOR lncol=1 TO lncolcount locolumn = togrid.COLUMNS(lacolorder[lnCol, 2]) *

ggreen86 commented 1 year ago

Brett—

Thank you. You have a good idea and thank you for submitting the code. My thoughts on how to implement would be to use custom properties assigned to the grid columns to signify to add a column formula. There could be different formulas that the developer may want – so I would add a new Boolean property AssignFormula (.T. assign a formula) and Character property FormulaExpression (SUM, COUNT, etc. if empty then default to SUM). These two properties would be assigned to the Column objects of the grid (use a custom column class). The grid names instead of the column names would be a Boolean property AssignFieldNames (.T. use table field names, .F. use grid column names); this property would be assigned to the grid object.

What are your thoughts on the above? This way there are fewer parameters being passed that I have to check. And there would be less impact to existing code as I would use PEMSTATUS() to check for the existence of the properties.

Greg

From: brett39 @.> Sent: Tuesday, July 25, 2023 10:38 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

I love using this class as it is a powerful tool. Congratulation on a great piece of coding.

I have made some amendments to your copy (Release 39) that I downloaded so that we can do a couple of additional things. One of them is to add another passed parameter to the SAVEGRIDTOWORKBOOK and SAVETABLETOWORKBOOK methods called TLSUM. If we call these methods in the class and set the TLSUM field to true, it indicates that we want to automatically sum the numeric columns that may exist in the grid and create a total line in the exported grid. I have copied in some code snippets that show this amendment. (see Snippet 1 and 2).

The other change was to put in an option to have the actual fields names of the grid put in as the column headers rather than the column titles. We used another passed parameter called TLHEADERNAME. (see Snippet 1 and 3).

Anyway, if you think that these are worthy additions to your existing code, please feel free to add them in.

Snippet 1. The passed parameters to the methods showing the TLSUM parameter.

LPARAMETERS togrid, txwb, tlfreeze, tlsavewb, tcsheetname, tlinclhiddencols, tlshowgridlines, tnsheet, tnbegrow, tnbegcol, tlsum, tlheadername

Snippet 2. Showing the code to build up the summed columns using TLSUM..

· *

*

· *

*

· IF tlsum

· FOR lncol = 1 TO lncolcount

· lcdatatype = THIS.getcelldatatype(lnwb, lnsh, 2, lncol)

· IF INLIST(lcdatatype, data_type_currency, data_type_float, data_type_int)

· lccolumn = THIS.columnindextoascii(lncol)

· THIS.setcellformula(lnwb, lnsh, lnrow + 2, lncol, "=SUM(" + lccolumn + ALLTRIM(STR(tnbegrow+1)) + ":" + lccolumn + ALLTRIM(STR(lnrow))+")")

· lnstylecell = THIS.getcellstyle(lnwb, lnsh, lnrow, lncol)

· THIS.setcellstyle(lnwb, lnsh, lnrow + 2, lncol, lnstylecell)

· THIS.setcellborder(lnwb, lnsh, lnrow + 2, lncol, border_top, border_style_double)

· ENDIF

· ENDFOR

· ENDIF

· *

*

· *

*

*

Snippet 3. Using the grid field names rather than the grid titles for the column headers using TLHEADERNAME. *

· *

*

· *

*

· IF tlheadername

· THIS.setcellvalue(lnwb, lnsh, 1, lncol, UPPER(locolumn.CONTROLSOURCE))

· ELSE

· THIS.setcellvalue(lnwb, lnsh, lnrow, lncol+tnbegcol-1, locolumn.header1.CAPTION)

· ENDIF

· *

*

· *

*

· THIS.setcellstyle(lnwb, lnsh, lnrow, lncol+tnbegcol-1, logrid.COLUMNS(lacolorder[lnCol, 2]).headerstyleid)

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ELSE

· lnrow = tnbegrow - 1

· FOR lncol=1 TO lncolcount

· locolumn = togrid.COLUMNS(lacolorder[lnCol, 2])

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ENDIF

*

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

brett39 commented 1 year ago

Hi Greg,

Thanks for reviewing.

Perhaps if I explain how we are using the VFPX class. I work for a company that has an ERP system written in FoxPro to which we develop lots of add-ons and local programs for our clients. Most of the processing within the application is done via forms with a header and then a grid for the details. A typical screen would look like my screen shot below (Fig 1). The software has an inbuilt facility that allows the grid to be exported directly to Excel by right mouse clicking on the grid header (Fig 2). One of our default settings is to allow the numeric columns in the grid to be auto totalled. Our clients find this very useful. We are only really interested in being able to SUM the column and have not had any requests to have other functions (I guess like AVG or CNT etc). However, as you say, maybe other developers would like some control over other functions.

The way that we using your class is by downloading it, then amending the code in the methods with our changes for the parameters and the code updates that I have sent you. (I’ve attached a zipped-up copy of our amended version of your class, see SAVETABLETOWORKBOOK and SAVEGRIDTOWORKBOOK). We just re-apply these changes to the upda6ted class each time we spot a new release. In our instances, we are just saying that ALL the numeric columns are to be totalled rather than selecting some numeric columns to be totalled, then leaving some of the others as is etc. Ours is quite a simple change and certainly does not some have any sophisticated code other than performing the SUM function.

So, what you are suggesting for a future update sounds like a great idea and I’m sure would be useful for lots of other developers doing more intricate processing but ours is reasonably straight forward. I guess the easiest thing about passing the new parameters to the SAVETABLETOWORKBOOK and SAVEGRIDTOWORKBOOK methods is that we then don’t have to call another method to update the resulting spreadsheet as it will be produced with the summed columns.

Either way, my suggestion is only that and if you think there is a better way of doing it, then I’m in no position to disagree compared with the amount of work that you have already put into this great piece of code. 😊

@.***

@.***

Kind regards,

Brett Hudson Help Desk Manager

Redgum Software & Support Yellow Goanna Pty Ltd 66 Rosslyn Street West Melbourne VIC 3003 Ph: +61 3 9563-0350 | Fax: +61 3 9563-0348 www.redgumsoft.com.auhttp://www.redgumsoft.com.au/ ABN: 38 165 809 889

This transmission is intended for the addressee only. If you have received this transmission in error, please delete it and notify the sender. Thank you. From: ggreen86 Sent: Thursday, July 27, 2023 1:29 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: Brett Hudson @.>; Author @.***> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Brett—

Thank you. You have a good idea and thank you for submitting the code. My thoughts on how to implement would be to use custom properties assigned to the grid columns to signify to add a column formula. There could be different formulas that the developer may want – so I would add a new Boolean property AssignFormula (.T. assign a formula) and Character property FormulaExpression (SUM, COUNT, etc. if empty then default to SUM). These two properties would be assigned to the Column objects of the grid (use a custom column class). The grid names instead of the column names would be a Boolean property AssignFieldNames (.T. use table field names, .F. use grid column names); this property would be assigned to the grid object.

What are your thoughts on the above? This way there are fewer parameters being passed that I have to check. And there would be less impact to existing code as I would use PEMSTATUS() to check for the existence of the properties.

Greg

From: brett39 @.> Sent: Tuesday, July 25, 2023 10:38 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

I love using this class as it is a powerful tool. Congratulation on a great piece of coding.

I have made some amendments to your copy (Release 39) that I downloaded so that we can do a couple of additional things. One of them is to add another passed parameter to the SAVEGRIDTOWORKBOOK and SAVETABLETOWORKBOOK methods called TLSUM. If we call these methods in the class and set the TLSUM field to true, it indicates that we want to automatically sum the numeric columns that may exist in the grid and create a total line in the exported grid. I have copied in some code snippets that show this amendment. (see Snippet 1 and 2).

The other change was to put in an option to have the actual fields names of the grid put in as the column headers rather than the column titles. We used another passed parameter called TLHEADERNAME. (see Snippet 1 and 3).

Anyway, if you think that these are worthy additions to your existing code, please feel free to add them in.

Snippet 1. The passed parameters to the methods showing the TLSUM parameter.

LPARAMETERS togrid, txwb, tlfreeze, tlsavewb, tcsheetname, tlinclhiddencols, tlshowgridlines, tnsheet, tnbegrow, tnbegcol, tlsum, tlheadername

Snippet 2. Showing the code to build up the summed columns using TLSUM..

· *

*

· *

*

· IF tlsum

· FOR lncol = 1 TO lncolcount

· lcdatatype = THIS.getcelldatatype(lnwb, lnsh, 2, lncol)

· IF INLIST(lcdatatype, data_type_currency, data_type_float, data_type_int)

· lccolumn = THIS.columnindextoascii(lncol)

· THIS.setcellformula(lnwb, lnsh, lnrow + 2, lncol, "=SUM(" + lccolumn + ALLTRIM(STR(tnbegrow+1)) + ":" + lccolumn + ALLTRIM(STR(lnrow))+")")

· lnstylecell = THIS.getcellstyle(lnwb, lnsh, lnrow, lncol)

· THIS.setcellstyle(lnwb, lnsh, lnrow + 2, lncol, lnstylecell)

· THIS.setcellborder(lnwb, lnsh, lnrow + 2, lncol, border_top, border_style_double)

· ENDIF

· ENDFOR

· ENDIF

· *

*

· *

*

*

Snippet 3. Using the grid field names rather than the grid titles for the column headers using TLHEADERNAME. *

· *

*

· *

*

· IF tlheadername

· THIS.setcellvalue(lnwb, lnsh, 1, lncol, UPPER(locolumn.CONTROLSOURCE))

· ELSE

· THIS.setcellvalue(lnwb, lnsh, lnrow, lncol+tnbegcol-1, locolumn.header1.CAPTION)

· ENDIF

· *

*

· *

*

· THIS.setcellstyle(lnwb, lnsh, lnrow, lncol+tnbegcol-1, logrid.COLUMNS(lacolorder[lnCol, 2]).headerstyleid)

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ELSE

· lnrow = tnbegrow - 1

· FOR lncol=1 TO lncolcount

· locolumn = togrid.COLUMNS(lacolorder[lnCol, 2])

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ENDIF

*

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

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

ggreen86 commented 1 year ago

Brett—

Not sure if my suggested use of properties was understood. There would be only a single call to the SaveGridToWorkBook() method; it would read the property settings in the grid and then generate the spreadsheet. In your case the grid would have all the columns that are numeric to be summed; the formula expression can be left blank (default to SUM). I could accommodate a property on the grid to indicate SUM numeric columns. So, there would not be two methods to call. However, in the table to be exported, there would have to have additional parameters sent.

Greg

From: brett39 @.> Sent: Thursday, July 27, 2023 12:37 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

Thanks for reviewing.

Perhaps if I explain how we are using the VFPX class. I work for a company that has an ERP system written in FoxPro to which we develop lots of add-ons and local programs for our clients. Most of the processing within the application is done via forms with a header and then a grid for the details. A typical screen would look like my screen shot below (Fig 1). The software has an inbuilt facility that allows the grid to be exported directly to Excel by right mouse clicking on the grid header (Fig 2). One of our default settings is to allow the numeric columns in the grid to be auto totalled. Our clients find this very useful. We are only really interested in being able to SUM the column and have not had any requests to have other functions (I guess like AVG or CNT etc). However, as you say, maybe other developers would like some control over other functions.

The way that we using your class is by downloading it, then amending the code in the methods with our changes for the parameters and the code updates that I have sent you. (I’ve attached a zipped-up copy of our amended version of your class, see SAVETABLETOWORKBOOK and SAVEGRIDTOWORKBOOK). We just re-apply these changes to the upda6ted class each time we spot a new release. In our instances, we are just saying that ALL the numeric columns are to be totalled rather than selecting some numeric columns to be totalled, then leaving some of the others as is etc. Ours is quite a simple change and certainly does not some have any sophisticated code other than performing the SUM function.

So, what you are suggesting for a future update sounds like a great idea and I’m sure would be useful for lots of other developers doing more intricate processing but ours is reasonably straight forward. I guess the easiest thing about passing the new parameters to the SAVETABLETOWORKBOOK and SAVEGRIDTOWORKBOOK methods is that we then don’t have to call another method to update the resulting spreadsheet as it will be produced with the summed columns.

Either way, my suggestion is only that and if you think there is a better way of doing it, then I’m in no position to disagree compared with the amount of work that you have already put into this great piece of code. 😊

@.<mailto:@.>

@.<mailto:@.>

Kind regards,

Brett Hudson Help Desk Manager

Redgum Software & Support Yellow Goanna Pty Ltd 66 Rosslyn Street West Melbourne VIC 3003 Ph: +61 3 9563-0350 | Fax: +61 3 9563-0348 www.redgumsoft.com.auhttp://www.redgumsoft.com.au/<http://www.redgumsoft.com.au%3chttp:/www.redgumsoft.com.au/> ABN: 38 165 809 889

This transmission is intended for the addressee only. If you have received this transmission in error, please delete it and notify the sender. Thank you. From: ggreen86 Sent: Thursday, July 27, 2023 1:29 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: Brett Hudson @.<mailto:@.>>; Author @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Brett—

Thank you. You have a good idea and thank you for submitting the code. My thoughts on how to implement would be to use custom properties assigned to the grid columns to signify to add a column formula. There could be different formulas that the developer may want – so I would add a new Boolean property AssignFormula (.T. assign a formula) and Character property FormulaExpression (SUM, COUNT, etc. if empty then default to SUM). These two properties would be assigned to the Column objects of the grid (use a custom column class). The grid names instead of the column names would be a Boolean property AssignFieldNames (.T. use table field names, .F. use grid column names); this property would be assigned to the grid object.

What are your thoughts on the above? This way there are fewer parameters being passed that I have to check. And there would be less impact to existing code as I would use PEMSTATUS() to check for the existence of the properties.

Greg

From: brett39 @.<mailto:@.>> Sent: Tuesday, July 25, 2023 10:38 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: Subscribed @.<mailto:@.>> Subject: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

I love using this class as it is a powerful tool. Congratulation on a great piece of coding.

I have made some amendments to your copy (Release 39) that I downloaded so that we can do a couple of additional things. One of them is to add another passed parameter to the SAVEGRIDTOWORKBOOK and SAVETABLETOWORKBOOK methods called TLSUM. If we call these methods in the class and set the TLSUM field to true, it indicates that we want to automatically sum the numeric columns that may exist in the grid and create a total line in the exported grid. I have copied in some code snippets that show this amendment. (see Snippet 1 and 2).

The other change was to put in an option to have the actual fields names of the grid put in as the column headers rather than the column titles. We used another passed parameter called TLHEADERNAME. (see Snippet 1 and 3).

Anyway, if you think that these are worthy additions to your existing code, please feel free to add them in.

Snippet 1. The passed parameters to the methods showing the TLSUM parameter.

LPARAMETERS togrid, txwb, tlfreeze, tlsavewb, tcsheetname, tlinclhiddencols, tlshowgridlines, tnsheet, tnbegrow, tnbegcol, tlsum, tlheadername

Snippet 2. Showing the code to build up the summed columns using TLSUM..

· *

*

· *

*

· IF tlsum

· FOR lncol = 1 TO lncolcount

· lcdatatype = THIS.getcelldatatype(lnwb, lnsh, 2, lncol)

· IF INLIST(lcdatatype, data_type_currency, data_type_float, data_type_int)

· lccolumn = THIS.columnindextoascii(lncol)

· THIS.setcellformula(lnwb, lnsh, lnrow + 2, lncol, "=SUM(" + lccolumn + ALLTRIM(STR(tnbegrow+1)) + ":" + lccolumn + ALLTRIM(STR(lnrow))+")")

· lnstylecell = THIS.getcellstyle(lnwb, lnsh, lnrow, lncol)

· THIS.setcellstyle(lnwb, lnsh, lnrow + 2, lncol, lnstylecell)

· THIS.setcellborder(lnwb, lnsh, lnrow + 2, lncol, border_top, border_style_double)

· ENDIF

· ENDFOR

· ENDIF

· *

*

· *

*

*

Snippet 3. Using the grid field names rather than the grid titles for the column headers using TLHEADERNAME. *

· *

*

· *

*

· IF tlheadername

· THIS.setcellvalue(lnwb, lnsh, 1, lncol, UPPER(locolumn.CONTROLSOURCE))

· ELSE

· THIS.setcellvalue(lnwb, lnsh, lnrow, lncol+tnbegcol-1, locolumn.header1.CAPTION)

· ENDIF

· *

*

· *

*

· THIS.setcellstyle(lnwb, lnsh, lnrow, lncol+tnbegcol-1, logrid.COLUMNS(lacolorder[lnCol, 2]).headerstyleid)

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ELSE

· lnrow = tnbegrow - 1

· FOR lncol=1 TO lncolcount

· locolumn = togrid.COLUMNS(lacolorder[lnCol, 2])

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ENDIF

*

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

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/93#issuecomment-1652857183, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGRCOXFJDOHTXQJBPHQ6TX3XSHOBJANCNFSM6AAAAAA2X5BV24. 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/93#issuecomment-1652899371, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33NH2DX5QYJXLX7EDEDXSHV5ZANCNFSM6AAAAAA2X5BV24. You are receiving this because you commented.Message ID: @.**@.>>

brett39 commented 1 year ago

Thanks Greg,

Sounds good. I note that for our purposes, whether the numeric columns are totalled is optional. Some users may want a summed total on their grids but other may not or it may depend on the data in the grid as to whether they want the totals. If we are able to get the totals for some, and not for others by a parameter or setting , then that would be great.

Kind regards,

Brett Hudson Help Desk Manager

Redgum Software & Support Yellow Goanna Pty Ltd 66 Rosslyn Street West Melbourne VIC 3003 Ph: +61 3 9563-0350 | Fax: +61 3 9563-0348 www.redgumsoft.com.auhttp://www.redgumsoft.com.au/ ABN: 38 165 809 889

This transmission is intended for the addressee only. If you have received this transmission in error, please delete it and notify the sender. Thank you. From: ggreen86 Sent: Thursday, July 27, 2023 2:51 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: Brett Hudson @.>; Author @.***> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Brett—

Not sure if my suggested use of properties was understood. There would be only a single call to the SaveGridToWorkBook() method; it would read the property settings in the grid and then generate the spreadsheet. In your case the grid would have all the columns that are numeric to be summed; the formula expression can be left blank (default to SUM). I could accommodate a property on the grid to indicate SUM numeric columns. So, there would not be two methods to call. However, in the table to be exported, there would have to have additional parameters sent.

Greg

From: brett39 @.> Sent: Thursday, July 27, 2023 12:37 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

Thanks for reviewing.

Perhaps if I explain how we are using the VFPX class. I work for a company that has an ERP system written in FoxPro to which we develop lots of add-ons and local programs for our clients. Most of the processing within the application is done via forms with a header and then a grid for the details. A typical screen would look like my screen shot below (Fig 1). The software has an inbuilt facility that allows the grid to be exported directly to Excel by right mouse clicking on the grid header (Fig 2). One of our default settings is to allow the numeric columns in the grid to be auto totalled. Our clients find this very useful. We are only really interested in being able to SUM the column and have not had any requests to have other functions (I guess like AVG or CNT etc). However, as you say, maybe other developers would like some control over other functions.

The way that we using your class is by downloading it, then amending the code in the methods with our changes for the parameters and the code updates that I have sent you. (I’ve attached a zipped-up copy of our amended version of your class, see SAVETABLETOWORKBOOK and SAVEGRIDTOWORKBOOK). We just re-apply these changes to the upda6ted class each time we spot a new release. In our instances, we are just saying that ALL the numeric columns are to be totalled rather than selecting some numeric columns to be totalled, then leaving some of the others as is etc. Ours is quite a simple change and certainly does not some have any sophisticated code other than performing the SUM function.

So, what you are suggesting for a future update sounds like a great idea and I’m sure would be useful for lots of other developers doing more intricate processing but ours is reasonably straight forward. I guess the easiest thing about passing the new parameters to the SAVETABLETOWORKBOOK and SAVEGRIDTOWORKBOOK methods is that we then don’t have to call another method to update the resulting spreadsheet as it will be produced with the summed columns.

Either way, my suggestion is only that and if you think there is a better way of doing it, then I’m in no position to disagree compared with the amount of work that you have already put into this great piece of code. 😊

@.<mailto:@.>

@.<mailto:@.>

Kind regards,

Brett Hudson Help Desk Manager

Redgum Software & Support Yellow Goanna Pty Ltd 66 Rosslyn Street West Melbourne VIC 3003 Ph: +61 3 9563-0350 | Fax: +61 3 9563-0348 www.redgumsoft.com.auhttp://www.redgumsoft.com.au/<http://www.redgumsoft.com.au%3chttp:/www.redgumsoft.com.au/> ABN: 38 165 809 889

This transmission is intended for the addressee only. If you have received this transmission in error, please delete it and notify the sender. Thank you. From: ggreen86 Sent: Thursday, July 27, 2023 1:29 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: Brett Hudson @.<mailto:@.>>; Author @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Brett—

Thank you. You have a good idea and thank you for submitting the code. My thoughts on how to implement would be to use custom properties assigned to the grid columns to signify to add a column formula. There could be different formulas that the developer may want – so I would add a new Boolean property AssignFormula (.T. assign a formula) and Character property FormulaExpression (SUM, COUNT, etc. if empty then default to SUM). These two properties would be assigned to the Column objects of the grid (use a custom column class). The grid names instead of the column names would be a Boolean property AssignFieldNames (.T. use table field names, .F. use grid column names); this property would be assigned to the grid object.

What are your thoughts on the above? This way there are fewer parameters being passed that I have to check. And there would be less impact to existing code as I would use PEMSTATUS() to check for the existence of the properties.

Greg

From: brett39 @.<mailto:@.>> Sent: Tuesday, July 25, 2023 10:38 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: Subscribed @.<mailto:@.>> Subject: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

I love using this class as it is a powerful tool. Congratulation on a great piece of coding.

I have made some amendments to your copy (Release 39) that I downloaded so that we can do a couple of additional things. One of them is to add another passed parameter to the SAVEGRIDTOWORKBOOK and SAVETABLETOWORKBOOK methods called TLSUM. If we call these methods in the class and set the TLSUM field to true, it indicates that we want to automatically sum the numeric columns that may exist in the grid and create a total line in the exported grid. I have copied in some code snippets that show this amendment. (see Snippet 1 and 2).

The other change was to put in an option to have the actual fields names of the grid put in as the column headers rather than the column titles. We used another passed parameter called TLHEADERNAME. (see Snippet 1 and 3).

Anyway, if you think that these are worthy additions to your existing code, please feel free to add them in.

Snippet 1. The passed parameters to the methods showing the TLSUM parameter.

LPARAMETERS togrid, txwb, tlfreeze, tlsavewb, tcsheetname, tlinclhiddencols, tlshowgridlines, tnsheet, tnbegrow, tnbegcol, tlsum, tlheadername

Snippet 2. Showing the code to build up the summed columns using TLSUM..

· *

*

· *

*

· IF tlsum

· FOR lncol = 1 TO lncolcount

· lcdatatype = THIS.getcelldatatype(lnwb, lnsh, 2, lncol)

· IF INLIST(lcdatatype, data_type_currency, data_type_float, data_type_int)

· lccolumn = THIS.columnindextoascii(lncol)

· THIS.setcellformula(lnwb, lnsh, lnrow + 2, lncol, "=SUM(" + lccolumn + ALLTRIM(STR(tnbegrow+1)) + ":" + lccolumn + ALLTRIM(STR(lnrow))+")")

· lnstylecell = THIS.getcellstyle(lnwb, lnsh, lnrow, lncol)

· THIS.setcellstyle(lnwb, lnsh, lnrow + 2, lncol, lnstylecell)

· THIS.setcellborder(lnwb, lnsh, lnrow + 2, lncol, border_top, border_style_double)

· ENDIF

· ENDFOR

· ENDIF

· *

*

· *

*

*

Snippet 3. Using the grid field names rather than the grid titles for the column headers using TLHEADERNAME. *

· *

*

· *

*

· IF tlheadername

· THIS.setcellvalue(lnwb, lnsh, 1, lncol, UPPER(locolumn.CONTROLSOURCE))

· ELSE

· THIS.setcellvalue(lnwb, lnsh, lnrow, lncol+tnbegcol-1, locolumn.header1.CAPTION)

· ENDIF

· *

*

· *

*

· THIS.setcellstyle(lnwb, lnsh, lnrow, lncol+tnbegcol-1, logrid.COLUMNS(lacolorder[lnCol, 2]).headerstyleid)

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ELSE

· lnrow = tnbegrow - 1

· FOR lncol=1 TO lncolcount

· locolumn = togrid.COLUMNS(lacolorder[lnCol, 2])

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ENDIF

*

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

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/93#issuecomment-1652857183, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGRCOXFJDOHTXQJBPHQ6TX3XSHOBJANCNFSM6AAAAAA2X5BV24. 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/93#issuecomment-1652899371, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33NH2DX5QYJXLX7EDEDXSHV5ZANCNFSM6AAAAAA2X5BV24. You are receiving this because you commented.Message ID: @.**@.>>

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

brett39 commented 1 year ago

Hi Greg,

On another issue, I have noticed the following.

When I create a spreadsheet via the SAVETABLETOWORKBOOKEX method using release 39, I note that I cannot manually (or via Excel Automation) add in a new sheet once the spreadsheet has been created.

When I open the new spreadsheet, I click on the ‘New sheet’ button, but I get the following error as per the following spreadsheet.

If I try the same on a spreadsheet created using release 29, then all is OK.

We are doing some Excel automation after having created the spreadsheet to generate a Pivot table and using Release 39, because we can’t create the new sheet via Excel automation, we can’t use the latest release.

Release 39 Spreadsheet cannot manually add new sheet.

@.***

@.***

Release 29 spreadsheet can manually add new sheet.

@. @.

Kind regards,

Brett Hudson Help Desk Manager

Redgum Software & Support Yellow Goanna Pty Ltd 66 Rosslyn Street West Melbourne VIC 3003 Ph: +61 3 9563-0350 | Fax: +61 3 9563-0348 www.redgumsoft.com.auhttp://www.redgumsoft.com.au/ ABN: 38 165 809 889

This transmission is intended for the addressee only. If you have received this transmission in error, please delete it and notify the sender. Thank you. From: ggreen86 Sent: Thursday, July 27, 2023 2:51 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: Brett Hudson @.>; Author @.***> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Brett—

Not sure if my suggested use of properties was understood. There would be only a single call to the SaveGridToWorkBook() method; it would read the property settings in the grid and then generate the spreadsheet. In your case the grid would have all the columns that are numeric to be summed; the formula expression can be left blank (default to SUM). I could accommodate a property on the grid to indicate SUM numeric columns. So, there would not be two methods to call. However, in the table to be exported, there would have to have additional parameters sent.

Greg

From: brett39 @.<mailto:@.>> Sent: Thursday, July 27, 2023 12:37 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

Thanks for reviewing.

Perhaps if I explain how we are using the VFPX class. I work for a company that has an ERP system written in FoxPro to which we develop lots of add-ons and local programs for our clients. Most of the processing within the application is done via forms with a header and then a grid for the details. A typical screen would look like my screen shot below (Fig 1). The software has an inbuilt facility that allows the grid to be exported directly to Excel by right mouse clicking on the grid header (Fig 2). One of our default settings is to allow the numeric columns in the grid to be auto totalled. Our clients find this very useful. We are only really interested in being able to SUM the column and have not had any requests to have other functions (I guess like AVG or CNT etc). However, as you say, maybe other developers would like some control over other functions.

The way that we using your class is by downloading it, then amending the code in the methods with our changes for the parameters and the code updates that I have sent you. (I’ve attached a zipped-up copy of our amended version of your class, see SAVETABLETOWORKBOOK and SAVEGRIDTOWORKBOOK). We just re-apply these changes to the upda6ted class each time we spot a new release. In our instances, we are just saying that ALL the numeric columns are to be totalled rather than selecting some numeric columns to be totalled, then leaving some of the others as is etc. Ours is quite a simple change and certainly does not some have any sophisticated code other than performing the SUM function.

So, what you are suggesting for a future update sounds like a great idea and I’m sure would be useful for lots of other developers doing more intricate processing but ours is reasonably straight forward. I guess the easiest thing about passing the new parameters to the SAVETABLETOWORKBOOK and SAVEGRIDTOWORKBOOK methods is that we then don’t have to call another method to update the resulting spreadsheet as it will be produced with the summed columns.

Either way, my suggestion is only that and if you think there is a better way of doing it, then I’m in no position to disagree compared with the amount of work that you have already put into this great piece of code. 😊

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

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

Kind regards,

Brett Hudson Help Desk Manager

Redgum Software & Support Yellow Goanna Pty Ltd 66 Rosslyn Street West Melbourne VIC 3003 Ph: +61 3 9563-0350 | Fax: +61 3 9563-0348 www.redgumsoft.com.auhttp://www.redgumsoft.com.au/<http://www.redgumsoft.com.au%3chttp:/www.redgumsoft.com.au/<http://www.redgumsoft.com.au%3chttp:/www.redgumsoft.com.au/%3chttp:/www.redgumsoft.com.au%3chttp:/www.redgumsoft.com.au/>> ABN: 38 165 809 889

This transmission is intended for the addressee only. If you have received this transmission in error, please delete it and notify the sender. Thank you. From: ggreen86 Sent: Thursday, July 27, 2023 1:29 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: Brett Hudson @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Author @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Brett—

Thank you. You have a good idea and thank you for submitting the code. My thoughts on how to implement would be to use custom properties assigned to the grid columns to signify to add a column formula. There could be different formulas that the developer may want – so I would add a new Boolean property AssignFormula (.T. assign a formula) and Character property FormulaExpression (SUM, COUNT, etc. if empty then default to SUM). These two properties would be assigned to the Column objects of the grid (use a custom column class). The grid names instead of the column names would be a Boolean property AssignFieldNames (.T. use table field names, .F. use grid column names); this property would be assigned to the grid object.

What are your thoughts on the above? This way there are fewer parameters being passed that I have to check. And there would be less impact to existing code as I would use PEMSTATUS() to check for the existence of the properties.

Greg

From: brett39 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: Tuesday, July 25, 2023 10:38 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: Subscribed @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

I love using this class as it is a powerful tool. Congratulation on a great piece of coding.

I have made some amendments to your copy (Release 39) that I downloaded so that we can do a couple of additional things. One of them is to add another passed parameter to the SAVEGRIDTOWORKBOOK and SAVETABLETOWORKBOOK methods called TLSUM. If we call these methods in the class and set the TLSUM field to true, it indicates that we want to automatically sum the numeric columns that may exist in the grid and create a total line in the exported grid. I have copied in some code snippets that show this amendment. (see Snippet 1 and 2).

The other change was to put in an option to have the actual fields names of the grid put in as the column headers rather than the column titles. We used another passed parameter called TLHEADERNAME. (see Snippet 1 and 3).

Anyway, if you think that these are worthy additions to your existing code, please feel free to add them in.

Snippet 1. The passed parameters to the methods showing the TLSUM parameter.

LPARAMETERS togrid, txwb, tlfreeze, tlsavewb, tcsheetname, tlinclhiddencols, tlshowgridlines, tnsheet, tnbegrow, tnbegcol, tlsum, tlheadername

Snippet 2. Showing the code to build up the summed columns using TLSUM..

· *

*

· *

*

· IF tlsum

· FOR lncol = 1 TO lncolcount

· lcdatatype = THIS.getcelldatatype(lnwb, lnsh, 2, lncol)

· IF INLIST(lcdatatype, data_type_currency, data_type_float, data_type_int)

· lccolumn = THIS.columnindextoascii(lncol)

· THIS.setcellformula(lnwb, lnsh, lnrow + 2, lncol, "=SUM(" + lccolumn + ALLTRIM(STR(tnbegrow+1)) + ":" + lccolumn + ALLTRIM(STR(lnrow))+")")

· lnstylecell = THIS.getcellstyle(lnwb, lnsh, lnrow, lncol)

· THIS.setcellstyle(lnwb, lnsh, lnrow + 2, lncol, lnstylecell)

· THIS.setcellborder(lnwb, lnsh, lnrow + 2, lncol, border_top, border_style_double)

· ENDIF

· ENDFOR

· ENDIF

· *

*

· *

*

*

Snippet 3. Using the grid field names rather than the grid titles for the column headers using TLHEADERNAME. *

· *

*

· *

*

· IF tlheadername

· THIS.setcellvalue(lnwb, lnsh, 1, lncol, UPPER(locolumn.CONTROLSOURCE))

· ELSE

· THIS.setcellvalue(lnwb, lnsh, lnrow, lncol+tnbegcol-1, locolumn.header1.CAPTION)

· ENDIF

· *

*

· *

*

· THIS.setcellstyle(lnwb, lnsh, lnrow, lncol+tnbegcol-1, logrid.COLUMNS(lacolorder[lnCol, 2]).headerstyleid)

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ELSE

· lnrow = tnbegrow - 1

· FOR lncol=1 TO lncolcount

· locolumn = togrid.COLUMNS(lacolorder[lnCol, 2])

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ENDIF

*

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

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/93#issuecomment-1652857183, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGRCOXFJDOHTXQJBPHQ6TX3XSHOBJANCNFSM6AAAAAA2X5BV24. 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/93#issuecomment-1652899371, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33NH2DX5QYJXLX7EDEDXSHV5ZANCNFSM6AAAAAA2X5BV24. 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/93#issuecomment-1652909592, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGRCOXA2BVOFVGFYTCDT7JTXSHXUPANCNFSM6AAAAAA2X5BV24. You are receiving this because you authored the thread.Message ID: @.**@.>>

brett39 commented 1 year ago

Hi Greg,

Just tested using SAVETABLETOWORKBOOK and it all is OK. Maybe SAVETABLETOWORKBOOKEX is supposed to act in this way.

Either way, I can use SAVETABLETOWORKBOOK all Ok so all good.

Thanks for you help and looking at the summing in the columns etc. I will keep an eye out for all the future updates. 😊

Kind regards,

Brett Hudson Help Desk Manager

Redgum Software & Support Yellow Goanna Pty Ltd 66 Rosslyn Street West Melbourne VIC 3003 Ph: +61 3 9563-0350 | Fax: +61 3 9563-0348 www.redgumsoft.com.auhttp://www.redgumsoft.com.au/ ABN: 38 165 809 889

This transmission is intended for the addressee only. If you have received this transmission in error, please delete it and notify the sender. Thank you. From: Brett Hudson Sent: Friday, July 28, 2023 8:58 AM To: ggreen86/XLSX-Workbook-Class @.>; ggreen86/XLSX-Workbook-Class @.> Cc: Author @.***> Subject: RE: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

On another issue, I have noticed the following.

When I create a spreadsheet via the SAVETABLETOWORKBOOKEX method using release 39, I note that I cannot manually (or via Excel Automation) add in a new sheet once the spreadsheet has been created.

When I open the new spreadsheet, I click on the ‘New sheet’ button, but I get the following error as per the following spreadsheet.

If I try the same on a spreadsheet created using release 29, then all is OK.

We are doing some Excel automation after having created the spreadsheet to generate a Pivot table and using Release 39, because we can’t create the new sheet via Excel automation, we can’t use the latest release.

Release 39 Spreadsheet cannot manually add new sheet.

@.***

@.***

Release 29 spreadsheet can manually add new sheet.

@. @.

Kind regards,

Brett Hudson Help Desk Manager

Redgum Software & Support Yellow Goanna Pty Ltd 66 Rosslyn Street West Melbourne VIC 3003 Ph: +61 3 9563-0350 | Fax: +61 3 9563-0348 www.redgumsoft.com.auhttp://www.redgumsoft.com.au/ ABN: 38 165 809 889

This transmission is intended for the addressee only. If you have received this transmission in error, please delete it and notify the sender. Thank you. From: ggreen86 Sent: Thursday, July 27, 2023 2:51 PM To: ggreen86/XLSX-Workbook-Class @.**@.>> Cc: Brett Hudson @.**@.>>; Author @.**@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Brett—

Not sure if my suggested use of properties was understood. There would be only a single call to the SaveGridToWorkBook() method; it would read the property settings in the grid and then generate the spreadsheet. In your case the grid would have all the columns that are numeric to be summed; the formula expression can be left blank (default to SUM). I could accommodate a property on the grid to indicate SUM numeric columns. So, there would not be two methods to call. However, in the table to be exported, there would have to have additional parameters sent.

Greg

From: brett39 @.<mailto:@.>> Sent: Thursday, July 27, 2023 12:37 AM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: ggreen86 @.<mailto:@.>>; Comment @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

Thanks for reviewing.

Perhaps if I explain how we are using the VFPX class. I work for a company that has an ERP system written in FoxPro to which we develop lots of add-ons and local programs for our clients. Most of the processing within the application is done via forms with a header and then a grid for the details. A typical screen would look like my screen shot below (Fig 1). The software has an inbuilt facility that allows the grid to be exported directly to Excel by right mouse clicking on the grid header (Fig 2). One of our default settings is to allow the numeric columns in the grid to be auto totalled. Our clients find this very useful. We are only really interested in being able to SUM the column and have not had any requests to have other functions (I guess like AVG or CNT etc). However, as you say, maybe other developers would like some control over other functions.

The way that we using your class is by downloading it, then amending the code in the methods with our changes for the parameters and the code updates that I have sent you. (I’ve attached a zipped-up copy of our amended version of your class, see SAVETABLETOWORKBOOK and SAVEGRIDTOWORKBOOK). We just re-apply these changes to the upda6ted class each time we spot a new release. In our instances, we are just saying that ALL the numeric columns are to be totalled rather than selecting some numeric columns to be totalled, then leaving some of the others as is etc. Ours is quite a simple change and certainly does not some have any sophisticated code other than performing the SUM function.

So, what you are suggesting for a future update sounds like a great idea and I’m sure would be useful for lots of other developers doing more intricate processing but ours is reasonably straight forward. I guess the easiest thing about passing the new parameters to the SAVETABLETOWORKBOOK and SAVEGRIDTOWORKBOOK methods is that we then don’t have to call another method to update the resulting spreadsheet as it will be produced with the summed columns.

Either way, my suggestion is only that and if you think there is a better way of doing it, then I’m in no position to disagree compared with the amount of work that you have already put into this great piece of code. 😊

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

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

Kind regards,

Brett Hudson Help Desk Manager

Redgum Software & Support Yellow Goanna Pty Ltd 66 Rosslyn Street West Melbourne VIC 3003 Ph: +61 3 9563-0350 | Fax: +61 3 9563-0348 www.redgumsoft.com.auhttp://www.redgumsoft.com.au/<http://www.redgumsoft.com.au%3chttp:/www.redgumsoft.com.au/<http://www.redgumsoft.com.au%3chttp:/www.redgumsoft.com.au/%3chttp:/www.redgumsoft.com.au%3chttp:/www.redgumsoft.com.au/>> ABN: 38 165 809 889

This transmission is intended for the addressee only. If you have received this transmission in error, please delete it and notify the sender. Thank you. From: ggreen86 Sent: Thursday, July 27, 2023 1:29 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: Brett Hudson @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>>; Author @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Brett—

Thank you. You have a good idea and thank you for submitting the code. My thoughts on how to implement would be to use custom properties assigned to the grid columns to signify to add a column formula. There could be different formulas that the developer may want – so I would add a new Boolean property AssignFormula (.T. assign a formula) and Character property FormulaExpression (SUM, COUNT, etc. if empty then default to SUM). These two properties would be assigned to the Column objects of the grid (use a custom column class). The grid names instead of the column names would be a Boolean property AssignFieldNames (.T. use table field names, .F. use grid column names); this property would be assigned to the grid object.

What are your thoughts on the above? This way there are fewer parameters being passed that I have to check. And there would be less impact to existing code as I would use PEMSTATUS() to check for the existence of the properties.

Greg

From: brett39 @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Sent: Tuesday, July 25, 2023 10:38 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Cc: Subscribed @.<mailto:@.mailto:***@***.***%3cmailto:***@***.***>> Subject: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

I love using this class as it is a powerful tool. Congratulation on a great piece of coding.

I have made some amendments to your copy (Release 39) that I downloaded so that we can do a couple of additional things. One of them is to add another passed parameter to the SAVEGRIDTOWORKBOOK and SAVETABLETOWORKBOOK methods called TLSUM. If we call these methods in the class and set the TLSUM field to true, it indicates that we want to automatically sum the numeric columns that may exist in the grid and create a total line in the exported grid. I have copied in some code snippets that show this amendment. (see Snippet 1 and 2).

The other change was to put in an option to have the actual fields names of the grid put in as the column headers rather than the column titles. We used another passed parameter called TLHEADERNAME. (see Snippet 1 and 3).

Anyway, if you think that these are worthy additions to your existing code, please feel free to add them in.

Snippet 1. The passed parameters to the methods showing the TLSUM parameter.

LPARAMETERS togrid, txwb, tlfreeze, tlsavewb, tcsheetname, tlinclhiddencols, tlshowgridlines, tnsheet, tnbegrow, tnbegcol, tlsum, tlheadername

Snippet 2. Showing the code to build up the summed columns using TLSUM..

· *

*

· *

*

· IF tlsum

· FOR lncol = 1 TO lncolcount

· lcdatatype = THIS.getcelldatatype(lnwb, lnsh, 2, lncol)

· IF INLIST(lcdatatype, data_type_currency, data_type_float, data_type_int)

· lccolumn = THIS.columnindextoascii(lncol)

· THIS.setcellformula(lnwb, lnsh, lnrow + 2, lncol, "=SUM(" + lccolumn + ALLTRIM(STR(tnbegrow+1)) + ":" + lccolumn + ALLTRIM(STR(lnrow))+")")

· lnstylecell = THIS.getcellstyle(lnwb, lnsh, lnrow, lncol)

· THIS.setcellstyle(lnwb, lnsh, lnrow + 2, lncol, lnstylecell)

· THIS.setcellborder(lnwb, lnsh, lnrow + 2, lncol, border_top, border_style_double)

· ENDIF

· ENDFOR

· ENDIF

· *

*

· *

*

*

Snippet 3. Using the grid field names rather than the grid titles for the column headers using TLHEADERNAME. *

· *

*

· *

*

· IF tlheadername

· THIS.setcellvalue(lnwb, lnsh, 1, lncol, UPPER(locolumn.CONTROLSOURCE))

· ELSE

· THIS.setcellvalue(lnwb, lnsh, lnrow, lncol+tnbegcol-1, locolumn.header1.CAPTION)

· ENDIF

· *

*

· *

*

· THIS.setcellstyle(lnwb, lnsh, lnrow, lncol+tnbegcol-1, logrid.COLUMNS(lacolorder[lnCol, 2]).headerstyleid)

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ELSE

· lnrow = tnbegrow - 1

· FOR lncol=1 TO lncolcount

· locolumn = togrid.COLUMNS(lacolorder[lnCol, 2])

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ENDIF

*

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

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/93#issuecomment-1652857183, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGRCOXFJDOHTXQJBPHQ6TX3XSHOBJANCNFSM6AAAAAA2X5BV24. 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/93#issuecomment-1652899371, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33NH2DX5QYJXLX7EDEDXSHV5ZANCNFSM6AAAAAA2X5BV24. 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/93#issuecomment-1652909592, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGRCOXA2BVOFVGFYTCDT7JTXSHXUPANCNFSM6AAAAAA2X5BV24. You are receiving this because you authored the thread.Message ID: @.**@.>>

ggreen86 commented 1 year ago

Brett--

I have corrected a bug in the SaveTableToWorkbookEx() method that was causing the error when trying to add a new sheet. Please review the Beta 9 release for the corrections. This method would be the preferred method for writing out a workbook as it is faster than the SaveTableToWorkbook() method. The first writes directly to the file output; whereas, the latter writes to the internal cursors first and then creates the workbook. The SaveTableToWorkbook() was actually written first and the 'Ex' method was written to increase the speed output. The SaveTableToWorkbook() allows you to write to the workbook and not save it which allows you to further make changes.

Greg

brett39 commented 1 year ago

Hi Greg,

That’s for that. Tested with the updated class and all seems to work well.

Kind regards,

Brett Hudson Help Desk Manager

Redgum Software & Support Yellow Goanna Pty Ltd 66 Rosslyn Street West Melbourne VIC 3003 Ph: +61 3 9563-0350 | Fax: +61 3 9563-0348 www.redgumsoft.com.auhttp://www.redgumsoft.com.au/ ABN: 38 165 809 889

This transmission is intended for the addressee only. If you have received this transmission in error, please delete it and notify the sender. Thank you. From: ggreen86 Sent: Saturday, July 29, 2023 4:26 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: Brett Hudson @.>; Author @.***> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Brett--

I have corrected a bug in the SaveTableToWorkbookEx() method that was causing the error when trying to add a new sheet. Please review the Beta 9 release for the corrections. This method would be the preferred method for writing out a workbook as it is faster than the SaveTableToWorkbook() method. The first writes directly to the file output; whereas, the latter writes to the internal cursors first and then creates the workbook. The SaveTableToWorkbook() was actually written first and the 'Ex' method was written to increase the speed output. The SaveTableToWorkbook() allows you to write to the workbook and not save it which allows you to further make changes.

Greg

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

brett39 commented 1 year ago

Hi Greg,

Re the totalling, I had a bit more of a look at the documentation for the ‘savegridtoworkbookex’ method and I can see that if the grid that is being exported has the Excel function (i.e., ‘sum’, ‘average’ etc) specified in the grids COLUMN TAG property, that the selected column will be totalled (or averaged etc).

However, within our development system, all the grids are bult dynamically via a class and the layouts are defined by table setups. Using this functionality, we can have the same grid at different sites and each will be different because the users may have re-positioned fields relative to each other in the grid or added/removed fields to/in the grids. Because of this, we can’t always know what grid column number is going to be a numeric column as it may be changed.

So, for our purposes, I don’t think we can use the COLUMN TAG property to control what columns can be totalled.

I noted in the description for the method that If table formatting is not used, then the following function names are allowed: SUM() COUNT() COUNTA() COUNTBLANK(). I don’t understand what this means or if it refers to some other way that we can get totalling in the grids. Is it a reference to somehow using the ‘tcTableFormat’ field to allow summing of the fields in the exported grid?

Snippet of the text from the ‘SaveGridToworkBookEx’ method (page 101 or 119).

Comments: The Grid column formatting is used to define the cell formatting in the sheet. The Column Tag property contains the type of formula added to the totals row. If the Table format is used, the following are the allowed values (case sensitive) for column formulas (Tag): average Represents the arithmetic mean min Represents the smallest value. count Represents a count of the number of non-empty cells stdDev Represents the estimated standard deviation countNums Represents the number of cells that contain numbers sum Represents the arithmetic sum max Represents the largest value var Represents the estimated variance If table formatting is not used, then the following function names are allowed: SUM() COUNT() COUNTA() COUNTBLANK(

Kind regards,

Brett Hudson Help Desk Manager

Redgum Software & Support Yellow Goanna Pty Ltd 66 Rosslyn Street West Melbourne VIC 3003 Ph: +61 3 9563-0350 | Fax: +61 3 9563-0348 www.redgumsoft.com.auhttp://www.redgumsoft.com.au/ ABN: 38 165 809 889

This transmission is intended for the addressee only. If you have received this transmission in error, please delete it and notify the sender. Thank you. From: ggreen86 Sent: Thursday, July 27, 2023 2:51 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: Brett Hudson @.>; Author @.***> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Brett—

Not sure if my suggested use of properties was understood. There would be only a single call to the SaveGridToWorkBook() method; it would read the property settings in the grid and then generate the spreadsheet. In your case the grid would have all the columns that are numeric to be summed; the formula expression can be left blank (default to SUM). I could accommodate a property on the grid to indicate SUM numeric columns. So, there would not be two methods to call. However, in the table to be exported, there would have to have additional parameters sent.

Greg

From: brett39 @.> Sent: Thursday, July 27, 2023 12:37 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

Thanks for reviewing.

Perhaps if I explain how we are using the VFPX class. I work for a company that has an ERP system written in FoxPro to which we develop lots of add-ons and local programs for our clients. Most of the processing within the application is done via forms with a header and then a grid for the details. A typical screen would look like my screen shot below (Fig 1). The software has an inbuilt facility that allows the grid to be exported directly to Excel by right mouse clicking on the grid header (Fig 2). One of our default settings is to allow the numeric columns in the grid to be auto totalled. Our clients find this very useful. We are only really interested in being able to SUM the column and have not had any requests to have other functions (I guess like AVG or CNT etc). However, as you say, maybe other developers would like some control over other functions.

The way that we using your class is by downloading it, then amending the code in the methods with our changes for the parameters and the code updates that I have sent you. (I’ve attached a zipped-up copy of our amended version of your class, see SAVETABLETOWORKBOOK and SAVEGRIDTOWORKBOOK). We just re-apply these changes to the upda6ted class each time we spot a new release. In our instances, we are just saying that ALL the numeric columns are to be totalled rather than selecting some numeric columns to be totalled, then leaving some of the others as is etc. Ours is quite a simple change and certainly does not some have any sophisticated code other than performing the SUM function.

So, what you are suggesting for a future update sounds like a great idea and I’m sure would be useful for lots of other developers doing more intricate processing but ours is reasonably straight forward. I guess the easiest thing about passing the new parameters to the SAVETABLETOWORKBOOK and SAVEGRIDTOWORKBOOK methods is that we then don’t have to call another method to update the resulting spreadsheet as it will be produced with the summed columns.

Either way, my suggestion is only that and if you think there is a better way of doing it, then I’m in no position to disagree compared with the amount of work that you have already put into this great piece of code. 😊

@.<mailto:@.>

@.<mailto:@.>

Kind regards,

Brett Hudson Help Desk Manager

Redgum Software & Support Yellow Goanna Pty Ltd 66 Rosslyn Street West Melbourne VIC 3003 Ph: +61 3 9563-0350 | Fax: +61 3 9563-0348 www.redgumsoft.com.auhttp://www.redgumsoft.com.au/<http://www.redgumsoft.com.au%3chttp:/www.redgumsoft.com.au/> ABN: 38 165 809 889

This transmission is intended for the addressee only. If you have received this transmission in error, please delete it and notify the sender. Thank you. From: ggreen86 Sent: Thursday, July 27, 2023 1:29 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: Brett Hudson @.<mailto:@.>>; Author @.<mailto:@.>> Subject: Re: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Brett—

Thank you. You have a good idea and thank you for submitting the code. My thoughts on how to implement would be to use custom properties assigned to the grid columns to signify to add a column formula. There could be different formulas that the developer may want – so I would add a new Boolean property AssignFormula (.T. assign a formula) and Character property FormulaExpression (SUM, COUNT, etc. if empty then default to SUM). These two properties would be assigned to the Column objects of the grid (use a custom column class). The grid names instead of the column names would be a Boolean property AssignFieldNames (.T. use table field names, .F. use grid column names); this property would be assigned to the grid object.

What are your thoughts on the above? This way there are fewer parameters being passed that I have to check. And there would be less impact to existing code as I would use PEMSTATUS() to check for the existence of the properties.

Greg

From: brett39 @.<mailto:@.>> Sent: Tuesday, July 25, 2023 10:38 PM To: ggreen86/XLSX-Workbook-Class @.<mailto:@.>> Cc: Subscribed @.<mailto:@.>> Subject: [ggreen86/XLSX-Workbook-Class] VFPX WORKBOOKS Column totals (Issue #93)

Hi Greg,

I love using this class as it is a powerful tool. Congratulation on a great piece of coding.

I have made some amendments to your copy (Release 39) that I downloaded so that we can do a couple of additional things. One of them is to add another passed parameter to the SAVEGRIDTOWORKBOOK and SAVETABLETOWORKBOOK methods called TLSUM. If we call these methods in the class and set the TLSUM field to true, it indicates that we want to automatically sum the numeric columns that may exist in the grid and create a total line in the exported grid. I have copied in some code snippets that show this amendment. (see Snippet 1 and 2).

The other change was to put in an option to have the actual fields names of the grid put in as the column headers rather than the column titles. We used another passed parameter called TLHEADERNAME. (see Snippet 1 and 3).

Anyway, if you think that these are worthy additions to your existing code, please feel free to add them in.

Snippet 1. The passed parameters to the methods showing the TLSUM parameter.

LPARAMETERS togrid, txwb, tlfreeze, tlsavewb, tcsheetname, tlinclhiddencols, tlshowgridlines, tnsheet, tnbegrow, tnbegcol, tlsum, tlheadername

Snippet 2. Showing the code to build up the summed columns using TLSUM..

· *

*

· *

*

· IF tlsum

· FOR lncol = 1 TO lncolcount

· lcdatatype = THIS.getcelldatatype(lnwb, lnsh, 2, lncol)

· IF INLIST(lcdatatype, data_type_currency, data_type_float, data_type_int)

· lccolumn = THIS.columnindextoascii(lncol)

· THIS.setcellformula(lnwb, lnsh, lnrow + 2, lncol, "=SUM(" + lccolumn + ALLTRIM(STR(tnbegrow+1)) + ":" + lccolumn + ALLTRIM(STR(lnrow))+")")

· lnstylecell = THIS.getcellstyle(lnwb, lnsh, lnrow, lncol)

· THIS.setcellstyle(lnwb, lnsh, lnrow + 2, lncol, lnstylecell)

· THIS.setcellborder(lnwb, lnsh, lnrow + 2, lncol, border_top, border_style_double)

· ENDIF

· ENDFOR

· ENDIF

· *

*

· *

*

*

Snippet 3. Using the grid field names rather than the grid titles for the column headers using TLHEADERNAME. *

· *

*

· *

*

· IF tlheadername

· THIS.setcellvalue(lnwb, lnsh, 1, lncol, UPPER(locolumn.CONTROLSOURCE))

· ELSE

· THIS.setcellvalue(lnwb, lnsh, lnrow, lncol+tnbegcol-1, locolumn.header1.CAPTION)

· ENDIF

· *

*

· *

*

· THIS.setcellstyle(lnwb, lnsh, lnrow, lncol+tnbegcol-1, logrid.COLUMNS(lacolorder[lnCol, 2]).headerstyleid)

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ELSE

· lnrow = tnbegrow - 1

· FOR lncol=1 TO lncolcount

· locolumn = togrid.COLUMNS(lacolorder[lnCol, 2])

· THIS.setcolumnwidth(lnwb, lnsh, lncol+tnbegcol-1, THIS.convertpixelstoexcelunits(locolumn.WIDTH))

· ENDFOR

· ENDIF

*

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

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/93#issuecomment-1652857183, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGRCOXFJDOHTXQJBPHQ6TX3XSHOBJANCNFSM6AAAAAA2X5BV24. 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/93#issuecomment-1652899371, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33NH2DX5QYJXLX7EDEDXSHV5ZANCNFSM6AAAAAA2X5BV24. You are receiving this because you commented.Message ID: @.**@.>>

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