Convert data in cells with given format

Aspose.Cells Product Family
1

Hi,


I have a use case where in excel we have data which is of data type date, but as excel does not support that format so it will store it as a Text. After applying a custom format also I am not able to save them as date in excel files.

So do aspose have an utility using which I can convert Text values in cell to date value using a custom format? Like we have in java,

e.g.

SimpleDateFormat formatter = new SimpleDateFormat(“dd-MMM-yyyy”);
String dateInString = “7-Jun-2013”;

try {

Date date = formatter.parse(dateInString);
System.out.println(date);
System.out.println(formatter.format(date));

} catch (ParseException e) {
e.printStackTrace();
}
2

Hi Kapil,


Thank you for contact Aspose support.

You may format the cell, row, column or range containing the text data as dates using the Style.setNumber (for predefined formats) or Style.setCustom (for custom formats). Once you have applied the formatting as desired, you can fetch back the formatted value using the Cell.getDisplayStringValue method. Please check the detailed article on setting the number & date formats.

Java

Workbook book = new Workbook(dir + “book1.xlsx”);
Style style = book.createStyle();
style.setCustom(“[$-409]d-mmm-yyyy”);
StyleFlag flag = new StyleFlag();
flag.setNumberFormat(true);

Cell A1 = book.getWorksheets().get(0).getCells().get(“A1”);
A1.setStyle(style);
System.out.println(A1.getDisplayStringValue());

3

Hi Babar,


Thanks for the reply. I went through your answers but I think you have misunderstood my query.
Let me explain it in details,

I have an excel file lets say “EmployeeDetails.xlsx”, in that we have one cell, let say B2, in which we have a value “16-Mar-2010 13:27:21”.

Now this is stored as text in MS Excel because there is no format defined in MS Excel to satisfy this criteria, so we will manually set format on B2 cell as “dd-mmm-yyyy hh:mm:ss”, but after applying this format also, still MS Excel does not convert that cell to Date.

Now, when I fetch B2 cell using Aspose.cells, it returns a text value though the format is set as Date on that cell, this may be because excel it self stored that value as text.
Now the question is
1) Can I create a Date object using a cell format and cell value which is set on B2 cell?
2) In excel we have created a format “dd-mmm-yyyy hh:mm:ss” but when we fetch format of cell in aspose it returns me “dd/mmm/yyyy\ hh:mm:ss”, can we get exact format which is set in excel files?


Thanks & Regards,
Kapil Jaiswal
4

Hi Kapil,

Thanks for your further explanation and considering Aspose.Cells.

We were able to understand your problem partially not completely. Could you please provide us a simple excel file which you can create yourself manually using Microsoft Excel and post here for our investigation. You can also add comments inside it.

Are you storing dates in Excel file in the Text format as string and trying to convert those text data into real date objects in your code?

Please also see the code below that converts A1 string value into date format. I have also attached the excel file used in this code for a reference.

Java


Workbook book = new Workbook(“book1.xlsx”);
Cell A1 = book.getWorksheets().get(0).getCells().get(“A1”);
Style style = A1.getStyle();
String format = style.getCustom();
format = format.toUpperCase();
format = format.replace("\", “”);
System.out.println(format);

SimpleDateFormat formatter = new SimpleDateFormat(format);
String dateInString = A1.getStringValue();
System.out.println(dateInString);

Date date = formatter.parse(dateInString);
System.out.println(date);

Console Output:
D-MMM-YYYY
21-Nov-2014
Sun Dec 29 00:00:00 UZT 2013

5

Hi Shakeel,


Thanks for a quick response.

Are you storing dates in Excel file in the Text format as string and trying to convert those text data into real date objects in your code?

The answer is YES, we have some sheets which are coming as bank statement, these sheets are auto generated, and every thing is stored as Text.

I am trying to read those excel sheets and want to perform some calculation based on data.

Please find the sample file for this. Please check K2 and K3 cells of Sheet1.

One more thing in your example ,
String format = style.getCustom();
format = format.toUpperCase();
format = format.replace("\", “”);
System.out.println(format);

You are getting custom format with “-” in it. But at my end I always get "/"

Thanks & Regards,
Kapil Jaiswal

6

Hi Kapil,


We have checked you provided spreadsheet and it seems that the problem cause is that the formatting is not being stored properly in the spreadsheet. Please check the snapshot for your reference. You will notice that the custom format has been specified as dd/mmm/yyyy but output is shown as dd-mmm-yyyy. Anyway, one solution to this problem could be to use Aspose.Cells APIs to change the format of the column rather than changing it manually with Excel application. Please check the following piece of code to assign a custom format to whole column K. After applying the format, please try the solution provided here.

Java

Workbook book = new Workbook(dir + “EmployeeDetails.xlsx”);
Style style = book.createStyle();
style.setCustom(“dd-mmm-yyyy hh:mm:ss”);
StyleFlag flag = new StyleFlag();
flag.setNumberFormat(true);
Column col = book.getWorksheets().get(0).getCells().getColumns().get(CellsHelper.columnNameToIndex(“K”));
col.applyStyle(style, flag);

7

Hi Babar,


I checked the snapshot you have attached. But it looks wrong at your end. I am attaching an format cells image at my end which is having format as “dd-mmm-yyyy hh:mm:ss”, Why at your end it looks different?

Thanks & Regards,
Kapil Jaiswal
8

Hi again,


We have noticed there is an empty space before every date value in column K, that is perhaps the cause for the values not being treated as valid date in Excel. Please check the following piece of code that resets the cells values while converting them to dates literals. Once the values are treated as dates, you can retrieve them as com.aspose.cells.DateTime objects, if required, convert them to java.util.Date objects as well.

Java
Workbook book = new Workbook(dir + “EmployeeDetails.xlsx”);
Worksheet sheet = book.getWorksheets().get(0);
Cells cells = sheet.getCells();
for (int i = 1; i <= cells.getMaxDataRow(); i++)
{
Cell cell = cells.get(i, CellsHelper.columnNameToIndex(“K”));
cell.putValue(cell.getStringValue().trim(), true);
DateTime dTime = (DateTime) cell.getValue();
Date date = dTime.toDate();
System.out.println(date);
}
9

Hi Team,

Thanks for all the responses.

I am able to solve my issue in a different way.

I am using steps mentioned in the below link to format the sheet properly.

Please refer this link may be other users also follow these steps.

http://www.theexceladdict.com/_t/t040519.htm

Thanks & Regards,

Kapil Jaiswal

10

Hi Kapil,


Thank you for sharing the tip, and it is good to know that you are able to resolve the problem. By the way, I have tested the solution on your provided spreadsheet and have noticed that the procedure splits the time part to a separate column. I will look further to share my feedback.

Please feel free to contact us back in case you need our further assistance with Aspose APIs.
11

Hi Aspose Team,

I have some excel files which are created in US time zone. In these files the date columns have a date-time format of US Time Zone i.e. the format applied on the cell is “MM/dd/yyyy HH:mm AM/PM”. Now this date time format is not available in Indian Time zone. So when I load these excels in java using aspose.cells in India Time zone then for the date-time column the API com.aspose.cells.Cell.getType not giving type as CellValueType.IS_DATE_TIME.
Please check the attached image for your reference. (Verified on version aspose-cells-17.7)
image.png (84.2 KB)

12

@kapil.jaiswal.1eq

Please share your Excel file so that we could experiment with your file and help you in dealing with this issue. Your sample code will also be helpful. Besides, let us know your expected date.

Could you share two Pdfs (or Excel files). One should have the actual date and other should have expected dates? Thanks for your cooperation in this regard and have a good day.