public List<Map<String, Object>> mxsxExcel(String fileName, MultipartFile file) throws Exception {
//fileName 文件名称
//file文件
// 存放所有行数据
List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
// 保存标题
List<String> columnTitleList = new ArrayList<String>();
boolean notNull = false;
//校验文件是否是xls文件
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
throw new Exception("上传文件格式不正确");
}
boolean isExcel2003 = true;
//校验文件是否是xlsx文件
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
//获取文件流数据
InputStream is = file.getInputStream();
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
if (sheet != null) {
notNull = true;
}
//获取列标题
if (columnTitleList.size() == 0) {
/* 1、读取表头操作 */
Row row = sheet.getRow(2); // 读取第二行
short totalCells = row.getLastCellNum();
for (short c = 0; c < totalCells; c++) {// 读取列,从第一列开始
Cell cell = row.getCell(c);
if (cell == null) {
// columnTitleList.add(ExcelUtil.EMPTY);
continue;
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String title = cell.getStringCellValue().trim();
if (!ExcelUtil.EMPTY.equals(title)) {
columnTitleList.add(title);
}
}
}
int totalRows = sheet.getLastRowNum();
//从第四行开始读取数据
for (int rownum = 3; rownum <= totalRows; rownum++) {
// 存放每一行数据
Map<String, Object> map = new HashMap<>();
Row row = sheet.getRow(rownum);
if (row == null) {
continue;
}
short totalCells = (short) columnTitleList.size();
String title = null;
for (short c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
title = columnTitleList.get(c);
if (cell == null) {
if("属性名称".equals(title)||"属性长度".equals(title)||"属性类型".equals(title)){
System.out.println("第"+rownum+1+"行有未输入的必填项,添加失败");
}
map.put(title, ExcelUtil.EMPTY);
continue;
}
// 设置成字符串
cell.setCellType(Cell.CELL_TYPE_STRING);
map.put(title, cell.getStringCellValue().trim());
}
dataList.add(map);
}
return dataList;
}
属性模板表(红色列为必填项) | |||
请输入属性名 | 请输入数字 | 请输入数字 | |
属性名称 | 属性长度 | 属性类型 | 属性描述 |
模板示例图