java读取表格数据,方法已封装好,多数情况下能够直接调用,再对返回的值进行操作。需要进行改动的根据情况进行改动。
1.导入包
需要导入下面三个包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17-beta1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17-beta1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17-beta1</version>
</dependency>
2.代码
public class Excel(){
public List<Map<String,String>> read(String filePath,String columns[]) {
Workbook wb =null;
Sheet sheet = null;
Row row = null;
List<Map<String, String>> list = null;
String cellData = null;
wb = readExcel(filePath);
if(wb != null){
//用来存放表中数据
list = new ArrayList<Map<String,String>>();
//获取第一个sheet
sheet = wb.getSheetAt(0);
//获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
//获取第一行
row = sheet.getRow(0);
//获取最大列数
int colnum = row.getPhysicalNumberOfCells();
for (int i = 1; i<rownum; i++) {
Map<String,String> map = new LinkedHashMap<String,String>();
row = sheet.getRow(i);
if(row !=null){
for (int j=0;j<colnum;j++){
cellData = getCellFormatValue(row.getCell(j));
map.put(columns[j], cellData);
}
}else{
break;
}
list.add(map);
}
}
return list;
}
//读取excel
public static Workbook readExcel(String filePath){
Workbook wb = null;
if(filePath==null){
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try { is = new FileInputStream(filePath);
if(".xls".equals(extString)){
return wb = new HSSFWorkbook(is);
}else if(".xlsx".equals(extString)){
return wb = new XSSFWorkbook(is);
}else{
return wb = null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
@SuppressWarnings("deprecation")
public static String getCellFormatValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
//把数字当成String来读,避免出现1读成1.0的情况
if(cell.getCellType()==0) {
Date d=cell.getDateCellValue();
//如果是时间,则转为yyyy-MM-dd的格式。可以根据自己的需要转化
DateFormat df= new SimpleDateFormat("yyyy-MM-dd");
cellValue=df.format(d);
}else {
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cell.setCellType(Cell.CELL_TYPE_STRING);
}
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: //数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
return cellValue;
}
}
方法说明:读者只需调用 read() 方法,该方法读表时已设置为忽略第一行表头,传入参数filePath:表格的文件地址,
String columns[] 表格中每个字段对应的名字,读者可自行取值,顺序相同即可。返回值为以 字段名为键值,字段值 为值的,map型链表。键值为column[]中的字符值。将所有的类型都转化为了String,读者在取值是可根据类型转换回来。
下面是一个调用和对返回的值处理 示例:
String c[]= {"stuId","stuName","orgName","orgClass"};
List<Map<String,String>> list=read(filePath,c);
DateFormat df= new SimpleDateFormat("yyyy-MM-dd");
for (Map<String,String> map : list) {
String stuId=map.get("stuId");
String stuName=map.get("stuName");
String orgName=map.get("orgName");
String orgClass=map.get("orgClass");
}
谢谢阅读