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;}@Overridepublic String toString() {    return "Entity [xingming=" + xingming + ", shenfenzheng=" + shenfenzheng + ", shouji=" + shouji + ", xingbie="            + xingbie + ", nianling=" + nianling + ", zhuzhi=" + zhuzhi + "]";}

}