1. 背景
项目中使用到了导入导出的功能所以自己写了一个关于POI的工具类和demo,希望能帮到会使用到的朋友。对比了很多市面上的方法,最后还是选择了easyExcel,也把easyExcel的demo也写了,文章底部会有相关跳转连接,跳转到easyExcel的。
2.代码
2.1. maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
2.2 工具类
package com.tencent.hr.corehr.utils;
import cn.hutool.core.bean.BeanDesc;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.TypeUtil;
import com.tencent.hr.corehr.dto.ExcelRecord;
import com.tencent.hr.corehr.dto.ExcelResult;
import com.tencent.hr.corehr.rest.PositionController;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.locks.ReentrantLock;
/**
* @Author: v_qiicai
* @date 2020/3/6
*/
public class POIUtils {
private static final Logger logger = LoggerFactory.getLogger(PositionController.class);
/**
* logger
*/
private static final Logger LOGGER = LoggerFactory.getLogger(POIUtils.class);
private static ThreadLocal<List<RectangleArea>> mergeAreaList = new ThreadLocal<>();
private static List<RectangleArea> getMergeAreaList() {
return mergeAreaList.get();
}
private static void setMergeAreaList(List<RectangleArea> list) {
mergeAreaList.set(list);
}
/**
* 将单元格内容转换为字符串
*
* @param cell
* @return
*/
private static String convertCellValueToString(Cell cell) {
if (cell == null) {
return null;
}
String returnValue = null;
switch (cell.getCellType()) {
//数字
case NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
Date tempValue = cell.getDateCellValue();
SimpleDateFormat simpleFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
returnValue = simpleFormat.format(tempValue);
} else {
Double doubleValue = cell.getNumericCellValue();
// 格式化科学计数法,取一位整数
DecimalFormat df = new DecimalFormat("0");
returnValue = df.format(doubleValue);
}
break;
//字符串
case STRING:
returnValue = cell.getStringCellValue();
//布尔
break;
case BOOLEAN:
Boolean booleanValue = cell.getBooleanCellValue();
returnValue = booleanValue.toString();
break;
// 空值
case BLANK:
break;
// 公式
case FORMULA:
returnValue = cell.getCellFormula();
break;
// 故障
case ERROR:
break;
default:
break;
}
return returnValue;
}
/**
* 读取excel
* 注意: 默认读取的数据是在第一个sheet中的,暂不支持多个sheet的读取
*
* @param file 输入流
* @param headRowNum 表头所在行数
* @return 返回excel中的字符串数据
* @throws Exception
*/
private static List<List<String>> readFile(File file, int headRowNum) throws Exception {
InputStream ins = new FileInputStream(file);
Workbook workbook = WorkbookFactory.create(ins);
Sheet sheet = workbook.getSheetAt(0);
// 获取excel总行数
int rownum = sheet.getLastRowNum();
List<List<String>> result = new ArrayList<>();
LOGGER.info("excel 总行数:{}", rownum);
// 获取表头那一行的数据长度
short allColumnNum = sheet.getRow(headRowNum).getLastCellNum();
for (int i = 0; i <= rownum; i++) {
Row row = sheet.getRow(i);
if (row == null || row.getPhysicalNumberOfCells() == 0) {
LOGGER.info("第{}行数据为空.", i);
continue;
}