导出Excel工具类

import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Description: 导出工具类
 * All Rights Reserved.
 */
public class ExcelExprot {
    @SuppressWarnings("unchecked")
    public void exportExcel(HttpServletResponse response, String fileName,
        ExcelModel... excelModels) {
    // 输出流
    OutputStream os = null;
    // excel文件
    Workbook wb = null;
    // excel工作表
    Sheet st;

    try {
        os = response.getOutputStream();
        if ((fileName.trim().substring(fileName.indexOf(".")))
            .equals(".xlsx"))
        wb = new XSSFWorkbook();
        else
        wb = new HSSFWorkbook();
        for (ExcelModel excelModel : excelModels) {
        if (excelModel.getSheetName() != null
            && excelModel.getSheetName().trim().length() != 0) {
            st = wb.createSheet(excelModel.getSheetName());
        } else {
            st = wb.createSheet();
        }
        Font font = wb.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // 设置文字蓝色 且剧中
        CellStyle styleBlueFontNotWrap = wb.createCellStyle();
        styleBlueFontNotWrap
            .setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        styleBlueFontNotWrap.setFont(font);
        styleBlueFontNotWrap.setAlignment(CellStyle.VERTICAL_CENTER);
        styleBlueFontNotWrap.setAlignment(CellStyle.ALIGN_CENTER);
        styleBlueFontNotWrap.setWrapText(false);

        Boolean cbFlag = excelModel.getCallBack() != null;
        Row row = null;
        Cell cell = null;
        int rowcursor = 0;
        int columncursor = 0;
        row = st.createRow(rowcursor);
        row.setHeight((short) 500);
        for (int i = 0; i < excelModel.getHeaders().length; i++) {
            st.setColumnWidth(i, 7000);
            columncursor = i;
            String value = excelModel.getHeaders()[i];
            cell = row.createCell(columncursor);
            cell.setCellValue(value);
            cell.setCellStyle(styleBlueFontNotWrap);
            if (cbFlag) {
            excelModel.getCallBack().execute(rowcursor,
                columncursor, cell, value);
            }
        }
        rowcursor++;
        List<?> datas = excelModel.getDatas();
        if (datas != null && datas.size() > 0) {
            while (true) {
            if (datas.size() + 1 == rowcursor) {
                break;
            }
            row = st.createRow(rowcursor);
            Class<?> cls = excelModel.getDataCls();
            if (cls.getName().equals("java.util.Map")) {
                Map<String, Object> map = (Map<String, Object>) datas
                    .get(rowcursor - 1);
                for (int i = 0; i < excelModel.getBodys().length; i++) {
                columncursor = i;
                Object value = map
                    .get(excelModel.getBodys()[i]);
                createCell(excelModel, cbFlag, row, rowcursor,
                    columncursor, value);
                }
            } else {
                Object obj = datas.get(rowcursor - 1);
                String[] bodys = excelModel.getBodys();
                for (int i = 0; i < bodys.length; i++) {
                columncursor = i;
                String methodName = "get"
                    + bodys[i].substring(0, 1)
                        .toUpperCase()
                    + bodys[i].substring(1);
                Method met = cls.getMethod(methodName);
                Object value = met.invoke(obj);
                createCell(excelModel, cbFlag, row, rowcursor,
                    columncursor, value);
                }
            }
            rowcursor++;
            }
        }
        }

        response.setContentType("application/msexcel;charset=UTF-8");
        // 定义输出类型
        fileName = new String(fileName.getBytes("gb2312"), "ISO-8859-1");
        response.setHeader("Content-disposition", "attachment; filename="
            + fileName);
        wb.write(os);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
        if (os != null)
            os.close();
        } catch (Exception e2) {
        e2.printStackTrace();
        }
    }
    }
    
    
    /**
     * <p>Title: exportExcelExtend  </p>
     * Description: 合并单元格 143      * @param response
     * @param fileName
     * @param excelModels
      */
     @SuppressWarnings("unchecked")
     public void exportExcelExtends(HttpServletResponse response,String fileName, String[] header1,String[] header2,
         ExcelModel... excelModels) {
     // 输出流
     OutputStream os = null;
     // excel文件
     Workbook wb = null;
     // excel工作表
     Sheet st;

     try {
         os = response.getOutputStream();
         if ((fileName.trim().substring(fileName.indexOf(".")))
             .equals(".xlsx"))
         wb = new XSSFWorkbook();
         else
         wb = new HSSFWorkbook();
         for (ExcelModel excelModel : excelModels) {
         if (excelModel.getSheetName() != null
             && excelModel.getSheetName().trim().length() != 0) {
             st = wb.createSheet(excelModel.getSheetName());
         } else {
             st = wb.createSheet();
         }
         Font font = wb.createFont();
         font.setBoldweight(Font.BOLDWEIGHT_BOLD);
         // 设置文字蓝色 且剧中
         CellStyle styleBlueFontNotWrap = wb.createCellStyle();
         styleBlueFontNotWrap
             .setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
         styleBlueFontNotWrap.setFont(font);
         styleBlueFontNotWrap.setAlignment(CellStyle.VERTICAL_CENTER);
         styleBlueFontNotWrap.setAlignment(CellStyle.ALIGN_CENTER);
         styleBlueFontNotWrap.setWrapText(false);

         Boolean cbFlag = excelModel.getCallBack() != null;
         Row row = null;
         Cell cell = null;
         int rowcursor = 0;
         int columncursor = 0;
         
         st.addMergedRegion(new CellRangeAddress(0,1,0,0)); 
         st.addMergedRegion(new CellRangeAddress(0,1,1,1)); 
         st.addMergedRegion(new CellRangeAddress(0,0,2,5)); 
         st.addMergedRegion(new CellRangeAddress(0,0,6,9)); 
         
         row = st.createRow(rowcursor);
         row.setHeight((short) 400);
         for (int i = 0; i < header1.length; i++) {
             st.setColumnWidth(i, 7000);
             columncursor = i;
             String value = header1[i];
             cell = row.createCell(columncursor);
             cell.setCellValue(value);
             cell.setCellStyle(styleBlueFontNotWrap);
             if (cbFlag) {
             excelModel.getCallBack().execute(rowcursor,
                 columncursor, cell, value);
             }
         }
         rowcursor++;
         row = st.createRow(rowcursor);
         row.setHeight((short) 400);
         for (int i = 1; i < header2.length; i++) {
             st.setColumnWidth(i, 7000);
             columncursor = i;
             String value = header2[i];
             cell = row.createCell(columncursor);
             cell.setCellValue(value);
             cell.setCellStyle(styleBlueFontNotWrap);
             if (cbFlag) {
             excelModel.getCallBack().execute(rowcursor,
                 columncursor, cell, value);
             }
         }
         rowcursor++;
         List<?> datas = excelModel.getDatas();
         if (datas != null && datas.size() > 0) {
             while (true) {
             if (datas.size() + 2 == rowcursor) {
                 break;
             }
             row = st.createRow(rowcursor);
             Class<?> cls = excelModel.getDataCls();
             if (cls.getName().equals("java.util.Map")) {
                 Map<String, Object> map = (Map<String, Object>) datas
                     .get(rowcursor - 2);
                 for (int i = 0; i < excelModel.getBodys().length; i++) {
                 columncursor = i;
                 Object value = map
                     .get(excelModel.getBodys()[i]);
                 createCell(excelModel, cbFlag, row, rowcursor,
                     columncursor, value);
                 }
             } else {
                 Object obj = datas.get(rowcursor - 2);
                 String[] bodys = excelModel.getBodys();
                 for (int i = 0; i < bodys.length; i++) {
                 columncursor = i;
                 String methodName = "get"
                     + bodys[i].substring(0, 1)
                         .toUpperCase()
                     + bodys[i].substring(1);
                 Method met = cls.getMethod(methodName);
                 Object value = met.invoke(obj);
                 createCell(excelModel, cbFlag, row, rowcursor,
                     columncursor, value);
                 }
             }
             rowcursor++;
             }
         }
         }
         response.setContentType("application/msexcel;charset=UTF-8");
         // 定义输出类型
         fileName = new String(fileName.getBytes("gb2312"), "ISO8859-1");
         response.setHeader("Content-disposition", "attachment; filename="
             + fileName);
         wb.write(os);
     } catch (Exception e) {
         e.printStackTrace();
     } finally {
         try {
         if (os != null)
             os.close();
         } catch (Exception e2) {
         e2.printStackTrace();
         }
     }
     }
    

    private void createCell(ExcelModel excelModel, Boolean cbFlag, Row row,
        int rowcursor, int columncursor, Object value) {
    if (value != null) {
        Cell cell = row.createCell(columncursor);
        try {
        if (value.getClass().getName().equals("java.util.Date")) {
            if (excelModel.getDateFormat() != null) {
            SimpleDateFormat df = new SimpleDateFormat(
                excelModel.getDateFormat());
            cell.setCellValue(df.format((Date) value));
            } else {
            cell.setCellValue((Date) value);
            }
        } else if (value.getClass().getName().equals("boolean")
            || value.getClass().getName()
                .equals("java.lang.Boolean")) {
            cell.setCellValue(Double.valueOf(value.toString()));
        } else {
            cell.setCellValue(value.toString());
        }
        } catch (Exception e) {
        e.printStackTrace();
        }
        if (cbFlag) {
        excelModel.getCallBack().execute(rowcursor, columncursor, cell,
            value);
        }
    }
    }
}

