9.简单格式Excel的读取

import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigInteger;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel文件的读与写
 * 
 * @see 读 2017年7月25日
 * @see 写
 * @author swd
 *
 */
public class ExcelUtil {

    public static void main(String[] args) throws Exception {
        // 读取Excel文件测试
        String sourcePath = "C:\\Users\\dell\\Desktop\\读取文件测试.xlsx";
        Class<Entity> clazz = Entity.class;
        int[] rowNums = { 0, 1, 2, 3, 4, 5 };
        int lineNum = 0;
        List<Object> objList = readExcel(sourcePath, clazz, rowNums, lineNum);

        for (Object object : objList) {
            Entity a = (Entity) object;
            System.out.println(a.toString());
        }

        // 写入Excel文件测试
    }

    /**
     * 读取Excel(.xlsx/.xls)文件
     * 
     * @see 1.对应关系:Excel文件每一列的对应的字段应与实体类对应的属性保持一致(含义对应,数量相等,顺序相同)
     * @see 2.rowNums:表示要读取Excel文件的哪几列,未读的列所对应实体类的属性值为null
     * @see 3.类型转换:该方法得到的是Object的集合,使用时应将集合中的对象强转为对应的类型
     * @param sourcePath
     *            源文件路径
     * @param clazz
     *            Excel文件对应的实体类的class
     * @param rowNums
     *            读取Excel文件的哪几列
     * @param lineNum
     *            从Excel文件的第几行开始读
     * @return 实体类的集合
     * @throws Exception
     */
    public static List<Object> readExcel(String sourcePath, Class<?> clazz, int[] rowNums, int lineNum)
            throws Exception {
        if (!(sourcePath == null || "".equals(sourcePath.trim()))) {
            String postfix = getPostfix(sourcePath);
            List<Object> objList = null;
            switch (postfix) {
            case "xlsx":
                objList = readXlsx(sourcePath, clazz, rowNums, lineNum);
                break;
            case "xls":
                objList = readXls(sourcePath, clazz, rowNums, lineNum);
                break;
            default:
                throw new Exception("File Type Mismatch");
            }
            return objList;
        }
        return null;
    }

