public class ReadExcel {
/**
* 读取.xlsx或.xls文件
*
* @param filePath
* @return
*/
public static Map<String, Object> readExcel(String filePath) throws Exception {
//文件输入流
FileInputStream in = null;
//wk对象
Workbook wk = null;
in = new FileInputStream(filePath);
wk = getWorkbook(in, filePath);
//获取sheet页个数
int numberOfSheets = wk.getNumberOfSheets();
//列数
int columnCount = 0;
//行数
int rowCount = 0;
//创建存放数据的容器
Map<String,Object> dataMap = new LinkedHashMap<>();
//内容
Map<String,List<String>> contents = new HashMap<>();
for (int i = 0; i < numberOfSheets; i++) {
//创建存放数据的容器
List<String> dataContent = new ArrayList<>();
//获取sheet页
Sheet sheet = wk.getSheetAt(i);
//获取sheet页最大行数
rowCount = sheet.getLastRowNum()>rowCount?sheet.getLastRowNum():rowCount;
//获取sheet页名字
String sheetName = wk.getSheetName(i);
//遍历sheet页中的每一行
for (Row row : sheet) {
String content = "";
//获取每一行的最大列数
columnCount = row.getLastCellNum()>columnCount?row.getLastCellNum():columnCount;
//遍历每一行的列
for (int j = 0; j < columnCount; j++) {
//获取列
Cell cell1 = row.getCell(j);
//获取列的值,为空的设置为空字符
String value = cell1 == null ?"":cell1.getStringCellValue();
//替换数据中的英文逗号
value = value.indexOf(",")>-1 ?value.replaceAll(",","") + "":value;
//拼接内容,使用逗号区分每一列
content += value + ",";
}
//将每一行的值添加到容器中
dataContent.add(content);
}
//将每个sheet页添加到容器中
contents.put(sheetName,dataContent);
}
//将所有数据存放进map容器中,如果读者需要其他变量数据可以自己声明再赋值
dataMap.put("dataContent",contents);
in.close();
wk.close();
return dataMap;
}
/**
* 判断excel文件是.xls还是.xlsx生成相应的读取文件对象
*
* @param inStr
* @param fileName
* @return
* @throws Exception
*/
public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (".xls".equals(fileType)) {
wb = new HSSFWorkbook(inStr); //2003-
} else if (".xlsx".equals(fileType)) {
wb = StreamingReader.builder()
.rowCacheSize(100) //缓存到内存中的行数,默认是10
.bufferSize(4096) //读取资源时,缓存到内存的字节大小,默认是1024
.open(inStr); //打开资源,必须,可以是InputStream或者是File,注意:只能打开XLSX格式的文件 //2007+
} else {
throw new Exception("解析的文件格式有误!");
}
return wb;
}
}
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.