import org.apache.poi.ss.usermodel.Cell;

/**
 * Description: excel的回调工具类 
 * All Rights Reserved.
 */
public interface ExcelCallBack {
    /**
     * Description: 可根据行号 或者列号来操作单元格的数据或者样式11      * @param row
     * @param column
     * @param cell
     * @param value
     */
    public void execute(int row, int column, Cell cell, Object value);
}

import java.util.List;

/**
 * Description: excelModel
 * All Rights Reserved.
 */
public class ExcelModel {
    // sheet 页名称
    private String sheetName;
    // 头名称数组
    private String[] headers;
    // 数据顺序 实体类属性或者map的key
    private String[] bodys;
    // 数据集合
    private List<?> datas;
    // 数据类型 map 或者实体类
    private Class<?> dataCls;
    // 回调函数 可以修改对应cell的值 也可以添加样式等
    private ExcelCallBack callBack;
    // 日期格式
    private String dateFormat;

    public String getSheetName() {
    return sheetName;
    }

    public void setSheetName(String sheetName) {
    this.sheetName = sheetName;
    }

    public String[] getHeaders() {
    return headers;
    }

    public void setHeaders(String[] headers) {
    this.headers = headers;
    }

    public void setHeader(String... headers) {
    this.headers = headers;
    }

