精华内容
下载资源
问答
  • 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导出

    错误信息:
    java.lang.IllegalStateException: Cannot add merged region C3:E3 to sheet because it overlaps with an existing merged region (B3:D3).

    image.png

    报表如上图所示,三级子表头有重复项,在使用easyExcel 1.1.1版本中会出现将三级子表头合并的情况

    数据库导入情况如下

    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('orgName','组织','Y','Y','20','1');
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('outputIncome','销项,开具增值税发票,不含税收入','Y','Y','20','2');
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('outputTax','销项,开具增值税发票,税额','Y','Y','20','3');
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('outputNoInvoicedIncome','销项,未开票收入,不含税收入','Y','Y','20','4');
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('outputNoInvoicedTax','销项,未开票收入,税额','Y','Y','20','5');
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('outputTotalIncome','销项,合计,不含税收入合计','Y','Y','20','6');
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('outputTotalTax','销项,合计,税额合计','Y','Y','20','7');
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('outputTotalAmount','销项,合计,价税合计','Y','Y','20','8');
    
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('subInitial','分包,期初留抵','Y','Y','20','9');
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('subTotalAmount','分包,分包发生额','Y','Y','20','10');
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('subAdjustiveNum','分包,调整数','Y','Y','20','11');
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('subRealTotalAmount','分包,实际抵扣分包含税金额','Y','Y','20','12');
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('subRealTax','分包,实际抵扣分包税额','Y','Y','20','13');
    INSERT INTO `globle_item_config` (`field`,`column_zh`,`benable`,`checked`,`busi_id`,`sort`) VALUES ('subLastAmount','分包,期未留抵数','Y','Y','20','14');
    

    导出数据时会报错:
    java.lang.IllegalStateException: Cannot add merged region C3:E3 to sheet because it overlaps with an existing merged region (B3:D3).

    考虑到是因为
    image.png

    销项一级子表头下的三级子表头有重复项,会被自动合并,合并过程中,因为“开具增值税发票”单元格已经合并,所以“税额”项在合并单元格时就报错了

    解决方案:
    方案1. 将相同的列名改成不一样的,这样就可以达到想要的合并单元格的效果。 缺点:可能导出的列名并不是自己想要的。
    方案2. 为了与页面上列名保持一致,就尝试的升级了easyExcel的版本,结果测试了一下,就OK了。将版本从1.1.1升级到2.0.5版本

    附上测试代码:

    private static byte[] getFileBytes(List<List<String>> easyData) {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0);
    
        Table table = new Table(1);
        List<List<String>> head = new ArrayList<>();
        //提取表头
        List<String> headNames = easyData.get(0);
        headNames.forEach(headName -> {
          List<String> headCoulumn = new ArrayList<>();
          String[] headStr = headName.split(",");
          if(headStr != null) {
            for (String str : headStr) {
              headCoulumn.add(str);
            }
          }
          head.add(headCoulumn);
        });
        table.setHead(head);
        //提取数据并写入流
        writer.write0(easyData.subList(1, easyData.size()), sheet, table);
        writer.finish();
        byte[] bytes = out.toByteArray();
        return bytes;
      }
    
      public static void main(String[] args) throws IOException {
    
        List<List<String>> exc = new ArrayList<>();
        List<String> rows = new ArrayList<>();
        rows.add("组织");
        rows.add("销项,开具增值税发票,不含税收入");
        rows.add("销项,开具增值税发票,税额");
        rows.add("销项,未开票收入,不含税收入");
        rows.add("销项,未开票收入,税额");
        rows.add("销项,合计,不含税收入合计");
        rows.add("销项,合计,税额合计");
        rows.add("销项,合计,价税合计");
        rows.add("分包,期初留抵");
        rows.add("分包,分包发生额");
        rows.add("进项,期初留抵");
        exc.add(rows);
        for(int i = 0; i < 3; i++){
          List<String> rowsValue = new ArrayList<>();
          rowsValue.add("组织名称"+i);
          rowsValue.add(String.valueOf(2+i));
          rowsValue.add(String.valueOf(2+i));
          rowsValue.add(String.valueOf(2+i));
          rowsValue.add(String.valueOf(2+i));
          rowsValue.add(String.valueOf(2+i));
          rowsValue.add(String.valueOf(2+i));
          rowsValue.add(String.valueOf(2+i)i);
          rowsValue.add(String.valueOf(2+i));
          rowsValue.add(String.valueOf(2+i));
          exc.add(rowsValue);
        }
        byte[] b = getFileBytes(exc);
        FileUtils.writeByteArrayToFile(new File("D:\\tmp\\testMuliteHead..xlsx"), b);
      }
    
    展开全文
  • EasyExcel导出

    2020-07-29 14:29:57
    导入依赖 <dependency>...easyexcel</artifactId> <version>2.1.6</version> </dependency> 导出 Controller import javax.servlet.http.HttpServletResponse; /** * 导出

    导入依赖

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.6</version>
    </dependency>
    

    导出

    Controller

    import javax.servlet.http.HttpServletResponse;
    
    /**
     * 导出数据
     * @param response response
     * @return result
     * @author liu
     */
    @GetMapping("exportExcelData")
    public Result exportExcelData(HttpServletResponse response) {
        service.exportExcelData(response);  
        return Result.ok();
    }
    

    Service

    import javax.servlet.http.HttpServletResponse;
    
    /**
     * 导出数据
     * @param response response
     * @author liu
     */
     void exportExcelData(HttpServletResponse response);
    

    ServiceImpl

    import javax.servlet.http.HttpServletResponse;
    
    @Override
    public void exportExcelData(HttpServletResponse response) {
        String fileName = "数据信息";
    	String sheetName = "数据信息";
    	/**  数据库查询数据并处理 */
    	List<ExcelData> dataList = mapper.exportExcelData(); //返回数据信息
    	
    	List<ExcelData> list = new ArrayList<>();
        
        for (ExcelData excelData : dataList) {
    		ExcelData excelPlanData = ExcelData.builder()
    		        .id(excelData.getId)
    		        .name(excelData.getName)
    		        .age(excelData.getAge)
    		        .sex(excelData.getSex).build();
    		list.add(excelPlanData);
        }
        try {
        	ExcelUtil.writeExcel(response,list,fileName,sheetName,ExcelData.class);	
        } catch (Exception e) {
        	e.printStackTrace();	
        }
    	
    }
    

    Model

    import com.alibaba.excel.annotation.ExcelProperty;
    import lombok.Builder;
    import lombok.Data;
    
    /**
     * 数据导出实体类
     * @author liu
     */
    @Data
    @Builder
    public class ExcelData {
    	
    	/**
    	 * id
    	 */
    	//导出的Excel标头信息,以及所在第几列
    	@ExcelProperty(value = "id", index = 0)  
    	private Integer id;
    
    	/**
    	 * 姓名
    	 */
    	@ExcelProperty(value = "姓名", index = 1) 
    	private String name;
    
    	/**
    	 * 年龄
    	 */
    	@ExcelProperty(value = "年龄", index = 2) 
    	private Integer age;
    
    	/**
    	 * 性别
    	 */
        @ExcelProperty(value = "性别", index = 3) 
    	private String sex;
    }
    

    ExcelUtil

    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.support.ExcelTypeEnum;
    import com.alibaba.excel.write.metadata.style.WriteCellStyleStrategy;
    
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.util.List;
    
    import javax.servlet.http.HttpServletResponse;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    
    /**
     * 导出工具类
     * @author liu
     */
    public class ExcelUtil {
    	
        /**
    	 * 导出
    	 * @param response response
    	 * @param data data
    	 * @param fileName fileName
    	 * @param sheetName sheetName
    	 * @param clazz clazz
         */
        public static void writeExcel(HttpServletResponse response,List<? extends Object> data, 
                String fileName,String sheetName,Class clazz) {
            //表头样式
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
     		//设置表头居中对齐
     		headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
     		//内容样式
     		WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
     		//设置内容靠左对齐
     		contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
     		HorizontalCellStyleStrategy horizontalCellStyleStrategy = 
     		        new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
     		try {
    		    EasyExcel.write(getOutputStream(fileName,response),clazz)
    		 	        .excelType(ExcelTypeEnum.XLSX)
    		 	        .sheet(sheetName)
    		 	        .registerWriteHandler(horizontalCellStyleStrategy)
    		 	        .doWrite(data);
    		} catch (Exception e) {
    		    e.printStackTrace();
    		}
        }
        
        /**
         * 格式
         * @param fileName fileName
         * @param response response
         * @throws Exception exception
         * @author liu
         * @return java.io.OutputStream 
         */ 
        private static OutputStream getOutputStream(String fileName,HttpServletResponse response) throws Exception {
            fileName = URLEncoder.encode(fileName,"UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment;filename=" + ".xlsx");
            return response.getOutputStream();
        }
    }
    
    展开全文
  • easyexcel导出

    2020-01-06 15:57:43
    需要添加的jar包: <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.6</version>...导出的control...

    需要添加的jar包:

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.6</version>
    </dependency>
    

    导出的controller代码实现:

    package com.ruoyi.web.controller.export;
    
    import com.alibaba.excel.EasyExcel;
    import com.google.common.collect.Lists;
    import com.ruoyi.web.controller.demo.domain.UserOperateModel;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    
    @Controller
    @RequestMapping("/download")
    public class Export {
        private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    
    	//第一种导出
        @GetMapping("/download")
        public void download(HttpServletResponse response) throws IOException {
            // 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), UserOperateModel.class).sheet("模板").doWrite(Lists.newArrayList());
        }
    	//第二种导出
        @GetMapping("/downloads")
        public void downloads(HttpServletResponse response) throws IOException {
            String fileName = "黑名单导入模板_" + sdf.format(new Date()) + ".xls";
            response.setHeader("Content-disposition",
                    "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));// 设置文件头编码格式
            response.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");// 设置类型
            response.setHeader("Cache-Control", "no-cache");// 设置头
            response.setDateHeader("Expires", 0);// 设置日期头
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), UserOperateModel.class).sheet("模板").doWrite(Lists.newArrayList());
        }
    }
    

    实体类:

    	/**
         * 忽略这个字段
         */
        @ExcelIgnore
        private int userId;
    
        @ExcelProperty("用户编号")
        private String userCode;
        @ExcelProperty("用户姓名")
        private String userName;
    
        @ExcelProperty("用户性别")
        private String userSex;
        @ExcelProperty("用户手机")
        private String userPhone;
    
        @ExcelIgnore
        private String userEmail;
    
        @ExcelIgnore
        private double userBalance;
    
        @ExcelIgnore
        private String status;
    
        @ExcelIgnore
        private Date createTime;
    

    导出的效果:
    在这里插入图片描述

    展开全文
  • EasyExcel导出excel

    2020-06-27 18:43:23
    EasyExcel导出excel 1.首先建立一个实体类 public class ExcelMode extends BaseRowModel { @ExcelIgnore //excel中不导出字段 private Integer id; @ExcelProperty(value = "姓名" ,index = 0) private String ...

    EasyExcel导出excel

    1.首先建立一个实体类

    public class ExcelMode extends BaseRowModel {
        @ExcelIgnore   //excel中不导出字段
        private Integer id;
        @ExcelProperty(value = "姓名" ,index = 0)
        private String name;
        @ExcelProperty(value = "密码" ,index = 1)
        private String password;
        @ExcelProperty(value = "手机号码",index = 2)
        private String phone;
        @ExcelProperty(value = "性别",index = 2,converter = SettlementConverter.class )
         //SettlementConverter.class 将数据库枚举字段转换成字符串类型(例:1/男,2/女
        private Integer sex;
    
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getPassword() {
            return password;
        }
    
        public void setPassword(String password) {
            this.password = password;
        }
    
        public String getPhone() {
            return phone;
        }
    
        public void setPhone(String phone) {
            this.phone = phone;
        }
    
        public Integer getSex() {
            return sex;
        }
    
        public void setSex(Integer sex) {
            this.sex = sex;
        }
    }
    
    1. SettlementConverter.class 实现 Converter 接口
    public class SettlementConverter implements Converter<Integer> {
    
        @Override
        public Class supportJavaTypeKey() {
            return null;
        }
    
        @Override
        public CellDataTypeEnum supportExcelTypeKey() {
            return null;
        }
    
        @Override
        public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
            return null;
        }
    
        @Override
        public CellData convertToExcelData(Integer s, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
            if(s == 1){
                return new CellData("男");
            }else {
                return new CellData("女");
            }
        }
    
    1. 导出excel
        @RequestMapping(value = "/toExcel")
    	@ResponseBody
    	public Object toExcel(HttpServletRequest rep , HttpServletResponse reps) {
    		try {
    			ExcelMode u = new ExcelMode();
    			
    			List<ExcelMode> list = userservice.ExcelMode();
    			String fileName = new String("文件名称.xlsx".getBytes(), "ISO-8859-1");
    			reps.addHeader("Content-Disposition", "filename=" + fileName);
    			ServletOutputStream out = reps.getOutputStream();
    			EasyExcelFactory.write(out, ExcelMode.class).sheet("文件标签名称").doWrite(list);
    		}catch (Exception e){
    			e.printStackTrace();
    		}
    		return null;
    	}
    
    展开全文
  • EasyExcel 导出报表

    2020-10-28 16:01:18
    使用阿里的EasyExcel导出简单的报表 阿里easyExcel官网文档 1.导入依赖 <dependency> <groupId>cglib</groupId> <artifactId>cglib</artifactId> <version>3.1</version&...
  • 使用EasyExcel导出Excel

    2017-09-29 09:52:28
    Excel EasyExcel 导出
  • 利用easyexcel导出文件

    2020-06-03 10:01:03
    利用easyexcel导出文件导出简单快速导出 导出 简单快速导出 1.首先pom文件引入jar包 下面展示一些 内联代码片。 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel<...
  • EasyExcel导出自定义合并单元格 https://cloud.tencent.com/developer/article/1671316
  • easyExcel导出和读取

    2020-12-04 19:34:54
    easyExcel导出和读取 读取(导入) 导包 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </...
  • java easyexcel 导出 excel

    2020-10-31 14:45:49
    java easyexcel 导出 excel 1 简介 EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 2 环境 springboot + idea 3 引入pom文件 <dependency> <...
  • EasyExcel导出并下载

    2021-01-02 16:57:53
    第一步:添加依赖 <dependency> <groupId>.../groupId>...说明:阿里easyexcel导出excel文件的思路: 和管理后台普通的查询列表一样,设计Api需要具备按条件查询的功能特性 按条件查询
  • JAVA使用easyexcel导出excel

    千次阅读 2018-11-28 14:32:03
    JAVA使用easyexcel导出excel 导出excel的工具有很多,如poi,jxl等但是他们导出excel会比较的消耗内存,一旦数据大起来可能造成内存溢出,而easyexcel很好的避免了内存溢出的问题。 首先下载easyexcel的依赖包: ...
  • EasyExcel导出自定义合并单元格策略

    千次阅读 2019-12-25 17:15:06
    EasyExcel导出自定义合并单元格策略 调用方式 //引用自定义合并单元格 ExcelWriter excelWriter = EasyExcel.write(outputStream) .withTemplate(getTemplateInputStream("excel_templates/xxx.xlsx")) .regi...
  • easyExcel 导出 excel 自定义表头 效果图: 代码: @RequestMapping(value = "export",method = RequestMethod.GET) public void export(HttpServletResponse response,Integer yds,Integer sds,String type) throws...
  • 使用POI做导出没有问题,样式也OK,也能导出,但是,数据量过大的时候,会内存溢出,于是使用EasyExcel导出避免内存溢出。 EasyExcel也可用于普通导出。 一.先说创建的样式 二.创建结果Bean,为对应的表头 @...
  • easyExcel导出表格及合并单元格 前言 废话不多说,直接上效果图。 效果图 合并之前导出的: 合并之后导出的: 代码的实现主要是合并之后的,为了更好的分清数据之间的关系。 二、代码 目录 <只需要注意红色的&...
  • EasyExcel导出某列为空解决方法

    千次阅读 2020-09-07 18:26:53
    EasyExcel导出某列为空easyExcel对数据进行导出,导出某列为空 easyExcel对数据进行导出,导出某列为空 easyExcel中对于属性命名为强制驼峰模式,当具有明确意义时可以识别 winRoomId 获胜房间号 当具有模糊意义时...

空空如也

空空如也

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

easyexcel导出