java导出Excel格式文件

[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;
    }
}

//源代码片段来自云代码http://yuncode.net