Package class:
PackageCom.xshcar.carcloud.util;ImportJava.text.SimpleDateFormat;Importjava.util.Date;ImportOrg.apache.poi.hssf.usermodel.HSSFCellStyle;ImportOrg.apache.poi.hssf.usermodel.HSSFFont;ImportOrg.apache.poi.hssf.usermodel.HSSFWorkbook;ImportOrg.apache.poi.hssf.util.HSSFColor; Public classExcelutil {/** column header cell style*/ Publichssfcellstyle Getcolumntopstyle (Hssfworkbook workbook) {//Set FontHssffont Font =Workbook.createfont (); //Set Font sizeFont.setfontheightinpoints (( Short) 11); //Font Boldfont.setboldweight (Hssffont.boldweight_bold); //Set the font nameFont.setfontname ("Courier New"); //set style; Hssfcellstyle style =Workbook.createcellstyle (); //set the bottom border; Style.setborderbottom (Hssfcellstyle.border_thin); //sets the bottom border color; Style.setbottombordercolor (HSSFColor.BLACK.index); //set the left border; Style.setborderleft (Hssfcellstyle.border_thin); //set the left border color; Style.setleftbordercolor (HSSFColor.BLACK.index); //set the right border; style.setborderright (Hssfcellstyle.border_thin); //set the color of the right border; Style.setrightbordercolor (HSSFColor.BLACK.index); //set the top border; style.setbordertop (Hssfcellstyle.border_thin); //sets the top border color; Style.settopbordercolor (HSSFColor.BLACK.index); //fonts that are set in the style with the app; Style.setfont (font); //set up automatic line wrapping; Style.setwraptext (false); //sets the horizontal alignment of the style to center-aligned; style.setalignment (Hssfcellstyle.align_center); //sets the vertical alignment of the style to center-aligned; style.setverticalalignment (Hssfcellstyle.vertical_center); returnstyle; } /** Column data information cell style*/ Publichssfcellstyle GetStyle (Hssfworkbook workbook) {//Set FontHssffont Font =Workbook.createfont (); //Set Font size//font.setfontheightinpoints ((short) 10); //Font Bold//font.setboldweight (Hssffont.boldweight_bold); //Set the font nameFont.setfontname ("Courier New"); //set style; Hssfcellstyle style =Workbook.createcellstyle (); //set the bottom border; Style.setborderbottom (Hssfcellstyle.border_thin); //sets the bottom border color; Style.setbottombordercolor (HSSFColor.BLACK.index); //set the left border; Style.setborderleft (Hssfcellstyle.border_thin); //set the left border color; Style.setleftbordercolor (HSSFColor.BLACK.index); //set the right border; style.setborderright (Hssfcellstyle.border_thin); //set the color of the right border; Style.setrightbordercolor (HSSFColor.BLACK.index); //set the top border; style.setbordertop (Hssfcellstyle.border_thin); //sets the top border color; Style.settopbordercolor (HSSFColor.BLACK.index); //fonts that are set in the style with the app; Style.setfont (font); //set up automatic line wrapping; Style.setwraptext (false); //sets the horizontal alignment of the style to center-aligned; style.setalignment (Hssfcellstyle.align_center); //sets the vertical alignment of the style to center-aligned; style.setverticalalignment (Hssfcellstyle.vertical_center); returnstyle; } PublicString formatString (Object obj) {if(obj==NULL){ return""; } if(objinstanceofDate) {SimpleDateFormat DateFormat=NewSimpleDateFormat ("Yyyy-mm-dd hh:mm"); returnDateformat.format (obj); } returnobj.tostring (); } PublicDouble formatdouble (Object obj) {returndouble.parsedouble (obj.tostring ()); } PublicString objtostring (Object obj) {if(obj==NULL){ return""; }Else{ returnobj.tostring (); } }}
Action
//IMI Data Export Public voidShopuboxexport () {Try{String path= Servletactioncontext.getservletcontext (). Getrealpath ("") + "/savepath/dataexport.xls"; Pagenum=1; Integer IsUser=NULL; CARSHOPTBL s= (CARSHOPTBL) session.get ("Carshop"); Integer Sid=S.getsid (); Integer Count=Uboxtblservice.get4sdeviceuboxcount (ISUSER,PRIMARYNAME,SID); List shopuboxlist=Uboxtblservice.get4sdeviceubox (Sid,isuser,primaryname, Pagenum, Count); Excelutil Excelutil=NewExcelutil (); Hssfworkbook Workbook=NewHssfworkbook ();//To create a workbook objectFileOutputStream fos =NewFileOutputStream (path);//create an. xls fileHssfsheet sheet = Workbook.createsheet ();//Create a worksheetSheet.setdefaultcolumnwidth (( Short) 30);//set worksheet column widthsSheet.setdefaultrowheight (( Short) 10);//set the worksheet row height /** Sheet Style definition "Getcolumntopstyle ()/getstyle () are all custom methods-in the following-extensible" **/Hssfcellstyle Columntopstyle= Excelutil.getcolumntopstyle (workbook);//Get column Header style objectHssfcellstyle style = Excelutil.getstyle (workbook);//cell Style Object//column header Array definitionstring[] Label = {"Device Imie Code", "Allotted Time", "Use status", "Device Accessory", "owner name", "License plate number"}; //Set column headerHssfrow Row1 = Sheet.createrow (( Short) 0);//create a row at index 0 (the topmost row)Hssfcell cell1 =NULL;//Create a cell at index 0 (upper left)//define the number of columns required intColumnnum =5; //set the column header to a cell in sheet for(intn=0;n<columnnum;n++) {cell1= Row1.createcell (( Short) (n));//Create a cell with the corresponding number of column headersCell1.setcelltype (hssfcell.cell_type_string);//set the data type of a column header cellCell1.setcellvalue (Label[n]);//set the value of a column header cellCell1.setcellstyle (Columntopstyle);//Set column header cell style } for(intI=0;i<shopuboxlist.size (); i++) {Object [] objs=(object[]) shopuboxlist.get (i); String Imiecode= Excelutil.objtostring (objs[0]); String Createtime=excelutil.formatstring (objs[1]); String Status=excelutil.objtostring (objs[2]); String Peijian=excelutil.objtostring (objs[3]); String Ciname=excelutil.objtostring (objs[4]); String cplate=excelutil.objtostring (objs[5]); //Converts a property to a string array format for writing to sheetString c[] ={imiecode,createtime,status,peijian,ciname,cplate}; //Create rows (from the following i+1 note that line No. 0 is the column header, so creating a new row starts with the next line)Hssfrow row = Sheet.createrow (i+1);//Create the desired number of rows for( Shortj=0;j<columnnum;j++) {Hssfcell cell= Row.createcell (j,hssfcell.cell_type_string);//set the data type of a cellCell.setcellvalue (C[j]);//set the value of a cellCell.setcellstyle (style);//Set cell style}} workbook.write (FOS);//output the Workbook object to a file Test.xlsFos.flush ();//BufferingFos.close ();//Close the stream (develop a good habit and don't forget to turn it off) if(Workbook! =NULL){ //get current time as file nameString filename =NewSimpleDateFormat ("Yyyymmddhhmmss"). Format (NewDate ()); //The output stream of the response is used here, and if the output stream is swapped for a normal file output stream, the resulting document can be written to disk, etc.//This is the key code that pops up the download dialog boxResponse.setcontenttype ("Application/vnd.ms-excel;charset=utf-8"); Response.setheader ("Content-disposition", "attachment;filename=" +filename); Response.setcharacterencoding ("Utf-8"); } PrintWriter out=Response.getwriter (); out=Response.getwriter (); Out.print (1); }Catch(Exception e) {e.printstacktrace (); } }
POI Export Excel Table