使用easyexcel实现Excel数据导入以及数据库数据导出成Excel
一、效果
1、数据库中数据
2、导出接口
3、导出内容
4、导入接口
5、打印内容
二、依赖
easyexcel
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.8</version>
</dependency>
mybatis-plus
<properties>
<mybatis.plus.version>3.4.0</mybatis.plus.version>
</properties>
<dependencies>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis.plus.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>${mybatis.plus.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>${mybatis.plus.version}</version>
</dependency>
<dependency>
<groupId>com.github.jeffreyning</groupId>
<artifactId>mybatisplus-plus</artifactId>
<version>1.5.1-RELEASE</version>
<scope>compile</scope>
</dependency>
</dependencies>
三、数据库
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`number` int(11) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES (1, 1, '华为', '电视');
INSERT INTO `product` VALUES (2, 1, '苹果13pro', '手机');
INSERT INTO `product` VALUES (3, 2, '红米', '手机');
四、代码
1、控制层
/**
* @author licy
* @description
* @date 2022/8/10
*/
@RestController
@RequestMapping("/product")
@Slf4j
public class ProductController {
@Autowired
private ProductService productService;
/**
* 文件导出
*
* @param response
* @param request
* @return
* @throws IOException
*/
@GetMapping("/exportTemplate")
public ExportExcelDTO exportTable(HttpServletResponse response, HttpServletRequest request) throws IOException {
//请求头
String fileName = URLEncoder.encode("数据导出", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//逻辑代码获取数据(自定义修改)
List<ProductDO> list = productService.list();
// ArrayList<ExportExcelDTO> exportExcelDTOArrayList = Lists.newArrayList();
List<ExportExcelDTO> productDOS = BeanUtil.listCopyTo(list, ExportExcelDTO.class);
//获取输出流
OutputStream outputStream = response.getOutputStream();
try {
//导出
EasyExcel.write(outputStream, ExportExcelDTO.class).sheet().doWrite(productDOS);
outputStream.flush();
} catch (IOException e) {
} finally {
outputStream.close();
}
return null;
}
/**
* 表格导入
*/
@PostMapping("/tableImport")
@Transactional
public Boolean tableImport(@RequestParam("multipartFile") MultipartFile multipartFile) throws IOException {
//读取上传文件数据转换成
List<ExportExcelDTO> resExcelDTOList = EasyExcelUtil.readExcel(multipartFile.getInputStream(), ExportExcelDTO.class, new EasyExcelUtil.ExcelListener<>());
log.info(resExcelDTOList.toString());
return true;
}
}
2、逻辑层
/**
* @author licy
* @description
* @date 2022/8/10
*/
public interface ProductService extends IMppService<ProductDO> {
}
/**
* @author licy
* @description
* @date 2022/8/10
*/
@Service
public class ProductServiceImpl extends MppServiceImpl<ProductMapper, ProductDO> implements ProductService {
}
3、持久层
/**
* @author licy
* @description
* @date 2022/8/10
*/
public interface ProductMapper extends MppBaseMapper<ProductDO> {
}
4、xml
<?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.example.demo.mapper.ProductMapper">
</mapper>
5、实体类以及其他数据类
/**
* @author licy
* @description
* @date 2022/8/10
*/
@Data
@TableName("product")
public class ProductDO implements Serializable {
@TableId(value = "id")
private Integer id;
@TableField(value = "type")
private String type;
@TableField(value = "number")
private Integer number;
@TableField(value = "name")
private String name;
}
/**
* @author licy
* @description
* @date 2022/8/26
*/
@Data
@ColumnWidth(45)//注释在具体属性上,设置单独列。注释在类上,统一设置列宽
@HeadRowHeight(50)//设置表头行高
public class ExportExcelDTO implements Serializable {
private static final long serialVersionUID = -1748449459046460402L;
@ExcelProperty("类型")
private String type;
@ExcelProperty("编号")
private Integer number;
@ExcelProperty("名称")
private String name;
}
6、工具类
public class BeanUtil extends BeanUtils {
public static <E> List<E> listCopyTo(List<?> source, Class<E> destinationClass) {
try {
if (source.size() == 0) {
return Collections.emptyList();
} else {
List<E> res = new ArrayList(source.size());
Iterator var3 = source.iterator();
while(var3.hasNext()) {
Object o = var3.next();
E e = destinationClass.newInstance();
BeanUtils.copyProperties(o, e);
res.add(e);
}
return res;
}
} catch (IllegalAccessException var6) {
throw new RuntimeException(var6);
} catch (InstantiationException var7) {
throw new RuntimeException(var7);
}
}
}
/**
* <p>ClassName:EasyExcel工具类</p >
* <p>Description:</p >
* <p>Date:2022/8/1</p >
*/
@Slf4j
public class EasyExcelUtil {
//-------------------------------------------------------------- 读取文件解析监听类 start ----------------------------------------------------
/**
* <p>ClassName:ExcelListener</p >
* <p>Description:读取文件解析监听类,此类供外部实例化使用需要设置为静态类</p >
* <p>Date:2021/9/2</p >
*/
public static class ExcelListener<T> extends AnalysisEventListener<T> {
/**
* <p>存放读取后的数据</p >
*
* @date 2021/9/2 0:10
*/
public List<T> datas = new ArrayList<>();
/**
* <p>读取数据,一条一条读取</p >
*
* @date 2021/9/2 0:15
*/
@Override
public void invoke(T t, AnalysisContext analysisContext) {
datas.add(t);
}
/**
* <p>解析完毕之后执行</p >
*
* @date 2021/9/2 0:06
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("读取数据条数:{}条!", datas.size());
}
public List<T> getDatas() {
return this.datas;
}
}
//-------------------------------------------------------------- 读取文件解析监听类 end ----------------------------------------------------
//-------------------------------------------------------------- 导出excel表格,设置自适应列宽配置类 start ----------------------------------------------------
/**
* <p>ClassName:Custemhandler</p >
* <p>Description:设置自适应列宽配置类</p >
* <p>Date:2021/10/14</p >
*/
public static class Custemhandler extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
//因为在自动列宽的过程中,有些设置地方让列宽显得紧凑,所以做出了个判断
private static final int COLUMN_WIDTH = 20;
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap(16);
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
} else {
if (columnWidth < COLUMN_WIDTH) {
columnWidth = columnWidth * 2;
}
}
Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
//-------------------------------------------------------------- 导出excel表格,设置自适应列宽配置类 end -----------------------------------------------------
/**
* <p> 读取Excel文件返回数据集合,不包含表头,默认读取第一个sheet数据 </p >
*
* @param inputStream 输入流
* @param tClass 数据映射类
* @param excelListener 读取监听类
* @return List 结果集
* @date 2021/9/2 0:17
*/
public static <T> List<T> readExcel(InputStream inputStream, Class<T> tClass, ExcelListener<T> excelListener) {
if (inputStream == null || tClass == null || excelListener == null) {
return null;
}
ExcelReaderBuilder read = EasyExcel.read(inputStream, tClass, excelListener);
read.sheet().doRead();
return excelListener.getDatas();
}
/**
* <p> 读取Excel文件返回数据集合,不包含表头,读取第x个sheet数据,不设置sheet就读取全部 </p >
*
* @param inputStream 输入流
* @param tClass 数据映射类
* @param excelListener 读取监听类
* @return List 结果集
* @date 2021/9/2 0:17
*/
public static <T> List<T> readExcel(InputStream inputStream, Integer sheetNo, Class<T> tClass, ExcelListener<T> excelListener) {
if (inputStream == null || tClass == null || excelListener == null) {
return null;
}
ExcelReaderBuilder read = EasyExcel.read(inputStream, tClass, excelListener);
if (sheetNo != null) {
read.sheet(sheetNo).doRead();
} else {
ExcelReader excelReader = read.build();
excelReader.readAll();
excelReader.finish();
}
return excelListener.getDatas();
}
}
7、配置文件
server:
port: 8888
#数据库配置
spring:
datasource:
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/avlicy?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&allowMultiQueries=true&rewriteBatchedstatements=true
username: root
password: a123456
# 连接池的配置信息
# 初始化大小,最小,最大
initial-size: 5
min-idle: 5
max-active: 20
#mybatis-plus
mybatis-plus:
#映射mapper.xml文件存放路径
mapper-locations: classpath:/mapper/*Mapper.xml
#实体扫描,多个package用逗号或者分号分隔
type-aliases-package: com.example.demo.entity.base,com.example.demo.entity.integration
configuration:
#下划线转驼峰配置
map-underscore-to-camel-cas: true
#使用二级缓存容易出现脏读,建议避免使用二级缓存
cache-enabled: false
#指定 MyBatis 应如何自动映射列到字段或属性。 NONE 表示取消自动映射;PARTIAL 只会自动映射没有定义嵌套结果集映射的结果集。
#FULL 会自动映射任意复杂的结果集(无论是否嵌套)。默认是partial,这是一种全局设置
auto-mapping-behavior: full
#控制台输出日志
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
1、你的人生不会辜负你的。那些转错的弯,那些流下的泪水,那些滴下的汗水,全都让你成为独—无二的自己。
2、无论大事还是小事,只要自己是认为办得好的,就坚定地去办,这就是性格。
3、抱怨是一件没有意义的事情,如果实在难以忍受周围的环境,那就暗自努力,然后跳出那个圈子。
4、努力些,不然钱还是会流向不缺钱的人,爱也会流向不缺爱的人。
5、假如命运亏待了你,我们无法选择命运,我们唯一可以选择的是,当命运露出狰狞的一面时,坦然无畏地活下去。
6、时间管理的关键是你必须要有一张总清单,也就是你必须要把今年所要做的每一件事都列出来。
7、生命对某些人来说是美丽的,这些人的一生都为某个目标而奋斗。
8、酸甜苦辣都是营养,成功失败都是经验。
9、我会用我的最大热诚去演这出人生的戏不怕失败不怕孤单不怕绝望!一直坚信雨后总会有彩虹!
10、有目标的人在感恩,他们知道努力才是人生的态度;没目标的人在抱怨,而且总是觉得全世界都欠他的。