java使用POI进行 Excel文件解析

　　

package com.timevale.esign.vip.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.timevale.esign.db.bean.ErrRefLog;
import com.timevale.esign.db.utils.StringUtil;
import com.timevale.esign.db.utils.UUIDGenerator;
import com.timevale.esign.vip.bean.ExcelDataBean;
import com.timevale.esign.vip.constants.ExcelConstans;

/**
 * 类名：ExcellUtils.java <br/>
 * 功能说明：excel解析工具类 <br/>
 * 修改历史： <br/>
 * 1.[2015年10月13日上午9:59:25]创建类 by hewu
 */
public class ExcelUtil {

    Workbook wb = null;

    List<String[]> dataList = new ArrayList<String[]>(100);

    private Logger LOG = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 功能说明：构造器
     * @param path 修改历史：<br/>
     *            1.[2015年10月13日下午3:52:19] 创建方法 by hewu
     */
    public ExcelUtil(final String path) {
        try {
            final InputStream inp = new FileInputStream(path);
            this.wb = WorkbookFactory.create(inp);
        } catch (FileNotFoundException e) {
            LOG.error("error to find excel File .", e);
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            LOG.error("error to InvalidFormat excel File .", e);
            e.printStackTrace();
        } catch (IOException e) {
            LOG.error("error to deal excel File and found a IOException.", e);
            e.printStackTrace();
        }
    }

    public ExcelUtil() {

        super();
    }

