1.依赖引入
<!--引入EasyPOI-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
2.封装easypoi工具类
package com.wzz.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.hssf.util.HSSFColor;
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;
/**
* Excel导入导出工具类
* @author pangu
*/
public class ExcelUtil {
/**
* 导出工具类
* @param list
* @param title
* @param sheetName
* @param pojoClass
* @param fileName
* @param isCreateHeader
* @param response
*/
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
* @param pojoClass
* @param fileName
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,
HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setStyle(ExcelStyleUtil.class);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
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) {
//TODO设置标题颜色
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层直接调用
/**
* 导出Excel excelVo
* 注意这里暂时只能用get方法,不能用post
*/
@GetMapping("/exportExcel")
@ApiOperation("根据过滤条件导出excel数据")
public void exportExcel(HttpServletResponse response,
@RequestParam(name = "pageNo") int pageNo,
@RequestParam("pageSize") int pageSize,
@RequestParam(name = "unit",required = false) String unit,
@RequestParam(name = "username",required = false) String username,
@RequestParam(name = "truename",required = false) String truename,
@RequestParam(name = "date",required = false) String date) throws IOException {
Evorecorddto queryInfo = new Evorecorddto();
queryInfo.setPageNo(pageNo);
queryInfo.setPageSize(pageSize);
if (unit !=null){
queryInfo.setUnit(unit);
}
if (username!=null){
queryInfo.setUsername(username);
}
if (truename!=null){
queryInfo.setTruename(truename);
}
if (date!=null){
ObjectMapper objectMapper = new ObjectMapper();
Evorecorddto.DateRange dateRange = objectMapper.readValue(date, Evorecorddto.DateRange.class);
queryInfo.setDate(dateRange);
}
// 查询所有答题用户列表
List<excelVo> records = evaluationService.excelprint(queryInfo);
ExcelUtil.exportExcel(records, null, "答题排名", excelVo.class, "答题排名", response);
}
另外需要修改excel导出颜色的话可以修改官方提供的接口
4.修改接口文章来源:https://www.toymoban.com/news/detail-678263.html
package com.wzz.utils;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
/**
* @author:Kevin
* @create: 2023-08-24 10:14
* @Description:
*/
public class ExcelStyleUtil implements IExcelExportStyler {
// 数据行类型
private static final String DATA_STYLES = "dataStyles";
// 标题类型
private static final String TITLE_STYLES = "titleStyles";
//数据行样式
private CellStyle styles;
// 标题样式
private CellStyle titleStyle;
public ExcelStyleUtil(Workbook workbook) {
this.init(workbook);
}
private void init(Workbook workbook) {
this.styles = initStyles(workbook);
this.titleStyle = initTitleStyle(workbook);
}
@Override
public CellStyle getHeaderStyle(short i) {
return null;
}
@Override
public CellStyle getTitleStyle(short i) {
return titleStyle;
}
@Override
public CellStyle getStyles(boolean b, ExcelExportEntity excelExportEntity) {
return styles;
}
@Override
public CellStyle getStyles(Cell cell, int i, ExcelExportEntity excelExportEntity, Object o, Object o1) {
return getStyles(true,excelExportEntity);
}
@Override
public CellStyle getTemplateStyles(boolean b, ExcelForEachParams excelForEachParams) {
return null;
}
/**
* 初始化--标题行样式
* @param workbook
* @return
*/
private CellStyle initTitleStyle(Workbook workbook) {
return buildCellStyle(workbook,TITLE_STYLES);
}
/**
* 初始化--数据行样式
* @param workbook
* @return
*/
private CellStyle initStyles(Workbook workbook) {
return buildCellStyle(workbook,DATA_STYLES);
}
/**
* 设置单元格样式
* @param workbook
* @param type 类型 用来区分是数据行样式还是标题样式
* @return
*/
private CellStyle buildCellStyle(Workbook workbook,String type) {
CellStyle style = workbook.createCellStyle();
// 字体样式
Font font = workbook.createFont();
if(TITLE_STYLES.equals(type)){
// 背景色
style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
font.setFontHeightInPoints((short)12);
font.setBold(true);
font.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
}
if(DATA_STYLES.equals(type)){
font.setFontHeightInPoints((short)10);
}
font.setFontName("Courier New");
style.setFont(font);
// 设置底边框
style.setBorderBottom(BorderStyle.THIN);
// 设置左边框
style.setBorderLeft(BorderStyle.THIN);
// 设置右边框;
style.setBorderRight(BorderStyle.THIN);
// 设置顶边框;
style.setBorderTop(BorderStyle.THIN);
// 设置底边颜色
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
// 设置左边框颜色;
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
// 设置右边框颜色;
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
// 设置顶边框颜色;
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
}
5.前端代码文章来源地址https://www.toymoban.com/news/detail-678263.html
//导出excel
exportExcel(){
//https://slgstu.top/api/common/exportExcel
axios.get("https://slgstu.top/api/common/exportExcel",{
params: this.queryInfo,
headers: { 'Content-Type': 'application/json,charset=utf-8'},
responseType: 'blob', //二进制流
}).then(res => {
console.log(res)
this.$notify({
title: '提示',
message: '正在导出,请稍等!'
});
// console.log(res);
// let blob = new Blob([res], { type: 'application/vnd.ms-excel,charset=utf-8' });
// let url =window.URL.createObjectURL(blob);
// let link = document.createElement('a');
// link.download = '答题记录.xlsx';
// link.href = url;
// link.click();
downloadFile(res.data, "答题排名", 'xlsx')
});
},
到了这里,关于实战分享之springboot+easypoi快速业务集成的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!