1、maven坐标
<!--excel操作 springboot-easypoi-->
<!-- 导入和导出-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.0.0</version>
</dependency>
2、先上工具类
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.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import lombok.extern.slf4j.Slf4j;
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.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Excel导入导出工具类
*/
@Slf4j
public class ExcelUtils {
/**
* 允许导出的最大条数
*/
private static final Integer EXPORT_EXCEL_MAX_NUM = 10000;
// /**
// * excel 导出 (本地)
// * @param list 数据列表
// * @param excelType HSSF, XSSF
// *
// */
// public static <T> void exportExcel(File file,List<T> list,ExcelType excelType) throws IOException {
// FileOutputStream fos = new FileOutputStream(file);
// Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(null,file.getName(), excelType),
// UserEntity.class, list);
//
// workbook.write(fos);
// workbook.close();
// }
/**
* excel 导出
*
* @param list 数据列表
* @param fileName 导出时的excel名称
* @param response
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName,ExcelType excelType, HttpServletResponse response) throws IOException {
defaultExport(list, fileName,excelType, response);
}
/**
* 默认的 excel 导出
*
* @param list 数据列表
* @param fileName 导出时的excel名称
* @param response
*/
private static void defaultExport(List<Map<String, Object>> list, String fileName,ExcelType excelType, HttpServletResponse response) throws IOException {
//把数据添加到excel表格中
Workbook workbook = ExcelExportUtil.exportExcel(list, excelType);
downLoadExcel(fileName, response, workbook);
}
/**
* excel 导出
*
* @param list 数据列表
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param response
* @param exportParams 导出参数(标题、sheet名称、是否创建表头,表格类型)
*/
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
//把数据添加到excel表格中
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
downLoadExcel(fileName, response, workbook);
}
/**
* excel 导出
*
* @param list 数据列表
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param exportParams 导出参数(标题、sheet名称、是否创建表头,表格类型)
* @param response
*/
public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* excel 导出
*
* @param list 数据列表
* @param title 表格内数据标题
* @param sheetName sheet名称
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
//给文件名拼接上日期
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateString = formatter.format(new Date());
fileName = fileName + dateString;
//判断导出数据是否为空
if (list == null) {
list = new ArrayList<>();
}
//判断导出数据数量是否超过限定值
if (list.size() > EXPORT_EXCEL_MAX_NUM) {
title = "导出数据行数超过:" + EXPORT_EXCEL_MAX_NUM + "条,无法导出、请添加导出条件!";
list = new ArrayList<>();
}
//获取导出参数
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
//设置导出样式
// exportParams.setStyle(ExcelStyleUtil.class);
//设置行高
exportParams.setHeight((short) 6);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* 根据模板生成excel后导出
*
* @param templatePath 模板路径
* @param map 数据集合
* @param fileName 文件名
* @param response
* @throws IOException
*/
public static void exportExcel(TemplateExportParams templatePath, Map<String, Object> map, String fileName, HttpServletResponse response) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(templatePath, map);
downLoadExcel(fileName, response, workbook);
}
/**
* excel下载
*
* @param fileName 下载时的文件名称
* @param response
* @param workbook excel数据
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));
workbook.setForceFormulaRecalculation(true); //强制开启excel公式计算
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param file excel文件
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
return importExcel(file, 1, 1, pojoClass);
}
/**
* excel 导入
*
* @param file 上传的文件
* @param titleRows 表格内数据标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (file == null) {
return null;
}
try {
return importExcel(file.getInputStream(), titleRows, headerRows, pojoClass);
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param inputStream 文件输入流
* @param titleRows 表格内数据标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (inputStream == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setNeedSave(true);
try {
ExcelImportResult<T> result = ExcelImportUtil.importExcelMore(inputStream, pojoClass, params);
// List<T> objects = ExcelImportUtil.importExcel(inputStream, pojoClass, params);
return result.getList();
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
}
3、controller
/**
* 导入excel数据
* @param file
* @return
* @throws IOException
*/
@PostMapping( "/import")
@ApiOperation(value = "模板数据入库")
@ApiImplicitParams({
@ApiImplicitParam(name = "type",value = "1、背景值 2、历史值")
})
public CommonResult importExcel(@RequestParam("file") MultipartFile file,String type) throws IOException {
List<HwWD> list = ExcelUtils.importExcel(file, HwWD.class);
if(list == null || list.size() == 0){
return CommonResult.ok( "数据为空!请核对后在导入!");
}
for (HwWqjhbjztD hwWqjhsinfB : list) {
//将数据写入到数据库中
hwDao.insertRow(hwWqjhsinfB,type);
}
return CommonResult.ok( "导入成功");
}
/**
* 导出数据
* @param response
* @throws IOException
*/
@GetMapping("/export")
@ApiOperation(value = "模板数据导出")
public void exportExcel( HttpServletResponse response,String year) throws IOException {
//查询出当前年份对应的测站列表
List<HashMap> list = jhService.listByYear(year);
Map map = new HashMap();
map.put("data",list);
TemplateExportParams templatePath = new TemplateExportParams(templateFile);
ExcelUtils.exportExcel(templatePath, map, "监测数据导入模板", response);
}
4、导出模板配置
此处循环你想要写入的数据