hi am having this error trying to export adf table to excell am in jdeveloper 11.1.1.7.0
the error is in this line cell = worksheet.createRow(j).createCell(i);
<af:commandButton text="Export To Excell" id="cb2">
<af:fileDownloadActionListener
filename="CustomClient.xlsx"
contentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
method="#{PoiBean.generateExcel}"/>
</af:commandButton>
|
public void generateExcel(FacesContext facesContext, OutputStream outputStream) throws IOException { |
|
|
|
|
|
Workbook workbook = new XSSFWorkbook(); //or new HSSFWorkbook(); |
|
Sheet worksheet = workbook.createSheet("Fonts"); |
|
|
|
|
|
///////////////////////////////////////////////////////////////////////////////////////////////////// |
|
DCBindingContainer bindings = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry(); |
|
DCIteratorBinding dcIteratorBindings = bindings.findIteratorBinding("CustomClientView1Iterator"); |
|
// HSSFRow excelrow = null; |
|
// oracle.jbo.Row[] rows = dcIteratorBindings.getAllRowsInRange(); |
|
ViewObject yourVO= dcIteratorBindings.getViewObject(); |
|
// Get all the rows of a ViewObject |
|
RowSetIterator iter = yourVO.createRowSetIterator("CustomClient"); |
|
iter.reset(); |
|
int i = 0; |
|
Cell cell; |
|
short j = 0; |
|
while (iter.hasNext()){ |
|
oracle.jbo.Row row = iter.next(); |
|
//print header on first row in excel |
|
// excelrow = (HSSFRow)worksheet.createRow((short)i); |
|
// excelrow = (HSSFRow)worksheet.createRow((int)i); |
|
//excelrow = worksheet.createRow(i); |
|
|
|
//cell = (Cell)worksheet.createRow(i); |
|
|
|
//cell = sheet.createRow(0).createCell((short)0); |
|
|
|
cell = worksheet.createRow(0).createCell(0); |
|
|
|
|
|
for (String colName : row.getAttributeNames()) { |
|
|
|
cell = worksheet.createRow(j).createCell((short)i); |
|
cell.setCellValue(colName); |
|
|
|
|
|
//HSSFCell cellA1 = excelrow.createCell((short) j); |
|
//HSSFCell cellA1 = excelrow.createCell((int)j); |
|
//cellA1.setCellValue(colName); |
|
//print data from second row in excel |
|
////////////////////////////////////////////////////////////// |
|
|
// excelrow = worksheet.createRow((short)i); |
|
//excelrow = worksheet.createRow((int)i); |
|
//excelrow = (HSSFRow)worksheet.createRow((int)i); |
|
cell = worksheet.createRow(0).createCell(0); |
|
for (String colName : row.getAttributeNames()) { |
|
System.out.println("helloccn "+row.getAttribute(colName)); |
|
System.out.println("hello "+colName); |
|
// HSSFCell cell = excelrow.createCell((int)j); |
|
//cell = excelrow.createCell(j); |
|
cell = worksheet.createRow(j).createCell(i); |
|
i++; |
|
/// cell.setCellValue(new HSSFRichTextString(rs.getS)); |
|
if (colName.equalsIgnoreCase("CcnCode")) { |
|
|
cell.setCellValue(row.getAttribute(colName).toString()); |
|
System.out.println("colName "+colName+"row.getAttribute(colName).toString()"+row.getAttribute(colName).toString()); |
|
} |
|
//logic for cell formatting |
|
if (colName.equalsIgnoreCase("CcnName")) { |
|
cell.setCellValue(row.getAttribute(colName).toString()); |
|
|
|
} |
|
//make it double if you want and convert accordingly |
|
else if (colName.equalsIgnoreCase("CcnRegDate")){ |
|
cell.setCellValue(row.getAttribute(colName).toString()); |
|
} |
|
else if (colName.equalsIgnoreCase("CcnCancelDate")){ |
|
if(null!=row.getAttribute(colName)){ |
|
cell.setCellValue(row.getAttribute(colName).toString()); |
|
} |
|
} else if (colName.equalsIgnoreCase("CcnUndertaking")){ |
|
if(null!=row.getAttribute(colName)){ |
|
cell.setCellValue(row.getAttribute(colName).toString()); |
|
} |
|
} |
|
else if (colName.equalsIgnoreCase("CcnCode8")){ |
|
if(null!=row.getAttribute(colName)){ |
|
cell.setCellValue(row.getAttribute(colName).toString()); |
|
} |
} |
|
else |
|
|
|
cell.setCellValue(row.getAttribute(colName).toString()); |
|
|
|
j++; |
|
|
|
} |
|
|
|
worksheet.createFreezePane(0, 1, 0, 1); |
|
} |
|
//excelrow = (HSSFRow)worksheet.createRow((int)i); |
|
|
|
cell = worksheet.createRow(j).createCell(i); |
|
|
|
workbook.write(outputStream); |
|
outputStream.flush(); |
|
|
|
} |
|
|
catch (Exception e) { |
|
e.printStackTrace(); |
|
} |
|
|
|
} |
this is the error
java.lang.IllegalArgumentException: Invalid column index (16384). Allowable column range for EXCEL2007 is (0..16383) or ('A'..'XFD')
at org.apache.poi.xssf.usermodel.XSSFCell.checkBounds(XSSFCell.java:853)
at org.apache.poi.xssf.usermodel.XSSFCell.setCellNum(XSSFCell.java:706)
at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:173)
at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:145)
at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:37)
at vinay.view.PoiBean.generateExcel(PoiBean.java:164)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.sun.el.parser.AstValue.invoke(Unknown Source)
at com.sun.el.MethodExpressionImpl.invoke(Unknown Source)
at org.apache.myfaces.trinidadinternal.taglib.listener.FileDownloadActionListener.processAction(FileDownloadActionListener.java:121)
at oracle.adfinternal.view.faces.event.rich.FileDownloadActionListener.processAction(FileDownloadActionListener.java:88)
at javax.faces.event.ActionEvent.processListener(ActionEvent.java:88)
at org.apache.myfaces.trinidad.component.UIXComponentBase.broadcast(UIXComponentBase.java:748)
at org.apache.myfaces.trinidad.component.UIXCommand.broadcast(UIXCommand.java:179)
at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl.broadcastEvents(LifecycleImpl.java:1086)
at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl._executePhase(LifecycleImpl.java:434)
at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:207)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:265)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:300)
at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
at oracle.adf.model.servlet.ADFBindingFilter.doFilter(ADFBindingFilter.java:205)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
at oracle.adfinternal.view.faces.webapp.rich.RegistrationFilter.doFilter(RegistrationFilter.java:128)
at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl$FilterListChain.doFilter(TrinidadFilterImpl.java:446)
at oracle.adfinternal.view.faces.activedata.AdsFilter.doFilter(AdsFilter.java:60)
at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl$FilterListChain.doFilter(TrinidadFilterImpl.java:446)
at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl._doFilterImpl(TrinidadFilterImpl.java:271)
at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl.doFilter(TrinidadFilterImpl.java:177)
at org.apache.myfaces.trinidad.webapp.TrinidadFilter.doFilter(TrinidadFilter.java:92)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
at oracle.adf.library.webapp.LibraryFilter.doFilter(LibraryFilter.java:180)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:119)
at java.security.AccessController.doPrivileged(Native Method)
at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:324)
at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:460)
at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:103)
at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:171)
at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:163)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
at weblogic.servlet.internal.RequestEventsFilter.doFilter(RequestEventsFilter.java:27)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3715)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3681)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2277)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2183)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1454)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:178)