[alibaba/easyexcel]写入操作能否返回文件流，我需要将文件传到第三方服务器

2

贴代码 + 想要的效果

zhuangjiaju

3

@fish7790714

excel 读取
判断excel中数据，错误输入写入remark
将判断后的 model集合写入到新的 excel
获取输入流字节数组
上传至第三方服务器

想要的效果：第4步能正常获取到新的excel字节数据，然后调用第三方图片上传接口

写入的model数据接口

@Data
@EqualsAndHashCode(callSuper = true)
public class PayeeExcelWriteModel extends BaseRowModel {

    /**
     * 账户类型名称
     */
    @ExcelProperty(index = 0, value = "账户类型")
    @CellStyleFormat(fillBackgroundColor= IndexedColors.LIGHT_YELLOW)
    private String account_type_name;

    /**
     * 金额
     */
    @ExcelProperty(index = 1, value = "金额")
    private BigDecimal amount;

    /**
     * 收款方户号
     */
    @ExcelProperty(index = 2, value = "收款方户名")
    private String bank_username;

    /**
     * 收款方银行名称
     */
    @ExcelProperty(index = 3, value = "收款方银行")
    private String bank_name;

    /**
     * 收款方银行账号
     */
    @ExcelProperty(index = 4, value = "收款方账户")
    private String account_number;

    /**
     * 省
     */
    @ExcelProperty(index = 5, value = "开户行(省)")
    private String province;

    /**
     * 市
     */
    @ExcelProperty(index = 6, value = "开户行(市)")
    private String city;

    /**
     * 收款方开户行
     */
    @ExcelProperty(index = 7, value = "开户行(支行)")
    private String open_bank_name;

    /**
     * 收款方联行号
     */
    @ExcelProperty(index = 8, value = "联行号")
    private String bank_line_number;

    @ExcelProperty(index = 9, value = "备注")
    private String remark;
}

evanyangg

4

场景是：前端需要拿到这个url 进行excel的下载

evanyangg

0

excel很大所以不建议放内存放硬盘他会慢慢的往硬盘写。建议流程：保存到服务硬盘System.getProperty("java.io.tmpdir") -> 读取文件并上传 -> 删除硬盘文件。然后不用withtemp，直接重新输出一份新的就可以，根据自己的modle输出。然后你是不是想2份流？一份给前端，一份上传服务器？这个时候你必须复制流，流只能读取一次。所以建议写文件，然后读2次，当然文件不大也可以直接内存复制流，先转byte[] 再转回流，2次

zhuangjiaju

8

我试下写到硬盘，但是发现出现

Attempting to write a row[0] in the range [0,1] that is already written to disk., cause=null

这种错误，打开文件夹发现硬盘创建了一个空的 *.xlsx 文件我的场景是这样的：这是一个空的模板，前端传给我的时候是存在数据的，我服务器端进行数据读取，然后以这个空模板来进行excel 写操作，我想的方案是：给错误的行添加背景色或者新增一列 “备注” 请教一下哪一种方式更容易实现。

我这端只是负责读取excel 判断写入excel，再读取excel ，上传文件服务器然后返回 excel在文件服务器的地址给前端

evanyangg

6

哪里处理很简单了，就是保存到服务硬盘System.getProperty("java.io.tmpdir") -> 读取文件并上传 -> 删除硬盘文件 -> 把上传到服务器的地址给前端。你写到模板参照下WriteTest.java 的writeV2007WithTemplate是不是headLineMun传错了之类的问题,单独写个test类测试，试试。应该是加一列备注比较容易，你直接write即可。

zhuangjiaju

4

@fish7790714

~~我要生成的excel差不多是这样的，里面有很多重复的数据，写入的时候出现这个异常~~

系统异常，errmsg=Cannot add merged region A1:J1 to sheet because it overlaps with an existing merged region (A1:I1)., cause=null

我搜了一下issues #51 不确定是不是同样的问题？下面的报错的地方

