EasyPoi 导出表格并设置表头

点击官方文档

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>

复杂表头

复杂表头