    public String[] getBodys() {
    return bodys;
    }

    public void setBodys(String[] bodys) {
    this.bodys = bodys;
    }

    public void setBody(String... bodys) {
    this.bodys = bodys;
    }

    public ExcelCallBack getCallBack() {
    return callBack;
    }

    public void setCallBack(ExcelCallBack callBack) {
    this.callBack = callBack;
    }

    public List<?> getDatas() {
    return datas;
    }

    public void setDatas(List<?> datas) {
    this.datas = datas;
    }

    public Class<?> getDataCls() {
    return dataCls;
    }

    public void setDataCls(Class<?> dataCls) {
    this.dataCls = dataCls;
    }

    public String getDateFormat() {
    return dateFormat;
    }

    public void setDateFormat(String dateFormat) {
    this.dateFormat = dateFormat;
    }
}

    private String methodName(Result result, String respResult,List<Object> list) {
        String period =new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        String excelName = null;
        ExcelExprot excelExprot = new ExcelExprot();
        // 构建参数
        ExcelModel excelModel = new ExcelModel();
        
        excelName = "";
        excelModel.setSheetName("");
        
        excelModel.setHeader("", "", "", "",""); 
        excelModel.setBody("", "", "", "","");
        
        excelModel.setDatas(list);
        excelModel.setDataCls(Object.class);
        try {
            excelModel.setCallBack(new ExcelCallBack() {
                @Override
                // 可根据行号 或者列号来操作单元格的数据或者样式
                public void execute(int row, int column, Cell cell, Object value) {
                                            Class<?> cls = value.getClass();
                    if (cls.equals(java.util.Date.class) || cls.equals(java.sql.Timestamp.class)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        cell.setCellValue(sdf.format((Date) value));
                    }else {
                        cell.setCellValue(value.toString());
                    }
                }
            });
            // 调用导出方法 response测试传入null
            excelExprot.exportExcel(response, excelName  + period + ".xlsx", excelModel);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            respResult = result.error(e.getMessage());
        }
        return respResult;
    }