I have an Excel file that has 5 columns having few merged cells, blank cells, dates, and other text information (a normal excel file).
I am reading this file using POI API in java. I am able to convert the file to pdf table using iText jar.
But, the whole format is not copied into the pdf. (e.g., merged cells come into one column, and other formatting or settings are all gone).
A simple pdf table is created.
How do i retain the same format as in excel? (I want exact copy of excel sheet in pdf)
Here is the code that I am using
//First we read the Excel file in binary format into FileInputStream
FileInputStream input_document = new FileInputStream(new File("K:\\DCIN_TER\\DCIN_EPU2\\CIRCUIT FROM BRANCH\\RAINBOW ORDERS\\" + SONo.trim() + "\\" + SONo.trim() + " - Checklist.xls"));
// Read workbook into HSSFWorkbook
HSSFWorkbook my_xls_workbook = new HSSFWorkbook(input_document);
// Read worksheet into HSSFSheet
HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);
// To iterate over the rows
Iterator<Row> rowIterator = my_worksheet.iterator();
//We will create output PDF document objects at this point
com.itextpdf.text.Document iText_xls_2_pdf = new com.itextpdf.text.Document();
PdfWriter.getInstance(iText_xls_2_pdf, new FileOutputStream("K:\\DCIN_TER\\DCIN_EPU2\\CIRCUIT FROM BRANCH\\RAINBOW ORDERS\\" + SONo.trim() + "\\" + SONo.trim() + " - Checklist.pdf"));
iText_xls_2_pdf.open();
//we have 5 columns in the Excel sheet, so we create a PDF table with 5 columns; Note: There are ways to make this dynamic in nature, if you want to.
PdfPTable my_table = new PdfPTable(5);
//We will use the object below to dynamically add new data to the table
PdfPCell table_cell;
//Loop through rows.
while(rowIterator.hasNext())
{
Row rowi = rowIterator.next();
Iterator<Cell> cellIterator = rowi.cellIterator();
while(cellIterator.hasNext())
{
Cell celli = cellIterator.next(); //Fetch CELL
switch(celli.getCellType())
{
//Identify CELL type you need to add more code here based on your requirement / transformations
case Cell.CELL_TYPE_STRING:
//Push the data from Excel to PDF Cell
table_cell = new PdfPCell(new Phrase(celli.getStringCellValue()));
//move the code below to suit to your needs
my_table.addCell(table_cell);
break;
case Cell.CELL_TYPE_NUMERIC:
//Push the data from Excel to PDF Cell
table_cell = new PdfPCell(new Phrase("" + celli.getNumericCellValue()));
//move the code below to suit to your needs
my_table.addCell(table_cell);
break;
}
//next line
}
}
//Finally add the table to PDF document
iText_xls_2_pdf.add(my_table);
iText_xls_2_pdf.close();
//we created our pdf file..
input_document.close(); //close xls
I have attached the excel file as an image
Convert All Sheets of Excel Files to PDF - Java The following steps convert the complete workbook (all sheets) to PDF format in Java. Load the Excel (XLS, XLSX) workbook using Converter. Convert it to PDF format using any of the overloaded convert() methods using PdfConvertOptions.
pdf, which is much more current and works using the latest stable release apache poi 3.17 . So we should using this. But since even those newer PdfOptions and PdfConverter are not part of the apache poi project, apache poi will not testing those with their releases.
Have you used ExcelToHtmlConverter
? It's in 3.13 release of the Apache POI. It has the same usage as WordToHtmlConverter
. After converting Excel to HTML you can use iText to convert HTML to PDF. This is a PDF I got by using those tools:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With