java 使用 poi 解析excel

背景:

web应用经常需要上传文件，有时候需要解析出excel中的数据，如果excel的格式没有问题，那就可以直接解析数据入库。

工具选择:

目前jxl和poi可以解析excel，jxl很早就停止维护了，只支持excel-2003也就是xls格式的文件；

 

poi可支持xls和xlsx格式的文件，经过考察，poi的功能强大很多，所以选择这个工具解析excel。文件上传在之前的一个专题有所提及。

 

需要如下jar包，jar包见附件，也可在官网下载。

注意：

1. 不支持单元格合并的情况，默认表格格式规范，格式规范指：

从第一行第一列开始，第一行为表头，后面全都是数据，每一个列代表一个字段。没有中途断掉的行或者列，允许单元格为空。

 

2. 代码初步运用了重构中的方法抽取(extract method)和方法对象抽取(extract method object)技术，代码行数前后没有太大的变化，但是结构明显要清楚一些。

只不过初步使用，有些抽取不是特别直观，写文件的两个方法没有使用重构技术，此外在单元格类型匹配，也还有深挖的空间，希望读者根据自己的需要，自行定制。

关于重构技术和解析excel的api，请参考《重构改善既有代码的设计》以及poi的源代码

 

package com.test;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
 * Created by tm on 2016/4/11.
 *
 * @author tm
 *         使用POI解析和生成excel，暂不支持单元格合并情况，默认是标准格式的excel
 *         实测同等的数据量（3张sheet超过2w条），xlsx的速度要远慢于xls的解析速度。
 *         同等数据量：
 *         xls的解析，如果只取表头，不超过500毫秒；如果取整个数据，时间不超过2000毫秒；
 *         而xlsx的解析，如果只取表头，需要2000毫秒左右，如果取整个数据，则在3000毫秒以上。
 *         以上耗时不同系统不同硬件有不同结果，仅作为相对比较结果。
 * 这个类提供三个功能：
 *         1.获得表格的表头所有列,这个功能可以酌情去掉,因为获得表格的所有数据也就获得了所有列.
 *         2.获得表格的所有数据
 *         3.传入数据写出来
 */
