public class ReadExcleutil {private static final Logger log = Logger.getLogger(String.valueOf(ReadExcleutil.class));/*** 解析上传的excel， 默认只解析第一张Sheet** @param fileexcel* @param startRow 开始行（若为 0 ，则从第一行）* @param endRow 结束行 （可自定义,，若为 0 ，则自动检索最后行数）* @param startCell 开始列（可自定义，若为 0 ，则自动检索首列）* @param endCell 结束行列（可自定义，若为 0 ，则自动检索最后列）* @return List<String [ ]>* @throws IOException*/public static List<JSONObject> getExcelData(MultipartFile file, int startRow,int endRow,int startCell,int endCell) throws IOException, InvalidFormatException {int resultSize = 0;List<JSONObject> resultData = new ArrayList<>();if (!checkFile(file)) {log.info("上传的excel文件格式有问题");return resultData;}//获得Workbook工作薄对象InputStream ins = file.getInputStream();//创建工作簿，不区分xls，xlsxWorkbook workbook = WorkbookFactory.create(ins);if (workbook != null) {//获取第一张sheet工作表Sheet sheet = workbook.getSheetAt(0);if (sheet == null) {return resultData;}// 重新初始化List结果大小resultSize = sheet.getLastRowNum() + 1;//获得当前sheet的开始行int firstRowNum = sheet.getFirstRowNum();//获得当前sheet的结束行int lastRowNum = sheet.getLastRowNum();//若输入的结束行数不为0，则根据输入的结束行数停止if(endRow>0){lastRowNum = endRow;}//循环除了startRow的所有行,如果要循环除第一行以外的就firstRowNum+1for (int rowNum = firstRowNum + startRow; rowNum <= lastRowNum; rowNum++) {//获得当前行Row row = sheet.getRow(rowNum);if (rowIsEmpty(row)) {break;}//获得当前行的开始列int firstCellNum = row.getFirstCellNum();//若输入的结束列不为0，则根据输入的开始行数if(startCell>0){firstCellNum = startCell;}//获得当前行的列数int lastCellNum = row.getLastCellNum();if(endCell>0){lastCellNum = endCell;}//String[] cells = new String[lastCellNum];JSONObject js = new JSONObject();//循环当前行for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {Cell cell = row.getCell(cellNum);//cells[cellNum] = getCellValue(cell);//System.out.println(cell.toString());js.put(String.valueOf(cellNum),getCellValue(cell));}resultData.add(js);}workbook.close();}return resultData;}/*** 检查文件格式** @param file* @throws IOException*/public static boolean checkFile(MultipartFile file) throws IOException {if (null == file) {log.info("文件不存在！");return false;}//获得文件名String fileName = file.getOriginalFilename();//判断文件是否是excel文件if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {log.info(fileName + "不是excel文件");return false;}return true;}public static String getCellValue(Cell cell) {String cellValue = "";if (cell == null) {return cellValue;}switch (cell.getCellTypeEnum()) {case NUMERIC://数字cellValue = stringDateProcess(cell);break;case STRING://字符串cellValue = String.valueOf(cell.getStringCellValue());break;case BOOLEAN://BooleancellValue = String.valueOf(cell.getBooleanCellValue());break;case FORMULA://公式cellValue = String.valueOf(cell.getCellFormula());break;case BLANK://空值cellValue = "";break;case ERROR://故障cellValue = "非法字符";break;default:cellValue = "未知类型";break;}return cellValue;}public static String stringDateProcess(Cell cell) {String result = new String();if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式SimpleDateFormat sdf = null;if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {sdf = new SimpleDateFormat("HH:mm");} else {// 日期sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");}Date date = cell.getDateCellValue();result = sdf.format(date);} else if (cell.getCellStyle().getDataFormat() == 58) {// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");double value = cell.getNumericCellValue();Date date = DateUtil.getJavaDate(value);result = sdf.format(date);} else {double value = cell.getNumericCellValue();CellStyle style = cell.getCellStyle();DecimalFormat format = new DecimalFormat();String temp = style.getDataFormatString();// 单元格设置成常规if (temp.equals("General")) {format.applyPattern("0");}result = format.format(value);}return result;}/*** @return * @param null* @Author* @Description //TODO 判断excel的row是否全为空* @Date /12/2 19:30* @Param*/public static boolean rowIsEmpty(Row row) {if (null == row) {return true;}for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {Cell cell = row.getCell(c);if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {return false;}}return true;}}
