Cell format with date string does not apply with smart marker data binding

Aspose.Cells Product Family
1

I’m loading Json data for data binding using Smart Marker and I noticed that template Excel file with a cell formatted with specific date time style does not get properly applied. Is there something I’m missing here?

Note: if I open up the exported Excel file and press ‘Enter’ on the cell, then the style gets applied.

Input template file:
Screenshot 2025-09-25 at 3.21.00 PM.jpg (149.8 KB)

Output template file:
Screenshot 2025-09-25 at 3.24.23 PM.png (10.7 KB)

JSON:

{
  "date": "2024-01-20"
}

Script:


    public byte[] processTemplate(byte[] templateBytes, String jsonData) throws Exception {
        logger.info("Starting Excel template processing. Template size: {} bytes", templateBytes.length);
        
        // Create workbook from template
        Workbook workbook = new Workbook(new ByteArrayInputStream(templateBytes));
        logger.debug("Workbook created successfully from template");

        // // Create smart marker processor
        WorkbookDesigner designer = new WorkbookDesigner();
        designer.setWorkbook(workbook);
        
        // // Set data source using the correct method signature
        logger.info("Setting data source from JSON: {}", jsonData);
        designer.setJsonDataSource("data", jsonData);

        // // Process the template with smart markers
        designer.process();

        workbook.calculateFormula();

        // Save to byte array
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.save(outputStream, SaveFormat.XLSX);
        byte[] result = outputStream.toByteArray();
        
        logger.info("Excel template processing completed. Output size: {} bytes", result.length);
        return result;
    }
2

@yoohee

It appears that the date format from your JSON data is not being applied correctly in the Excel template. When using Smart Markers, ensure that the cell in the Excel template is formatted to accept the date format you are providing.

Here are a few steps you can take to troubleshoot and resolve the issue:

  1. Check Cell Formatting: Ensure that the cell in your Excel template is formatted as a date. You can do this by right-clicking the cell, selecting “Format Cells,” and choosing an appropriate date format.

  2. Date Format in JSON: Verify that the date string in your JSON is in a format that Excel recognizes. The format “YYYY-MM-DD” is generally compatible, but you may also want to try formats like “MM/DD/YYYY” or “DD/MM/YYYY” based on your regional settings.

  3. Use Smart Markers Correctly: Confirm that you are using the correct Smart Marker syntax in your Excel template. For instance, if you are binding to a date, you might need to use a specific marker format such as =date or similar.

  4. Debugging: Implement logging to check what data is being processed. You can log the data after setting the JSON data source to ensure it is being read correctly.

Here’s a small adjustment to your code to log the data source:

logger.info("Data source set: {}", designer.getDataSource());

If the issue persists, consult the Aspose.Cells documentation for Smart Markers and date formatting for further guidance.

3

@yoohee,

Thanks for the screenshots, JSON data, details and code snippet.

DateTime values are rendered as string data when Smart Markers are processed whose data source is JSON data. Please change your Smart Maker (in the template Excel file), i.e.,

&=data.date

to:

&=data.date(numeric)

it will work fine. Please use the “numeric” parameter to convert string data into numeric format, as this is essential for applying Date style/formatting. I tested your scenario using the Smart Marker “&=data.date(numeric)” with your JSON data, and it worked perfectly as expected. The DateTime formatting was applied correctly in the output Excel file.

Let us know if you still find any issue.

4

I can confirm that this fixes the issue. Thank you!

5

@yoohee
Thank you for your feedback. You are welcome. I’m glad you solved the issue using “&=data.date(numeric)”. If you have any questions, please feel free to contact us at any time.