First, Batch export:
/*** * @Title: Expexcel * @Description: Bulk export of customer information *@param @paramparams *@param @paramRequest *@param @paramResponse *@param @throwsException *@returnvoid *@throws */@RequestMapping ("Expexcel") Public voidExpexcel (Querycustomparam params,httpservletrequest request,httpservletresponse response)throwsexception{User u= GetUser (Request.getsession ());//Sessionutils.getuser (Request.getsession ());SimpleDateFormat SDF=NewSimpleDateFormat ("Yyyy-mm-dd"); SimpleDateFormat SDF2=NewSimpleDateFormat ("Yyyy-mm-dd hh:mm"); //Gets the site deployment path (via the ServletContext object), which is used to determine the download file location for downloadString path = Request.getservletcontext (). Getrealpath ("/"); List<CustomResp> list =NULL; List<ContactsResp> List2 =NULL; String FileName= "My Customers Export"; String URL= path + "download\\" + FileName + ". xls"; Params.setempids (String.valueof (U.getempid ())); Querycontactsparam PARAMS2=NewQuerycontactsparam (); Params2.setempid (Long.valueof (U.getempid ())); Params2.setcorpid (Long.valueof (U.getcorpid ())); Params2.setflag ("4"); Try{List= Customservice.selectmycustom (params);//My clientsList2 = Contactsservice.selectcontactslist (PARAMS2);//Query All contact information that my customer contains//Datalist<string[]> DataSet =NewArraylist<string[]>(); for(inti = 0; I < list.size (); i++) {string[] arr=NewString[16]; arr[0] = List.get (i). GetName () = =NULL? "": List.get (i). GetName (). toString (); arr[1] = List.get (i). Getshortname () = =NULL? "": List.get (i). Getshortname (). toString (); arr[2] = List.get (i). GetNumber () = =NULL? "": List.get (i). GetNumber (). toString (); arr[3] = List.get (i). getaddress () = =NULL? "": List.get (i). GetAddress (). toString (); arr[4] = List.get (i). GETURL () = =NULL? "": List.get (i). GETURL (). toString (); arr[5] = List.get (i). GetDescription () = =NULL? "": List.get (i). GetDescription (). toString (); arr[6] = List.get (i). Gethighseasname () = =NULL? "": List.get (i). Gethighseasname (). toString (); arr[7] = List.get (i). Gethighseasdate () = =NULL? "": Sdf2.format (List.get (i). Gethighseasdate ()); arr[8] = List.get (i). Getempname () = =NULL? "": List.get (i). Getempname (). toString (); arr[9] = List.get (i). Getorganname () = =NULL? "": List.get (i). Getorganname (). toString (); arr[10] = "";//Collection Date no dataARR[11] = List.get (i). Getexpiredate () = =NULL? "": Sdf2.format (List.get (i). Getexpiredate ()); arr[12] = "4";//Number of contactsARR[13] = List.get (i). Getcreatedate () = =NULL? "": Sdf2.format (List.get (i). Getcreatedate ()); arr[+] = List.get (i). Getcustomstatusname () = =NULL? "": List.get (i). Getcustomstatusname (). toString (); arr[[] = List.get (i). Getcustomlevelname () = =NULL? "": List.get (i). Getcustomlevelname (). toString (); Dataset.add (arr); } //DataList<string[]> Dataset2 =NewArraylist<string[]>(); for(inti = 0; I < list2.size (); i++) {string[] arr2=NewString[16]; arr2[0] = List2.get (i). GetName () = =NULL? "": List2.get (i). GetName (). toString (); arr2[1] = List2.get (i). Getsex () = =NULL? "": Getsex (List2.get (i). Getsex ()); arr2[2] = List2.get (i). Getbirthday () = =NULL? "": Sdf.format (List2.get (i). Getbirthday ());ARR2[3] = ""; arr2[4] = List2.get (i). Getorganname () = =NULL? "": List2.get (i). Getorganname (). toString (); arr2[5] = List2.get (i). getPosition () = =NULL? "": List2.get (i). GetPosition (). toString (); arr2[6] = List2.get (i). Getcompany () = =NULL? "": List2.get (i). Getcompany (). toString (); arr2[7] = List2.get (i). getaddress () = =NULL? "": List2.get (i). GetAddress (). toString (); arr2[8] = List2.get (i). GETURL () = =NULL? "": List2.get (i). GETURL (). toString (); arr2[9] = List2.get (i). Getinterest () = =NULL? "": List2.get (i). Getinterest (). toString (); arr2[= List2.get (i). Getremark () = =NULL? "": List2.get (i). Getremark (). toString (); arr2[One] = List2.get (i). Getcreatedate () = =NULL? "": Sdf2.format (List2.get (i). Getcreatedate ()); arr2[= List2.get (i). Getcustomname () = =NULL? "": List2.get (i). Getcustomname (); arr2[= List2.get (i). Getempname () = =NULL? "": List2.get (i). Getempname (); arr2[+] = List2.get (i). Getcontactsrolename () = =NULL? "": List2.get (i). Getcontactsrolename (); arr2[[] = List2.get (i). Getcontactsrelationname () = =NULL? "": List2.get (i). Getcontactsrelationname (); Dataset2.add (ARR2); } //Table HeaderString[] handers1 = {"Customer full name", "Customer abbreviation", "Customer number", "Address", "url", "Introduction", "High seas Name", "Join high Seas Time", "follow-up", "follow-up department", "Pick Date", "Expiration Date", "Number of contacts", " Created date, customer status, customer rating }; String[] Handers2= {"Name", "Gender", "Birthday", "mobile", "department", "title", "Company", "Company Address", "company website", "interests", "notes", "Created Date", "client abbreviation", "Attribution", "Role relationship", "intimacy" }; //ObjectExcelexp e1 =NewExcelexp ("My Customers", Handers1, DataSet); EXCELEXP E2=NewExcelexp ("Customer Contact", Handers2, Dataset2); List<ExcelExp> MySheet =NewArraylist<excelexp>(); Mysheet.add (E1); Mysheet.add (E2); Excelexportutil.exportmanysheetexcel (URL, mysheet); //Generate sheetExcelexportutil.getexcel (URL, fileName, response);//Download Sheet } Catch(IOException e) {e.printstacktrace (); } }
Excel object:
Public classExcelexp {PrivateString FileName;//name of the sheet PrivateString[] handers;//the title in sheet Privatelist<string[]> DataSet;//data sets in the sheet PublicExcelexp (String fileName, string[] handers, list<string[]>dataset) { This. FileName =FileName; This. handers =handers; This. DataSet =DataSet; } //getter, setter method}
To generate Excel:
/*** * @Title: Exportmanysheetexcel * @Description: can generate single, multiple sheet *@param @paramfile export Path *@param @paramMysheets *@returnvoid *@throws */ Public Static voidExportmanysheetexcel (String file, list<excelexp>mysheets) {Hssfworkbook WB=NewHssfworkbook ();//Create a workbooklist<excelexp> sheets =mysheets; //table Header StyleHssfcellstyle style =Wb.createcellstyle (); Style.setalignment (Hssfcellstyle.align_center); //Create a center format//font StyleHssffont FontStyle =Wb.createfont (); Fontstyle.setfontname ("Microsoft Jas Black"); Fontstyle.setfontheightinpoints (( Short) 12); Fontstyle.setboldweight (Hssffont.boldweight_bold); Style.setfont (FontStyle); for(Excelexp excel:sheets) {//Create a new sheetHssfsheet sheet = wb.createsheet (Excel.getfilename ());//Gets the sheet namestring[] Handers= Excel.gethanders ();//gets the title name of the sheetHssfrow Rowfirst = sheet.createrow (0);//First Act title of first sheet//Write a title for(inti=0;i){ //get each cell of the first rowHssfcell cell =Rowfirst.createcell (i); //write data into cellsCell.setcellvalue (Handers[i]); Cell.setcellstyle (style); //Add StyleSheet.setcolumnwidth (i, 4000);//set the column width for each column } //Write data setslist<string[]> DataSet =Excel.getdataset (); for(intI=0;i<dataset.size (); i++) {string[] data= Dataset.get (i);//gets the object//To create a data rowHssfrow row = Sheet.createrow (i+1); for(intj=0;j<data.length;j++){ //set the value of the corresponding cellRow.createcell (j). Setcellvalue (Data[j]); } } } //Write a file Try{FileOutputStream out=NewFileOutputStream (Newfile); Out.flush (); Wb.write (out); Out.close (); } Catch(FileNotFoundException e) {e.printstacktrace (); } Catch(IOException e) {e.printstacktrace (); } }
Java Export Excel (multi sheet supported)