Skip to main content



      Home
Home » Eclipse Projects » NatTable » Formatting numbers in excel export
Formatting numbers in excel export [message #1857667] Mon, 20 February 2023 11:47 Go to next message
Eclipse UserFriend
I want to export a nattable containing double values to excel.

The problem is the formatting of the output.

* Default column formats in the excel workbook will be overwritten in org.eclipse.nebula.widgets.nattable.extension.poi.PoiExcelExporter#exportCell
* Formatting the values using a display converter to strings is possible, but the excel file demands the conversion of the fields to number format to use it in later calculations
* the ..#exportCell - function is not really extendable because their are a much of hidden things
* the format will be requested by #getDataFormatString only for the types Calendar and Date and overwriting would not help!?

Do I missed something or what could be a start for a solution?

Thanks
Uwe Peuker
Re: Formatting numbers in excel export [message #1857678 is a reply to message #1857667] Tue, 21 February 2023 01:20 Go to previous messageGo to next message
Eclipse UserFriend
Yes, you are missing the ExportConfigAttributes.EXPORT_FORMATTER

By default the export values are Strings, as this was the original implementation several years ago, where no other types could be exported. If you want another type to be exported, you can define this via an IExportFormatter.

The _773_GridExcelExportFormatterExample has an example for the row header to export the row numbers as Integers:

configRegistry.registerConfigAttribute(
        ExportConfigAttributes.EXPORT_FORMATTER,
        new IExportFormatter() {
            @Override
            public Object formatForExport(ILayerCell cell, IConfigRegistry configRegistry) {
                // simply return the data value which is an
                // integer for the row header doing this avoids
                // the default conversion to string for export
                return cell.getDataValue();
            }
        },
        DisplayMode.NORMAL,
        GridRegion.ROW_HEADER);


Doing something similar in your setup should export the double values as double.

The corresponding sources are:

NatExporter lines 489-495
PoiExcelExporter lines 278-279
Re: Formatting numbers in excel export [message #1857683 is a reply to message #1857678] Tue, 21 February 2023 02:46 Go to previous messageGo to next message
Eclipse UserFriend
Thanks for the response, but I think this is not my problem.

I have the value as Number for the exportCell-function. And it will be stored in the excel file.
But my aim is to set a formatting cell style for the number columns in excel.

In line 231-232 in PoiExcelExporter the CellStyle is created and applied. But it misses the data format for every value except Date and Calendar.

So my idea would be to expand the getDataFormatString function more abstract and to deliver a common "data format" and not only a "date format". Overwriting the function in a superclass does not work because of the restriction in lines 216-219 in PoiExcelExporter. Possibly this check should be removed to call it for every type!? I see the problem of the default return value of this function!

Another solution could be to extend the function with a cell format parameter? So the solution to search for it inside of exportCell could be replaced.

Added a sample, which would help in my case without breaking the previous behavior .

[Updated on: Tue, 21 February 2023 08:51] by Moderator

Re: Formatting numbers in excel export [message #1857691 is a reply to message #1857683] Tue, 21 February 2023 09:07 Go to previous messageGo to next message
Eclipse UserFriend
OK got it.

https://bugs.eclipse.org/bugs/show_bug.cgi?id=581562

Let me know if this fixes the issue for you.
Re: Formatting numbers in excel export [message #1857692 is a reply to message #1857691] Tue, 21 February 2023 10:13 Go to previous messageGo to next message
Eclipse UserFriend
Looks great for me.

If I understand it right I have two possibilities to format for excel:

1. Overwriting the getDataFormatString in an extended PoiExcelExporter or
2. (may be the better way) Add labels for formatting in the NatTable and registering special formats depending on labels?

Last question: If it is merged, how can I get it for my application to prevent a bunch of copy and paste code.

Usually I use the URL http://download.eclipse.org/nattable/releases/2.0.5/repository?

[Updated on: Tue, 21 February 2023 10:18] by Moderator

Re: Formatting numbers in excel export [message #1857695 is a reply to message #1857692] Tue, 21 February 2023 11:54 Go to previous messageGo to next message
Eclipse UserFriend
Well, typically you would already have labels as you have numbers that you want to convert in the table as well as in the export. And configuration is typically better than overriding internals.

It is already merged and available as snapshot build.

https://www.eclipse.org/nattable/download.php
Re: Formatting numbers in excel export [message #1857713 is a reply to message #1857695] Wed, 22 February 2023 02:57 Go to previous messageGo to next message
Eclipse UserFriend
Thanks, works fine.

But there is a little side effect which could be disturb other existing users:
If you don't overwrite the geteDataFormatString function and you don't register formats mit related labels, every number in the excel table will be formatted as date.

I would see the following extensions:

- Adding additional ExportConfigAttributes like LONG_FORMAT and DOUBLE_FORMAT and look for them and not for DATE_FORMAT in the case of the number types
- Do not look for the format for String and Boolean values
- Possibly expand Integer with Long and(or Double with Float (I know there are more!?)

Note: Possibly Integer could be ignored too? As I know excel does not support formats like "0.00 kg"?

[Updated on: Wed, 22 February 2023 03:17] by Moderator

Re: Formatting numbers in excel export [message #1857717 is a reply to message #1857713] Wed, 22 February 2023 09:14 Go to previous messageGo to next message
Eclipse UserFriend
You can only use formats that are specified in org.apache.poi.ss.usermodel.BuiltinFormats. At least this is what the Javadoc of org.apache.poi.ss.usermodel.CellStyle.setDataFormat(short fmt) says.

As you noticed a regression (that I would agree with) I added the ExportConfigAttributes.NUMBER_FORMAT which is not set by default. This should fix the problem and avoid regressions. I changed the default handling to return 0.00 for Double and Float and 0 for any other Number type.
Re: Formatting numbers in excel export [message #1857737 is a reply to message #1857717] Thu, 23 February 2023 07:51 Go to previous messageGo to next message
Eclipse UserFriend
Apologies for the intrusiveness.

A next remark to the default format for numbers.
It could possibly better to deliver "null", if no format is defined.
This way the behavior would be as before and the numbers will be unformatted plain in the excel file.
Rebuilding this behavior by configuring a "null" value as format always would lead to the default and would not be possible.
Re: Formatting numbers in excel export [message #1857742 is a reply to message #1857737] Thu, 23 February 2023 08:15 Go to previous messageGo to next message
Eclipse UserFriend
But that would be consistent to the behavior with Date values.

And, if you don't want to export the value as a Number, don't configure an export formatter

[Updated on: Thu, 23 February 2023 08:17] by Moderator

Re: Formatting numbers in excel export [message #1857746 is a reply to message #1857742] Thu, 23 February 2023 11:05 Go to previous messageGo to next message
Eclipse UserFriend
Yes you are right.

But it differs from the behavior before. Numbers had been written as numbers in Excel but without explicit formatting.

It is not my use case, but it could be a use case sometimes for anyone.

I have not tested, what would be the format in excel for Date oder Calendar without setting an explicit format.
Possibly the more general solution would be to never offer a default even for Date and Calendar?

Re: Formatting numbers in excel export [message #1857755 is a reply to message #1857746] Fri, 24 February 2023 01:39 Go to previous messageGo to next message
Eclipse UserFriend
OK, as defaults in a framework often lead to discussions, I remove the default again. So actually the fix is only to add a new configuration attribute to enable number formatting, without changing the previous behavior.

If there is no format specified for Date or Calendar objects, the value in Excel will be a number. It would be not possible for a user to see that it is actually a Date. That's why the requirement at the implementation time was to set a default format in case nothing is applied. And if really someone wants such a IMHO wrong behavior, getDataFormatString() can be overriden. Therefore I will not remove the default format for Date or Calendar.
Re: Formatting numbers in excel export [message #1857756 is a reply to message #1857755] Fri, 24 February 2023 01:51 Go to previous messageGo to next message
Eclipse UserFriend
>Therefore I will not remove the default format for Date or Calendar.

I totally agree, because I have not checked the default behavior in excel.

BTW: There is already a function "setNumberFormat" which seems not to have a functionality that I have seen? May be that could be used as default value? (Sorry for the new idea).
Re: Formatting numbers in excel export [message #1857757 is a reply to message #1857756] Fri, 24 February 2023 02:05 Go to previous messageGo to next message
Eclipse UserFriend
Nope, the NumberFormat is used in combination with formulas.
Re: Formatting numbers in excel export [message #1857760 is a reply to message #1857757] Fri, 24 February 2023 02:52 Go to previous message
Eclipse UserFriend
Ok, then I haven't recognized that.
Previous Topic:Any plans for Web/Theia based NatTable?
Next Topic:Buffering for tables
Goto Forum:
  


Current Time: Thu May 22 06:52:36 EDT 2025

Powered by FUDForum. Page generated in 0.05268 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top