    /**
     * Excel(.xlsx)文件的读取
     * 
     * @param sourcePath
     *            源Excel文件路径
     * @param clazz
     *            实体类
     * @param rowNums
     *            需要读取哪几列
     * @param lineNum
     *            从第几行开始读
     * @return 实体类列表
     * @throws Exception
     */
    private static List<Object> readXlsx(String sourcePath, Class<?> clazz, int[] rowNums, int lineNum)
            throws Exception {

        InputStream is = new FileInputStream(sourcePath);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        is.close();
        List<Object> objList = new ArrayList<Object>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null)
                continue;
            // Read the Row
            for (int rowNum = lineNum, lastRowNum = xssfSheet.getLastRowNum(); rowNum <= lastRowNum; rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    Object obj = clazz.newInstance();
                    Field[] fields = clazz.getDeclaredFields();
                    int j = fields.length <= rowNums.length ? fields.length : rowNums.length;
                    for (int i = 0; i < j; i++) {
                        fields[rowNums[i]].setAccessible(true);
                        fields[rowNums[i]].set(obj, getValue(xssfRow.getCell(rowNums[i]), xssfWorkbook));
                    }
                    objList.add(obj);
                }
            }
        }
        return objList;
    }

    /**
     * Excel(.xls)文件的读取
     * 
     * @param sourcePath
     *            源Excel文件路径
     * @param clazz
     *            实体类
     * @param rowNums
     *            需要读取哪几列
     * @param lineNum
     *            从第几行开始读
     * @return 实体类列表
     * @throws Exception
     */
    private static List<Object> readXls(String sourcePath, Class<?> clazz, int[] rowNums, int lineNum)
            throws Exception {
        InputStream is = new FileInputStream(sourcePath);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        is.close();
        List<Object> objList = new ArrayList<Object>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null)
                continue;
            // Read the Row
            for (int rowNum = lineNum; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    Object obj = clazz.newInstance();
                    Field[] fields = clazz.getDeclaredFields();
                    int j = fields.length <= rowNums.length ? fields.length : rowNums.length;
                    for (int i = 0; i < j; i++) {
                        fields[rowNums[i]].setAccessible(true);
                        fields[rowNums[i]].set(obj, getValue(hssfRow.getCell(rowNums[i]), hssfWorkbook));
                    }
                    objList.add(obj);
                }
            }
        }
        return objList;
    }

    /**
     * 获得文件后缀
     * 
     * @param sourcePath
     * @return
     */
    private static String getPostfix(String sourcePath) {
        if (sourcePath.contains(".")) {
            return sourcePath.substring(sourcePath.lastIndexOf(".") + 1, sourcePath.length());
        }
        return "";
    }

    /**
     * 读取xlsx文件单元格的值
     * 
     * @param xssfRow
     * @param xssfWorkbook
     * @return 单元格的值 ,若xssfRowCell为null,则返回""
     */
    private static String getValue(XSSFCell xssfRowCell, XSSFWorkbook xssfWorkbook) {
        StringBuffer sb = new StringBuffer();
        if (xssfRowCell != null) {
            switch (xssfRowCell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC: // 0
                boolean dateFlag = DateUtil.isCellDateFormatted(xssfRowCell);
                if (dateFlag) // 转日期
                    sb.append(DateFormat.getDateTimeInstance().format(xssfRowCell.getDateCellValue()));
                else // 已包含科学计数法
                    sb.append(getRealStringValueOfDouble(xssfRowCell.getNumericCellValue()));
                break;
            case XSSFCell.CELL_TYPE_STRING: // 1
                sb.append(xssfRowCell.getStringCellValue().trim());
                break;
            case XSSFCell.CELL_TYPE_FORMULA: // 2
                FormulaEvaluator evaluator = xssfWorkbook.getCreationHelper().createFormulaEvaluator();
                xssfRowCell.getCellFormula();
                evaluator.evaluateFormulaCell(xssfRowCell);
                DecimalFormat decimalFormat = new DecimalFormat("0");
                sb.append(decimalFormat.format(xssfRowCell.getNumericCellValue()));
                break;
            case XSSFCell.CELL_TYPE_BLANK: // 3
                sb.append("");
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN: // 4
                sb.append(String.valueOf(xssfRowCell.getBooleanCellValue()));
                break;
            case XSSFCell.CELL_TYPE_ERROR: // 5
                sb.append(xssfRowCell.getErrorCellString());
                break;
            default:
                sb.append(xssfRowCell.getRichStringCellValue());
                break;
            }
        } else {
            return "";
        }
        return sb.toString();
    }

    /**
     * 读取xls文件单元格的值
     * 
     * @param hssfCell
     * @return 单元格的值 ,若hssfCell为null,则返回""
     */
    private static String getValue(HSSFCell hssfCell, HSSFWorkbook hssfWorkbook) {
        StringBuffer sb = new StringBuffer();
        if (hssfCell != null) {
            switch (hssfCell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC: // 0
                boolean dateFlag = DateUtil.isCellDateFormatted(hssfCell);
                if (dateFlag) // 转日期
                    sb.append(DateFormat.getDateTimeInstance().format(hssfCell.getDateCellValue()));
                else // 已包含科学计数法
                    sb.append(getRealStringValueOfDouble(hssfCell.getNumericCellValue()));
                break;
            case XSSFCell.CELL_TYPE_STRING: // 1
                sb.append(hssfCell.getStringCellValue().trim());
                break;
            case XSSFCell.CELL_TYPE_FORMULA: // 2
                FormulaEvaluator evaluator = hssfWorkbook.getCreationHelper().createFormulaEvaluator();
                hssfCell.getCellFormula();
                evaluator.evaluateFormulaCell(hssfCell);
                DecimalFormat decimalFormat = new DecimalFormat("0");
                sb.append(decimalFormat.format(hssfCell.getNumericCellValue()));
                break;
            case XSSFCell.CELL_TYPE_BLANK: // 3
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN: // 4
                sb.append(String.valueOf(hssfCell.getBooleanCellValue()));
                break;
            case XSSFCell.CELL_TYPE_ERROR: // 5
                sb.append(String.valueOf(hssfCell.getErrorCellValue()));
                break;
            default:
                sb.append(hssfCell.getRichStringCellValue());
                break;
            }
        } else {
            return "";
        }
        return sb.toString();
    }

    /**
     * 处理科学计数法,转成普通字符串
     * 
     * @param doubleValue
     * @return
     */
    private static String getRealStringValueOfDouble(Double doubleValue) {
        String doubleStr = doubleValue.toString();
        boolean flag = doubleStr.contains("E");
        int indexOfPoint = doubleStr.indexOf('.');
        if (flag) {
            int indexOfE = doubleStr.indexOf('E');
            // 小数部分
            BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint + BigInteger.ONE.intValue(), indexOfE));
            // 指数
            int pow = Integer.valueOf(doubleStr.substring(indexOfE + BigInteger.ONE.intValue()));
            int xsLen = xs.toByteArray().length;
            int scale = xsLen - pow > 0 ? xsLen - pow : 0;
            doubleStr = String.format("%." + scale + "f", doubleValue);
        } else {
            java.util.regex.Pattern p = Pattern.compile(".0$");
            java.util.regex.Matcher m = p.matcher(doubleStr);
            if (m.find()) {
                doubleStr = doubleStr.replace(".0", "");
            }
        }
        return doubleStr;
    }
}

2.示例实体类

“`
package com.xxx.util;

public class Entity {

private String xingming;
private String shenfenzheng;
private String shouji;
private String xingbie;
private String nianling;
private String zhuzhi;
public String getXingming() {
    return xingming;
}
public void setXingming(String xingming) {
    this.xingming = xingming;
}

public String getShouji() {
    return shouji;
}
public void setShouji(String shouji) {
    this.shouji = shouji;
}
public String getXingbie() {
    return xingbie;
}
public void setXingbie(String xingbie) {
    this.xingbie = xingbie;
}
public String getNianling() {
    return nianling;
}
public void setNianling(String nianling) {
    this.nianling = nianling;
}
public String getZhuzhi() {
    return zhuzhi;
}
public void setZhuzhi(String zhuzhi) {
    this.zhuzhi = zhuzhi;
}
@Override
public String toString() {
    return "Entity [xingming=" + xingming + ", shenfenzheng=" + shenfenzheng + ", shouji=" + shouji + ", xingbie="
            + xingbie + ", nianling=" + nianling + ", zhuzhi=" + zhuzhi + "]";
}

}