packagecom.test.excel;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.xssf.usermodel.XSSFCell;importorg.apache.poi.xssf.usermodel.XSSFRow;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importjava.io.FileInputStream;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.util.Map;/***@authorlh
* @date 2020/7/3
* @description*/
public classExcelUtils {/*** 读取Excel数据
*@returnList
*@throwsException*/
public static List> readExcle() throwsException{//用流的方式读取Excel文件
FileInputStream fis = new FileInputStream("C:\\Users\\Think\\Desktop\\test.xlsx");//获取Excel工作簿
XSSFWorkbook xf = newXSSFWorkbook(fis);//获取第一个sheet
XSSFSheet sheet = xf.getSheetAt(0);//获取第一行
int firstRow =sheet.getFirstRowNum();//获取最后一行
int lastRow =sheet.getLastRowNum();//System.out.println(lastRow);//用于map设置key值,自定义
String columns[] = {"username", "password"};
List< Map> list = new ArrayList<>();for (int i = 0;i<=lastRow;i++){
XSSFRow row=sheet.getRow(i);if (row!=null){//获取第一行,第一列
int firstcell =row.getFirstCellNum();//获取最后一行,最后一列
int lastcell =row.getLastCellNum();
Map map = new HashMap<>();for (int j = 0;j
XSSFCell cell=row.getCell(j);if (cell == null){continue;
}//封装成map
map.put(columns[j], getCellFormatValue(cell));
}//System.out.println(map);//将map放入List
list.add(map);
}
fis.close();
xf.close();
}returnlist;
}//格式化数值类型
public staticObject getCellFormatValue(Cell cell){
Object cellValue= null;/*** getCellTypeEnum()方法是枚举类型,用于判断单元格值类型,有以下五种格式:
* _NONE(-1),
* NUMERIC(0),
* STRING(1),
* FORMULA(2),
* BLANK(3),
* BOOLEAN(4),
* ERROR(5);*/
switch(cell.getCellTypeEnum()){caseSTRING:
cellValue=cell.getStringCellValue();break;caseNUMERIC:/**如果是数字类型转换成数字类型,但是初始化数字加的.0,因此可以转换成int类型去掉.0
* cell.getNumericCellValue()*/cellValue= newDouble(cell.getNumericCellValue()).intValue();break;caseBOOLEAN:break;caseFORMULA:break;
}returncellValue;
}//测试
public static void main(String[] arg) throwsException{
ExcelUtils excelTest= newExcelUtils();
excelTest.readExcle();
}
}