java poi 单元格合并
合并单元格报错 -------> 先生成单元格, 然后再进行合并, 合并之后只保留一个单元格, 反正需要合并的单元格中数据都是一样的, 如果不一样没做测试
ERROR http-nio-9104-exec-7 com.szeport.action.wxgl.ReportAction - [Action exportCusExrateForExcel Exception message]: Cannot add merged region A2:A6 to sheet because it overlaps with an existing merged region (A1:A3). java.lang.IllegalStateException: Cannot add merged region A2:A6 to sheet because it overlaps with an existing merged region (A1:A3). at org.apache.poi.xssf.usermodel.XSSFSheet.validateMergedRegions(XSSFSheet.java:480) at org.apache.poi.xssf.usermodel.XSSFSheet.addMergedRegion(XSSFSheet.java:413) at org.apache.poi.xssf.usermodel.XSSFSheet.addMergedRegion(XSSFSheet.java:367) at com.szeport.action.wxgzhHdsjcx.WxgzhHdsjcxAction.exportCusExrateForExcel(WxgzhHdsjcxAction.java:244) at sun.reflect.GeneratedMethodAccessor160.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at ognl.OgnlRuntime.invokeMethod(OgnlRuntime.java:892) at ognl.OgnlRuntime.callAppropriateMethod(OgnlRuntime.java:1294) at ognl.ObjectMethodAccessor.callMethod(ObjectMethodAccessor.java:68) at com.opensymphony.xwork2.ognl.accessor.XWorkMethodAccessor.callMethodWithDebugInfo(XWorkMethodAccessor.java:117) at com.opensymphony.xwork2.ognl.accessor.XWorkMethodAccessor.callMethod(XWorkMethodAccessor.java:108) at ognl.OgnlRuntime.callMethod(OgnlRuntime.java:1370) at ognl.ASTMethod.getValueBody(ASTMethod.java:91) at ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212) at ognl.SimpleNode.getValue(SimpleNode.java:258) at ognl.Ognl.getValue(Ognl.java:467) at ognl.Ognl.getValue(Ognl.java:431) at com.opensymphony.xwork2.ognl.OgnlUtil$3.execute(OgnlUtil.java:352) at com.opensymphony.xwork2.ognl.OgnlUtil.compileAndExecuteMethod(OgnlUtil.java:404) at com.opensymphony.xwork2.ognl.OgnlUtil.callMethod(OgnlUtil.java:350) at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:430) at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:290) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:251) at com.pub.framework.interceptors.SessionInterceptor.intercept(SessionInterceptor.java:41) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at org.apache.struts2.interceptor.DeprecationInterceptor.intercept(DeprecationInterceptor.java:41) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:256) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:168) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:265) at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:76) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:138) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:229) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:229) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:191) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:73) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at org.apache.struts2.interceptor.DateTextFieldInterceptor.intercept(DateTextFieldInterceptor.java:125) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:91) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:253) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:145) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:171) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:140) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:193) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:189) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245) at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:54) at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:575) at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:81) at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:99) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at com.szeport.filter.LoginFilter.doFilter(LoginFilter.java:61) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:230) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:230) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748)
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
//导出文件 --> 页面 <span style="display:none;" id="fileName"></span>
private String filename;
private InputStream inputStream;
private String type;
/** * (活动数据查询 - 详情 - 报表导出) * @return */ @Action(value = "exportCusExrateForExcel", interceptorRefs = {@InterceptorRef(value = "sessionStack")}, results = {@Result(name = "success", type="stream", params= {"contentType", "${type}", "inputName", "inputStream", "bufferSize", "1024", "contentDisposition", "attachment;filename=${filename}"})} ) public String exportCusExrateForExcel() { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("活动数据详情"); CreationHelper createHelper = workbook.getCreationHelper(); TUserInfo currUser = (TUserInfo) SessionUtil.getSessionAttribute(Constants.USERINFO); try { System.out.println("=============================================" + queryBean.getHeadSeqNo()); System.out.println("=============================================" + queryBean.getModuleCode()); String moduleCode = queryBean.getModuleCode()==null ? "" : queryBean.getModuleCode(); hdsjDetailBeanList = wxgzhHdsjcxService.queryHdsjDetailList(queryBean, page); Cell cell; int tWorkSheetCell = 0; int tWorkSheetRow = 0; XSSFRow row = spreadsheet.createRow(tWorkSheetRow++); //序号 标题 培训时间 填报时间 公司名称 报名者微信号 报名者/姓名 报名者/手机 报名者/邮箱 随同人员/姓名 随同人员/手机 随同人员/邮箱 String[] titles = new String[] {"序号", "标题", "培训时间", "填报时间", "公司名称", "报名者微信号", "报名者/姓名", "报名者/手机", "报名者/邮箱", "随同人员/姓名", "随同人员/手机", "随同人员/邮箱"}; //序号 月份 发布日期 币种 英文名称 海关汇率 来源 操作 for(String title : titles) { cell = row.createCell(tWorkSheetCell++); cell.setCellValue(title); } if(null != hdsjDetailBeanList && hdsjDetailBeanList.size() > 0) { int countNum = 0; for (HdsjDetailBean bean : hdsjDetailBeanList) { ++countNum; for (int i = 0; i < bean.getChildCount(); i++) { //序号 月份 发布日期 币种 英文名称 海关汇率 来源 操作 row = spreadsheet.createRow(tWorkSheetRow++); //将第一个sheet页中的 第4列 第1行到16行进行单元格合并(行和列从0开始) // spreadsheet.addMergedRegion(new CellRangeAddress(5, 6, 0, 0)); cell = row.createCell(0);//序号 cell.setCellValue(countNum); cell = row.createCell(1);//"标题" cell.setCellValue(bean.getTitle()); cell = row.createCell(2);//"培训时间" cell.setCellValue(DateUtils.dateFormatStr(bean.getTrainingStartTime(), DateUtils.DATE_FORMAT_STR) + " ~ " + DateUtils.dateFormatStr(bean.getTrainingEndTime(), DateUtils.DATE_FORMAT_STR)); cell = row.createCell(3);//"填报时间" cell.setCellValue(DateUtils.dateFormatStr(bean.getCreateTime(), DateUtils.DATE_TIME_FORMAT_STR)); cell = row.createCell(4);//"公司名称" cell.setCellValue(bean.getEtpsName()); cell = row.createCell(5);//"报名者微信号" cell.setCellValue(bean.getOperId()); cell = row.createCell(6);//"报名者/姓名" cell.setCellValue(bean.getUserName()); cell = row.createCell(7);//"报名者/手机" cell.setCellValue(bean.getPhoneNo()); cell = row.createCell(8);//"报名者/邮箱", cell.setCellValue(bean.getEmail()); if(null != bean.getAccoPersons() && bean.getAccoPersons().size() > 0) { cell = row.createCell(9);//"随同人员/姓名", cell.setCellValue(bean.getAccoPersons().get(i).getUserName()); cell = row.createCell(10);//"随同人员/手机", cell.setCellValue(bean.getAccoPersons().get(i).getPhoneNo()); cell = row.createCell(11);//""随同人员/邮箱" cell.setCellValue(bean.getAccoPersons().get(i).getEmail()); } } } int totleCount = 0; for (int i = 0; i < hdsjDetailBeanList.size(); i++) { int childCount = hdsjDetailBeanList.get(i).getChildCount(); totleCount = totleCount + childCount; int start = totleCount -1 ; int end = totleCount; System.out.println("-----------" + start); System.out.println("-----------" + end); if(childCount > 1) { spreadsheet.addMergedRegion(new CellRangeAddress(start, end, 0, 0)); spreadsheet.addMergedRegion(new CellRangeAddress(start, end, 1, 1)); spreadsheet.addMergedRegion(new CellRangeAddress(start, end, 2, 2)); spreadsheet.addMergedRegion(new CellRangeAddress(start, end, 3, 3)); spreadsheet.addMergedRegion(new CellRangeAddress(start, end, 4, 4)); spreadsheet.addMergedRegion(new CellRangeAddress(start, end, 5, 5)); spreadsheet.addMergedRegion(new CellRangeAddress(start, end, 6, 6)); spreadsheet.addMergedRegion(new CellRangeAddress(start, end, 7, 7)); spreadsheet.addMergedRegion(new CellRangeAddress(start, end, 8, 8)); } } } filename = "ImportCusExrate.xlsx"; File xls = new File(filename); FileOutputStream out = new FileOutputStream(xls); workbook.write(out); out.close(); setType("application/excel;charset=utf-8"); inputStream = new FileInputStream(xls); } catch (Exception e) { log.error("[Action exportCusExrateForExcel Exception message]: "+e.getMessage(), e); e.printStackTrace(); this.addActionError("工单信息下载异常!!!"); return "errorException"; } return SUCCESS; }
