java中处理Excel

package com.hm.pro.util.excel;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import javax.mail.internet.MimeUtility;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLDecoder;
import java.util.*;

/**
 * User:
 * Date:
 * Time: 15:11:35
 * To:
 */
public final class ExcelUtil {

    private static Log log = LogFactory.getLog(ExcelUtil.class);


    /**
     * Excel 数据的导出
     *
     * @param data     Collection<Map> 对应Excel显示数据
     * @param request  HttpServletRequest
     * @param response HttpServletResponse
     * @throws java.io.IOException exception
     */
    public static void exportDataToExcel(Collection<Map> data, HttpServletRequest request, HttpServletResponse response) throws IOException {
        exportDataToExcel(null, data, request, response);
    }

    /**
     * Excel 数据的导出
     *
     * @param map      map 对应Excel第一列显示
     * @param data     Collection<Map> 对应Excel显示数据
     * @param request  HttpServletRequest
     * @param response HttpServletResponse
     * @throws java.io.IOException exception
     */
    public static void exportDataToExcel(Map<String, String> map, Collection<Map> data, HttpServletRequest request, HttpServletResponse response) throws IOException {
        response.setHeader("Content-Type", "application/force-download");
        response.setHeader("Content-Type", "application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = request.getParameter("fileName");
        if (StringUtils.isNotBlank(fileName)) {
            String s = request.getHeader("User-Agent");
            if (StringUtils.isNotBlank(s) && s.indexOf("MSIE") != -1) {
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            } else {
                String uname = URLDecoder.decode(fileName, "UTF-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + MimeUtility.encodeText(uname, "UTF8", "B") + ".xls");
            }
        } else {
            response.setHeader("Content-Disposition", "attachment;filename=export.xls");
        }
        OutputStream out = response.getOutputStream();
        exportDataToExcel(map, data, out);
    }

    /**
     * Excel 数据的导出
     *
     * @param map  map 对应Excel第一列显示
     * @param data Collection<Map> 对应Excel显示数据
     * @param out  outStream
     * @throws java.io.IOException exception
     */
    public static void exportDataToExcel(Map<String, String> map, Collection<Map> data, OutputStream out) throws IOException {
        int total = data.size(); //Excel要导出数据总量 modify zhouwei 2011-5-17
        int max_persheet = 60000; //每个sheet最多导出记录数 modify zhouwei 2011-5-17
        if (map == null || map.isEmpty()) {
            log.debug(" 第一列信息为空，用数据key值作为第一列显示！ ");
            exportDataToExcel(data, out);
            return;
        }

        if (data == null || data.isEmpty()) {
            log.debug(" 数据信息不能为空！ ");
            throw new IOException(" 数据信息不能为空！ ");
        }
        Excel e = Excel.getInstance();
        int sheet_num = total / max_persheet + 1;//得到sheet总数 modify zhouwei 2011-5-17
        for(int j = 0 ; j < sheet_num ;j ++){
            e.createSheet(j); //创建sheet
            int i = 0;
            e.createRow(i++);
            int k = 0;
            log.debug(" start ---------- 设置Excel第一行内容");
            for (String s : map.keySet()) {
                e.setCellTitle(k++, map.get(s));
            }
            
            log.debug(" start ---------- 设置Excel数据内容");
            for(int m = max_persheet * j ; m < (max_persheet + (max_persheet * j)) && m < total; m++){
                Map aData = (Map)((List)data).get(m);
                e.createRow(i++);
                k = 0;
                for (String o : map.keySet()) {
                    e.setCell(k++, aData.get(o).toString());
                }
            }
        }
        try {
            e.getWorkbook().write(out);
        } catch (IOException ioe) {
            log.debug(" 写入Excel文件出错! ");
            throw new IOException(" 写入Excel文件出错! ", ioe);
        }
    }

    /**
     * Excel 数据的导出
     *
     * @param data Collection<Map<String, ?>> map中key对应Excel标题 value为显示数据
     * @param out  outStream
     * @throws java.io.IOException exception
     */
    public static void exportDataToExcel(Collection<Map> data, OutputStream out) throws IOException {
        if (data == null || data.isEmpty()) {
            log.info(" 数据信息不能为空！ ");
            throw new IOException(" 数据信息不能为空！ ");
        }

        Excel e = Excel.getInstance();
        e.createSheet(0);//创建sheet addby zhouwei 2011-5-24
        Iterator<Map> itr = data.iterator();
        int i = 0, j;
        while (itr.hasNext()) {
            Map t = itr.next();
            if (i == 0) {
                log.debug(" start ---------- 设置Excel第一行内容");
                e.createRow(i);
                int k = 0;
                for (Object o : t.keySet()) {
                    e.setCellTitle(k++, (String) o);
                }
            }
            j = 0;
            e.createRow(++i);
            log.debug(" start ---------- 设置Excel数据内容");
            for (Object o : t.keySet()) {
                e.setCell(j++, t.get(o));
            }
        }
        try {
            e.getWorkbook().write(out);
        } catch (IOException ioe) {
            log.debug(" 写入Excel文件出错! ");
            throw new IOException(" 写入Excel文件出错! ", ioe);
        }
    }

    /**
     * 导入Excel
     *
     * @param list 保存返回数据中Map的key值
     * @param in   Excel文件流
     * @return List
     * @throws Exception exception
     */
    public static List<Map> importExcelToData(List<String> list, InputStream in) throws Exception {
        POIFSFileSystem fs = new POIFSFileSystem(in);
        return importExcelToData(list, fs);
    }

    /**
     * 导入Excel
     *
     * @param list     保存返回数据中Map的key值
     * @param FileName Excel路径名称
     * @return List
     * @throws Exception exception
     */
    public static List<Map> importExcelToData(List<String> list, String FileName) throws Exception {
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(FileName));
        return importExcelToData(list, fs);
    }

    /**
     * 导入Excel
     *
     * @param list 保存返回数据中Map的key值
     * @param fs   POIFSFileSystem
     * @return List
     * @throws Exception exception
     */
    public static List<Map> importExcelToData(List<String> list, POIFSFileSystem fs) throws Exception {
        HSSFWorkbook wb;
        try {
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            log.debug(e);
            throw new Exception(" 导入数据出错！ ");
        }
        List<Map> result = new ArrayList<Map>();
        HSSFSheet sheet;
        HSSFRow row;
        HSSFCell cell;
        Map<String, Object> t;
        for (int k = 0; k < wb.getNumberOfSheets(); k++) {
            sheet = wb.getSheetAt(k);
            int rows = sheet.getPhysicalNumberOfRows();
            ArrayList<Map> temp = new ArrayList<Map>(rows);

            row = sheet.getRow(0);
            int cells = (row != null) ? row.getPhysicalNumberOfCells() : 0;
            for (int r = 1; r < rows; r++) {
                boolean rowValueIsNotBlank = false;
                row = sheet.getRow(r);
                t = new Hashtable<String, Object>();
                String key;
                for (int c = 0; c < cells; c++) {
                    cell = row.getCell((short) c);
                    key = list.get(c);
                    if (cell == null) continue;
                    switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_FORMULA:
                            t.put(key, cell.getCellFormula());
                            rowValueIsNotBlank = true;
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                t.put(key, (HSSFDateUtil.getJavaDate(cell.getNumericCellValue())));
                            } else {
                                t.put(key, String.valueOf(cell.getNumericCellValue()));
                            }
                            rowValueIsNotBlank = true;
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            t.put(key, cell.getStringCellValue());
                            rowValueIsNotBlank = true;
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:
                            t.put(key, cell.getStringCellValue());
                            break;
                        default:
                            t.put(key, cell.getStringCellValue());
                            rowValueIsNotBlank = true;
                            break;
                    }
                }
                if(rowValueIsNotBlank )temp.add(t);
            }
            result.addAll(temp);
        }
        return result;

    }


