Covert Excel Worksheet to PDF

[Joe San]

Guys,

I would like to know of an API (preferably OpenSource) which would convert my excel worksheets as it is to a pdf file. My Excel document has around 600 worksheets and the API has to generate a pdf for each one of those sheets. Any idea which API to use if any is available. I would not go for OpenOffice. Anything else?

[Joe San]

I tried the JExcel API, but it does not provide any option to convert the excel worksheet to a PDF??

[Joe San]

How would it look like if I decide to use OpenOffice?

[Srikanth Nutigattu]

Hmm...
My two cents:
Use Apache POI http://poi.apache.org/ To read from the excel files.
Use iText http://www.lowagie.com/iText/ to generate PDF files.

Not sure if you can achieve it with a much simpler approach!
HTH

[Ulf Dittmer]

The JODConverter library makes it easy to use OpenOffice in server mode to convert between document formats.

[Joe San]

But I looked at the SourceForge website for JODConverter and the downloadables look like a web application.

http://sourceforge.net/projects/jodconverter/files/

[Joe San]

Ok i downloaded the zip file.

Referring to the example here on this link http://www.artofsolving.com/node/16 do I need to have OpenOffice installed? How can I ensure that the OpenOffice application that I have installed runs on port 8100? I did not quite understand this...

[Joe San]

I have multiple worksheets in my excel and I would like to create a pdf for each one of them with the name of the worksheet being the name of the pdf. Is this achievable with the JODConverter?

[Joe San]

I installed OpenOffice and I would like to know how can I make it listen on port 8100 as the JODConverter requires that OpenOffice listens onn Port 8100?

[Rob Spoor]

From the README inside the ZIP file:

soffice -headless -accept="socket,port=8100;urp;"

[Joe San]

The below is what I'm trying and not able to get the connection with OpenOffice. I have installed OpenOffice 3. Where did I go wrong? Getting null for the connection instance...

package com.cognizant.exceltopdf;

import java.io.File;
import java.util.Date;

import com.artofsolving.jodconverter.DocumentConverter;
import com.artofsolving.jodconverter.openoffice.connection.OpenOfficeConnection;
import com.artofsolving.jodconverter.openoffice.connection.SocketOpenOfficeConnection;
import com.artofsolving.jodconverter.openoffice.converter.OpenOfficeDocumentConverter;


public class ExcelReader {
	
	private static OpenOfficeConnection connection;
	
	public ExcelReader() {
		// connect to an OpenOffice.org instance running on port 8100
		connection = new SocketOpenOfficeConnection(8100);		
	}
	
	public static void main(String[] args) {
		getExcelTab();
	}
	
