12

I was searching the internet for an answer to exporting data from my Java desktop app to Excel and I came across the Apache POI API. Then I found out that the JDBC would work, and then I stumbled on JExcel. Are they all equally good, or should I focus on the best among them, and which is it?

Cheers.

wassimans
  • 8,382
  • 10
  • 47
  • 58
Akinwale Agbaje
  • 315
  • 3
  • 6
  • 17
  • Export exactly how much? Which format? Excel supports several formats. The easiest is using csv which is comma seperated files. This is very easy to generate. Then let excel save as a "real" excel format. – Esben Skov Pedersen Apr 17 '12 at 08:01
  • Esben, the app would allow users generate a report. That report would normally be an excel file loaded with a couple of values. How large? I'm not entirely sure, but I don't think it would be too large though. The reason I'm asking about Excel is because I want the app to take a designed template (formatted with colours, a company logo, etc), put the data in via the java app, and then open the excel file for the user to view and print. Can I go down this route with CSV? – Akinwale Agbaje Apr 17 '12 at 11:36
  • possible duplicate of [JExcelAPI vs Apache POI, which is better?](http://stackoverflow.com/questions/4763624/jexcelapi-vs-apache-poi-which-is-better) – assylias Apr 17 '12 at 11:58
  • I also asked about JDBC, so it can't be a duplicate – Akinwale Agbaje Apr 17 '12 at 12:22
  • No in that case I don't think it would be a good fit for csv – Esben Skov Pedersen Apr 20 '12 at 11:25
  • Please find the link here on which one to use over Apache POI and JExcel as they are good libraries for Excel documents. [Here](http://stackoverflow.com/questions/4763624/jexcelapi-vs-apache-poi-which-is-better) – Phani Apr 17 '12 at 07:55

4 Answers4

21

Why so complicated?

Just TAB separate your columns and write the output to plain text file with an ".xls" extension?

That way, all you need to do is open the generated ".xls" file. Even though it's actually just a TAB-separated text file, Excel will open it and automatically treat each tab as a new column.

No idea why people still use csv files, frankly.

zeb
  • 243
  • 2
  • 2
  • By far the best answer. Much simpler way to export. – Watercolours May 11 '14 at 09:57
  • 6
    The problem with this approach is that the user gets a warning when opening a file with the extension ".xls" that is not xls. – Omri Spector Oct 14 '14 at 06:20
  • 4
    Indeed this has caused problems for me not only with the warning, but also when the user requests any styling within the spreadsheet, e.g. zebra striping rows. – Marty Oct 31 '14 at 15:26
  • No that is not a XLS export! Why ? Well just tell me what would you do if you had to make some formulas ? – hzitoun May 17 '16 at 08:41
  • We want to export data to Excel for what Excel offers (or any other spreadsheet app) in terms of styling tables and data representations. How do you style a table with your solution? – wassimans Aug 24 '16 at 09:04
16

I might be late to answer this, but I guess your correct choice would be Jxls. I faced a similar scenario in my module where I had to retain a certain template like logo,color,col-span,fixed column... So that's very hectic to write a separate java code and design it.

Jxls core is poi and syntactically similar to jstl, all you need is to map a array-list of bean with desired column in excel

Tadija Malić
  • 445
  • 7
  • 26
sayannayas
  • 764
  • 9
  • 15
0

If the exported excel file is large, maybe there will be outofmemory exception. (It is the problem I met before, I don't know whether it is improved now.)

The most easiest way is to export as CSV file.

Mavlarn
  • 3,807
  • 2
  • 37
  • 57
0

The best way is to do Tab separation for column and \n for row. Save as .xls.

Perfect solution :) ty Zeb

Ashish Yeole
  • 17
  • 2
  • 6
    Are you sure this is a valid answer to the question? – GabrielOshiro Jun 16 '15 at 22:32
  • 1
    Yea, this is not a good solution. First, problem is that MS Excel will prompt a warning saying "The file format and extension don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it.". Second the OP wanted to add formatting and image, this cannot be done with that. This requires [creating a real Excel file](https://www.gemboxsoftware.com/spreadsheet-java/examples/create-write-excel-file-in-java/402) or writing data into a [template spreadsheet](https://www.gemboxsoftware.com/spreadsheet-java/examples/java-export-to-excel-template/403). – NixonUposseen Nov 25 '19 at 08:00