POI为“Poor Obfuscation Implementation”的首字母缩写。Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能。一般对Excel表格进行操作。
- pom文件导入poi所需要的依赖
<dependencies>
<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>
</dependencies>
- 准备表格中的数据,先创建一个Student类,属性有name,age,grade。(自己发挥)
private String name;
private int age;
private String grade;
// 有参
// 无参
// getter和setter方法
- 现在开始编写后端代码
import org.apache.poi.hssf.usermodel.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
public class TestPOI2 {
public static void main(String[] args) {
createExcel(getStudent());
List<Student> student = readExcel();
System.out.println(student.size());
System.out.println(student.toString());
}
private static List<Student> getStudent() {
List<Student> list = new ArrayList<>();
Student student1 = new Student("小明", 18, "二年级");
Student student2 = new Student("小光", 19, "三年级");
Student student3 = new Student("小花", 10, "四年级");
list.add(student1);
list.add(student2);
list.add(student3);
return list;
}
/*创建Excel表格*/
public static void createExcel(List<Student> list) {
//1.创建一个excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
//2.创建一个工作表
HSSFSheet sheet = workbook.createSheet("学生表一");
//3.添加表头
HSSFRow hssfRow = sheet.createRow(0);//从0开始,是第一行
//4.设置单元格居中
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//5.添加表头信息
HSSFCell headCell = hssfRow.createCell(0);//从0开始,是第一行
headCell.setCellValue("姓名");
//设置单元格样式
headCell.setCellStyle(cellStyle);
headCell = hssfRow.createCell(1);//第二行
headCell.setCellValue("年龄");
//设置单元格样式
headCell.setCellStyle(cellStyle);
headCell = hssfRow.createCell(2);//从0开始,是第三行
headCell.setCellValue("年级");
//设置单元格样式
headCell.setCellStyle(cellStyle);
//6.添加数据内容
for (int i = 0; i < list.size(); i++) {
hssfRow = sheet.createRow(i + 1);
Student student = list.get(i);
// 创建单元格
HSSFCell cell = hssfRow.createCell(0);
cell.setCellValue(student.getName());
cell.setCellStyle(cellStyle);
cell = hssfRow.createCell(1);
cell.setCellValue(student.getAge());
cell.setCellStyle(cellStyle);
cell = hssfRow.createCell(2);
cell.setCellValue(student.getGrade());
cell.setCellStyle(cellStyle);
//7.保存Excel文件到目标路径
try {
FileOutputStream fileOutputStream = new FileOutputStream("D:\\student.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/*
读取Excel表格中的文件
*/
public static List<Student> readExcel() {
List<Student> list = new ArrayList<>();
HSSFWorkbook workbook = null;
try {
FileInputStream fileInputStream = new FileInputStream("E:\\student.xls");
workbook = new HSSFWorkbook(fileInputStream);
fileInputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
//循环工作表
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
HSSFSheet hssfSheet = workbook.getSheetAt(i);
if (hssfSheet == null) {
continue;
}
//循环行
for (int j = 1; j <= hssfSheet.getLastRowNum(); j++) {
HSSFRow row = hssfSheet.getRow(j);
if (row == null) {
continue;
}
//将单元格中的数据存放到list集合中
Student student = new Student();
HSSFCell cell = row.getCell(0);
if (cell == null) {
continue;
}
student.setName(cell.getStringCellValue());
cell = row.getCell(1);
if (cell == null) {
continue;
}
student.setAge((int) cell.getNumericCellValue());
cell = row.getCell(2);
if (cell == null) {
continue;
}
student.setGrade(cell.getStringCellValue());
list.add(student);
}
}
return list;
}
}