java poi 单元格合并
2020-12-23 15:29
标签：debuginfo alias drive filter tco standard compile core runnable 合并单元格报错 -------> 先生成单元格, 然后再进行合并, 合并之后只保留一个单元格, 反正需要合并的单元格中数据都是一样的, 如果不一样没做测试 import org.apache.poi.ss.usermodel.Cell; //导出文件 --> 页面 java poi 单元格合并 标签：debuginfo alias drive filter tco standard compile core runnable 原文地址：https://www.cnblogs.com/jkfeng/p/13212837.htmlERROR 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.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;
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 ) {
//序号 月份 发布日期 币种 英文名称 海关汇率 来源 操作
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 ) {
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;
}
下一篇：分享8个强大的***技术学习网站