org.apache.poi.xssf.usermodel.XSSFSheet   第400行  validate = true

    private int addMergedRegion(CellRangeAddress region, boolean validate) {
        if (region.getNumberOfCells() < 2) {
            throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells");
        }
        region.validate(SpreadsheetVersion.EXCEL2007);

        if (validate) {
            // throw IllegalStateException if the argument CellRangeAddress intersects with
            // a multi-cell array formula defined in this sheet
            validateArrayFormulas(region);

            // Throw IllegalStateException if the argument CellRangeAddress intersects with
            // a merged region already in this sheet 
            validateMergedRegions(region);
        }

        CTMergeCells ctMergeCells = worksheet.isSetMergeCells() ? worksheet.getMergeCells() : worksheet.addNewMergeCells();
        CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
        ctMergeCell.setRef(region.formatAsString());
        return ctMergeCells.sizeOfMergeCellArray();
    }

evanyangg

8

你先把reamark 注释掉试试看。问题一个个慢慢来。然后异常记得贴全，堆栈全部要，不然没法看

zhuangjiaju

2

我知道问题出在哪里了，报错的地方是A1 和J1, 我把remark注释掉只有报错的地方是A1和I1，也就是说他检测了我模板里面的数据，我写入的时候是从第三行开始写的，不知道为啥还检测模板数据

evanyangg

7

数据和错误信息

