Hi, Below is the excel to pdf draft code. And I have enclosed the excel and generated pdf for reference. My concerns are,
I want to add html fragment which consists of an image, report name and report date only at the starting of the first page of the pdf document. Please suggest sample code to achieve this functionality.
If you observe the generated PDF, The second column data is not completely shown. Also, how we can show if any column contains several lines of data.
String excelPreference = outputSettings.getExcelPreference();
String excelOutputFile = FilenameUtils
.removeExtension(outputGeneratorContext.getOutputFile().getAbsolutePath());
if (null != excelPreference && excelPreference.equalsIgnoreCase("excel-2003")) {
excelOutputFile += ".xls";
} else {
excelOutputFile += ".xlsx";
}
int pdfPageOrientation = PageOrientationType.PORTRAIT;
if (pageWidth > 597.6) {
pdfPageOrientation = PageOrientationType.LANDSCAPE;
}
// set aspose license
// pdfOutputGeneratorHelper.setAsposePdfLicense();
setAsposeExcelLicense();
Workbook excelWb = new Workbook(excelOutputFile);
for (int i = 0; i < excelWb.getWorksheets().getCount(); i++) {
Worksheet sheet = excelWb.getWorksheets().get(i);
sheet.getPageSetup().setOrientation(pdfPageOrientation);
sheet.getPageSetup().setFitToPagesWide(i);
sheet.getPageSetup().setFitToPagesTall(0);
}
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
if(pdfPageOrientation == PageOrientationType.LANDSCAPE)
pdfSaveOptions.setAllColumnsInOnePagePerSheet(true);
// pdfSaveOptions.setOnePagePerSheet(true);
// Save the PDF file
excelWb.save(outputGeneratorContext.getOutputFile().getAbsolutePath(), pdfSaveOptions);
You can insert a row in the first worksheet to place data, and then use the Cell.setHtmlString and Cell.setEmbeddedImage methods to set cell values or embed images.
If you want to display the hidden data in a column, you need to call Worksheet.autoFitColumns() and Worksheet.autoFitRows() to automatically adjust the rows and columns.
Please refer to the attachment. result.zip (159.2 KB)
The sample code as follows:
int pdfPageOrientation = PageOrientationType.LANDSCAPE;
Workbook excelWb = new Workbook(filePath + "17081.xlsx");
//Set embeded iamge and html string value
Cells cells =excelWb.getWorksheets().get(0).getCells();
cells.insertRow(0);
Cell a1 = cells.get("A1");
byte[] imageBytes = imageToByteArray(filePath + "favicon-32x32.png");
a1.setEmbeddedImage(imageBytes);
Cell b1 = cells.get("B1");
b1.setHtmlString("<b>report name<b>");
Cell c1 = cells.get("C1");
c1.setHtmlString("<i>report date<i>");
excelWb.save(filePath + "out_java.xlsx");
for (int i = 0; i < excelWb.getWorksheets().getCount(); i++) {
Worksheet sheet = excelWb.getWorksheets().get(i);
sheet.getPageSetup().setOrientation(pdfPageOrientation);
// autofit columns and rows
sheet.autoFitColumns();
sheet.autoFitRows();
sheet.getPageSetup().setFitToPagesWide(i);
sheet.getPageSetup().setFitToPagesTall(0);
}
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
if(pdfPageOrientation == PageOrientationType.LANDSCAPE)
pdfSaveOptions.setAllColumnsInOnePagePerSheet(true);
// pdfSaveOptions.setOnePagePerSheet(true);
// Save the PDF file
excelWb.save(filePath + "out_java.pdf", pdfSaveOptions);
public static byte[] imageToByteArray(String imagePath) {
File file = new File(imagePath);
ByteArrayOutputStream byteArrayOutputStream = null;
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(file);
byteArrayOutputStream = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int bytesRead;
while ((bytesRead = fileInputStream.read(buffer)) != -1) {
byteArrayOutputStream.write(buffer, 0, bytesRead);
}
return byteArrayOutputStream.toByteArray();
} catch (IOException e) {
e.printStackTrace();
return null;
} finally {
try {
if (fileInputStream != null) {
fileInputStream.close();
}
if (byteArrayOutputStream != null) {
byteArrayOutputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
You mentioned that you need to add an HTML fragment consisting of an image, report name, and report date at the start of the first page of the PDF document. Also, you want the second column data to be completely shown in the generated PDF, without manipulating the Excel file. It is not possible to accomplish this without manipulating the Excel spreadsheet. Could you manually do it in MS Excel without updating the spreadsheet? No, you can’t. Please try the suggested code segment to accomplish your task.
@amjad.sahi yes you are correct. Will try with the suggested code segment.
And as suggested I have set the sheet autoFitColumns(); and autoFitRows(); after that The generated PDF is looking as attached. Is there any way to wrap the column, if the column data exceeds after certain number of chars let’s say 100 or 150?
@koteswaragunda
You can use the following sample code to set the auto wrap style on the column. Please refer to the attachment. out_java.pdf (149.1 KB)
The sample code as follows:
int pdfPageOrientation = PageOrientationType.LANDSCAPE;
Workbook excelWb = new Workbook(filePath + "17081.xlsx");
Cells cells =excelWb.getWorksheets().get(0).getCells();
cells.insertRow(0);
Cell a1 = cells.get("A1");
byte[] imageBytes = imageToByteArray(filePath + "favicon-32x32.png");
a1.setEmbeddedImage(imageBytes);
Cell b1 = cells.get("B1");
b1.setHtmlString("<b>report name<b>");
Cell c1 = cells.get("C1");
c1.setHtmlString("<i>report date<i>");
excelWb.save(filePath + "out_java.xlsx");
Style columnStyle = excelWb.createStyle();
columnStyle.setTextWrapped(true);
StyleFlag flag = new StyleFlag();
flag.setAll(true);
for (int i = 0; i < excelWb.getWorksheets().getCount(); i++) {
Worksheet sheet = excelWb.getWorksheets().get(i);
sheet.getPageSetup().setOrientation(pdfPageOrientation);
//get the second column
Column col = sheet.getCells().getColumns().get(1);
col.applyStyle(columnStyle, flag);
// autofit columns and rows
//The column is set to wrap automatically, so the column width is not automatically adjusted.
//sheet.autoFitColumns();
sheet.autoFitRows();
sheet.getPageSetup().setFitToPagesWide(i);
sheet.getPageSetup().setFitToPagesTall(0);
}
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
if(pdfPageOrientation == PageOrientationType.LANDSCAPE)
pdfSaveOptions.setAllColumnsInOnePagePerSheet(true);
// pdfSaveOptions.setOnePagePerSheet(true);
// Save the PDF file
excelWb.save(filePath + "out_java.pdf", pdfSaveOptions);
public static byte[] imageToByteArray(String imagePath) {
File file = new File(imagePath);
ByteArrayOutputStream byteArrayOutputStream = null;
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(file);
byteArrayOutputStream = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int bytesRead;
while ((bytesRead = fileInputStream.read(buffer)) != -1) {
byteArrayOutputStream.write(buffer, 0, bytesRead);
}
return byteArrayOutputStream.toByteArray();
} catch (IOException e) {
e.printStackTrace();
return null;
} finally {
try {
if (fileInputStream != null) {
fileInputStream.close();
}
if (byteArrayOutputStream != null) {
byteArrayOutputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
Hi @John.He I tried to wrap the text but its not working. I followed the code as you mentioned. More over it overridden the column formats. Please find the input and output and following sample code.
public void generateOutputFile(OutputGeneratorContext outputGeneratorContext) {
ReportOutputGeneratorSettings outputSettings = outputGeneratorContext.getReportOutputGeneratorSettings();
ReportProcessHeader submitHeader = outputGeneratorContext.getProcessHeader();
MLMessageMap mlMessageMap = mlMessageFactory.getMessageMap(outputSettings.getLocaleCode());
String dataSheetName = mlMessageMap.getReportDownloadMlValue(ReportOutputConstants.DATA_L);
dataSheetName = StringUtils.hasText(dataSheetName) ? dataSheetName : "Data";
String filtersSheetName = mlMessageMap.getReportDownloadMlValue(ReportOutputConstants.RPT_FILTERS_L);
filtersSheetName = StringUtils.hasText(filtersSheetName) ? filtersSheetName : "Report Filters";
String excelPreference = outputSettings.getExcelPreference();
String excelOutputFile = FilenameUtils
.removeExtension(outputGeneratorContext.getOutputFile().getAbsolutePath());
String excelFileExtension;
if (null != excelPreference && excelPreference.equalsIgnoreCase("excel-2003")) {
excelFileExtension = ".xls";
} else {
excelFileExtension = ".xlsx";
}
// Get the paths to the source and destination files
Path sourceExcelPath = Paths.get(excelOutputFile + excelFileExtension);
Path destExcelPath = Paths.get(excelOutputFile + "-pdf" + excelFileExtension);
// Copy the file
try {
Files.copy(sourceExcelPath, destExcelPath);
splashLogger
.info("created copy of excel file. This is a temp file used for pdf conversion, later deleted.");
} catch (IOException e) {
throw new SplashBIReportOutputException("error while creating copy of excel file.", e);
}
String localeCode = outputSettings.getLocaleCode();
boolean chineseLocale = false;
if (null != localeCode && localeCode.equals("zh_CN")) {
chineseLocale = true;
}
try {
String title = getParsedText(submitHeader.getTitle());
String requestedDate = outputSettings.getActualStartDate();
// Add an image to the header
Image image = new Image();
image.setImageStream(Thread.currentThread().getContextClassLoader()
.getResourceAsStream("images/".concat(PdfOutputConstants.SPLASH_ICON_IMAGE_FILE_NAME)));
image.setFixWidth(10); // Set the image width
image.setFixHeight(10); // Set the image height
Map<String, String> fontFamilyMap = loadFonts();
String pdfFontFileName = getPdfFont(outputSettings.getPdfFont(), chineseLocale, fontFamilyMap);
Font font = FontRepository.openFont(Thread.currentThread().getContextClassLoader()
.getResourceAsStream("fonts/".concat(pdfFontFileName)), FontTypes.TTF);
int columnHeaderFontSize = 6;
double fontWidth = font.measureString("A", columnHeaderFontSize);
Double pageWidth = getPageWidth(submitHeader.getColumnProperties(), title, requestedDate, fontWidth);
// Set the page size as A4 (11.7 x 8.3 in) and in Aspose.Pdf, 1 inch = 72 points
// So A4 dimensions in points will be (842.4, 597.6)
int pdfPageOrientation = PageOrientationType.PORTRAIT;
if (pageWidth > 597.6) {
pdfPageOrientation = PageOrientationType.LANDSCAPE;
}
// set aspose license
setAsposeExcelLicense();
Workbook excelWb = new Workbook(destExcelPath.toFile().getAbsolutePath());
if (Boolean.TRUE.equals(submitHeader.getHasTotals())) {
// except totals sheet, delete all others
deleteDataSheets(excelWb, dataSheetName, filtersSheetName, "Pivot");
} else {
// except data sheet, delete all others
deleteTotalsSheets(excelWb, dataSheetName);
}
Style columnStyle = excelWb.createStyle();
columnStyle.setTextWrapped(true);
StyleFlag flag = new StyleFlag();
flag.setAll(true);
for (int i = 0; i < excelWb.getWorksheets().getCount(); i++) {
Worksheet sheet = excelWb.getWorksheets().get(i);
sheet.getPageSetup().setOrientation(pdfPageOrientation);
for (int colCount = 0; colCount < sheet.getCells().getColumns().getCount(); colCount++) {
Column col = sheet.getCells().getColumns().get(colCount);
//col.getStyle().setTextWrapped(true);
col.applyStyle(columnStyle, flag);
}
sheet.autoFitColumns();
sheet.autoFitRows();
sheet.getPageSetup().setFitToPagesWide(i);
sheet.getPageSetup().setFitToPagesTall(0);
}
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
if (pdfPageOrientation == PageOrientationType.LANDSCAPE)
pdfSaveOptions.setAllColumnsInOnePagePerSheet(true);
// Save the PDF file
excelWb.save(outputGeneratorContext.getOutputFile().getAbsolutePath(), pdfSaveOptions);
splashLogger.info("Pdf output file generated successfully.");
/*Document pdfDocument = new Document(outputGeneratorContext.getOutputFile().getAbsolutePath());
Page page = pdfDocument.getPages().get_Item(1);
page.getParagraphs().add(image);
pdfDocument.save();*/
} catch (Exception e) {
throw new SplashBIReportOutputException("Error while conversion of excel to pdf.", e);
}
try {
Files.deleteIfExists(Paths.get(destExcelPath.toFile().getAbsolutePath()));
} catch (IOException e) {
splashLogger.error("Error while deleting excel copy file.", e);
}
}
@koteswaragunda
Please comment out the following line of code. When you set the column style to auto wrap, there is no need to automatically adjust the columns.
//The column is set to wrap automatically, so the column width is not automatically adjusted.
//sheet.autoFitColumns();
sheet.autoFitRows();
Yes @John.He it worked. But observed that excel cell formats overridden. Example no borders and header background colors etc… not persisted. Please check the excel file attached in the earlier post.
@koteswaragunda
You can use the “setXXX” methods of StyleFlag to control various style attributes. If you only want to apply the automatic line wrap style, please use the following code:
StyleFlag flag = new StyleFlag();
//This line of code will apply all style settings to the columns.
//flag.setAll(true);
flag.setWrapText(true);
@koteswaragunda
Embedding images into cells is a new feature of Excel. This feature was not available in the old version of Excel. The SetEmbeddedImage method has only been supported since version v24.3. If this method is needed, an upgraded version is required. Of course, you can also add images to specific positions, but the images float on the cells instead of being embedded within them.
Please refer to the following document on how to manage images.
Hi @John.He
Able to embed image in excel and it successfully converted into pdf. But one challenging issue facing is, GAP between image logo and reportname. Which is varying based on the column width. I have enclosed the both images. and following is the code snippet for the same. I am not sure if this is achieved in excel. So I am thinking, first convert excel to pdf, later add the image logo and report name in the generated pdf at the top of the first page. Can you please suggest the approach and sample code please.
I believe the gap (between image logo and reportname) is caused by the line of code above, so you should not use it. It’s clear that when you set the width of the first column, the gap will be visible.
Please note Aspose.Cells is an MS Excel spreadsheet manipulation library/API, so you cannot modify an existing PDF file by Aspose.Cells for Java. I think you may use Aspose.PDF for Java library to manipulate PDF and insert image at the top (once it is generated by Aspose.Cells for Java). See the document on how to add image to existing PDF document for your reference. Also, see the document on how to add text to PDF document for your reference.
Hi @amjad.sahi after the pdf is converted, i used aspose pdf library to add image, report name and date but the content getting overlapped with the existing content. Please find the enclosed generated pdf file. and below is the sample code i am using for adding the same. Please help in resolving the content overloading. Also there is a space between image cell and title cell. how i can reduce. please suggest.
setAsposePdfLicense();
Document pdfDocument = new Document(pdfFilePath);
Page page = pdfDocument.getPages().get_Item(1);
Table table = new Table();
table.setColumnAdjustment(ColumnAdjustment.AutoFitToWindow);
// Add an image to the header
Image image = new Image();
image.setImageStream(Thread.currentThread().getContextClassLoader()
.getResourceAsStream(“images/”.concat(PdfOutputConstants.SPLASH_ICON_IMAGE_FILE_NAME)));
image.setFixWidth(20); // Set the image width
image.setFixHeight(20); // Set the image height
Row imageRow = table.getRows().add();
Row hrRow = table.getRows().add();
Row executedOn = table.getRows().add();
Cell imageCell = imageRow.getCells().add();
imageCell.setRowSpan(3);
imageCell.setAlignment(HorizontalAlignment.Left);
imageCell.getParagraphs().add(image);
imageCell.setWidth(50);