功能需求:
excel导出一张模版表格给用户输入基本信息填写,然后用户上传excel,使用批量插入到数据库,并且要去重。
一、引入pom依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
二、编写pojo类
package com.example.excel.domain;
import cn.afterturn.easypoi.excel.annotation.Excel;
public class Jzginfo {
@Excel(name = "工号", orderNum = "0")
private String gh;
@Excel(name = "姓名", orderNum = "1")
private String xm;
@Excel(name = "性别", orderNum = "2")
private String xb;
@Excel(name = "民族", orderNum = "3")
private String mz;
@Excel(name = "政治面貌", orderNum = "4")
private String ZZMM;
@Excel(name = "身份证件类型", orderNum = "5")
private String SFZJLX;
@Excel(name = "身份证件号", orderNum = "6")
private String SFZJH;
@Excel(name = "单位号", orderNum = "7")
private String DWH;
@Excel(name = "科室号", orderNum = "8")
private String KSH;
@Excel(name = "职务", orderNum = "9")
private String ZW;
@Excel(name = "岗位", orderNum = "10")
private String GW;
@Excel(name = "手机号码", orderNum = "11")
private String SJHM;
@Excel(name = "籍贯", orderNum = "12")
private String JG;
@Excel(name = "人员状态", orderNum = "13")
private String RYZT;
// @Excel(name = "性别", replace = {"男_1", "女_2"}, orderNum = "1")
// private String sex;
//
// @Excel(name = "生日", exportFormat = "yyyy-MM-dd", orderNum = "2")
// private Date birthday;
public Jzginfo() {
}
public Jzginfo(String gh, String xm, String xb, String mz, String ZZMM, String SFZJLX, String SFZJH, String DWH, String KSH, String ZW, String GW, String SJHM, String JG, String RYZT) {
this.gh = gh;
this.xm = xm;
this.xb = xb;
this.mz = mz;
this.ZZMM = ZZMM;
this.SFZJLX = SFZJLX;
this.SFZJH = SFZJH;
this.DWH = DWH;
this.KSH = KSH;
this.ZW = ZW;
this.GW = GW;
this.SJHM = SJHM;
this.JG = JG;
this.RYZT = RYZT;
}
@Override
public String toString() {
return "Jzhinfo{" +
"gh='" + gh + '\'' +
", xm='" + xm + '\'' +
", xb='" + xb + '\'' +
", mz='" + mz + '\'' +
", ZZMM='" + ZZMM + '\'' +
", SFZJLX='" + SFZJLX + '\'' +
", SFZJH='" + SFZJH + '\'' +
", DWH='" + DWH + '\'' +
", KSH='" + KSH + '\'' +
", ZW='" + ZW + '\'' +
", GW='" + GW + '\'' +
", SJHM='" + SJHM + '\'' +
", JG='" + JG + '\'' +
", RYZT='" + RYZT + '\'' +
'}';
}
public String getGh() {
return gh;
}
public void setGh(String gh) {
this.gh = gh;
}
public String getXm() {
return xm;
}
public void setXm(String xm) {
this.xm = xm;
}
public String getXb() {
return xb;
}
public void setXb(String xb) {
this.xb = xb;
}
public String getMz() {
return mz;
}
public void setMz(String mz) {
this.mz = mz;
}
public String getZZMM() {
return ZZMM;
}
public void setZZMM(String ZZMM) {
this.ZZMM = ZZMM;
}
public String getSFZJLX() {
return SFZJLX;
}
public void setSFZJLX(String SFZJLX) {
this.SFZJLX = SFZJLX;
}
public String getSFZJH() {
return SFZJH;
}
public void setSFZJH(String SFZJH) {
this.SFZJH = SFZJH;
}
public String getDWH() {
return DWH;
}
public void setDWH(String DWH) {
this.DWH = DWH;
}
public String getKSH() {
return KSH;
}
public void setKSH(String KSH) {
this.KSH = KSH;
}
public String getZW() {
return ZW;
}
public void setZW(String ZW) {
this.ZW = ZW;
}
public String getGW() {
return GW;
}
public void setGW(String GW) {
this.GW = GW;
}
public String getSJHM() {
return SJHM;
}
public void setSJHM(String SJHM) {
this.SJHM = SJHM;
}
public String getJG() {
return JG;
}
public void setJG(String JG) {
this.JG = JG;
}
public String getRYZT() {
return RYZT;
}
public void setRYZT(String RYZT) {
this.RYZT = RYZT;
}
}
三、编写util工具类
package com.example.excel.util;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
public class FileUtils {
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
defaultExport(list, fileName, response);
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
// throw new NormalException(e.getMessage());
}
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
if (StringUtils.isBlank(filePath)){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new FileInputStream(filePath), pojoClass, params);
}catch (NoSuchElementException e){
// throw new NormalException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
// throw new NormalException(e.getMessage());
}
return list;
}
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
if (file == null){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
}catch (NoSuchElementException e){
// throw new NormalException("excel文件不能为空");
} catch (Exception e) {
// throw new NormalException(e.getMessage());
}
return list;
}
}
四、编写controller层
4.1controller层的导出功能
@GetMapping("/export")
public void export(HttpServletResponse response){
//模拟从数据库获取需要导出的数据
List<Jzginfo> jzginfoList = new ArrayList<>();
// Person person1 = new Person("路飞","1",new Date());
// personList.add(person1);
//导出操作
FileUtils.exportExcel(jzginfoList,"教职工基本信息表","草帽一伙",Jzginfo.class,"教职工基本信息.xls",response);
}
4.2controller层的导入功能,一个是文件路径,另外一个是form表单上传文件实现导入功能
@PostMapping("/importExcel")
@ResponseBody
public Integer importExcel(@RequestParam("file") MultipartFile file){
// String filePath = "/users/海贼王.xls";
//解析excel,
// List<Person> personList = FileUtils.importExcel(filePath,1,1,Person.class);
//也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入
List<Jzginfo> jzginfoList = FileUtils.importExcel(file, 1, 1, Jzginfo.class);
System.out.println("导入数据一共【"+jzginfoList.size()+"】行");
jzginfoList.forEach(System.out::println);
if(CollectionUtils.isEmpty(jzginfoList)){
return 0;
}
List<Jzginfo> list = jzginfoService.getJzhinfo();
// list.forEach(System.out::println);
List<Jzginfo> newList = new ArrayList<>();
for (Jzginfo jzginfo : jzginfoList) {
for (int i = 0; i < list.size();i++) {
if(jzginfo.getGh().equals(list.get(i).getGh())){
System.out.println(jzginfo);
break;
}
if(i == list.size()-1){
newList.add(jzginfo);
}
}
}
newList.forEach(System.out::println);
boolean b = jzginfoService.insertJzginfo(newList);
if(b){
return 1;
}else{
return 0;
}
//TODO 保存数据库
}
五、批量插入
这里使用的是oracle,自定义批量插入语句,没有使用tk通用mapper的insertList。
首先配置类扫描mapper包
@MapperScan(basePackages = {“com.example.excel.mapper”})
1、以下是oracle的
<?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.excel.mapper.JzginfoMapper">
<select id="getJzhinfo" resultType="Jzginfo">
select * from dboperation.ZSJLSGLSY_XMGXYJZGJBXX
</select>
<insert id="insertList" parameterType="java.util.List">
insert into dboperation.ZSJLSGLSY_XMGXYJZGJBXX
<foreach collection="list" item="Jzginfo" separator="union all" index="index">
SELECT
#{Jzginfo.gh,jdbcType=VARCHAR},#{Jzginfo.xm,jdbcType=VARCHAR},#{Jzginfo.xb,jdbcType=VARCHAR},#{Jzginfo.mz,jdbcType=VARCHAR},#{Jzginfo.ZZMM,jdbcType=VARCHAR},#{Jzginfo.SFZJLX,jdbcType=VARCHAR},#{Jzginfo.SFZJH,jdbcType=VARCHAR},#{Jzginfo.DWH,jdbcType=VARCHAR},#{Jzginfo.KSH,jdbcType=VARCHAR},#{Jzginfo.ZW,jdbcType=VARCHAR},#{Jzginfo.GW,jdbcType=VARCHAR},#{Jzginfo.SJHM,jdbcType=VARCHAR},#{Jzginfo.JG,jdbcType=VARCHAR},#{Jzginfo.RYZT,jdbcType=VARCHAR}
FROM dual
</foreach>
</insert>
</mapper>
2、以下是mysql的
idea的springboot项目要想使用mapper.xml需要yml配置
#mybatis xml文件位置
mybatis:
mapper-locations: classpath:mapper/*.xml
#配置驼峰转下划线(数据库的下划线和model的驼峰自动转换)
configuration:
map-underscore-to-camel-case: true
#配置别名
type-aliases-package: com.example.excel.domain
六、excel导入和导出的前端实现
<a href="/excel/export">教职工信息表格模板下载</a>
<form id="person_info" style="margin-top: 20px">
<input type="file" name="file" id="file">
<input type="button" value="提交" onclick="person()">
</form>
<script type="text/javascript">
function person() {
console.log("111");
const headImg = document.getElementById("file").files[0];
console.log(headImg);
// console.log(file);
const form = $('#person_info')[0];
const data = new FormData(form);
data.append('headImg', headImg);
console.log(data);
$.ajax({
type: "post",
url: ctx + "importExcel",
data: data,
async: false,
cache: false,
processData: false,
contentType: false,
success: function (msg) {
console.log(msg);
if(msg === 1){
alert("上传成功");
}else{
alert("上传失败!");
}
},
error: function (msg) {
alert("上传失败!");
}
});
// $("#info_submit").attr("data-dismiss", "modal");
// window.location.reload();
}
</script>