search

ggreen86 / XLSX-Workbook-Class

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

Support column InputMask and Format #27

Closed DougHennig closed 4 years ago

DougHennig commented 5 years ago

Have SaveGridToWorkbook and SaveGridToWorkbookEx support the InputMask and Format properties of each grid column, at least for numeric values. For example, with Format = "$" and InputMask = "999,999.99", use the appropriate format in Excel.

ggreen86 commented 5 years ago

Doug--

Sorry for the late reply -- yes I believe it could be done without too much of a performance hit. Not sure when I can do this but will keep this as a to-do item. Right now I am busy building a house for my wife and I (I am actually doing much of the work myself) -- it is nearing a point that we can move in.

Greg

From: Doug Hennig notifications@github.com Sent: Tuesday, April 30, 2019 9:29 AM To: ggreen86/XLSX-Workbook-Class Cc: Subscribed Subject: [ggreen86/XLSX-Workbook-Class] Support column InputMask and Format (#27)

Have SaveGridToWorkbook and SaveGridToWorkbookEx support the InputMask and Format properties of each grid column, at least for numeric values. For example, with Format = "$" and InputMask = "999,999.99", use the appropriate format in Excel.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/27, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AGWB33MKMFSNVOEG3IA3KWDPTBCUTANCNFSM4HJLUATQ.

DougHennig commented 4 years ago

Hi Greg.

I added support for % formats to SaveGridToWorkbookEx with the following changes:

*** DH 2020-03-17: handle all numeric types
***         CASE lcVarType = "N"
            CASE lcVarType $ "NFIBY"
                DO CASE
*** DH 2020-03-17: handle % format
                    CASE '%' $ loGrid.Columns[lnCol].InputMask and '.' $ loGrid.Columns[lnCol].InputMask
                        loGrid.Columns[lnCol].NumFmtsIndex = CELL_FORMAT_PERCENT_FLOAT

                    CASE '%' $ loGrid.Columns[lnCol].InputMask
                        loGrid.Columns[lnCol].NumFmtsIndex = CELL_FORMAT_PERCENT_INTEGER
*** DH 2020-03-17: end of new code
                    CASE EMPTY(loGrid.Columns[lnCol].Format)
                        loGrid.Columns[lnCol].NumFmtsIndex = 0

                    CASE loGrid.Columns[lnCol].Format = "L"
                        loGrid.Columns[lnCol].NumFmtsIndex = 99

                    CASE loGrid.Columns[lnCol].Format = "R"
                        lnNumFmtsId = lnNumFmtsId + 1
                        loGrid.Columns[lnCol].NumFmtsIndex = lnNumFmtsId + 164
                        loGrid.Columns[lnCol].FormatCode   = ''

                    CASE loGrid.Columns[lnCol].Format = "$"
                        loGrid.Columns[lnCol].NumFmtsIndex = CELL_FORMAT_CURRENCY_RED_PAREN
                ENDCASE

            CASE lcVarType = "T"
                loGrid.Columns[lnCol].NumFmtsIndex = CELL_FORMAT_DATETIME_DDMMMYYYY_TTAM

*** DH 2020-03-17: commented this out since handled above
*           CASE lcVarType = "Y"
*               loGrid.Columns[lnCol].NumFmtsIndex = CELL_FORMAT_CURRENCY_RED_PAREN
        ENDCASE
ggreen86 commented 4 years ago

Code changes incorporated into Release 32.