Учебное пособие по Apache POI
Добро пожаловать в учебник Apache POI. Иногда нам нужно прочитать данные из файлов Microsoft Excel или нам нужно создать отчеты в формате Excel, в основном для целей бизнеса или финансов. Java не предоставляет встроенной поддержки для работы с файлами Excel, поэтому нам нужно искать API с открытым исходным кодом для работы. Когда я начал искать Java API для Excel, большинство людей рекомендовали JExcel или Apache POI. После дальнейших исследований я обнаружил, что Apache POI — это то, что нужно, по следующим основным причинам. Есть и другие причины, связанные с расширенными функциями, но не будем вдаваться в подробности.
- Поддержка фонда Apache.
- JExcel не поддерживает формат xlsx, тогда как POI поддерживает форматы xls и xlsx.
- Apache POI обеспечивает поточную обработку, которая подходит для больших файлов и требует меньше памяти.
Апач POI
Apache POI обеспечивает отличную поддержку для работы с документами Microsoft Excel. Apache POI может работать с форматами электронных таблиц XLS и XLSX. Вот некоторые важные моменты, касающиеся Apache POI API:
- Apache POI содержит реализацию HSSF для формата файлов Excel 97 (-2007), то есть XLS.
- Реализация Apache POI XSSF должна использоваться для файлов формата Excel 2007 OOXML (.xlsx).
- API Apache POI HSSF и XSSF предоставляет механизмы для чтения, записи и изменения электронных таблиц Excel.
- Apache POI также предоставляет API SXSSF, который является расширением XSSF для работы с очень большими листами Excel. SXSSF API требует меньше памяти и подходит для работы с очень большими электронными таблицами и ограниченным объемом динамической памяти.
- Вы можете выбрать одну из двух моделей: событийную и пользовательскую. Модель события требует меньше памяти, потому что файл excel читается в токенах и требует их обработки. Пользовательская модель более объектно-ориентирована и проста в использовании, и мы будем использовать ее в наших примерах.
- Apache POI обеспечивает превосходную поддержку дополнительных функций Excel, таких как работа с формулами, создание стилей ячеек путем заполнения цветом и границами, шрифтами, верхними и нижними колонтитулами, проверками данных, изображениями, гиперссылками и т. д.
Зависимости Apache POI Maven
Если вы используете maven, добавьте ниже зависимости Apache POI.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
Пример Apache POI — чтение файла Excel
package com.journaldev.excel.read;
public class Country {
private String name;
private String shortCode;
public Country(String n, String c){
this.name=n;
this.shortCode=c;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getShortCode() {
return shortCode;
}
public void setShortCode(String shortCode) {
this.shortCode = shortCode;
}
@Override
public String toString(){
return name + "::" + shortCode;
}
}
Пример программы Apache POI для чтения файла Excel в список стран выглядит следующим образом. ReadExcelFileToList.java
package com.journaldev.excel.read;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFileToList {
public static List<Country> readExcelData(String fileName) {
List<Country> countriesList = new ArrayList<Country>();
try {
//Create the input stream from the xlsx/xls file
FileInputStream fis = new FileInputStream(fileName);
//Create Workbook instance for xlsx/xls file input stream
Workbook workbook = null;
if(fileName.toLowerCase().endsWith("xlsx")){
workbook = new XSSFWorkbook(fis);
}else if(fileName.toLowerCase().endsWith("xls")){
workbook = new HSSFWorkbook(fis);
}
//Get the number of sheets in the xlsx file
int numberOfSheets = workbook.getNumberOfSheets();
//loop through each of the sheets
for(int i=0; i < numberOfSheets; i++){
//Get the nth sheet from the workbook
Sheet sheet = workbook.getSheetAt(i);
//every sheet has rows, iterate over them
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
String name = "";
String shortCode = "";
//Get the row object
Row row = rowIterator.next();
//Every row has columns, get the column iterator and iterate over them
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
//Get the Cell object
Cell cell = cellIterator.next();
//check the cell type and process accordingly
switch(cell.getCellType()){
case Cell.CELL_TYPE_STRING:
if(shortCode.equalsIgnoreCase("")){
shortCode = cell.getStringCellValue().trim();
}else if(name.equalsIgnoreCase("")){
//2nd column
name = cell.getStringCellValue().trim();
}else{
//random data, leave it
System.out.println("Random data::"+cell.getStringCellValue());
}
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.println("Random data::"+cell.getNumericCellValue());
}
} //end of cell iterator
Country c = new Country(name, shortCode);
countriesList.add(c);
} //end of rows iterator
} //end of sheets for loop
//close file input stream
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
return countriesList;
}
public static void main(String args[]){
List<Country> list = readExcelData("Sample.xlsx");
System.out.println("Country List\n"+list);
}
}
Программа очень проста для понимания и содержит следующие шаги:
- Создайте экземпляр
Workbook
на основе типа файла.XSSFWorkbook
для формата xlsx иHSSFWorkbook
для формата xls. Обратите внимание, что мы могли бы создать класс-оболочку с фабричным шаблоном, чтобы получить экземпляр книги на основе имени файла. - Используйте workbook getNumberOfSheets(), чтобы получить количество листов, а затем используйте цикл for для анализа каждого из листов. Получите экземпляр
Sheet
с помощью метода getSheetAt(int i). - Получите итератор
Row
, а затем итераторCell
, чтобы получить объект Cell. Apache POI использует здесь шаблон итератора. - Используйте switch-case, чтобы прочитать тип ячейки и обработать его соответствующим образом.
Теперь, когда мы запускаем приведенную выше примерную программу Apache POI, она выводит на консоль следующий вывод.
Random data::1.0
Random data::2.0
Random data::3.0
Random data::4.0
Country List
[India::IND, Afghanistan::AFG, United States of America::USA, Anguilla::AIA,
Denmark ::DNK, Dominican Republic ::DOM, Algeria ::DZA, Ecuador ::ECU]
Пример Apache POI — запись файла Excel
Запись файла excel в apache POI аналогична чтению, за исключением того, что здесь мы сначала создаем книгу. Затем установите значения листов, строк и ячеек и используйте FileOutputStream, чтобы записать их в файл. Давайте напишем простой пример POI Apache, в котором мы будем использовать список стран из описанного выше метода для сохранения в другой файл на одном листе. WriteListToExcelFile.java
package com.journaldev.excel.read;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteListToExcelFile {
public static void writeCountryListToFile(String fileName, List<Country> countryList) throws Exception{
Workbook workbook = null;
if(fileName.endsWith("xlsx")){
workbook = new XSSFWorkbook();
}else if(fileName.endsWith("xls")){
workbook = new HSSFWorkbook();
}else{
throw new Exception("invalid file name, should be xls or xlsx");
}
Sheet sheet = workbook.createSheet("Countries");
Iterator<Country> iterator = countryList.iterator();
int rowIndex = 0;
while(iterator.hasNext()){
Country country = iterator.next();
Row row = sheet.createRow(rowIndex++);
Cell cell0 = row.createCell(0);
cell0.setCellValue(country.getName());
Cell cell1 = row.createCell(1);
cell1.setCellValue(country.getShortCode());
}
//lets write the excel data to file now
FileOutputStream fos = new FileOutputStream(fileName);
workbook.write(fos);
fos.close();
System.out.println(fileName + " written successfully");
}
public static void main(String args[]) throws Exception{
List<Country> list = ReadExcelFileToList.readExcelData("Sample.xlsx");
WriteListToExcelFile.writeCountryListToFile("Countries.xls", list);
}
}
Пример Apache POI — чтение формулы Excel
package com.journaldev.excel.read;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFormula {
public static void readExcelFormula(String fileName) throws IOException{
FileInputStream fis = new FileInputStream(fileName);
//assuming xlsx file
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
switch(cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println("Cell Formula="+cell.getCellFormula());
System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType());
if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
System.out.println("Formula Value="+cell.getNumericCellValue());
}
}
}
}
}
public static void main(String args[]) throws IOException {
readExcelFormula("FormulaMultiply.xlsx");
}
}
Когда мы выполняем приведенную выше примерную программу apache poi, мы получаем следующий вывод.
1.0
2.0
3.0
4.0
Cell Formula=A1*A2*A3*A4
Cell Formula Result Type=0
Formula Value=24.0
Пример Apache POI — формула записи Excel
Иногда нам нужно выполнить некоторые вычисления, а затем записать значения ячеек. Мы можем использовать формулы Excel для выполнения этого расчета, и это сделает его более точным, потому что значения изменятся, если будут изменены значения ячеек, используемые в вычислениях. Давайте посмотрим на простой пример написания файла excel с формулами с использованием apache poi api. WriteExcelWithFormula.java
package com.journaldev.excel.read;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcelWithFormula {
public static void writeExcelWithFormula(String fileName) throws IOException{
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Numbers");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(10);
row.createCell(1).setCellValue(20);
row.createCell(2).setCellValue(30);
//set formula cell
row.createCell(3).setCellFormula("A1*B1*C1");
//lets write to file
FileOutputStream fos = new FileOutputStream(fileName);
workbook.write(fos);
fos.close();
System.out.println(fileName + " written successfully");
}
public static void main(String[] args) throws IOException {
writeExcelWithFormula("Formulas.xlsx");
}
}