java读取excel文件_Java 读取Excel 文件内容

packagecom.apusic;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importjava.io.File;importjava.io.FileInputStream;importjava.io.InputStream;importjava.util.ArrayList;importjava.util.List;/*** Created by baizhuang on 2018-11-20.

*

* dsccription: 读取本地的一个excel文档，并输出到控制台*/

public classReadLocalExcel {//总行数

private int totalRows = 0;//总列数

private int totalCells = 0;//错误信息

privateString errorInfo;privateReadLocalExcel(){

}public intgetTotalRows() {returntotalRows;

}public intgetTotalCells() {returntotalCells;

}publicString getErrorInfo() {returnerrorInfo;

}//检查文件是否为excel或者为空

public booleanvalidateExcel(String filePath){//检查文件格式

if (filePath==null || !(UUtil.isExcel2003(filePath) ||UUtil.isExcel2007(filePath))) {

errorInfo= "不是excel格式";return false;

}//检查文件是否存在

File file = newFile(filePath);if(file==null || !file.exists()){

errorInfo= "文件不存在";return false;

}return true;

}public List>read(String filePath){

List> dataList = new ArrayList>();

InputStream is= null;try{//验证文件

if(!validateExcel(filePath)){

System.out.println(errorInfo);return null;

}//判断文件类型

boolean isExcel2003 = true;if(UUtil.isExcel2007(filePath)){

isExcel2003= false;

}//调用读取方法

File file = newFile(filePath);

is= newFileInputStream(file);

dataList=read(is,isExcel2003);

is.close();

}catch(Exception e ){

}finally{

}returndataList;

}public List> read(InputStream inputStream,boolean isExcel2003) throwsException{

List> dataLst = null;

Workbook wb= null;if(isExcel2003){

wb= newHSSFWorkbook(inputStream);

}else{

wb= newXSSFWorkbook(inputStream);

}

dataLst=readWork(wb);returndataLst;

}public List>readWork(Workbook wb){

List> dataList = new ArrayList>();//得到第一个shell

Sheet sheet =wb.getSheetAt(0);this.totalRows =sheet.getPhysicalNumberOfRows();if(this.totalRows>=1 && sheet.getRow(0)!=null){this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();

}//循环excel的行

for (int r=0;r

Row row=sheet.getRow(r);if(row == null){continue;

}

List rowList = new ArrayList();//循环excel的列

for(int c=0;c

Cell cell=row.getCell(c);

String cellValue= "";if(null !=cell){//判断数据类型

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);

}

dataList.add(rowList);

}returndataList;

}public static voidmain(String[] args){

ReadLocalExcel t= newReadLocalExcel();//List> list = t.read("D://a.xlsx");

List> list = t.read("D:\\a.xlsx");if(list!=null){

System.out.println("***************************************");for (int i=0;i

System.out.print("第"+i+"行：");

List cellList =list.get(i);for (int j=0;j

System.out.print(" "+cellList.get(j));

}

System.out.println();

}

System.out.println("***************************************");

}

}

}classUUtil{public static booleanisExcel2003(String filePath){return filePath.matches("^.+\\.(?i)(xls)$");

}public static booleanisExcel2007(String filePath){return filePath.matches("^.+\\.(?i)(xlsx)$");

}

}