Then do the processing of inserting footer using Aspose Cells in this location using below code(which works well in standalone mode).
Workbook pworkbook = new Workbook(fileDir + File.separator + fname);
pworkbook.getSettings().setRegion(CountryCode.USA);
for (int i = 0; i < pworkbook.getWorksheets().getCount(); i++) {
// Get the first default sheet
Worksheet sheet = null;
try {
sheet = pworkbook.getWorksheets().get(i);
}
catch (Exception ex) {
// logger.error("watermarkTextExcelFile load sheet ex:" + ex.getMessage());
ex.printStackTrace();
}
if(sheet != null) {
PageSetup pageSetup = sheet.getPageSetup();
pageSetup.setFooterMargin(0);
//01/01/2024 to set font size to 8
//trying to include time also on 05/16/2024
String wtxt = "&KFF0000&8"+footerText+"&D"+ " " + "&T" + footerText1;
//to set the footer in the center
pageSetup.setFooter(1,wtxt );
//trying to do right aligned on 05/16/2024 since it is overlapping for PR001715.xls
// pageSetup.setFooter(2,wtxt );
}
else {
// no sheet
break;
}
} //end of for loop
pworkbook.save(fileDir+File.separator+fname);
After this when i try to open an xls file, it gives a error like
@sabkan
By opening the result file you provided through MS-Excel, we can observe error messages. Additionally, the code you provided contains unassigned variables. Would you like to provide sample file and executable test code? We will check it soon.
Actually we are running the code in another software called Agile PLM.There we download the excel file using the code snippets I had shared.
some unassigned variables are
String FooterText = “Once printed, this document is reference only. This document is valid for /on the date printed. Please reference Agile for the latest revision”;
footerText = FooterText.substring(0, 74);
footerText1 = FooterText.substring(74);
fileDir is for the file directory where the file is getting downloaded
fname is the file name
The sample files where the footer has to be inserted is:
@sabkan
By using the sample file you provided and testing the following code on the latest version v24.5, we can obtain the correct results. Please refer to the attachment. result.zip (61.7 KB)
The sample code as follows:
String FooterText = "Once printed, this document is reference only. This document is valid for /on the date printed. Please reference Agile for the latest revision";
String footerText = FooterText.substring(0, 74);
String footerText1 = FooterText.substring(74);
Workbook pworkbook = new Workbook(filePath + "PR002295.xlsx");
pworkbook.getSettings().setRegion(CountryCode.USA);
for (int i = 0; i < pworkbook.getWorksheets().getCount(); i++)
{
// Get the first default sheet
Worksheet sheet = null;
try {
sheet = pworkbook.getWorksheets().get(i);
}
catch (Exception ex) {
// logger.error("watermarkTextExcelFile load sheet ex:" + ex.getMessage());
ex.printStackTrace();
}
if(sheet != null) {
PageSetup pageSetup = sheet.getPageSetup();
pageSetup.setFooterMargin(0);
//01/01/2024 to set font size to 8
//trying to include time also on 05/16/2024
String wtxt = "&KFF0000&8"+footerText+"&D"+ " " + "&T" + footerText1;
//to set the footer in the center
pageSetup.setFooter(1,wtxt );
//trying to do right aligned on 05/16/2024 since it is overlapping for PR001715.xls
// pageSetup.setFooter(2,wtxt );
}
else {
// no sheet
break;
}
} //end of for loop
pworkbook.save(filePath + "PR002295_out.xlsx");
Additionally, you can first save the downloaded file locally and check if it can be opened normally in Excel. Perhaps the file you downloaded is missing data, causing the file to crash. You can add the following code to flush the cache when downloading the sample file.
@John.He
Tried using 24.5 jar and use the fos.flush() as you suggested.
Now the xlsx file is opening, but the xls is not opening with the same error file format not valid.
code snippet with flush
try {
for (int length = 0; (length = content.read(bytes)) > 0;){
try {
fos.write(bytes, 0, length);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}}
//flush suggested by Aspose team
fos.flush();
fos.close();
@sabkan,
It seems the issue is on your end in your code where you are reading or saving XLS workbook byte data and then writing to output file streams. Please debug your code and fix it accordingly on your end by yourselves. I tested using the following sample code with your “PR001715.xls” file and it works absolutely fine. The output XLS file is Ok and can be opened fine in MS Excel.
e.g., Sample code:
String filePath = "d:\\files\\";
String FooterText = "Once printed, this document is reference only. This document is valid for /on the date printed. Please reference Agile for the latest revision";
String footerText = FooterText.substring(0, 74);
String footerText1 = FooterText.substring(74);
Workbook pworkbook = new Workbook(filePath + "PR001715.xls");
pworkbook.getSettings().setRegion(CountryCode.USA);
for (int i = 0; i < pworkbook.getWorksheets().getCount(); i++)
{
// Get the first default sheet
Worksheet sheet = null;
try {
sheet = pworkbook.getWorksheets().get(i);
}
catch (Exception ex) {
// logger.error("watermarkTextExcelFile load sheet ex:" + ex.getMessage());
ex.printStackTrace();
}
if(sheet != null) {
PageSetup pageSetup = sheet.getPageSetup();
pageSetup.setFooterMargin(0);
//trying to include time also on 05/16/2024
String wtxt = "&KFF0000&8"+footerText+"&D"+ " " + "&T" + footerText1;
//to set the footer in the center
pageSetup.setFooter(1,wtxt );
//trying to do right aligned on 05/16/2024 since it is overlapping for PR001715.xls
// pageSetup.setFooter(2,wtxt );
}
else {
// no sheet
break;
}
} //end of for loop
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
pworkbook.save(outputStream, SaveFormat.EXCEL_97_TO_2003);
FileOutputStream fos = new FileOutputStream(filePath + "PR001715_out1.xls");
fos.write(outputStream.toByteArray());
Please find attached both input and output XLS files in the zipped archive for your reference. files1.zip (15.4 KB)
Please try the above code segment (adjust filePath accordingly in code) in a separate Java program/application and check the output XLS file which should be fine tuned and can be opened in MS Excel without error. Now evaluate your actual code and fix it by yourselves on your end.
if (fname.endsWith(“.xls”) || fname.endsWith(“.XLS”)) {
pworkbook.save(outputStream, SaveFormat.EXCEL_97_TO_2003);
} else {
pworkbook.save(outputStream, SaveFormat.XLSX);
}
but it gives some error for save.