导出Excel到istMap中工具类
/**
* 将Excel数据读取到ListMap
* @param file
*/
public static List<Map<String, Object>> readExcelDataToListMap(MultipartFile file) {
String edition_2003 = "^.+\\.(?i)(xls)$"; // Excel_2003版本
String edition_2007 = "^.+\\.(?i)(xlsx)$"; // Excel_2007版本
String fileName = file.getOriginalFilename(); // 获取文件名
if (StringUtils.isNotBlank(fileName)) {
try {
// 根据Excel版本创建对象
Workbook wb = null;
if (fileName.matches(edition_2003)) {
wb = new HSSFWorkbook(file.getInputStream());
} else {
if (fileName.matches(edition_2007)) {
wb = new XSSFWorkbook(file.getInputStream());
}
}
// 读取Excel里面的数据
if (null != wb) {
// 得到第一个shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行数
int totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列数(前提是有行数)
int totalCells = 0;
if (totalRows > 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
// 循环Excel行数
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
// 循环Excel的列
Map<String, Object> map = new HashMap<String, Object>();
for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
int cellType = cell.getCellType();
if (cellType == 0) {
DataFormatter dataFormatter = new DataFormatter();
dataFormatter.addFormat("###########", null);
String str = dataFormatter.formatCellValue(cell);
if (StringUtils.isNotBlank(str)) {
map.put(c+"",str);
}
}else{
String str = String.valueOf(cell);
if (StringUtils.isNotBlank(str)) {
map.put(c+"",str);
}
}
}
}
// 添加到list
if (map.size() > 0) {
listMap.add(map);
}
}
return listMap;
}
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}