Excel工具类

package com.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List; 
import java.util.Map;

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

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * Excel工具类
 * 
 * @作者 张剑
 * @版本 1.3
 * @日期 2014年8月22日
 * @时间 上午10:39:10
 * @更新 张剑--2014年8月22日--1.1--添加读取Excel为String[][]
 * @更新 张剑--2014年8月22日--1.2--关闭输入输出流 抛出异常
 * @更新 张剑--2014年8月25日--1.3--修改读取代码
 */
public class ZJ_ExcelUtils {

    /**
     * 设置下载头，防止文件名乱码
     * 
     * @param request
     * @param response
     * @param fileName
     */
    public static void setFileDownloadHeader(HttpServletRequest request, HttpServletResponse response, String fileName) {
        final String userAgent = request.getHeader("USER-AGENT");
        try {
            String finalFileName = null;
            if (StringUtils.contains(userAgent, "Firefox")) {// google,火狐浏览器
                finalFileName = new String(fileName.getBytes(), "ISO8859-1");
            } else {
                finalFileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
            }
            response.setHeader("Content-Disposition", "attachment; filename=\"" + finalFileName + "\"");// 这里设置一下让浏览器弹出下载提示框，而不是直接在浏览器中打开
        } catch (UnsupportedEncodingException e) {
        }
    }

    /**
     * *
     * 
     * @param filename
     *            保存到客户端的文件名 例：用户.xls
     * @param title
     *            标题行 例：String[]{"名称","地址"}
     * @param key
     *            从查询结果List取得的MAP的KEY顺序， 需要和title顺序匹配，
     *            例：String[]{"name","address"}
     * @param values
     *                 List<Map<String, String>> values=new ArrayList<Map<String,String>>();
     *                 Map<String, String> m=new HashMap<String, String>();
     *                 m.put("name", "zhangjian");
     *                m.put("address", "郑州");
     *                values.add(m);
     *            结果集
     * @param httpServletResponse
     * @throws IOException
     */
    public static void createExcel(String fileName, String[] title, String[] key, List<Map<String, String>> values, HttpServletRequest request, HttpServletResponse response) throws IOException {
        setFileDownloadHeader(request, response, fileName);
        HSSFWorkbook workbook = null;
        response.setContentType("application/x-download");
        response.setCharacterEncoding("UTF-8");
        workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = null;
        HSSFCell cell = null;
        row = sheet.createRow(0);
        for (int i = 0; title != null && i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(new HSSFRichTextString(title[i]));
        }
        Map<String, String> map = null;
        for (int i = 0; values != null && i < values.size(); i++) {
            row = sheet.createRow(i + 1);
            map = values.get(i);
            for (int i2 = 0; i2 < key.length; i2++) {
                cell = row.createCell(i2);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                if (map.get(key[i2]) == null) {
                    cell.setCellValue(new HSSFRichTextString(""));
                } else {
                    cell.setCellValue(new HSSFRichTextString(map.get(key[i2]).toString()));
                }
            }
        }
        ServletOutputStream servletOutputStream = null;
        try {
            servletOutputStream = response.getOutputStream();
            workbook.write(servletOutputStream);
        }finally {
            if (null != servletOutputStream) {
                servletOutputStream.flush();
                servletOutputStream.close();
            }
        }
    }

    /**
     * 导出到文件
     * 
     * @param outputStream
     * @param title
     *            String[]
     * @param key
     *            String[]
     * @param values
     *            List<Map<String, String>>
     * @throws IOException
     */
    public static void createExcel(OutputStream outputStream, String[] title, String[] key, List<Map<String, String>> values) throws IOException {
        HSSFWorkbook workbook = null;
        workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = null;
        HSSFCell cell = null;
        row = sheet.createRow(0);
        for (int i = 0; title != null && i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(new HSSFRichTextString(title[i]));
        }
        Map<String, String> map = null;
        for (int i = 0; values != null && i < values.size(); i++) {
            row = sheet.createRow(i + 1);
            map = values.get(i);
            for (int i2 = 0; i2 < key.length; i2++) {
                cell = row.createCell(i2);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                if (map.get(key[i2]) == null) {
                    cell.setCellValue(new HSSFRichTextString(""));
                } else {
                    cell.setCellValue(new HSSFRichTextString(map.get(key[i2]).toString()));
                }
            }
        }
        ServletOutputStream servletOutputStream = null;
        try {
            workbook.write(outputStream);
        } finally {
            if (null != servletOutputStream) {
                servletOutputStream.flush();
                servletOutputStream.close();
            }
        }
    }

