目录
AnalysisEventListener实现类(invoke方法实现解析后从接收类转为实体类并保存在数据库中):
概述
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目(一行一行放入内存中)。在尽可能节约内存的情况下支持读写百M的Excel。
EasyExcel控制表格注解
@ContentRowHeight(int):设置 row 高度,不包含表头,标记在类上
@HeadRowHeight(int):设置 表头 高度(与 @ContentRowHeight 相反) ,标记在类上
@ColumnWidth(int):设置列宽 标记在属性上
@ExcelProperty(value = String[], index = int):设置表头信息 value: 表名称 index: 列号
Excel导出
依赖:
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
创建Excel实体类对象:
@ColumnWidth(15)
public class CustomerRow {
@ExcelProperty("用户编码")
private String code;
@ExcelProperty("用户卡号")
private String yhkh;
@ExcelProperty("用户名称")
private String name;
@ExcelProperty("身份证号")
private String idNumber;
}
控制器方法:
@RestController
public class ExcelController {
@Autowired
private CustomerService customerService;
@Transactional
@PostMapping("/excel")
public String importCustomer(MultipartFile file, HttpServletRequest request) {
if(file == null){
throw new RuntimeException("未上传文件");
}
try {
CustomerListener listener = new CustomerListener(customerService);
EasyExcel.read(file.getInputStream(), CustomerRow.class, listener).sheet().doRead();
}catch (Exception e) {
throw new RuntimeException(e);
}
return "上传成功";
}
}
AnalysisEventListener实现类(invoke方法实现解析后从接收类转为实体类并保存在数据库中):
package com.example.excelstudy.Listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.excelstudy.bean.Customer;
import com.example.excelstudy.bean.CustomerRow;
import com.example.excelstudy.service.CustomerService;
import java.util.Random;
/**
* @author : Administrator
* @version : V1.0.0
* @className : CustomerListener
* @description : 该类功能 TODO
* @createTime : 2022-11-15
*/
public class CustomerListener extends AnalysisEventListener<CustomerRow>{
private CustomerService customerService;
public CustomerListener (CustomerService service){
customerService=service;
}
@Override
public void invoke(CustomerRow customer, AnalysisContext analysisContext) {
Random random = new Random();
long l = random.nextLong();
// 数据结构转换
Customer source = this.parseCustomer(customer);
source.setId(l);
customerService.addSave(source);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
/**
* 将行数据转换成对应的用户对象
* @param row 行数据
* @return
*/
private Customer parseCustomer(CustomerRow row) {
Customer customer = new Customer();
// 自动生成用户编码
customer.setCode(row.getCode());
customer.setYhkh(row.getYhkh());
customer.setName(row.getName());
customer.setIdNumber(row.getIdNumber());
return customer;
}
}
日志文件:
<?xml version="1.0" encoding="UTF-8"?>
<configuration debug="false">
<!--定义日志文件的存储地址 勿在 LogBack 的配置中使用相对路径-->
<property name="LOG_HOME" value="/logs/charge" />
<!--控制台日志, 控制台输出 -->
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
<!--格式化输出:%d表示日期,%thread表示线程名,%-5level:级别从左显示5个字符宽度,%msg:日志消息,%n是换行符-->
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
</encoder>
</appender>
<!--文件日志, 按照每天生成日志文件 -->
<appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
<rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
<!--日志文件输出的文件名-->
<FileNamePattern>${LOG_HOME}/%d{yyyy-MM-dd}_%i.log</FileNamePattern>
<!--日志文件最大的大小-->
<maxFileSize>20MB</maxFileSize>
<!--日志文件保留天数-->
<MaxHistory>90</MaxHistory>
</rollingPolicy>
<encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
<!--格式化输出:%d表示日期,%thread表示线程名,%-5level:级别从左显示5个字符宽度%msg:日志消息,%n是换行符-->
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
</encoder>
<!--日志文件最大的大小-->
<!-- <triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">
<MaxFileSize>10MB</MaxFileSize>
</triggeringPolicy> -->
</appender>
<!--myibatis log configure-->
<!-- <logger name="org.apache.ibatis" level="DEBUG" /> -->
<!-- <logger name="java.sql.Connection" level="DEBUG"/>
<logger name="java.sql.Statement" level="DEBUG"/>
<logger name="java.sql.PreparedStatement" level="DEBUG"/> -->
<!-- <logger name="java.sql" level="DEBUG"/> -->
<!-- 打印包下的sql -->
<logger name="com.example.excelstudy" level="DEBUG"/>
<!-- 日志输出级别 -->
<root level="INFO">
<appender-ref ref="STDOUT"/>
<appender-ref ref="FILE"/>
</root>
</configuration>