Client-side export with custom formatting

[svashi]

Hello, Isomorphic. We stumbled upon a problem when trying to do some custom formatting in client side export.


We are using client side formatted export, I checked the showcase example “com.smartgwt.sample.showcase.client.dataintegration.java.others.FormattedExportSample”.

But our case is little bit different - we are using DataSource.fetchData() and manually set this data to listGrid.setData() in success callback. The DataSource.fetchData() is invoked onClick event of the button “LoadData” button that I have added to the sample and fetch Data based on some criteria.
The problems that we face are:

1. The cell formatters are not being invoked for export. I am using a CellFormatter for unitPriceField , it works for grid, but not for export :
ListGridField unitPriceField = new ListGridField("unitPrice");
unitPriceField.setAlign(Alignment.RIGHT);
unitPriceField.setWidth(64);
unitPriceField.setAlign(Alignment.RIGHT);
unitPriceField.setCellAlign(Alignment.RIGHT);
unitPriceField.setExportRawValues(false);
final NumberFormat numFormatter = NumberFormat.getFormat("###,###,###.000");
unitPriceField.setCellFormatter(new CellFormatter() {
public String format(Object value, ListGridRecord record, int rowNum, int colNum) {
double price=record.getAttributeAsDouble("unitPrice");
return numFormatter.format(price/10);
}
});
What we want to show in export the “value”, where value = someCustomizedFormatter(fieldValue/10) ; so is there any other way that we can customize formatting the data for export?
I also checked the “setExportFormat()” method, but it is using a fixed format string, it can’t be used for complex calculations.

2. The other problem is with ListGridField, which is not being shown in the grid but has to be shown in the export. If these field is shown in grid, only then it is being displayed in export, otherwise not.
The idea is we want to show some different fields in export other than showing in grid.
In sample code I made the orderDateField.setHidden(true);
And tried to use setExportFields() in sample code
dsRequestProperties.setExportFields(new String[]{"orderID", "itemID", "unitPrice", "orderDate", "itemTotalField"}); but in export it is showing only "orderID", "itemID", "unitPrice", "itemTotalField"

You can find the changed showcase sample in attachments.
Thanks in advance for your support.

Best regards,
Sonia

[Isomorphic]

We were not able to reproduce your first issue. Please, try again with a more recent build. Moreover, we've applied a fix to solve the 2nd issue. So, please try the next nightly build, dated May 22.

Regards
Isomorphic Software

[svashi]

Hello Isomorphic,

I forgot to mention, but we use version 5.0 -p<20171110>.
Could you port back the solution for second problem to 5.0 as well?
Can you reproduce the first issue for version - 5.0? If it is reproducible, Could you please also provide a fix for it?

Thanks in advance.

Regards,
Sonia

[Isomorphic]

We were not able to reproduce the first issue in 5.0 either, so please try again using the latest code of this version. Also, the fix for the 2nd issue has been backported to 5.0. So, please try the next nightly build, dated May 23.

Regards
Isomorphic Software

[svashi]

Hello Isomorphic,

Thanks for the fix for second problem.
But in export file - the column titles are named as internal field names instead of field titles (localized)
I have tried to set the PropertyIdentifier in sample code :
dsRequestProperties.setExportPropertyIdentifier(PropertyIdentifier.TITLE);
but it doesn't work.

Thanks in advance.

Regards,
Sonia

[svashi]

Hello Isomorphic,

I am able to reproduce the first issue(related to cell formatting).
In the showcase sample code - com.smartgwt.sample.showcase.client.dataintegration.java.others.FormattedExportSample
I am using CellFormatter for "Qty" ListGridField and test it with two case:

First case : Cell Formatter returning a string "1.5"

ListGridField quantityField = new ListGridField("quantity","Qty");
quantityField.setWidth(48);
quantityField.setCellFormatter(new CellFormatter() {

@Override
public String format(Object value, ListGridRecord record, int rowNum, int colNum) {
return "1.5";
}
});
In grid, it shows formatted "Qty" - "1.5"
But has problem with export type - "XLS (Excel97)" and "XLSX (Excel2007/OOXML)"
with "csv" , it is working fine.

Second case : cnage the Cell Formatter and return a string "w1.5"

