POI和easyExcel
1.poi导入依赖
<!--07-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!--03-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<!--单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
2. poi写入操作(03写入快HSSF,但是最大只能写65536行 07可以写入大量数据,但是写入慢 XSFF 07升级版写入快并且可以写入大量SXSFF)
@Test
public void test1(){
String PATH="D:\\excellDownLoad\\";
// 创建一个工作蒲
Workbook workbook = new HSSFWorkbook();
// 创建一个工作表
Sheet sheet = workbook.createSheet("03统计表");
// 创建行
Row row = sheet.createRow(0);
// 创建第一列单元格（1,1）
Cell cell = row.createCell(0);
cell.setCellValue("员工姓名");
// 创建第二列单元格（1,2）
Cell cell2 = row.createCell(1);
cell2.setCellValue("时间日期");
// (2,1)
Row row2 = sheet.createRow(1);
// 创建第二列
Cell cell3 = row2.createCell(0);
cell3.setCellValue("小王");
// 创建第二列单元格（1,2）
Cell cell4 = row2.createCell(1);
cell4.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
OutputStream os=null;
try {
os= new FileOutputStream(PATH+"03测试版本.xls");
workbook.write(os);
} catch (Exception e) {
e.printStackTrace();
}
finally {
try {
os.close();
System.out.println("03生成成功");
} catch (IOException e) {
e.printStackTrace();
}
}
}
3.读取操作
@Test
public void test3() throws Exception{
String PATH="D:\\excellDownLoad\\";
//获取文件流
FileInputStream fis = new FileInputStream(PATH +"07测试版本.xls");
//创建一个工作簿。使用 excel能操作的这边他都可以操作
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
//获取标题内容
Row rowTitle = sheet.getRow(0);
if (rowTitle != null) {
//得到一行有多少列有数据
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + "|");
}
}
System.out.println();
}
//获取表中的内容
//获取表中有多少行有数据
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData != null) {
//读取列
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.println("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
Cell cell = rowData.getCell(cellNum);
//匹配列的数据类型
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING://字符
System.out.print("【 String】");
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN://布尔
System.out.print("【 BOOLEAN】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK://空
System.out.print("【 BLANK】");
break;
case HSSFCell.CELL_TYPE_NUMERIC://数字(日期、普通数字)
System.out.print("【 NUMERIC】");
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期
System.out.print("--【日期】");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
//不是日期格式，防止数字过长！
System.out.print("--【转换为字符串输出】");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_ERROR://错误
System.out.print("【 数据类型错误】");
break;
}
System.out.println(cellValue);
}
}
}
}
//关闭流
fis.close();
}
EasyExcel
1.导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.0-beta2</version>
</dependency>
2.controller层
package com.wl.excell.controller;
import com.wl.excell.easy.EasyExcel2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.ResponseBody;
/**
* @Autor: wl
* @Date: 2021-10-12 11:51
* @Version 1.0
* @Describe
*/
@Controller
public class UserController {
@Autowired
private EasyExcel2 easyExcel;
//导出操作
@PostMapping("/export")
@ResponseBody
public String exports(){
easyExcel.simpleWrite();
return "a";
}
// 导入操作
@PostMapping("/import")
@ResponseBody
public String imports(){
easyExcel.simpleRead();
return "a";
}
}
3.easy读写操作
package com.wl.excell.easy;
import com.alibaba.excel.EasyExcel;
import com.wl.excell.dao.UserDao;
import com.wl.excell.dto.UserDTO;
import com.wl.excell.listener.Listener;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @Autor: wl
* @Date: 2021-10-12 11:20
* @Version 1.0
* @Describe
*/
@Service
public class EasyExcel2 {
@Autowired
private UserDao userDao;
private List<UserDTO> data() {
List<UserDTO> list = userDao.selectAll();
return list;
}
/**
* 最简单的写
* <p>
* 1. 创建excel对应的实体对象 参照{@link }
* <p>
* 2. 直接写即可
*/
public void simpleWrite() {
String Path = "D:\\excelDownLoad\\";
// 写法1 JDK8+
// since: 3.0.0-beta1
String fileName = Path + "积分信息管理" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, UserDTO.class)
.sheet("模板")
.doWrite(data());
}
/**
* 最简单的读
* <p>
* 1. 创建excel对应的实体对象 参照{@link }
* <p>
* 2. 直接读
*/
public void simpleRead() {
// 写法1：JDK8+ ,不用额外写一个DemoDataListener
// since: 3.0.0-beta1
String Path = "D:\\excelDownLoad\\";
String fileName =Path+ "simpleWrite1634016318240.xlsx";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
// 这里每次会读取3000条数据 然后返回过来 直接调用使用数据就行
EasyExcel.read(fileName, UserDTO.class,new Listener(userDao)).sheet().doRead();
}
}
4.dao层
package com.wl.excell.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wl.excell.dto.UserDTO;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
* @Autor: wl
* @Date: 2021-10-12 11:25
* @Version 1.0
* @Describe
*/
@Repository
public interface UserDao extends BaseMapper<UserDTO> {
// 查询所有数据
List<UserDTO> selectAll();
// 批量插入
int insertBatch(List<UserDTO> list);
}
5.DTO层
package com.wl.excell.dto;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;
import java.util.Date;
/**
* @Autor: wl
* @Date: 2021-10-12 11:13
* @Version 1.0
* @Describe
*/
@Data
public class UserDTO {
@ExcelProperty("员工编号")
private String staffNO;
@ExcelProperty("员工姓名")
private String staffNAME;
@ExcelProperty("职位")
private String POSITION;
@ExcelProperty("标题")
private String TITLE;
@ExcelProperty("编码id")
private String OPENID;
@ExcelProperty("员工邮箱")
private String EMAIL;
@ExcelProperty("所属部门")
private String DEPARTMENT;
@ExcelProperty("员工状态")
private String ORGANIZATION;
@ExcelProperty("当前总积分")
private Integer currentTotalPOINTS;
@ExcelProperty("总积分")
private Integer totalPOINTS;
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
@ExcelProperty("上次更新时间")
private Date lastUPDATETIME;
@ExcelProperty("开始时间")
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
private Date beginTIME;
}
6.监听器
package com.wl.excell.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.wl.excell.dao.UserDao;
import com.wl.excell.dto.UserDTO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
/**
* @Autor: wl
* @Date: 2021-10-12 13:30
* @Version 1.0
* @Describe
*/
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class Listener extends AnalysisEventListener<UserDTO> {
private static final Logger LOGGER = LoggerFactory.getLogger(Listener.class);
/**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 3000;
/**
* 缓存的数据
*/
private List<UserDTO> list = new ArrayList<>(BATCH_COUNT);
/**
* 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private UserDao userDao;
public Listener() {
// 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
// demoDAO = new DemoDao();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param demoDAO
*/
public Listener(UserDao demoDAO) {
this.userDao = demoDAO;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(UserDTO data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list = new ArrayList<>(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据，开始存储数据库！", list.size());
userDao.insertBatch(list);
LOGGER.info("存储数据库成功！");
}
}
7.mapper层
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wl.excell.dao.UserDao">
<select id="selectAll" resultMap="userCommon">
select * from t_staff_inf
</select>
<insert id="insertBatch" parameterType="java.util.List">
insert into t_staff_inf2 values
<foreach collection="list" separator="," index="index" item="item">
(#{item.staffNO},#{item.staffNAME},#{item.POSITION},#{item.TITLE},#{item.OPENID},#{item.EMAIL},#{item.DEPARTMENT},#{item.ORGANIZATION},#{item.currentTotalPOINTS},#{item.totalPOINTS},#{item.lastUPDATETIME},#{item.beginTIME})
</foreach>
</insert>
<resultMap id="userCommon" type="com.wl.excell.dto.UserDTO">
<id property="staffNO" column="STAFF_NO"/>
<result property="staffNAME" column="STAFF_NAME"/>
<result property="POSITION" column="POSITION"/>
<result property="TITLE" column="TITLE"/>
<result property="OPENID" column="OPENID"/>
<result property="EMAIL" column="EMAIL"/>
<result property="DEPARTMENT" column="DEPARTMENT"/>
<result property="ORGANIZATION" column="ORGANIZATION"/>
<result property="currentTotalPOINTS" column="CURRENT_TOTAL_POINTS"/>
<result property="totalPOINTS" column="TOTAL_POINTS"/>
<result property="lastUPDATETIME" column="LAST_UPDATE_TIME"/>
<result property="beginTIME" column="BINDING_TIME"/>
</resultMap>
</mapper>```