Apache POI in Action

As most of us use MS-Excel files in our daily activities we need to create, maintain or update them regularly or once in a while. Using a Java API is perhaps the easiest way to manipulate these documents. We will see actual examples of applications wanting to create, modify and update MS-Excel files.

The Bugsby Restaurant
We will use an example of a eat-in restaurant for illustrating examples of POI-HSSF. Bugsby restaurant is based in New Orleans and has patrons arriving at its doors from all over the country. Bugsby has an order maintainance system it maintains from a custom made application which receives orders for items from patrons and registers the same in the database. This application requires to generate MS-Excel file extracts at regular intervals for stock taking purposes.
This include daily orders list generated at the end of the day, daily inventory statistics also generated at the end of the day, daily number of drinks distributed or sold in the bar details, weekly and monthly reports of the same.

Daily Orders List
The Daily Orders list is generated at the end of the day at day closure and the MS-Excel files are generated as records or backups of data generated in the Order Maintenance application. This is very similar to reports generated from an application for identical reasons or purposes with the only difference being the MS-Excel files act as actual data storage or backup for application data. This might not be true for reports as they are only preview material and do not provide a mechanism for storing the generated data.
Creating a New Workbook, New Worksheet and New Cells
package BugsbyOrderMaintainence;
/**
*
* @author Visual Basic Premier
*/
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.FileNotFoundException;
import java.io.*;
import java.util.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.*;
public class Orders_List_Create {
public static void Orders_List_Create(){
try{
FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Documents\\JavaApplication6\\Orders_List.xls");
// create a new workbook
Workbook wb = new HSSFWorkbook();
// create a new sheet
Sheet s = wb.createSheet("Orders List");
CreationHelper createHelper = wb.getCreationHelper();
s.autoSizeColumn((short)0); //adjust width of the first column
s.autoSizeColumn((short)1);
s.autoSizeColumn((short)2);
Row row0 = s.createRow((short)0);
Cell cell0 = row0.createCell(0);
Cell cell1 = row0.createCell(1);
Cell cell2 = row0.createCell(2);
Cell cell3 = row0.createCell(3);
Cell cell4 = row0.createCell(4);
Cell cell5 = row0.createCell(5);
Cell cell6 = row0.createCell(6);
Cell cell7 = row0.createCell(7);
Cell cell8 = row0.createCell(8);
cell0.setCellValue("Order Id");
cell1.setCellValue("Order Number");
cell2.setCellValue("Order Date");
cell3.setCellValue("Item Name");
cell4.setCellValue("Item Price");
cell5.setCellValue("Item Quantity");
cell6.setCellValue("Order Cost");
cell7.setCellValue("Order Table");
cell8.setCellValue("Waiter Name");
wb.write(fileOut);
fileOut.close();
}catch(Exception e){System.out.println(e);}
}
public static void main(String[] args) {
Orders_List_Create ord_list= new Orders_List_Create();
ord_list.Orders_List_Create();
}
}

 Created Orders List
The output of the above program with the created workbook, worksheet and cells are shown.