Poi實(shí)現(xiàn)Excel導(dǎo)出工具類封裝

Poi實(shí)現(xiàn)Excel導(dǎo)出工具類封裝工具類代碼PoiExcelExport如下：packagecom.myssm.util.poi;importjava.io.File;importjava.io.FileOutputStream;importjava.io.lOException;importjava.io.OutputStream;importjava.lang.reflect.Method;import.URLEncoder;importjava.text.DecimalFormat;importjava.util.List;importjavax.servlet.http.HttpServletResponse;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.usermodel.HSSFFont;importorg.apache.poi.hssf.usermodel.HSSFPalette;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;HSSFCellStyledataStyle=workbook.createCellStyle();titlestyle=(HSSFCellStyle)setFontAndBorder(titlestyle.,contentFontType,(short)contentFontsize);if(titleColumn.length>0){for(introwindex=1;rowIndex<=dataList.size();rowIndex++){Objectobj=dataList.get(rowlndex-l); //獲得該對象Classclsss=obj.getClass(); //獲得該對對象的class實(shí)例RowdataRow=workbook.getSheet(sheetName).createRow(rowIndex);for(intcolumnindex=0;columnIndex<titleColumn.length;columnIndex++){Stringtitle=titleColumn[columnindex].toString().trim();if(!"".equals(title)){〃字段不為空〃使首字母大寫StringUTitle=Character.tollpperCase(title.charAt(0))+title.substring(1,title.length());//使其首字母大寫；StringmethodName="gef^UTitle;//設(shè)置要執(zhí)行的方法Methodmethod=clsss.getDeclaredMethod(methodName);〃獲取返回類型StringreturnType=method.getReturnType().getName();Stringdata=method.invoke(obj)==null?HM:method.invoke(obj).toString();Cellcell=dataRow.createCell(columnlndex);if(data!=null&&!"".equals(data)){if(,,int".equals(returnType)){cell?setCellValue(Integer.parseInt(data));}elseif("long”.equals(returnType)){cell.setCellValue(Long.parseLong(data));}elseif("float"?equals(returnType)){cell.setCellValue(floatDecimalFormat.format(Float.parseFloat(data)));}elseif("double".equals(returnType)){cell?setCellValue(doubleDecimalFormat.format(Double.parseDouble(data)));}else{cell?setCellValue(data);))}else{〃字段為空檢查該列是否是公式if(colFormula!=null){StringsixBuf=colFormula[columnIndex].replace("礦，(rowlndex+1)+“”)；Cellcell=dataRow.createCell(columnindex);cell?setCellFormula(sixBuf?toString());)}))))workbook.write(out);}catch(Exceptione){e.printStackTrace();}finally{try{out.close();}catch(lOExceptione){e.printStackTrace();)))/**將16進(jìn)制的顏色代碼寫入樣式中來設(shè)置顏色@paramstyle保證style統(tǒng)一@paramcolor顏色：66FFDD@paramindex索引8-64使用時不可重復(fù)@return/publicCellstylesetColor(Cellstylestyle^Stringcolor,shortindex){if(color!=""&&color!=null){〃轉(zhuǎn)為RGB碼intr=Integer.parselnt((color.substring( 0,2)),16);//轉(zhuǎn)為16進(jìn)制intg=Integer.parselnt((color.substring(2,4)),16);intb=Integer.parselnt((color.substring(4,6)),16);〃自定義cell顏色HSSFPalettepalette=workbook.getCustomPalette();palette.setColorAtIndex((short)indexJ(byte)r,(byte)g,(byte)b);style.setFillPattern(Cellstyle.SOLID_FOREGROUND);style.setFillForegroundColor(index);)returnstyle;)/**設(shè)置字體并加外邊框@paramstyle樣式@paramstyle字體名@paramstyle樣式@paramstyle字體名@paramstyle大小*@return*/publicCellstylesetFontAndBorder(Cellstylestyle,StringfontName^shortsize){HSSFFontfont=workbook.createFont();font?setFontHeightlnPoints(size);font?setFontName(fontName);font?setBold(true);style.setFont(font);style.setBorderBottom(CellStyle.BORDER_THIN);//下邊框style.setBorderLeft(CellStyle.BORDER_THIN);//左邊框style.setBorderTop(CellStyle.BORDER_THIN);//上邊框style.setBorderRight(CellStyle.BORDER_THIN);//右邊框returnstyle;)/**刪除文件@paramfileDir@return/publicbooleandeleteExcel(){booleanflag=false;Filefile=newFile(this.fileDir);//判斷目錄或文件是否存在if(!file.exists()){//不存在返回falsereturnflag;}else{//判斷是否為文件if(file.isFile()){//為文件時調(diào)用刪除文件方法file.delete();flag=true;)}returnflag；)/**刪除文件@paramfileDir@return/publicbooleandeleteExcel(Stringpath){booleanflag=false;Filefile=newFile(path);//判斷目錄或文件是否存在if(!file.exists()){//不存在返回falsereturnflag;}else{//判斷是否為文件if(file.isFile()){//為文件時調(diào)用刪除文件方法file.delete();flag=true;))returnflag;}}</pre>測試如下：實(shí)體bean:packagecom.myssm.util.poi;publicclassMan{privateStringname;privateintsex;privateStringidCard;privatefloatsalary;publicMan(Stringname,intsex.StringidCard^floatsalary){super();=name;this.sex=sex;this.idCard=idCard;this.salary=salary;}publicMan(){super();}publicStringgetName(){returnname;}publicvoidsetName(Stringname){=name;}publicintgetSex(){returnsex;}publicvoidsetSex(intsex){this.sex=sex;)publicStringgetldCard(){returnidCard;)publicvoidsetldCard(StringidCard){this.idCard=idCard;)publicfloatgetSalary(){returnsalary;)publicvoidsetSalary(floatsalary){this.salary=salary;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Cellstyle;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.util.CellRangeAddress;publicclassPoiExcelExport{HttpServletResponseresponse;//文件名privateStringfileName;〃文件保存路徑privateStringfileDir;//sheet名privateStringsheetName;//表頭字體privateStringtitleFontType="ArialUnicodeMS”;//表頭背景色privateStringtitleBackColor="C1FBEE";〃表頭字號privateshorttitleFontSize=12;//添加自動篩選的列如A:M}</pre>測試類:packagecom.myssm.util.poi;importjava.util.ArrayList;importjava.util.List;publicclassTest{publicstaticvoidmain(String[]args){PoiExcelExportpee=newPoiExcelExport(HE:/test.xlsH"sheetl");〃數(shù)據(jù)List<Man>dataList=newArrayList();Manmanl=newMan(“張三“，20,“男二(float)10000.8);Manman2=newMan("李四:21,“男二(float)11000?8);Manman3=newMan(“王五“，22,"女二(float)1200?8);Manman4=newMan(“趙六"，23,“男“，(float)13000.8);Manman5=newMan("田七“，24」男(float)14000.8);Manman6=newMan();man6.setName("老八”)；dataList.add(manl);dataList.add(man2);dataList.add(man3);dataList.add(man4);dataList.add(man5);dataList.add(man6);〃調(diào)用StringtitleColumn[]={"name"Jsex"JidCarcT,"salary"J”};StringtitleName[]={"姓名"J性別”J身份證號,,「月薪二“年薪”};inttitleSize[]={13,13,13,13,13};〃其他設(shè)置set方法可全不調(diào)用StringcolFormula[]=newString[5];colFormula[4]="D@*12”；//設(shè)置第5列的公式pee.setColFormula(colFormula);pee.setAddress("A:D");//自動篩選pee.wirteExcel(titleColumn.,titleName^titleSize^dataList);}}</pre>privateStringaddress="http://正文字體privateStringcontentFontType="ArialUnicodeMS";〃正文字號privateshortcontentFontSize=12;//Float類型數(shù)據(jù)小數(shù)位privateStringfloatDecimal=u.00";//Double類型數(shù)據(jù)小數(shù)位privateStringdoubleDecimal=M.00";//設(shè)置列的公式privateStringcolFormula[]=null;DecimalFormatfloatDecimalFormat=newDecimalFormat(floatDecimal);DecimalFormatdoubleDecimalFormat=newDecimalFormat(doubleDecimal);privateHSSFWorkbookworkbook=null;publicPoiExcelExport(StringfileDir^StringsheetName){this.fileDir=fileDir;this.sheetName=sheetName;workbook=newHSSFWorkbook();}publicPoiExcelExport(HttpServletResponseresponse,StringfileName^StringsheetName){this.response=response;this.sheetName=sheetName;workbook=newHSSFWorkbook();)/**設(shè)置表頭字體.@paramtitleFontType/publicvoidsetTitleFontType(StringtitleFontType){this.titleFontType=titleFontType;)/**設(shè)置表頭背景色.@paramtitleBackColor十六進(jìn)制/publicvoidsetTitleBackColor(StringtitleBackColor){this.titleBackColor=titleBackColor;)/**設(shè)置表頭字體大小.@paramtitleFontSize/publicvoidsetTitleFontSize(shorttitleFontSize){this.titleFontSize=titleFontSize;}/**設(shè)置表頭自動篩選欄位，如A：AC.@paramaddress/publicvoidsetAddress(Stringaddress){this.address=address;)/**設(shè)置正文字體.@paramcontentFontType/publicvoidsetContentFontType(StringcontentFontType){this.contentFontType=contentFontType;)/**設(shè)置正文字號.@paramcontentFontSize/publicvoidsetContentFontsize(shortcontentFontSize){this.contentFontSize=contentFontSize;)/**設(shè)置float類型數(shù)據(jù)小數(shù)位默認(rèn).00@paramdoubleDecimal如".00"/publicvoidsetDoubleDecimal(StringdoubleDecimal){this.doubleDecimal=doubleDecimal;}/**設(shè)置doubel類型數(shù)據(jù)小數(shù)位默認(rèn).0。@paramfloatDecimalFormat如".00/publicvoidsetFloatDecimalFormat(DecimalFormatfloatDecimalFormat){this.floatDecimalFormat=floatDecimalFormat;)/***設(shè)置列的公式@paramcolFormula存儲i-1列的公式涉及到的行號使用@替換如A@+B@*/publicvoidsetColFormula(String口colFormula){this.colFormula=colFormula;)/**寫excel.@paramtitleColumn對應(yīng)bean的屬性名@paramtitleNameexcel要導(dǎo)出的表名@paramtitlesize列寬@paramdataList數(shù)據(jù)*/publicvoidwirteExcel(StringtitleColumn[]StringtitleName[]^inttitleSize[]?List<?>dataList){〃添加Worksheet（不添加sheet時生成的xls文件打開時會報(bào)錯）Sheetsheet=workbook.createSheet(this.sheetName);〃新建文件Outputstreamout=null;try{if(fileDir!=null){〃有文件路徑out=newFileOutputStream(fileDir);}