excel文件导入，同时兼容.xls和.xlsx

**

excel文件导入，同时兼容.xls和.xlsx

这里呢多说无益，直接附上代码，供各位小伙伴查询，如有不足之处，还望大家踊跃指出，互相进步
/
* 导入excel文件
*
* @param importDataQo
* @return
*/
@Override
public CheckMsg impData(ImportDataQo importDataQo) {
Map<String, Object> resultMap = new HashMap<>();
File excel = null;
FileInputStream fis = null;
Workbook wb;
try {
//第一步：解析文件，得到标题数据和内容数据
excel = new File(importDataQo.getFilePath());
if (excel.isFile() && excel.exists()) { //判断文件是否存在
String[] split = excel.getName().split(“\.”); //.是特殊字符，需要转义！！！！！
if (“xls”.equalsIgnoreCase(split[1])) {
fis = new FileInputStream(excel); //文件流对象
if (StringUtil.isEmpty(fis)) {
return CheckMsg.fail().setMsgInfo(“文件生成流处理过程错误”);
}
wb = new HSSFWorkbook(fis);
} else if (“xlsx”.equalsIgnoreCase(split[1])) {
fis = new FileInputStream(excel); //文件流对象
if (StringUtil.isEmpty(fis)) {
return CheckMsg.fail().setMsgInfo(“文件生成流处理过程错误”);
}
wb = new XSSFWorkbook(fis);
} else {
System.out.println(“文件类型错误!”);
return CheckMsg.fail().setMsgInfo(“文件类型错误”);
}
//解析行列数据
analyticExcelData(importDataQo, resultMap, wb);
}

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (!StringUtil.isEmpty(fis)) {
            try {
                fis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    return CheckMsg.success().setData(resultMap);
}

/**
* 解析excel文件数据
*
* @param resultMap
* @param hwb
* @return
*/
public Map<String, Object> analyticExcelData(ImportDataQo importDataQo, Map<String, Object> resultMap, Workbook hwb) {
//开始解析
//读取当前sheet页
Sheet sheet = hwb.getSheetAt(importDataQo.getExcelPageNum());
//读取标题行
Row titleRow = sheet.getRow(importDataQo.getTitleRow() - 1);
//获取开始列
Integer firstCellNum = Integer.valueOf(titleRow.getFirstCellNum());
if (StringUtil.isNotEmpty(importDataQo.getFirstRowNum())) {
firstCellNum = FrTool.getNumByChar(importDataQo.getFirstRowNum()) - 1;
}
//获取结束列
Integer lastCellNum = Integer.valueOf(titleRow.getLastCellNum());
if (StringUtil.isNotEmpty(importDataQo.getLastRowNum())) {
lastCellNum = FrTool.getNumByChar(importDataQo.getLastRowNum()) - 1;
}
//开始行号
Integer firstRowIndex = importDataQo.getFirstCellNum() - 1;
//结束行号
Integer lastRowIndex = sheet.getLastRowNum();
//获取结束列
if (!StringUtil.isEmpty(importDataQo.getLastCellNum()) && 9999 != importDataQo.getLastCellNum()) {
lastRowIndex = importDataQo.getLastCellNum() - 1;
}
//excel文件标题数据
List excelTitleList = new ArrayList<>();
//excel文件内容数据
List<Map<String, Object>> excelDataList = new ArrayList<>();
Map<String, Object> titelMap = new HashMap<>();
//获取标题行数据
for (int j = firstCellNum; j <= lastCellNum; j++) {
Cell cell = titleRow.getCell(j);
if (StringUtil.isEmpty(cell)) {
continue;
}
String cellValue = ReportExcleUtils.getCellValue(cell);
if (excelTitleList.contains(cellValue)) {
cellValue += j;
}
excelTitleList.add(cellValue);
titelMap.put(cellValue, cellValue);
}
if (StringUtil.isNotEmpty(titelMap)) {
excelDataList.add(titelMap);
}
//获取内容行数据
for (int i = firstRowIndex; i <= lastRowIndex; i++) {
Row row = sheet.getRow(i);
if (StringUtil.isEmpty(row)) {
continue;
}
Map<String, Object> map = new HashMap<>();
for (int j = firstCellNum; j <= lastCellNum; j++) {
Cell cell = row.getCell(j);
if (StringUtil.isEmpty(cell)) {
continue;
}
String cellValue = ReportExcleUtils.getCellValue(cell);
if (StringUtil.isNotEmpty(excelTitleList) && excelTitleList.size() > j) {
map.put(excelTitleList.get(j), cellValue);
}
}
if (StringUtil.isNotEmpty(map)) {
excelDataList.add(map);
}
}
resultMap.put(ImportConstant.EXCEL_TITLE, excelTitleList);
resultMap.put(ImportConstant.EXCEL_DATA, excelDataList);
return resultMap;
}

/**
* 获取excel格子的数据
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
String value = “”;
if (cell == null) {
return value;
}
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat(“yyyy-MM-dd”)
.format(date);
} else {
value = “”;
}
} else {
Double cellValue = cell.getNumericCellValue();
if (cellValue == null || cellValue - 0.0 < 1e-6) {
value = “0”;
} else {
value = new DecimalFormat(“0.00”).format(cellValue);
}
if (value.indexOf(“,”) >= 0) {
value = value.replaceAll(“,”, “”);
}
}
break;
case XSSFCell.CELL_TYPE_FORMULA:
// 导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals(“”)) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + “”;
}
break;
case XSSFCell.CELL_TYPE_BLANK:
break;
case XSSFCell.CELL_TYPE_ERROR:
value = “”;
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? “Y”
: “N”);
break;
default:
value = “”;
}
return value;
}