精华内容
下载资源
问答
  • EasyExcel导出
    2021-12-15 10:28:37

    工具类

        /**
         * excel 流导出(需返回前端指定信息时调用该方法)
         *
         * @param response   输出流
         * @param fileName   文件名前缀
         * @param exportData 导出数据
         * @param clazz      Class对象
         * @throws IOException 异常
         */
        public static void exportExcel(HttpServletResponse response, String fileName, List exportData, Class clazz) throws IOException {
            exportExcel(response, fileName, exportData, clazz, "导出成功", ResultCode.SUCCESS.getCode());
        }
    
     /**
         * excel 流导出(需返回前端指定信息时调用该方法)
         *
         * @param response   输出流
         * @param fileName   文件名前缀
         * @param exportData 导出数据
         * @param clazz      Class对象
         * @param resultMsg  返回前端数据(放header里面返回)为空默认导出成功
         * @param code       返回前端状态码:与controller返回状态码一致 全部成功:600 失败:800 为空默认600(放header里面与resultMsg一起返回)
         * @throws IOException 异常
         */
        public static void exportExcel(HttpServletResponse response, String fileName, List exportData, Class clazz, String resultMsg, Long code) throws IOException {
            try {
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setCharacterEncoding("utf-8");
                
                // 设置返回前端信息
                Map<String, Object> result = new HashMap<>();
                result.put("code", code);
                result.put("msg", resultMsg);
                response.setHeader("result", URLEncoder.encode(JSONUtil.toJsonStr(result), "UTF-8"));
                
                //设置该属性前端可见
                response.setHeader("Access-Control-Expose-Headers", "result,Content-disposition");
                
                // 使用URLEncoder.encode 防止中文乱码
                String fileNameEncode = URLEncoder.encode(fileName, "UTF-8") + DateUtil.formatDateTime(new Date());
                response.setHeader("Content-disposition", "attachment;filename=" + fileNameEncode + ".xlsx");
    
                // 设置不关闭流
                EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(new CellWriteWidthHandler()).autoCloseStream(Boolean.FALSE).sheet(fileName).doWrite(exportData);
            } catch (Exception e) {
                log.error("下载文件失败:", e);
                // 重置response
                response.reset();
                response.setContentType("application/json");
                response.setCharacterEncoding("utf-8");
                response.getWriter().println(JSON.toJSONString(CommonResult.failed("下载失败")));
            }
        }
    

    使用:

     @ApiOperation("导出车辆")
        @PostMapping(value = "/excel/export")
        public void export(@ApiParam(name = "搜索条件") @RequestBody @Valid CarPageReq carPageReq, HttpServletResponse response) throws IOException {
            List<CarInfo> carInfos = iCarInfoService.getCarInfo(carPageReq);
            List<CarExportExcelDTO> carExportExcelDTOS = new ArrayList<>();
            CarExportExcelDTO carExportExcelDTO = null;
            for (CarInfo carInfo : carInfos) {
                carExportExcelDTO = new CarExportExcelDTO();
                BeanUtils.copyProperties(carInfo, carExportExcelDTO);
                carExportExcelDTO.setFirstRecordDate(DateUtil.format(carInfo.getFirstRecordDate(), "yyyy-MM-dd"));
                carExportExcelDTO.setWhetherUse(UseStatusEnum.translate(carInfo.getWhetherUse()));
                carExportExcelDTO.setInsuranceStatus(InsuranceStatusEnum.translate(carInfo.getInsuranceStatus()));
                carExportExcelDTOS.add(carExportExcelDTO);
            }
            ExcelUtils.exportExcel(response, "车辆信息", carExportExcelDTOS, CarExportExcelDTO.class);
        }
    

    自动调整列宽:

    public class CellWriteWidthHandler extends AbstractColumnWidthStyleStrategy {
    
        private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
    
        @Override
        protected void setColumnWidth(CellWriteHandlerContext context) {
            List<WriteCellData<?>> cellDataList = context.getCellDataList();
            Cell cell = context.getCell();
            boolean needSetWidth = context.getHead() || (null != cellDataList && cellDataList.size() > 0);
            WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();
            if (needSetWidth) {
                Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
                if (maxColumnWidthMap == null) {
                    maxColumnWidthMap = new HashMap<>();
                    CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
                }
    
                Integer columnWidth = this.dataLength(cellDataList, cell, context.getHead());
                if (columnWidth >= 0) {
                    if (columnWidth > 50) {
                        columnWidth = 50;
                    }
    
                    Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                    if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                        maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                    }
    
                }
            }
        }
    
        private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
            if (isHead) {
                return cell.getStringCellValue().getBytes().length;
            } else {
                CellData cellData = cellDataList.get(0);
                CellDataTypeEnum type = cellData.getType();
                if (type == null) {
                    return -1;
                } else {
                    switch (type) {
                        case STRING:
                            return cellData.getStringValue().getBytes().length;
                        case BOOLEAN:
                            return cellData.getBooleanValue().toString().getBytes().length;
                        case NUMBER:
                            return cellData.getNumberValue().toString().getBytes().length;
                        default:
                            return -1;
                    }
                }
            }
        }
    }
    
    更多相关内容
  • EasyExcel导出.txt

    2021-11-04 11:53:48
    EasyExcel 导出,含动态表头导出
  • easyExcel导出合并单元格策略 WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)) .registerWriteHandler(new CustomCellWriteHandler()) //设置合并单元格策略 .registerWriteHandler(new ...
  • easyExcel 2.1.7 poi 3.17 springboot 2.2.5 lombok 1.18.12
  • EasyExcel 导出

    2021-08-12 15:11:43
    文章目录1.EasyExcel 写入1.1. 创建excel映射对象、一次写入response1.2. 不创建excel映射对象、多次写入File1.3. 单元格合并策略1.4. 列宽样式策略1.5. 转换器1.5.1. 转换器注入方式1.5.2....excel导出实体: @

    1.EasyExcel 导出

    1.1. Response流导出单sheet页

    1.1.1 response流处理(防止中文乱码):

    		response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode(getTitle(), "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    

    1.1.2 根据excel映射对象导出(适合结构化数据:列表台账)

    excel导出实体:

    @Data
    @NoArgsConstructor
    //@ContentRowHeight(50)//内容单元格高度
    //@HeadRowHeight(50)//表头单元格高度
    //@ColumnWidth(50)//单元格宽度
    public class DemoExcelBO {
        @ExcelProperty(value = {"导出台账","序号"})
        private Integer no;
        @ExcelProperty(value = {"导出台账","编号"})
        private  String  code;
    }
    

    执行实际的导出动作:

            //要导出的excel 数据
            List<DemoExcelBO> dataList = new ArrayList<>();
            //指定outputStream和 excel映射实体
             EasyExcel.write(response.getOutputStream(), DemoExcelBO.class)
             			//注册 WriteHandler ,getDefaultWriteHandler() 见下
                        .registerWriteHandler(getDefaultWriteHandler())
                        //注入原生 或 自定义转换器
                        .registerConverter(new DateStringConverter())
                        .registerConverter(new BigDecimalStringConverter())
                        .sheet("这是指定要写入的sheet页")
                        .doWrite(dataList );
    

    1.1.3. 根据单元格集合导出(适合非结构化数据:详情页)

    		//执行excel写入对象
            ExcelWriter excelWriter = EasyExcel.write(url).build();
            //当前sheet页 写入对象创建者
            ExcelWriterSheetBuilder sheetBuilder = EasyExcel.writerSheet("测试sheet");
            //todo sheetBuilder  可以注入 writeHandler
            WriteSheet writeSheet = sheetBuilder.build();
            //实际要写入的数据 ,List<Object> 为每一行的合并前的数据,放入List中为整个要写入的数据
            List<List<Object>> dataList = new ArrayList<>();
            //可以多次调用  excelWriter.write() 方法 。 写入同一sheet 的话 ,WriteSheet 对象不变
            excelWriter.write(list, writeSheet);
            //关流
            excelWriter.finish();
    

    1.2. 单元格样式(背景色、加粗、字体、边框)

    HorizontalCellStyleStrategy 分别设置 表头 和 表体 的样式:

    	//默认设置的样式 (表头样式 和 表体样式 不同)
        default WriteHandler getDefaultWriteHandler() {
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            WriteFont headWriteFont = new WriteFont();
    //        headWriteFont.setBold(true);// 加粗
            headWriteFont.setFontHeightInPoints((short) 11);//字体大小
            headWriteCellStyle.setWriteFont(headWriteFont);
            headWriteCellStyle.setWrapped(false);//自动换行
    //        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);//CENTER
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            contentWriteCellStyle.setBorderBottom(BorderStyle.NONE);//THIN
            contentWriteCellStyle.setBorderLeft(BorderStyle.NONE);
            contentWriteCellStyle.setBorderRight(BorderStyle.NONE);
            contentWriteCellStyle.setBorderTop(BorderStyle.NONE);
            contentWriteCellStyle.setWrapped(false);//自动换行
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            return horizontalCellStyleStrategy;
    
        }
    

    1.3. 单元格合并策略

    easyExcel 抽象父类类 :AbstractMergeStrategy

    public abstract class AbstractMergeStrategy implements CellWriteHandler {
        @Override
        public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
            Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    		//在创建单元格之前调用
        }
    
        @Override
        public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
            Head head, Integer relativeRowIndex, Boolean isHead) {
    		//创建单元格后调用
    	}
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
            List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            //在单元格上的所有操作完成后调用
            if (isHead) {
                return;
            }
            merge(writeSheetHolder.getSheet(), cell, head, relativeRowIndex);
        }
    
        //合并策略
        protected abstract void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex);
    }
    

    1.3.1 只合并一次

    easyExcel 预制子类:OnceAbsoluteMergeStrategy

    //创建单元格时只合并一次
    public class OnceAbsoluteMergeStrategy extends AbstractMergeStrategy {
        private int firstRowIndex;
        private int lastRowIndex;
        private int firstColumnIndex;
        private int lastColumnIndex;
    }
    

    1.3.2. 循环合并

    easyExcel 预制子类:LoopMergeStrategy

    //循环区域合并
    public class LoopMergeStrategy extends AbstractMergeStrategy {
        private int eachRow;
        private int columnCount;
        private int columnIndex;
    }
    

    继承 AbstractMergeStrategy 重写 merge()方法 ,可自定义合并策略

    1.4. 列宽样式策略

    easyExcel 抽象父类:

    public abstract class AbstractColumnWidthStyleStrategy implements CellWriteHandler, NotRepeatExecutor {
    
        @Override
        public String uniqueValue() {
            return "ColumnWidthStyleStrategy";
        }
    
        @Override
        public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
            Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    		//在创建单元格之前调用
    }
    
        @Override
        public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
            Head head, Integer relativeRowIndex, Boolean isHead) {
    		//在创建单元格之后调用
    }
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        	 //在单元格上的所有操作完成后调用
            setColumnWidth(writeSheetHolder, cellDataList, cell, head, relativeRowIndex, isHead);
        }
    
     	//列宽样式策略
        protected abstract void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
            Integer relativeRowIndex, Boolean isHead);
    
    }
    

    1.4.1. 根据每个列标题返回列宽

    easyExcel 预制子类:AbstractHeadColumnWidthStyleStrategy

    //根据每个列标题返回列宽
    public abstract class AbstractHeadColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
    }
    

    1.4.2. 以最长列的宽度作为所有列的宽度

    easyExcel 预制子类:LongestMatchColumnWidthStyleStrategy

    //以最长列的宽度作为宽度
    public class LongestMatchColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
    }
    

    1.4.3. 定长列宽

    easyExcel 预制子类:SimpleColumnWidthStyleStrategy

    //所有列的宽度相同
    public class SimpleColumnWidthStyleStrategy extends AbstractHeadColumnWidthStyleStrategy {
    }
    

    继承 AbstractColumnWidthStyleStrategy 重写 setColumnWidth()方法 ,可自定义列宽策略

    1.4.4. 自动调整列宽(推荐)

    在这里插入代码片
    

    1.5. 转换器

    easyExcel 转换器 父接口:

    public interface Converter<T> {
    
    	// 写入excel时 ,指定一个class ,该class 会在写入时 被拦截执行转换
        Class supportJavaTypeKey();
        // 读取excel时 ,指定一个excel字段类型,该类型 会在读取时 被拦截执行转换
        CellDataTypeEnum supportExcelTypeKey();
    
    	//写入时 , java 类型 转换 excel数据类型 动作
        T convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
            GlobalConfiguration globalConfiguration) throws Exception;
    
       //读取时 , excel数据类型 转换  java 类型动作
        CellData convertToExcelData(T value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration)
            throws Exception;
    }
    

    有预制转换器,均实现改接口,如需自定义转换器,可实现 Converter 接口。

    1.5.1. 转换器注入方式

    方式一: 在excel实体上指定 某些特定字段生效

    public class ContractAccountExcelBO {
        @ExcelProperty(value = {"合同导出台账","","序号"} ,converter = IntegerStringConverter.class)
        private Integer no;
    }
    

    方式二: .registerConverter() 方法注入 ,本次读取或写入时,均生效

     //要导出的excel 数据
    List<DemoExcelBO> dataList = new ArrayList<>();
    EasyExcel.write(response.getOutputStream(),DemoExcelBO.class )
                        .registerConverter(new BigDecimalStringConverter())
                        .doWrite(dataList);//
    

    1.5.2. 转换器 注解

    指定日期格式注解:@DateTimeFormat(“yyyy-MM-dd”)
    指定数字格式注解:@NumberFormat("#.00")
    NumberFormat 中 format格式 遵循 Bigdecimal.format() 策略(#不补位,没有数字则空。0自动补位,没有数字自动补零)。

    1.6. RowWriteHandler 行处理器

    // 行创建 动作 拦截器
    public interface RowWriteHandler extends WriteHandler {
    	//行 创建前执行
        void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex,
            Integer relativeRowIndex, Boolean isHead);
    	//行 创建后执行
        void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
            Integer relativeRowIndex, Boolean isHead);
    	//在该行的所有操作完成后调用。填充数据时不调用此方法。
        void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
            Integer relativeRowIndex, Boolean isHead);
    }
    

    1.7. CellWriteHandler 单元格处理器

    // 单元格创建 动作 拦截器
    public interface CellWriteHandler extends WriteHandler {
    	//单元格创建 前动作
        void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head,
            Integer columnIndex, Integer relativeRowIndex, Boolean isHead);
    	//单元格创建 后动作
        void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head,
            Integer relativeRowIndex, Boolean isHead);
    	//在单元格上的所有操作完成后调用
        void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
            List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead);
    }
    

    1.8. 图片导出

    EasyExcel支持5种图片类型导出,根据1.1.3.把图片形式放入单元格中,写会即可

    @Data
    @ContentRowHeight(200)
    @ColumnWidth(200 / 8)
    public class ImageData {
        // 图片导出方式有5种
        private File file;
        private InputStream inputStream;
        /**
         * 如果string类型 必须指定转换器,string默认转换成string,该转换器是官方支持的
         */
        @ExcelProperty(converter = StringImageConverter.class)
        private String string;
        private byte[] byteArray;
        /**
         * 根据url导出 版本2.1.1才支持该种模式
         */
        private URL url;
    }
    
    

    Excel解析工具easyexcel全面探索
    easyexcel复杂格式导出、自定义合并
    图片导出

    展开全文
  • 使用EasyExcel导出数据

    2021-07-29 17:09:49
    使用EasyExcel导出 下载模板 导入excel功能,都需要对应的模板导入,也有动态的表头数据导入,可以看excel行数据不规则解析匹配。这里一般都是固定excel文件,存放在项目路径下面,下载模板的时候都是找对应的文件。...

    使用EasyExcel导出

    下载模板

    导入excel功能,都需要对应的模板导入,也有动态的表头数据导入,可以看excel行数据不规则解析匹配。这里一般都是固定excel文件,存放在项目路径下面,下载模板的时候都是找对应的文件。

    
    @GetMapping("/downloadTemplate")
        public void downloadTemplate(HttpServletResponse response) {
            String fileName = "导入模板.xlsx";
            ClassPathResource classPathResource = new ClassPathResource("template/file.xlsx");
            try (InputStream inputStream = classPathResource.getInputStream();
                 OutputStream out = response.getOutputStream();) {
                setResponseAttribute(fileName, response);
                Map<String, String> map = new HashMap<>(4);
                int year = DateUtil.thisYear();
                map.put("lastYear", String.valueOf(year - 1));
                map.put("currentYear", String.valueOf(year));
                ExcelWriterBuilder writerBuilder = EasyExcel.write(out).withTemplate(inputStream);
                //这边第二个sheet页面里面有个参数需要动态生成,指定了往第二个sheet页里面填充值
                writerBuilder.sheet(1).doFill(map);
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
    

    数据下载

    表头数据修改

    一般导出数据固定的话都可以使用对象导入,建立对应的对象,使用EasyExcel中相应的注解,对Excel中单元格样式,字体样式,背景颜色等等都可以实现。

    /**
     * 
     * @author 洛必达法则不会
     * @since 2021-07-22
     */
    @Data
    @HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER)
    @HeadFontStyle(fontHeightInPoints = 10, fontName = "微软雅黑")
    @ContentFontStyle(fontHeightInPoints = 10)
    @HeadRowHeight(18)
    @ColumnWidth(14)
    public class TalentSituationHeadData {
        /**
         * 年份
         */
        @ColumnWidth(8)
        @ExcelProperty(value = "年份", index = 0)
        private String year;
    
        /**
         * 事业部
         */
        @ExcelProperty(value = "事业分部", index = 1)
        private String deptName;
        
        /**
         * 待招岗位
         */
        @ExcelProperty(value = "待招岗位", index = 2)
        private String recruitedPost;
    
        /**
         * 姓名
         */
        @HeadStyle(fillForegroundColor = 44)
        @ExcelProperty(value = "姓名", index = 3)
        private String name;
    }
    
    

    本次功能导入需要导出一个Excel包含多个sheet页,其中一个sheet页面的表头需要根据时间动态生成,直接使用@ExcelProperty注解,value值都是固定的。

    // 首先尝试的通过 WriteSheet中head属性获取表头的字段,再修改里面对应的值,实际运行中属性值是null,此方法不可以
    WriteSheet potentialCustomerSheet = EasyExcel.writerSheet(index,sheetNameEnum.getName()).head(PotentialCustomerHeadData.class).build();
    List<List<String>> headList = potentialCustomerSheet.getHead();
    //另一种就是重新构造List<List<String>> headList,通过EasyExcel.write(fileName).head(headList).sheet("模板");写入表头,这种方式在对象的字体颜色注解就没有,需要重新构造对应的格式,也比较繁琐
    List<List<String>> headList = buildHeadDataList();
    EasyExcel.write(fileName).head(headList).sheet("模板");
    //之前的逻辑写好,不想用上面的方法,后面就采用反射的方式,拿到对应属性上面的注解,修改里面的值再通过EasyExcel.writerSheet写入
    String year = StrUtil.isNotBlank(param.getStartDate())
                    ? param.getStartDate().substring(0, 4)
                    : Integer.toString(DateUtil.thisYear());
    Field yearEstimateField = PotentialCustomerHeadData.class.getDeclaredField("yearEstimate");
    yearEstimateField.setAccessible(true);
    ExcelProperty excel = yearEstimateField.getAnnotation(ExcelProperty.class);
    InvocationHandler excelH = Proxy.getInvocationHandler(excel);
    // 获取 AnnotationInvocationHandler 的 memberValues 字段
    Field excelF = excelH.getClass().getDeclaredField("memberValues");
    excelF.setAccessible(true);
    Map excelValues = (Map) excelF.get(excelH);
    excelValues.put("value", new String[]{"项目利润", String.format("%s预估\n(万元/年)", year)});
    WriteSheet potentialCustomerSheet = EasyExcel.writerSheet(index, sheetNameEnum.getName()).head(PotentialCustomerHeadData.class).build();
    
    合并单元格

    刚好一个表单导出的时候,需要实现单元格合并,实现AbstractMergeStrategy类的方法,具体合并策略根据业务需求实现。

    //策略类
    public class CellMergeStrategy extends AbstractMergeStrategy {
        private Map<String, List<RowRange>> strategyMap;
        private Sheet sheet;
    
        public CellMergeStrategy() {
        }
    
        public CellMergeStrategy(Map<String, List<RowRange>> strategyMap) {
            this.strategyMap = strategyMap;
        }
    
        @Override
        protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
            this.sheet = sheet;
            if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
                /**
                 * 保证每个cell被合并一次,如果不加上面的判断,因为是一个cell一个cell操作的,
                 * 例如合并A2:A3,当cell为A2时,合并A2,A3,但是当cell为A3时,又是合并A2,A3,
                 * 但此时A2,A3已经是合并的单元格了
                 */
                for (Map.Entry<String, List<RowRange>> entry : strategyMap.entrySet()) {
                    int columnIndex = Integer.parseInt(entry.getKey());
                    entry.getValue().forEach(rowRange -> {
                        //添加一个合并请求
                        sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
                                rowRange.getEnd(), columnIndex, columnIndex));
                    });
                }
            }
        }
    }
    
    /**
     * Excel工具类
     */
    public class ExcelUtil {
        //获取需要合并的单元格
        public static Map<String, List<RowRange>> getProfitReachedMergeStrategy(List<ProfitReachedHeadData> profitReachedHeadDataList) {
            Map<String, List<RowRange>> strategyMap = new HashMap<>();
            ProfitReachedHeadData previousData = null;
            int size = profitReachedHeadDataList.size();
            for (int i = 0; i < size; i++) {
                ProfitReachedHeadData currentData = profitReachedHeadDataList.get(i);
                if (previousData != null) {
                    if (currentData.getDeptName().equals(previousData.getDeptName())) {
                        fillStrategyMap(strategyMap, "0", i);
                    }
                    if (currentData.getOrgName().equals(previousData.getOrgName())) {
                        fillStrategyMap(strategyMap, "1", i);
                    }
                }
                previousData = currentData;
            }
            return strategyMap;
        }
        
        /**
         * 增加合并策略
         * @param strategyMap   Map<String, List<RowRange>>
         * @param key           String
         * @param index         int
         */
        private static void fillStrategyMap(Map<String, List<RowRange>> strategyMap, String key, int index) {
            List<RowRange> rowRangeList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
            boolean flag = false;
            for (RowRange rowRange : rowRangeList) {
                //分段list中是否有end索引是上一行索引的,如果有,则索引+1
                if (rowRange.getEnd() == index) {
                    rowRange.setEnd(index + 1);
                    flag = true;
                }
            }
            //如果没有,则新增分段
            if (!flag) {
                rowRangeList.add(new RowRange(index, index + 1));
            }
            strategyMap.put(key, rowRangeList);
        }
        
        public class RowRange {
            private int start;
            private int end;
    	}
    }
    
    //业务实现
     List<ProfitReachedHeadData> profitSummaryHeadDataList = profitSummaryService.buildProfitReachedHeadDataList(entry.getValue());
    Map<String, List<RowRange>> summaryStrategyMap = ExcelUtil.getProfitReachedMergeStrategy(profitSummaryHeadDataList);
    WriteSheet profitSummarySheet = EasyExcel.writerSheet(index, String.format("%s-%s合计-%s月", sheetNameEnum.getName(),permissionOperate.getDeptName(),entry.getKey().substring(5))).registerWriteHandler(new CellMergeStrategy(summaryStrategyMap)).head(ProfitReachedHeadData.class).build();
    excelWriter.write(profitSummaryHeadDataList, profitSummarySheet);
    
    展开全文
  • 使用easyExcel导出数据

    2021-10-31 09:45:56
    如题,使用easyExcel简单的导出一下数据,代码如下: 首先在pom.xml里面引入如下依赖: <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <...

    如题,使用easyExcel简单的导出一下数据,代码如下:
    首先在pom.xml里面引入如下依赖:

    <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.6</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.16.20</version>
            </dependency>
    

    controller层代码:

    @RestController
    @RequestMapping("/export")
    public class ExportTestController {
    
        private static Logger log= LoggerFactory.getLogger(ExportTestController.class);
        @RequestMapping("/hello")
        public String hello(){
            return "hello world!";
        }
    
        @Autowired
        private ExcelTestService excelTestService;
    
        /**
         * 史上最简单的数据数据导出demo
         * 开箱即会
         */
        @RequestMapping("/exporttest")
        public void exporttest(HttpServletResponse response){
            excelTestService.export(response);
        }
    }
    

    service层代码:

    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.metadata.Sheet;
    import com.alibaba.excel.support.ExcelTypeEnum;
    import com.hdd.exportTest.model.Users;
    import com.hdd.exportTest.service.ExcelTestService;
    import org.springframework.stereotype.Service;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @ClassName ExcelTestServiceImpl
     * @DesCription TODO
     * @Author 584654
     * @Date 2021/10/31 9:08
     * @Version 1.0
     * @PackageName com.hdd.exportTest.service.impl
     **/
    @Service
    public class ExcelTestServiceImpl implements ExcelTestService {
    
        public void export(HttpServletResponse response){
            //需要导出的数据
            List<Users> list=getUsersList();
            try {
                ServletOutputStream out = response.getOutputStream();
                ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
                String fileName = new String("用户信息表格"
                        .getBytes("utf-8"), "iso8859-1");
                Sheet sheet1 = new Sheet(1, 0, Users.class);
                sheet1.setSheetName("sheet1");
    
                response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx");
                response.setContentType("multipart/form-data");
                response.setCharacterEncoding("utf-8");
                writer.write(list, sheet1);
                writer.finish();
                out.flush();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        private List<Users> getUsersList() {
            List<Users> usersList=new ArrayList<Users>();
            usersList.add(new Users("高俊也",28,"男"));
            usersList.add(new Users("李雪琴",25,"女"));
            return usersList;
        }
    }
    
    

    model层代码:

    import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.metadata.BaseRowModel;
    import lombok.Data;
    
    import java.io.Serializable;
    
    /**
     * @ClassName Users
     * @DesCription TODO
     * @Author 584654
     * @Date 2021/10/31 9:13
     * @Version 1.0
     * @PackageName com.hdd.exportTest.model
     **/
    @Data
    public class Users extends BaseRowModel implements Serializable {
    
        private static final long serialVersionUID = 15353L;
    
        @ExcelProperty(value = "姓名",index = 0)
        private String name;
    
        @ExcelProperty(value = "年龄",index = 1)
        private Integer age;
    
        @ExcelProperty(value = "性别",index = 2)
        private String sex;
    
        public Users(String name, Integer age, String sex) {
            this.name = name;
            this.age = age;
            this.sex = sex;
        }
    }
    
    

    启动项目后导出数据结果如下:
    在这里插入图片描述

    展开全文
  • easyExcel导出

    千次阅读 2020-03-30 18:32:09
    easyExcel导出 错误信息: java.lang.IllegalStateException: Cannot add merged region C3:E3 to sheet because it overlaps with an existing merged region (B3:D3). 报表如上图所示,三级子表头有重复项,在...
  • easyExcel导出通用方法

    2021-10-11 19:07:08
    easyExcel导出通用方法 开箱即用 导出效果 导出通用方法 ```java @Slf4j public class EasyExcelExport { /** * EasyExcel输出通用 * * @param response * @param title * @param companyName * @param ...
  • Java EasyExcel 导出

    2022-04-20 09:56:32
    maven依赖 <dependency>...easyexcel</artifactId> <version>2.2.7</version> </dependency> Model import java.math.BigDecimal; import java.util.Date; import com
  • EasyExcel导出大量数据

    千次阅读 2021-08-25 11:35:11
    1:EasyExcel导出数据 2:查询时分sheet页查询写入 3:自定义导出格式 1:pom文件: <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> &...
  • EasyExcel 动态表头 导出 ,非注解,后台导出, 可分页,可限制每页显示行数,依赖 com.alibaba easyexcel
  • 目前网上找到的EasyExcel自定义合并单元格都是ExcelFillCellMergeStrategy,这个工具类只要下一行的cell和上一行的cell内容相同就会合并,不符合目前的需求。本例也是在此基础上进行逻辑修改。 参考链接。 测试代码 ...
  • 使用easyexcel下载模板
  • 导出 Excel想要设置自定义的 导出时间样式方法 超详细
  • EasyExcel导出excel表格

    2022-04-02 17:41:10
    传统Excel操作或者解析都是利用Apach POI进行操作,但是使用过这个框架的人都知道,这个框架并不完美,有较...EasyExcel实现excel导出: try { //设置头居中 WriteCellStyle headWriteCellStyle = new WriteCellSty
  • EasyExcel导出工具类

    2022-02-22 16:42:21
    EasyExcel导出工具类
  • EasyExcel导出时,默认有个列宽,数据太长会隐藏起来,加个设置就可以了,但不能保证完全OK,但是可以用,总比没有强。 // 设置下载类型,这个类型支持在线预览 response.setContentType("application/octet-...
  • EasyExcel导出详解

    千次阅读 2022-04-17 14:18:42
    1.EasyExcel简单介绍 EasyExcel工具是阿里的一个操作excel的开源项目,对现有的POI框架进行性能优化,解决了大数据量时内存溢出的问题。同时封装的更加简单灵活,适合初学者上手。 EasyExcel仓库 EasyExcel官方文档 ...
  • EasyExcel导出图片

    2021-12-22 18:17:28
    在excel里导出图片: 1、数据实体 import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.*; import lombok.Data; import org.apache.poi.ss.usermodel....
  • easyExcel导出文件为空解决

    千次阅读 2021-10-24 23:27:05
    在操作过程中发现了一个问题,直接拷用官方文档的文件导出的文件是空的,在一番研究之下写出了后面的代码,实现了用easyExcel导出。直接上干货 包引入 这里说个贼有意思的事情,官方文档也是写的简介,简介到pom...
  • 1.导入jar包 <dependency>...easyexcel</artifactId> <version>3.0.5</version> </dependency> 2.点击导出按钮,浏览器开始下载对应的数据,也可以实现批量导出 3.实...
  • easyexcel导出excel文件之图片导出

    千次阅读 2021-03-09 22:39:16
    1.easyexcel导出图片简单操作参考官网 2.项目中遇到的问题:项目导出报表需要导出图片 easyexcel导出图片,数据库中的存储的图片是字符串类型(实际上还是图片的URL链接),需要先转为URL,再进行导出操作. 图片字段...
  • --easyexcel,推荐使用2.0 以上版本,功能更加完善--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version&...
  • 阅读官方文档(很有必要)文档链接 ...导出文档功能主要代码 java 部分 try{ response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder.
  • easyexcel 动态导出复杂表头 例子 带cellRange
  • EasyExcel导出报表(多table包含合并策略) 导入Pom <!-- easyexcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>...
  • easyexcel 导出excel文件

    2021-11-22 16:08:22
    easyexcel 引入: <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-collections4</artifactId> <version>4.4</version> </dependency&...
  • Java通过EasyExcel导出excel

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 7,291
精华内容 2,916
关键字:

easyexcel导出

友情链接: xzbot.rar