search

firegloves / MemPOI

A library to simplify export from database to Excel files using Apache POI :japanese_goblin:
MIT License
58 stars 7 forks source link

Dates not exporting to Excel properly #53

Closed apoorva-sriv closed 2 years ago

apoorva-sriv commented 2 years ago

Dates from Oracle SQL are getting converted to random floats in Excel. I need to change the format to Date in Excel to view them correctly.

firegloves commented 2 years ago

Hi @apoorva-sriv ,

did you have a look at this issue?

In case it does not resolve your problem, can you post some info to reproduce the issue? I need at least the definition of the date field inside Oracle

apoorva-sriv commented 2 years ago

@firegloves, I found it's due to using XSSFWorkbook (since I need a table) instead of the default SXSSFWorkbook. None of the solutions in issue #8 are working for XSSFWorkbook. I have a column of Oracle DATETIME DATE.

Also, when I use StoneStyleTemplate with XSSFWorkbook, only the header cells have the correct background; the table body cells are still white. (It works properly with SXSSFWorkbook.) Is XSSFWorkbook support really that bad? That makes me hesitant to use it since I don't know what other features it may be missing/using incorrectly.

firegloves commented 2 years ago

@apoorva-sriv currently MemPOI does not support styles for tables, so the white body is quite normal. It could be a future improvement, even if I didn't either analyze the situation to be sure that it is supported by Apache POI. And if I well remember, tables are supported only using XSSFWorkbook, so what do you mean that everything works as expected while using SXSSFWorkbook?

XSSFWorkbook should not suffer from other (known) issues, except the ones reported by Apache POI (mainly about heavy memory usage).

I'll test the DATETIME issue as soon as possible and I'll let you know.

apoorva-sriv commented 2 years ago

OK, the white table body cells in XSSFWorkbook make sense now. Since I thought I found two issues in XSSFWorkbook, I thought there was something wrong with that format 😅

firegloves commented 2 years ago

@apoorva-sriv according to Oracle docs, it seems that DATETIME is not a supported data type. Can you please provide me with an example to reproduce your issue?

What I need is the SQL statement to create the table, the SQL query to execute to fetch data from the database and the MemPOI code that you are using

apoorva-sriv commented 2 years ago

Sorry, it is DATE.

firegloves commented 2 years ago

@apoorva-sriv I'm not able to reproduce your issue. I have a table with a DATE field and I am using a XSSFWorkbook. Basing on your first request that was reporting a DATETIME, I inserted into the table a record containing the value 2011-04-22 08:30:00.000. I can see this value in the exported file 22/04/2011 08:30.

Can I ask you to try targeting a different DBMS? Because the reason of the issue could be the configuration of Excel, or it could not be able to parse the value due to the locale and it could result in the float representation.

Can you please try to reproduce the issue by manually populating the excel cell with the value returned by the database to check if the format is the cause?

apoorva-sriv commented 2 years ago

The data in the database is of the form 2022-04-29 01:09:30. When I copied the value into Excel, it formats it correctly, but, when it's exported from MemPOI as an XSSFWorkbook, it becomes 44680.06285 (Excel "General" format). I need to manually convert it to Excel Date format. I can't switch the DBMS unfortunately.

firegloves commented 2 years ago

@apoorva-sriv I tried several ways but I can't reproduce the issue. I'm sorry but in this case I can't help you