java生成详细的excel文件

 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="自定义" 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 = 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 value = "";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 = 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 = 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 = 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 = 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 = propertyContent[i].split(":");243       if("integer".equals(name)&&Integer.parseInt(statusValueAndReplaceValue[0])==(Integer)value){244         value = 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 }

原标题：java生成详细的excel文件

关键词：JAVA