How to format &D in footer of an excel file using Aspose Cells for Java API

Aspose.Cells Product Family
1

Hi

How to format &D in footer of an excel using using Aspose Cells for Java API.

Right now my code is like this:

String wtxt = “&KFF0000&8”+footerText+“&D”+footerText1;

//to set the footer in the center
pageSetup.setFooter(1,wtxt );

Date appears in dd-MM-yyyy format.

I wanted in MM/dd/yyyy , is there any way to do it?

Thanks
Sabarish

2

@sabkan
Aspose.Cells follows the specifications and rules of MS Excel. Date formatting cannot be implemented in the Header/Footer section. If you still have any questions, please manually set the date for the Header/Footer in Excel and format it. If you can complete this operation, please provide us with a sample file, and we will check it soon.

Additionally, as this date format is fixed. It is related to the region, and you can obtain different header/footer date formats by setting different regions. Please check the attachment. HeaderDate.zip (62.0 KB)

The sample code as follows:

Workbook wb = new Workbook(filePath + "Sample.xlsx");
wb.getSettings().setRegion(CountryCode.USA);
wb.save(filePath + "out_java_usa.pdf");

wb.getSettings().setRegion(CountryCode.CHINA);
wb.save(filePath + "out__java_china.pdf");

Hope helps a bit.

3

Hi
Thanks. Tried using this code snippet
wb.getSettings().setRegion(CountryCode.USA);

but still the date appears in dd-MM-yyyy format.

4

@sabkan,

Do you convert to PDF or Excel (e.g., XLSX) format? Could you please provide the input Excel file, output file, and sample code that you are using? We will check it soon.

5

I dont convert to PDF. it is saved in xlsx format ,if input file is xlsx format.
Uploading the zip file containing the code snippet used,input excel file and output excel file.

zip for excel date investigation.zip (15.8 KB)

Thanks

6

@sabkan,

Thanks for the sample files.

This is the expected behavior. Please note that when you render to the Excel (e.g., XLSX) file format and open the output Excel file in MS Excel manually, it will override/use the region/locale of the system (OS) where you are opening the file, and you cannot change this behavior. For example, on a US locale/region, the Date header will be displayed in “MM/dd/yyyy” format in Excel. For other systems with different regions/locales, the Date header will be shown differently according to their locale settings.

When you render to PDF or an image, it will take into account the workbook.getSettings().setRegion(…); and you will get the header format irrespective of the region/locale settings of the OS where you are opening the PDF/image.

7

Thanks for the detailed explanation.

Got it.

8

@sabkan,

You are welcome.