Java 自定义读取Excel文件

项目依赖包

        <dependency>
            <groupId>xml-apis</groupId>
            <artifactId>xml-apis</artifactId>
            <version>1.4.01</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>18.0</version>
        </dependency>
		<dependency>
			<groupId>org.wuwz</groupId>
			<artifactId>ExcelKit</artifactId>
			<version>2.0</version>
		</dependency>
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

使用注解的Bean，序号标识该字段对应Excel的第几列

/**
 * Created by user on 2017/4/20.
 */
public class PostLogisticImport {

    @ExcelAnnotion(name = "记录序号",order = 0)
    private String id;

    @ExcelAnnotion(name = "用户自编号",order = 2)
    private String uid;

    @ExcelAnnotion(name = "寄达局邮编",order = 1)
    private String sid;

    @ExcelAnnotion(name = "寄达局名称",order = 3)
    private String sname;

    @ExcelAnnotion(name = "收件人名称",order = 4)
    private String receiver;

    @ExcelAnnotion(name = "收件人地址",order = 6)
    private String telephone;

    @ExcelAnnotion(name = "收件人电话",order = 5)
    private String receiveAddress;

    @ExcelAnnotion(name = "邮件重量",order = 7)
    private String receiveUnit;

    @ExcelAnnotion(name = "邮件号码",order = 8)
    private String receiveCode;

    @ExcelAnnotion(name = "备注",order = 9)
    private String weight;

    @ExcelAnnotion(name = "收件人单位",order = 10)
    private String logisticId;

    @ExcelAnnotion(name = "收件人邮编",order = 11)
    private String remark;

    @ExcelAnnotion(name = "内件详情",order = 12)
    private String innerDetail;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

    public String getSid() {
        return sid;
    }