    /**
     * 导出到文件
     * 
     * @param outputStream
     * @param values
     * @throws IOException
     */
    public static void createExcel(OutputStream outputStream, List<Map<String, String>> values) throws IOException {
        HSSFWorkbook workbook = null;
        workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = null;
        HSSFCell cell = null;
        row = sheet.createRow(0);
        Object[] title = null;
        // 添加标题
        if (values.size() > 0) {
            title = values.get(0).keySet().toArray();
            for (int i = 0; title != null && i < title.length; i++) {
                cell = row.createCell(i);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(title[i].toString());
            }
        }

        Map<String, String> map = null;
        for (int i = 0; values != null && i < values.size(); i++) {
            row = sheet.createRow(i + 1);
            map = values.get(i);
            for (int i2 = 0; i2 < title.length; i2++) {
                cell = row.createCell(i2);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                if (map.get(title[i2]) == null) {
                    cell.setCellValue("");
                } else {
                    cell.setCellValue(map.get(title[i2]).toString());
                }
            }
        }
        ServletOutputStream servletOutputStream = null;
        try {
            workbook.write(outputStream);
        } finally {
            if (null != servletOutputStream) {
                servletOutputStream.flush();
                servletOutputStream.close();
            }
        }
    }

    /**
     * 导出到文件
     * 
     * @param outputStream
     * @param title
     *            String[]
     * @param values
     *            List<String[]>
     * @throws IOException
     */
    public static void createExcel(OutputStream outputStream, String[] title, List<String[]> values) throws IOException {
        HSSFWorkbook workbook = null;
        workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = null;
        HSSFCell cell = null;
        row = sheet.createRow(0);
        for (int i = 0; title != null && i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(new HSSFRichTextString(title[i]));
        }
        String[] strArray = null;
        for (int i = 0; values != null && i < values.size(); i++) {
            row = sheet.createRow(i + 1);
            strArray = values.get(i);
            for (int i2 = 0; i2 < strArray.length; i2++) {
                cell = row.createCell(i2);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                if (strArray[i2] == null) {
                    cell.setCellValue(new HSSFRichTextString(""));
                } else {
                    cell.setCellValue(strArray[i2].toString());
                }
            }
        }
        ServletOutputStream servletOutputStream = null;
        try {
            workbook.write(outputStream);
        } finally {
            if (null != servletOutputStream) {
                servletOutputStream.flush();
                servletOutputStream.close();
            }
        }
    }

    /**
     * 读取excel（第一行必须为标题）
     * 
     * @param filePath
     * @return
     * @throws IOException
     * @throws FileNotFoundException
     */
    public static List<Map<String, String>> getMapListByExcelFilePath(String filePath) throws FileNotFoundException, IOException {
        File file = new File(filePath);
        List<Map<String, String>> list = getMapListByExcelFile(file);
        return list;
    }

    /**
     * 读取excel（第一行必须为标题）
     * 
     * @param file
     * @return
     * @throws IOException
     * @throws FileNotFoundException
     */
    public static List<Map<String, String>> getMapListByExcelFile(File file) throws FileNotFoundException, IOException {
        List<Map<String, String>> list = new LinkedList<Map<String, String>>();
        FileInputStream fileInputStream = null;
        try {
            // 创建对Excel工作簿文件的引用
            fileInputStream = new FileInputStream(file);
            HSSFWorkbook wookbook = new HSSFWorkbook(fileInputStream);
            // 在Excel文档中，第一张工作表的缺省索引是0
            HSSFSheet sheet = wookbook.getSheetAt(0);
            // 获取到Excel文件中的所有行数
            int rows = sheet.getPhysicalNumberOfRows();
            int columns = 0;
            HSSFRow titleRow = null;
            if (rows > 0) {
                titleRow = sheet.getRow(0);
                columns = titleRow.getPhysicalNumberOfCells();
            }
            // 遍历行
            for (int i = 1; i < rows; i++) {
                Map<String, String> map = new HashMap<String, String>();
                HSSFRow row = sheet.getRow(i);
                if (row != null) {

                    // 遍历列
                    for (int j = 0; j < columns; j++) {
                        // 获取到列的值
                        HSSFCell cell = row.getCell(j);
                        if (cell != null) {
                            switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                map.put(titleRow.getCell(j).getStringCellValue(), cell.getNumericCellValue() + "");
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                map.put(titleRow.getCell(j).getStringCellValue(), cell.getStringCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA:
                                map.put(titleRow.getCell(j).getStringCellValue(), cell.getNumericCellValue() + "");
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                map.put(titleRow.getCell(j).getStringCellValue(), "");
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                map.put(titleRow.getCell(j).getStringCellValue(), cell.getBooleanCellValue() + "");
                                break;
                            default:
                                map.put(titleRow.getCell(j).getStringCellValue(), null);
                                break;
                            }
                        }
                    }
                    list.add(map);
                }
            }
            return list;
        } finally {
            if (null != fileInputStream) {
                fileInputStream.close();
            }
        }
    }

