1. EasyExcel的特点
2.用easyexcel进行写操作
(1)引入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
<!--xls-->
(2)创建实体类,和excel里面数据对应上
package com.atguigu.demo.excel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class DemoData {
//设置excel表头名称
@ExcelProperty(value = "学生编号",index = 0)//第一列
private Integer sno;
@ExcelProperty(value = "学生姓名",index = 1)//第二列
private String sname;
}
package com.atguigu.demo.excel;
import com.alibaba.excel.EasyExcel;
import java.util.ArrayList;
import java.util.List;
public class TestEasyExcel {
public static void main(String[] args) {
//实现excel写的操作
//1 设置写入文件夹地址和excel文件名称
String filename = "E:\\write.xlsx";
//2 调用easyexcel里面的方法实现写操作
//write方法两个参数:第一个参数文件路径名称,第二个参数实体类class
EasyExcel.write(filename,DemoData.class).sheet("学生列表(sheet1列表名)").doWrite(getData());
}
//创建方法返回list集合,手动构建数据
private static List<DemoData> getData() {
List<DemoData> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setSno(i);
data.setSname("lucy"+i);
list.add(data);
}
return list;
}
}
3.用easyexcel进行读操作
读的时候要监听器,它用于一行一行的读取excel中的内容
(1)实体类,写时已写
(2)写监听器
package com.atguigu.demo.excel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.Map;
public class ExcelListener extends AnalysisEventListener<DemoData> {
//一行一行读取excel内容,但是从第二行开始读出,因为第一行是表头
@Override
public void invoke(DemoData data, AnalysisContext analysisContext) {
System.out.println("****"+data);//他会把每行内容封装到一个data对象里面
}
//读取表头内容,即读取第一行的内容
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头:"+headMap);
}
//读取完成之后
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) { }
}
package com.atguigu.demo.excel;
import com.alibaba.excel.EasyExcel;
import java.util.ArrayList;
import java.util.List;
public class TestEasyExcel {
public static void main(String[] args) {
//实现excel读操作
String filename = "E:\\write.xlsx";
EasyExcel.read(filename,DemoData.class,new ExcelListener()).sheet().doRead();
}
}
实践一:
写:把数据库的数据导出到一个Excel
读:把Excel中的数据读到并封装到我们的一个bean对象
//导出数据字典接口,导到Excel
@GetMapping("exportData")
public void exportDict(HttpServletResponse response) {
//这个参数可以让用户自己选择下载到哪个路径
dictService.exportDictData(response);
}
//导出数据字典接口,即让用户下载excel
@Override
public void exportDictData(HttpServletResponse response) {
//1.设置下载信息
//设置content类型
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//URLEncoder.encode可以防止中文乱码
try {
String fileName = URLEncoder.encode("数据字典列表", "UTF-8");
//Content-disposition让我这个操作以下载方式打开
response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");
}catch (UnsupportedEncodingException e){
e.printStackTrace();
}
//2.查询数据库
List<Dict> dictList = baseMapper.selectList(null);
//Dict -- >DictEeVo
List<DictEeVo> dictVoList = new ArrayList<>();
for(Dict dict:dictList) {
DictEeVo dictEeVo = new DictEeVo();
// dictEeVo.setId(dict.getId());
BeanUtils.copyProperties(dict,dictEeVo);//属性名相同的他会自己帮我们复制
dictVoList.add(dictEeVo);
}
//3.调用方法进行写操作
try {
EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("dict")
.doWrite(dictVoList);
} catch (IOException e) {
e.printStackTrace();
}
}
@Data
public class DictEeVo {
@ExcelProperty(value = "id" ,index = 0)
private Long id;
@ExcelProperty(value = "上级id" ,index = 1)
private Long parentId;
@ExcelProperty(value = "名称" ,index = 2)
private String name;
@ExcelProperty(value = "值" ,index = 3)
private String value;
@ExcelProperty(value = "编码" ,index = 4)
private String dictCode;
}
导入:
//导入数据字典,上传Excel文件到项目
@PostMapping("importData")
public Result importDict(MultipartFile file) {
//MultipartFile file可以得到上传的文件
dictService.importDictData(file);
return Result.ok();
}
//导入数据字典
@Override
@CacheEvict(value = "dict", allEntries=true)
public void importDictData(MultipartFile file) {
try {
EasyExcel.read(file.getInputStream(),DictEeVo.class,new DictListener(baseMapper)).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
}
//真正做的是在监听器里,每次取一行一行...
public class DictListener extends AnalysisEventListener<DictEeVo> {
//把mapper通过构造方法注入进来
private DictMapper dictMapper;
public DictListener(DictMapper dictMapper) {
this.dictMapper = dictMapper;
}
//一行一行读取到一个dicEoVo的实例
@Override
public void invoke(DictEeVo dictEeVo, AnalysisContext analysisContext) {
//调用方法添加数据库
Dict dict = new Dict();
//把DictVo变成跟数据库对应的Dict
BeanUtils.copyProperties(dictEeVo,dict);
dictMapper.insert(dict);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
<a href="http://localhost:8202/admin/dict/exportData" target="_blank">
<el-button type="primary" size="mini"><i class="fa fa-plus"/> 导出</el-button>
</a>
//或者用事件@click=exportData()
//导出数据字典到Excel
exportData(){
// window.location.href = ''
}
实践二:
(1)entity类
package com.party.excel;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import lombok.Data;
import com.alibaba.excel.annotation.ExcelProperty;
import java.io.Serializable;
@Data
@ContentRowHeight(20)//数据行的行高
@ColumnWidth(20)//列宽
@ExcelIgnoreUnannotated//表示没有加@ExcelProperty的注解不会参与读写
public class ActivistData implements Serializable {
@ExcelProperty(value = "姓名",index = 0)
private String name;//姓名
@ExcelProperty(value = "性别",index = 1)
private String sex;//性别
@ExcelProperty(value = "身份证号",index = 2)
private String idCard;//身份证
@ExcelProperty(value = "籍贯",index = 3)
private String nativePlace;//籍贯
@ExcelProperty(value = "户籍所在地",index = 4)
private String residence;//户籍所在地
@ExcelProperty(value = "民族",index = 5)
private String nation;//民族
@ExcelProperty(value = "联系电话",index = 6)
private String phone;//电话号码
@ExcelProperty(value = "年级",index = 7)
private String grade;//年级
@ExcelProperty(value = "班级",index = 8)
private String classNum;//班级
@ExcelProperty(value = "学号",index = 9)
private String sid;//学号
@ExcelProperty(value = "宿舍",index = 10)
private String room;//宿舍号
@ExcelProperty(value = "职务",index = 11)
private String duty;//职务(副部长)
@ExcelProperty(value = "职称",index = 12)
private String title;//职称
@ExcelProperty(value = "学历",index = 13)
private String aducation;//学历
@ExcelProperty(value = "所在党总支",index = 14)
private String generalName;
@ExcelProperty(value = "所在党支部",index = 15)
private String partyName;
@ExcelProperty(value = "所在党小组",index = 16)
private String groupName;
@ExcelProperty(value = "所在团支部",index = 17)
private String leagueBranchName;
@DateTimeFormat("yyyy-MM-dd")
@ExcelProperty(value = "申请书日期",index = 18)
private java.util.Date petitionConfirm;//落款时间
@DateTimeFormat("yyyy-MM-dd")
@ExcelProperty(value = "确定为积极分子日期",index = 19)
private java.util.Date activistTime;//成为积极分子时间
@ExcelProperty(value = "培养联系人1",index = 20)
private String culture1Name;
@ExcelProperty(value = "培养联系人1学号",index = 21)
private String culture1Sid;
@ExcelProperty(value = "培养联系人2",index = 22)
private String culture2Name;
@ExcelProperty(value = "培养联系人2学号",index = 23)
private String culture2Sid;
}
(2)controller类
package com.party.controller.excel;
import com.alibaba.dubbo.config.annotation.Reference;
import com.alibaba.excel.EasyExcel;
import com.party.entity.R;
import com.party.excel.ActivistData;
import com.party.service.system.BaseUserService;
import com.party.vo.ActivistVo;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
@RestController
@RequestMapping("/excel")
public class ExcelController {
@Reference
private BaseUserService baseUserService;
@PostMapping("/leadInActivist")
public R leadIn(MultipartFile file){
//不知道为啥file文件传不到service去。
List<ActivistData> list = new ArrayList<>();
try{
list = EasyExcel.read(file.getInputStream()).head(ActivistData.class)
.sheet().doReadSync();
}catch (Exception e){
e.printStackTrace();
}
for (ActivistData activistData:list){
ActivistVo activistVo = new ActivistVo();
BeanUtils.copyProperties(activistData,activistVo);
baseUserService.excelAdd(activistVo);
}
return R.ok();
}
@GetMapping("/leadOutActivist")
public void leadOut(HttpServletResponse response){
//1.获取所有数据列表
List<ActivistData> activistOut = baseUserService.findActivistOut();
//配置下载属性
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName="";
try {
fileName = URLEncoder.encode("积极分子表", "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
try {
EasyExcel.write(response.getOutputStream(),ActivistData.class).sheet("积极分子列表").doWrite(activistOut);
} catch (IOException e) {
e.printStackTrace();
}
}
}
//导出方法二
// @GetMapping("/leadOutActivist")
// public R leadOut(HttpServletRequest request, HttpServletResponse response) throws IOException {
// //1.获取所有数据列表
// List<ActivistData> activistOut = baseUserService.findActivistOut();
// //配置下载属性
// String fileName="";
// try {
// fileName = new String("积极分子表".getBytes(), "ISO-8859-1");
// } catch (UnsupportedEncodingException e) {
// e.printStackTrace();
// }
ExcelExportUtil
// response.addHeader("Content-DisPosition","filename="+fileName+".xlsx");
// ServletOutputStream out = response.getOutputStream();
String filename="f:\\www.xlsx";
// try {
// EasyExcel.write(out,ActivistData.class).sheet("积极分子列表").doWrite(activistOut);
// } catch (Exception e) {
// e.printStackTrace();
// }
//
// return R.ok();
// }
(3)service类
@Transactional
public void excelAdd(ActivistVo activistVo) {
System.out.println(activistVo.toString());
//根据名字查党总支id
Example example1 = new Example(General.class);
Example.Criteria criteria1 = example1.createCriteria();
criteria1.andEqualTo("generalName",activistVo.getGeneralName());
General general = generalMapper.selectOneByExample(example1);
if (general==null) {
throw new PartyException(20001,"该党支部不存在");
}else {
activistVo.setGeneralId(general.getId());
}
//党总支
Example example2 = new Example(Party.class);
Example.Criteria criteria2 = example2.createCriteria();
criteria2.andEqualTo("partyName",activistVo.getPartyName());
Party party = partyMapper.selectOneByExample(example2);
if (party==null) {
throw new PartyException(20001,"该党总支不存在");
}else {
activistVo.setPartyId(party.getId());
}
//党小组
Example example3 = new Example(Group.class);
Example.Criteria criteria3 = example3.createCriteria();
criteria3.andEqualTo("groupName",activistVo.getGroupName());
Group group = groupMapper.selectOneByExample(example3);
if (party==null) {
throw new PartyException(20001,"该党小组不存在");
}else {
activistVo.setGroupId(group.getId());
}
//团支部
Example example4 = new Example(LeagueBranch.class);
Example.Criteria criteria4 = example4.createCriteria();
criteria4.andEqualTo("name",activistVo.getLeagueBranchName());
LeagueBranch leagueBranch = leagueBranchMapper.selectOneByExample(example4);
if (leagueBranch==null){
throw new PartyException(20001,"该团支部不存在");
}else {
activistVo.setLeagueBranchId(leagueBranch.getId());
}
//涉及插入发展表(成为积极分子的时间,培养人1和培养人2)和基本用户表
BaseUser baseUser = new BaseUser();
BeanUtils.copyProperties(activistVo,baseUser);
baseUser.setId(idWorker.nextId()+"");
baseUser.setTypeId(0);//积极分子的类型都是0
baseUserMapper.insert(baseUser);
//如果用户id在发展表里面已经存在了,那么就更新,不在就插入
Development development = new Development();
development.setUserId(baseUser.getId());
development= developmentMapper.selectOne(development);
Development development2 = new Development();
// BeanUtils.copyProperties(activistVo,development2);因为复制的到的目前只有ActivistTime
if (development!=null){
development2.setId(development.getId());
developmentMapper.updateByPrimaryKeySelective(development2);
}else {//新增
if (StringUtils.isNotEmpty(activistVo.getCulture1Sid())){
//根据学号查出这个培养人的id
Example example= new Example(BaseUser.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo("sid",activistVo.getCulture1Sid());
BaseUser baseUser1 = baseUserMapper.selectOneByExample(example);
development2.setCulture1Id(baseUser1.getId());
development2.setCulture1Name(baseUser1.getName());
}
if (StringUtils.isNotEmpty(activistVo.getCulture2Sid())){
//查出这个培养人的id
Example example= new Example(BaseUser.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo("sid",activistVo.getCulture2Sid());
BaseUser baseUser1 = baseUserMapper.selectOneByExample(example);
development2.setCulture2Id(baseUser1.getId());
development2.setCulture2Name(baseUser1.getName());
}
development2.setActivistTime(activistVo.getActivistTime());
development2.setId(idWorker.nextId()+"");
development2.setIsActivist("1");
development2.setUserId(baseUser.getId());
developmentMapper.insert(development2);
}
}
public List<ActivistData> findActivistOut() {
return baseUserMapper.findActivistOut();
}
(4)mapper
@Select("SELECT NAME,sex,id_card idCard,native_place nativePlace,residence,nation,phone,grade,class_num classNum,sid,room," +
" duty,title,aducation,general_name generalName,party_name partyName,group_name groupName,league_branch_name leagueBranchName,petition_confirm petitionConfirm," +
" activist_time activistTime,culture1_name culture1Name,culture1_sid culture1Sid,culture2_name culture2Name,culture2_sid culture2Sid"+
" FROM tb_base_user bu,tb_development d WHERE bu.`id` = d.`user_id` and bu.type_id=0")
public List<ActivistData> findActivistOut();
(5)前端
<el-button type="danger" icon="el-icon-upload2" @click="downExcel" >导出</el-button>
```java
<el-form-item>
<el-upload
ref="upload"
:auto-upload="true"
:on-success="fileUploadSuccess"
:on-error="fileUploadError"
:disabled="importBtnDisabled"
:loading="loading"
:limit="1"
action="http://localhost:9101/excel/leadInActivist.do"
name="file"
accept="application/vnd.ms-excel">
<el-button slot="trigger" size="small" type="primary">导入</el-button>
</el-upload>
</el-form-item>
downExcel(){
window.location.href="/excel/leadOutActivist.do";
}