Date Formatting is not happening on the total column in aspose.cells

Aspose.Cells Product Family
1

Hi ,

I am trying to convert a string type column to a date column by using setConvertNumericOrDate(true)
and then applying the date format on top of it by using
layoutOptions.setDateFormat(‘DD-MMM-YYYY’);

Only some records and some columns are being changed to the given format

I am attaching the code which I am using for your reference

var aspose = aspose || {};
aspose.cells = require("aspose.cells");
const path = require('path');
const data = [
      {
          "Employee Id": "T510",
          "First Name": "Darwinboxcore76",
          "Last Name": "Employee76",
          "Gender": "Male",
          "Date Of Birth": "24-02-1976",
          "Date Of Joining": "03-10-2022",
          "Date Of Exit": " ",
          "Bank Account Number": " ",
          "Bank Ifsc Code": " "
      }
]

const workbook = aspose.cells.Workbook(aspose.cells.FileFormatType.XLSX);
workbook.getSettings().setRegion(aspose.cells.CountryCode['UNITED_KINGDOM']);
var worksheet = workbook.getWorksheets().get(0);
var layoutOptions = aspose.cells.JsonLayoutOptions()
layoutOptions.setArrayAsTable(true);
layoutOptions.setConvertNumericOrDate(true);
layoutOptions.setNumberFormat("0");
layoutOptions.setDateFormat('DD-MMM-YYYY');
aspose.cells.JsonUtility.importData(JSON.stringify(data), worksheet.getCells(), 0, 0, layoutOptions)
let range = worksheet.getCells().getMaxDisplayRange();
console.log(range.getRefersTo());
let filePath = path.normalize(`${process.cwd()}/result/sample9.xls`);
workbook.save(filePath);

As you can see from thw above data and code
I was using the setConvertNumericOrDate(true) to make the string type to date type and then adding the date format to it

if you see the output screenshot ( attached below ) , Date of Birth column is coming in expected for the FIRST RECORD but date formatting is not getting applied for the second record

The same goes to the other columns as well

Screenshot 2023-05-22 at 1.52.43 PM.png (43.5 KB)

Attaching the sample outputs for your reference .

SAMPLE OUTPUTS.zip (259.7 KB)

can you please help me on this ??

Thanks.

2

@Veerendra1234,

Thanks for the sample files and screenshot.

I tested using your sample code with Aspose.Cells for Node.js via Java and found it only renders/imports one record (data range is A1:I2). The date formatting is applied to Date of Birth and Date of Joining columns fine in the output file. Could you please provide complete test case (source code with complete JSON data without compilation errors) to reproduce the issue (as per your screenshot) on our end, we will check it soon.

3

@amjad.sahi

please check this below code

var aspose = aspose || {};
aspose.cells = require("aspose.cells");
const path = require('path');
const data = [
      {
          "Employee Id": "T510",
          "First Name": "Darwinboxcore76",
          "Last Name": "Employee76",
          "Gender": "Male",
          "Date Of Birth": "24-02-1976",
          "Date Of Joining": "03-10-2022",
          "Date Of Exit": " ",
          "Bank Account Number": " ",
          "Bank Ifsc Code": " "
      }
]

const workbook = aspose.cells.Workbook(aspose.cells.FileFormatType.XLSX);
workbook.getSettings().setRegion(aspose.cells.CountryCode['UNITED_KINGDOM']);
var worksheet = workbook.getWorksheets().get(0);
var layoutOptions = aspose.cells.JsonLayoutOptions()
layoutOptions.setArrayAsTable(true);
layoutOptions.setConvertNumericOrDate(true);
layoutOptions.setNumberFormat("0");
layoutOptions.setDateFormat('DD-MMM-YYYY');
aspose.cells.JsonUtility.importData(JSON.stringify(data), worksheet.getCells(), 0, 0, layoutOptions)
let range = worksheet.getCells().getMaxDisplayRange();
console.log(range.getRefersTo());
let filePath = path.normalize(`${process.cwd()}/result/sample10.xls`);

workbook.save(filePath);

Here as you can see there is one JSON record having
“Date Of Birth”: “24-02-1976”,
“Date Of Joining”: “03-10-2022”,

When I am applying setDateFormat(‘DD-MMM-YYYY’); , ideally these has to be changed to

“Date Of Birth”: “24-FEB-1976”,
“Date Of Joining”: “03-Oct-2022”,
which is not happening ( please refer below screenshot )

DATE OF BIRTH column is changed to DD_MON_YYYY , but “Date Of Joining” is showing as “03-10-2022” which is not expected , right ???

Screenshot 2023-05-22 at 2.33.38 PM.png (20.8 KB)

Attaching the screenshot of the file and also the sheet ZIP
sample10.xls.zip (2.0 KB)

please let me know , in case any other details are required

thanks

4

@Veerendra1234,

It works fine on my end. See the attached output XLS file which is generated on my end. Which version of Aspose.Cells for Node.js via Java you are using? Also, what is your environment (OS, regional settings/locale settings, node.js version, JDK version, etc.)?
sample10.zip (1.6 KB)

5

Hi ,
these are the version I am using

“aspose.cells”: “^23.5.0”,

“node”: “16.10.0”,

“npm”: “7.24.0”,

java version “1.8.0_351”
Java™ SE Runtime Environment (build 1.8.0_351-b10)
Java HotSpot™ 64-Bit Server VM (build 25.351-b10, mixed mode)

local OS details : Mac OS 13.3.1 (a) (22E772610a) , Apple M1 chip

attaching regional , locale settings
Screenshot 2023-05-22 at 2.51.55 PM.png (47.6 KB)

6

@Veerendra1234,

Thanks for providing version and environment details.

I am able to reproduce the issue as you mentioned. I found Date formatting is not implementing on all columns in Aspose.Cells. Please note, it works fine with Aspose.Cells for Node.js via Java v23.3 but it does not work with Aspose.Cells for Node.js via Java v23.5.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNODEJSJAVA-47

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

7

Hi ,
I need to use Aspose.Cells for Node.js via Java v23.5 , because I was told that leading zeros issue has been fixed in this version

refer this link please

8

@Veerendra1234,

Yes, sure, we will figure it out in the newer version soon. Please spare us little time to evaluate and to resolve the issue. Once we have an update on the issue, we will let you know here.

9

Hi ,
is there an approximate date when the new release/error fixing will be released ?
so that we can plan our things accordingly

10

@Veerendra1234,
The fix will be included in our upcoming release (Aspose.Cells v23.6) which is scheduled in the first half of June 2023. You will be notified once the next release is published.

11

The issues you have found earlier (filed as CELLSNODEJSJAVA-47) have been fixed in this update. This message was posted using Bugs notification tool by Nick.Liu