EasyExcel Springboot实现导入导出excel

 <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>${easyexcel-version}</version>
        </dependency>
 <easyexcel-version>2.2.10</easyexcel-version>     
复制

/**
 * 专题名称
 */
@ExcelProperty(index = 0,value = "专题名称")
@Column(name = "special", nullable = true,length = 255)
private String special;


/**
 * 标题名称
 */
@ExcelProperty(index = 1,value = "标题名称")
@Column(name = "title", nullable = true ,length = 255)
private String title;


/**
 * 技术栈ID
 */
@ExcelIgnore
@Column(name = "technology_id", nullable = true, length = 36)
private String technologyId;
复制

/**
* @Description: 导出
* @Author: Kra
* @Date: 2021/8/19 13:22
*/
@Override
public void export(HttpServletResponse response) throws IOException {
// 注意：easyexcel 最多显示0-1048576条数据 超过会报错需要处理
// 1.获取面经题库所有的条数
Integer number = questionBankRepository.getCount();
// 2.计算按每页五万条数据可以存放几页
Integer sheetPage = number % excelPageNumber == 0 ?number/excelPageNumber : number/excelPageNumber + 1;
ServletOutputStream out = response.getOutputStream();
log.info("开始导出{}数据...",sheetName);
// 3.创建excel写入对象
ExcelWriter excelWriter = EasyExcel.write(out, QuestionBank.class).build();
for (Integer i = 1; i <= sheetPage; i++) {
Page<QuestionBank> questionBanks = questionBankRepository.findAll(LimitParaUtils.getPageRequest(i, excelPageNumber));
// 4.1.创建sheet
WriteSheet sheet = EasyExcel.writerSheet("sheet"+i).build();
// 4.2.写入
excelWriter.write(questionBanks.getContent(), sheet);
}
//通知浏览器以附件的形式下载处理，设置返回头要注意文件名有中文
response.setHeader("Content-disposition", "attachment;filename=" + new String( sheetName.getBytes("gb2312"), "ISO8859-1" ) + suffix);
// 5.关闭
excelWriter.finish();
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
out.flush();
log.info("导出完成!");
}
复制

questionBankRepository.getCount();
复制

@Query(value = "select count(1) from t_question_bank",nativeQuery = true)
Integer getCount();
复制

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.myapestech.education.pojo.edu.QuestionBank;


import java.util.ArrayList;
import java.util.List;


/**
* @Author: Kra
* @Version: 1.0
* @Date: 2021/08/19/ 15:33
* @Description: excel 读取监听类
*/
public class EasyExcelListenner extends AnalysisEventListener<QuestionBank> {


   private List<QuestionBank> datas = new ArrayList<>();
   /**
    * When analysis one row trigger invoke function.
    *
    * @param data   one row value. Is is same as {@link AnalysisContext#readRowHolder()}
    * @param context
    */
   @Override
   public void invoke(QuestionBank data, AnalysisContext context) {
       datas.add(data);
  }


   public List<QuestionBank> getDatas() {
       return datas;
  }


   public void setDatas(List<QuestionBank> datas) {
       this.datas = datas;
  }


   /**
    * if have something to do after all analysis
    *
    * @param context
    */
   @Override
   public void doAfterAllAnalysed(AnalysisContext context) {
       // 读取结束执行的操作
  }
}
复制

/**
* @Description: 导入
* @Author: Kra
* @Date: 2021/8/19 15:06
*/
@Override
@Transactional
public void imports(MultipartFile file) throws IOException {
//1.获取文件流
InputStream inputStream = file.getInputStream();
//2.实例化实现了AnalysisEventListener接口的类
EasyExcelListenner listener = new EasyExcelListenner();
//3.创建excel读取对象
ExcelReader excelReader = EasyExcel.read(inputStream, QuestionBank.class, listener).build();
//4.读取有几个sheet表
Integer sheetNum = excelReader.getSheets().size();
log.info("开始读取excel...");
for (Integer i = 0; i < sheetNum; i++) {
ReadSheet readSheet = EasyExcel.readSheet(i).build();
excelReader.read(readSheet);
excelReader.finish();
}
log.info("读取excel完成！");
//5.获取数据
List<QuestionBank> list = listener.getDatas();
//6.判断重复则不插入
log.info("开始写入数据库...");
for (QuestionBank q : list) {
Example example = Example.of(q);
boolean exists = questionBankRepository.exists(example);
if(!exists){
q.setCreatedTime(new Date());
questionBankRepository.save(q);
}
continue;
}
log.info("写入完成！");
}
复制