Working with Excel in Java through the Apache POI

In this article you will learn about the writing and reading of data from Excel files in Java (to be considered as XLS, and XLSX format). We will use Apache POI library, and will focus on working with the types String and Date, the last work is fairly tricky. Let me remind you that the work with the numbers we have already considered  in another article.

Library poi-XX.jar you can use all the old (xls, doc, ppt) files, Microsoft Office, for new (xlsx,docx, pptx), you will need poi-ooxml-XX.jar. It is important to understand that what is, as used classes are also different - for older extensions isHSSFWorkbook, and for new - XSSFWorkbook.

Preparation: loading libraries and dependencies

Of course, there are many public libraries that allow you to work with Excel files in Java, for example, JXL, but we will be having the most extensive use of the API and the most popular - Apache POI. To use it, you need to download jar files and add them through Eclipse manually, or you can leave it to Maven.

In the second case, you simply need to add the following two dependencies:

1 2 3 4 5 6 7 8 9 10 11 12

<dependencies>     <dependency>         <groupId>org.apache.poi</groupId>         <artifactId>poi</artifactId>         <version>3.12</version>     </dependency>     <dependency>         <groupId>org.apache.poi</groupId>         <artifactId>poi-ooxml</artifactId>         <version>3.12</version>     </dependency>   </dependencies>

The most convenient in Maven - it will load not only the specified poi.jar andpoi-ooxml.jar, but all the jar files that are used internally, ie xmlbeans-2.6.0.jar,stax-api-1.0.1.jar , poi-ooxml-schemas-3.12.jar and commons-codec-1.9.jar.

If you add a library manually - do not forget about the above-mentioned files. Download all possible fromhere. Remember - if you download only thepoi-XX.jar, then your code compiles without errors, but then falls to java.lang.NoClassDefFoundError: org / apache / xmlbeans / XmlObject,because inside will be called xmlbeans.jar.

Recording

In this example, we write in the xls file the following data: the first cell - a string with the name, and the second - the date of birth. Here are step by step instructions:

• Create object HSSFWorkBook;
• Create a list using the facility, created in the previous step, createSheet ();
• Create a line on a sheet using createRow ();
• Create a cell in the row - createCell ();
• Asking cell value through setCellValue ();
• Write the workbook in the File through FileOutputStream;
• Close the workbook, causing address close e-().

To write numbers or strings that is enough, but to record the date, we need to do something else:

• Create DateFormat;
• Create CellStyle;
• Record DateFormat in CellStyle;
• Record CellStyle into the cell;
• Now in this cell can be written object Date through all the same setCellValue;
• To date fit into a cell, we need to add a column to automatically change the size of the property: sheet.autoSizeColumn (1).

All together it will look like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

@SuppressWarnings("deprecation")     public static void writeIntoExcel(String file) throws FileNotFoundException, IOException{         Workbook book = new HSSFWorkbook();         Sheet sheet = book.createSheet("Birthdays");         // Нумерация начинается с нуля         Row row = sheet.createRow(0);                  // Мы запишем имя и дату в два столбца         // имя будет String, а дата рождения --- Date,         // формата dd.mm.yyyy         Cell name = row.createCell(0);         name.setCellValue("John");                  Cell birthdate = row.createCell(1);                  DataFormat format = book.createDataFormat();         CellStyle dateStyle = book.createCellStyle();         dateStyle.setDataFormat(format.getFormat("dd.mm.yyyy"));         birthdate.setCellStyle(dateStyle);                  // Нумерация лет начинается с 1900-го         birthdate.setCellValue(new Date(110, 10, 10));                  // Меняем размер столбца         sheet.autoSizeColumn(1);                  // Записываем всё в файл         book.write(new FileOutputStream(file));         book.close();     }

Reading

Now we think of the newly created file that we recorded there.

• First, create HSSFWorkBook, passed to the constructor FileInputStream;
• We get the list, passing getSheet () its name or number;
• Get the string using getRow ();
• Get the cell using getCell ();
• We learn the type of cell, using her getCellType ();
• Depending on the type of cell, read its values ​​using getStringCellValue (),getNumericCellValue () or getDateCellValue ();
• Close the workbook using the address close e-().

Recall that Excel stores dates as numbers, ie cell type will still CELL_TYPE_NUMERIC.

In the form of this code is as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

public static void readFromExcel(String file) throws IOException{         HSSFWorkbook myExcelBook = new HSSFWorkbook(new FileInputStream(file));         HSSFSheet myExcelSheet = myExcelBook.getSheet("Birthdays");         HSSFRow row = myExcelSheet.getRow(0);                  if(row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_STRING){             String name = row.getCell(0).getStringCellValue();             System.out.println("name : " + name);         }                  if(row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){             Date birthdate = row.getCell(1).getDateCellValue();             System.out.println("birthdate :" + birthdate);         }                  myExcelBook.close();              }

Finally

As mentioned above upomnalos reading from xlsx file does not differ - but instead needHSSFWorkBook, HSSFSheet, HSSFRow (and others) from poi-XX.jar use XSSFWorkBook,XSSFSheet, XSSFRow of poi-ooxml-XX.jar. That's all you need to know to read and write files in Excel. Of course, with the help of Apache POI library, you can do much more, but this article is to help you quickly get used to it.