java生成详细的excel文件，java生成excel,import java.

java生成详细的excel文件，java生成excel,import java.

import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.InputStream;import java.io.UnsupportedEncodingException;import java.lang.reflect.Array;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.text.SimpleDateFormat;import java.util.List;import java.util.regex.Matcher;import java.util.regex.Pattern;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import com.gzbugu.action.ActionBase;import com.gzbugu.domain.BusiObservePlan;/** * @author ylh */public class ExcelAction extends ActionBase{    private List downExcelAttrsList;    private String fileName;    /**     * 导出Excel公共方法     * 使用action模板配置文件：        <action name="自定义" class="自定义" method="自定义">            <result name="success" type="chain">                <param name="actionName">getDownloadExcel</param>                   <param name="downExcelAttrsList">${downExcelAttrsList}</param>              </result>        </action>     * 必须的参数downExcelAttrsList，必须是有setter,getter方法的属性，其包括参数顺序如下：     * @param valueList   必须，通过hql查询数据库后返回的对象List,支持关联查询,在属性前加上对象名: {"BusiObservePlan.planType,0:个人计划,1:部门月度计划",...}     * @param sheetName  必须，Excel的sheet的名字，     * @param beanPropertyNames 必须，对象中需要被输出的值,如果是状态值需要被替换的，则如此填写：   {"propertyName,0:个人计划,1:部门月度计划", ...}     * @param titleNames 必须，对应上面属性的名字，用来做Excel的表头     * @param fileName     可选，生成的excel名称，如果没有，则默认是sheetName     */    public InputStream getDownloadExcel(){        final List list = (List)downExcelAttrsList.get(0);        final String sheetName = (String)downExcelAttrsList.get(1);        final String[] beanPropertyNames = (String[])downExcelAttrsList.get(2);        final String[] titleNames = (String[])downExcelAttrsList.get(3);        if(downExcelAttrsList.size()>=5) {            fileName = (String)downExcelAttrsList.get(4);        }else{            fileName = sheetName;        }        if(!fileName.contains(".xls")){            fileName = fileName + ".xls";        }        InputStream is = null;        try {            is = this.createExcelFile(list, sheetName, beanPropertyNames, titleNames);        } catch (Exception e1) {            e1.printStackTrace();        }        try {            fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");        } catch (UnsupportedEncodingException e) {            e.printStackTrace();        }        if(null==is) System.out.print("shit...");        return is;    }    /**     * 生成Excel表     */    private InputStream createExcelFile(List valueList, String sheetName, String[] beanPropertyNames, String[] titleNames) throws Exception{         Workbook wb = new HSSFWorkbook();        Sheet sheet = wb.createSheet(sheetName);        //单元格默认宽度为20        sheet.setDefaultColumnWidth(20);        Cell cell;        //表头        Row headerRow = sheet.createRow(0);        headerRow.setHeightInPoints(18f);        for (int i = 0; i < titleNames.length; i++) {            cell = headerRow.createCell(i);            cell.setCellValue(titleNames[i]);            cell.setCellStyle(this.getHeaderCellStyle(wb));        }        //freeze the first row        sheet.createFreezePane(0, 1);        Row row;        int rownum = 1, listSize = valueList.size(), beanPropertyNamesLength = beanPropertyNames.length;        for (int i = 0; i < listSize; i++, rownum++) {            row = sheet.createRow(rownum);            Object currentObj = valueList.get(i);            for ( int j=0; j < beanPropertyNamesLength; j++ ) {                cell = row.createCell(j);                cell.setCellStyle(this.getContentCellStyle(wb));                Object value = this.getPropertyValue(currentObj, beanPropertyNames[j]);                this.getCellSetValue(cell, value);            }        }        //将输出流转化为输入流        ByteArrayOutputStream out = new ByteArrayOutputStream();        wb.write(out);        return new ByteArrayInputStream(out.toByteArray());    }       /**     * 设置单元格值     * @param cell     * @param value     */    private void getCellSetValue(Cell cell, Object value){        String type = value.getClass().toString().toLowerCase();        if(type.endsWith("integer")){            cell.setCellValue((Integer)value);        }else if(type.endsWith("double")){            cell.setCellValue((Double)value);        }else if(type.endsWith("timestamp")){            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm").format(value).toString());        }else{            String val = (String)value;            Pattern pattern = Pattern.compile("<\\w*\\s*/?>");            Matcher matcher = pattern.matcher(val);            String v = matcher.replaceAll("");            //将结束符号替换为：。            pattern = Pattern.compile("</\\w*\\s*/?>");            matcher = pattern.matcher(v);            v = matcher.replaceAll("。");            cell.setCellValue(v);        }           }    /**     * 获得bean对象中对应属性的值     * @param obj     * @param propertyName     * @return     */    private Object getPropertyValue(Object obj,String beanPropertyName){        final String[] property = beanPropertyName.split(",");         final String[] beanNameAndPropertyName = property[0].split("\\.");        final String beanName = beanNameAndPropertyName[0].toLowerCase();        final String propertyName = beanNameAndPropertyName[1];        Object value = "";        Method met = null;        //关联查询        if(obj.getClass().isArray()){            int objLength = Array.getLength(obj);            Object[] currentObjectArray = (Object[])obj;            for(int j=0;j<objLength;j++){                Object currentObject = currentObjectArray[j];                                   String currentObjectBeanName = currentObject.getClass().getSimpleName().toLowerCase();                if(currentObjectBeanName.equals(beanName)){                    try {                        met = currentObject.getClass().getMethod(this.getterMethodName(propertyName));                    } catch (SecurityException e) {                        e.printStackTrace();                    } catch (NoSuchMethodException e) {                        e.printStackTrace();                    }                    try {                        value = met.invoke(currentObject);                    } catch (IllegalArgumentException e) {                        e.printStackTrace();                    } catch (IllegalAccessException e) {                        e.printStackTrace();                    } catch (InvocationTargetException e) {                        e.printStackTrace();                    }                 }            }                         }else{            //属性的形式为：   对象.属性               if(beanNameAndPropertyName.length>1){                try {                    met = obj.getClass().getMethod(this.getterMethodName(propertyName));                } catch (SecurityException e1) {                    e1.printStackTrace();                } catch (NoSuchMethodException e1) {                    e1.printStackTrace();                }                try {                    value = met.invoke(obj);                } catch (IllegalArgumentException e) {                    e.printStackTrace();                } catch (IllegalAccessException e) {                    e.printStackTrace();                } catch (InvocationTargetException e) {                    e.printStackTrace();                }             }else{                //属性的形式为：   属性                try {                    met = obj.getClass().getMethod(this.getterMethodName(property[0]));                } catch (SecurityException e) {                    e.printStackTrace();                } catch (NoSuchMethodException e) {                    e.printStackTrace();                }                  try {                    value = met.invoke(obj);                } catch (IllegalArgumentException e) {                    e.printStackTrace();                } catch (IllegalAccessException e) {                    e.printStackTrace();                } catch (InvocationTargetException e) {                    e.printStackTrace();                }            }                         }        //状态值替换        if(property.length>1){            value = this.replaceValue(property, value);        }        return value;    }    /**     * 根据内容来替换对应的状态值     * @param propertyContent     * @param value     * @return     */    private Object replaceValue(String[] propertyContent, Object value){        int len = propertyContent.length;        String name = value.getClass().getSimpleName().toLowerCase();        for(int i=1;i<len;i++){            String[] statusValueAndReplaceValue = propertyContent[i].split(":");            if("integer".equals(name)&&Integer.parseInt(statusValueAndReplaceValue[0])==(Integer)value){                value = statusValueAndReplaceValue[1];                break;            }        }        return value;    }    /**     * 根据属性名字获得对应的bean对象的getter名字     * @param beanPropertyName  bean对象的属性名字     * @return     */    private String getterMethodName(String beanPropertyName){        String name = "get"+beanPropertyName.substring(0, 1).toUpperCase()+beanPropertyName.substring(1);        return name;    }    /**     * 表头样式     * @param wb     * @return     */    private CellStyle getHeaderCellStyle(Workbook wb){        Font headerFont = wb.createFont();        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);        CellStyle style = createBorderedStyle(wb);        style.setAlignment(CellStyle.ALIGN_CENTER);        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());        style.setFillPattern(CellStyle.SOLID_FOREGROUND);        style.setFont(headerFont);        return style;    }    /**     * 单元格边框样式     * @param wb     * @return     */    private CellStyle createBorderedStyle(Workbook wb){        CellStyle style = wb.createCellStyle();        style.setBorderRight(CellStyle.BORDER_THIN);        style.setRightBorderColor(IndexedColors.BLACK.getIndex());        style.setBorderBottom(CellStyle.BORDER_THIN);        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());        style.setBorderLeft(CellStyle.BORDER_THIN);        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());        style.setBorderTop(CellStyle.BORDER_THIN);        style.setTopBorderColor(IndexedColors.BLACK.getIndex());        return style;    }    /**     * 内容部分单元格样式     * @param wb     * @return     */    private CellStyle getContentCellStyle(Workbook wb){        CellStyle style = createBorderedStyle(wb);        style.setAlignment(CellStyle.ALIGN_CENTER);        return style;    }    public List getDownExcelAttrsList() {        return downExcelAttrsList;    }    public void setDownExcelAttrsList(List downExcelAttrsList) {        this.downExcelAttrsList = downExcelAttrsList;    }    public String getFileName() {        return fileName;    }    public void setFileName(String fileName) {        this.fileName = fileName;    }}