easy_refresh 自定义图片 easypoi自定义样式

转载

我心依旧 2024-04-29 15:21:28

文章标签 easy_refresh 自定义图片 java 数据 ide 文章分类 架构后端开发

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

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

easy_refresh 自定义图片 easypoi自定义样式_ide

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
*/
@Slf4j
public 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
@NoArgsConstructor
public 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);

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。