EasyPOI
1、pom文件添加依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.2</version>
</dependency>
2、EasyPOI工具类
package com.hanshuai.system.utils;
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.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
/**
* @author hs
* @title: EasyPoiUtils
* @description: TODO
* @date 2021/4/15 8:32
*/
public class EasyPoiUtils {
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);
}
/**
*
* @param list 数据列表
* @param title 标题
* @param sheetName sheet名字
* @param pojoClass 导出对象的Class类型
* @param fileName 文件名
* @param response
*/
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 File(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());
System.out.println(e.getMessage());
}
return list;
}
}
3、Controller层
@GetMapping("export")
@LogAnnotation(title = "交易查询", action = "交易查询导出")
@RequiresPermissions("trade:export:list")
public void export(HttpServletResponse response) {
List<TEtcTradePay> tEtcTradePays = tEtcTradePayService.getAll();
if(tEtcTradePays == null || tEtcTradePays.size() == 0){
return;
}
int i =1;
for (TEtcTradePay cc : tEtcTradePays) {
cc.setId(i++);
}
EasyPoiUtils.exportExcel(tEtcTradePays, "停车场入场纪录", "导出sheet1", TEtcTradePay.class, "停车场入场纪录.xls", response);
}
4、实体类
package com.hanshuai.business.bean;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;
import java.util.Date;
/**
* @title: TEtcTradePay
* @description: TODO
* @author 韩帅
* @date 2021/5/13 9:36
*/
@ExcelTarget("t_etc_trade_pay")
@Data
public class TEtcTradePay {
@Excel(name = "序号")
private Integer id;
/**
* 停车场编号
*/
@Excel(name = "停车场ID", orderNum = "0",width = 10)
private Integer parkId;
/**
* 停车场名称
*/
@Excel(name = "停车场名称", orderNum = "1",width = 20)
private String parkName;
/**
* 系统流水号
*/
@Excel(name = "系统流水号", orderNum = "2",width = 15)
private Long sysTranNo;
/**
* 统一下单流水号
*/
@Excel(name = "统一下单流水号", orderNum = "3",width = 15)
private Long prePayNo;
/**
* 创建时间
*/
@Excel(name = "创建时间", orderNum = "34",width = 20)
private String createTime;
}
5、layui前端ajax
因为需要token,所以用原生的ajax进行发送请求
//不需要token,直接这样写就行
window.location.href=api+'report/laneBlack?userId='+userId;
//导出
var ext = function(){
var token = CoreUtil.getData("access_token");
var url = api +'trade/export';
var xhr = new XMLHttpRequest();
xhr.responseType = "arraybuffer";
xhr.open("GET", url, true);
xhr.onload = function () {
const blob = new Blob([this.response], {type:"application/vnd.ms-excel"});
if(blob.size < 1){
layer.msg('导出失败,导出的内容为空!');
return;
}
if(window.navigator.msSaveOrOpenBlob) {
navigator.msSaveOrOpenBlob(blob, 'test.xls')
} else {
const aLink = document.createElement('a');
aLink.style.display = 'none';
aLink.href = window.URL.createObjectURL(blob);
aLink.download = '黑名单纪录';
document.body.appendChild(aLink);
aLink.click();
document.body.removeChild(aLink);
return;
}
}
xhr.setRequestHeader("Authorization", token);
xhr.setRequestHeader("Content-Type", "application/json");
xhr.send();
}
6、解析导入得Excel(批量添加)
@PostMapping("batchAdd")
@LogAnnotation(title = "白名单管理", action = "批量添加白名单数据")
@RequiresPermissions("white:batchAdd")
public DataResult batchAdd(@RequestParam("file") MultipartFile file, HttpServletRequest request) throws Exception{
ImportParams importParams = new ImportParams();
importParams.setHeadRows(1);
importParams.setTitleRows(1);
importParams.setNeedVerify(false);
ExcelImportResult<TWhite> result = ExcelImportUtil.importExcelMore(file.getInputStream(), TWhite.class, importParams);
List<TWhite> list = result.getList();
String token = request.getHeader(Constant.ACCESS_TOKEN);
Integer userId = JwtTokenUtils.getUserId(token);
tWhiteService.batchAdd(list,userId);
return DataResult.BATCHADD_SUCCESS;
}