With this example we will show you how to create an excel file using Apache POI in Java language.
The Apache POI is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). You can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is the Java Excel solution (for Excel 97-2008).
For more information please go through https://poi.apache.org/
If you already have an idea on how to create a maven project in Eclipse will be great otherwise I will tell you here how to create a maven project in Eclipse.
Prerequisites
The following things are required in order to run the application
Eclipse Kepler
JDK 1.8
Have maven 3 installed and configured
Apache POI dependencies in pom.xml
Now we will see the below steps how to create a maven based spring project in Eclipse
Step 1. Create a standalone maven project in Eclipse
Go to File -> New -> Other. On popup window under Maven select Maven Project. Then click on Next. Select the workspace location – either default or browse the location. Click on Next. Now in next window select the row as highlighted from the below list of archtypes and click on Next button.
maven-arctype-quickstart
Now enter the required fields (Group Id, Artifact Id) as shown below
Group Id : com.roytuts
Artifact Id : apache-poi
Step 2. Modify the pom.xml file as shown below.
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.roytuts</groupId>
<artifactId>apache-poi</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>apache-poi</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<poi.version>3.12</poi.version>
<junit.version>4.11</junit.version>
</properties>
<dependencies>
<!-- apache poi for xls, doc etc reading/writing -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<!-- apache poi for xlsx, docx etc reading/writing -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>${junit.version}</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>Step 3. If you see JRE System Library[J2SE-1.5] then change the version by below process
Do right-click on the project and go to Build -> Configure build path, under Libraries tab click on JRE System Library[J2SE-1.5], click on Edit button and select the appropriate jdk 1.8 from the next window. Click on Finish then Ok.
Step 4. Create a class for creating and writing to the excel file
package com.roytuts.poi.excel;
import static com.roytuts.common.PoiConstants.EXCEL_EXT_XLS;
import static com.roytuts.common.PoiConstants.EXCEL_EXT_XLSX;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
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 com.roytuts.common.PoiUtils;
public class Create {
public static void main(String[] args) {
final String fileName = "workbook.xlsx";
createExcel(fileName);
}
public static void createExcel(final String fileName) {
// get the file extension
String fileExt = PoiUtils.getFileExtension(fileName);
Workbook workbook = null;
// based on file extension create Workbook object
if (EXCEL_EXT_XLS.equalsIgnoreCase(fileExt)) {
workbook = PoiUtils.getHSSFWorkbook();
} else if (EXCEL_EXT_XLSX.equalsIgnoreCase(fileExt)) {
workbook = PoiUtils.getXSSFWorkbook();
}
// create Sheet object
// sheet name must not exceed 31 characters
// the name must not contain 0x0000, 0x0003, colon(:), backslash(\),
// asterisk(*), question mark(?), forward slash(/), opening square
// bracket([), closing square bracket(])
Sheet sheet = workbook.createSheet("my_sheet");
// Create first row. Rows are 0 based.
Row row = sheet.createRow((short) 0);
// Create a cell
Cell cell = row.createCell(0);
// put a value in cell.
// set integer
cell.setCellValue(10);
FileOutputStream fileOut = null;
try {
fileOut = new FileOutputStream(fileName);
workbook.write(fileOut);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}Step 5. Create a utility class
package com.roytuts.common;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public final class PoiUtils {
private PoiUtils() {
}
public static String getFileExtension(final String fileName) {
if (fileName != null) {
int len = fileName.trim().lastIndexOf(".");
String ext = fileName.trim().substring(len);
return ext;
}
return "";
}
public static Workbook getHSSFWorkbook() {
return new HSSFWorkbook();
}
public static Workbook getXSSFWorkbook() {
return new XSSFWorkbook();
}
}Step 6. Create a class which holds constants
package com.roytuts.common;
public final class PoiConstants {
private PoiConstants() {
}
public static final String EXCEL_EXT_XLS = ".xls";
public static final String EXCEL_EXT_XLSX = ".xlsx";
}Step 7. Run the above test class, you will get workbook.xlsx file created. Open the file you will get the output in sheet “my_sheet”.
That’s all. Thanks for reading.