Working with an Excel spreadsheet from Java
Working with an Excel spreadsheet from Java
Actually, a problem arose - to process the data from the table and based on them to get another table.
There are several solutions to this problem:
- Macro - the only problem is VBA, for the study of which there is no time at all, and its syntax is not like
- An application in C # here everything seems to be fine, but the machine on which this application will be run immediately has many additional requirements:
- .NET Framework
- Installed Office
- Installed Primary Interoperability Assembly (PIA) for the Office application
- Java bundle and Apache POI library — I want to dwell on this method in more detail
Apache POI is a Java API for accessing Microsoft format documents.
Actually, the POI-HSSF project is intended for working with Excel tables . At the moment there are 2 library options:
- POI 3.5 beta 5, and Office Open XML Support (2009-02-19) —will work on support for the Office 2007 format
- POI 3.2-FINAL Released (2008-10-19) - latest stable release
I will talk about working with version 3.2.
The main class for working with an Excel table is the HSSFWorkbook class of the org.apache.poi.hssf.usermodel package, which represents an Excel workbook.
To read a book from a file, you can use the following code: The method returns an object of the HSSFWorkbook class if everything is successful and null otherwise. The following method can be used to save changes: The method writes the wb workbook to fileName. The HSSFSheet class is designed to work with workbook sheets. An object of the HSSFSheet class can be obtained in the following ways:
public static HSSFWorkbook readWorkbook(String filename) {
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
HSSFWorkbook wb = new HSSFWorkbook(fs);
return wb;
}
catch (Exception e) {
return null;
}
}
public static void writeWorkbook(HSSFWorkbook wb, String fileName) {
try {
FileOutputStream fileOut = new FileOutputStream(fileName);
wb.write(fileOut);
fileOut.close();
}
catch (Exception e) {
//Обработка ошибки
}
}
- By name
HSSFSheet sheet= wb.getSheet("Лист 3")
- By number (numbering starts from 0)
HSSFSheet sheet= wb.getSheet(0)
- Create a new sheet
HSSFSheet sheet= wb.createSheet(["имя листа"])
The next object is the line - HSSFRow.
You can also access the string object in 3 ways:
- By index (indexing starts at 0)
HSSFRow row = sheet.getRow(index)
- Through an iterator
Iterator rowIter = sheet.rowIterator(); while (rowIter.hasNext()) { HSSFRow row = (HSSFRow) rowIter.next(); }
- Create a new line
HSSFRow row=sheet.createRow(0);
Well, the last object on the data access path is an object of the HSSFCell class - a row cell.
You can access the cell from the row object in the following ways:
- By cell index (indexing starts at 0)
HSSFCell cell = row.getCell(0);
- Through an iterator
Iterator cellIter = row.cellIterator(); while (cellIter.hasNext()) { HSSFCell cell = (HSSFCell) cellIter.next(); }
- Create a new cell
HSSFCell cell = row.createCell(0);
You can already receive and set data directly from the cell object:
- Boolean value
boolean b = cell.getBooleanCellValue();
cell.setCellValue(b); - date
Date date = cell.getDateCellValue();
cell.setCellValue(date); - Numerical value
double d = cell.getNumericCellValue();
cell.setCellValue(d); - String value
String str = cell.getRichStringCellValue().getString();
cell.setCellValue(new HSSFRichTextString(str)); - Formula
String formula = cell.getCellFormula();
cell.setCellFormula(formula);
This knowledge is enough to process simple tables.
The library also provides rich opportunities for formatting cells, for merging, freezing, etc.
A detailed description of the functions can be found on their website.
This method is primarily valuable in that it does not require the installation of the office itself and the PIA package.