public class ExcelUtil {
    public static final String SUFFIX_XLS = "xls";
    public static final String SUFFIX_XLSX = "xlsx";
    public static final String NO_FILE_NULL = "NO FILE NULL";
    public static final String NO_FILE_EMPTY = "NO FILE EMPTY";
    /**
     * 获得表格的列
     * @param fileName
     * @return
     * @throws RuntimeException
     */
    public static List<Map<String, Object>> getTableSheetCol(String fileName) throws RuntimeException{
        if(!valid(fileName))
            return null;
        else{
            if(validExcelFormat(fileName,SUFFIX_XLS)){
                //是excel2003？
                return getXlsSheetItem(fileName);
            }
            if(validExcelFormat(fileName, SUFFIX_XLSX)){
                //是excel2007以后
                return getXlsxSheetItem(fileName);
            }else{
                print("nonsupport file format");
                print("fileFormat : " + getFileNameSuffix(fileName));
                throw new RuntimeException("nonsupport file format, please check input fileName again");
            }
        }
    }
    /**
     * getXlsSheetItem ： 读取xls格式文件的所有sheet表的所有列名集合。
     *
     * @param fileName 文件名
     *                  默认表格格式规范，列名全部为字符串。
     */
    private static List<Map<String, Object>> getXlsSheetItem(String fileName) {
        TableObject tableObject = new TableObject(fileName).invoke();
        HSSFWorkbook book = tableObject.getHssfBook();
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        for (int i = 0; i < tableObject.getSheet_number(); i++) {
            Sheet sheet = book.getSheetAt(i);
            if (ifSheetNullOrEmpty(sheet)) continue;
            Row row = sheet.getRow(0);
            if (ifRowNullOrEmpty(row)) continue;
            result.add(packageColsWithSheetName(book, i, row));
        }
        return result;
    }
    /**
     * getXlsxSheetItem ： 读取xlsx格式文件的所有sheet表的所有列名集合。
     *
     * @param fileName 文件名
     *                  默认表格格式规范，列名全部为字符串。
     * 方法还可以进一步化简，可参考 getExcelData 进一步整合。
     */
    private static List<Map<String, Object>> getXlsxSheetItem(String fileName) {
        TableObject tableObject = new TableObject(fileName).invoke();
        XSSFWorkbook book = tableObject.getXssfBook();
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        for (int i = 0; i < tableObject.getSheet_number(); i++) {
            Sheet sheet = book.getSheetAt(i);
            if (ifSheetNullOrEmpty(sheet)) continue;
            Row row = sheet.getRow(0);
            if (ifRowNullOrEmpty(row)) continue;
            result.add(packageColsWithSheetName(book, i, row));
        }
        return result;
    }
    private static Map<String, Object> packageColsWithSheetName(Workbook book, int i, Row row) {
        Map<String, Object> map = new HashMap<String, Object>();
        String sheet_name = book.getSheetName(i);
        List<String> cols = getCols(row);
        map.put("sheet_name", sheet_name);
        map.put("cols", cols);
        return map;
    }
    private static List<String> getCols(Row row) {
        List<String> cols = new ArrayList<String>();
        System.out.println(row.getLastCellNum());
        for (int j = 0; j < row.getLastCellNum(); j++) {
            Object obj = row.getCell(j);
            cols.add(obj == null ? "" : obj.toString());
        }
        return cols;
    }
    private static boolean ifRowNullOrEmpty(Row row) {
        if (row == null || row.getLastCellNum() == 0 || row.getCell(0) == null) {
            return true;
        }
        return false;
    }
    private static boolean ifSheetNullOrEmpty(Sheet sheet) {
        if (sheet == null || sheet.getLastRowNum() == 0) {
            return true;
        }
        return false;
    }
    /**
     * 获取表格的全部数据
     * @param fileName 文件名
     * @return
     * @throws RuntimeException
     */
    public static Map<String, List<Map<String, Object>>> getTableSheetData(String fileName) throws RuntimeException{
        if(!valid(fileName)) {
            print("文件名校验失败");
            return null;
        }
        else{
            return getExcelData(fileName);
        }
    }
    /**
     * 根据文件后缀格式，确定调用哪种Workbook，此处运用了多态，具体的解析操作都用的是接口。
     * @param fileName
     * @return
     */
    private static Map<String, List<Map<String, Object>>> getExcelData(String fileName) {
        TableObject tableObject = new TableObject(fileName).invoke();
        Workbook book = null;
        if(validExcelFormat(fileName,SUFFIX_XLS)){
            //是excel2003？
            book = tableObject.getHssfBook();
        }
        else if(validExcelFormat(fileName, SUFFIX_XLSX)){
            //是excel2007以后
            book = tableObject.getXssfBook();
        }else{
            print("nonsupport file format");
            throw new RuntimeException("nonsupport file format, please check input fileName again");
        }
        Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>();
        for (int i = 0; i < tableObject.getSheet_number(); i++) {
            List<Map<String, Object>> sheet_data = new ArrayList<Map<String, Object>>();
            Sheet sheet = book.getSheetAt(i);
            if (ifSheetNullOrEmpty(sheet)) continue;
            String sheet_name = book.getSheetName(i);
            System.out.println(sheet.getLastRowNum());
            for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                Row row = sheet.getRow(j);
                if (ifRowNullOrEmpty(row)) continue;
                Map<String, Object> record = new HashMap<String, Object>();
                Row first = sheet.getRow(0);
                getRowData(row, record, first);
                sheet_data.add(record);
            }
            result.put(sheet_name, sheet_data);
        }
        return result;
    }
    
    /**
     * 此处有个点要注意,getLastCellNum,下标是从1开始,有多少列,这里就是这个值.而getLastRowNum,下标是从0开始,也就是21行的表格,这里获得的值是20.用户可自行验证.
     * @param row 该行记录
     * @param record 返回值
     * @param first 表头
     */
    private static void getRowData(Row row, Map<String, Object> record, Row first) {
        for (int k = 0; k < row.getLastCellNum(); k++) {
            String value;
            if (row.getCell(k) == null) {
                value = "";
            } else {
                value = parseDate(row.getCell(k));
                if (value.endsWith("00:00:00")) {
                    value = value.substring(0, value.lastIndexOf("00:00:00"));
                }
            }
            record.put(first.getCell(k).toString(), value);
        }
    }
    /**
     * 判断单元格格式，转化日期格式,日期在poi里保存的是数字,所以这里要转化一下.
     * @param cell 单元格
     * @return
     */
    private static String parseDate(Cell cell) {
        String result = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:// 数字类型
                if (DateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                            .getBuiltinFormat("yyyy/MM/dd")) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd");
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    }
                    Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) {
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil
                            .getJavaDate(value);
                    result = sdf.format(date);
                } else {
                    double value = cell.getNumericCellValue();
                    CellStyle style = cell.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                    String temp = style.getDataFormatString();
                    // 单元格设置成常规
                    if (temp.equals("General")) {
                        format.applyPattern("#");
                    }
                    result = format.format(value);
                }
                break;
            case Cell.CELL_TYPE_STRING:// String类型
                result = cell.getRichStringCellValue().toString();
                break;
            case Cell.CELL_TYPE_BLANK:
                result = "";
                break;
            default:
                result = "";
                break;
        }
        return result;
    }
    /**
     * ifXls：判断是否为 xls 文件
     *
     * @param fileName 包含后缀的文件名
     */
    private static boolean validExcelFormat(String fileName,String type) {
        if(getOS().contains("win")){
            return valid(fileName) && getFileNameSuffix(fileName).equalsIgnoreCase(type);
        }else if(getOS().contains("linux")){
            return valid(fileName) && getFileNameSuffix(fileName).equals(type);
        }else{
            print("System OS is not windows or linux");
            throw new RuntimeException("System OS is not windows or linux , if you use this class in another sys,please implement yourself");
        }
    }
    private static String getOS() { return System.getProperty("os.name").toLowerCase(); }
    private static void print(String msg) {
        System.out.println(msg);
    }
    private static String getFileNameSuffix(String fileName) {
        return fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
    }
    /**
     * valid :判断文件是否存在
     *
     * @param fileName fileName 文件名。
     *                 区分为文件名为null，或者为空。
     */
    private static boolean valid(String fileName) {
        return !ifNull(fileName) && !ifEmpty(fileName);
    }
    private static boolean ifEmpty(String fileName) {
        if (fileName.equals("")) {
            print(NO_FILE_EMPTY);
            return true;
        }
        return false;
    }
    private static boolean ifNull(String fileName) {
        if (fileName == null) {
            print(NO_FILE_NULL);
            return true;
        }
        return false;
    }
    /**
     * 将excel文件写出成txt格式
     * 此处没有指定输出格式
     * @param filePath 输入文件路径
     * @param destFilePath 输出文件路径
     * @throws Exception
     */
    public static void writeExcel2Txt(String filePath,String destFilePath) throws Exception {
        BufferedWriter bufferedWriter = null;
        try {
            FileWriter fw = new FileWriter(destFilePath);
            bufferedWriter = new BufferedWriter(fw);
        } catch (IOException e) {
            e.printStackTrace();
            return;
        }
        Map<String, List<Map<String, Object>>> data = getTableSheetData(filePath);
        System.out.println(data.toString());
        if(data==null){
            throw new Exception("解析数据失败");
        }
        for (int i = 0; i < data.size(); i++) {
            try {
                bufferedWriter.write(DataTransformUtil.mapGetKeyList(data).get(i));
                System.out.println(DataTransformUtil.mapGetKeyList(data).get(i));
                bufferedWriter.newLine();
                bufferedWriter.write("===========================================================");
                bufferedWriter.newLine();
            } catch (IOException e) {
                e.printStackTrace();
            }
            System.out.println("==========================================");
            List<Map<String, Object>> sheet = data.get(DataTransformUtil.mapGetKeyList(data).get(i));
            for (Map map : sheet) {
                System.out.println(map.toString());
                bufferedWriter.write(map.toString());
                bufferedWriter.newLine();
            }
            System.out.println();
            bufferedWriter.newLine();
        }
        bufferedWriter.flush();
        bufferedWriter.close();
    }
    /***
     * writeXlsxFile：将指定数据集生成为excel文件。
     *
     * @param data 一个包含record的List集合
     * @param name 文件名【带后缀】
     * @param path 路径名
     *             只是简单输出，尚未设置样式，单元格格式也未深究。
     */
    public static String writeXlsxFile(List<Map<String, Object>> data, String path, String name) {
        // 创建Excel的工作书册 Workbook,对应到一个excel文档
        Workbook book = null;
        if(validExcelFormat(name,SUFFIX_XLS)){
            book = new HSSFWorkbook();
        }else if(validExcelFormat(name,SUFFIX_XLSX)){
            book = new XSSFWorkbook();
        }else {
            print("nonsupport file format");
            throw new RuntimeException("nonsupport file format, please check input fileName again");
        }
        // 创建Excel的工作sheet,对应到一个excel文档的tab
        Sheet sheet = book.createSheet("sheet1");
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 3500);
        // 创建Excel的sheet的一行
        int c = data.get(0).size();
        List cols = DataTransformUtil.mapGetKeyList(data.get(0));
        Row head = sheet.createRow(0);
        for (int j = 0; j < cols.size(); j++) {
            Cell cell = head.createCell(j, 1);
            cell.setCellValue(cols.get(j).toString());
        }
        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < c; j++) {
                Cell cell = row.createCell(j, 1);
                cell.setCellValue(data.get(i).get(cols.get(j).toString()).toString());
            }
        }
        FileOutputStream os = null;
        try {
            os = new FileOutputStream(new File(new File(path), name));
            book.write(os);
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name ;
    }
    public static void main(String[] args) throws Exception {
//        String str="[{'name':'zhang3','age':33,'gender':'f'}," +
//                "{'name':'li4','age':44,'gender':'m'}," +
//                "{'name':'wang5','age':55,'gender':'m'}," +
//                "{'name':'wang5','age':55,'gender':'m'}," +
//                "{'name':'wang5','age':55,'gender':'m'}]";
//        List<Map<String,Object>> list=DataTransformUtil.nestJsonArrayString2List(str);
//        System.out.println(list.toString());
//        writeXlsxFile(list,"F:\","test");
        long start = System.currentTimeMillis();
        System.out.println("start ：" + System.currentTimeMillis());
       // writeExcel2Txt("F:\152657884.xls","F:\123.txt");
        //System.out.println(getTableSheetCol("F:\需要本地开发功能列表.xlsx"));
        System.out.println(getTableSheetData("F:\需要本地开发功能列表.xlsx"));
    }
    /**
     * extract method object
     */
    private static class TableObject {
        private String fileName;
        private XSSFWorkbook xssfBook;
        private HSSFWorkbook hssfBook;
        private int sheet_number;
        public TableObject(String fileName) {
            this.fileName = fileName;
        }
        public XSSFWorkbook getXssfBook() {
            return xssfBook;
        }
        public HSSFWorkbook getHssfBook() {
            return hssfBook;
        }
        public int getSheet_number() {
            return sheet_number;
        }
        public TableObject invoke() {
            xssfBook = null;
            hssfBook = null;
            sheet_number = 0;
            try {
                FileInputStream is = new FileInputStream(new File(fileName));
                if(validExcelFormat(fileName,SUFFIX_XLS)){
                    hssfBook = new HSSFWorkbook(is);
                    sheet_number = hssfBook.getNumberOfSheets();
                }else if(validExcelFormat(fileName,SUFFIX_XLSX)){
                    xssfBook = new XSSFWorkbook(is);
                    sheet_number = xssfBook.getNumberOfSheets();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return this;
        }
    }
}

发现有个WorkBookFactory的api方法,可以直接拿到对应的HSSFWorkbook或XSSFWorkbook

所以说,研究清楚api才是正道啊……

大牛博客传送门：http://blog.csdn.net/lovesomnus/article/details/23843549