Excel Cell Format

[Carl P.]

Is it possible to change the format of a cell in Genero 2.50?

Our users want to be able to use the Filter functionality within Excel on dates. With the date cells being formatted as General they can only filter on a string, see ScreenShot1.jpg, instead of by date, see ScreenShot2.jpg.

Changing the column to a Date type also has no effect. You have to edit the value in the cell and then save it to force Excel to accept it as a date.

I've tried using the RTL Date Class in Genero Studio:

Date.fromIsoValue(r_audtr.pau_date.isoValue).format("DD/MM/YYYY")

but that doesn't change the cell type.

Has anyone tried to do this? Is there a workaround?

Thanks,

Carl

[Leo S.]

Hi Carl, Genero puts the Excel values as strings into the clipboard (it does it internally by calling the standard 'cbset' front call), so there is no direct excel clipboard format created.
You will need to look at the various solutions how to use a bit VBA code to achieve what you want and call then this vba piece from Genero (assuming you are using GDC as client)
googling "excel vba format column date" gives me
http://stackoverflow.com/questions/12324190/vba-code-to-set-date-format-for-a-specific-column-as-yyyy-mm-dd
HTH and Regards, Leo

[Stefan S.]

Hello,

we use the wincom-Frontend-Functions to Format the Excel-Table after filing the cells.

therefore we use an Array to set the Column-Format, I think a Date-Format must be able to.

define gl_zellenformat  record   # bestimmt das Spaltenformat für
          s01 string,            # die Entsprechende Excel-Zeile
          s02 string,            # @=Textformat
          s03 string,            # #.##0,00     tausenderpunkte mit 2 NK
          s04 string,            #################### ganze Zahl ohne Nachkommastellen
          s05 string,
          s06 string,
....

then with the Wincom-Frontcall I set the Format for each Column

#Formate für die Spalten
   if gl_zellenformat.s01 is not null then
      let lo_format = gl_zellenformat.s01
      let lo_zelle = "A:A"
      let lo_anweisung = "Range(\"", lo_zelle clipped, "\").Select"
      call ui.interface.frontcall("WinCOM", "CallMethod", [gl_xlapp, lo_anweisung clipped], [lo_fehler])
      call ui.Interface.frontCall("WinCOM", "SetProperty", [gl_xlapp, "Selection.NumberFormat",
                                   lo_format],[lo_fehler])
   end if



a second way is to set the format for each cell (it is a little bit slow)
therefore I have several Functions, to format the cell.

f.e.:
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
function zellen_schriftgroesse(lo_zelle, lo_groesse)
define lo_zelle     string,
       lo_farbe     string,
       lo_groesse   string,
       lo_err       string,
       lo_anweisung string

   let lo_anweisung = "Range(\"", lo_zelle clipped, "\").Select"
   call ui.interface.frontcall("WinCOM", "CallMethod", [gl_xlapp, lo_anweisung], [lo_err])
   call ui.Interface.frontCall("WinCOM", "SetProperty", [gl_xlapp, "Selection.Font.Size", lo_groesse],[lo_err])
   call CheckError(lo_err, __LINE__)

end function
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
function zellen_ausrichtung(lo_zelle, lo_grad)
define lo_zelle     string,
       lo_farbe     string,
       lo_grad      string,
       lo_err       string,
       lo_anweisung string

   let lo_anweisung = "Range(\"", lo_zelle clipped, "\").Select"
   call ui.interface.frontcall("WinCOM", "CallMethod", [gl_xlapp, lo_anweisung], [lo_err])
   call ui.Interface.frontCall("WinCOM", "SetProperty", [gl_xlapp, "Selection.Orientation", lo_grad],[lo_err])
   call CheckError(lo_err, __LINE__)

end function
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
function zellen_zahlenformat(lo_zelle, lo_format)
define lo_zelle     string,    #   @=Textformat
       lo_farbe     string,    #   #.##0,00
       lo_format    string,    #   ######## ganze Zahl ohne Nachkommastellen
       lo_err       string,    #   00000    fünfstellig mit führenden nullen
       lo_anweisung string

   let lo_anweisung = "Range(\"", lo_zelle clipped, "\").Select"
   call ui.interface.frontcall("WinCOM", "CallMethod", [gl_xlapp, lo_anweisung], [lo_err])
   call ui.Interface.frontCall("WinCOM", "SetProperty", [gl_xlapp, "Selection.NumberFormat", lo_format],[lo_err])
   call CheckError(lo_err, __LINE__)

end function

[Alex G.]

GRW currently creates only numeric or string cell types in Excel spread sheets. Text from DECIMALFORMATBOX produces numeric cells and all other (e.g. WORDBOX) produce string cells. Since date values are drawn using WORDBOX they end up as strings without format. What we need is something like a DATEFORMATBOX that preserves the value and the formatting string as separate information as we do for the DECIMALFORMATBOX. I am afraid that there currently is no workaround for this requirement.
Regards,
Alex

[Reuben B.]

To clarify any confusion the above posts may cause, there are many different ways utilising Genero you can interact with Excel.

1. Copy and Paste.  Not just the "Copy Visible Table" option available in tables in the GDC but with the GDC it is also possible to write a very simple "Copy Entire Table" function that enables the end-user to copy an entire table into the clipboard from where it can then be pasted into Excel

2. Create a .csv file via base.Channel

3. With GDC on Windows, you can use the WinCom frontcalls to create and edit a spreadsheet

4. Use IMPORT JAVA and the Apache POI Java libraries

5. Use Genero Report Writer to output a report as an .xls or xlsx file

As the Original Post is in the "Reporting Tools(GRW, GRE)" sub-forum, the original poster is likely to be using Genero Report Writer to produce their .xls or .xlsx file, and Alex (as lead developer for GRW) response is most appropriate.

Leo and Stefans response relate to using the WinCom frontcalls to create and edit a spreadsheet.  What you could potentially do if using GDC on Windows, is use WinCom frontcalls to open the spreadsheet produced by GRW and edit the cells as required.  (to figure out appropriate calls, a handy technique is to record as a macro the steps you want to do, and then note the macro code).  (you would also have to transfer the file from back-end to front-end)

Similarly you could potentially use IMPORT JAVA and the Apache POI libraries to do the same thing, that is generate the spreadsheet via GRW, then use Apache POI  to open the resultant spreadsheet, and edit the cells as required.  I don't know the Apache POI that well but it should be achievable.  It has the advantage as it will work on platforms other than just Windows, and it will be working with the file on the back-end where it is produced by GRW rather than having to transfer it to front-end.

Hope that helps,

Reuben

[Alex G.]

Just saw that this topic is the second most viewed so that it is perhaps useful if I mention that we introduced in version 3.20 the DATAFORMATBOX which fixes this issue.