package com;

import java.io.File;

import java.io.IOException;

import jxl.Sheet;

import jxl.Workbook;

import jxl.WorkbookSettings;

import jxl.read.biff.BiffException;

public class ParseExcel {

    /**

     * 解析excel

     * @param filePath 路径

     */

    public void parseExcel(String filePath){

        WorkbookSettings workbookSettings = new WorkbookSettings();

        workbookSettings.setEncoding("gbk");//防止乱码情况

        try {

            Workbook workbook = Workbook.getWorkbook(new File(filePath), workbookSettings);

            Sheet[] sheets = workbook.getSheets();//获取excel中的要解析的sheet

            if(sheets==null||sheets.length==0){

                return;

            }

            for(int sheetIndex=0;sheetIndex<sheets.length;sheetIndex++){

                Sheet sheet = sheets[sheetIndex];

                String sheetName = sheet.getName();

                if("Sheet1".equals(sheetName)){

                    int rows = sheet.getRows();

                    int columns = sheet.getColumns();

                    for(int rowIndex=0;rowIndex<rows;rowIndex++){//遍历行

                        String number = "";

                        String name = "";

                        String sex = "";

                        String birth = "";

                        String address = "";

                        for(int columnIndex=0;columnIndex<columns;columnIndex++){//遍历列

                            String content = sheet.getCell(columnIndex, rowIndex).getContents().trim();//动态获取cell中的内容

                            if(columnIndex==0){

                                number = content;

                            }else if(columnIndex==1){

                                name = content;

                            }else if(columnIndex==2){

                                sex = content;

                            }else if(columnIndex==3){

                                birth = content;

                            }else if(columnIndex==4){

                                address = content;

                            }

                        }

                        System.out.println(number+" | "+name+" | "+sex+" | "+birth+" | "+address);//输出解析的内容

                        if(rowIndex==0){//第一行是标题行，从下一行解析

                            continue;

                        }

                        if("".equals(number)&&"".equals(name)&&"".equals(sex)

                                &&"".equals(birth)&&"".equals(address)){//遇到都是空行停止遍历

                            break;

                        }

                        //TODO 保存一类的业务逻辑

                    }

                }else if("Sheet2".equals(sheetName)){

                    //TODO Sheet2表单业务

                }else if("Sheet3".equals(sheetName)){

                    //TODO Sheet3表单业务

                }

            }

        } catch (BiffException e) {

            e.printStackTrace();

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

    public static void main(String[] args) {

        String filePath = "D:\\test.xls";

        ParseExcel parseExcel = new ParseExcel();

        parseExcel.parseExcel(filePath);

    }

}