1. What is POI
Apache POI - the Java API for Microsoft Documents. As the name suggests, Apache's third-party package is used to operate Microsoft office documents, and most of the time it is used to operate excel. Therefore, it will be explained here in terms of Excel.
Two packages need to be introduced, maven The address is as follows (version 3.9):
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
In the component list of POI, HSSF and XSSF components are mainly aimed at Excel. The former is aimed at 97-2007 general version excel, i.e. suffix xls; The latter is for Excel 2007 or later, i.e. the suffix xlsx.
The official summary is as follows:
HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
2. POI core class
Object oriented, object-oriented, in that case, it's natural to find some classes that can represent the content in excel.
2.1 Workbook
Create or maintain the hyperinterface of all classes of Excel Workbook, Workbook, which belongs to org apache. poi. ss. Usermodel package.
There are two implementation classes:
- HSSFWorkbook: read xls format and method of writing to Microsoft Excel file. It is compatible with Microsoft Office 97-2003
- Xssf Workbook: it has the format of reading and writing XML files of Microsoft Excel and OpenOffice xls or xlsx method. It is compatible with MS office version 2007 or later
Therefore, different workbooks should be used for different versions of excel. Constructor, commonly used:
HSSFWorkbook
HSSFWorkbook() HSSFWorkbook(java.io.InputStream s)
XSSFWorkbook
XSSFWorkbook() XSSFWorkbook(java.io.File file) XSSFWorkbook(java.io.InputStream is)
2.2 tab Sheet
HSSFSheet and XSSFSheet are implementation classes of Sheet interface. Sheets can be obtained by using two methods of Workbook:
workbook.createSheet(); workbook.createSheet(String sheetName);
2.3 Row
Similarly, Row is the interface between HSSFRow and XSSFRow. It is obtained through the Sheet:
sheet.createRow(int rownum);
2.4 Cell
Similarly, Cell is the interface between HSSFCell and XSSFCell. It is obtained through Row:
row.createCell(int column); row.createCell(int column, int type);
3. Create and read
In fact, if you can understand object-oriented, it is very simple. In addition, there are corresponding encapsulation classes, including fonts, formulas, hyperlinks, etc. only a few core classes are proposed here. You need to know more and expand them yourself.
For example, you can directly pick it from other people's tutorials. In addition, you can debug and view the contents of the workbook you read. Pay attention to the public slave Java technology stack, reply: tools, you can get more dry goods of tool series.
3.1 create a blank Workbook
import java.io.*;
import org.apache.poi.xssf.usermodel.*;
public class CreateWorkBook
{
public static void main(String[] args)throws Exception
{
XSSFWorkbook workbook = new XSSFWorkbook();
FileOutputStream out = new FileOutputStream(
new File("createworkbook.xlsx"));
workbook.write(out);
out.close();
System.out.println("
createworkbook.xlsx written successfully");
}
}
3.2 open an existing Workbook
import java.io.*;
import org.apache.poi.xssf.usermodel.*;
public class OpenWorkBook
{
public static void main(String args[])throws Exception
{
File file = new File("openworkbook.xlsx");
FileInputStream fIP = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(fIP);
if(file.isFile() && file.exists())
{
System.out.println(
"openworkbook.xlsx file open successfully.");
}
else
{
System.out.println(
"Error to open openworkbook.xlsx file.");
}
}
}
3.3. Transfer any object List to Excel document
The tag name and column name can be defined with annotation. A method is written to convert the List of a class into the corresponding Excel document. If the annotation is not used, the column name defaults to the attribute name:
Class:
@Excel(name = "Student tab")
public class Student {
@Excel(name = "full name")
private String name;
private boolean male;
@Excel(name = "height")
private int height;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public boolean isMale() {
return male;
}
public void setMale(boolean male) {
this.male = male;
}
public int getHeight() {
return height;
}
public void setHeight(int height) {
this.height = height;
}
}
Test method:
public static void main(String[] args) {
List<Student> list = new ArrayList<Student>();
Student student1 = new Student();
student1.setName("Xiao Hong");
student1.setMale(false);
student1.setHeight(167);
Student student2 = new Student();
student2.setName("Xiao Ming");
student2.setMale(true);
student2.setHeight(185);
list.add(student1);
list.add(student2);
File file = new File("C:/Users/Dulk/Desktop/1314.xls");
createExcel(list, file);
}
Output results:
Notes:
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUNTIME)
public @interface Excel {
public String name() default "";
}
method:
import org.apache.log4j.Logger;
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;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtil {
private static Logger log = Logger.getLogger(ExcelUtil.class);
public static Workbook gainWorkbook(File file) throws ExcelException {
if (!isExcel(file)) {
throw new ExcelException("File is not Excel type");
}
if (!file.exists()) {
try {
OutputStream os = new FileOutputStream(file);
Workbook workbook = isOlderEdition(file) ? new HSSFWorkbook() : new XSSFWorkbook();
workbook.write(os);
log.debug("The file does not exist. Create a new one Excel file");
os.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
try {
InputStream is = new FileInputStream(file);
return isOlderEdition(file) ? new HSSFWorkbook(is) : new XSSFWorkbook(is);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
private static boolean isOlderEdition(File file) {
return file.getName().matches(".+\\.(?i)xls");
}
private static boolean isExcel(File file) {
String fileName = file.getName();
String regXls = ".+\\.(?i)xls";
String regXlsx = ".+\\.(?i)xlsx";
return fileName.matches(regXls) || fileName.matches(regXlsx);
}
public static <E> Workbook createExcel(List<E> list, File file) {
String sheetName = "default";
if (list.size() == 0) {
return null;
}
Workbook workbook = null;
try {
Class clazz = list.get(0).getClass();
Field[] fields = clazz.getDeclaredFields();
if (clazz.isAnnotationPresent(Excel.class)) {
Excel excel = (Excel) clazz.getAnnotation(Excel.class);
sheetName = excel.name();
}
workbook = gainWorkbook(file);
Sheet sheet = workbook.createSheet(sheetName);
Row line = sheet.createRow(0);
for (int k = 0; k < fields.length; k++) {
Cell cell = line.createCell(k);
String columnName = fields[k].getName();
if (fields[k].isAnnotationPresent(Excel.class)) {
Excel excel = fields[k].getAnnotation(Excel.class);
columnName = excel.name();
}
cell.setCellValue(columnName);
}
for (int i = 1; i <= list.size(); i++) {
Row row = sheet.createRow(i);
for (int j = 1; j <= fields.length; j++) {
Cell cell = row.createCell(j - 1);
String fieldName = fields[j - 1].getName();
String fieldFirstLetterUpper = fieldName.substring(0, 1).toUpperCase();
String prefix = "get";
if ("boolean".equals(fields[j - 1].getType().getName())) {
prefix = "is";
}
String methodName = prefix + fieldFirstLetterUpper + fieldName.substring(1);
Method method = clazz.getMethod(methodName);
cell.setCellValue(String.valueOf(method.invoke(list.get(i - 1))));
}
}
log.debug("List Read in complete");
OutputStream os = new FileOutputStream(file);
workbook.write(os);
os.close();
} catch (ExcelException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return workbook;
}
}