Pesquisa de site

Apache POI Tutorial

Bem-vindo ao Tutorial do Apache POI. Às vezes, precisamos ler dados de arquivos do Microsoft Excel ou gerar relatórios no formato Excel, principalmente para fins comerciais ou financeiros. O Java não fornece suporte integrado para trabalhar com arquivos do Excel, portanto, precisamos procurar APIs de código aberto para o trabalho. Quando comecei a busca por APIs Java para Excel, a maioria das pessoas recomendava JExcel ou Apache POI. Após mais pesquisas, descobri que o Apache POI é o caminho a seguir pelos seguintes motivos principais. Existem alguns outros motivos relacionados a recursos avançados, mas não vamos entrar em muitos detalhes.

  • Apoio da Fundação Apache.
  • O JExcel não oferece suporte ao formato xlsx, enquanto o POI oferece suporte aos formatos xls e xlsx.
  • Apache POI fornece processamento baseado em fluxo, adequado para arquivos grandes e requer menos memória.

Apache POI

O Apache POI fornece excelente suporte para trabalhar com documentos do Microsoft Excel. O Apache POI é capaz de lidar com os formatos XLS e XLSX de planilhas. Alguns pontos importantes sobre a API do Apache POI são:

  1. Apache POI contém implementação HSSF para o formato de arquivo Excel 97(-2007), ou seja, XLS.
  2. A implementação do Apache POI XSSF deve ser usada para o formato de arquivo Excel 2007 OOXML (.xlsx).
  3. Apache POI HSSF e XSSF API fornece mecanismos para ler, gravar ou modificar planilhas do Excel.
  4. O Apache POI também fornece a API SXSSF, que é uma extensão do XSSF para trabalhar com planilhas muito grandes do Excel. A API SXSSF requer menos memória e é adequada ao trabalhar com planilhas muito grandes e a memória heap é limitada.
  5. Existem dois modelos para escolher - modelo de evento e modelo de usuário. O modelo de evento requer menos memória porque o arquivo Excel é lido em tokens e requer processamento deles. O modelo do usuário é mais orientado a objetos e fácil de usar e usaremos isso em nossos exemplos.
  6. Apache POI fornece excelente suporte para recursos adicionais do Excel, como trabalhar com fórmulas, criar estilos de célula preenchendo cores e bordas, fontes, cabeçalhos e rodapés, validações de dados, imagens, hiperlinks, etc.

Dependências Apache POI Maven

Se você estiver usando o maven, adicione abaixo as dependências do 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>

Exemplo de Apache POI - Ler arquivo do 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;
	}
	
}

O programa de exemplo do Apache POI para ler o arquivo Excel na lista de países é semelhante ao abaixo. 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);
	}

}

O programa é muito fácil de entender e contém as seguintes etapas:

  1. Crie a instância Workbook com base no tipo de arquivo. XSSFWorkbook para o formato xlsx e HSSFWorkbook para o formato xls. Observe que poderíamos ter criado uma classe wrapper com padrão de fábrica para obter a instância da pasta de trabalho com base no nome do arquivo.
  2. Use a pasta de trabalho getNumberOfSheets() para obter o número de planilhas e, em seguida, use o loop for para analisar cada uma das planilhas. Obtenha a instância Sheet usando o método getSheetAt(int i).
  3. Obtenha o iterador Row e, em seguida, o iterador Cell para obter o objeto Cell. O Apache POI está usando o padrão do iterador aqui.
  4. Use a caixa de seleção para ler o tipo de célula e processá-la de acordo.

Agora, quando executamos o programa de exemplo Apache POI acima, ele produz a seguinte saída no console.

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]

Exemplo de Apache POI - Gravar arquivo do Excel

A gravação do arquivo Excel no apache POI é semelhante à leitura, exceto que aqui primeiro criamos a pasta de trabalho. Em seguida, defina os valores de planilhas, linhas e células e use FileOutputStream para gravá-lo no arquivo. Vamos escrever um exemplo simples de POI do apache, onde usaremos a lista de países do método acima para salvar em outro arquivo em uma única planilha. 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);
	}
}

Exemplo de Apache POI - Leia a fórmula do 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");
	}
}

Quando executamos o programa de exemplo apache poi acima, obtemos a seguinte saída.

1.0
2.0
3.0
4.0
Cell Formula=A1*A2*A3*A4
Cell Formula Result Type=0
Formula Value=24.0

Exemplo de Apache POI - Fórmula de gravação do Excel

Às vezes, precisamos fazer alguns cálculos e depois escrever os valores das células. Podemos usar as fórmulas do Excel para fazer esse cálculo e isso o tornará mais preciso porque os valores mudarão se os valores das células usadas nos cálculos forem alterados. Vamos ver um exemplo simples para escrever arquivo excel com fórmulas usando 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");
	}
}