poi版本：
<poi.version>4.1.1</poi.version>
工具类：
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
/**
* <poi.version>4.1.1</poi.version>
* Excel导入导出List＜Map＜String, Object＞＞工具类
*/
@Slf4j
public class ExcelUtil {
static DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
public static void main(String[] args) throws Exception {
//导出测试
List<Map<String, Object>> list = new ArrayList<>();
for (int i = 0; i < 3; i++) {
Map<String, Object> map = new HashMap<>();
map.put("姓名", i);
map.put("年龄", i);
map.put("性别", i);
list.add(map);
}
HSSFWorkbook workbook = ExcelUtil.createExcel(list);
File file = new File("C:\\Users\\***\\Desktop\\a.xls");
workbook.write(file);
workbook.close();
System.out.println("执行完成");
//导入测试
list = importExcel(file, "sheet1");
log.info(list.toString());
}
/**
* 将List＜Map＜String, Object＞＞转为 HSSFWorkbook
*
* @param list
* @return
*/
public static HSSFWorkbook createExcel(List<Map<String, Object>> list) {
//定义一个新的工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//创建Excel的sheet
HSSFSheet sheet = workbook.createSheet("sheet1");
//设置表头
Map<String, Object> map = list.get(0);
int num = 0;
HSSFRow first = sheet.createRow(0);//创建sheet的第一行
for (String key : map.keySet()) {
//创建单元格并赋值
first.createCell(num).setCellValue(key);
num++;
}
//从list取第一行到最后一行的内容并放到对应的Excel里，若记录里某字段值没有会有问题
int rowNum = 1;//行数
for (Map<String, Object> data : list) {
HSSFRow row = sheet.createRow(rowNum);//创建sheet的第rownum+1行
int n = 0;//列数
for (String key : data.keySet()) {
row.createCell(n).setCellValue(data.get(key) != null ? data.get(key).toString() : "");
n++;
}
rowNum++;
}
return workbook;
}
public static OutputStream getOutputStream(HttpServletResponse response, String fileName) throws IOException {
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setContentType("content-type:octet-stream");
return response.getOutputStream();
}
public static List<Map<String, Object>> importExcel(File file, String sheetName) throws IOException, InvalidFormatException {
String name = file.getName();
Workbook workbook = null;
List<Map<String, Object>> list = new ArrayList<>();
try (InputStream is = new FileInputStream(file)) {
// 创建excel操作对象
if (name.contains(".xlsx") || name.contains(".xls")) {
//使用工厂方法创建.
workbook = WorkbookFactory.create(is);
}
Sheet sheet = workbook.getSheet(sheetName);
//获得数据的总行数
int totalRowNum = sheet.getLastRowNum();
//获得总列数
int cellLength = sheet.getRow(0).getPhysicalNumberOfCells();
//获取表头
Row firstRow = sheet.getRow(0);
List<String> keys = new ArrayList<>();
for (int i = 0; i < cellLength; i++) {
Cell cell = firstRow.getCell(i);
keys.add(String.valueOf(getCellValue(cell)));
}
//从第i行开始获取
for (int i = 1; i <= totalRowNum; i++) {
Map<String, Object> map = new LinkedHashMap<>();
//获得第i行对象
Row row = sheet.getRow(i);
// 遇到空行则结束
if (row == null) {
break;
}
//如果一行里的所有单元格都为空则不放进list里面
int a = 0;
for (int j = 0; j < cellLength; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
continue;
}
// 获取列值
Object value = getCellValue(cell);
map.put(keys.get(j), value);
}
if (!checkNullMap(map)) {
list.add(map);
}
}
} finally {
if (workbook != null) {
workbook.close();
}
}
return list;
}
//如果map存储的value都是null返回true
private static boolean checkNullMap(Map<String, Object> map) {
for (Object value : map.values()) {
if (Objects.nonNull(value))
return false;
}
return true;
}
private static Object getCellValue(Cell cell) {
CellType cellType = cell.getCellType();
Object cellValue = null;
if (cellType == CellType._NONE) {
cellValue = null;
} else if (cellType == CellType.NUMERIC) {
// 数值型
if (DateUtil.isCellDateFormatted(cell)) {
// 日期类型
Date d = cell.getDateCellValue();
cellValue = dateTimeFormatter.format(LocalDateTime.ofInstant(d.toInstant(), ZoneId.systemDefault()));
} else {
double numericCellValue = cell.getNumericCellValue();
BigDecimal bdVal = new BigDecimal(numericCellValue);
if ((bdVal + ".0").equals(Double.toString(numericCellValue))) {
// 整型
cellValue = bdVal;
} else if (String.valueOf(numericCellValue).contains("E10")) {
// 科学记数法
cellValue = new BigDecimal(numericCellValue).toPlainString();
} else {
// 浮点型
cellValue = numericCellValue;
}
}
} else if (cellType == CellType.STRING) {
// 字符串型
cellValue = cell.getStringCellValue();
if (cellValue != null) {
cellValue = cellValue.toString().trim();
}
} else if (cellType == CellType.FORMULA) {
// 公式型
cellValue = cell.getCellFormula();
} else if (cellType == CellType.BLANK) {
// 空值
cellValue = "";
} else if (cellType == CellType.BOOLEAN) {
// 布尔型
cellValue = cell.getBooleanCellValue();
} else if (cellType == CellType.ERROR) {
// 错误
cellValue = cell.getErrorCellValue();
}
log.info("cellType={}, cellValue={}", cellType.name(), cellValue);
return cellValue;
}
}
