Not able to parse date from MS Excel correctly
Developer is uploading an Excel file and parsing data from it. The MSOParseExcelFile activity is parsing the content, however dates present in Excel are converted into double when mapped to clipboard.
Steps to Reproduce
1. Upload Excel file with at least one date field.
2. Create a template to map the Excel value to clipboard.
3. Parse Excel by using the activity MSOParseExcelFile.
4. Observe that Date gets mapped as doubles in the clipboard.
An issue in the custom application code or rules: Dates are stored as integers in Excel (and Datetimes as decimals)
Here’s the explanation for the reported behavior:
This is expected behavior.
1. Use Apache POI (Excel interface) or java functions to convert content:
Note: Excel stores dates as the number of days (plus fractional days) since 1900 (and in some cases it can be from 1904). See Microsoft KB article 180162.
Here is a minimal working example how to convert an Excel date to a Java date in java:
Date javaDate= DateUtil.getJavaDate((double) 41275.00); System.out.println(new SimpleDateFormat("MM/dd/yyyy").format(javaDate));
2. Import the following packages:
> java.text.SimpleDateFormat > java.util.Date
0% found this useful