Учебник по Apache POI

Учебники
Java

Добро пожаловать в учебник Apache POI. Иногда нам необходимо читать данные из файлов Microsoft Excel или создавать отчеты в формате Excel, преимущественно для бизнеса или финансовых целей. Java не предоставляет встроенной поддержки для работы с файлами Excel, поэтому нам нужно искать открытые исходные коды API для выполнения этой задачи. Когда я начал поиск Java API для Excel, большинство людей рекомендовали JExcel или Apache POI. После дальнейших исследований я обнаружил, что Apache POI – это лучший вариант по следующим основным причинам. Есть и другие причины, связанные с расширенными функциями, но давайте не углубляться в подробности.

  • Поддержка Apache Foundation.
  • JExcel не поддерживает формат xlsx, тогда как POI поддерживает и форматы xls и xlsx.
  • Apache POI предоставляет потоковую обработку, которая подходит для работы с большими файлами и требует меньше памяти.

Apache POI

Apache POI отлично подходит для работы с документами Microsoft Excel. Apache POI способен обрабатывать форматы электронных таблиц XLS и XLSX. Некоторые важные моменты об API Apache POI:

  1. Apache POI содержит реализацию HSSF для формата файлов Excel ’97(-2007), то есть XLS.
  2. Для формата файла Excel 2007 OOXML (.xlsx) следует использовать реализацию Apache POI XSSF.
  3. API Apache POI HSSF и XSSF предоставляют механизмы для чтения, записи или изменения электронных таблиц Excel.
  4. Апачи POI также предоставляет API SXSSF, который является расширением XSSF и предназначен для работы с очень большими электронными таблицами. API SXSSF требует меньше памяти и подходит для работы с очень большими электронными таблицами, когда память кучи ограничена.
  5. Есть две модели на выбор – модель событий и модель пользователя. Модель событий требует меньше памяти, потому что файл Excel считывается токенами и требует их обработки. Модель пользователя более объектно-ориентирована, проста в использовании, и мы будем использовать ее в наших примерах.
  6. Apache POI отлично поддерживает дополнительные функции Excel, такие как работа с формулами, создание стилей ячеек с заполнением цветов и границ, шрифты, верхние и нижние колонтитулы, проверки данных, изображения, гиперссылки и т. д.

Зависимости Maven Apache POI

Если вы используете 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 – 3.10-FINAL. Если у вас есть автономное приложение Java, включите библиотеки из изображения ниже.

Пример Apache POI – Чтение файла Excel

Предположим, у нас есть файл Excel “Sample.xlsx” с двумя листами и данными, как на изображении ниже. Мы хотим прочитать файл Excel и создать список стран. На листе 1 есть дополнительные данные, которые мы проигнорируем при его разборе. Наш код бина Country на языке Java выглядит следующим образом: Country.java

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 readExcelData(String fileName) {
		List countriesList = new ArrayList();
		
		try {
			//Создать поток ввода из файла xlsx/xls
			FileInputStream fis = new FileInputStream(fileName);
			
			//Создать экземпляр Workbook для потока ввода файла xlsx/xls
			Workbook workbook = null;
			if(fileName.toLowerCase().endsWith("xlsx")){
				workbook = new XSSFWorkbook(fis);
			}else if(fileName.toLowerCase().endsWith("xls")){
				workbook = new HSSFWorkbook(fis);
			}
			
			//Получить количество листов в файле xlsx
			int numberOfSheets = workbook.getNumberOfSheets();
			
			//перебрать каждый из листов
			for(int i=0; i < numberOfSheets; i++){
				
				//Получить n-й лист из книги
				Sheet sheet = workbook.getSheetAt(i);
				
				//у каждого листа есть строки, переберите их
				Iterator rowIterator = sheet.iterator();
				while (rowIterator.hasNext()) 
		        {
					String name = "";
					String shortCode = "";
					
					//Получить объект строки
					Row row = rowIterator.next();
					
					//У каждой строки есть столбцы, получите итератор столбцов и переберите их
					Iterator cellIterator = row.cellIterator();
		             
		            while (cellIterator.hasNext()) 
		            {
		            	//Получить объект ячейки
		            	Cell cell = cellIterator.next();
		            	
		            	//проверить тип ячейки и обработать соответственно
		            	switch(cell.getCellType()){
		            	case Cell.CELL_TYPE_STRING:
		            		if(shortCode.equalsIgnoreCase("")){
		            			shortCode = cell.getStringCellValue().trim();
		            		}else if(name.equalsIgnoreCase("")){
		            			//2-й столбец
		            			name = cell.getStringCellValue().trim();
		            		}else{
		            			//случайные данные, оставьте их
		            			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
			
			//закрыть поток ввода файла
			fis.close();
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return countriesList;
	}

	public static void main(String args[]){
		List list = readExcelData("Sample.xlsx");
		System.out.println("Country List\n"+list);
	}

}

Программа очень легкая для понимания и содержит следующие шаги:

  1. Создайте экземпляр Workbook на основе типа файла. Используйте XSSFWorkbook для формата xlsx и HSSFWorkbook для формата xls. Обратите внимание, что можно было бы создать обёрточный класс с использованием шаблона фабрики для получения экземпляра книги на основе имени файла.
  2. Используйте метод workbook getNumberOfSheets(), чтобы получить количество листов, а затем используйте цикл for для обработки каждого листа. Получите экземпляр Sheet, используя метод getSheetAt(int i).
  3. Получите итератор Row, а затем итератор Cell, чтобы получить объект Cell. Здесь Apache POI использует шаблон итератора.
  4. Используйте 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 для записи в файл. Давайте рассмотрим простой пример использования Apache POI, где мы будем использовать список стран из вышеуказанного метода для сохранения в другой файл в одном листе. 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 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 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());
		}
		
		// давайте теперь записать данные Excel в файл
		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 list = ReadExcelFileToList.readExcelData("Sample.xlsx");
		WriteListToExcelFile.writeCountryListToFile("Countries.xls", list);
	}
}

При выполнении вышеуказанной программы примера Apache POI создается следующий файл Excel, похожий на изображение ниже.

Пример Apache POI – Чтение формулы Excel

Иногда нам нужно работать с сложными файлами Excel с формулами. Давайте рассмотрим простой пример использования Apache POI для чтения формулы ячейки со значением. ReadExcelFormula.java

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);
		
		// предполагая, что файл в формате xlsx
		Workbook workbook = new XSSFWorkbook(fis);
		Sheet sheet = workbook.getSheetAt(0);
		Iterator rowIterator = sheet.iterator();
		while (rowIterator.hasNext()) 
        {
			Row row = rowIterator.next();
			Iterator 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 с использованием формул с помощью API Apache POI. 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);
		// установим формульную ячейку
		row.createCell(3).setCellFormula("A1*B1*C1");
		
		// давайте запишем в файл
		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");
	}
}

Файл Excel, созданный с помощью приведенной выше программы примера API Apache POI, выглядит как на изображении ниже. Вот и все, что касается учебного пособия по Apache POI для работы с файлами Excel, изучите методы классов Apache POI, чтобы узнать больше функций. Ссылки: Руководство разработчика Apache POI

Source:
https://www.digitalocean.com/community/tutorials/apache-poi-tutorial