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-23 00:33:58
Category: default
Tags: none
未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到
最新评论