1 import java.io.ByteArrayInputStream;  2 import java.io.ByteArrayOutputStream;  3 import java.io.InputStream;  4 import java.io.UnsupportedEncodingException;  5 import java.lang.reflect.Array;  6 import java.lang.reflect.InvocationTargetException;  7 import java.lang.reflect.Method;  8 import java.text.SimpleDateFormat;  9 import java.util.List; 10 import java.util.regex.Matcher; 11 import java.util.regex.Pattern; 12  13 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 14 import org.apache.poi.ss.usermodel.Cell; 15 import org.apache.poi.ss.usermodel.CellStyle; 16 import org.apache.poi.ss.usermodel.Font; 17 import org.apache.poi.ss.usermodel.IndexedColors; 18 import org.apache.poi.ss.usermodel.Row; 19 import org.apache.poi.ss.usermodel.Sheet; 20 import org.apache.poi.ss.usermodel.Workbook; 21  22 import com.gzbugu.action.ActionBase; 23 import com.gzbugu.domain.BusiObservePlan; 24  25 /** 26  * @author ylh 27  */ 28 public class ExcelAction extends ActionBase{ 29  30     private List downExcelAttrsList; 31     private String fileName; 32  33     /** 34      * 导出Excel公共方法 35      * 使用action模板配置文件： 36         <action name="自定义" class="自定义" method="自定义"> 37             <result name="success" type="chain"> 38                 <param name="actionName">getDownloadExcel</param>    39                 <param name="downExcelAttrsList">${downExcelAttrsList}</param>   40             </result> 41         </action> 42      * 必须的参数downExcelAttrsList，必须是有setter,getter方法的属性，其包括参数顺序如下： 43      * @param valueList   必须，通过hql查询数据库后返回的对象List,支持关联查询,在属性前加上对象名: {"BusiObservePlan.planType,0:个人计划,1:部门月度计划",...} 44      * @param sheetName  必须，Excel的sheet的名字， 45      * @param beanPropertyNames 必须，对象中需要被输出的值,如果是状态值需要被替换的，则如此填写：   {"propertyName,0:个人计划,1:部门月度计划", ...} 46      * @param titleNames 必须，对应上面属性的名字，用来做Excel的表头 47      * @param fileName     可选，生成的excel名称，如果没有，则默认是sheetName 48      */ 49     public InputStream getDownloadExcel(){ 50         final List list = (List)downExcelAttrsList.get(0); 51         final String sheetName = (String)downExcelAttrsList.get(1); 52         final String[] beanPropertyNames = (String[])downExcelAttrsList.get(2); 53         final String[] titleNames = (String[])downExcelAttrsList.get(3); 54         if(downExcelAttrsList.size()>=5) { 55             fileName = (String)downExcelAttrsList.get(4); 56         }else{ 57             fileName = sheetName; 58         } 59         if(!fileName.contains(".xls")){ 60             fileName = fileName + ".xls"; 61         } 62         InputStream is = null; 63         try { 64             is = this.createExcelFile(list, sheetName, beanPropertyNames, titleNames); 65         } catch (Exception e1) { 66             e1.printStackTrace(); 67         } 68         try { 69             fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1"); 70         } catch (UnsupportedEncodingException e) { 71             e.printStackTrace(); 72         } 73         if(null==is) System.out.print("shit..."); 74         return is; 75     } 76  77     /** 78      * 生成Excel表 79      */ 80     private InputStream createExcelFile(List valueList, String sheetName, String[] beanPropertyNames, String[] titleNames) throws Exception{ 81         Workbook wb = new HSSFWorkbook(); 82         Sheet sheet = wb.createSheet(sheetName); 83         //单元格默认宽度为20 84         sheet.setDefaultColumnWidth(20); 85         Cell cell; 86  87         //表头 88         Row headerRow = sheet.createRow(0); 89         headerRow.setHeightInPoints(18f); 90         for (int i = 0; i < titleNames.length; i++) { 91             cell = headerRow.createCell(i); 92             cell.setCellValue(titleNames[i]); 93             cell.setCellStyle(this.getHeaderCellStyle(wb)); 94         } 95  96         //freeze the first row 97         sheet.createFreezePane(0, 1); 98  99         Row row;100         int rownum = 1, listSize = valueList.size(), beanPropertyNamesLength = beanPropertyNames.length;101         for (int i = 0; i < listSize; i++, rownum++) {102             row = sheet.createRow(rownum);103             Object currentObj = valueList.get(i);104             for ( int j=0; j < beanPropertyNamesLength; j++ ) {105                 cell = row.createCell(j);106                 cell.setCellStyle(this.getContentCellStyle(wb));107                 Object value = http://www.mamicode.com/this.getPropertyValue(currentObj, beanPropertyNames[j]);108                 this.getCellSetValue(cell, value);109             }110         }111 112         //将输出流转化为输入流113         ByteArrayOutputStream out = new ByteArrayOutputStream();114         wb.write(out);115         return new ByteArrayInputStream(out.toByteArray());116     }   117 118     /**119      * 设置单元格值120      * @param cell121      * @param value122      */123     private void getCellSetValue(Cell cell, Object value){124         String type = value.getClass().toString().toLowerCase();125         if(type.endsWith("integer")){126             cell.setCellValue((Integer)value);127         }else if(type.endsWith("double")){128             cell.setCellValue((Double)value);129         }else if(type.endsWith("timestamp")){130             cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm").format(value).toString());131         }else{132             String val = (String)value;133             Pattern pattern = Pattern.compile("<\w*\s*/?>");134             Matcher matcher = pattern.matcher(val);135             String v = matcher.replaceAll("");136             //将结束符号替换为：。137             pattern = Pattern.compile("</\w*\s*/?>");138             matcher = pattern.matcher(v);139             v = matcher.replaceAll("。");140             cell.setCellValue(v);141         }       142     }143 144     /**145      * 获得bean对象中对应属性的值146      * @param obj147      * @param propertyName148      * @return149      */150     private Object getPropertyValue(Object obj,String beanPropertyName){151         final String[] property = beanPropertyName.split(",");152         final String[] beanNameAndPropertyName = property[0].split("\.");153         final String beanName = beanNameAndPropertyName[0].toLowerCase();154         final String propertyName = beanNameAndPropertyName[1];155         Object valuehttp://www.mamicode.com/= "";156         Method met = null;157 158         //关联查询159         if(obj.getClass().isArray()){160             int objLength = Array.getLength(obj);161             Object[] currentObjectArray = (Object[])obj;162             for(int j=0;j<objLength;j++){163                 Object currentObject = currentObjectArray[j];                   164                 String currentObjectBeanName = currentObject.getClass().getSimpleName().toLowerCase();165                 if(currentObjectBeanName.equals(beanName)){166                     try {167                         met = currentObject.getClass().getMethod(this.getterMethodName(propertyName));168                     } catch (SecurityException e) {169                         e.printStackTrace();170                     } catch (NoSuchMethodException e) {171                         e.printStackTrace();172                     }173                     try {174                         value =http://www.mamicode.com/ met.invoke(currentObject);175                     } catch (IllegalArgumentException e) {176                         e.printStackTrace();177                     } catch (IllegalAccessException e) {178                         e.printStackTrace();179                     } catch (InvocationTargetException e) {180                         e.printStackTrace();181                     }182                 }183             }                 184         }else{185             //属性的形式为：   对象.属性   186             if(beanNameAndPropertyName.length>1){187                 try {188                     met = obj.getClass().getMethod(this.getterMethodName(propertyName));189                 } catch (SecurityException e1) {190                     e1.printStackTrace();191                 } catch (NoSuchMethodException e1) {192                     e1.printStackTrace();193                 }194                 try {195                     value =http://www.mamicode.com/ met.invoke(obj);196                 } catch (IllegalArgumentException e) {197                     e.printStackTrace();198                 } catch (IllegalAccessException e) {199                     e.printStackTrace();200                 } catch (InvocationTargetException e) {201                     e.printStackTrace();202                 }203             }else{204                 //属性的形式为：   属性205                 try {206                     met = obj.getClass().getMethod(this.getterMethodName(property[0]));207                 } catch (SecurityException e) {208                     e.printStackTrace();209                 } catch (NoSuchMethodException e) {210                     e.printStackTrace();211                 }  212                 try {213                     value =http://www.mamicode.com/ met.invoke(obj);214                 } catch (IllegalArgumentException e) {215                     e.printStackTrace();216                 } catch (IllegalAccessException e) {217                     e.printStackTrace();218                 } catch (InvocationTargetException e) {219                     e.printStackTrace();220                 }221             }                 222         }223 224         //状态值替换225         if(property.length>1){226             value = http://www.mamicode.com/this.replaceValue(property, value);227         }228 229         return value;230     }231 232     /**233      * 根据内容来替换对应的状态值234      * @param propertyContent235      * @param value236      * @return237      */238     private Object replaceValue(String[] propertyContent, Object value){239         int len = propertyContent.length;240         String name = value.getClass().getSimpleName().toLowerCase();241         for(int i=1;i<len;i++){242             String[] statusValueAndReplaceValue = http://www.mamicode.com/propertyContent[i].split(":");243             if("integer".equals(name)&&Integer.parseInt(statusValueAndReplaceValue[0])==(Integer)value){244                 value = http://www.mamicode.com/statusValueAndReplaceValue[1];245                 break;246             }247         }248         return value;249     }250 251     /**252      * 根据属性名字获得对应的bean对象的getter名字253      * @param beanPropertyName  bean对象的属性名字254      * @return255      */256     private String getterMethodName(String beanPropertyName){257         String name = "get"+beanPropertyName.substring(0, 1).toUpperCase()+beanPropertyName.substring(1);258         return name;259     }260 261     /**262      * 表头样式263      * @param wb264      * @return265      */266     private CellStyle getHeaderCellStyle(Workbook wb){267         Font headerFont = wb.createFont();268         headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);269         CellStyle style = createBorderedStyle(wb);270         style.setAlignment(CellStyle.ALIGN_CENTER);271         style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());272         style.setFillPattern(CellStyle.SOLID_FOREGROUND);273         style.setFont(headerFont);274         return style;275     }276 277     /**278      * 单元格边框样式279      * @param wb280      * @return281      */282     private CellStyle createBorderedStyle(Workbook wb){283         CellStyle style = wb.createCellStyle();284         style.setBorderRight(CellStyle.BORDER_THIN);285         style.setRightBorderColor(IndexedColors.BLACK.getIndex());286         style.setBorderBottom(CellStyle.BORDER_THIN);287         style.setBottomBorderColor(IndexedColors.BLACK.getIndex());288         style.setBorderLeft(CellStyle.BORDER_THIN);289         style.setLeftBorderColor(IndexedColors.BLACK.getIndex());290         style.setBorderTop(CellStyle.BORDER_THIN);291         style.setTopBorderColor(IndexedColors.BLACK.getIndex());292         return style;293     }294 295     /**296      * 内容部分单元格样式297      * @param wb298      * @return299      */300     private CellStyle getContentCellStyle(Workbook wb){301         CellStyle style = createBorderedStyle(wb);302         style.setAlignment(CellStyle.ALIGN_CENTER);303         return style;304     }305 306     public List getDownExcelAttrsList() {307         return downExcelAttrsList;308     }309 310     public void setDownExcelAttrsList(List downExcelAttrsList) {311         this.downExcelAttrsList = downExcelAttrsList;312     }313 314     public String getFileName() {315         return fileName;316     }317 318     public void setFileName(String fileName) {319         this.fileName = fileName;320     }321 }