ListGridField quantityField = new ListGridField("quantity","Qty");
quantityField.setWidth(48);
quantityField.setCellFormatter(new CellFormatter() {

@Override
public String format(Object value, ListGridRecord record, int rowNum, int colNum) {
return "w1.5";
}
});

Now, it shows formatted "Qty" - "w1.5" in grid, and export type - "csv", "xls", "xlsx" as well.

Formatting is different for grid and export if the formatted string only contains number .

Could you please try to reproduce it? and provide a fix - if it is reproducible.

Thanks in advance for your support.

Regards,

Sonia FormattedExportSample.java

[Isomorphic]

What you're running into is an intended behavior, or at least, it's the best we can with Excel's quirks.

If we give Excel a formatted number like "500,000" it will not treat that value as a number, so sums are other basic spreadsheet features won't work. So we use the heuristic that if your formatted value parses as a number, you probably want it treated as a number in the spreadsheet, so we give Excel the unformatted numeric value and tell Excel it's a numeric data value.

You might expect that we would give Excel *both* the formatted value and the numeric value, but this is only possible by creating what's called a "custom format" for that cell, and Excel is limited to a very small number of these.

With this Excel limitation in mind, we expect you'll want to stay with our behavior here. If not, one approach would be to change the field to string type (perhaps just for export, so that sorts, etc still work as expected).

We're check on the titles issue.

[svashi]

Hello Isomorphic,

Any updates on the titles issue.

Thanks & Regards,

Sonia

[Isomorphic]

Today is a holiday in the US and very few business hours have elapsed since you reported that issue, please allow reasonable time.

Note: if you provide numeric formats via listGridField.format/exportFormat, those are sent to the server and translated into Excel formats. So this could both simplify your code and get your formats working in Excel.

[Isomorphic]

But in export file - the column titles are named as internal field names instead of field titles (localized)

This issue should be resolved in today's build (ie, the build that will appear on the website later today, dated May 30). Please try it and let us know if you still see the issue.

[svashi]

Hello Isomorphic,

Thank you for the fix.
But it shows the capitalised and space separated internal field names not the field titles.
For example - if internal field name is "internalId" then it shows "Internal Id" as column title in export file.

Thanks & Regards,

Sonia

[Isomorphic]

In our internal tests, we are explicitly testing for specified titles (as opposed to the derived titles you describe) on exports directly on dataSources and on both databound and unbound listGrids, and, in the case of a bound listGrid, with titles specified in the DataSource and overridden at the listGrid level. In all of these cases, we get the true titles exported as we expect. We would only expect to see a derived title like "Internal Id" if no proper title was provided for a field.

So in short, we do not reproduce this problem and we would need a complete, minimal concrete test case to investigate it further

Regards,
Isomorphic Software Support

[svashi]

Hello Isomorphic,

I am able to reproduce it using showcase sample code - com.smartgwt.sample.showcase.client.dataintegration.java.others.FormattedExportSample

I have made the following changes in sample code :
As I discussed in my first post that we are using a button to load data into grid based on some criteria.
The other changes are:
1 - changed the title of "orderDateField" ListGridField to "Date" instead of default "Order Date"

ListGridField orderDateField = new ListGridField("orderDate");
orderDateField.setTitle("Date");

2 - using setExportFields()
dsRequestProperties.setExportFields(new String[]{"orderID","itemID","quantity","orderDate","itemTotalField"});

The final version of sample after all these changes is attached.

Now when I exported as XLS/XSLX (as well as other formats) it shows the the following column titles :

Order ID, Item ID , Quantity, Order Date , itemTotalField

- here the "Item ID" is prettified internal field name (in grid it shows "Item Name")
- "Quantity" is the prettified internal field name (in grid it shows "Qty")
- "Order Date" is the prettified internal field name, and in code the title is set as "Date" (in grid it shows "Order")
- "itemTotalField" is the raw internal field name (in grid it shows "Item Total")

instead of (according to the grid):
Order ID , Item Name , Qty , Date , Item Total

Thanks in advance for your support.

Regards,

Sonia FormattedExportSample.java

[Isomorphic]

Thank you for the example case - we do see the issue, and an engineer is scheduled to look into it. We will report back on this thread soon when the issue is resolved.

Regards,
Isomorphic Software Support

[svashi]

Hello Isomorphic,

Any updates on this title issue.
Thanks in advance for your support.

Regards,

Sonia