excel(自己)

package excel;

import org.apache.poi.hssf.usermodel.HSSFCell;
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 java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Lenovo on 2017/11/22.
 */
public class ImportExcel {
    private int totalRows;//总行数
   // private int totalCells;//总列数
    private String errorInfo;//错误信息

    //验证excel
    public boolean valideExcel(String filePath){
        if(filePath ==null ||!(WDWUtil.isExcel2003(filePath)||WDWUtil.isExcel2007(filePath))){
            errorInfo="文件不是excel格式";
            return false;
        }
        File file = new File(filePath);
        if(file == null || !file.exists()){
            errorInfo="文件不存在";
            return  false;
        }
        return true;
    }
    //根据文件名读取excel
    public List<List<String>> read(String filePath){
        List<List<String>> dataList = new ArrayList<List<String>>();
        InputStream in=null;
        try {
            if(!valideExcel(filePath)){
                System.out.println(errorInfo);
                return null;
            }
            File file = new File(filePath);
            in = new FileInputStream(file);
            Workbook wb=null;
            if(WDWUtil.isExcel2007(filePath)){
                wb = new XSSFWorkbook(in);
            }else{
                wb = new HSSFWorkbook(in);
            }
            dataList = read(wb);

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(in!=null) {
                    in.close();
                }
            }catch (IOException e){
                e.printStackTrace();
            }

        }
        return dataList;
    }

    public List<List<String>> read(Workbook wb){
        List<List<String>> dataLst = new ArrayList<List<String>>();
        //得到第一个工作表
        Sheet sheet = wb.getSheetAt(0);
        if(sheet == null){
            return dataLst;
        }
        //得到当前表的行数
        //totalRows = sheet.getPhysicalNumberOfRows();(实际有数据的物理行数，中间有空行会忽略)
        totalRows = sheet.getLastRowNum()+1;
//        if(totalRows >=1 && sheet.getRow(0)!=null){
//            totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
//        }（防止每一行的列数不一样，注释，如果项目有要求是规定的列数则放开）
        //循环行将当前的数据拿到
        Row row;
        int totalCells;
        for (int i=0;i<totalRows;i++){
            row = sheet.getRow(i);
            if(row == null){
                continue;
            }
            //totalCells=row.getPhysicalNumberOfCells();//实际有值的列数（会导致后面的值丢失）
            totalCells=row.getLastCellNum();
            List<String> rowlist = new ArrayList<>();
            for(int j=0;j<totalCells;j++){
                Cell cell = row.getCell(j);
                String cellValue="";
                if(cell != null){
                    switch (cell.getCellType()){
                        case HSSFCell.CELL_TYPE_NUMERIC://数字
                            cellValue=cell.getNumericCellValue()+"";
                            break;
                        case HSSFCell.CELL_TYPE_STRING://字符串
                            cellValue=cell.getStringCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                            cellValue=cell.getBooleanCellValue()+"";
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA://公式
                            cellValue=cell.getCellFormula()+"";
                            break;
                        case HSSFCell.CELL_TYPE_BLANK://空值
                            cellValue="";
                            break;
                        case HSSFCell.CELL_TYPE_ERROR://故障
                            cellValue="非法字符";
                            break;
                        default:
                            cellValue="未知类型";
                            break;
                    }
                }
                rowlist.add(cellValue);
            }
            dataLst.add(rowlist);
        }
        return dataLst;
    }
}
class  WDWUtil{
    public static boolean isExcel2003(String filePath){
        return filePath.matches("^.+\\.(?i)(xls)$");
    }
    public static boolean isExcel2007(String filePath){
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

}

package excel;

import java.util.List;

/**
 * Created by Lenovo on 2017/11/22.
 */
public class ExcelTest {
    public static void main(String[] args) {
        ImportExcel poi = new ImportExcel();
        List<List<String>> read = poi.read("C:\\Users\\Lenovo\\Desktop\\1.xlsx");
        for(int i=0;i<read.size();i++){
            for (int j=0;j<read.get(i).size();j++){
                System.out.println("第"+i+"行，第"+j+"列："+read.get(i).get(j));
            }
        }
    }
}