java操作excel_每天一个小技术之Java操作Excel

/*** @Auther: Mr.BoBo* @Date: 2020/6/10 15:38* @Description:*/

public class ExcelUtil {

private final static String excel2003L =".xls"; //2003- 版本的excel private final static String excel2007U =".xlsx"; //2007+ 版本的excel /*** Excel导入*/

public static List> getBankListByExcel(InputStream in, String fileName) throws Exception{

List> list = null;

//创建Excel工作薄 Workbook work = getWorkbook(in,fileName);

if(null == work){

throw new Exception("创建Excel工作薄为空！");

}

Sheet sheet = null;

Row row = null;

Cell cell = null;

list = new ArrayList<>();

//遍历Excel中所有的sheet for (int i = 0; i < work.getNumberOfSheets(); i++) {

sheet = work.getSheetAt(i);

if(sheet==null){continue;}

//遍历当前sheet中的所有行 //包涵头部，所以要小于等于最后一列数,这里也可以在初始值加上头部行数，以便跳过头部 for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {

//读取一行 row = sheet.getRow(j);

//去掉空行和表头 if(row==null||row.getFirstCellNum()==j){continue;}

//遍历所有的列 List li = new ArrayList();

for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {

cell = row.getCell(y);

li.add(getCellValue(cell));

}

list.add(li);

}

}

return list;

}

/*** 描述：根据文件后缀，自适应上传文件的版本*/

public static Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{

Workbook wb = null;

String fileType = fileName.substring(fileName.lastIndexOf("."));

if(excel2003L.equals(fileType)){

wb = new HSSFWorkbook(inStr); //2003