基于Apache Poi的excel解析 附示例代码链接

• poi是Apache基金会下的一个开源项目,对excel、doc等文档进行解析和创建
• 首先是对excel的解析
• 这个是一个excel的模板,上代码
• 一个实体类对应excel的信息

package com.sunyw.xyz.vo;

import java.io.Serializable;

public class PoiExcelBean implements Serializable {
    private static final long serialVersionUID = 4056442638289512007L;


    private String name;

    private String age;

    private String phoneNumber;

    private String email;
    
    private String address;


    @Override
    public String toString() {
        return "PoiExcelBean{" +
                "name='" + name + '\'' +
                ", age='" + age + '\'' +
                ", phoneNumber='" + phoneNumber + '\'' +
                ", email='" + email + '\'' +
                ", address='" + address + '\'' +
                '}';
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getPhoneNumber() {
        return phoneNumber;
    }

    public void setPhoneNumber(String phoneNumber) {
        this.phoneNumber = phoneNumber;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

• excel工具类

package com.sunyw.xyz.utils;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.util.StringUtils;

import java.text.DecimalFormat;
import java.util.regex.Pattern;

public class ExcelUtils {
    /**
     * 取值
     *
     * @param row
     * @param cell
     * @param sheet
     * @return
     */
    public String getCellValue(int row, int cell, Sheet sheet) {
        String value;
        CellType cellType = sheet.getRow(row).getCell(cell).getCellType();
        switch (cellType) {
            case STRING:
                value = sheet.getRow(row).getCell(cell).getStringCellValue();
                break;
            case NUMERIC:
                value = String.valueOf(sheet.getRow(row).getCell(cell).getNumericCellValue());
                break;
            case FORMULA:
                value = formatString(String.valueOf(sheet.getRow(row).getCell(cell).getNumericCellValue()));
                break;
            case BOOLEAN:
                value = String.valueOf(sheet.getRow(row).getCell(cell).getBooleanCellValue());
                break;
            default:
                value = "";
        }
        return value;
    }

    /**
     * 去除"."
     *
     * @param value
     * @return
     */
    public String formatString(String value) {
        if (!StringUtils.isEmpty(value)) {
            value = value.substring(0, value.indexOf("."));
        }
        return value;
    }

    /**
     * 科学计数法转换为数字
     *
     * @param value
     * @return
     */
    public String format(String value) {
        Pattern pattern = Pattern.compile("(-?\\d+\\.?\\d*)[Ee]{1}[\\+-]?[0-9]*");
        DecimalFormat ds = new DecimalFormat("0");
        if (!pattern.matcher(value).matches()) {
            return value;
        }
        value = ds.format(Double.parseDouble(value)).trim();
        return value;
    }
}

• 工具类的getcell中row为竖行,cell为列,从0开始计数
  
• 像姓名,如果要取值的话就应该是在第2行的第0位,往后依次类推
• 因为有的像电话中在读出来后可能会显示为科学计数法的形式,还有像整数会有.0,比如我上面的年龄20,在取值后就变为了20.0,所以做了去除0和科学计数法转换
• 处理类

package com.sunyw.xyz.service;

import com.sunyw.xyz.utils.ExcelUtils;
import com.sunyw.xyz.vo.PoiExcelBean;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;

public class ExcelService {

    private static final String xlsx = ".xlsx";
    private static final String xls = ".xls";

    public static void main(String[] args) throws IOException {
        System.out.println("------------------------<开始进行excel解析>------------------------");
        PoiExcelBean bean = new PoiExcelBean();
        File file = new File("/21.xls");
        //File file = new File("D:/21.xls");
        String fileName = file.getName();
        //判断文件类型
        InputStream inputStream = new FileInputStream(file);
        String end_name = fileName.substring(fileName.lastIndexOf("."));
        Workbook wb;
        System.out.println("endname:" + end_name);
        if (xlsx.equals(end_name)) {
            /*xlsx格式需要使用 XSSFWorkbook对象进行解析*/
            wb = new XSSFWorkbook(inputStream);
            System.out.println("当前为xlsx格式开始解析");
        } else if (xls.equals(end_name)) {
            /*xls格式需要使用 HSSFWorkbook对象进行解析*/
            wb = new HSSFWorkbook(inputStream);
            System.out.println("当前为xls格式,开始解析");
        } else {
            System.out.println("文件格式错误");
            return;
        }
        //获取第一个sheet页,即excel下方的sheet
        Sheet sheet = wb.getSheetAt(0);
        //excel的角标读取时是从0开始,跟数组一样
        ExcelUtils utils = new ExcelUtils();
        String name = utils.getCellValue(2, 0, sheet);
        System.out.println("姓名:" + name);
        bean.setName(name);
        String age = utils.formatString(utils.getCellValue(2, 1, sheet));
        System.out.println("年龄:" + age);
        bean.setAge(age);
        String phone = utils.format(utils.getCellValue(2, 2, sheet));
        System.out.println("电话:" + phone);
        bean.setPhoneNumber(phone);
        String email = utils.getCellValue(2, 3, sheet);
        System.out.println("邮箱:" + email);
        bean.setEmail(email);
        String address = utils.getCellValue(2, 4, sheet);
        System.out.println("地址:" + address);
        bean.setAddress(address);
        System.out.println("对象信息为:[" + bean + "]");
        System.out.println("------------------------<excel解析完毕>------------------------");
    }

}

• 结果
  

• 码云地址:https://gitee.com/sunyaowei/poitest