精华内容
下载资源
问答
  • 利用easy poi 导出批量导出word

    千次阅读 2019-04-08 17:23:08
    利用easy poi 实现word批量导出easy poi 引入实现功能代码 easy poi 引入 maven <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> ...

    利用easy poi 实现word批量导出

    easy poi 引入

    maven

    <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>
    

    easy poi 作者官方文档

    实现功能

    实现基于word模板批量生成word文档,并压缩成压缩包进行导出;

    代码

        public void exportWord(String projectId, HttpServletResponse response) throws Exception{
            //获取项目根目录
            File path = new File(ResourceUtils.getURL("classpath:").getPath());
            //导出前的检查
            beforeExportWord(path);
            //业务代码
            //根据项目id获取项目信息
            ProjectEntity entity = projectRepository.getOne(projectId);
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-download");
            //把项目名称当作zip包的名字
            String fileName = entity.getProjectName()+".zip";
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            //获取该项目下评测机构的list
            List<PgzjEntity> pgzjList = pgzjRepository.findByProjectIdOrderByPgzjIdAsc(projectId);
            List<PgzjCompanyEntity> companList = new ArrayList<>();
            if(pgzjList.size()>0){
                for(PgzjEntity pgzjItem: pgzjList){
                    PgjgInfoUserEntity pgjgInfoUser = pgjgInfoUserRepository.findByPgjgLoginIdAndProjectId(pgzjItem.getLoginId(), pgzjItem.getProjectId()).get(0);
                    PgjgInfoEntity pgjgInfo = pgjgInfoRepository.getOne(pgjgInfoUser.getPgjgInfoId());
                    //获取每个机构下评测的产品
                    companList = pgzjCompanyRepository.findByPgzjId(pgzjItem.getPgzjId());
                    if(companList.size()>0){
                        for(PgzjCompanyEntity companItem:companList){
                            //异步执行生成word方法
                            asyncService.caretWord(companItem,pgjgInfo,path,pgzjItem);
                            //生成的word文档放到word文件夹下
                        }
                    }
                }
            }
            //业务代码结束
            //超两分钟跳出循环,执行打,防止无限循环
            Long startId = System.currentTimeMillis();
            while (System.currentTimeMillis()-startId<120*1000){
                int a = 0;
                File word = new File(path.getAbsolutePath(), "/word");
                System.out.println("开始:");
                File[] arr = word.listFiles();
                if(arr.length==(pgzjList.size()*companList.size())){
                    break;
                }
                System.out.println("等待:"+arr.length);
            }
            exportZip(path,response);
        }
    
        public void beforeExportWord(File path) throws Exception {
            //获取临时存放生成word文件夹
            File word = new File(path.getAbsolutePath(), "/word");
            //判断文件夹是否存在,存在清空文件夹,不存在创建文件夹
            if (word.exists()) {
                for (File f : word.listFiles()) {
                    f.delete();
                }
            } else {
                word.mkdir();
            }
            //获取临时存放生成zip文件夹
            File zip = new File(path.getAbsolutePath(), "/zip");
            //判断文件夹是否存在,存在清空文件夹,不存在创建文件夹
            if (zip.exists()) {
                for (File f : zip.listFiles()) {
                    f.delete();
                }
            } else {
                zip.mkdir();
            }
    
        }
    
        public void exportZip(File path,HttpServletResponse response) throws Exception{
            FileOutputStream fos1 = new FileOutputStream(new File(path.getAbsolutePath()+"/zip/模板.zip"));
            ZipUtils.toZip(path.getAbsolutePath()+"/word", fos1, true);
            OutputStream fos = null;
            BufferedInputStream bis = null;
            BufferedOutputStream bos = null;
            try {
                fos = response.getOutputStream();
                bis = new BufferedInputStream(new FileInputStream(path.getAbsolutePath()+"/zip/模板.zip"));
                bos = new BufferedOutputStream(fos);
                byte[] buff = new byte[2048];
                int bytesRead;
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                    bos.write(buff, 0, bytesRead);
                }
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                fos.flush();
                bis.close();
                bos.close();
                fos.close();
            }
        }
    }
    
    public class ZipUtils {
    
        private static final int BUFFER_SIZE = 2 * 1024;
    
        public static void toZip(String srcDir, OutputStream out, boolean KeepDirStructure)
                throws RuntimeException {
            long start = System.currentTimeMillis();
            ZipOutputStream zos = null;
            try {
                zos = new ZipOutputStream(out);
                File sourceFile = new File(srcDir);
                compress(sourceFile, zos, sourceFile.getName(), KeepDirStructure);
                long end = System.currentTimeMillis();
                System.out.println("压缩完成,耗时:" + (end - start) + " ms");
            } catch (Exception e) {
                throw new RuntimeException("zip error from ZipUtils", e);
            } finally {
                if (zos != null) {
                    try {
                        zos.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    
        /**
         * 56
         * 压缩成ZIP 方法2
         * 57
         *
         * @param srcFiles 需要压缩的文件列表
         *                 58
         * @param out      压缩文件输出流
         *                 59
         * @throws RuntimeException 压缩失败会抛出运行时异常
         *                          60
         */
        public static void toZip(List<File> srcFiles, OutputStream out) throws RuntimeException {
            long start = System.currentTimeMillis();
            ZipOutputStream zos = null;
            try {
                zos = new ZipOutputStream(out);
                for (File srcFile : srcFiles) {
                    byte[] buf = new byte[BUFFER_SIZE];
                    zos.putNextEntry(new ZipEntry(srcFile.getName()));
                    int len;
                    FileInputStream in = new FileInputStream(srcFile);
                    while ((len = in.read(buf)) != -1) {
                        zos.write(buf, 0, len);
                    }
                    zos.closeEntry();
                    in.close();
                }
                long end = System.currentTimeMillis();
                System.out.println("压缩完成,耗时:" + (end - start) + " ms");
            } catch (Exception e) {
                throw new RuntimeException("zip error from ZipUtils", e);
            } finally {
                if (zos != null) {
                    try {
                        zos.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    
        /**
         * 94
         * 递归压缩方法
         * 95
         *
         * @param sourceFile       源文件
         *                         96
         * @param zos              zip输出流
         *                         97
         * @param name             压缩后的名称
         *                         98
         * @param KeepDirStructure 是否保留原来的目录结构,true:保留目录结构;
         *                         99
         *                         false:所有文件跑到压缩包根目录下(注意:不保留目录结构可能会出现同名文件,会压缩失败)
         *                         100
         * @throws Exception 101
         */
        private static void compress(File sourceFile, ZipOutputStream zos, String name,
                                     boolean KeepDirStructure) throws Exception {
            byte[] buf = new byte[BUFFER_SIZE];
            if (sourceFile.isFile()) {
                // 向zip输出流中添加一个zip实体,构造器中name为zip实体的文件的名字
                zos.putNextEntry(new ZipEntry(name));
                // copy文件到zip输出流中
                int len;
                FileInputStream in = new FileInputStream(sourceFile);
                while ((len = in.read(buf)) != -1) {
                    zos.write(buf, 0, len);
                }
                // Complete the entry
                zos.closeEntry();
                in.close();
            } else {
                File[] listFiles = sourceFile.listFiles();
                if (listFiles == null || listFiles.length == 0) {
                    // 需要保留原来的文件结构时,需要对空文件夹进行处理
                    if (KeepDirStructure) {
                        // 空文件夹的处理
                        zos.putNextEntry(new ZipEntry(name + "/"));
                        // 没有文件,不需要文件的copy
                        zos.closeEntry();
                    }
                } else {
                    for (File file : listFiles) {
                        // 判断是否需要保留原来的文件结构
                        if (KeepDirStructure) {
                            // 注意:file.getName()前面需要带上父文件夹的名字加一斜杠,
                            // 不然最后压缩包中就不能保留原来的文件结构,即:所有文件都跑到压缩包根目录下了
                            compress(file, zos, name + "/" + file.getName(), KeepDirStructure);
                        } else {
                            compress(file, zos, file.getName(), KeepDirStructure);
                        }
                    }
                }
            }
        }
    
        public static void main(String[] args) throws Exception {
            /** 测试压缩方法1  */
            FileOutputStream fos1 = new FileOutputStream(new File("E:\\IdeaProjects\\perfect\\perfect-consumer\\src\\main\\resources\\zip/mytest02.zip"));
            ZipUtils.toZip("E:\\IdeaProjects\\perfect\\perfect-consumer\\src\\main\\resources\\word", fos1, true);
            File file = new File("E:\\IdeaProjects\\perfect\\perfect-consumer\\src\\main\\resources\\zip");
            if (file.isDirectory()) {
                File[] files = file.listFiles();
                for (File f : files) {
                    f.delete();
                }
            }
    
            /** 测试压缩方法2  */
            List<File> fileList = new ArrayList<>();
            fileList.add(new File("D:/Java/jdk1.7.0_45_64bit/bin/jar.exe"));
            fileList.add(new File("D:/Java/jdk1.7.0_45_64bit/bin/java.exe"));
            FileOutputStream fos2 = new FileOutputStream(new File("c:/mytest02.zip"));
            ZipUtils.toZip(fileList, fos2);
        }
    }
    
    展开全文
  • Excel word PDF导入导出 Easy POI

    千次阅读 2019-10-06 19:57:59
    http://easypoi.mydoc.io/#category_49974 转载于:https://www.cnblogs.com/durenniu/p/11338747.html

    http://easypoi.mydoc.io/#category_49974

    转载于:https://www.cnblogs.com/durenniu/p/11338747.html

    展开全文
  • 优点: 该说说优点了,比如注解式 起手, 导出,可以无侵入代码,可以侵入代码写法, 侵入代码写法,可以明确校验字段,字段转换 有dataHandle 作者自己定义了一套 对应类型的转化 formatter 机制 比如时间 --》dataformat ...

    优点:

    该说说优点了,比如注解式 起手, 导出,可以无侵入代码,可以侵入代码写法, 侵入代码写法,可以明确校验字段,字段转换 有dataHandle 作者自己定义了一套 对应类型的转化 formatter 机制

    比如时间 --》dataformat
    比如数字类型–〉 小树
    比如枚举类型,自动替换id 为汉字
    支持sax 大批量导入

    缺点:

    • 1.这个要用的是忽略空行,这个支持选择单一主键 进行忽略整行 就是数据第一行应该是忽略不了,记个bug
    • 2.不适合位处理机制,拓展性差
    • 3.支持样式较少

    缺点代码:

     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();
            List<ExcelCollectionParams> excelCollection = new ArrayList();
            String targetId = null;
            this.i18nHandler = params.getI18nHandler();
            boolean isMap = Map.class.equals(pojoClass);
            if (!isMap) {
                Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
                ExcelTarget etarget = (ExcelTarget)pojoClass.getAnnotation(ExcelTarget.class);
                if (etarget != null) {
                    targetId = etarget.value();
                }
    
                this.getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, (List)null, (ExcelEntity)null);
            }
    
            Iterator<Row> rows = sheet.rowIterator();
    
            for(int j = 0; j < params.getTitleRows(); ++j) {
                rows.next();
            }
    
            Map<Integer, String> titlemap = this.getTitleMap(rows, params, excelCollection, excelParams);
            this.checkIsValidTemplate(titlemap, excelParams, params, excelCollection);
            Row row = null;
            Object object = null;
            int readRow = 1;
    
            for(int i = 0; i < params.getStartRows(); ++i) {
                rows.next();
            }
    
            if (excelCollection.size() > 0 && params.getKeyIndex() == null) {
                params.setKeyIndex(0);
            }
    
            if (params.isConcurrentTask()) {
                ForkJoinPool forkJoinPool = new ForkJoinPool();
                int endRow = sheet.getLastRowNum() - params.getLastOfInvalidRow();
                if (params.getReadRows() > 0) {
                    endRow = params.getReadRows();
                }
    
                ExcelImportForkJoinWork task = new ExcelImportForkJoinWork(params.getStartRows() + params.getHeadRows() + params.getTitleRows(), endRow, sheet, params, pojoClass, this, targetId, titlemap, excelParams);
                ExcelImportResult forkJoinResult = (ExcelImportResult)forkJoinPool.invoke(task);
                collection = forkJoinResult.getList();
                this.failCollection = forkJoinResult.getFailList();
                return (List)collection;
            } else {
                while(true) {
                    do {
                        if (!rows.hasNext() || row != null && sheet.getLastRowNum() - row.getRowNum() <= params.getLastOfInvalidRow() || params.getReadRows() > 0 && readRow > params.getReadRows()) {
                            return (List)collection;
                        }
    
                        row = (Row)rows.next();
                        if (sheet.getLastRowNum() - row.getRowNum() < params.getLastOfInvalidRow()) {
                            return (List)collection;
                        }
                    } while(row.getLastCellNum() < 0);
    
                    if (isMap && object != null) {
                        ((Map)object).put("excelRowNum", row.getRowNum());
                    }
    
                    StringBuilder errorMsg = new StringBuilder();
                    //有问题: 第一次,除去标题行,object!=null==》永远false 所以第一数据行为空 不会忽略
                    if (params.getKeyIndex() != null && (row.getCell(params.getKeyIndex()) == null || StringUtils.isEmpty(this.getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) {
                        Iterator var32 = excelCollection.iterator();
    
                        while(var32.hasNext()) {
                            ExcelCollectionParams param = (ExcelCollectionParams)var32.next();
                            this.addListContinue(object, param, row, titlemap, targetId, pictures, params, errorMsg);
                        }
                    } else {
                        object = PoiPublicUtil.createObject(pojoClass, targetId);
    
                        try {
                            Set<Integer> keys = titlemap.keySet();
                            Iterator var19 = keys.iterator();
    
                            label136:
                            while(true) {
                                while(true) {
                                    Integer cn;
                                    Cell cell;
                                    String titleString;
                                    do {
                                        if (!var19.hasNext()) {
                                            if (object instanceof IExcelDataModel) {
                                                ((IExcelDataModel)object).setRowNum(row.getRowNum());
                                            }
    
                                            var19 = excelCollection.iterator();
    
                                            while(var19.hasNext()) {
                                                ExcelCollectionParams param = (ExcelCollectionParams)var19.next();
                                                this.addListContinue(object, param, row, titlemap, targetId, pictures, params, errorMsg);
                                            }
    
                                            if (this.verifyingDataValidity(object, row, params, isMap, errorMsg)) {
                                                ((List)collection).add(object);
                                            } else {
                                                this.failCollection.add(object);
                                            }
                                            break label136;
                                        }
    
                                        cn = (Integer)var19.next();
                                        cell = row.getCell(cn);
                                        titleString = (String)titlemap.get(cn);
                                    } while(!excelParams.containsKey(titleString) && !isMap);
    
                                    if (excelParams.get(titleString) != null && ((ExcelImportEntity)excelParams.get(titleString)).getType() == BaseEntityTypeConstants.IMAGE_TYPE) {
                                        String picId = row.getRowNum() + "_" + cn;
                                        this.saveImage(object, picId, excelParams, titleString, pictures, params);
                                    } else {
                                        try {
                                            this.saveFieldValue(params, object, cell, excelParams, titleString, row);
                                        } catch (ExcelImportException var24) {
                                            if (params.isNeedVerify() && ExcelImportEnum.GET_VALUE_ERROR.equals(var24.getType())) {
                                                errorMsg.append(" ").append(titleString).append(ExcelImportEnum.GET_VALUE_ERROR.getMsg());
                                            }
                                        }
                                    }
                                }
                            }
                        } catch (ExcelImportException var25) {
                            LOGGER.error("excel import error , row num:{},obj:{}", readRow, ReflectionToStringBuilder.toString(object));
                            if (!var25.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
                                throw new ExcelImportException(var25.getType(), var25);
                            }
                        } catch (Exception var26) {
                            LOGGER.error("excel import error , row num:{},obj:{}", readRow, ReflectionToStringBuilder.toString(object));
                            throw new RuntimeException(var26);
                        }
                    }
    
                    ++readRow;
                }
            }
        }
    
    展开全文
  • Easy POI的使用

    2019-07-20 17:56:20
    初认识Easy POI 何为easy ...要知道easy poi是什么,先去POI说起,Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能,所以从字面意思可知,easy poi是对...

    何为easy POI

    要知道easy poi是什么,先去POI说起,Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能,所以从字面意思可知,easy poi是对POI的再封装,是对导入导出更方便的一种解决方案

    如何使用

    加依赖

    		<!--Easy POI 很牛逼哦-->
    		<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>
    

    写注解

    /**
     * @author luohuiqi
     * @Description: 
     * @Date: 2019/7/11 10:36
     */
    @Data
    @Accessors(chain = true)
    @AllArgsConstructor
    public class QrcodeBO implements Serializable {
    
    	private static final long serialVersionUID = 1L;
    
    	@Excel(name = "名称", isImportField = "true_st", width = 20)
    	private String stpragePointNmae;
    
    	@Excel(name = "标签编号", width = 20, isImportField = "true_st")
    	private String monitorPointCode;
    
    	@Excel(name = "二维码", width = 20, isImportField = "true_st")
    	private String qrcode;
    }
    

    这里使用到了最基础的@Excel注解

    写实现

    		// 封装数据
    		List<QrcodeBO> qrcodeBOS = new ArrayList<>();
    		monitorPointDOS.forEach(monitorPointDO ->
    				qrcodeBOS.add(new QrcodeBO(collectionPointMap.get(monitorPointDO.getPointId()), monitorPointDO.getCode(), monitorPointDO.getQrcode()))
    		);
    		// 导出
    		FileUtil.exportExcel(qrcodeBOS, "产生点二维码表", "产生点二维码",
    				QrcodeBO.class, "产生点二维码表.xls", HttpKit.getResponse());
    

    这里有用到一个工具类

    /**
     * @author luohuiqi
     * @Description:
     * @Date: 2019/7/14 1:40
     */
    public class FileUtil {
    	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) {
    		exportParams.setStyle(ExcelStyleUtil.class);
    		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 RuntimeException(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 RuntimeException("模板不能为空");
    		} catch (Exception e) {
    			e.printStackTrace();
    			throw new RuntimeException(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 RuntimeException("excel文件不能为空");
    		} catch (Exception e) {
    			throw new RuntimeException(e.getMessage());
    		}
    		return list;
    	}
    
    }
    

    执行

    在这里插入图片描述
    是不是很很简单呢,借用大目老师的话就是10分钟上手,哈哈哈

    样式设置

    由导出的图可以看到吗,基本没有什么样式,比如表格的边框,文字的大小,边距等
    easy POI 提供了一个接口 == IExcelExportStyler==,我们可以实现它,来设置样式

    书写样式

    /**
    * @author luohuiqi
    * @Description:
    * @Date: 2019/7/17 19:17
    */
    public class ExcelStyleUtil implements IExcelExportStyler {
       
       @Override
       public CellStyle getHeaderStyle(short headerColor) {
       	return null;
       }
    
       @Override
       public CellStyle getTitleStyle(short color) {
       	return null;
       }
    
       @Override
       public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
       	return null;
       }
    
       @Override
       public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
       	return null;
       }
    
       @Override
       public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
       	return null;
       }
    }
    

    大家可以看到,官方提供了4个方法(一个已经过时)供我们实现,它们分别是

    • getHeaderStyle (获取表格头样式)
    • getTitleStyle (获取列标题样式)
    • getStyles (获取样式)
    • getTemplateStyles(获取模板的样式)
      用于模板导入/出
      设置好后的模板为
    package pro.nbbt.medical.medicalwaste.util.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.*;
    
    import static org.apache.poi.ss.usermodel.BorderFormatting.BORDER_THIN;
    import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_CENTER;
    import static org.apache.poi.ss.usermodel.CellStyle.SOLID_FOREGROUND;
    import static org.apache.poi.ss.usermodel.CellStyle.VERTICAL_CENTER;
    
    /**
    * @author luohuiqi
    * @Description:
    * @Date: 2019/7/17 19:17
    */
    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 workbook
       * @return
       */
      private CellStyle initTitleStyle(Workbook workbook) {
      	CellStyle style = getBaseCellStyle(workbook);
      	style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
      	//背景色
      	style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
      	style.setFillPattern(SOLID_FOREGROUND);
      	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);
      }
    
      /**
       * 模板使用的样式设置
       */
      @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 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(BORDER_THIN);
      	//左边框
      	style.setBorderLeft(BORDER_THIN);
      	//上边框
      	style.setBorderTop(BORDER_THIN);
      	//右边框
      	style.setBorderRight(BORDER_THIN);
      	//水平居中
      	style.setAlignment(ALIGN_CENTER);
      	//上下居中
      	style.setVerticalAlignment(VERTICAL_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;
      }
    }
    

    使用样式

    	private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
    		if (Objects.equals(MEDICALWASTEMONTHSTATIC, exportParams.getSheetName())) {
    			exportParams.setHeight((short) 7);
    		} else if ((Objects.equals(MEDICALWASTEYEARSTATIC, exportParams.getSheetName()))) {
    			exportParams.setHeight((short) 14);
    		}
    		// 设置样式
    		exportParams.setStyle(ExcelStyleUtil.class);  
    		Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
    		// 写入数据
    		if (workbook != null) {
    			downLoadExcel(fileName, response, workbook);
    		}
    	}
    

    导出效果2

    在这里插入图片描述

    遇到的坑

    • 设置@Excel的height参数无效
      解决方法:其实官方已经说的很清楚了
          /**
        * 导出时在excel中每个列的高度 单位为字符,一个汉字=2个字符
        * 优先选择@ExportParams中的 height
        */
       @Deprecated
       public double height() default 10;
      
      所以可以在刚才刚才设置样式的地方,同样设置高即可
      在这里插入图片描述
    • 设置@Excel的isStatistics时,琪单元格的样式会和数据列的样式不一致
      解决方法:可以继续在之前的defaultExport方法中设置
      在这里插入图片描述
      如图所示,若需要展示额外数据也可以添加
      最后导出的效果如下
      在这里插入图片描述

      任意合并单元格

      解决官方提供的只能合并相同内容的单元格和空单元格不能合并的问题
      	/**
      * 纵向合并相同内容的单元格(包括空)
      *
      * @param sheet
      * @param mergeMap key--列,value--依赖的列,没有传空
      * @param startRow 开始行
      * @param endRow   结束行
      */
      public static void mergeCells(Sheet sheet, Map<Integer, int[]> mergeMap, int startRow,
         						  int endRow) {
         Map<Integer, MergeEntity> mergeDataMap = new HashMap<Integer, MergeEntity>();
         if (mergeMap.size() == 0) {
         	return;
         }
         Row row;
         Set<Integer> sets = mergeMap.keySet();
         String text;
         for (int i = startRow; i <= endRow; i++) {
         	row = sheet.getRow(i);
         	for (Integer index : sets) {
         		if (row == null || row.getCell(index) == null) {
         			if (mergeDataMap.get(index) == null) {
         				continue;
         			}
         			if (mergeDataMap.get(index).getEndRow() == 0) {
         				mergeDataMap.get(index).setEndRow(i - 1);
         			}
         		} else {
         			text = row.getCell(index).getStringCellValue();
         			hanlderMergeCells(index, i, text, mergeDataMap, sheet, row.getCell(index),
         					mergeMap.get(index));
         		}
         	}
         }
         // 为合并后的单元格赋值
         if (mergeDataMap.size() > 0) {
         	for (Integer index : mergeDataMap.keySet()) {
         		if (mergeDataMap.get(index).getEndRow() > mergeDataMap.get(index).getStartRow()) {
         			sheet.addMergedRegion(new CellRangeAddress(mergeDataMap.get(index).getStartRow(),
         					mergeDataMap.get(index).getEndRow(), index, index));
         		}
         	}
         }
      
      }
      
      public static MergeEntity createMergeEntity(String text, int rowNum, Cell cell, int[] delys) {
         MergeEntity mergeEntity = new MergeEntity(text, rowNum, rowNum);
         // 存在依赖关系
         if (delys != null && delys.length != 0) {
         	List<String> list = new ArrayList<String>(delys.length);
         	mergeEntity.setRelyList(list);
         	for (int i = 0; i < delys.length; i++) {
         		list.add(getCellNotNullText(cell, delys[i], rowNum));
         	}
         }
         return mergeEntity;
      }
      
      /**
      * 获取一个单元格的值,确保这个单元格必须有值,不然向上查询
      *
      * @param cell
      * @param index
      * @param rowNum
      * @return
      */
      public static String getCellNotNullText(Cell cell, int index, int rowNum) {
         if (cell == null || cell.getRow() == null) {
         	return null;
         }
         if (cell.getRow().getCell(index) != null
         		&& org.apache.commons.lang3.StringUtils.isNotEmpty(cell.getRow().getCell(index).getStringCellValue())) {
         	return cell.getRow().getCell(index).getStringCellValue();
         }
         return getCellNotNullText(cell.getRow().getSheet().getRow(--rowNum).getCell(index), index,
         		rowNum);
      }
      
      /**
      * 处理合并单元格
      *
      * @param index
      * @param rowNum
      * @param text
      * @param mergeDataMap
      * @param sheet
      * @param cell
      * @param delys
      */
      public static void hanlderMergeCells(Integer index, int rowNum, String text,
         								 Map<Integer, MergeEntity> mergeDataMap, Sheet sheet,
         								 Cell cell, int[] delys) {
         if (mergeDataMap.containsKey(index)) {
         	// 检查是否有依赖关系
         	if (checkIsEqualByCellContents(mergeDataMap.get(index), text, cell, delys, rowNum)) {
         		mergeDataMap.get(index).setEndRow(rowNum);
         	} else {
         		if (mergeDataMap.get(index).getEndRow() > mergeDataMap.get(index).getStartRow()) {
         			sheet.addMergedRegion(new CellRangeAddress(mergeDataMap.get(index).getStartRow(),
         					mergeDataMap.get(index).getEndRow(), index, index));
         		}
         		mergeDataMap.put(index, createMergeEntity(text, rowNum, cell, delys));
         	}
         } else {
         	mergeDataMap.put(index, createMergeEntity(text, rowNum, cell, delys));
         }
      }
      
      private static boolean checkIsEqualByCellContents(MergeEntity mergeEntity, String text,
         											  Cell cell, int[] delys, int rowNum) {
         // 没有依赖关系
         if (delys == null || delys.length == 0) {
         	return mergeEntity.getText().equals(text);
         }
         // 存在依赖关系 测试
         if (mergeEntity.getText().equals(text)) {
         	for (int i = 0; i < delys.length; i++) {
         		if (mergeEntity.getRelyList().get(i) == null || !mergeEntity.getRelyList().get(i)
         				.equals(getCellNotNullText(cell, delys[i], rowNum))) {
         			return false;
         		}
         	}
         	return true;
         }
         return false;
      }
      
    此文是对http://easypoi.mydoc.io/ 的注脚,也是为了以后自己查阅方便,现阶段并没有遇到其他类型的导出,故只做于此,待日后补充
    
    展开全文
  • 最近在导出功能,查到easypoi 做个简单的工具类,希望帮助到您,自己做下收藏! pom.xml 引入 <!--easypoi导入导出--> <dependency> <groupId>cn.afterturn</groupId>...
  • spring boot+mybatis plus+easy poi实现数据库导出成excel和excel导入到数据库
  • 完整项目代码 https://download.csdn.net/download/y1534414425/13135874 一、Maven依赖 <dependency> <groupId>org.springframework.boot</groupId> <artifactId>.../dependen
  • easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法 ...
  • easy-poi导入导出excel实例,包含所需jar包,可直接使用
  • easy poipoi 冲突解决方案

    千次阅读 2019-12-25 11:45:23
    在1个项目中使用easy poi 是不错的选择,不过会与项目中的poi冲突,这就需要解决jar包冲突的问题,在网上找了很多解决方案吗,但是并没有找到。方便日后查阅。希望帮助大家。 <!-- Excel 导出 --> <...
  • 10Easy POI学习笔记

    2021-12-29 00:46:15
    文章目录1 Easy POI简介1.1 基本简介1.2 功能2 Easy POI环境搭建3 Easy POI注解3.1 @ExcelTarget3.2 @Excel3.3 @ExcelIgnore3.4 @ExcelEntity3.5 @ExcelCollection4 Easy POI写入Excel4.1 写入基本数据4.2 写入指定...
  • easy poi 简单导出

    2019-03-06 14:00:00
    2019独角兽企业重金招聘Python工程师标准>>> ...
  • 数据导入导出1.1.Easy POI1.2.准备工作1.3.实现功能1.4.测试功能 1.数据导入导出 这里的数据具体是指员工数据,员工数据导入导出在实际应用场景中比较常见;比如说我们需要把多个员工的数据一起在某些地方去使用,...
  • Easy POI流程1.maven依赖2.实体类编写3. 编写导出操作 1.maven依赖 <!-- easypoi 依赖 excel导出--> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-spring-...
  • 文章目录一、使用easy poi注解实现二级表头效果二、实现1、依赖2、实体bean3、生成导出4、问题5、使用ExcelExportEntity实现动态表头传值 一、使用easy poi注解实现二级表头效果 使用注解写的话easy poi 貌似最多...
  • easy poi 生产多表excel

    2020-12-16 09:33:43
    调用poi接口 * * @param queryParam 查询参数 * @param response 响应参数 * @return 下载结果 * @author: leiming5 */ public void downloadExcel(StTcrrQueryParam queryParam, HttpServletResponse response) { ...
  • Easy Poi入门

    2018-11-21 16:17:00
    最近有一个需求,就是把excel中的内容,解析成Json对象格式的文件输出。 然后就上网找了一波资料,大神们都说用POI来做。但是我看了一下POI的解析过程,但是为了秉... easy poi的理念就是为了让没有接触过POI的开...
  • spring boot 整合Easy POI

    2021-11-23 20:13:51
    POI 工具类,Excel的快速导入导出,Excel模板导出,Word模板导出,可以仅仅5行代码就可以完成Excel的导入导出,修改导出格式...-- Easy POI 依赖 --> <dependency> <groupId>cn.afterturn</groupId>
  • Java easy poi 模板 导出 内存溢出 OutOfMemoryError异常描述使用的jar包版本原因分析合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格...
  • 1.easy poi与springboot的整合 1.1 依赖 依赖 <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-spring-boot-starter</artifactId> <version>4.1.3</...
  • 一、 Excel读写引用库替换为Easy POI 原因: Easy POI更简单(具体可对比下方代码跟2.5中Excel读取代码) Easy POI学习地址:http://easypoi.mydoc.io/ 1. 引入库如下: 2. Maven 坐标 <dependency&...
  • easypoi4.1.0.jar以及其依赖的其他jar包,包括了easypoi-web-4.1.0.jar,easypoi-base-4.1.0.jar,easypoi-annotation-4.1.0.jar,poi-4.1.0.jar,poi-ooxml-4.1.0.jar,poi-ooxml-schemas-4.1.0.jar,poi-scratchpad-...
  • easy poi的使用

    2021-09-02 14:39:31
    --easy poi依赖--> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-spring-boot-starter</artifactId> <version>4.1.3</version> &...
  • Easy excel: https://www.yuque.com/easyexcel/doc/easyexcel 还有什么autoPoi大家也可以了解下 体会: 1.导出需要考虑实际的数据量,大的话 需要考虑分页 避免内存溢出 2.共享数据充分利用缓存,如redis 或者

空空如也

空空如也

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

easypoi