Java Programming
Java Programming
How To: Modify Existing Excel Document with Apache POI
If you are like me, building applications for your customers that take existing documents and need to populate those forms with system data, you may have found yourself looking for tools to do this. In this how-to, I'll be covering Apache POI and how it can be used to populate an existing xls file with data from your application.
Here's the easiest way to add data to an existing Excel XLS file. Use Apache POI. For this how-to, I have dloaded POI 3.6 (12/14/2009) from Apache's website. You can go here for more information on POI.
Today, I'll specifically be showing you how to open an existing document in Java with POI, Locate a row and cell and set some data. First, make sure that the POI jar is somewhere in your classpath so that you have access to the libraries. PLEASE NOTE: this tutorial is for Office format XLS, versions up to and including Office 2003. This will not work with an XLSX, Office 2007/10 format. You should be able to do the same thing, just replace HSSF with XSSF in the following methods.)
First, the code to load the existing XLS into an object that Java can use:
public HSSFWorkbook loadWorkbook() {
String path = "c:/path/to/existing.xls";
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(path));
return new HSSFWorkbook(fs);
}
Now that you have an HSSFWorkbook object, you'll be able to access rows and columns to add data. The code is very simple to access a cell and set the value. It is very important that you know the formatting of the existing XLS, if you try to access a row or column beyond that which is already used, you'll throw an NPE and take your system down with it. I'll show at the end of this how to add rows/cells to an existing worksheet.
public void changeInfo() {
HSSFWorkbook myWorkbook = loadWorkbook();
HSSFSheet workingSheet = myWorkbook.getSheetAt(0); // 0 for sheet 1.
HSSFRow worksheetRow = workingSheet.getRow(24); // 0 = row 1, etc.
HSSFCell myCell = worksheetRow.getCell(3) // same as above.
// modify cell data below
}
Now that you have the access to the cell, you can set the value of the cell to anything. The HSSFCell class has a method .setCellValue that accepts the following classes: Double, Date, Calendar, String, RichTextString (POI Class) or Boolean.
Once you set the value, you can load your object with the contents of another cell and continue. It's really that simple. To add a formula, simply change the method and drop the = sign, like so:
myCell.setCellFormula("sum(A1,A4,A5)");
Then, when you save the excel document and open it in excel, the formula will be calculated like normal.
A couple things, First, To populate a merged cell, us the top-left cell address. Secondly, if your existing XLS has form elements (Radio buttons, checkboxes, Textfields, etc), you need to read the next section.
Form Controls in XLS with POI: First, in order to use form controls with POI, you must make sure that the form control is connected to a cell. To do this, Right Click on the control in the existing xls and choose Format Control. On the last tab (Control), add a cell link by clicking the button on the right and assigning it to an existing cell. Now, when the excel file is loaded, the controls will be set to the values in the linked cells.
Then, to manipulate the controls with POI, set the linked cell value to the appropriate setting to set the control. It's as simple as that. Now, to make it look cleaner, hide the text in the original xls of the linked cells so that they don't show when the file is loaded.
Saving a modified excel document: Finally, if you are programming an application and populating the excel file, you probably don't want to overwrite your master file, so you have to make a slight change on how to save it. Should be pretty straight forward, but here is the code to make sure you save in a different name.
FileOutputStream out = new FileOutputStream("c:/path/to/save/new.xls");
myWorkbook.write(out);
You can use any output stream you would like. If it's a web application in struts, for example, you might use a ServletOutputStream rather than the FileOutputStream.
Cheers, and enjoy working with existing XLS files from within your Java Application. If you are interested in creating a new XLS file, rather than working with an existing one, the tutorial on this page is good for creating new excel xls files. Finally, POI is not just for Excel Documents, but will do all Microsoft Office documents. A simple Google search should turn up other tutorials for word and other formatted documents.
^ TOP
Use "Like" in a Java Prepared Statement
To use Like in a Java PreparedStatement, you need to split up the ? by spaces. In PL_SQL your java code would look like this:
public String query =
" select column1, column2, column3 " +
" from table1 " +
" where lower(column1) like '%' || ? || '%' ";
When using like in a prepared statement, I like to add the lower function in the call so that the query is case insensitive. Then, later in your Java Code, you'd be able to use it like this
// connection is an object that holds the connection
// to your database.
PreparedStatement ps = connection.prepareStatement(query);
ps.setString(1, "Like String".toLowerCase());
// execute statement and get results
Now your Java can return a select based on a case insensitive partial search!
^ TOP