6

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

3
  • Hey did you solve your problem..you can try jodconverter.. i had the same problem statement.. i used that lib and it works like a charm with very little piece of code.. Commented Sep 11, 2014 at 14:49
  • hi Sumeet, does the conversion also a file that will be stored under a local folder you have set up? can you just convert and get the stream out of it? Commented Jan 5, 2015 at 5:57
  • facing the same problem, the generated pdf is ugly with tables without border and doesn't fit in pdf viewport. Commented May 20, 2016 at 6:09

2 Answers 2

3

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

Sign up to request clarification or add additional context in comments.

Comments

0

With Apache Tika, you can convert xlsx file to html format and via apache pdfbox you can convert html formatted text to pdf.

4 Comments

hey, thanks for your reply. But, since i am using itext and poi for a long rime, want to do it with them. but, if its not at all possible, then, i shall migrate to anither 3 rd party library
First, you have to get apache tika lib. If you are using Maven, you can use that: <dependency> <groupId>org.apache.tika</groupId> <artifactId>tika-parsers</artifactId> <version>1.5</version> </dependency> And than, you can check for source codes on the stackoverflow for converting xls file to html. You can use AutoDetectParser for parsing but i don't remember related control handler's name. Please check it on the stackoverflow. After that, use pdfbox for converting html to pdf. It is very simple. Please believe in searching :)
hey, sorry, thanks for the reply. will try n let u know of teh results
Apache tika strips all formatting and styles from excel sheet.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.