Learn to Read and Write Microsoft Excel Documents with Jakarta’s POI

Package

Purpose

org.apache.poi.hssf.eventmodel

Handles different events generated in the process of readingand writing Excel documents.

org.apache.poi.hssf.eventusermodel

Provides classes to read Excel documents.

org.apache.poi.hssf.record.formula

Contains classes to handle FORMULA used in Excel document.

org.apache.poi.hssf.usermodel

Contains classes to generate Excel documents.

org.apache.poi.hssf.util

Contains utility classes to handle different attributes of theExcel document.

• Create the header by spanning it across two cells.
• Leave a gap of one row.
• Populate the first and second cell of the following row with the displayed data.
• The last row displays a total, based on a formula that calculates the total value.

You can see that creating the spreadsheet requires separate tasks where you create a worksheet, create rows and cells, create a merged region for the worksheet caption, specify different styles for the cells and create a special type of cell to contain the mathematical formula that calculates the total. Finally, you have to save the workbook in the file system.

Creating a new Excel worksheet is simple. First you create a new workbook:

   // create a new workbook   HSSFWorkbook workBook = new HSSFWorkbook();

Then you can create a new worksheet within that workbook:

   //create a new worksheet   HSSFSheet sheet = workBook.createSheet();   etc...

You create rows by calling the Worksheet object’s createRow method. Remember, the row index starts from 0.

   // ***************header data******************//   //create a header row   HSSFRow headerRow = sheet.createRow((short) 0);

To create the merged area for the caption you want to define a merged region from the first to the third row, and from first to the tenth column. Here’s the code:

   //define the area for the header data(row1->row3,       col1-->col10)   sheet.addMergedRegion(new Region(0, (short) 0, 2,       (short) 10));

After creating the caption region, create a cell to contain the caption value.

   //create the header data cell   HSSFCell headerCell =       headerRow.createCell((short) 0);   //add the date to the header cell   headerCell.setCellValue("The Bowling Score");

Because the header row spans multiple rows and columns, the caption “The Bowling Score” will span the defined merged region.

Worksheet cells have many display properties that you can control through HSSF. To do that, you create an HSSFCellStyle object and apply styles such as cell alignment (CENTER/LEFT/RIGHT). The following code shows how you can set the background colors and the font style for individual cells.

   //create a style for the header cell   HSSFCellStyle headerStyle =       workBook.createCellStyle();           headerStyle.setAlignment(      HSSFCellStyle.ALIGN_CENTER);   headerCell.setCellStyle(headerStyle);         //create a style for this header columns   HSSFCellStyle columnHeaderStyle =        workBook.createCellStyle();      columnHeaderStyle.setFillBackgroundColor(      HSSFColor.BLUE_GREY.index);      columnHeaderStyle.setFillForegroundColor(      HSSFColor.BLUE_GREY.index);      HSSFFont font = workBook.createFont();   font.setColor(HSSFFont.COLOR_RED);   columnHeaderStyle.setFont(font);

After defining cell styles, you can apply those styles as follows:

   HSSFCell colHeading1 =       columnHeaderRow.createCell((short) 0);   HSSFCell colHeading2 =       columnHeaderRow.createCell((short) 4);      colHeading1.setCellStyle(columnHeaderStyle);   colHeading2.setCellStyle(columnHeaderStyle);

The Worksheet essentially consists of multiple rows and cells. You can create as many rows and cells as you need and then apply styles and assign data to the cells. The following example creates five rows with two cells in each row. The code sets the cell values last, using the setCellValue method.

   //**************report data rows and cols*********//   // create 5 rows of data   HSSFRow row1 = sheet.createRow((short) 5);   HSSFRow row2 = sheet.createRow((short) 6);   HSSFRow row3 = sheet.createRow((short) 7);   HSSFRow row4 = sheet.createRow((short) 8);   HSSFRow row5 = sheet.createRow((short) 9);      // create the 2 cells for each row   HSSFCell c11 = row1.createCell((short) 0);   HSSFCell c12 = row1.createCell((short) 4);   HSSFCell c21 = row2.createCell((short) 0);   HSSFCell c22 = row2.createCell((short) 4);   HSSFCell c31 = row3.createCell((short) 0);   HSSFCell c32 = row3.createCell((short) 4);   HSSFCell c41 = row4.createCell((short) 0);   HSSFCell c42 = row4.createCell((short) 4);      // writing data to the cells   c11.setCellValue("Sam");   c12.setCellValue(100);      c21.setCellValue("John");   c22.setCellValue(50);      c31.setCellValue("Paul");   c32.setCellValue(25);      c41.setCellValue("Richard");   c42.setCellValue(20);

One of the main advantages of Excel is that you can attach mathematical formulas to cells. The following code shows how to attach a formula to a cell. The example assigns a formula to calculate the total value of other cells in the worksheet. Notice that, in order to create a cell with a formula, you must first set the cell type to the FORMULA type.

   //create a formula for the total           totalValue.setCellType(HSSFCell.CELL_TYPE_FORMULA);   totalValue.setCellFormula("SUM(E6:E9)");

