/**
*
* @Title: upload
* @Description: 批量上传
* @return HResponse
* @author fun
* @date 2018年9月7日
*/
@ResponseBody
@RequestMapping(value = "/upload", method = RequestMethod.POST)
public HResponse upload(@RequestParam(value = "file") MultipartFile file) {
List<T> list = this.getExcelInfo(file);
return HResponseUtil.toStringSuccess(CommonCanstants.MESSAGE_CODE_200000);
}
/**
*
* @Title: getExcelInfo
* @Description: 读取Excel信息
* @return List<T>
* @author fun
* @date 2018年9月7日
*/
public List<T> getExcelInfo(MultipartFile mFile) {
String fileName = mFile.getOriginalFilename();
List<T> list = new ArrayList<>();
try {
if (!validateExcel(fileName)) {
return null;
}
boolean isExcel2003 = true;
if (isExcel2007(fileName)) {
isExcel2003 = false;
}
list = createExcel(mFile.getInputStream(), isExcel2003, error);
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
*
* @Title: validateExcel
* @Description: 验证EXCEL文件
* @return boolean
* @author fun
* @date 2018年9月10日
*/
public boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
String errorMsg = "文件名不是excel格式";
return false;
}
return true;
}
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
/**
*
* @Title: createExcel
* @Description: 根据excel里面的内容读取信息
* @return List<T>
* @author fun
* @date 2018年9月10日
*/
public List<T> createExcel(InputStream is, boolean isExcel2003, List<String> error) {
List<T> list = new ArrayList<>();
try {
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
list = readExcelValue(wb, error);
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
/**
*
* @Title: readExcelValue
* @Description: 读取Excel信息
* @return List<T>
* @author fun
* @date 2018年9月5日
*/
public List<T> readExcelValue(Workbook wb, List<String> error) {
ReadExcel readExcel = new ReadExcel();
int totalRows = readExcel.getTotalRows();
int totalCells = readExcel.getTotalCells();
Sheet sheet = wb.getSheetAt(0);
totalRows = sheet.getPhysicalNumberOfRows();
if (totalRows > 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List<T> list = new ArrayList<>();
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
T model = new T();
for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (c == 0) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
model.set***(cell.getStringCellValue());
}
} else if (c == 1) {
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
model.set***(cell.getStringCellValue());
}
} else if (c == 2) {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
model.set***(new DecimalFormat("0").format(cell.getNumericCellValue()));
}
}
}
}
list.add(model);
}
return list;
}