简单的实现一个数据库导入导出Excel文件
这里用到一个阿里开发的EasyExcel
EasyExcel文档地址:https://alibaba-easyexcel.github.io/index.html
- 导入依赖
<!-- easyexcel 依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
- 准备一个entity类。属性上的注解@ExcelProperty是设置生成的excel表格的列名,如果不写 生成的列名就是你实体类的属性名
package com.example.getexcel.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class House {
/**
* 序号
*/
@ExcelProperty("序号")
private Integer id;
/**
* 联系电话
*/
@ExcelProperty("联系电话")
private String phone;
/**
* 地址
*/
@ExcelProperty("地址")
private String location;
/**
* 大小
*/
@ExcelProperty("大小")
private String size;
/**
* 设施
*/
@ExcelProperty("设施")
private String laundry;
/**
* 租金
*/
@ExcelProperty("租金")
private Double money;
/**
* 规格
*/
@ExcelProperty("规格")
private String hao;
}
3.在Service层编写上传下载代码
package com.example.getexcel.service.impl;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.example.getexcel.entity.House;
import com.example.getexcel.mapper.HouseMapper;
import com.example.getexcel.service.HouseService;
import com.example.getexcel.until.DataListener;
import com.example.getexcel.until.Result;
import lombok.SneakyThrows;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;
@Service
public class HouseServiceImpl implements HouseService {
private final HouseMapper houseMapper;
@Autowired
public HouseServiceImpl(HouseMapper houseMapper) {
this.houseMapper = houseMapper;
}
/**
* 导出excel
* @param response
* @return
*/
@SneakyThrows
@Override
public Result download(HttpServletResponse response) {
List<House> houseList = houseMapper.findAll();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), House.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//自适应表格格式
.sheet("模板")
.doWrite(houseList);
return Result.success("下载成功");
}
/**
* 导入cxcel
* @param file
* @return
*/
@SneakyThrows
@Override
public Result upload(MultipartFile file) {
EasyExcel.read(file.getInputStream(), House.class, new DataListener(houseMapper)).sheet().doRead();
return Result.success("上传成功");
}
}
4.mapper层代码
package com.example.getexcel.mapper;
import com.example.getexcel.entity.House;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
@Mapper
public interface HouseMapper {
/**
* 查询房子
*/
List<House> findAll();
/**
* 批量插入房子信息
* @return
*/
int save(List<House> houseList);
}
5.mybatis映射文件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.getexcel.mapper.HouseMapper">
<select id="findAll" resultType="com.example.getexcel.entity.House">
select * from house
</select>
<insert id="save" parameterType="list">
insert into house(phone,location,size,laundry,money,hao)
values
<foreach collection="list" item="item" separator=",">
(
#{item.phone},
#{item.location},
#{item.size},
#{item.laundry},
#{item.money},
#{item.hao}
)
</foreach>
</insert>
</mapper>
6.controller层
package com.example.getexcel.controller;
import com.example.getexcel.service.HouseService;
import com.example.getexcel.until.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
@RestController
@RequestMapping("/house")
public class HouseController {
private HouseService houseService;
@Autowired
public HouseController(HouseService houseService) {
this.houseService = houseService;
}
/**
* 导出excel
* @param response
* @return
*/
@RequestMapping("/download")
public Result download(HttpServletResponse response){
return houseService.download(response);
}
/**
* 导入excel
* @param file
* @return
*/
@RequestMapping("/upload")
public Result upload(@RequestParam("file")MultipartFile file){
return houseService.upload(file);
}
}
7.mysql数据库
8.测试一下
浏览器上输入url 会看到excel下载下来了
打开看看
和数据表的内容是一样的
在试试上传,由于没有写前端页面 直接就用postman测试了
6点击send会把刚刚生成的表格里的数据新增进数据库 数据表里的数据会变成6条 。
看看数据库