When the workbook is complete, you can save it to a file system by opening a FileOutputStream object for the destination file and writing the Workbook contents to the stream using the write method.

   FileOutputStream stream = new       FileOutputStream("c:/dev/src/customs/Book1.xls");   workBook.write(stream);

It is really as simple as that. Here’s the entire procedure in condensed form.

• First, create a HSSFWorkbook object.
• Obtain a HSSFWorksheet object from the HSSFWorkbook object. Note, that the only way, you can obtain a reference to a Worksheet object is via a Workbook object. There’s no public constructor for the Worksheet object. This makes sense because Excel has no model for Worksheets that exist separately from a Workbook.
• Similarly, you obtain HSSFRow objects from the Worksheet object and HSSFCell objects from Row objects?the existence of each type depends on its parent.
• To create headers, create a merged region of rows and cells using the org.apache.poi.hssf.util.Region object. Note that the collections of rows and cells are zero-based.
• Add the data to the cells using the HSSFCell object’s setCellValue method. Cells support all Java native data types such as String, int, boolean etc. The API also supports some other commonly used data types such as Date and Calendar and lets you set those through the setCellValue() method.
• The HSSFCellStyle class in HSSF handles display attributes for the cells such as color, font, width, height etc.. You can create an arbitrary style for a particular cell. For complex attributes such as colors and fonts HSSF provides objects such as HSSFColour and HSSFFont.
• The cells support formulas. The example applies the formula SUM(E6:E9) to the total cell value.
• Lastly, to physically create the Excel document in the file system, you need to create an OutputStream object and call the HSSFWorkbook’s write method, passing the created OutputStream object as a parameter.

Reading an Excel Document
Reading an existing Excel document is just as simple as creating one. HSSF provides a custom POIFSFileSystem object that specializes in reading Microsoft’s OLE 2 documents. Using the POIFSFileSystem object, you construct an HSSFWorkbook object from a specified Excel document. Here’s an example.

   //create a POIFSFileSystem object to read the data   POIFSFileSystem fs = new POIFSFileSystem(       new FileInputStream(       "c:/dev/src/customs/Book1.xls"));

Next, create a new workbook from the input stream. You can obtain a reference to any of the worksheets within the workbook. Notice that the worksheet index starts at 0.

   // create a workbook out of the input stream   HSSFWorkbook wb = new HSSFWorkbook(fs);   // get a reference to the worksheet       HSSFSheet sheet = wb.getSheetAt(0);

A Little Bit More
Some of you may need to use Java to access Excel from a Web application. In such cases, you may need to construct an Excel document and show it in a browser. That’s not difficult but does require a little trick. You send the Excel document as the response, but to make it appear in Excel, you need to set the MIME type of the response to the appropriate type. This method is browser independent. Here’s an example:

   try {      OutputStream out = res.getOutputStream();         // SET THE MIME TYPE      res.setContentType("application/vnd.ms-excel");         // set content dispostion to attachment in       // case the open/save dialog needs to appear      res.setHeader("Content-disposition",          "inline; filename=sample");         workBook.write(out);         out.flush();         out.close();   } catch (FileNotFoundException fne) {        System.out.println("File not found...");   } catch (IOException ioe) {        System.out.println("IOException..." +            ioe.toString());   }

The following points are important:

• Use the MIME type string “application/vnd.ms-excel”
• In the response header, the content-disposition entry defines how to deliver this content to the browser. For example, an inline attribute specifies that the browser should present an Open/Save/Cancel dialog before opening this document. The filename attribute specifies a default file name for the document.

This tutorial should help you to get started in the world of Java-to-Microsoft bridges. The POI project offers considerably more than I’ve discussed here, such as the capability to interact with Microsoft Word and with Adobe’s PDF files. The project is very active, so you should monitor the POI project and keep up what’s going on.

The Latest

6 Online Coding Resources

March 18, 2023 5:59 pm

One of the most rapidly growing job industries today is coding. As more and more of the world moves to a digital format, the demand for skilled coders continues to increase. With a high demand for the skillset, coders tend to be very well compensated for their work. Furthermore, coders

The Best Malware Removal Software in 2023

March 17, 2023 5:48 pm

In the modern world, computers are practically a necessity for every home. Computers give us access to vast amounts of information through the internet, as well as provide us with innovative programs and applications like social media, email, and much more. But with these benefits also come the threats of

Why Technology Literacy is So Important for Modern Workers 

March 17, 2023 5:45 pm

Importance of Having Technology Literacy Today’s world is completely reliant on technological products and advancements. In the current technology-based era, it is crucial to have technology literacy. And these particular instances might be most suitable for the cases of modern workers dealing with modern equipment, especially in the construction sector. 

The Small Business & Data Analytics: What You Need to Know

March 17, 2023 3:49 pm

The objective of any business is to afford its community a product and create extra money for the services provided. Ideally, this always goes as planned, but at times when it doesn’t, having an extra eye on the data analytics of your small business may make a significant difference. Metrics

Break Into Technology Sales in 4 Easy Steps

March 15, 2023 8:20 pm

One of the fastest-growing industries of the 21st century is undoubtedly tech sales. Between innovations like computers, AI, social media, and more, tech is everywhere. And the demand continues to increase. With constant innovations and massive demand, tech sales have become an extremely lucrative industry. As such, there is no