先说下踩的坑:
-
jxl 不支持.xlsx 只支持.xls(但使用较为方便)。 poi都支持。
-
cell.getType :获取单元格内的类型 类型如下是:
* CELL_TYPE_NUMERIC 数值型和日期型 值为0
* CELL_TYPE_STRING 字符串型 值为1
* CELL_TYPE_FORMULA 公式型 值为2
* CELL_TYPE_BLANK 空值 值为3
* CELL_TYPE_BOOLEAN 布尔型 值为4
* CELL_TYPE_ERROR 错误 值为5 -
对于 CELL_TYPE_FORMULA (公式型) 直接.toString()
或者其他强转会报错。须用cell.getNumericCellValue()方法计算值。 -
对于带有时分秒的日期 用cell.toString()会直接转换为日期,没有了时分秒,须判断if(cell.getCellType()==0&& DateUtil.isCellDateFormatted(cell)),然后用cell.getDateCellValue()方法,最后时间格式化;
放代码
pom:
<!-- 引入poi,解析workbook视图 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<!-- 处理excel和上面功能是一样的-->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
实现类:
/**
* @param path excle
* @param n 列标题行的位置
* @return
*/
@Override
public Map excleReadMap(String path, Integer n) {
File file = new File(path);
try {
Workbook wb = getWorkbok(file);
if (wb == null) {
Map map = new HashMap();
map.put("errorReason", "文件类型错误");
return map;
}
// Excel的页签数量
int sheet_size = wb.getNumberOfSheets();
Map<String, Object> resultMap = new HashMap<>();
for (int index = 0; index < sheet_size; index++) {
List<LinkedHashMap> outerList = new ArrayList<LinkedHashMap>();
// 每个页签创建一个Sheet对象
Sheet sheet = wb.getSheetAt(index);
//列标题行为第一行
List<String> titleList = new ArrayList<>();
Row titleRow = sheet.getRow(n);
if (titleRow == null) {
continue;
}
int firstTitleRowNum = titleRow.getFirstCellNum();
int lastTitleRowNum = titleRow.getLastCellNum();
for (int i = firstTitleRowNum; i < lastTitleRowNum; i++) {
Cell cell = titleRow.getCell(i);
if (cell != null) {
titleList.add(cell.toString());
}
}
int firstRowIndex = sheet.getFirstRowNum() + n; //解析开始的行数
int lastRowIndex = sheet.getLastRowNum();
for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
//遍历行
LinkedHashMap innerMap = new LinkedHashMap();
Row row = sheet.getRow(rIndex);
if (row != null) {
int firstCellIndex = row.getFirstCellNum();
//第一列没值则跳过
String flg = row.getCell(firstCellIndex) + "";
if ("".equals(flg)) {
continue;
}
int lastCellIndex = row.getLastCellNum();
for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {
//遍历列
Cell cell = row.getCell(cIndex);
if (cell == null) {
String key = titleList.get(cIndex);
innerMap.put(key, "");
} else {
String key = titleList.get(cIndex);
/**
* CELL_TYPE_NUMERIC 数值型 0
* CELL_TYPE_STRING 字符串型 1
* CELL_TYPE_FORMULA 公式型 2
* CELL_TYPE_BLANK 空值 3
* CELL_TYPE_BOOLEAN 布尔型 4
* CELL_TYPE_ERROR 错误 5
*/
int cellType = cell.getCellType();
if (cellType == 0 && DateUtil.isCellDateFormatted(cell)) {
Date dateCellValue = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String cellValue = sdf.format(dateCellValue);
innerMap.put(key, cellValue);
} else {
//公式型 获取值
if (cellType!=2 && cellType!=0){
String cellValue = cell.toString();
innerMap.put(key, cellValue);
}else {
double cellValue = cell.getNumericCellValue();
innerMap.put(key, cellValue);
}
}
}
}
}
outerList.add(rIndex - n, innerMap);
}
String sheetName = sheet.getSheetName();
resultMap.put(sheetName, outerList);
}
return resultMap;
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 判断Excel的版本,获取Workbook
*
* @param file
* @return
* @throws IOException
*/
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
public static Workbook getWorkbok(File file) throws IOException {
Workbook wb = null;
FileInputStream in = new FileInputStream(file);
if (file.getName().endsWith(EXCEL_XLS)) {
//Excel 2003
wb = new HSSFWorkbook(in);
} else if (file.getName().endsWith(EXCEL_XLSX)) {
// Excel 2007/2010
wb = new XSSFWorkbook(in);
} else {
return null;
}
return wb;
}
解析的excle:
解析效果如下:
{
"厂区实验楼": [
{
"统计时间": "统计时间",
"到达人清单": "到达人清单",
"到达人数": "到达人数",
"未到达人清单": "未到达人清单",
"未到达人数": "未到达人数"
},
{
"统计时间": "2020-07-27 09:00:00",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:01",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:02",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:03",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:04",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:05",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:06",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:07",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:08",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:09",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:10",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:11",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:12",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:13",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:14",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:15",
"到达人清单": "1@2@3@4",
"到达人数": 4.0,
"未到达人清单": "5@6",
"未到达人数": 2.0
},
{
"统计时间": "2020-07-27 09:00:16",
"到达人清单": "1@2@3@4",
"到达人数": 4.0,
"未到达人清单": "5@6",
"未到达人数": 2.0
},
{
"统计时间": "2020-07-27 09:00:17",
"到达人清单": "1@2@3@4",
"到达人数": 4.0,
"未到达人清单": "5@6",
"未到达人数": 2.0
},
{
"统计时间": "2020-07-27 09:00:18",
"到达人清单": "1@2@3@4",
"到达人数": 4.0,
"未到达人清单": "5@6",
"未到达人数": 2.0
},
{
"统计时间": "2020-07-27 09:00:19",
"到达人清单": "1@2@3@4",
"到达人数": 4.0,
"未到达人清单": "5@6",
"未到达人数": 2.0
},
{
"统计时间": "2020-07-27 09:00:20",
"到达人清单": "1@2@3@4@5@6",
"到达人数": 6.0,
"未到达人清单": "",
"未到达人数": 0.0
},
{
"统计时间": "2020-07-27 09:00:21",
"到达人清单": "1@2@3@4@5@6",
"到达人数": 6.0,
"未到达人清单": "",
"未到达人数": 0.0
},
{
"统计时间": "2020-07-27 09:00:22",
"到达人清单": "1@2@3@4@5@6",
"到达人数": 6.0,
"未到达人清单": "",
"未到达人数": 0.0
},
{
"统计时间": "2020-07-27 09:00:23",
"到达人清单": "1@2@3@4@5@6",
"到达人数": 6.0,
"未到达人清单": "",
"未到达人数": 0.0
},
{
"统计时间": "2020-07-27 09:00:24",
"到达人清单": "1@2@3@4@5@6",
"到达人数": 6.0,
"未到达人清单": "",
"未到达人数": 0.0
},
{
"统计时间": "2020-07-27 09:00:25",
"到达人清单": "1@2@3@4@5@6",
"到达人数": 6.0,
"未到达人清单": "",
"未到达人数": 0.0
},
{
"统计时间": "2020-07-27 09:00:26",
"到达人清单": "1@2@3@4@5@6",
"到达人数": 6.0,
"未到达人清单": "",
"未到达人数": 0.0
},
{
"统计时间": "2020-07-27 09:00:27",
"到达人清单": "1@2@3@4@5@6",
"到达人数": 6.0,
"未到达人清单": "",
"未到达人数": 0.0
},
{
"统计时间": "2020-07-27 09:00:28",
"到达人清单": "1@2@3@4@5@6",
"到达人数": 6.0,
"未到达人清单": "",
"未到达人数": 0.0
},
{
"统计时间": "2020-07-27 09:00:29",
"到达人清单": "1@2@3@4@5@6",
"到达人数": 6.0,
"未到达人清单": "",
"未到达人数": 0.0
},
{
"统计时间": "2020-07-27 09:00:30",
"到达人清单": "1@2@3@4@5@6",
"到达人数": 6.0,
"未到达人清单": "",
"未到达人数": 0.0
},
{
},
{
"到达人清单": "说明:\n1.每一行的“到达人员清单”与“到达人数”相匹配,且到达人清单中的录入格式为:“人员编号1@人员编号2@人员编号3”\n2.每一行的“未到达人员清单”与“未到达人数”相匹配,且未到达人清单中的录入格式为:“人员编号1@人员编号2@人员编号3”",
"到达人数": "",
"未到达人清单": "",
"未到达人数": ""
}
],
"综合办公楼": [
{
"统计时间": "统计时间",
"到达人清单": "到达人清单",
"到达人数": "到达人数",
"未到达人清单": "未到达人清单",
"未到达人数": "未到达人数"
},
{
"统计时间": "2020-07-27 09:00:00",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:01",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:02",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:03",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:04",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:05",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:06",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:07",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:08",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:09",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:10",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:11",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:12",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:13",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:14",
"到达人清单": "1@2@3",
"到达人数": 3.0,
"未到达人清单": "4@5@6",
"未到达人数": 3.0
},
{
"统计时间": "2020-07-27 09:00:15",
"到达人清单": "1@2@3@4",
"到达人数": 4.0,
"未到达人清单": "5@6",
"未到达人数": 2.0