In the software industry, information needs to be portable and hence any valid data is available in XLS and XLSX formats i.e. excel formats. In order to communicate between excel formats and java, we need to use Apache POI and JExcel APIs. Let us see in this article how to use them via a sample maven project. Necessary dependencies to be used for using Apache POI and JExcel :
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.0</version>
</dependency>
Apache POI
It supports both XLS and XLSX formats.
- Workbook interface is used to model an excel file
- Shell, Row, and Cell interfaces are used to model elements of the excel file
- For older XLS formats, we need to use HSSFWorkbook, HSSFSheet, HSSFRow, and HSSFCell class
- For the latest XLSX formats, we need to use XSSFWorkbook, XSSFSheet, XSSFRow, and XSSFCell classes.
We can see the entire concept via a sample maven project.
Example Maven Project
Project Structure:
As this is the maven project, let us see the dependencies present in
pom.xml
XML
<?xml version="1.0" encoding="UTF-8"?> xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 <modelVersion>4.0.0</modelVersion> <artifactId>workingwithapache-poi</artifactId> <version>0.0.1-SNAPSHOT</version> <name>apache-poi</name> <parent> <groupId>com.gfg</groupId> <artifactId>parent-modules</artifactId> <version>1.0.0-SNAPSHOT</version> </parent> <dependencies> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls-jexcel</artifactId> <version>${jexcel.version}</version> <exclusions> <exclusion> <artifactId>commons-logging</artifactId> <groupId>commons-logging</groupId> </exclusion> </exclusions> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-resources-plugin</artifactId> <version>${maven.resources.plugin.version}</version> <configuration> <encoding>UTF-8</encoding> <nonFilteredFileExtensions> <nonFilteredFileExtension>xlsx</nonFilteredFileExtension> <nonFilteredFileExtension>xls</nonFilteredFileExtension> </nonFilteredFileExtensions> </configuration> </plugin> </plugins> </build> <properties> <poi.version>5.2.0</poi.version> <jexcel.version>1.0.6</jexcel.version> <maven.resources.plugin.version>3.2.0</maven.resources.plugin.version> </properties> </project> |
Excel mainly is used to store alpha/numeric/alphanumeric(string)/boolean values which means we should have separate mechanisms to read a different kinds of values. Via the below java files, we can view them
CellValueAndFormulaCalculationSampleHelper.java
Java
import java.io.File;import java.io.FileInputStream;import java.io.IOException; import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.FormulaEvaluator;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.ss.util.CellAddress;import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class CellValueAndFormulaCalculationSampleHelper { public Object retrieveCellValueByFetchingTheLastCachedValue(String fileLocation, String cellLocation) throws IOException { Object cellValue = new Object(); FileInputStream inputStream = new FileInputStream(new File(fileLocation)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet workSheet = workbook.getSheetAt(0); CellAddress cellAddress = new CellAddress(cellLocation); Row row = workSheet.getRow(cellAddress.getRow()); Cell cell = row.getCell(cellAddress.getColumn()); if (cell.getCellType() == CellType.FORMULA) { switch (cell.getCachedFormulaResultType()) { case BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case NUMERIC: cellValue = cell.getNumericCellValue(); break; case STRING: cellValue = cell.getStringCellValue(); break; default: cellValue = null; } } workbook.close(); return cellValue; } public Object retrieveCellValueByEvaluatingTheFormula(String fileLocation, String cellLocation) throws IOException { Object cellValue = new Object(); FileInputStream inputStream = new FileInputStream(new File(fileLocation)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet workSheet = workbook.getSheetAt(0); FormulaEvaluator evaluator = workbook.getCreationHelper() .createFormulaEvaluator(); CellAddress cellAddress = new CellAddress(cellLocation); Row row = workSheet.getRow(cellAddress.getRow()); Cell cell = row.getCell(cellAddress.getColumn()); if (cell.getCellType() == CellType.FORMULA) { switch (evaluator.evaluateFormulaCell(cell)) { case BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case NUMERIC: cellValue = cell.getNumericCellValue(); break; case STRING: cellValue = cell.getStringCellValue(); break; default: cellValue = null; } } workbook.close(); return cellValue; }} |
SampleExcelCellFormatter.java
Java
import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DataFormatter;import org.apache.poi.ss.usermodel.FormulaEvaluator;import org.apache.poi.ss.usermodel.Workbook; public class SampleExcelCellFormatter { public String getCellStringValue(Cell cell) { DataFormatter dataFormatter = new DataFormatter(); return dataFormatter.formatCellValue(cell); } public String getCellStringValueWithFormula(Cell cell, Workbook workbook) { DataFormatter dataFormatter = new DataFormatter(); FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); return dataFormatter.formatCellValue(cell, formulaEvaluator); }} |
JExcelSampleHelper.java
Java
import jxl.*;import java.util.Map;import java.util.HashMap;import java.util.ArrayList;import java.util.List;import jxl.read.biff.BiffException;import java.io.File;import java.io.IOException;import jxl.write.*;import jxl.write.Number;import jxl.format.Colour; public class JExcelSampleHelper { public Map<Integer, List<String>> readJExcelContents(String fileLocation) throws IOException, BiffException { Map<Integer, List<String>> dataMap = new HashMap<>(); Workbook workbook = Workbook.getWorkbook(new File(fileLocation)); Sheet workSheet = workbook.getSheet(0); int noOfRows = workSheet.getRows(); int noOfColumns = workSheet.getColumns(); for (int i = 0; i < noOfRows; i++) { dataMap.put(i, new ArrayList<String>()); for (int j = 0; j < noOfColumns; j++) { dataMap.get(i).add(workSheet.getCell(j, i).getContents()); } } return dataMap; } public void writeJExcelContents() throws IOException, WriteException { WritableWorkbook writableWorkBook = null; try { File currentDirectory = new File("."); String absolutePath = currentDirectory.getAbsolutePath(); String fileLocation = absolutePath.substring(0, absolutePath.length() - 1) + "temp.xls"; writableWorkBook = Workbook.createWorkbook(new File(fileLocation)); WritableSheet writableSheet = writableWorkBook.createSheet("Sheet 1", 0); WritableCellFormat headerFormat = new WritableCellFormat(); WritableFont font = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD); headerFormat.setFont(font); headerFormat.setBackground(Colour.LIGHT_BLUE); headerFormat.setWrap(true); Label headerLabel = new Label(0, 0, "GeekEmployeeName", headerFormat); writableSheet.setColumnView(0, 60); writableSheet.addCell(headerLabel); headerLabel = new Label(1, 0, "GeekEmployeeAge", headerFormat); writableSheet.setColumnView(0, 40); writableSheet.addCell(headerLabel); WritableCellFormat cellFormat = new WritableCellFormat(); cellFormat.setWrap(true); Label cellLabel = new Label(0, 2, "Rachel", cellFormat); writableSheet.addCell(cellLabel); Number cellNumber = new Number(1, 2, 30, cellFormat); writableSheet.addCell(cellNumber); writableWorkBook.write(); } finally { if (writableWorkBook != null) { writableWorkBook.close(); } } }} |
Now it is our job to test the same. Hence let us take some input files.
jexceltemp.xls contents
JExcelSampleIntegrationTest.java
Java
import static org.junit.Assert.assertEquals; import java.io.File;import java.io.IOException;import java.util.List;import java.util.Map; import org.junit.After;import org.junit.Before;import org.junit.Test; import jxl.read.biff.BiffException;import jxl.write.WriteException; public class JExcelSampleIntegrationTest { private JExcelSampleHelper jExcelSampleHelper; private static String FILE_NAME = "jexceltemp.xls"; private String fileLocation; @Before public void generationOfExcelFile() throws IOException, WriteException { File currentDirectory = new File("."); String absolutePath = currentDirectory.getAbsolutePath(); fileLocation = absolutePath.substring(0, absolutePath.length() - 1) + FILE_NAME; jExcelSampleHelper = new JExcelSampleHelper(); jExcelSampleHelper.writeJExcelContents(); } @Test public void checkingDataCorrectnessByParsing() throws IOException, BiffException { Map<Integer, List<String>> dataMap = jExcelSampleHelper.readJExcelContents(fileLocation); assertEquals("GeekEmployeeName", dataMap.get(0) .get(0)); assertEquals("GeekEmployeeAge", dataMap.get(0) .get(1)); assertEquals("Rachel", dataMap.get(2) .get(0)); assertEquals("30", dataMap.get(2) .get(1)); } @After public void cleanup(){ File testFile = new File(fileLocation); if (testFile.exists()) { testFile.delete(); } }} |
Output of JUnit:
ExcelCellFormatterSample.xlsx contents:
ExcelCellFormatterSampleUnitTest.java
Java
import static org.junit.Assert.assertEquals; import java.io.IOException;import java.net.URISyntaxException;import java.nio.file.Paths; 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;import org.junit.Before;import org.junit.Test; public class ExcelCellFormatterSampleUnitTest { private static final String EXCEL_FILE_NAME = "ExcelCellFormatterSample.xlsx"; private static final int STRING_CELL_INDEX = 0; private static final int BOOLEAN_CELL_INDEX = 1; private static final int RAW_NUMERIC_CELL_INDEX = 2; private static final int FORMATTED_NUMERIC_CELL_INDEX = 3; private static final int FORMULA_CELL_INDEX = 4; private String fileLocation; @Before public void setup() throws IOException, URISyntaxException { fileLocation = Paths.get(ClassLoader.getSystemResource(EXCEL_FILE_NAME).toURI()).toString(); } @Test public void checkingForStringValue() throws IOException { Workbook workbook = new XSSFWorkbook(fileLocation); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); SampleExcelCellFormatter formatter = new SampleExcelCellFormatter(); assertEquals("Working with Excel", formatter.getCellStringValue(row.getCell(STRING_CELL_INDEX))); workbook.close(); } @Test public void checkingForBooleanValue() throws IOException { Workbook workbook = new XSSFWorkbook(fileLocation); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); SampleExcelCellFormatter formatter = new SampleExcelCellFormatter(); assertEquals("TRUE", formatter.getCellStringValue(row.getCell(BOOLEAN_CELL_INDEX))); workbook.close(); } @Test public void checkingForNumericValue() throws IOException { Workbook workbook = new XSSFWorkbook(fileLocation); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); SampleExcelCellFormatter formatter = new SampleExcelCellFormatter(); assertEquals("100.234", formatter.getCellStringValue(row.getCell(RAW_NUMERIC_CELL_INDEX))); assertEquals("100.23", formatter.getCellStringValue(row.getCell(FORMATTED_NUMERIC_CELL_INDEX))); workbook.close(); } @Test public void checkingForCellContainingFormula() throws IOException { Workbook workbook = new XSSFWorkbook(fileLocation); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); SampleExcelCellFormatter formatter = new SampleExcelCellFormatter(); assertEquals("SUM(100+200)", formatter.getCellStringValue(row.getCell(FORMULA_CELL_INDEX))); workbook.close(); } @Test public void checkingForCellCalculatingFormula() throws IOException { Workbook workbook = new XSSFWorkbook(fileLocation); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); SampleExcelCellFormatter formatter = new SampleExcelCellFormatter(); assertEquals("300", formatter.getCellStringValueWithFormula(row.getCell(FORMULA_CELL_INDEX), workbook)); workbook.close(); } } |
Output of JUnit:
test.xlsx file contents:
CellValueAndFormulaCalculationUnitTest.java
Java
import static org.junit.jupiter.api.Assertions.assertEquals; import java.io.IOException;import java.net.URISyntaxException;import java.nio.file.Paths; import org.junit.Before;import org.junit.Test; public class CellValueAndFormulaCalculationUnitTest { private CellValueAndFormulaCalculationSampleHelper readCellValueAndNotFormulaHelper; private String fileLocation; private static final String FILE_NAME = "test.xlsx"; @Before public void setup() throws URISyntaxException { fileLocation = Paths.get(ClassLoader.getSystemResource(FILE_NAME).toURI()).toString(); readCellValueAndNotFormulaHelper = new CellValueAndFormulaCalculationSampleHelper(); } @Test public void givenExcelCell_whenReadCellValueByLastCachedValue_thenProduceCorrectResult() throws IOException { final double expectedResult = 700.0; final Object cellValue = readCellValueAndNotFormulaHelper.retrieveCellValueByFetchingTheLastCachedValue(fileLocation, "C2"); assertEquals(expectedResult, cellValue); } @Test public void givenExcelCell_whenReadCellValueByEvaluatingFormula_thenProduceCorrectResult() throws IOException { final double expectedResult = 6000.0; final Object cellValue = readCellValueAndNotFormulaHelper.retrieveCellValueByEvaluatingTheFormula(fileLocation, "C3"); assertEquals(expectedResult, cellValue); }} |
Output of JUnit:
Conclusion
Apache POI and JEXCEL API are very useful and we can easily read the contents of the excel file, and manipulate the same in different ways according to our requirements. As information needs to be portable, on many occasions we expect the file contents to be in xls or xlsx formats. That time we can prefer this approach.
