POI 导出excel方法 ExcelExportUtil

package com.topsec.tsm.util;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.*;
import java.util.*;

/**
 * Created by Chuxs on 2016/5/14.
 */
public class ExcelExportUtil {

    private static Logger _log = Logger
            .getLogger(ExcelExportUtil.class);

    /**
     * ��ȡfileName�е����м�¼���ļ���ʽΪ����һ�����������ݿ��ά��Ľṹ��ÿ��sheet�ĵ�һ��Ϊ�ֶ�����������Ϊ��������
     *   ���صļ�¼��map��ʽ��֯�����е����ݶ����ַ��ܱ�ʾ
     * @param fileName ��ȡ�ļ���
     * @return ��¼�б�
     * @throws
     */
    public static List readExcel(String fileName) throws Exception {
        FileInputStream in = null;

        try {
            in = new FileInputStream(fileName);
            return readExcel(in);
        } catch (Exception e) {
            throw new Exception("readExcel error",e);
        }finally{
            if(in!=null){
                try {
                    in.close();
                } catch (IOException e) {
                    throw new Exception("readExcel error",e);
                }
            }
        }
    }

    /**
     * ��ȡxls��ʽ�ļ����е����м�¼���ļ���ʽΪ����һ�����������ݿ��ά��Ľṹ��ÿ��sheet�ĵ�һ��Ϊ�ֶ�����������Ϊ��������
     *   ���صļ�¼��map��ʽ��֯�����е����ݶ����ַ��ܱ�ʾ
     * @param in �ļ�������������ʽ�������xls������?
     * @return ��¼�б�
     * @throws Exception
     */
    public static List readExcel(InputStream in) throws Exception {
        POIFSFileSystem fs = new POIFSFileSystem(in);
        HSSFWorkbook wb = new HSSFWorkbook(fs);

        //��ȡ����ֶ����б�?
        List headList = readTableHead(wb);

        //��ȡ��¼��Ϣ
        List records = new ArrayList();
        int sheetSize = wb.getNumberOfSheets();
        for (int i = 0; i < sheetSize; i++) {
            //��ȡÿһ��sheet������
            records.addAll(readRecords(wb.getSheetAt(i), headList.size()));
        }

        return convertToMaps(headList,records);
    }

    /**
     * @param sheet
     * @param colSize TODO
     * @return
     */
    private static List readRecords(HSSFSheet sheet, int colSize) {
        List records = new ArrayList();
        Iterator rowIter = sheet.rowIterator();
        //ȥ������һ��sheet������е�һ�б���?
        if (rowIter.hasNext() == true) {
            rowIter.next();
        }
        while (rowIter.hasNext()) {
            HSSFRow row = (HSSFRow) rowIter.next();
            records.add(getRowRecord(row, colSize));
        }
        return records;
    }

    /**
     * @param wb
     * @return
     */
    private static List readTableHead(HSSFWorkbook wb) {
        HSSFSheet sheet1 = wb.getSheetAt(0);
        HSSFRow row1 = sheet1.getRow(0);
        List head = getRowRecord(row1, row1.getLastCellNum()+1);
        return head;
    }

    /**
     * @param row
     * @param colSize TODO
     * @return
     */
    private static List getRowRecord(HSSFRow row, int colSize) {
        List record = new ArrayList();
        for (int i = 0; i < colSize; i++) {
            HSSFCell cell = row.getCell((short)i);
            if(cell==null){
                record.add(null);
                continue;
            }
            int type = cell.getCellType();
            if(type==HSSFCell.CELL_TYPE_STRING){
                record.add(cell.getStringCellValue());
            }else if(type==HSSFCell.CELL_TYPE_NUMERIC){
                Double cellValue =new Double(cell.getNumericCellValue());
                if(cellValue.longValue()==cellValue.doubleValue()){
                    record.add(Long.toString(cellValue.longValue()));
                }else{
                    record.add(cellValue.toString());
                }
            }else if(type==HSSFCell.CELL_TYPE_BLANK){
                record.add(null);
            }
            else if(type==HSSFCell.CELL_TYPE_BOOLEAN){
                record.add(String.valueOf(cell.getBooleanCellValue()));
            }
        }
        return record;
    }