	public static void getExcelTab() {
		try {
			final String fileName = "C:\\Dokumente und Einstellungen\\jothi\\Desktop\\source.xls";
			String destFileName = "C:\\Dokumente und Einstellungen\\jothi\\Desktop\\result.pdf";
			File inputFile = new File(fileName);
			//Connect to Open Office
			connection.connect();
			File outputFile = new File(destFileName);
			// convert
			DocumentConverter converter = new OpenOfficeDocumentConverter(connection);
			converter.convert(inputFile, outputFile);
			// close the connection
			connection.disconnect();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
}

[Rob Spoor]

You only initialize your static connection when you create an instance, which you never do.

[Joe San]

Fine, I corrected my mistake. I tried running it and it gave me the output pdf. But what I want to have is to have separate pdf's for each of the worksheets inside. Literally, I would like to iterate through the worksheets in the given excel file, use the name of the worksheet to print the pdf. In the end I would be having as many pdf's as the number of worksheets. How could I now acheive this?

[Joe San]

Rob, your help was really appreciated, but can you help me furthur?

[Rob Spoor]

Not really, sorry. I haven't worked with Open Office yet. Perhaps someone else has. If so you will surely get an answer.

Also, patience is a virtue.

[Deepak Bala]

I am not sure how customizable your current solution is. Why not try the POI + iText combo ? Your final solution will be flexible. Is time a constrain ? How many times will this tool be invoked ? Will it need to be very flexible ?

[Ulf Dittmer]

From the user guide: "HTML can be used as an output format but while all other formats always generate a single output file, HTML can produce multiple files.", so you may be out of luck. You might check out the source code to see how hard it would be to add this functionality.

[Joe San]

Deepak Bala wrote:I am not sure how customizable your current solution is. Why not try the POI + iText combo ? Your final solution will be flexible. Is time a constrain ? How many times will this tool be invoked ? Will it need to be very flexible ?

Well, this would be invoked just once a month or so. But it very much required that a single pdf be generated for each such worksheets inside the excel workbook. I think with POI, I can get the individual worksheets. Am I right here? But how do I manipulate the Sheet object using iText? Is there a straightforward solution already available? Any suggestions?

[Deepak Bala]

Jothi Shankar Kumar wrote:

Deepak Bala wrote:I am not sure how customizable your current solution is. Why not try the POI + iText combo ? Your final solution will be flexible. Is time a constrain ? How many times will this tool be invoked ? Will it need to be very flexible ?

Well, this would be invoked just once a month or so. But it very much required that a single pdf be generated for each such worksheets inside the excel workbook. I think with POI, I can get the individual worksheets. Am I right here? But how do I manipulate the Sheet object using iText? Is there a straightforward solution already available? Any suggestions?

If you take this route you will indeed be able to do what you want to. However you will have to code using both POI and iText specific methods, which may take some learning to get by. I have used iText before but I cannot remember what the Sheet object is. Heck I cant even remember some of the basic classes used in the API. You will have to consult the iText book and online forums

[Ulf Dittmer]

iText has no provisions for working with POI objects. You'd have to code that yourself.

If this was my problem I'd start by looking into the OO Java API - the "accessing file formats" FAQ page links to an article that shows how to access spreadsheets through it. If that didn't work out I'd look into adapting JODConverter. The last approach would be to code it myself using POI and iText.

[shukla raghav]

i think JasperReports do have a way for fetching data from excel sheets. Yes i am very much sure. iText is just a rendering tool used for designing the report.

[Rob Spoor]

If the PDF contains one page per worksheet, perhaps you can split the PDF file - one page in the original becomes a complete new PDF file.

[Joe San]

Rob Prime wrote:If the PDF contains one page per worksheet, perhaps you can split the PDF file - one page in the original becomes a complete new PDF file.

Nope...sometimes one sheet or one workbook in an excel document could span multiple pdf pages.

[Rob Spoor]

You can perhaps try to use Apache POI or JExcelAPI (I prefer the latter) to convert your Excel file into multiple HTML files; simply use <table> elements. Then convert those each to a single PDF file:

Workbook workbook = Workbook.getWorkbook(myFile);
for (Sheet sheet: workbook.getSheets())
{
    StringBuilder sb = new StringBuilder();
    sb.append("<table>");
    sb.append("</table>");
    int rows = sheet.getRows();
    for (int i = 0; i < rows; i++)
    {
        sb.append("<tr>");
        for (Cell cell: sheet.getRow(i))
        {
            sb.append("<td style=\"");
            sb.append(getStyle(cell));
            sb.append("\">");
            sb.append(getCellValue(cell));
            sb.append("</td>");
        }
        sb.append("</tr>");
    }
}

String getCellValue(Cell cell)
{
    if (cell instanceof BooleanCell) // includes BooleanFormulaCell
    {
        BooleanCell boolCell = (BooleanCell)cell;
        return String.valueOf(boolCell.getValue());
    }
    if (cell instanceof DateCell) // includes DateFormulaCell
    {
        DateCell dateCell = (DateCell)cell;
        return dateCell.getDateFormat().format(dateCell.getDate());
    }
    // similar for other Cell types
    return "";
}

String getStyle(Cell cell)
{
    CellFormat format = cell.getCellFormat();
    if (format == null)
    {
        return "";
    }
    Alignment alignment = format.getAlignment(); // left, center, right etc
    Font font = format.getFont(); // use getBoldWeight for bold, isItalic, isStruckout, getUnderlineStyle etc
    // etc
    return "some string built from the CellFormat";
}

Of course this will not include the more advanced features like graphs and images but it's a start.

[Joe San]

Yes, I'm already doing something of that sort what you had mentioned and as Ulf has mentioned in the above post.

[Joe San]

And thanks Rob once again for the help!

[Joe San]

Once I succeed, I would post the entire source code here for corrections / reference...

[Deepak Bala]

Jothi Shankar Kumar wrote:Once I succeed, I would post the entire source code here for corrections / reference...

Think twice if that would be a wise idea, in case the code is to be copyrighted or something like that.

[Joe San]

Since this thread is long enough I started a new thread which continues with the furthur discussions on this topic and it is here https://coderanch.com/t/465633/Java-General/java/Converting-Excel-Workbook-PDF#2080981

Moderators kindly lock this thread please!

[Rob Spoor]

This thread is nowhere near "too long". As Campbell already suggested in your other thread one thread is enough. If the number of posts exceeds a certain number we simply will get a second page.

About your questions in that thread:
1) set the border attribute of the table to 1.
2) I have no idea to be honest.

[Joe San]

Thanks Rob. I'm just considering some other options to get this done. Would keep posting my questions ans solutions here as and when I find them.

[Rob Spoor]

Since it's all the same issue, converting Excel into PDF, that's not a problem. Only create a new thread for a new topic, when it no longer has anything to do with this conversion issue.

[Joe San]

So the complete working code for the Excel to PDF conversion can be found at the following post (Scroll to the end for the final working code)

https://coderanch.com/t/465904/Other-Open-Source-Projects/Strange-error-with-Apache-POI#2084060