导出excel

package com.ssh.file.util.export;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ssh.framework.exception.ServiceException;

/**
 * 导出excel2007以上版本xlsx文件
 * 

public class ExportXExcelUtil {

    private ExportXExcelUtil() {}

    /***
     * 
     * @param List
     *            <List<CellVo>> list Excel的每一行数据，Cellvo为每个单元格的数据
     * @param titleArr
     *            Excel 第一行标题头数据
     * @param sheetName
     *            Excel sheet名称
     * @return
     * @throws ServiceException
     */
    public static XSSFWorkbook getWorkbook(List<List<CellVo>> list, String[] titleArr, String sheetName)
            throws ServiceException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(sheetName);
        XSSFRow row = null;
        XSSFCell cell = null;
        // 设置Excel标题头
        XSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
//      style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        if (titleArr != null) {
            row = sheet.createRow(0);
            for (int i = 0; i < titleArr.length; i++) {
                cell = row.createCell(i);
//              cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(titleArr[i]);
                cell.setCellStyle(style);
            }
        }
        // 创建Excel内容
        createExcelContent(list, titleArr, sheet, row, cell);
        return workbook;
    }

    /**
     * 创建Excel内容
     * 
     * @param list
     * @param titleArr
     * @param sheet
     * @param row
     * @param cell
     */
    private static void createExcelContent(List<List<CellVo>> list, String[] titleArr, XSSFSheet sheet, Row row, Cell cell) {
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            for (int k = 0; k < titleArr.length; k++) {
                for (int j = 0; j < list.get(i).size(); j++) {
                    CellVo vo = list.get(i).get(j);
                    String value = vo.getValue().substring(vo.getValue().indexOf(",") + 1);
                    String title = vo.getValue().substring(0, vo.getValue().indexOf(","));
                    if (title.equals(titleArr[k])) {
                        cell = row.createCell(k);
                        sheet.setColumnWidth(k, 3900);
//                      cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(value);
                    }
                }
            }
        }
    }

    /**
     * @param request
     * @param response
     * @param list
     *            Vo类list集合
     * @param titles
     *            标题头数组
     * @param fields
     *            字段属性数组
     * @param 
     * @param sheetName
     *            excel的sheet名
     */
    @SuppressWarnings({ "unchecked" })
    public static void export(HttpServletRequest request, HttpServletResponse response, List<?> list,
            String[] titles, String[] fields, String fileName, String sheetName) throws Exception {
        List<List<CellVo>> cellList = new ArrayList<List<CellVo>>();
        for (int i = 0; i < list.size(); i++) {
            Object view = list.get(i);
            List<CellVo> cellVoList = null;
            if (view instanceof Map) { // 对返回的MAP类型集合进行导出
                cellVoList = getCellList(fields, (Map<String, Object>) view, titles);
            } else { // 对非MAP类型的对象集合进行导出
                List<Method> methodList = getMethods(view);
                cellVoList = getCellList(methodList, fields, view, titles);
            }
            cellList.add(cellVoList);

        }
        XSSFWorkbook wb = getWorkbook(cellList, titles, sheetName);
        exportExcel(request, response, wb, fileName);
    }

    /**
     * 
     * @param methodList
     * @param fields
     * @param view
     * @param titles
     * @return
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     * @throws InvocationTargetException
     */
    private static List<CellVo> getCellList(String[] fields, Map<String, Object> map, String[] titles)
            throws IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        List<CellVo> cellVoList = new ArrayList<CellVo>();
        for (int k = 0; k < fields.length; k++) {
            String name = fields[k];
            if (map.containsKey(name)) {
                CellVo cell = new CellVo();
                Object o = map.get(name);
                String value = o != null ? o.toString() : "";
                cell.setValue(titles[k] + "," + value);
                cellVoList.add(cell);
            }
        }
        return cellVoList;
    }

    /**
     * 
     * @param methodList
     * @param fields
     * @param view
     * @param titles
     * @return
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     * @throws InvocationTargetException
     */
    private static List<CellVo> getCellList(List<Method> methodList, String[] fields, Object view,
            String[] titles) throws IllegalAccessException, IllegalArgumentException,
            InvocationTargetException {
        List<CellVo> cellVoList = new ArrayList<CellVo>();
        for (int l = 0; l < methodList.size(); l++) {
            Method m = methodList.get(l);
            String name = m.toString()
                    .substring(m.toString().lastIndexOf(".") + 1, m.toString().length() - 2);
            for (int k = 0; k < fields.length; k++) {
                if (("get" + fields[k]).equalsIgnoreCase(name)) {
                    CellVo cell = new CellVo();
                    Object o = m.invoke(view);
                    String value = o != null ? o.toString() : "";
                    cell.setValue(titles[k] + "," + value);
                    cellVoList.add(cell);
                }
            }
        }
        return cellVoList;
    }

    /**
     * 获得对象的所有public公共方法
     * 
     * @param view
     *            实体对象
     * @return
     */
    private static List<Method> getMethods(Object view) {
        Method[] methods = view.getClass().getDeclaredMethods();
        List<Method> methodList = new ArrayList<Method>();
        for (int j = 0; j < methods.length; j++) {
            if (methods[j].toString().indexOf(".get") > 0) {
                methodList.add(methods[j]); // 只添加public get方法。
            }
        }
        return methodList;
    }

    /***
     * 导出Excel文件
     * 
     * @param workbook
     *            HSSFWorkbook对象
     * @param fileName
     *            文件名称
     * @throws UnsupportedEncodingException
     */
    public static void exportExcel(HttpServletRequest request, HttpServletResponse response,
            XSSFWorkbook workbook, String fileName) throws UnsupportedEncodingException {
        if (request.getHeader("user-agent").indexOf("MSIE") != -1) { // 兼容IE
            fileName = java.net.URLEncoder.encode(fileName, "utf-8") + ".xlsx";
        } else {
            fileName = new String(fileName.getBytes("utf-8"), "iso-8859-1") + ".xlsx";
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

 

 

cellvo类

 

public class CellVo {

   private String item;
   private String value;
   
   public String getItem() {
      return item;
   }
   public void setItem(String item) {
      this.item = item;
   }
   public String getValue() {
      return value;
   }
   public void setValue(String value) {
      this.value = value;
   }
   
   public String toString() {
      return "CellVO [item=" + item + ", value=" + value + "]";
   }
}