点击官方文档
EasyPoiUtil 工具类
package com.hollysys.server.common.excel;
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 com.hollysys.server.vo.ExcelEmployeeVo;
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;
/**
* @Auther: HeJD
* @Date: 2018/10/9 13:54
* @Description:
*/
public class EasyPoiUtil {
/**
* 功能描述:复杂导出Excel,包括文件名以及表名。创建表头
*
* @author HeJD
* @date 2018/10/9 13:54
* @param list 导出的实体类
* @param title 表头名称
* @param sheetName sheet表名
* @param pojoClass 映射的实体类
* @param isCreateHeader 是否创建表头
* @param fileName
* @param response
* @return
*/
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);
}
/**
* 功能描述:复杂导出Excel,包括文件名以及表名,不创建表头
*
* @author HeJD
* @date 2018/10/9 13:54
* @param list 导出的实体类
* @param title 表头名称
* @param sheetName sheet表名
* @param pojoClass 映射的实体类
* @param fileName
* @param response
* @return
*/
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));
}
/**
* 功能描述:Map 集合导出
*
* @author HeJD
* @date 2018/10/9 13:54
* @param list 实体集合
* @param fileName 导出的文件名称
* @param response
* @return
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
defaultExport(list, fileName, response);
}
/**
* 功能描述:默认导出方法
*
* @author HeJD
* @date 2018/10/9 13:54
* @param list 导出的实体集合
* @param fileName 导出的文件名
* @param pojoClass pojo实体
* @param exportParams ExportParams封装实体
* @param response
* @return
*/
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);
}
}
/**
* 功能描述:Excel导出
*
* @author HeJD
* @date 2018/10/9 15:35
* @param fileName 文件名称
* @param response
* @param workbook Excel对象
* @return
*/
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 RuntimeException(e);
}
}
/**
* 功能描述:默认导出方法
*
* @author HeJD
* @date 2018/7/23 15:33
* @param list 导出的实体集合
* @param fileName 导出的文件名
* @param response
* @return
*/
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);
}
/**
* 功能描述:根据文件路径来导入Excel
*
* @author HeJD
* @date 2018/10/9 14:17
* @param filePath 文件路径
* @param titleRows 表标题的行数
* @param headerRows 表头行数
* @param pojoClass Excel实体类
* @return
*/
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 RuntimeException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 功能描述:根据接收的Excel文件来导入Excel,并封装成实体类
*
* @author HeJD
* @date 2018/10/9 14:17
* @param file 上传的文件
* @param titleRows 表标题的行数
* @param headerRows 表头行数
* @param pojoClass Excel实体类
* @return
*/
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 RuntimeException("excel文件不能为空");
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
return list;
}
}
设置表头 NewExcelExportStylerDefaultImpl 工具类
package com.hollysys.server.common.excel;
import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;
/**
* @author :xiezhijian
* @date :Created in 2020/3/18 11:55
* @description:表头自定义
* @parameter:
* @modified By:
* @version: $
*/
public class NewExcelExportStylerDefaultImpl extends AbstractExcelExportStyler
implements IExcelExportStyler {
public NewExcelExportStylerDefaultImpl(Workbook workbook) {
super.createStyles(workbook);
}
@Override
public CellStyle getTitleStyle(short color) {
CellStyle titleStyle = workbook.createCellStyle();
Font font = this.workbook.createFont();
font.setBold(true); // 字体加粗
titleStyle.setFont(font);
titleStyle.setAlignment(HorizontalAlignment.CENTER);//居中
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
titleStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//设置颜色(黄色)
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setBorderRight(BorderStyle.THIN);//设置右边框
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
titleStyle.setWrapText(true);
return titleStyle;
}
@Override
public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
CellStyle style = workbook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setDataFormat(STRING_FORMAT);
if (isWarp) {
style.setWrapText(true);
}
return style;
}
@Override
public CellStyle getHeaderStyle(short color) {
CellStyle titleStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 12);
titleStyle.setFont(font);
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
return titleStyle;
}
@Override
public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
CellStyle style = workbook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setDataFormat(STRING_FORMAT);
if (isWarp) {
style.setWrapText(true);
}
return style;
}
}
VO实体类 对应的是表的列名
package com.hollysys.server.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import javax.validation.constraints.NotBlank;
/**
* @Author: ligang
* @Date: 2019/12/11 9:48
*/
@Data
public class ExcelEmployeeVo {
@Excel(name = "序号", orderNum = "0")
private Integer rowNum;
//isColumnHidden = true 隐藏列
@Excel(name = "id",isColumnHidden = true)
private Integer id;
@Excel(name = "姓名", orderNum = "1")
private String name;
@Excel(name = "工资号", orderNum = "2")
private String salaryNum;
@Excel(name = "性别", orderNum = "3")
private String gender;
@Excel(name = "单位", orderNum = "4",width = 20)
private String departmentName;
@Excel(name = "职务", orderNum = "5")
private String roleName;
@Excel(name = "资格证书", orderNum = "6",width = 20,isWrap = true)
private String licenses;
}
Controller 1 未设置表头版本
@ApiOperation("根据节点导出人员信息Excel表")
@GetMapping("/exportExcel")
public ApiResultData exportExcel(@RequestParam("path") String path,@RequestParam("deptName") String deptName, HttpServletResponse response) {
// 查询要导出的数据
List<ExcelEmployeeVo> list = pmsEmployeeService.getListEmployee(path);
EasyPoiUtil.exportExcel(list,deptName+"人员信息","人员信息",ExcelEmployeeVo.class,deptName+"人员信息.xls",response);
return new ApiResultData(1,"成功");
}
Controller 2 设置表头版本
/**
*根据节点导出人员信息Excel表
* @param path
* @param deptName
* @param response
* @return
*/
@ApiOperation("根据节点导出人员信息Excel表")
@GetMapping("/exportExcel")
public ApiResultData exportExcel(@RequestParam("path") String path,@RequestParam("deptName") String deptName, HttpServletResponse response) {
// 查询要导出的数据
List<ExcelEmployeeVo> list = pmsEmployeeService.getListEmployee(path);
// 主要导出方法
ExportParams exportParams = new ExportParams();
exportParams.setTitle(deptName+"人员信息");
exportParams.setSheetName("人员信息");
exportParams.setStyle(NewExcelExportStylerDefaultImpl.class);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, ExcelEmployeeVo.class, list);
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader(
"Content-Disposition", "attachment;filename=" + URLEncoder.encode(deptName+"人员信息.xls", "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
return new ApiResultData(1,"成功");
}
Service 查询导出Excel人员数据
List<ExcelEmployeeVo> getListEmployee(String path);
ServiceImpl
/**
* 获取表格
*
* @param path
* @return
*/
@Override
public List<ExcelEmployeeVo> getListEmployee(String path) {
// 获取部门ID
List<Integer> deptIds = pmsDepartmentMapper.getDeptIdsByPath(path);
// 获取表格
List<ExcelEmployeeVo> list = pmsEmployeeMapper.getExcelList(deptIds);
for (ExcelEmployeeVo excelEmployeeVo : list) {
List<Integer> licenseIds =
employeeLicensesMapper.getLicensesByEmployee(excelEmployeeVo.getId());
// 查询证书名称
if (licenseIds.size() > 0) {
List<String> licensesName = pmsLicensesMapper.getLicensesListById(licenseIds);
String strLicenses = String.join("\n", licensesName);
excelEmployeeVo.setLicenses(strLicenses);
}
}
return list;
}
导出时分割
@Override
public List < ExcelDevRecordVo > getRecordListByStation ( Integer stationId) {
List < ExcelDevRecordVo > excelDevRecordVos =
pmsDevRecordMapper. getRecordListByStationId ( stationId) ;
for ( ExcelDevRecordVo excelDevRecordVo : excelDevRecordVos) {
if ( excelDevRecordVo. getProperty ( ) != null ) {
List < Integer > pids = JSON. parseArray ( excelDevRecordVo. getProperty ( ) , Integer . class ) ;
if ( pids. size ( ) > 0 ) {
List < String > propertyList = pmsCfgPropertyMapper. getNamesByIds ( pids) ;
String propertyStr = String . join ( ";" , propertyList) ;
excelDevRecordVo. setProperty ( propertyStr) ;
}
}
if ( excelDevRecordVo. getContent ( ) != null ) {
List < Integer > tids = JSON. parseArray ( excelDevRecordVo. getContent ( ) , Integer . class ) ;
if ( tids. size ( ) > 0 ) {
List < String > taskList = pmsCfgTaskContentMapper. getNamesByIds ( tids) ;
String contentStr = String . join ( ";" + "\n" , taskList) ;
excelDevRecordVo. setContent ( contentStr) ;
}
}
if ( excelDevRecordVo. getChangeArea ( ) != null ) {
List < Integer > cids = JSON. parseArray ( excelDevRecordVo. getChangeArea ( ) , Integer . class ) ;
if ( cids. size ( ) > 0 ) {
List < String > devList = pmsDevRecordMapper. getNamesByIds ( cids) ;
String devStr = String . join ( ";" , devList) ;
excelDevRecordVo. setChangeArea ( devStr) ;
}
}
if ( excelDevRecordVo. getDevPosition ( ) != null ) {
List < Integer > pids = JSON. parseArray ( excelDevRecordVo. getDevPosition ( ) , Integer . class ) ;
if ( pids. size ( ) > 0 ) {
List < String > devList = pmsCfgPositionMapper. getNamesByIds ( pids) ;
String devStr = String . join ( ";" , devList) ;
excelDevRecordVo. setDevPosition ( devStr) ;
}
}
}
return excelDevRecordVos;
}
Mapper
/**
* 查找节点下的单位id列表
* @return List
*/
List<Integer> getDeptIdsByPath(String path);
/**
* 查询Excel人员导出数据
*
* @param deptIds
* @return
*/
List<ExcelEmployeeVo> getExcelList(@Param("deptIds") List<Integer> deptIds);
Mapper.xml
<select id="getDeptIdsByPath" resultType="java.lang.Integer">
SELECT id FROM pms_department WHERE path LIKE CONCAT(#{path},'%') AND is_del = 0
</select>
<select id="getExcelList" resultType="com.hollysys.server.vo.ExcelEmployeeVo">
SELECT
@rownum := @rownum + 1 AS rownum, res.* FROM
(SELECT
e.id,
e.`name`,
e.salary_num salaryNum,
e.gender,
d.`name` departmentName,
r.`name` roleName
FROM
pms_employee e
LEFT JOIN pms_role r ON e.role_id = r.id
LEFT JOIN pms_department d ON e.department_id = d.id
WHERE
e.is_del = 0 AND d.is_del = 0 AND e.name != 'admin' AND e.is_public = 0
<if test="deptIds != null and deptIds.size()>0 ">
AND e.department_id IN
<foreach collection="deptIds" item="deptId" index="index" open="(" close=")" separator=",">
#{deptId}
</foreach>
</if>
ORDER BY
e.gmt_create DESC) res , ( SELECT @rownum := 0 ) r
</select>
复杂表头
复杂表头