Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

convert excel file to pdf using java, itext and POI API and retain the settings

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

excel file as .png file. As you can see, the file is a simple one. I want the same styles as in Excel in pdf also. Please guide me

like image 548
user1416631 Avatar asked Apr 04 '14 07:04

user1416631


People also ask

How can I convert XLS to PDF in Java?

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.

Does Apache POI support PDF?

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.


1 Answers

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:

sample

like image 53
Chen Avatar answered Oct 21 '22 06:10

Chen