    /**
     * 功能说明：读取Excel所有数据，包含header
     * @param sheetIndex sheet下标
     * @return <br/>
     *         修改历史：<br/>
     *         1.[2015年10月13日下午3:21:47] 创建方法 by hewu
     */
    public final ExcelDataBean readExcel(int sheetIndex) {
        ExcelDataBean bean = new ExcelDataBean();
        int columnNum = 0;
        final Sheet sheet = this.wb.getSheetAt(sheetIndex);
        String name = sheet.getRow(0).getCell(0).getStringCellValue().trim();
        if (name.contains("企业")) {
            bean.setType(ExcelConstans.USER_TYPE_ORGANIZE);
        } else {
            bean.setType(ExcelConstans.USER_TYPE_PERSON);
        }
        if (sheet.getRow(0) != null) {
            columnNum = sheet.getRow(0).getLastCellNum()
                    - sheet.getRow(0).getFirstCellNum();
        }
        if (columnNum > 0) {
            // for (Row row : sheet) {
            int rowNum = sheet.getLastRowNum();
            for (int k = 1; k <= rowNum; k++) {
                final String[] singleRow = new String[columnNum];
                int n = 0;
                for (int i = 0; i < columnNum; i++) {
                    final Cell cell = sheet.getRow(k).getCell(i,
                            Row.CREATE_NULL_AS_BLANK);
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        singleRow[n] = "";
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        singleRow[n] = Boolean.toString(cell
                                .getBooleanCellValue());
                        break;
                    // 数值
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            singleRow[n] = String.valueOf(cell
                                    .getDateCellValue());
                        } else {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            String temp = cell.getStringCellValue();
                            // 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
                            if (temp.indexOf(".") > -1) {
                                singleRow[n] = String.valueOf(new Double(temp))
                                        .trim();
                            } else {
                                singleRow[n] = temp.trim();
                            }
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        singleRow[n] = cell.getStringCellValue().trim();
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        singleRow[n] = "";
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        singleRow[n] = cell.getStringCellValue();
                        if (singleRow[n] != null) {
                            singleRow[n] = singleRow[n].replaceAll("#N/A", "")
                                    .trim();
                        }
                        break;
                    default:
                        singleRow[n] = "";
                        break;
                    }
                    n++;
                }
                // 如果第一行为空，跳过
                final StringBuffer str1 = new StringBuffer();
                for (int t = 0; t < columnNum; t++) {
                    str1.append(singleRow[t]);
                }
                if (!StringUtil.isNull(str1.toString())) {
                    this.dataList.add(singleRow);
                }
            }
        }
        bean.setColData(dataList);
        return bean;
    }

    /**
     * 功能说明：读取Excel，返回Excel最大行index值，实际行数要加1
     * @param sheetIndex sheet下标
     * @return <br/>
     *         修改历史：<br/>
     *         1.[2015年10月13日下午3:21:58] 创建方法 by hewu
     */
    public final int getRowNum(final int sheetIndex) {
        final Sheet sheet = this.wb.getSheetAt(sheetIndex);
        return sheet.getLastRowNum();
    }

    /**
     * 功能说明：读取Excel,返回数据的列数
     * @param sheetIndex sheet下标
     * @return int <br/>
     *         修改历史：<br/>
     *         1.[2015年10月13日下午3:22:07] 创建方法 by hewu
     */
    public final int getColumnNum(final int sheetIndex) {
        final Sheet sheet = this.wb.getSheetAt(sheetIndex);
        final Row row = sheet.getRow(0);
        if (row != null && row.getLastCellNum() > 0) {
            return row.getLastCellNum();
        }
        return 0;
    }

    /**
     * 读取Excel,获取某一行数据
     * @param sheetIndex sheet下标
     * @param rowIndex 计数从0开始，rowIndex为0代表header行
     * @return String[] 修改历史：<br/>
     *         1.[2015年10月13日下午3:22:07] 创建方法 by hewu
     */
    public final String[] getRowData(final int sheetIndex, final int rowIndex) {
        String[] dataArray = null;
        if (rowIndex > this.getColumnNum(sheetIndex)) {
            return dataArray;
        } else {
            dataArray = new String[this.getColumnNum(sheetIndex)];
            return this.dataList.get(rowIndex);
        }

    }

    /**
     * 功能说明：读取Excel获取某一列数据
     * @param sheetIndex sheet下标
     * @param colIndex 列下标
     * @return <br/>
     *         修改历史：<br/>
     *         1.[2015年10月13日下午3:22:46] 创建方法 by hewu
     */
    public final String[] getColumnData(final int sheetIndex, final int colIndex) {
        String[] dataArray = null;
        if (colIndex > this.getColumnNum(sheetIndex)) {
            return dataArray;
        } else {
            if (this.dataList != null && this.dataList.size() > 0) {
                dataArray = new String[this.getRowNum(sheetIndex) + 1];
                int index = 0;
                for (String[] rowData : this.dataList) {
                    if (rowData != null) {
                        dataArray[index] = rowData[colIndex];
                        index++;
                    }
                }
            }
        }
        return dataArray;

    }

    /**
     * 功能说明：导出excel（按照参数 userType导出个人/企业的失败日志）
     * @param templatePath 导出模板路径
     * @param ErrRefLogs 错误对象
     * @param userType 用户类型
     * @return String 导出的excel的临时文件<br/>
     *         修改历史：<br/>
     *         1.[2015年10月13日下午8:06:48] 创建方法 by hewu
     */
    public String writeExcel(String templatePath, List<ErrRefLog> errRefLogs,
            int userType) {
        String onceFile = "";
        onceFile = FileUtil.Copy(new File(templatePath), getClass()
                .getResource("/errlog").getFile()
                + UUIDGenerator.getUUID()
                + ".xlsx");
        FileInputStream input = null;
        Workbook workbook = null;
        String regStr = "[\【\,\】]";
        try {
            input = new FileInputStream(new File(onceFile));
            workbook = WorkbookFactory.create(input);
        } catch (Exception e) {
            e.printStackTrace();
            this.LOG.error("error to create write Excel", e);
        }
        final Sheet sheet = workbook.getSheetAt(0);
        if (userType == ExcelConstans.USER_TYPE_PERSON) {
            // 解析每一条数据,增加行
            for (int i = 0; i < errRefLogs.size(); i++) {
                ErrRefLog log = errRefLogs.get(i);
                final Row row = sheet.createRow(i
                        + ExcelConstans.EXCEL_START_INDEX);
                // 增加列数据
                // ErrRefLog errRefLog = ErrRefLogs.get(i);
                final CellStyle ss = workbook.createCellStyle();
                ss.setLocked(false);

                final Cell nameC = row.createCell(0);
                nameC.setCellStyle(ss);
                nameC.setCellType(XSSFCell.CELL_TYPE_STRING);
                nameC.setCellValue(log.getName());// 写入内容

                final Cell mobileC = row.createCell(1);
                mobileC.setCellType(XSSFCell.CELL_TYPE_STRING);
                mobileC.setCellStyle(ss);
                mobileC.setCellValue(log.getMobile());// 写入内容

                final Cell idNoC = row.createCell(2);
                idNoC.setCellType(XSSFCell.CELL_TYPE_STRING);
                idNoC.setCellStyle(ss);
                idNoC.setCellValue(log.getIdNo());// 写入内容

//                final Cell emailC = row.createCell(3);
//                emailC.setCellType(XSSFCell.CELL_TYPE_STRING);
//                emailC.setCellStyle(ss);
//                emailC.setCellValue(log.getEmail());// 写入内容

                final Cell companyC = row.createCell(3);
                companyC.setCellType(XSSFCell.CELL_TYPE_STRING);
                companyC.setCellStyle(ss);
                companyC.setCellValue(log.getOrgan());// 写入内容

                StringBuffer errReason = new StringBuffer();
                final Cell reasonC = row.createCell(4);
                reasonC.setCellType(XSSFCell.CELL_TYPE_STRING);
                reasonC.setCellStyle(ss);
                if(log.getErrReason().contains("【")){
                    String [] reason = log.getErrReason().split(regStr);
                    errReason.append(reason[0]+"【");
                    errReason.append("*"+reason[1].subSequence(reason[1].length()-1, reason[1].length())+","+reason[2].substring(0, 6)+"****");
                    errReason.append("】"+reason[3]);
                    reasonC.setCellValue(errReason.toString());
                }else {
                    reasonC.setCellValue(log.getErrReason());
                }
            }
        } else if (userType == ExcelConstans.USER_TYPE_ORGANIZE) {
            // 解析每一条数据,增加行
            for (int i = 0; i < errRefLogs.size(); i++) {
                final ErrRefLog log = errRefLogs.get(i);
                final JSONObject obj = JSONObject.fromObject(log
                        .getContentJson());
                final Row row = sheet.createRow(i
                        + ExcelConstans.EXCEL_START_INDEX);
                // 增加列数据
                // ErrRefLog errRefLog = ErrRefLogs.get(i);
                final CellStyle ss = workbook.createCellStyle();
                ss.setLocked(false);
                ss.setAlignment(CellStyle.ALIGN_CENTER);
                // 企业名称
                final Cell nameC = row.createCell(0);
                nameC.setCellStyle(ss);
                nameC.setCellType(XSSFCell.CELL_TYPE_STRING);
                nameC.setCellValue(log.getName());
                // 手机号
                final Cell mobileC = row.createCell(1);
                mobileC.setCellType(XSSFCell.CELL_TYPE_STRING);
                mobileC.setCellStyle(ss);
                mobileC.setCellValue(log.getMobile());
                // 组织机构代码证号
                final Cell organCodeC = row.createCell(2);
                organCodeC.setCellType(XSSFCell.CELL_TYPE_STRING);
                organCodeC.setCellStyle(ss);
                organCodeC.setCellValue(log.getOrganCode());
                // 邮箱
//                final Cell emailC = row.createCell(3);
//                emailC.setCellType(XSSFCell.CELL_TYPE_STRING);
//                emailC.setCellStyle(ss);
//                emailC.setCellValue(log.getEmail());
                // 单位类型
                final Cell companyC = row.createCell(3);
                companyC.setCellType(XSSFCell.CELL_TYPE_STRING);
                companyC.setCellStyle(ss);
                companyC.setCellValue(obj.getInt("organType"));
                // 注册类型
                final Cell userTypeC = row.createCell(4);
                userTypeC.setCellType(XSSFCell.CELL_TYPE_STRING);
                userTypeC.setCellStyle(ss);
                userTypeC.setCellValue(obj.getInt("userType"));
                // 工商注册号
                final Cell regCodeC = row.createCell(5);
                regCodeC.setCellType(XSSFCell.CELL_TYPE_STRING);
                regCodeC.setCellStyle(ss);
                regCodeC.setCellValue(obj.getString("regCode"));
                // 法定代表姓名
                final Cell legalNameC = row.createCell(6);
                legalNameC.setCellType(XSSFCell.CELL_TYPE_STRING);
                legalNameC.setCellStyle(ss);
                legalNameC.setCellValue(obj.getString("legalName"));
                // 法定代表人身份证号
                final Cell legalIdNoC = row.createCell(7);
                legalIdNoC.setCellType(XSSFCell.CELL_TYPE_STRING);
                legalIdNoC.setCellStyle(ss);
                legalIdNoC.setCellValue(obj.getString("legalIdNo"));
                // 法定代表人归属地
                final Cell legalAreaC = row.createCell(8);
                legalAreaC.setCellType(XSSFCell.CELL_TYPE_STRING);
                legalAreaC.setCellStyle(ss);
                legalAreaC.setCellValue(obj.getInt("legalArea"));
                // 代理人姓名
                final Cell agentNameC = row.createCell(9);
                agentNameC.setCellType(XSSFCell.CELL_TYPE_STRING);
                agentNameC.setCellStyle(ss);
                agentNameC.setCellValue(obj.getString("agentName"));
                // 代理人姓名
                final Cell agentIdNoC = row.createCell(10);
                agentIdNoC.setCellType(XSSFCell.CELL_TYPE_STRING);
                agentIdNoC.setCellStyle(ss);
                agentIdNoC.setCellValue(obj.getString("agentIdNo"));
                // 公司地址
                final Cell addressC = row.createCell(11);
                addressC.setCellType(XSSFCell.CELL_TYPE_STRING);
                addressC.setCellStyle(ss);
                addressC.setCellValue(obj.getString("address"));
                // 经营范围
                final Cell scopeC = row.createCell(12);
                scopeC.setCellType(XSSFCell.CELL_TYPE_STRING);
                scopeC.setCellStyle(ss);
                scopeC.setCellValue(obj.getString("scope"));
                // 失败原因
                StringBuffer errReason = new StringBuffer();
                final Cell reasonC = row.createCell(13);
                reasonC.setCellType(XSSFCell.CELL_TYPE_STRING);
                reasonC.setCellStyle(ss);
                if(log.getErrReason().contains("【")){
                    String [] reason = log.getErrReason().split(regStr);
                    errReason.append(reason[0]+"【");
                    errReason.append("*"+reason[1].subSequence(reason[1].length()-1, reason[1].length())+","+reason[2].substring(0, 6)+"****");
                    errReason.append("】"+reason[3]);
                    reasonC.setCellValue(errReason.toString());
                }else {
                    reasonC.setCellValue(log.getErrReason());
                }
            }
        } else {

        }
        // 新建一输出流
        FileOutputStream fout;
        try {
            fout = new FileOutputStream(onceFile);
            // 存盘
            workbook.write(fout);
            fout.flush();
            // 结束关闭
            fout.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            this.LOG.error("fileNotFoundException", e);
        } catch (IOException e) {
            e.printStackTrace();
            this.LOG.error("IOException", e);
        }
        return onceFile;
    }

    // public static void main(String[] args) {
    // // 导入excel
    // // ExcelUtils utils = new ExcelUtils("D:\用户导入模版.xls");
    // ExcelUtil utils = new ExcelUtil("d:\企业用户导入模版 .xlsx");
    // List<String[]> list = utils.readExcel(0);
    // for (int i = 0; i < list.size(); i++) {
    // String[] str = list.get(i);
    // for (int j = 0; j < str.length; j++) {
    // System.out.println(str[j]);
    // }
    //
    // }
    // //导出excel
    // ExcelUtils utils = new ExcelUtils(
    // ExcelConstans.ERR_ORGANIZE_ACCOUNT_EXPORT_DIR);
    // List<ErrRefLog> accounts = new ArrayList<ErrRefLog>();
    // String path = utils.writeExcel(
    // ExcelConstans.ERR_ORGANIZE_ACCOUNT_EXPORT_DIR, accounts, 2);
    // System.out.println(path);
    // }
//    public static void main(String[] args) {
//        String regStr = "[\【\,\】]";
//        String s = "我是【张三,430381199007086018】使用";
//        System.out.println(s.split(regStr)[2]);
//        
//    }
}

　　文件解析的相关依赖包可以去maven库下载