    public void setSid(String sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getReceiver() {
        return receiver;
    }

    public void setReceiver(String receiver) {
        this.receiver = receiver;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public String getReceiveAddress() {
        return receiveAddress;
    }

    public void setReceiveAddress(String receiveAddress) {
        this.receiveAddress = receiveAddress;
    }

    public String getReceiveUnit() {
        return receiveUnit;
    }

    public void setReceiveUnit(String receiveUnit) {
        this.receiveUnit = receiveUnit;
    }

    public String getReceiveCode() {
        return receiveCode;
    }

    public void setReceiveCode(String receiveCode) {
        this.receiveCode = receiveCode;
    }

    public String getWeight() {
        return weight;
    }

    public void setWeight(String weight) {
        this.weight = weight;
    }

    public String getLogisticId() {
        return logisticId;
    }

    public void setLogisticId(String logisticId) {
        this.logisticId = logisticId;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    public String getInnerDetail() {
        return innerDetail;
    }

    public void setInnerDetail(String innerDetail) {
        this.innerDetail = innerDetail;
    }

    @Override
    public String toString() {
        return "PostLogisticImport{" +
                "id='" + id + '\'' +
                ", uid='" + uid + '\'' +
                ", sid='" + sid + '\'' +
                ", sname='" + sname + '\'' +
                ", receiver='" + receiver + '\'' +
                ", telephone='" + telephone + '\'' +
                ", receiveAddress='" + receiveAddress + '\'' +
                ", receiveUnit='" + receiveUnit + '\'' +
                ", receiveCode='" + receiveCode + '\'' +
                ", weight='" + weight + '\'' +
                ", logisticId='" + logisticId + '\'' +
                ", remark='" + remark + '\'' +
                ", innerDetail='" + innerDetail + '\'' +
                '}';
    }
}

读取Excel 工具类

/**
 * Created by user on 2017/4/20.
 */

import cn.soqi.mp.qihome.annotion.ExcelAnnotion;
import cn.soqi.mp.qihome.po.PostLogisticImport;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

/**
 * Excel模板转Bean读取工具
 *
 * @author brozer
 * @email 798121446@qq.com
 * @create 2017-04-20 10:07
 */
public class ExcelUtil {


    /**
     * 读取XLS文件
     * @param file
     * @param pointXY 从第几行，第几列开始读取
     * @throws IOException
     */
    public static List<Object> readXLSFile(File file,Class cs,Integer...pointXY) throws IOException, IllegalAccessException {
        List<Object> objectList=new LinkedList<>();
        InputStream ExcelFileToRead = new FileInputStream(file.getAbsoluteFile());
        HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell cell;
        int pointY=0,pointX=0;
        if(pointXY.length>1&&pointXY[0]!=null){//从第几行开始读(默认从0开始)
            pointY=pointXY[0];
        }
        if(pointXY.length>1&&pointXY[1]!=null){//从第几列开始读（默认从0开始）
            pointX=pointXY[1];
        }
        Iterator rows = sheet.rowIterator();
        while (rows.hasNext()) {
            row = (HSSFRow) rows.next();
            if(pointY>0){
                pointY--;
                continue;
            }
            Object object=null;
            //反射机制
            try {
                object=cs.newInstance();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            Iterator cells = row.cellIterator();
            int templateX=pointX;
            while (cells.hasNext()) {
                cell = (HSSFCell) cells.next();
                if(templateX>0){
                    templateX--;
                    continue;
                }
                Field[] fields=cs.getDeclaredFields();
                if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING||cell.getCellType() ==HSSFCell.CELL_TYPE_NUMERIC){
                    int i=cell.getColumnIndex(); //第几列（跟字段的注解映射）
                    for (int k=0;k<fields.length;k++){
                        Field field=fields[k];
                        Annotation[] annotations=field.getAnnotations();
                        ExcelAnnotion excelAnnotion= (ExcelAnnotion) annotations[0];
                        if(excelAnnotion.order()==i){
                            String value="";
                            if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
                                value=cell.getStringCellValue()+"";
                            if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
                                value=cell.getNumericCellValue()+"";
                            field.setAccessible(true);
                            field.set(object,value);
                            break;
                        }
                    }
                }
            }
            objectList.add(object);
        }
        return objectList;
    }

    /**
     * 待开发中
     * @param file
     * @throws IOException
     */
    public static void writeXLSFile(File file) throws IOException {

        String excelFileName = file.getAbsolutePath();// name of excel file

        String sheetName = "Sheet1";// name of sheet
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);

        // iterating r number of rows
        for (int r = 0; r < 5; r++) {
            HSSFRow row = sheet.createRow(r);

            // iterating c number of columns
            for (int c = 0; c < 5; c++) {
                HSSFCell cell = row.createCell(c);

                cell.setCellValue("Cell " + r + " " + c);
            }
        }

        FileOutputStream fileOut = new FileOutputStream(excelFileName);

        // write this workbook to an Outputstream.
        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }

    /**
     * 读取XLSX文件
     * @param file 文件名
     * @param pointXY 从第几行，第几列开始读取
     * @throws IOException
     */
    public static List<Object> readXLSXFile(File file, Class cs, Integer...pointXY) throws IOException, IllegalAccessException {
        List<Object> objectList=new LinkedList<>();
        InputStream ExcelFileToRead = new FileInputStream(file.getAbsoluteFile());
        XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
        XSSFWorkbook test = new XSSFWorkbook();
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row;
        XSSFCell cell;
        int pointY=0,pointX=0;
        if(pointXY.length>1&&pointXY[0]!=null){//从第几行开始读(默认从0开始)
            pointY=pointXY[0];
        }
        if(pointXY.length>1&&pointXY[1]!=null){//从第几列开始读（默认从0开始）
            pointX=pointXY[1];
        }
        Iterator rows = sheet.rowIterator();
        while (rows.hasNext()) {
            row = (XSSFRow) rows.next();
            if(pointY>0){
                pointY--;
                continue;
            }
            Object object=null;
            //反射机制
            try {
                object=cs.newInstance();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            Iterator cells = row.cellIterator();
            int templateX=pointX;
            while (cells.hasNext()) {
                cell = (XSSFCell) cells.next();
                if(templateX>0){
                    templateX--;
                    continue;
                }
                Field[] fields=cs.getDeclaredFields();
                if(cell.getCellType() == XSSFCell.CELL_TYPE_STRING||cell.getCellType() ==XSSFCell.CELL_TYPE_NUMERIC){
                    int i=cell.getColumnIndex(); //第几列（跟字段的注解映射）
                    for (int k=0;k<fields.length;k++){
                        Field field=fields[k];
                        Annotation[] annotations=field.getAnnotations();
                        ExcelAnnotion excelAnnotion= (ExcelAnnotion) annotations[0];
                        if(excelAnnotion.order()==i){
                            /*System.out.print(excelAnnotion.order());
                            System.out.print(excelAnnotion.name());*/
                            String value="";
                            if(cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
                                value=cell.getStringCellValue()+"";
                            if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
                                value=cell.getNumericCellValue()+"";
                            field.setAccessible(true);
                            field.set(object,value);
                            break;
                        }
                    }
                }
            }
            objectList.add(object);
        }
        return objectList;
    }

    /**
     * 待开发中
     * @throws IOException
     */
    public static void writeXLSXFile() throws IOException {

        String excelFileName = "C:/Test.xlsx";// name of excel file

        String sheetName = "Sheet1";// name of sheet

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(sheetName);

        // iterating r number of rows
        for (int r = 0; r < 5; r++) {
            XSSFRow row = sheet.createRow(r);

            // iterating c number of columns
            for (int c = 0; c < 5; c++) {
                XSSFCell cell = row.createCell(c);

                cell.setCellValue("Cell " + r + " " + c);
            }
        }

        FileOutputStream fileOut = new FileOutputStream(excelFileName);

        // write this workbook to an Outputstream.
        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }

    public static List<Object> readExcel(File file,Class cs) throws IOException, IllegalAccessException {
        List<Object> objectList;
        try {
            objectList=readXLSXFile(file,cs);
        }catch (Exception ex){
            objectList=readXLSFile(file,cs);

        }
        return objectList;
    }

}