//    public static void mainss(String[] args) throws Exception {
//        List<String> list = new ArrayList<String>();
//        list.add("nature");
//        list.add("visible");
//        list.add("creater");
//        list.add("auditer");
//        list.add("createdate");
//        list.add("name");
//        list.add("id");
//        list.add("status");
//        list.add("audittime");
//        list.add("type");
//        list.add("edittempid");
//        list.add("code");
//        InputStream in = new FileInputStream(new File("D:/财务分类信息Excel.xls"));
//        List<Map> t1 = ExcelUtil.importExcelToData(list, in);
//        for (Map map : t1) {
//            log.debug(StringUtils.center("一行数据", 50, "*"));
//            for (Object o : map.keySet()) {
//                log.debug("key is " + o + " value is : " + map.get(o));
//            }
//        }
//    }

    public static void main(String[] args) throws Exception {
        List<String> list = new ArrayList<String>();
        list.add("productId");
        list.add("versionId");
        list.add("courseIds");
        list.add("categoryId");
        list.add("banPromotion");
        list.add("banDongdongPay");
        list.add("financeCategoryId");
        list.add("contentType");
        list.add("editablecurriculm");

        list.add("versionName");
        list.add("maxCanSelect");
        list.add("price");
        list.add("purchasePrice");
        list.add("onSaleTime");
        list.add("keyword");
        list.add("costType");
        list.add("usefulLifeType");
        list.add("activeDays");

        list.add("endDate");
        list.add("amount");
        list.add("lessonNum");
        list.add("formalProductId");
        list.add("formalVersionId");
        list.add("saleOnClass");
        list.add("presale");

        list.add("rechageable");
        list.add("rechargeAmountCell");
        list.add("delayDayCount");
        list.add("intro");
        list.add("studyGoal");
        list.add("adaptivePeople");
        list.add("brief");
        list.add("selfDefinition1");
        list.add("selfDefinition2");
        InputStream in = new FileInputStream(new File("D:/My Documents/产品列表.xls"));
        List<Map> t1 = ExcelUtil.importExcelToData(list, in);
        for (Map map : t1) {
            log.info(StringUtils.center("一行数据", 50, "*"));
            for (Object o : map.keySet()) {
                log.info("key is " + o + " value is : " + map.get(o));
            }
        }
    }
}