2019-05-29 14:23:10.901 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，errmsg=Cannot add merged region A1:I1 to sheet because it overlaps with an existing merged region (A1:I1)., cause=null
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，XSSFSheet.java,org.apache.poi.xssf.usermodel.XSSFSheet, validateMergedRegions(480)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，XSSFSheet.java,org.apache.poi.xssf.usermodel.XSSFSheet, addMergedRegion(413)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，XSSFSheet.java,org.apache.poi.xssf.usermodel.XSSFSheet, addMergedRegion(367)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，SXSSFSheet.java,org.apache.poi.xssf.streaming.SXSSFSheet, addMergedRegion(394)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，WriteContext.java,com.alibaba.excel.context.WriteContext, addMergedRegionToCurrentSheet(183)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，WriteContext.java,com.alibaba.excel.context.WriteContext, initTableHead(169)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，WriteContext.java,com.alibaba.excel.context.WriteContext, initCurrentSheet(137)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，WriteContext.java,com.alibaba.excel.context.WriteContext, currentSheet(125)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ExcelBuilderImpl.java,com.alibaba.excel.write.ExcelBuilderImpl, addContent(71)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ExcelWriter.java,com.alibaba.excel.ExcelWriter, write(91)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，PayOrderController.java,com.njwd.bankenterprise.web.controller.PayOrderController, importPayeeList(744)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，NativeMethodAccessorImpl.java,sun.reflect.NativeMethodAccessorImpl, invoke0(-2)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，NativeMethodAccessorImpl.java,sun.reflect.NativeMethodAccessorImpl, invoke(62)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，DelegatingMethodAccessorImpl.java,sun.reflect.DelegatingMethodAccessorImpl, invoke(43)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，Method.java,java.lang.reflect.Method, invoke(498)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，InvocableHandlerMethod.java,org.springframework.web.method.support.InvocableHandlerMethod, doInvoke(215)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，InvocableHandlerMethod.java,org.springframework.web.method.support.InvocableHandlerMethod, invokeForRequest(142)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ServletInvocableHandlerMethod.java,org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod, invokeAndHandle(102)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，RequestMappingHandlerAdapter.java,org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter, invokeHandlerMethod(895)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，RequestMappingHandlerAdapter.java,org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter, handleInternal(800)
2019-05-29 14:23:10.902 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，AbstractHandlerMethodAdapter.java,org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter, handle(87)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，DispatcherServlet.java,org.springframework.web.servlet.DispatcherServlet, doDispatch(1038)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，DispatcherServlet.java,org.springframework.web.servlet.DispatcherServlet, doService(942)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，FrameworkServlet.java,org.springframework.web.servlet.FrameworkServlet, processRequest(998)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，FrameworkServlet.java,org.springframework.web.servlet.FrameworkServlet, doPost(901)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，HttpServlet.java,javax.servlet.http.HttpServlet, service(660)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，FrameworkServlet.java,org.springframework.web.servlet.FrameworkServlet, service(875)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，HttpServlet.java,javax.servlet.http.HttpServlet, service(741)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, internalDoFilter(231)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, doFilter(166)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，WsFilter.java,org.apache.tomcat.websocket.server.WsFilter, doFilter(53)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, internalDoFilter(193)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, doFilter(166)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，CORSConfigFilter.java,com.njwd.bankenterprise.web.config.CORSConfigFilter, doFilter(39)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, internalDoFilter(193)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, doFilter(166)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，RequestContextFilter.java,org.springframework.web.filter.RequestContextFilter, doFilterInternal(99)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，OncePerRequestFilter.java,org.springframework.web.filter.OncePerRequestFilter, doFilter(107)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, internalDoFilter(193)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, doFilter(166)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，FormContentFilter.java,org.springframework.web.filter.FormContentFilter, doFilterInternal(92)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，OncePerRequestFilter.java,org.springframework.web.filter.OncePerRequestFilter, doFilter(107)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, internalDoFilter(193)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, doFilter(166)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，HiddenHttpMethodFilter.java,org.springframework.web.filter.HiddenHttpMethodFilter, doFilterInternal(93)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，OncePerRequestFilter.java,org.springframework.web.filter.OncePerRequestFilter, doFilter(107)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, internalDoFilter(193)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, doFilter(166)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，CharacterEncodingFilter.java,org.springframework.web.filter.CharacterEncodingFilter, doFilterInternal(200)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，OncePerRequestFilter.java,org.springframework.web.filter.OncePerRequestFilter, doFilter(107)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, internalDoFilter(193)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ApplicationFilterChain.java,org.apache.catalina.core.ApplicationFilterChain, doFilter(166)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，StandardWrapperValve.java,org.apache.catalina.core.StandardWrapperValve, invoke(199)
2019-05-29 14:23:10.903 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，StandardContextValve.java,org.apache.catalina.core.StandardContextValve, invoke(96)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，AuthenticatorBase.java,org.apache.catalina.authenticator.AuthenticatorBase, invoke(490)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，StandardHostValve.java,org.apache.catalina.core.StandardHostValve, invoke(139)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ErrorReportValve.java,org.apache.catalina.valves.ErrorReportValve, invoke(92)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，StandardEngineValve.java,org.apache.catalina.core.StandardEngineValve, invoke(74)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，CoyoteAdapter.java,org.apache.catalina.connector.CoyoteAdapter, service(343)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，Http11Processor.java,org.apache.coyote.http11.Http11Processor, service(408)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，AbstractProcessorLight.java,org.apache.coyote.AbstractProcessorLight, process(66)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，AbstractProtocol.java,org.apache.coyote.AbstractProtocol$ConnectionHandler, process(770)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，NioEndpoint.java,org.apache.tomcat.util.net.NioEndpoint$SocketProcessor, doRun(1415)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，SocketProcessorBase.java,org.apache.tomcat.util.net.SocketProcessorBase, run(49)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ThreadPoolExecutor.java,java.util.concurrent.ThreadPoolExecutor, runWorker(1149)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，ThreadPoolExecutor.java,java.util.concurrent.ThreadPoolExecutor$Worker, run(624)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，TaskThread.java,org.apache.tomcat.util.threads.TaskThread$WrappingRunnable, run(61)
2019-05-29 14:23:10.904 ERROR 2401 --- [nio-8188-exec-2] serviceLogger                            : 系统异常，Thread.java,java.lang.Thread, run(748)

evanyangg

6

提示很明显啊已经有合并了你又合并了一次你先把writeV2007WithTemplate 跑跑看有没有问题估计是你代码写错了

zhuangjiaju

2

非常感谢，我加了一行代码就ok了。 sheet1.setStartRow(2); 有点疑惑 startRow 和 headLineMun的关系。最后向您请教一下，表头的写入是否有控制开关呢？我现在不想写入表头。

evanyangg

4

如果是写个空白的就别设置如头，然后sheet1.setStartRow(-1) 如果是模板想不重复输出头就 getWriterWithTemp needHead =false

zhuangjiaju

6

@fish7790714 ok了，非常感谢。附一张成功图。数据ok了，只是字体和边框有点不一样，不重要了。

evanyangg

[alibaba/easyexcel]写入操作能否返回文件流，我需要将文件传到第三方服务器

回答

相关问题