I have Excel files with dates that I need to convert to PDF. When I run the convert, the date format ends up matching the local of the server where Aspose is running. What I would like to do is force all date cells to use yyyy-mm-dd. Is there a way to do that?
Thanks.
To achieve your desired task, you got to set your desired Date format (“yyyy-mm-dd”) as custom formatting to your specified whole column/columns or range of cells first and then render to PDF file format. See the document with example code on how to set custom formatting for your reference.
Could you please provide sample code (JAVA)?
I am looking to iterate over all the cells in a Workbook and check if they are Date, apply a custom date format (e.g. yyyy-mm-dd), and if Date/Time, apply a custom date-time format (yyyy-mm-dd h:m:s AM/PM).
Currently we have this:
public class SheetConversionTask extends ConversionTask {
public SheetConversionTask(int LENGTH, InputStream SOURCE) {
super(LENGTH, SOURCE);
}
@Override
void doConversion() throws IOException, Exception {
Workbook book = new Workbook(sourceStream);
for (int i = 0; i < book.getWorksheets().getCount(); i++) {
Worksheet sheet = book.getWorksheets().get(i);
sheet.getPageSetup().setOrientation(PageOrientationType.LANDSCAPE);
sheet.getPageSetup().setZoom(75);
}
book.save(out, com.aspose.cells.SaveFormat.PDF);
}
}@trevors,
You may please share a sample Excel file for our reference. We will analyze it and share relevant code accordingly.
dates_test_various.xlsx.zip (9.1 KB)
@ahsaniqbalsidiqui here is an excel file we are using for testing. The problem we are noticing is that Aspose running our US-locale server changes the date time cells (that use * dates, i.e. change based on locale of machine opening the file), and we’d like to use (force) ISO-8601 format of yyyy-mm-dd (and similarly for date-times).
@trevors,
You may please give a try to the following sample code and share your feedback:
Workbook workbook = new Workbook("dates_test_various.xlsx");
for(Object item: workbook.getWorksheets().get(0).getCells())
{
Cell cell = (Cell)item;
Style style = cell.getStyle();
if (cell.getValue() != null)
{
if(cell.getNumberCategoryType() == NumberCategoryType.DATE)
{
style.setCustom("yyyy-mm-dd");
cell.setStyle(style);
}
else if(cell.getNumberCategoryType() == NumberCategoryType.TIME)
{
style.setCustom("yyyy-mm-dd hh:mm");
cell.setStyle(style);
}
}
}
workbook.save("dates_test_various_java.pdf", SaveFormat.PDF);
Good to know that the suggested code segment works for your needs. In the event of further queries or issue, feel free to write us back.
@ahsaniqbalsidiqui I’m now wondering if it is possible to detect if the cell is using regional date format. E.g. “*2012-03-14”?
We think it might be nice to only format the cell if it is regionally formatted, and otherwise maintain the format of the cell as it is in Excel.
@trevors,
Well, it seems that it will take similar time and processing to check and then set custom formatting for the desired cells only. I could not find any built in feature to achieve this however you may try the sample codes shared here:
@ahsaniqbalsidiqui
I wasn’t trying to prevent going cell by cell, but rather to only set the cell style if the date format uses regional formatting. This is an excel feature, where dates with format starting with an asterisk (*) will change depending on the locale of the machine viewing the file.
I think there isn’t a method for this, so I will give up on that, and always override the format as in the sample code you provided.
Thank you.
@trevors,
You are welcome and feel free to write us back if you have any other query related to Aspose.Cells.
Please note, MS Excel styles are of two types
As you know you use Style.getNumber() attribute for Built-in Styles and Style.getCustom() for Custom Styles. Now you can get the style object of a cell like this
Style st = cell.getStyle();
Now you may first check Style.getNumber() property like this
System.out.println(st.getNumber());
If it prints some number greater than 0 (where as if it is “0” that means custom formatting is already applied), then it means, Cell has a Built-in style or regional formatting which will be changed accordingly for different locales. You may use if condition in code for evaluation. So, here, you may set your desired custom formatting so it should not be changed and its formatting remains constant as per your needs.
Hope, this helps.