How do i apply custom date formatting from excel in java?

Question

I have an 2007 excel file called Test10. The first row has 10 headers, test1 test2 etc to test10. The second row, column 3 has a date, 10-10-2020 on the cell with the 10-10-2020 date there is a custom date format -> dd-mm-yyyy

The date in the cell is written as 2020-10-10.

I have the format from the cell in Java, however when I get it it's dd/-mm/-yyyy;@ and the date is 2020-10-10.

I need to somehow apply that format to the date at hand.

I need something more flexible, is there a class that does that format? All I have managed to find so far is how to apply formatting to the excel in java when creating the excel, but not the other way around, when getting it from the Excel file.

I can manually serialize the format and remove the special characters that are not needed but that is a workaround.

I need a class that will apply the strange looking format to the date, in Java. Is there anything like that?

Also the example I provided above is a very simple example.

We're using a custom handler, the date comes as a double and we currently use DateUtil.getJavaDate to get the date as a string.

I have not created the handler so it's quite hard to understand the whole of it, but somewhere in the handler, the custom formatting for the cell is extracted from the excel and put into a variable, at the point where the DateUtil.getJavaDate is called I have access to the formatting variable.

Previously it was using SimpleDateFormat(CONSTANT_FORMAT).format(DateUtil.getJavaDate(d)) to format it, but I need to apply the custom format. Is there a library that parses xlsx date formats. As the dd/-mm/-yyyy;@ format will not work with SimpleDateFormat.

Also I cannot serialize the format hardcoded as if I replace mm with MM how do I know if the user wanted minutes or months?

Are you using Apache POI or some other means for reading the Excel workbook? How exactly are you reading the date format and the date value? — Ole V.V.
@OleV.V.we're using a custom handler, the date comes as a double and we currently use DateUtil.getJavaDate to get the date as a string, I have not created the handler so it's quite hard to understand the whole of it, but somewhere in the handler, the custom formatting from the excel is extracted and put into a variable, at the point where the DateUtil.getJavaDate is called i have access to the formatting. Previously it was using SimpleDateFormat(CONSTANT_FORMAT).format(DateUtil.getJavaDate(d)) to format it, but i need to apply the custom format. Is there a library that parses xlsx date format — Sebastian Zdroana
(1) That’s good information. Please paste it into your question so we have everything in one place. (2) From what I have seen most use Apache POI - the Java API for Microsoft Documents for reading .xlsx files in Java. I have worked with it. the only issue I had was that the version I used didn’t support java.time, the modern Java date and time API. — Ole V.V.
@OleV.V. we also use Apache.POI, the issue is not extracting the data from the excel. I have the date and the format, the issue is applying the Excel format to the date, as the Excel format dd/-mm/-yyyy;@ is different from the java format, which should be dd-MM-yyyy, i can use .replaceAll to get rid of the / ; @ and to change the mm to MM, but i can't do that because i don't know if the user wanted mm for minutes or MM for months — Sebastian Zdroana

Sebastian Zdroana Sebastian Zdroana · Accepted Answer · 2021-04-13T06:54:26

DataFormatter from org.apache.poi handles the weird excel formats

How do i apply custom date formatting from excel in java?

1 Answers