    /**
     * ����¼��Ϣд��excel�ļ���
     * @param fileName ����·��
     * @param colNameList �ֶ����б�
     * @param recordList ��¼�б�
     */
    public static void writeExcel(String fileName,List colNameList, List recordList)throws Exception
    {
        FileOutputStream out = null;
        try{
            out = new FileOutputStream(fileName);

            writeExcel(colNameList, recordList, out);
        }catch(Exception e)
        {
            throw new Exception("writeExcel error:",e);
        }
        finally{
            if(out!=null){
                try{
                    out.close();
                }catch(Exception e)
                {
                    throw new Exception("writeExcel error:",e);
                }
            }
        }
    }

    /**
     * ����¼��Ϣд��excel��ʽ������
     * @param
     * @param colNameList �ֶ����б�
     * @param recordList ��¼�б�
     * @throws Exception
     */
    public static void writeExcel(List colNameList, List recordList, OutputStream out) throws Exception {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet();
            //��ӱ���?
            createRow(colNameList, sheet,0);

            for (int i = 0; recordList!=null && i < recordList.size() && i < 1; i++) {
                //��Ӿ����¼
                Map record = (Map)recordList.get(i);
                List dispRecord = new ArrayList();
                for (int j = 0; j < colNameList.size(); j++) {
                    dispRecord.add(record.get(colNameList.get(j)));
                    System.out.println(record.get("===========" +colNameList.get(j))) ;
                }
                createRow(dispRecord,sheet,i+1);
            }

            wb.write(out);

        } catch(Exception ex) {
            ex.printStackTrace() ;
        }
    }
    /**
     * ����¼��Ϣд��excel��ʽ������
     * ���ߣ��Ŷ���
     * @param
     * @param colNameList �ֶ����б�
     * @param recordList ��¼�б�
     * @throws Exception
     */
    public static void outExcel(List colNameList, List recordList, OutputStream out) throws Exception {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet();
            //��ӱ���?
            createRow(colNameList, sheet,0);

            for (int i = 0; recordList!=null && i < recordList.size(); i++) {
                //��Ӿ����¼
                Map record = (Map)recordList.get(i);
                List dispRecord = new ArrayList();
                for (int j = 0; j < colNameList.size(); j++) {
                    dispRecord.add(record.get(colNameList.get(j)));
                }
                createRow(dispRecord,sheet,i+1);
            }

            wb.write(out);

        } catch(Exception ex) {
            ex.printStackTrace() ;
        }
    }

    /**
     * @param colNameList
     * @param sheet
     */
    private static void createRow(List colNameList, HSSFSheet sheet,int rowNum) {
        HSSFRow row = sheet.createRow(rowNum);
        for (int i = 0; i < colNameList.size(); i++) {
            HSSFCell cell = row.createCell((short)i);
//            cell.setEncoding(HSSFCell.ENCODING_UTF_16);

            cell.setCellValue((String)colNameList.get(i));
        }
    }

    public static List convertToMaps(List tableHead,List recordList)throws Exception
    {
        if(tableHead==null||tableHead.size()==0||recordList==null||recordList.size()==0){
            return new ArrayList();
        }

        List retList = new ArrayList();
        for (int i = 0; i < recordList.size(); i++) {
            List record = (List)recordList.get(i);
            retList.add(convertToMap(tableHead,record));
        }
        return retList;
    }

    public static Map convertToMap(List tableHead,List record)throws Exception
    {
        if(tableHead.size()!=record.size()){
            throw new Exception("column size not equal with value size!");
        }

        Map map = new HashMap();
        for (int i = 0; i < tableHead.size(); i++) {
            map.put(tableHead.get(i),record.get(i));
        }

        return map;
    }
}