    /**
     * 读取Excel转为二维数组
     * 
     * @param file
     * @return
     * @throws IOException
     * @throws FileNotFoundException
     */
    public static String[][] getStrArrayByExcelFile(File file) throws FileNotFoundException, IOException {
        String[][] strArray = null;
        FileInputStream fileInputStream = null;
        fileInputStream = new FileInputStream(file);
        try {
            // 创建对Excel工作簿文件的引用
            HSSFWorkbook wookbook = new HSSFWorkbook(fileInputStream);
            // 在Excel文档中，第一张工作表的缺省索引是0
            HSSFSheet sheet = wookbook.getSheetAt(0);
            // 获取到Excel文件中的所有行数
            int rows = sheet.getPhysicalNumberOfRows();
            int cellNum = 0;
            if (rows > 0) {
                cellNum = sheet.getRow(0).getPhysicalNumberOfCells();
                strArray = new String[rows][cellNum];
            }
            // 遍历行
            for (int i = 0; i < rows; i++) {
                HSSFRow row = sheet.getRow(i);
                if (row != null) {
                    // 遍历列
                    for (int j = 0; j < cellNum; j++) {
                        // 获取到列的值
                        HSSFCell cell = row.getCell(j);
                        if (cell != null) {
                            switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                strArray[i][j] = cell.getNumericCellValue() + "";
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                strArray[i][j] = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA:
                                strArray[i][j] = cell.getNumericCellValue() + "";
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                strArray[i][j] = "";
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                strArray[i][j] = cell.getBooleanCellValue() + "";
                                break;
                            default:
                                strArray[i][j] = null;
                                break;
                            }
                        }
                    }
                }
            }
            return strArray;
        } finally {
            if (null != fileInputStream) {
                fileInputStream.close();
            }
        }
    }

    /**
     * 读取Excel转为二维数组
     * 
     * @param filePath
     * @return
     * @throws IOException
     * @throws FileNotFoundException
     */
    public static String[][] getStrArrayByExcelFilePath(String filePath) throws FileNotFoundException, IOException {
        File file = new File(filePath);
        String[][] strArray = getStrArrayByExcelFile(file);
        return strArray;
    }

    public static void main(String[] args) throws FileNotFoundException, IOException {
        // 演示1
        String[] title = { "姓名", "密码" };
        List<String[]> values = new ArrayList<String[]>();
        values.add(new String[] { "zhangjian", "123456" });
        values.add(new String[] { "lisi", "654321" });
        createExcel(new FileOutputStream("D:/测试1.xls"), title, values);
        // ------------------------------------------------------------------
        // 演示2
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        Map<String, String> m1 = new HashMap<String, String>();
        m1.put("账号", "zhangjian");
        m1.put("密码", "123456");
        Map<String, String> m2 = new HashMap<String, String>();
        m2.put("账号", "lisi");
        m2.put("密码", "654321");
        list.add(m1);
        list.add(m2);
        createExcel(new FileOutputStream("D:/测试2.xls"), list);
        // ------------------------------------------------------------------
        // 演示3
        String[] title3 = { "账号", "密码" };
        String[] key3 = { "code", "pwd" };
        List<Map<String, String>> values3 = new ArrayList<Map<String, String>>();
        Map<String, String> v1 = new HashMap<String, String>();
        v1.put("code", "zhangjian");
        v1.put("pwd", "123456");
        Map<String, String> v2 = new HashMap<String, String>();
        v2.put("code", "lisi");
        v2.put("pwd", "654321");
        values3.add(v1);
        values3.add(v2);
        createExcel(new FileOutputStream("D:/测试3.xls"), title3, key3, values3);

        // 读取测试1
        List<Map<String, String>> list4 = getMapListByExcelFilePath("D:/1.xls");
        System.out.println(list4.get(0));

        // 读取测试2
        String[][] strArray = getStrArrayByExcelFilePath("D:/1.xls");
        for (String[] strings : strArray) {
            for (String string : strings) {
                System.out.print(String.format("%5s", string) + "\t");
            }
            System.out.println("");
        }
    }
}