easypoi-基于POI,使用超简单,可定制的报表工具_Java_MJ的博客-CSDN博客

研究了一整天Excel导入导出的博客,发现了easypoi这款工具,他是基于POI,具有使用简单,有一定可定制化的优点,满足报表不是很炫酷,数据量不是很大的数据导出(可能OOM)。官网:http://easypoi.mydoc.io/

1、效果预览。定制了title、header、底部签名效果

2、依赖

  <!--easypoi-->      <dependency>          <groupId>cn.afterturn</groupId>          <artifactId>easypoi-base</artifactId>          <version>3.2.0</version>      </dependency>      <dependency>          <groupId>cn.afterturn</groupId>          <artifactId>easypoi-web</artifactId>          <version>3.2.0</version>      </dependency>      <dependency>          <groupId>cn.afterturn</groupId>          <artifactId>easypoi-annotation</artifactId>          <version>3.2.0</version>      </dependency>

3、定制title、header样式,该类会在工具类EasyPoiUtil中配置

 /** * <p> * Description: 自定义title和header的样式-标题12号加粗,header11号带25%灰背景色 * 覆盖原有样式继承{@link ExcelExportStylerDefaultImpl } 实现{@link IExcelExportStyler} * 使用方法:{@link EasyPoiUtil#exportExcel(Collection, String, String, String, Class, boolean, boolean, HttpServletResponse)} * </p> * * @author majun * @version 1.0 * @date 2019-06-29 23:33 */public class KscExcelStyle extends ExcelExportStylerDefaultImpl implements IExcelExportStyler {       public KscExcelStyle(Workbook workbook) {        super(workbook);    }     /**     * 覆盖此方法实现自定义HeaderStyle     * @param i     * @return     */     @Override    public CellStyle getHeaderStyle(short i) {        CellStyle style = getBaseCellStyle(workbook);        style.setFont(getFont(workbook, (short) 12, true));        return style;         /**         * 覆盖此方法实现自定义TitleStyle         * @param i         * @return         */    }    @Override    public CellStyle getTitleStyle(short i) {        CellStyle style = getBaseCellStyle(workbook);        style.setFont(getFont(workbook, (short) 11, false));        //背景色        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);        return style;    }     private CellStyle getBaseCellStyle(Workbook workbook) {        CellStyle style = workbook.createCellStyle();        //下边框        style.setBorderBottom(BorderStyle.THIN);        //左边框        style.setBorderLeft(BorderStyle.THIN);        //上边框        style.setBorderTop(BorderStyle.THIN);        //右边框        style.setBorderRight(BorderStyle.THIN);        //水平居中        style.setAlignment(HorizontalAlignment.CENTER);        //上下居中        style.setVerticalAlignment(VerticalAlignment.CENTER);        //设置自动换行        style.setWrapText(true);        return style;    }     /**     * 字体样式     *     * @param size   字体大小     * @param isBold 是否加粗     * @return     */    private Font getFont(Workbook workbook, short size, boolean isBold) {        Font font = workbook.createFont();        //字体样式        font.setFontName("宋体");        //是否加粗        font.setBold(isBold);        //字体大小        font.setFontHeightInPoints(size);        return font;    }      /**     * 数据行样式     *     * @param parity 可以用来表示奇偶行     * @param entity 数据内容     * @return 样式     *//*    @Override    public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {        CellStyle style = getBaseCellStyle(workbook);        style.setFont(getFont(workbook, (short) 11, false));        style.setDataFormat(STRING_FORMAT);        return style;    }*/     /**     * 获取样式方法     *     * @param dataRow 数据行     * @param obj     对象     * @param data    数据     *//*    @Override    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {        return getStyles(true, entity);    }*/ }

4、工具类编写,三个静态方法:导出一个,导入两个。导出定制了底部签名,导入时需要通过设置titleRows,headerRows及ignoreLastRowCount三个参数来忽略Excel的title、header及最后的签名行

  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.exception.excel.ExcelImportException;import com.construn.vehicle.common.constant.CommonConstant;import com.construn.vehicle.common.constant.ErrorEnum;import com.construn.vehicle.common.exception.CommonException;import io.swagger.models.auth.In;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;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.Collection;import java.util.List; /*** <p>Description:基于easypoi的Excel导出工具类</p>* @author  majun* @date   2019/6/30 0030 10:06*/@Slf4jpublic class EasyPoiUtil {     public static void exportExcel(Collection<?> data, String title, String sheetName,  String fileName,Class<?> pojoClass, boolean isCreateHeader, boolean isNeedSignature , HttpServletResponse response) throws CommonException {        ExportParams exportParams = new ExportParams(title, sheetName);        exportParams.setCreateHeadRows(isCreateHeader);        exportParams.setStyle(KscExcelStyle.class);        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, data);        //如果需页底要有签名,则需要合并单元格并为其设置样式和签名模板内容        if (isNeedSignature) {            Sheet sheet = workbook.getSheet(sheetName);            int lastRowNum = sheet.getLastRowNum();            short lastCellNum = sheet.getRow(2).getLastCellNum();            CellRangeAddress rangeAddress = new CellRangeAddress(lastRowNum+1 , lastRowNum+2 , 0, lastCellNum-1);            sheet.addMergedRegion(rangeAddress);            Cell signatureCell = sheet.createRow(lastRowNum + 1).createCell(0);            CellStyle signatureCellStyle = workbook.createCellStyle();            signatureCellStyle.setAlignment(HorizontalAlignment.RIGHT);            signatureCell.setCellStyle(signatureCellStyle);            signatureCell.setCellValue("签名:             日期:               ");        }        //使用response将workbook内容写回浏览器        downLoadExcel(fileName,response,workbook);     }      private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws CommonException {        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 CommonException(CommonConstant.FAILED,"导出Excel异常");        }    }     /**     *     * @param filePath 文件路径     * @param titleRows 标题所占行数     * @param headerRows header所占行数     * @param ignoreLastRowCount 忽略最后多少行,如忽略最后一行“签名”     * @param pojoClass 用哪个model来装数据     * @param <T>     * @return     * @throws CommonException     * @throws IOException     */     public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Integer ignoreLastRowCount, Class<T> pojoClass) throws CommonException, IOException {        ImportParams params = new ImportParams();        params.setTitleRows(titleRows);        params.setHeadRows(headerRows);        params.setLastOfInvalidRow(ignoreLastRowCount);//最后一行        try {            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);        }catch (ExcelImportException e){            throw new CommonException(CommonConstant.FAILED,"请传入规范的Excel文件");        } catch (Exception e) {            e.printStackTrace();            throw new CommonException(ErrorEnum.UNKNOWN_ERROR);        }     }    /**     *     * @param file 文件对象     * @param titleRows 标题所占行数     * @param headerRows header所占行数     * @param ignoreLastRowCount 忽略最后多少行,如忽略最后一行“签名”     * @param pojoClass 用哪个model来装数据     * @param <T>     * @return     * @throws CommonException     * @throws IOException     */     public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Integer ignoreLastRowCount, Class<T> pojoClass) throws CommonException {        if (file==null) {            throw new CommonException(CommonConstant.FAILED,"请选择你要导入的Excel文件");        }        ImportParams params = new ImportParams();        params.setTitleRows(titleRows);        params.setHeadRows(headerRows);        try {            return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);        }catch (ExcelImportException e){            throw new CommonException(CommonConstant.FAILED,"请传入规范的Excel文件");        } catch (Exception e) {            e.printStackTrace();            throw new CommonException(ErrorEnum.UNKNOWN_ERROR);        }     } }

5、数据model跟Excel的列是通过@Excel注解来完成了,该注解还提供了其它熟悉来设置一些样式,如值得替换,1_男,2_女。详见官网http://easypoi.mydoc.io/

@Getter@Setter@AllArgsConstructor@NoArgsConstructorpublic class EntpRenterInfoVO implements Serializable{    private static final long serialVersionUID = 1L;     private String id;     @Excel(name = "序号", orderNum = "1", width = 5.0)    private int sortNum;     @Excel(name = "企业名称", orderNum = "2", width = 25.0)    private String entpName;     /**     * 信用代码     */    @Excel(name = "信用代码", orderNum = "5", width = 20.0)    private String creditCode;     /**     * 企业地址     */    @Excel(name = "注册地址", orderNum = "6", width = 30.0)    private String address;     /**     * 对账人姓名     */    @Excel(name = "对账人姓名", orderNum = "3", width = 20)    private String contactName;    /**     *对账人联系电话     */    @Excel(name = "联系电话", orderNum = "4", width = 30)    private String contactPhone;}

6、使用姿势如下,有没有一句话解决报表导入导出的痛处

//导出  EasyPoiUtil.exportExcel(list,                exportName,                exportName,                entpName+exportName+".xls",                EntpRenterInfoVO.class,                true,                true,                response);//本地文件导入1        List<EntpRenterInfoVO> list1 = EasyPoiUtil.importExcel("D:/test2.xls", 1, 1, 1, EntpRenterInfoVO.class);//浏览器上传导入2        MultipartFile file=null;//该参数是springMVC的方式接收的文件对象        List<EntpRenterInfoVO> list2 = EasyPoiUtil.importExcel(file, 1, 1, 1, EntpRenterInfoVO.class);

Original url: Access
Created at: 2020-03-22 23:26:41
Category: default
Tags: none

请先后发表评论
  • 最新评论
  • 总共0条评论