前言
最近, 看了别人的博客, 说一个开发人员写了10年代码, 还不知道怎么去实现一个java处理excel文件的简单操作, WTFK! 我严重怀疑他在说我, 很明显我也不会, 所以你有福了, 和我一起走进POI的世界吧!
依赖
添加如下依赖:
<!--处理2007版本以下的excel, 后缀为.xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<!--处理2007版本以上的excel, 后缀为.xlsx-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
HSSF基本使用(用户模型)
新建student实体
package com.huntkey.rx.sceo.entity;
/**
* @description: Student实体
* @author: wangml
* @date: 2021/8/5 14:56
*/
public class Student {
/**
* 名称
*/
private String name;
/**
* 年龄
*/
private int age;
/**
* 年级
*/
private String grade;
public Student() {
}
public Student(String name, int age, String grade) {
this.name = name;
this.age = age;
this.grade = grade;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = grade;
}
@Override
public String toString() {
return "Student{" +
"name='" + name + '\'' +
", age=" + age +
", grade='" + grade + '\'' +
'}';
}
}
建立测试类
package com.huntkey.rx.sceo.test;
import com.huntkey.rx.sceo.entity.Student;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;
/**
* @description: Excel功能测试
* Excel和HSSFWorkBook:
* 一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（HSSFSheet）组成，
* 一个sheet是由多个row（HSSFRow）组成，一个row是由多个cell（HSSFCell）组成
*
* HSSFWorkbook excel的文档对象
* HSSFSheet excel的表单
* HSSFRow excel的行
* HSSFCell excel的格子单元
* HSSFFont excel字体
* HSSFDataFormat 日期格式
* HSSFHeader sheet头
* HSSFFooter sheet尾（只有打印的时候才能看到效果）
* 样式：
* HSSFCellStyle cell样式
* 辅助操作包括：
* HSSFDateUtil 日期
* HSSFPrintSetup 打印
* HSSFErrorConstants 错误信息表
*
* @author: wangml
* @date: 2021/8/5 15:01
*/
public class ExcelTest {
private static final Logger logger = LoggerFactory.getLogger(ExcelTest.class);
/**
* sheet头部信息
*/
private static String [] colNameArray = new String[]{"姓名", "年龄", "年级"};
/**
* 实体类和 excel表字段映射关系
*/
private static Map<String, String> relMap = new HashMap<String, String> () {{
put("name", "姓名");
put("age", "年龄");
put("grade", "年级");
}};
/**
* 列数
*/
private static int cols = 3;
/**
* sheet 表名
*/
private static String sheetName = "学生表一";
public static void main(String[] args) throws IOException {
List<Student> list = new ArrayList<Student>();
Collections.addAll(list, new Student("张三", 10, "三年级"),
new Student("李四", 14, "初二"),
new Student("王五", 18, "高三"),
new Student("赵六", 21, "大一"),
new Student("钱七", 40, "硕士")
);
// 写入Excel(xls)文件
writeToExcel(list);
// 读取Excel(xls)文件
// readExcel();
}
/**
* HSSF写入Excel(原来有内容就追加, 没有就新增)
*
* @param list
*/
public static void writeToExcel(List<Student> list) throws IOException {
// 判断是追加还是覆盖
Boolean flag = validateFileExistData();
// 创建HSSFWorkbook对象(Excel对象)
HSSFWorkbook workbook = null;
if (flag) {
workbook = new HSSFWorkbook();
// 创建一个工作表
HSSFSheet sheet = workbook.createSheet(sheetName);
// 设置数据校验规则, 保证后续输入的数据的正确性和读取文件的时候遇到同一列数据不同格式问题, 导致读取失败
DataValidation nameValidation = validateExcelData(sheet, "name");
DataValidation ageValidation = validateExcelData(sheet, "age");
DataValidation gradeValidation = validateExcelData(sheet, "grade");
sheet.addValidationData(nameValidation);
sheet.addValidationData(ageValidation);
sheet.addValidationData(gradeValidation);
//设置缺省列高
sheet.setDefaultRowHeightInPoints(20);
// sheet.setDefaultColumnWidth(20);//设置缺省列宽
//设置指定列的列宽，256 * 50这种写法是因为width参数单位是单个字符的256分之一
//第一个参数代表列id(从0开始),第2个参数代表宽度值 参考 ："2017-06-01"的宽度为2500
sheet.setColumnWidth(0, 256 * 20);
sheet.setColumnWidth(1, 256 * 10);
sheet.setColumnWidth(2, 256 * 20);
// 添加表头
HSSFRow headerRow = sheet.createRow(0);
// 表头样式(背景色)
HSSFCellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFillForegroundColor((short) 10);// 设置背景色
headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 边框
headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 字体
HSSFFont font = workbook.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 12); //字体大小
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //粗体显示
headerCellStyle.setFont(font);
// 单元格格式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 自动换行
cellStyle.setWrapText(true);
// 列数
for(int i = 0; i < cols; i++) {
HSSFCell headCell = headerRow.createCell(i);
headCell.setCellValue(colNameArray[i]);
headCell.setCellStyle(headerCellStyle);
}
createSheetData(list, cellStyle, sheet, 0);
logger.info("********覆盖原文件内容!***********");
} else {
try {
InputStream inputStream = new FileInputStream("D:/student.xls");
workbook = new HSSFWorkbook(inputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
// 获取工作表
HSSFSheet sheet = workbook.getSheet(sheetName);
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
createSheetData(list, cellStyle, sheet, sheet.getLastRowNum());
logger.info("#########追加文件内容!#############");
}
try {
OutputStream file = new FileOutputStream("D:/student.xls");
file.flush();
workbook.write(file);
file.close();
logger.info("写入文件成功!");
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* HSSF读取Excel(读取xls文件后缀的Excel)
*/
public static void readExcel() {
List<Student> list = new ArrayList<>(10);
HSSFWorkbook workbook = null;
try {
InputStream inputStream = new FileInputStream("D:/student.xls");
workbook = new HSSFWorkbook(inputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
HSSFSheet sheet = workbook.getSheet(sheetName);
HSSFRow headerRow = sheet.getRow(0);
// 获取列数
short lastCellNum = headerRow.getLastCellNum();
// 分析: 由于excel中列 和 java 属性的顺序可能不一致, 必须知道excel列对应java的属性和类型
// 第一步:获取Excel第一列开始每一列对应java中的数据类型 {name: java.lang.String, age: int, grade: java.lang.String}
Map<String, String> stuMap = reflectFiled(new Student());
logger.info("输出stuMap: {}", stuMap.toString());
if (CollectionUtils.isEmpty(stuMap)) {
logger.error("反射获取数据异常, 请联系开发人员确认!");
throw new RuntimeException("运行时异常, 类属性获取失败, 无法继续操作!");
}
// 获取每一列的java数据类型
String cellName;
String key;
Map<String, String> colTypeMap = new LinkedHashMap<>(lastCellNum);
for (int i = 0; i < lastCellNum; i++) {
cellName = headerRow.getCell(i).getStringCellValue();
key = getMapKeyByValue(cellName);
if (stuMap.containsKey(key) && !StringUtils.isEmpty(stuMap.get(key))) {
colTypeMap.put(key, stuMap.get(key));
} else {
logger.error("实体类和Excel映射异常, Excel中 {} 列, 没有属性对应, 请验证!", cellName);
throw new RuntimeException("运行时异常, 映射有误, 无法继续操作!");
}
}
logger.info("输出colTypeMap: {}", colTypeMap.toString());
// 第二步: 遍历excel除表头的行数据
Student student;
HSSFCell cell;
logger.info("最后一行的下标: {}", sheet.getLastRowNum());
for (int rowIndex = 1; rowIndex < sheet.getLastRowNum() + 1; rowIndex++) {
HSSFRow row = sheet.getRow(rowIndex);
student = new Student();
Object value = new Object();
for (int i = 0; i < lastCellNum; i++) {
cell = row.getCell(i);
String fieldName = getMapKeyByValue(headerRow.getCell(i).getStringCellValue());
String fieldType = colTypeMap.get(fieldName);
switch (fieldType) {
case "java.lang.String":
value = cell.getStringCellValue();
break;
case "int":
value = cell.getNumericCellValue();
break;
default:
}
setStudentStringFiled(student, fieldName, fieldType, value);
}
logger.info("反射构建的第 {} 个学生实体数据: {}", rowIndex, student.toString());
list.add(student);
}
logger.info("----------------读取excel文件输出结果---------");
logger.info("结果: {}", list.toString());
}
/**
* 判断写入数据类型追加还是覆盖(默认覆盖)
*
* @return
*/
public static Boolean validateFileExistData() throws IOException {
// 第一步: 校验文件存不存在, 不存在则新增文件, 直接返回true(默认覆盖)
File file = new File("D:/student.xls");
if (!file.exists()) {
file.createNewFile();
logger.info("该路径下文件不存在!");
return true;
}
// 第二步: 文件存在时, 读取文件, 判断表名(sheet)是否存在, 不存在的话, 直接返回true(默认覆盖)
HSSFWorkbook workbook = null;
try {
InputStream inputStream = new FileInputStream("D:/student.xls");
workbook = new HSSFWorkbook(inputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
HSSFSheet sheet = workbook.getSheet(sheetName);
if (null == sheet) {
logger.info("xls文件中不存在 表名为: {} 的表结构!", sheetName);
return true;
}
// 第三步: 表名存在时, 判断数据行是否 >= 2(至少有一条数据, 除表头外), 若 数据行 < 2(只有表头, 或者表头都没有), 直接返回true(默认覆盖)
int rowNum = sheet.getLastRowNum();
if (rowNum < 2) {
logger.info("xls文件中表名为: {} 的表没有有效数据!", sheetName);
return true;
}
// 第四步: 数据行 >= 2时, 返回 false(追加内容)
return false;
}
/**
* 构建EXCEL文件内容
*
* @param list
* @param cellStyle
* @param sheet
* @param startRow
*/
private static void createSheetData(List<Student> list, HSSFCellStyle cellStyle, HSSFSheet sheet, int startRow) {
HSSFRow row;
HSSFCell cell;
Student student;
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((startRow + 1) + i);
student = list.get(i);
for(int j = 0; j < cols; j++) {
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
switch (j) {
case 0 :
cell.setCellValue(student.getName());
cell.setCellType(1); // 好像没有用, 还是常规类型
break;
case 1 :
cell.setCellValue(student.getAge());
cell.setCellType(0);
break;
default:
cell.setCellValue(student.getGrade());
cell.setCellType(1);
}
}
}
}
/**
* 生成excel时的数据校验规则
*
* @param sheet 表格
* @param fieldName 校验的字段
* @return 校验规则
*/
public static DataValidation validateExcelData(HSSFSheet sheet, String fieldName) {
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
DataValidation validation = null;
switch (fieldName) {
case "name": {
// name 列 文本类型 介于 2 - 10 之间
DataValidationConstraint nameConstraint = dvHelper.createTextLengthConstraint(0, "2", "10");
// 校验范围
CellRangeAddressList addressList = new CellRangeAddressList(1, 10000, 0, 0);
validation = dvHelper.createValidation(nameConstraint, addressList);
validation.createErrorBox("Error", "name not in range, max is 5, min is 2!");
// STOP级别错误(数据校验失败不允许保存)
validation.setShowErrorBox(true);
validation.setEmptyCellAllowed(false);
validation.setSuppressDropDownArrow(true);
validation.setShowPromptBox(true);
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
break;
}
case "age": {
// age 列 整数类型 介于 1 -200 之间
DataValidationConstraint ageConstraint = dvHelper.createIntegerConstraint(0, "1", "200");
CellRangeAddressList addressList = new CellRangeAddressList(1, 10000, 1, 1);
validation = dvHelper.createValidation(ageConstraint, addressList);
validation.createErrorBox("Error", "age not in range, max is 200, min is 1!");
validation.setShowErrorBox(true);
validation.setEmptyCellAllowed(false);
validation.setSuppressDropDownArrow(true);
validation.setShowPromptBox(true);
// Waring级别(警告: 还是可以继续去执行非法输入, 不符合要求)
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
break;
}
case "grade": {
// grade 列 文本类型 大于等于 2
DataValidationConstraint gradeConstraint = dvHelper.createTextLengthConstraint(4, "2", "10");
CellRangeAddressList addressList = new CellRangeAddressList(1, 10000, 2, 2);
validation = dvHelper.createValidation(gradeConstraint, addressList);
validation.createErrorBox("Error", "grade size max >= 2");
validation.setShowErrorBox(true);
validation.setEmptyCellAllowed(false);
validation.setSuppressDropDownArrow(true);
validation.setShowPromptBox(true);
// INFO级别(提示: 对单元格进行保存时就会弹出提示, 而且可以执行非法操作, 不符合要求)
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
break;
}
default: logger.info("暂时未添加其他验证规则!");
}
return validation;
}
/**
* Student 赋值
*
* @param student
* @param fieldName
* @param fieldType
* @param value
*/
public static void setStudentStringFiled(Student student, String fieldName, String fieldType, Object value) {
Class<? extends Student> aClass = student.getClass();
char[] chars = fieldName.trim().toCharArray();
if (chars[0] >= 97 && chars[0] <= 123) {
chars[0] -= 32;
}
String methodName = "set" + String.valueOf(chars);
logger.info("方法名称, {}", methodName);
try {
Method declaredMethod;
if ("java.lang.String".equals(fieldType)) {
declaredMethod = aClass.getDeclaredMethod(methodName, String.class);
declaredMethod.invoke(student, (String)value);
} else if ("int".equals(fieldType)) {
declaredMethod = aClass.getDeclaredMethod(methodName, int.class);
declaredMethod.invoke(student, ((Double)value).intValue());
} else {
logger.info("暂不处理其他类型的.............");
}
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
/**
* 获取map中key
*
* @param value 值
* @return key 键
*/
public static String getMapKeyByValue(String value) {
if (!CollectionUtils.isEmpty(relMap)) {
Iterator<Map.Entry<String, String>> iterator = relMap.entrySet().iterator();
while (iterator.hasNext()) {
Map.Entry<String, String> next = iterator.next();
if (value.equals(next.getValue())) {
return next.getKey();
}
}
}
logger.info("数据异常 ---- 请查看!");
return "";
}
/**
* 反射获取实体数据
*
* @param obj 实体
* @return Map<K, V> K: 属性名, V: 属性类型
*/
public static Map<String, String> reflectFiled (Object obj) {
Class<?> myClass = obj.getClass();
Field[] fields = myClass.getDeclaredFields();
// 属性类型, 属性名
String type;
String name;
// Map<K, V> K: 属性名, V: 属性值
Map<String, String> stuMap = new LinkedHashMap<> ();
for (Field field : fields) {
type = field.getType().getName();
name = field.getName();
if (!StringUtils.isEmpty(name)) {
stuMap.put(name, type);
}
}
return stuMap;
}
}
相信看到上面的代码你一定已经开始不耐烦了, 已经打算放弃治疗了, 其实读写excel的功能根本不需要这么多代码去实现, 只是笔者为了巩固下 反射的使用而多加了一些料而已. 冷静一下, 你还可以继续!
调用writeToExcel结果如下:
再调用一次writeToExcel方法:
09:13:39.677 [main] INFO com.huntkey.rx.sceo.test.ExcelTest - #########追加文件内容!#############
09:13:39.739 [main] INFO com.huntkey.rx.sceo.test.ExcelTest - 写入文件成功!
当修改或新增单元格数据时, 会触发单元格数据有效性校验
是不是很强大! 其实我在写入excel的时候做了很多操作
1. 如果Excel中有内容, 就在Excel 内容后面追加新的内容, 没有就覆盖(默认就是覆盖)
2. 表头字体, 边框, 背景颜色, 单元格的默认行高, 列宽等等
3. 表格中添加了数据校验功能, 校验除了表头外的所有列的输入, 防止用户对内容胡乱写入.
调用readToexcel结果如下:
09:12:32.556 [main] INFO com.huntkey.rx.sceo.test.ExcelTest - ----------------读取excel文件输出结果---------
09:12:32.556 [main] INFO com.huntkey.rx.sceo.test.ExcelTest - 结果: [Student{name='张三', age=10, grade='三年级'}, Student{name='李四', age=14, grade='初二'}, Student{name='王五', age=18, grade='高三'}, Student{name='赵六', age=21, grade='大一'}, Student{name='钱七', age=40, grade='硕士'}]
其中我做了很多的处理, 还通过反射去给属性赋值. 这样处理是因为在读入的时候我们根本不知道 EXCEL中的列 和 java 属性之间的 映射关系. 我的处理方式也有很大问题, 属性赋值方法调用次数太频繁了, 数据量大的情况下就不适用了, 所以这只是我测试POI使用的例子.
这个时候, 我想到了那xlsx 文件我又要怎么处理呢? 难道再加一套处理逻辑么? 这时候就可以用WorkBook实现了,
Workbook 实现POI (用户模型)
public static void main(String[] args) throws IOException {
List<Student> list = new ArrayList<Student>();
Collections.addAll(list, new Student("张三", 10, "三年级"),
new Student("李四", 14, "初二"),
new Student("王五", 18, "高三"),
new Student("赵六", 21, "大一"),
new Student("钱七", 40, "硕士")
);
// 写入Excel(xls)文件
// writeToExcel(list);
// 读取Excel(xls)文件
// readExcel();
// 读取Excel(xls. xlsx)文件
InputStream fileXls = new FileInputStream("D:/student.xls");
InputStream fileXlsx = new FileInputStream("D:/student.xlsx");
readExcelCommon(fileXls);
readExcelCommon(fileXlsx);
// 写入Excel(xls, xlsx)文件
writeToExcelCommon(list, "D:/student.xls");
}
/**
* 读取excel文件(xls, xlsx后缀的文件)
*/
public static void readExcelCommon(InputStream inputStream) {
List<Student> list = new ArrayList<>(10);
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(inputStream);
} catch (InvalidFormatException | IOException e) {
e.printStackTrace();
}
Sheet sheet = workbook.getSheet(sheetName);
Student student;
for(Row row : sheet) {
student = new Student();
if (row.getRowNum() == 0) {
continue;
}
String cell1Value = row.getCell(0).getStringCellValue();
Double cell2Value = row.getCell(1).getNumericCellValue();
String cell3Value = row.getCell(2).getStringCellValue();
student.setName(cell1Value);
student.setAge(cell2Value.intValue());
student.setGrade(cell3Value);
list.add(student);
}
logger.info("读取成功!, xls, xlsx的Excel表得到的数据, {}", list.toString());
}
/**
* 写入excel(.xls, .xlsx)
*
* @param list
*/
public static void writeToExcelCommon(List<Student> list, String filePath) {
// 在内存中创建WorkBook
Workbook workbook = null;
if (filePath.endsWith(".xls")) {
workbook = new HSSFWorkbook();
} else if (filePath.endsWith(".xlsx")) {
workbook = new XSSFWorkbook();
}
Sheet sheet = workbook.createSheet(sheetName);
//标题行
Row titleRow=sheet.createRow(0);
titleRow.createCell(0).setCellValue("姓名");
titleRow.createCell(1).setCellValue("年龄");
titleRow.createCell(2).setCellValue("年级");
if (!CollectionUtils.isEmpty(list)) {
Student student;
Row row;
for (int i = 0; i < list.size(); i++) {
student = list.get(i);
row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(student.getName());
row.createCell(1).setCellValue(student.getAge());
row.createCell(2).setCellValue(student.getGrade());
}
}
OutputStream outputStream = null;
try {
outputStream = new FileOutputStream(filePath);
workbook.write(outputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
logger.info("写入成功! xls, xlsx文件操作成功!");
}
遗留问题: excel列和java类的映射关系是怎么实现的呢?
反射+自定义注解—实现Excel数据列属性和JavaBean属性的自动映射
总结
- POI能实现对exce不同文件格式的读写操作, 提供的基础操作类很多, 可以自定义的去处理, 功能丰富, 但是上手比较难, 因为东西太多了, 适用于需要对excel做复杂处理的功能
- 处理功能简单, 只需简单读写excel的情况, 推荐使用其他工具 比如阿里easyExcel
4798
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
