my dog learned polymorphism*
The moose likes Java in General and the fly likes Covert Excel Worksheet to PDF Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "Covert Excel Worksheet to PDF" Watch "Covert Excel Worksheet to PDF" New topic
Author

Covert Excel Worksheet to PDF

Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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?


SCJP 1.4, SCWCD 1.4 - Hints for you, Certified Scrum Master
Did a rm -R / to find out that I lost my entire Linux installation!
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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

Joined: Sep 26, 2006
Posts: 9351
    
    2

How would it look like if I decide to use OpenOffice?
Srikanth Nutigattu
Ranch Hand

Joined: Oct 30, 2007
Posts: 114
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
Marshal

Joined: Mar 22, 2005
Posts: 41581
    
  54
The JODConverter library makes it easy to use OpenOffice in server mode to convert between document formats.


Ping & DNS - my free Android networking tools app
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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

http://sourceforge.net/projects/jodconverter/files/
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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
Sheriff

Joined: Oct 27, 2005
Posts: 19672
    
  18

From the README inside the ZIP file:
soffice -headless -accept="socket,port=8100;urp;"


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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...

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19672
    
  18

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

Joined: Sep 26, 2006
Posts: 9351
    
    2

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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

Joined: Oct 27, 2005
Posts: 19672
    
  18

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
Bartender

Joined: Feb 24, 2006
Posts: 6661
    
    5

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 ?


SCJP 6 articles - SCJP 5/6 mock exams - More SCJP Mocks
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41581
    
  54
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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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
Bartender

Joined: Feb 24, 2006
Posts: 6661
    
    5

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
Marshal

Joined: Mar 22, 2005
Posts: 41581
    
  54
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
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
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
Sheriff

Joined: Oct 27, 2005
Posts: 19672
    
  18

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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
Sheriff

Joined: Oct 27, 2005
Posts: 19672
    
  18

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:
Of course this will not include the more advanced features like graphs and images but it's a start.
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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

Joined: Sep 26, 2006
Posts: 9351
    
    2

And thanks Rob once again for the help!
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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

Joined: Feb 24, 2006
Posts: 6661
    
    5

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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

Moderators kindly lock this thread please!
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19672
    
  18

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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
Sheriff

Joined: Oct 27, 2005
Posts: 19672
    
  18

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9351
    
    2

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)

http://www.coderanch.com/t/465904/Other-Open-Source-Projects/Strange-error-with-Apache-POI#2084060
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Covert Excel Worksheet to PDF