精华内容
下载资源
问答
  • EasyPoi

    2020-12-04 15:42:20
    EasyPoi 1.0 通用样式 2.0 样式文件 package com.gcb.platform.utils.easyPoi; import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity; import ...

    EasyPoi

    1.0 通用样式

    image-20201125103427010

    2.0 样式文件

    package com.gcb.platform.utils.easyPoi;
    
    import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
    import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
    import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.BuiltinFormats;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.apache.poi.ss.usermodel.Workbook;
    
    /**
     * author Mr.ALan
     * DESCRIPTION 设置导出表格的基础样式
     * create 2019/12/25
     */
    
    public class ExcelStyleUtil implements IExcelExportStyler {
        private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
        private static final short FONT_SIZE_TEN = 10;
        private static final short FONT_SIZE_ELEVEN = 11;
        private static final short FONT_SIZE_TWELVE = 12;
        /**
         * 大标题样式
         */
        private CellStyle headerStyle;
        /**
         * 每列标题样式
         */
        private CellStyle titleStyle;
        /**
         * 数据行样式
         */
        private CellStyle styles;
    
        public ExcelStyleUtil(Workbook workbook) {
            this.init(workbook);
        }
    
        /**
         * 初始化样式
         *
         * @param workbook
         */
        private void init(Workbook workbook) {
            this.headerStyle = initHeaderStyle(workbook);
            this.titleStyle = initTitleStyle(workbook);
            this.styles = initStyles(workbook);
        }
    
        /**
         * 大标题样式
         *
         * @param color
         * @return
         */
        @Override
        public CellStyle getHeaderStyle(short color) {
            return headerStyle;
        }
    
        /**
         * 每列标题样式
         *
         * @param color
         * @return
         */
        @Override
        public CellStyle getTitleStyle(short color) {
            return titleStyle;
        }
    
        /**
         * 数据行样式
         *
         * @param parity 可以用来表示奇偶行
         * @param entity 数据内容
         * @return 样式
         */
        @Override
        public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
            return styles;
        }
    
        /**
         * 获取样式方法
         *
         * @param dataRow 数据行
         * @param obj     对象
         * @param data    数据
         */
        @Override
        public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
            return getStyles(true, entity);
        }
    
        /**
         * 模板使用的样式设置
         */
        @Override
        public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
            return null;
        }
    
        /**
         * 初始化--大标题样式
         *
         * @param workbook
         * @return
         */
        private CellStyle initHeaderStyle(Workbook workbook) {
            CellStyle style = getBaseCellStyle(workbook);
            style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
            return style;
        }
    
        /**
         * 初始化--每列标题样式
         *
         * @param workbook
         * @return
         */
        private CellStyle initTitleStyle(Workbook workbook) {
            CellStyle style = getBaseCellStyle(workbook);
            style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
            return style;
        }
    
        /**
         * 初始化--数据行样式
         *
         * @param workbook
         * @return
         */
        private CellStyle initStyles(Workbook workbook) {
            CellStyle style = getBaseCellStyle(workbook);
            style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
            style.setDataFormat(STRING_FORMAT);
            return style;
        }
    
        /**
         * 基础样式
         *
         * @return
         */
        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(false);
            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;
        }
    }
    

    3.0 EasyPoi 工具类

    package com.gcb.platform.utils.easyPoi;
    
    import cn.afterturn.easypoi.excel.ExcelExportUtil;
    import cn.afterturn.easypoi.excel.entity.ExportParams;
    import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
    import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.ss.usermodel.Workbook;
    
    import javax.servlet.http.HttpServletResponse;
    import javax.ws.rs.core.MediaType;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.nio.charset.StandardCharsets;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    /**
     * author Mr.ALan
     * DESCRIPTION 使用自定义的表格样式导出文件
     * create 2019/12/25
     */
    
    @Slf4j
    public class OfficeExportUtil {
    
        /**
         * 允许导出的最大条数
         */
        private static final Integer EXPORT_EXCEL_MAX_NUM = 100000;
    
        /**
         * 获取导出的 Workbook对象
         *
         * @param title     大标题
         * @param sheetName 页签名
         * @param object    导出实体
         * @param list      数据集合
         * @return Workbook
         */
        public static Workbook getWorkbook(String title, String sheetName, Class object, List list) {
            //判断导出数据是否为空
            if (list == null) {
                list = new ArrayList<>();
            }
            //判断导出数据数量是否超过限定值
            if (list.size() > EXPORT_EXCEL_MAX_NUM) {
                title = "导出数据行数超过:" + EXPORT_EXCEL_MAX_NUM + "条,无法导出、请添加导出条件!";
                list = new ArrayList<>();
            }
            //获取导出参数
            ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.HSSF);
            //设置导出样式
            exportParams.setStyle(ExcelStyleUtil.class);
            //输出Workbook流
            return ExcelExportUtil.exportExcel(exportParams, object, list);
        }
    
        /**
         * 使用easyPoi导出含有多个sheet的excel
         *
         * @param titles  按照顺序存放标题名称
         * @param sheets  按照顺序存放sheet名称
         * @param objects 按照顺序存放导出的实体字节码对象
         * @param data    按照顺序存放要导出来的数据集合
         * @return
         */
        public static Workbook getWorkbook(List<String> titles,List<String> sheets, List<Class> objects, List<List> data) {
            List<Map<String, Object>> sheetsList = new ArrayList<>(titles.size());
            for (int i = 0; i < titles.size(); i++) {
                // 创建参数对象(用来设定excel得sheet得内容等信息)
                ExportParams exportParams = new ExportParams();
                exportParams.setStyle(ExcelStyleUtil.class);
                // 设置sheet得名称
                exportParams.setSheetName(sheets.get(i));
                exportParams.setTitle(titles.get(i));
                // 创建sheet1使用得map
                Map<String, Object> exportMap = new HashMap<>();
                // title的参数为ExportParams类型,
                exportMap.put("title", exportParams);
                // 模版导出对应得实体类型
                exportMap.put("entity", objects.get(i));
                // sheet中要填充得数据
                exportMap.put("data", data.get(i));
                // 将sheet使用添加到添加到集合总
                sheetsList.add(exportMap);
            }
            return ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
        }
    
        /**
         * 获取导出的 Workbook对象
         *
         * @param path 模板路径
         * @param map  导出内容map
         * @return Workbook
         */
        public static Workbook getWorkbook(String path, Map<String, Object> map) {
            //获取导出模板
            TemplateExportParams params = new TemplateExportParams(path);
            //设置导出样式
            params.setStyle(ExcelStyleUtil.class);
            //输出Workbook流
            return ExcelExportUtil.exportExcel(params, map);
        }
    
        /**
         * 导出Excel
         *
         * @param workbook workbook流
         * @param fileName 文件名
         * @param response 响应
         */
        public static void exportExcel(Workbook workbook, String fileName, HttpServletResponse response) {
            // 拼接上文件的后缀
            if (!fileName.contains("xlsx")){
                fileName = fileName + ".xls";
            }
            //输出文件
            try (OutputStream outStream = response.getOutputStream()) {
                if (fileName.contains("xlsx")){
                    response.setContentType("application/xlsx;charset=gbk");
                } else {
                    response.setContentType("application/xls;charset=gbk");
                }
                fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
                response.setContentType(MediaType.APPLICATION_OCTET_STREAM);
                response.setHeader("Content-Disposition", "attachment; filename=" + fileName + "; filename*=utf-8''" + fileName);
                workbook.write(outStream);
            } catch (IOException e) {
                log.error("文件导出异常:", e);
            }
        }
    }
    

    4.0 注解导出

    1. 添加导出的实体,并添加相对的注解

      package com.gcb.platform.model.excelExport;
      
      import cn.afterturn.easypoi.excel.annotation.Excel;
      import lombok.Data;
      
      import java.util.Date;
      
      /**
       * author Mr.ALan
       * DESCRIPTION 用户评价导出实体
       * create 2019/12/27
       */
      @Data
      public class Comment {
      
          @Excel(name = "订单编号",width = 18)
          private String taskCode;
      
          @Excel(name = "报修时间",width = 22)
          private String taskTime;
      
          @Excel(name = "报修产品",width = 20)
          private String equipmentName;
      
          @Excel(name = "报修位置",width = 22)
          private String address;
      
          @Excel(name = "故障描述",width = 20)
          private String description;
      
          @Excel(name = "品牌",width = 15)
          private String eqBrand;
      
          @Excel(name = "型号")
          private String eqNum;
      
          @Excel(name = "报修人")
          private String publisherName;
      
          @Excel(name = "报修人电话",width = 18)
          private String phone;
      
          @Excel(name = "处理人")
          private String handleName;
      
          @Excel(name = "评价时间",width = 22,format = "yyyy-MM-dd HH:mm:ss")
          private Date creationTime;
      
          @Excel(name = "评价等级")
          private String scoreLevel;
      
          @Excel(name = "评分")
          private Float score;
      
          @Excel(name = "评价标签",width = 20)
          private String tags;
      
          @Excel(name = "评价内容",width = 40)
          private String text;
      
      }
      
      
    2. 查询数据并导出

      {
          // 获取导出的数据
          List<Comment> list = getList();
          //写表头
          String title = "标题";
          String fileName = "用户评价表";
          OfficeExportUtil.exportExcel(
              OfficeExportUtil.getWorkbook(title, "用户评价表", Comment.class, list), 					fileName, response);
      }
      
    展开全文
  • easypoi

    2020-02-11 21:33:40
    easypoi环境搭建Excel导出(包含多sheet导出)Excel导入 环境搭建 <!-- easypoi导入导出excel --> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base<...

    环境搭建

    		<!-- easypoi导入导出excel -->
          <dependency>
              <groupId>cn.afterturn</groupId>
              <artifactId>easypoi-base</artifactId>
              <version>3.1.0</version>
          </dependency>
          <dependency>
              <groupId>cn.afterturn</groupId>
              <artifactId>easypoi-web</artifactId>
              <version>3.1.0</version>
          </dependency>
          <dependency>
              <groupId>cn.afterturn</groupId>
              <artifactId>easypoi-annotation</artifactId>
              <version>3.1.0</version>
          </dependency>
          <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    	<dependency>
    	    <groupId>org.apache.poi</groupId>
    	    <artifactId>poi</artifactId>
    	    <version>3.17</version>
    	</dependency>
    	<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
    	<dependency>
    	    <groupId>org.apache.commons</groupId>
    	    <artifactId>commons-lang3</artifactId>
    	    <version>3.4</version>
    	</dependency>
    

    Excel导出(包含多sheet导出)

    import cn.afterturn.easypoi.excel.entity.ExportParams;
    import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;	
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Workbook;
    import cn.afterturn.easypoi.excel.entity.ExportParams;
    import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
    import cn.afterturn.easypoi.excel.export.ExcelExportService;
    
    	@RequestMapping("/toExportExcel.action")
        public void export(HttpServletResponse response) {
    
            //表头设置
            List<ExcelExportEntity> hdList = new ArrayList<ExcelExportEntity>();
    
            ExcelExportEntity colEntity = new ExcelExportEntity("日期", "dt");
            colEntity.setNeedMerge(true);
            hdList.add(colEntity);
    
            colEntity = new ExcelExportEntity("产品PV", "pv");
            colEntity.setNeedMerge(true);
            hdList.add(colEntity);
    
            colEntity = new ExcelExportEntity("产品UV", "uv");
            colEntity.setNeedMerge(true);
            hdList.add(colEntity);
    
            ExcelExportEntity group_1 = new ExcelExportEntity("业务数据", "businessData");
            List<ExcelExportEntity> exportEntities = new ArrayList<ExcelExportEntity>();
            exportEntities.add(new ExcelExportEntity("数据1", "data1"));
            exportEntities.add(new ExcelExportEntity("数据2", "data2"));
            exportEntities.add(new ExcelExportEntity("数据3", "data3"));
            exportEntities.add(new ExcelExportEntity("数据4", "data4"));
            group_1.setList(exportEntities);
            hdList.add(group_1);
    
            //文件数据
            List<Map<String, Object>> bdList = new ArrayList<Map<String, Object>>();
            Map<String, Object> valMap = new HashMap<String, Object>();
            valMap.put("dt", "2019-11-26 22:16:30");
            valMap.put("pv", "产品测试1");
            valMap.put("uv", "产品测试2");
            List<Map<String, Object>> list_1 = new ArrayList<Map<String, Object>>();
            Map<String, Object> valMap_1 = new HashMap<String, Object>();
            valMap_1.put("data1", "1");
            valMap_1.put("data2", "2");
            valMap_1.put("data3", "3");
            valMap_1.put("data4", "4");
            list_1.add(valMap_1);
            valMap.put("businessData", list_1);
            bdList.add(valMap);
    		
    		ExportParams zyldtjbzbParam = new ExportParams();
    		//设置表格的sheet名称
            zyldtjbzbParam.setSheetName("数据表");
            //设置表格的标题
            zyldtjbzbParam.setTitle("数据表");
    		
    		//塞数据
    		Map<String, Object> zyldtjbzbMap = new HashMap<String, Object>();
            zyldtjbzbMap.put("title", zyldtjbzbParam);
            zyldtjbzbMap.put("entity", hdList);
            zyldtjbzbMap.put("data", bdList);
        	
        	//将每一个sheet的map塞入list中
        	List<Map<String, Object>> sheetsList = new ArrayList<Map<String, Object>>();
        	sheetsList.add(zyldtjbzbMap);
    
    		//参数:response,表格名称(英文),sheet集合
        	export(response,"sjb",sheetsList);
        }
        
    	//导出
    	public void export(HttpServletResponse response,String excelName,List<Map<String, Object>> sheetsList) {
          
        	Workbook workbook = new HSSFWorkbook();
        	
        	for(int i = 0; i < sheetsList.size();i++) {
        		ExcelExportService service = new ExcelExportService();
        		service.createSheetForMap(workbook, (ExportParams)sheetsList.get(i).get("title"), (List<ExcelExportEntity>)sheetsList.get(i).get("entity"), (List<Map<String, Object>>)sheetsList.get(i).get("data"));
        	}
        	setResponseHeader(response, excelName);
            write(workbook, response);
        }
    
    	private void setResponseHeader(HttpServletResponse response, String fileName) {
            try {
                fileName = new String(fileName.getBytes(), "UTF-8");
                response.setContentType("application/octet-stream;charset=UTF-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
                response.addHeader("Pargam", "no-cache");
                response.addHeader("Cache-Control", "no-cache");
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    
    	private void write(Workbook book, HttpServletResponse response) {
            try {
                OutputStream os = response.getOutputStream();
                book.write(os);
                os.flush();
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    

    Excel导入

    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    
    public class ImportExcelUtil {
    	
    	/** 
         * @param file 表格的流
         * @param sheetIndex sheet的序号,默认从0开始数
         */  
    	public static List<Map<String, String>> importExcel(File file,int sheetIndex) {
    		try {
    			//获取表格流
    			FileInputStream is = new FileInputStream(file);
    			//读取excel
    			Workbook workbook = new HSSFWorkbook(is);
    			//拿到第几个sheet
    			Sheet sheet = workbook.getSheetAt(sheetIndex);
    			//拿到最后一行的行数
    			int lastRowNum = sheet.getLastRowNum();
    			
    			//获取表头
    			Map<Integer, String> headMap = new HashMap<Integer, String>();
    			//读取表头所在的行,默认从0开始数
    			Row row = sheet.getRow(1);
    		    //获取最后一列的列数
    		    short lastCellNum = row.getLastCellNum();
    	        for (int j = 0; j < lastCellNum; j++) {
    	            Cell cell = row.getCell(j);
    	            headMap.put(j, cell.getStringCellValue());
    	        }
    	        
    	        //获取表体
    	        List<Map<String, String>> bodyList = new ArrayList<Map<String, String>>();
    	        //你的数据是从第几行开始的就是几,默认从0开始数
    	        for (int i = 2; i <= lastRowNum; i++) {
            		Map<String,String> map = new HashMap<String,String>();
    	        	Row rowBody = sheet.getRow(i);
    	        	//这里j的最大值以表头的列数为参考,因为内容有的可能没有值,就会少算
    	        	for (int j = 0; j < lastCellNum; j++) {
    	        		String value = getMergedRegionValueAndInfo(sheet,i,j);
    	        		if(value == null || "".equals(value)){
    		        		Cell cell = rowBody.getCell(j);
    		        		map.put(headMap.get(j), getCellValue(cell));
    	        		}else{
    	        			map.put(headMap.get(j), value);
    	        		}
    	        	}
    	        	bodyList.add(map);
    	        }
    	        return bodyList;
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		return null;
    	}
    
    	public static String getCellValue(Cell cell) {
            String ret;
            try {
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        ret = "";
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        ret = String.valueOf(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        ret = null;
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        try {
                            ret = String.valueOf(cell.getStringCellValue());
                        } catch (IllegalStateException e) {
                            ret = String.valueOf(cell.getNumericCellValue());
                        }
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        ret = cell.getNumericCellValue() + "";
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            Date date = cell.getDateCellValue();
                            if (date != null) {
                                ret = new SimpleDateFormat("yyyy-MM-dd").format(date);
                            } else {
                                ret = "";
                            }
                        } else {
                            ret = cell.getNumericCellValue() + "";
                            ret = subZeroAndDot(ret);
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        ret = cell.getRichStringCellValue().getString();
                        break;
                    default:
                        ret = "";
                }
            } catch (Exception e) {
                e.printStackTrace();
                ret = null;
            }
            return ret; // 有必要自行trim
        }
    
    	/**   
        * 获取合并单元格的值   
        * @param sheet   
        * @param row   
        * @param column   
        * @return   
        */    
        public static String getMergedRegionValueAndInfo(Sheet sheet ,int row , int column){    
        	String MergedVal="";
            int sheetMergeCount = sheet.getNumMergedRegions();    
            for(int i = 0 ; i < sheetMergeCount ; i++){    
                CellRangeAddress ca = sheet.getMergedRegion(i);    
                int firstColumn = ca.getFirstColumn();    
                int lastColumn = ca.getLastColumn();    
                int firstRow = ca.getFirstRow();    
                int lastRow = ca.getLastRow();    
                if(row >= firstRow && row <= lastRow){    
                    if(column >= firstColumn && column <= lastColumn){    
                        Row fRow = sheet.getRow(firstRow);    
                        Cell fCell = fRow.getCell(firstColumn);    
                        MergedVal=getCellValue(fCell);
                        return MergedVal ;    
                    }    
                }    
            }    
            return null ;    
        } 
    
    	private static String subZeroAndDot(String s) {
    	    if (s.indexOf(".0") > 0) {
    	        // 去掉多余的 
    	        s = s.replaceAll("0+?$", ""); 
    	       // 如果最后一位是.则去掉
    	        s = s.replaceAll("[.]$", "");
    	    } 
    	   return s;
    	}
    

    项目经验相关操作补充

    查看导入的数据中是否包含重复的数据

    对于数据量不大,不超过一万到两行的excel

    	//使用List集合将指定查重的字段存储起来
    	List<String> list = new ArrayList<String>();
    	for(){
    		//将指定查重字段存储到list,如果有多个字段,就把它拼起来放到list集合中
    	}
    	//查重
    	if(list.stream().distinct().count() == (long)(list.size())) {
    		//无重复
    	}else{
    		//有重复
    	}
    

    导入数据对于Oracle库中已经存在的数据操作

    通过使用触发器对于库中已经存在的数据:有就更新

    #创建触发器
    create or replace trigger jcStudentXX
    #触发条件:插入之前
    before
    insert
    on T_SCHTCSBB
    #对于每一行插入操作
    for each row
    declare
    v_count number;
    #对触发器的操作开启新的事务
    pragma autonomous_transaction;
    begin
      select count(*) into v_count from T_SCHTCSBB where studentSFZH = :new.studentSFZH and delestatus = '0';
      if (v_count > 0) then
      	#删除原来的数据
        delete from T_SCHTCSBB where studentSFZH = :new.studentSFZH and delestatus = '0';
        #提交触发器事务
        commit;
      end if;
    end;
    
    展开全文
  • EasyPOI

    2012-03-28 15:18:00
    easypoi v0.2使用poi3.7开发,提供了更直观易用的常用方法。主要支持以下的功能: 实现类似jQuery的链式调用方式 只支持xls格式,可以加载已存在的xls文件作为模板 提供了行编辑器、列编辑器和区域编辑器,可以一...

    easypoi v0.2使用poi3.7开发,提供了更直观易用的常用方法。主要支持以下的功能:

    1. 实现类似jQuery的链式调用方式
    2. 只支持xls格式,可以加载已存在的xls文件作为模板
    3. 提供了行编辑器、列编辑器和区域编辑器,可以一次操作一行、一列或一个区域的值或样式
    4. 可设置的样式包括:边框大小、颜色;背景色;字体大小、颜色、粗体、斜体、删除线、斜体等;数据格式;单元格宽高;对齐方式……等
    5. 设置打印样式、设置密码、按行或按列分组
    6. 插入图片、批注、公式

    EasyPOI 的目的是封装了poi的写excel的API,一个简单的例子:

    publicclassHelloWord{
    publicstaticvoidmain(String[]args){
    Excelexcel=newExcel();
    excel
    .cell(,)//选择第一个单元格
    .value("Hello World!")//写入值
    .align(Align.CENTER)//设置水平对齐方式
    .bgColor(Color.LIGHT_YELLOW)//设置背景色
    .height(30)//设置高度
    .font(newIFontEditor(){//设置字体
    publicvoidupdateFont(Fontfont){
    font
    .boldweight(BoldWeight.BOLD);//粗体
    font
    .color(Color.BROWN);//字体颜色
    }
    });
    excel
    .region(,,,10).merge();//合并第一行10个单元格

    Object[]val=newObject[]{"插入一行数据",123,'A',Math.PI,newDate(),"hello"};

    excel
    .row(2)//选择第3行
    .value(val)//写入数据
    .addWidth(2000)//增加宽度
    .borderOuter(BorderStyle.DASH_DOT_DOT,Color.CORAL);//设置外边框样式

    excel
    .row(4,1)//选择第5行,但忽略第1个单元格,从第2个单元格开始操作
    .value(val)
    .borderFull(BorderStyle.DASH_DOT,Color.RED);//设置全部边框

    excel
    .row(6)//选择第7行
    .value(val,2)//从第3个单元格开始写入数据
    .borderTop(BorderStyle.THIN,Color.BLUE);//只设置上边框

    excel
    .column(11)
    .value(val)//也可以操作一列
    .align(Align.CENTER)
    .borderFull(BorderStyle.THICK,Color.CORNFLOWER_BLUE)
    .autoWidth();//根据内容长度,自动调整列宽

    excel
    .cell(7,).value("=IF(B3=123,\"等于\",\"不等于\")");//写入Excel函数
    excel
    .cell(7,1).value(0.578923).dataFormat("0.00%");//设置数据格式

    excel
    .region(8,,10,1).image("http://poi.apache.org/resources/images/group-logo.jpg");//插入一张图片

    excel
    .sheet().freeze(1,)//冻结第一行
    .sheetName("这是第一个表");//重命名当前处于工作状态的表的名称

    //操作第二个表
    excel
    .setWorkingSheet(1).sheetName("第二个表");//把第二个表设置为工作状态,并改名

    excel
    .row().value(val);//第二个表写入数据
    excel
    .saveExcel("E:/helloworld.xls");
    }
    }





    public class HelloWord {
    public static void main(String[] args) {
    Object[] val = new Object[]{"插入一行数据",123,'A',Math.PI,new Date(), "hello"};

    Excel excel = new Excel();
    excel.cell(0, 0) //选择第一个单元格
    .value("Hello World!")//写入值
    .align(Align.CENTER)//设置水平对齐方式
    .bgColor(Color.LIGHT_YELLOW)//设置背景色
    .height(30)//设置高度
    .font(new IFontEditor(){//设置字体
    public void updateFont(Font font) {
    font.boldweight(BoldWeight.BOLD);//粗体
    font.color(Color.BROWN);//字体颜色
    }
    });
    excel.region(0, 0, 0, 10).merge();//合并第一行10个单元格
    excel.region("$A$2:$K$2").merge();//也可以这样选取区域

    excel.row(2)//选择第3行
    .value(val)//写入数据
    .addWidth(2000)//增加宽度
    .borderOuter(BorderStyle.DASH_DOT_DOT, Color.CORAL);//设置外边框样式

    excel.row(4,1)//选择第5行,但忽略第1个单元格,从第2个单元格开始操作
    .value(val)
    .borderFull(BorderStyle.DASH_DOT, Color.RED);//设置全部边框

    excel.row(6)//选择第7行
    .value(val, 2)//从第3个单元格开始写入数据
    .borderTop(BorderStyle.THIN, Color.BLUE);//只设置上边框

    excel.column(11)
    .value(val)//也可以操作一列
    .align(Align.CENTER)
    .borderFull(BorderStyle.THICK, Color.CORNFLOWER_BLUE)//设置全部边框
    .autoWidth();//根据内容长度,自动调整列宽

    excel.cell(7, 0).value("=IF(B3=123,\"等于\",\"不等于\")");//写入Excel函数
    excel.cell(7, 1).value(0.578923).dataFormat("0.00%");//设置数据格式
    excel.cell(7, 2).value(0.578923, "0.00%");//也可以这样设置数据格式

    //插入一张图片
    excel.region(8, 0, 10, 1).image("http://poi.apache.org/resources/images/group-logo.jpg");

    excel.sheet().freeze(1, 0)//冻结第一行
    .sheetName("这是第一个表");//重命名当前处于工作状态的表的名称

    //设置单元格备注
    excel.cell(8, 5).value("这个单元格设置了备注").comment("这是一条备注");

    //操作第二个表
    excel.setWorkingSheet(1).sheetName("第二个表");//把第二个表设置为工作状态,并改名
    excel.row(0).value(val);//第二个表写入数据
    excel.sheet().groupColumn(0, 3);//按列分组

    excel.saveExcel("E:/temp/excel/helloworld.xls");
    }
    }

    展开全文
  • EasyPoi教程

    2018-09-30 11:55:25
    EasyPoi教程
  • easypoi导出工具

    2020-11-05 09:37:47
    easypoi常用导入,导出,工具,引入easypoi包ExcelUtil工具类,可以直接导出查询的数据
  • SpringBoot集成EasyPoi(Excel导入导出)

    万次阅读 热门讨论 2018-07-04 21:12:57
    1.导入jar包 <!--EasyPoi导入导出--> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <...

    1.导入jar包

        <!--EasyPoi导入导出-->
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-base</artifactId>
                <version>3.0.3</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-web</artifactId>
                <version>3.0.3</version> </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>3.0.3</version>
            </dependency>
            <!-- 文件上传 -->
    
            <dependency>
                <groupId>commons-fileupload</groupId>
                <artifactId>commons-fileupload</artifactId>
                <version>1.3.1</version>
            </dependency>

    2.导入工具类(官网和百度都有)

    package com.zzf.finals.utiles;
    
    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 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;
    
    public class ExcelUtiles {
        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);
        }
    
        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));
        }
    
        public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
            defaultExport(list, fileName, response);
        }
    
        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);
        }
    
        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 NormalException(e.getMessage());
            }
        }
    
        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);
        }
    
        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 NormalException("模板不能为空");
            } catch (Exception e) {
                e.printStackTrace();
                //throw new NormalException(e.getMessage());
            } return list;
        }
    
            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 NormalException("excel文件不能为空");
            } catch (Exception e) {
                //throw new NormalException(e.getMessage());
                System.out.println(e.getMessage());
            }
            return list;
        }
    
    }
    

    3.编写实体映射类

    package com.zzf.finals.entity;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    import lombok.Data;
    import org.springframework.format.annotation.DateTimeFormat;
    
    import javax.persistence.*;
    import java.util.Date;
    
    @Entity
    @Table(name = "seckill")
    public class DemoExcel {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Excel(name = "id" ,orderNum = "0")
        private Long seckillId;
    
        @Column(name = "name")
        @Excel(name = "姓名" ,orderNum = "1")
        private String name;
    
        @Column(name = "number")
        @Excel(name = "数量" ,orderNum = "2")
        private int number;
    
        @Column(name = "start_time")
        @Excel(name = "开始日期" ,orderNum = "3",importFormat = "yyyy-MM-dd HH:mm:ss")//exportFormat = "yyyy-MM-dd HH:mm:ss")
        private Date startTime;
    
        @Column(name = "end_time")
        @Excel(name = "结束日期" ,orderNum = "4",importFormat = "yyyy-MM-dd HH:mm:ss")//exportFormat = "yyyy-MM-dd HH:mm:ss")
        private Date endTime;
    
        @Column(name = "create_time")
        @Excel(name = "创建日期" ,orderNum = "5",importFormat = "yyyy-MM-dd HH:mm:ss")//exportFormat = "yyyy-MM-dd HH:mm:ss")
        private Date createTime;
    
        public Long getSeckillId() {
            return seckillId;
        }
    
        public void setSeckillId(Long seckillId) {
            this.seckillId = seckillId;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public int getNumber() {
            return number;
        }
    
        public void setNumber(int number) {
            this.number = number;
        }
    
        public Date getStartTime() {
            return startTime;
        }
    
        public void setStartTime(Date startTime) {
            this.startTime = startTime;
        }
    
        public Date getEndTime() {
            return endTime;
        }
    
        public void setEndTime(Date endTime) {
            this.endTime = endTime;
        }
    
        public Date getCreateTime() {
            return createTime;
        }
    
        public void setCreateTime(Date createTime) {
            this.createTime = createTime;
        }
    
        @Override
        public String toString() {
            return "DemoExcel{" +
                    "seckillId=" + seckillId +
                    ", name='" + name + '\'' +
                    ", number=" + number +
                    ", startTime=" + startTime +
                    ", endTime=" + endTime +
                    ", createTime=" + createTime +
                    '}';
        }
    }
    

     

    4.控制器代码

    package com.zzf.finals.controller;
    
    import cn.afterturn.easypoi.excel.ExcelImportUtil;
    import cn.afterturn.easypoi.excel.entity.ImportParams;
    import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
    import cn.afterturn.easypoi.handler.inter.IExcelDataHandler;
    import cn.afterturn.easypoi.util.PoiPublicUtil;
    import com.zzf.finals.entity.DemoExcel;
    import com.zzf.finals.repository.DemoExcelRepository;
    import com.zzf.finals.service.DemoService;
    import com.zzf.finals.utiles.ExcelUtiles;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.*;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.File;
    import java.io.IOException;
    import java.text.SimpleDateFormat;
    import java.util.List;
    import java.util.Map;
    
    @RestController
    @RequestMapping("/Excel")
    public class ExcelController {
    
        @Autowired
        private DemoExcelRepository demoExcelRepository;
    
        @Autowired
        private DemoService demoService;
    
        @GetMapping("/export")
        public void export(HttpServletResponse response) {
            System.out.println(1);
    //        模拟从数据库获取需要导出的数据
            List<DemoExcel> personList = demoExcelRepository.findAll();
    //         导出操作
            ExcelUtiles.exportExcel(personList, "测试名", "什么名字", DemoExcel.class, "测试.xls", response);
    
        }
    
        @PostMapping("/importExcel2")
        public void importExcel2(@RequestParam("file") MultipartFile file) {
            ImportParams importParams = new ImportParams();
            // 数据处理
            importParams.setHeadRows(1);
            importParams.setTitleRows(1);
    
            // 需要验证
            importParams.setNeedVerfiy(true);
    
            try {
                ExcelImportResult<DemoExcel> result = ExcelImportUtil.importExcelMore(file.getInputStream(), DemoExcel.class,
                        importParams);
    
                List<DemoExcel> successList = result.getList();
                for (DemoExcel demoExcel : successList) {
                  System.out.println(demoExcel);
                }
            } catch (IOException e) {
            } catch (Exception e) {
            }
        }
    }
    
    
    
    

    输入连接后如图所示。

    导入后如:

    最最最简单的导入导出就这么完成了。

     

     

    修改自定义样式:

     

    查看源码我发现他内部封装的是:

     

    所以我们只需要设置这个样式就行了如:

    public class ShelterIExcelExportStyler extends ExcelExportStylerDefaultImpl implements IExcelExportStyler{
    
    	
    	
    	public ShelterIExcelExportStyler(Workbook workbook) {
    		super(workbook);
    	}
    
        @Override
        public CellStyle getTitleStyle(short color) {
            CellStyle titleStyle = workbook.createCellStyle();
            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) 20);
            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();
            Font font = workbook.createFont();
    //        font.setFontHeightInPoints((short) 15);
            style.setFont(font);
            style.setAlignment(CellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            style.setDataFormat(STRING_FORMAT);
            
            if (isWarp) {
                style.setWrapText(true);
            }
            return style;
        }
    
    
    }

    这里我只是简单的修改了他默认字体大小。如果要设置表格的宽度不可以在这里设置。

     

        private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
                                          HttpServletResponse response, ExportParams exportParams, String sheetName) {
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
            Sheet sheet=workbook.getSheet(sheetName);
    //        sheet.CreateRow(0).Height = (short)(200*20);
           // sheet.createRow(0);
            sheet.getRow(0).setHeight((short)(50*20));
            sheet.getRow(1).setHeight((short)(30*20));
            if (workbook != null); downLoadExcel(fileName, response, workbook);
        }

    他会调用ExportExcel返回一个Workbook对象,然后通过这个对象获取Sheet才能改变行的宽度千万不腰用CreateRow会覆盖。

     

    有些人会在使用导入的时候出现只导入一条数据或者列缺少的情况,个人推荐可以使用步进指令去调试这段代码改成适用自己的”轮子“。我通过调试发现保存数据是这个方法:

    	private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params,
    			Map<String, PictureData> pictures) throws Exception {
    		List collection = new ArrayList();
    		Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();
    		List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>();
    		String targetId = null;
    		if (!Map.class.equals(pojoClass)) {
    			Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
    			ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
    			if (etarget != null) {
    				targetId = etarget.value();
    			}
    			getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null, null);
    		}
    		Iterator<Row> rows = sheet.rowIterator();
    		for (int j = 0; j < params.getTitleRows(); j++) {
    			rows.next();
    		}
    		Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection);
    		checkIsValidTemplate(titlemap, excelParams, params, excelCollection);
    		Row row = null;
    		Object object = null;
    		String picId;
    		int readRow = 0;
    		// 跳过无效行
    		for (int i = 0; i < params.getStartRows(); i++) {
    			rows.next();
    		}
    		while (rows.hasNext()
    				&& (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
    			if (params.getReadRows() > 0 && readRow > params.getReadRows()) {
    				break;
    			}
    			row = rows.next();
    			// Fix 如果row为无效行时候跳出
    			if (sheet.getLastRowNum() - row.getRowNum() < params.getLastOfInvalidRow()) {
    				break;
    			}
    			// 判断是集合元素还是不是集合元素,如果是就继续加入这个集合,不是就创建新的对象
    			// keyIndex 如果为空就不处理,仍然处理这一行
    			if (params.getKeyIndex() != null && !(row.getCell(params.getKeyIndex()) == null
    					|| StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) {
    
    				for (ExcelCollectionParams param : excelCollection) {
    					addListContinue(object, param, row, titlemap, targetId, pictures, params);
    				}
    			} else {
    				object = PoiPublicUtil.createObject(pojoClass, targetId);
    				try {
    					// 标记为null的次数
    					int count = 0;
    					int sum = titlemap.size();
    					for (int i = row.getFirstCellNum(); i <= sum; i++) {
    						Cell cell = row.getCell(i);
    						boolean flag = true;
    						if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
    							count++;
    							flag = false;
    						}
    						String titleString = (String) titlemap.get(i);
    						if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) {
    							if (excelParams.get(titleString) != null && excelParams.get(titleString).getType() == 2) {
    								picId = row.getRowNum() + "_" + i;
    								saveImage(object, picId, excelParams, titleString, pictures, params);
    							} else {
    								if (saveFieldValue(params, object, cell, excelParams, titleString, row)) {
    									if (flag)//只有当没有count++过才能添加。
    										count++;
    								}
    							}
    						}
    					}
    
    					for (ExcelCollectionParams param : excelCollection) {
    						addListContinue(object, param, row, titlemap, targetId, pictures, params);
    					}
    					if (verifyingDataValidity(object, row, params, pojoClass)) {
    						// count等于0或者
    						if ((count == 0) || (count <= sum - 2))
    							collection.add(object);
    					} else {
    						// 如果为null的次数小于5则添加
    						// if (count!=0 || count < sum-3)
    						failCollection.add(object);
    					}
    				} catch (ExcelImportException e) {
    					LOGGER.error("excel import error , row num:{},obj:{}", readRow,
    							ReflectionToStringBuilder.toString(object));
    					if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
    						throw new ExcelImportException(e.getType(), e);
    					}
    				} catch (Exception e) {
    					LOGGER.error("excel import error , row num:{},obj:{}", readRow,
    							ReflectionToStringBuilder.toString(object));
    					throw new RuntimeException(e);
    				}
    			}
    			readRow++;
    		}
    		return collection;
    	}

    这个类是

    修改的位置在这里,我通过修改这段代码使其强制进入else中可以拿到所有数据,然后判断null的次数选择是否添加。

    适用自己的轮子才是最好的轮子……

    文档地址:http://easypoi.mydoc.io/

    参考:https://www.jianshu.com/p/5d67fb720ece

    展开全文

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,876
精华内容 1,150
关键字:

easypoi