Dynamically update columns in an exported Excel template using a POI

Basic ideas:

1. Get the template's stream file from the attachment server

2. Use Workbook.write (outs) before you get the stream file, and change the data in the stream file.

        Else if(Pagecontext.getparameter ("Export")! =NULL) {oraclepreparedstatement oraclepreparedstatement;            Oracleresultset Oracleresultset; Oraclepreparedstatement=NULL; Oracleresultset=NULL; String Attachmentid=NULL; intCount = 0; Try{oraclepreparedstatement=(oraclepreparedstatement) editimportdataam.getoadbtransaction (). Createpreparedstatement ("Select COUNT (1) \ n" + "from mtl_categories_b_kfv\n" + "WHERE category_i D in (select category_id from Cux_pon_categorys WHERE auction_header_id = ' "+auctionheaderid+" ') ",                                                                                               1); Oracleresultset=(Oracleresultset) oraclepreparedstatement.executequery (); if(Oracleresultset.next ()) {count= Oracleresultset.getint (1); }            } Catch(SQLException e) {}if(count==0){                Throw NewOaexception ("Cux", "Cux_category_demand_not_null",NULL, Oaexception.error,NULL); }            Try{oraclepreparedstatement=(oraclepreparedstatement) editimportdataam.getoadbtransaction (). Createpreparedstatement ("Select Cat.id from cux_attachment_t cat where cat.source_type= ' PON ' and cat.source_table = ' Pon_auction_item_templete ' A nd cat.source_id= ' -99999 ' and cat.file_status = ' C ' ",                                                                                                                1); Oraclepreparedstatement.definecolumntype (1,-5); Oracleresultset=(Oracleresultset) oraclepreparedstatement.executequery (); if(Oracleresultset.next ()) {Attachmentid=                             (NewStringBuilder ()). Append (""). Append (Oracleresultset.getlong (1) . toString (); } Else {                    Throw NewOaexception ("No Pricing line import template exists"); }            } Catch(Exception exception2) {Throwoaexception.wrapperexception (Exception2);            } logutil.of (Attachmentid, PageContext). Print (PageContext); Downloadfilefromserver (PageContext, Webbean, Attachmentid);//Exportmodeexcel (PageContext, Webbean, Attachmentid);            return; }                                     Public voidDownloadfilefromserver (Oapagecontext pagecontext, Oawebbean Webbean, String Attachmentid) {oaapplicationmodule am=Pagecontext.getapplicationmodule (Webbean); Attachementvoimpl Attachementvo=(Attachementvoimpl) am.findviewobject ("Attachementvo"); if(Attachementvo = =NULL) {Attachementvo=(Attachementvoimpl) am.createviewobject ("Attachementvo",                                                            "Cux.oracle.apps.cux.attachement.server.AttachementVO"); } attachementvo.setwhereclause (NULL); Attachementvo.setwhereclauseparams (NULL); String SQL= "1=1 and id=" + Attachmentid + "'";        Attachementvo.setwhereclause (SQL); Attachementvo.setmaxfetchsize (-1);        Attachementvo.executequery (); Attachementvorowimpl Attachementinfo=(Attachementvorowimpl) Attachementvo.first (); HttpServletResponse Response=(HttpServletResponse) Pagecontext.getrenderingcontext (). Getservletresponse (); String FileType=Attachementinfo.getfilemimetype ();        Response.setcontenttype (FileType); String FileName=Attachementinfo.getfilename (); Try{Response.AddHeader ("Content-disposition",                                "Attachment;filename=" +NewString (Filename.getbytes ("GBK"),                                                                    "Iso-8859-1")); Response.setheader ("Content-transfer-encoding", "binary"); //Response.setheader ("Cache-control", "Must-revalidate, Post-check=0, pre-check=0");Response.setheader ("Cache-control", "No-store"); Response.setheader ("Pragma", "public"); } Catch(unsupportedencodingexception e) {logutil.of ("Downloadfilefromserver from FTP server error-filename!",                         This); } InputStream in=NULL; Servletoutputstream Outs=NULL; Ftputil FTP= This. GETFTP (PageContext); Try{Outs=Response.getoutputstream (); //get attachment Stream filein =Ftp.getfile (Attachementinfo.getfilepath (), Attachementinfo.getfilename ()); //int ch;//While ((ch = in.read ())! =-1) {//outs.write (CH);//            }                        //Workbook Workbook = new Xssfworkbook (in);//Org.apache.poi.ss.usermodel.Sheet Sheet = workbook.getsheetat (1);//Org.apache.poi.ss.usermodel.Row Row = null;//Org.apache.poi.ss.usermodel.Cell Cell = null;////String cellvalue = "132700002800";////for (int i = 0; i < 5; i++) {//System.out.println ("i =" + i);//row = Sheet.createrow (i);//cell = Row.createcell (0);//Cell.setcellvalue (cellvalue);//            }//workbook.write (outs);Exportexcel (pagecontext,in, outs); } Catch(IOException e) {logutil.of ("Downloadfilefromserver from FTP server error-!" +e.getmessage (), This); } finally {            Try{Outs.flush ();                Outs.close (); if(In! =NULL) {in.close ();            } ftp.closeclient (); } Catch(Exception e) {e.printstacktrace (); }        }    }                /*** Get FTP configuration information and initialize the connection *@paramPageContext *@return     */    protectedftputil getftp (Oapagecontext pagecontext) {String host= Pagecontext.getprofile ("Cux_srm_ftp_host"); String Port= Pagecontext.getprofile ("Cux_srm_ftp_port"); String User= Pagecontext.getprofile ("Cux_srm_ftp_user"); String Password= Pagecontext.getprofile ("Cux_srm_ftp_password"); return NewFtputil (host, port, user, password); }                 Public voidexportexcel (oapagecontext pagecontext,inputstream ins,outputstream outs) {Try{oaapplicationmodule am=Pagecontext.getrootapplicationmodule (); Oaapplicationmodule Editimportdataam=NULL; Editimportdataam=(Oaapplicationmodule) am.findapplicationmodule ("Editimportdataam"); Oaviewobject Auctionheadersallvo=(Oaviewobject) am.findviewobject ("Auctionheadersallvo"); Auctionheadersallvorowimpl Auctionheadersallrow=(Auctionheadersallvorowimpl) Auctionheadersallvo.first (); Number Auctionheaderid=Auctionheadersallrow.getauctionheaderid (); Oaviewobject Itemsegvo=(Oaviewobject) editimportdataam.findviewobject ("CuxAucItemSegmentsVO1"); Itemsegvo.setwhereclause (NULL); Itemsegvo.setwhereclauseparams (NULL); Itemsegvo.setwhereclauseparam (0, Auctionheaderid);            Itemsegvo.executequery (); Rowsetiterator Itemsegiter=Itemsegvo.findrowsetiterator ("Itemsegiter") = =NULL?Itemsegvo.createrowsetiterator ("Itemsegiter"): Itemsegvo.findrowsetiterator ("Itemsegiter"); Row Itemsegrow=NULL; intItemsegcount =Itemsegvo.getrowcount (); Workbook Workbook=Newxssfworkbook (INS); Org.apache.poi.ss.usermodel.Sheet Sheet= Workbook.getsheetat (1); Org.apache.poi.ss.usermodel.Row Row=NULL; Org.apache.poi.ss.usermodel.Cell Cell=NULL; if(Itemsegcount > 0) {Itemsegiter.setrangestart (0);                Itemsegiter.setrangesize (Itemsegcount);  for(inti = 0; i < Itemsegcount; i++) {Itemsegrow=Itemsegiter.getrowatrangeindex (i); String ItemDescription=(String) Itemsegrow.getattribute ("Description"); String ItemNumber=(String) Itemsegrow.getattribute ("ItemNumber"); Row=Sheet.createrow (i); Cell= Row.createcell (0);                    Cell.setcellvalue (ItemDescription); Cell= Row.createcell (1);                Cell.setcellvalue (ItemNumber);                        }} itemsegiter.closerowsetiterator ();        Workbook.write (outs); }Catch(FileNotFoundException e) {e.printstacktrace (); } Catch(IOException e) {e.printstacktrace (); } finally {            Try{Outs.flush ();                Outs.close (); if(INS! =NULL) {ins.close (); }            } Catch(Exception e) {e.printstacktrace (); }        }    }

Dynamically update columns in an exported Excel template using a POI