java解析excel

java解析excel 

前提条件，添加依赖
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.15</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.15</version>
    </dependency>

目录结构：

以下为代码：

Change 实体类：

/**
 * @Author: huangshun
 * @Date: 2019/8/14 13:38
 * @Version 1.0
 */
public class Change {
    private String newEmployeeId;     //新工号
    private String eRPEmployeeId;     //erp 老员工工号

    @Override
    public String toString() {
        return "Change{" +
                "newEmployeeId='" + newEmployeeId + '\'' +
                ", eRPEmployeeId='" + eRPEmployeeId + '\'' +
                '}';
    }

    public String getNewEmployeeId() {
        return newEmployeeId;
    }

    public void setNewEmployeeId(String newEmployeeId) {
        this.newEmployeeId = newEmployeeId;
    }

    public String geteRPEmployeeId() {
        return eRPEmployeeId;
    }

    public void seteRPEmployeeId(String eRPEmployeeId) {
        this.eRPEmployeeId = eRPEmployeeId;
    }
}

两个测试的Servlet:

import java.io.IOException;

/**
 * @Author: huangshun
 * @Date: 2019/8/14 11:39
 * @Version 1.0
 */
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

public class readExcelServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");

        //excel文件路径
        String excelPath = "D:\\ygEmpIdNewOld.xlsx";
        List<Change> list=new ArrayList<Change>();
        try {
            //String encoding = "GBK";
            File excel = new File(excelPath);
            if (excel.isFile() && excel.exists()) {   //判断文件是否存在

                String[] split = excel.getName().split("\\.");  //.是特殊字符，需要转义！！！！！
                Workbook wb;
                //根据文件后缀（xls/xlsx）进行判断
                if ( "xls".equals(split[1])){
                    FileInputStream fis = new FileInputStream(excel);   //文件流对象
                    wb = new HSSFWorkbook(fis);
                }else if ("xlsx".equals(split[1])){

                wb = new XSSFWorkbook(excel);
                }else {
                    System.out.println("文件类型错误!");
                    return;
                }

                //开始解析
                Sheet sheet = wb.getSheetAt(0);     //读取sheet 0

                int firstRowIndex = sheet.getFirstRowNum()+1;   //第一行是列名，所以不读
                int lastRowIndex = sheet.getLastRowNum();
                //System.out.println("firstRowIndex: "+firstRowIndex);//第一行
                //System.out.println("lastRowIndex: "+lastRowIndex);//最后一行
                for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {   //遍历行
                    //System.out.println("rIndex 第: " + rIndex+"行--");
                    Row row = sheet.getRow(rIndex);    //得到具体的行
                    Change change=null;//创建一个新老员工的对象
                    if (row != null) {
                        change=new Change();
                        int firstCellIndex = row.getFirstCellNum();//第一列
                        int lastCellIndex = row.getLastCellNum();//最后一列
                        for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {   //遍历列
                            Cell cell = row.getCell(cIndex);
                            if (cell != null) {
                                if(cIndex%2==0){
                                    //偶数  eRPEmployeeId
                                    change.setNewEmployeeId(cell+"");
                                }else{
                                    change.seteRPEmployeeId(cell+"");
                                }
                                list.add(change);
                                //System.out.println("cell----"+cell.toString());
                            }
                        }
                    }
                }
                System.out.println("list--"+list);
            } else {
                System.out.println("找不到指定的文件");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        request.setAttribute("list",list);
        request.getRequestDispatcher("/secondServlet").forward(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }
}

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

/**
 * @Author: huangshun
 * @Date: 2019/8/14 14:06
 * @Version 1.0
 */
@WebServlet("/secondServlet")
public class SecondServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        List<Change> list = (List<Change>)request.getAttribute("list");
        System.out.println("SecondServlet"+list);
        //解析list 集合
        for (Change change : list) {
            if("text".equals(change.geteRPEmployeeId())){
                //存在与新工号对应的老员工工号
                System.out.println("老员工工号为---"+change.geteRPEmployeeId());
            }

        }



    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);

    }
}