Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    Problem with Excel export and column data types

    Hi,

    i'm doing a listgrid excel export (ooxml) with "exportClientData" (see code below), but there's an issue with the columns, they all get the type "general" when opened in Excel. Date columns for example, becomes just a bunch of numbers, you have to go in manually on each column and set it right, really annoying.

    The reason for doing exportclientdata is to 1. avoid costly db queries and 2. preserve client-side Nordic formatting of fields.

    I looked but didn't find any way to tell the excel export what column types i want in the exported file.

    Happy for pointers!

    EDIT: I checked other threads and saw that you in one wrote:
    If you use a DataSource where you have declared the field type as a number type (eg "integer") then it will be exported to Excel as a number.
    this is not my experience, all become "general" data type, regardless of what i have in the datasource XML.

    export code:
    Code:
    DSRequest dsRequestProperties = new DSRequest();
                dsRequestProperties.setExportAs(ExportFormat.OOXML);
                dsRequestProperties.setExportDisplay(ExportDisplay.DOWNLOAD);
                dsRequestProperties.setAttribute("exportFilename", getFileName());
                dsRequestProperties.setExportFields(reportFields);
                dsRequestProperties.setExportDelimiter(";");
                reportGrid.exportClientData(dsRequestProperties);
    Last edited by mathias; 2 Sep 2012, 13:37.

    #2
    You should see that numeric values are automatically treated as numbers but *only* if you have not added any formatting (eg leading '$') that would them to fail to parse as numbers in Java.

    Comment


      #3
      Hey Iso, thanks for responding.

      OK, so i do have a few formatters in the system, since we're in the Nordic countries:

      1. I am setting date formatters and datetimeformatters via the
      Code:
      DateUtil.setNormalDateDisplayFormatter
      and other methods.

      2. I have a float formatter on all float fields, for the same reason as above.


      Does this mean that there is no way to get the fields mentioned above to be correct data types?

      You have any suggestions as to how people normally solve this? Is it because you send it unformatted to the server and there try to convert to correct data types or what?



      As as sidenote, my "text" fields also become generic, is this intended?


      (Finally, i have another excel-related issue but i'll create a new thread for that, would be great if you had time to read it)

      Comment


        #4
        In general, this codepath assumes there is no server-side DataSource definition and potentially incomplete type information on the client, and does a lot more guessing.

        We automatically handle dates becoming true dates by sending both the underlying date value and formatted value, but do not automatically do the same thing for numbers. That makes sense as an enhancement (but we can't provide an ETA unless you sponsor it), although it would greatly increase data volume so might require a setting to disable it.

        What behavioral distinction in Excel are you seeing for "text" vs generic?

        Comment


          #5
          "text" vs "generic" is not a big deal, i just pointed it out as a comment on that all data types, including text, become general.

          I have "double" fields that i havent touched besides creating them, they become "general" too.

          You're saying that you do sent the date value, so you're saying that it *should* work??

          So why is it that i don't get it formatted? It's just a long number in Excel, in a column of the type "general"... any ideas?

          Comment


            #6
            It should work for date values, numeric values will become "generic". Both behaviors are visible in the sample.

            Comment


              #7
              OK, i have done further testing, and from what i can see the "exportclientdata" function does NOT create the excel column as "date" type if i set custom formatters, as i would have expected.

              If you look at the code below please. This is what happens:
              ---If i HAVE the global date formatters, it looks good in the listgrid that i am to export, but the export date columns are of type "Generic" and are displayed as for example "41184,5069444444" in Excel.

              ---If i REMOVE the global date formatters from my bootstrap, the listgrid dates will not look as i want, but the columns in the Excel export WILL be of type "Date".


              Please, oh please, let me know that there's something i can do...

              code to export:
              Code:
              DSRequest dsRequestProperties = new DSRequest();
              dsRequestProperties.setExportAs(ExportFormat.OOXML);
              dsRequestProperties.setExportDisplay(ExportDisplay.DOWNLOAD);
              dsRequestProperties.setAttribute("exportFilename", fileNameCreator.createReportName());
              dsRequestProperties.setExportFields(reportFields);
              dsRequestProperties.setExportDelimiter(";");
              
              reportGrid.exportClientData(dsRequestProperties);
              code to set global date and time formatters:
              Code:
              final DateTimeFormat dateFormat = DateTimeFormat.getFormat("yyyy-MM-dd");
              final DateTimeFormat dateTimeFormat = DateTimeFormat.getFormat("yyyy-MM-dd HH:mm");
              
              DateDisplayFormatter dateFormatter = new DateDisplayFormatter() {
                          @Override
                          public String format(Date date) {
                              String form = dateFormat.format(date);
                              return form;
                          }
                      };
                      DateDisplayFormatter datetimeFormatter = new DateDisplayFormatter() {
                          @Override
                          public String format(Date date) {
                              String form = dateTimeFormat.format(date);
                              return form;
                          }
                      };
              
                      DateUtil.setNormalDateDisplayFormatter(dateFormatter);
                      DateUtil.setShortDateDisplayFormatter(dateFormatter);
                      DateUtil.setShortDatetimeDisplayFormatter(datetimeFormatter);
              
              
                      DateUtil.setDateParser(new DateParser() {
                          public Date parse(String dateString) {
                              try {
                                  return dateTimeFormat.parse(dateString);
                              } catch (IllegalArgumentException e) {
                                  try {
                                      return dateFormat.parse(dateString);
                                  } catch (IllegalArgumentException e1) {
                                  }
                              }
                              return null;
                          }
                      });

              Comment


                #8
                For both numbers and dates with custom formats we have a basic Excel limitation - you can't create an arbitrary display value for a given stored value. You must instead create an Excel "format" and apply it to the cell. And Excel limits the number of formats you can define, so there aren't enough that you could apply one for every cell.

                This is why only built-in formats will work, because we translate them to a single Excel format that we can apply to every cell. But we can't reverse engineer what a custom formatter is doing in order to create a single Excel format.

                We plan to work around this by providing a mechanism to provide a format string that we can translate to an Excel format.

                Comment


                  #9
                  OK... I'm not sure this makes sense to me, so please help me out.

                  You wrote:
                  We automatically handle dates becoming true dates by sending both the underlying date value and formatted value
                  And:
                  For both numbers and dates with custom formats we have a basic Excel limitation - you can't create an arbitrary display value for a given stored value. You must instead create an Excel "format" and apply it to the cell. And Excel limits the number of formats you can define, so there aren't enough that you could apply one for every cell.

                  Here is the *weird* thing.

                  If i, again, remove ALL global formatters as described above, i get undesired formatting everywhere in grids, forms etc.

                  However, if i THEN go ahead and apply a custom formatting ONLY on my report listgrid date columns, like this:

                  Code:
                  field.setCellFormatter(createCellFormatter(NubaCommonConstants.dateFormat));
                  field.setAlign(Alignment.LEFT);
                  My report dates looks JUST like i want "2012-08-01 12:00" for example, and it DOES become date in the Excel file!! But again, in the rest of my app, i get unwanted date formats...

                  So, it seems that the global formatting destroys it, not any actual custom formatters on the listgrid im running on!

                  How is this possible?? To me, it seems that there IS a way, but the, in the context of the report, unrelated global formatting screws it up!
                  Last edited by mathias; 6 Oct 2012, 12:21.

                  Comment


                    #10
                    Hey, long time coming with no response, so i'm bumping in hope of a renewed discussion.


                    So, my statement still stands - if i apply a formatter to a listgrid field directly, it doesn't break the export, but a global formatter does.

                    Is there any way you could remedy this, i.e. make global formatters not break the client side export, just like listgrid specific ones don't?

                    Comment

                    Working...
                    X