package com.tdx.tdxoatd.service; import com.tdx.annotation.InitConst; import com.tdx.tdxoatd.util.ConstUtil; import com.tdx.tdxoatd.util.UuidUtil; import com.tdx.util.MapUtil; import com.tdx.util.XmlUtil; import org.apache.logging.log4j.LogManager; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.net.URL; import java.net.URLDecoder; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import com.tdx.web.annotation.Service; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; import javax.annotation.Resource; @Service public class ReadFileService { @Resource private UserService userService; private static Logger logger = LogManager.getLogger("TdxOatd"); @InitConst public void init(){ try { URL url = XmlUtil.class.getClassLoader().getResource("test_119023.xlsx"); String filePath= url.getPath();//获取文件的路径 List<Map<String, Object>> resultList = readExcel(filePath); //遍历集合,保存数据 } catch (Exception e){ logger.error("初始化失败", e); } } /** * 读取Excel文件内容 * @param filePath 要读取的Excel文件所在路径 * @return 读取结果列表,读取失败时返回null */ public static List<Map<String, Object>> readExcel(String filePath) { Workbook workbook = null; FileInputStream inputStream = null; try { // 获取Excel后缀名 String fileType = filePath.substring(filePath.lastIndexOf(".") + 1, filePath.length()); //获取券商ID String p = URLDecoder.decode(filePath,"UTF-8"); File excelFile = new File(p); String fileName = excelFile.getName(); String temp = fileName.substring(0, fileName.lastIndexOf(".")); String arr[] = temp.split("_"); String QSID = arr[arr.length-1];//券商ID // 获取Excel文件 if (!excelFile.exists()) { logger.info("指定的Excel文件不存在!"); return null; } // 获取Excel工作簿 inputStream = new FileInputStream(excelFile); workbook = getWorkbook(inputStream, fileType); // 读取excel中的数据 List<Map<String, Object>> resultDataList = parseExcel(workbook,QSID); return resultDataList; } catch (Exception e) { logger.info("解析Excel失败,文件名:" + filePath + " 错误信息:" + e.getMessage()); return null; } finally { try { if (null != workbook) { workbook.close(); } if (null != inputStream) { inputStream.close(); } } catch (Exception e) { logger.info("关闭数据流出错!错误信息:" + e.getMessage()); return null; } } } /** * 解析Excel数据 * @param workbook Excel工作簿对象 * @return 解析结果 */ private static List<Map<String, Object>> parseExcel(Workbook workbook,String QSID) { List<Map<String, Object>> resultDataList = new ArrayList<Map<String,Object>>(); // 解析sheet for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { Sheet sheet = workbook.getSheetAt(sheetNum); // 校验sheet是否合法 if (sheet == null) { continue; } // 获取第一行数据 int firstRowNum = sheet.getFirstRowNum(); Row firstRow = sheet.getRow(firstRowNum); if (null == firstRow) { logger.info("解析Excel失败,在第一行没有读取到任何数据!"); } // 解析每一行的数据,构造数据对象 int rowStart = firstRowNum + 1; int rowEnd = sheet.getPhysicalNumberOfRows(); for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) { Row row = sheet.getRow(rowNum); if (null == row) { continue; } Map<String, Object> map = new HashMap<String, Object>(); Cell cell; int cellNum = 0; // 获取创建日期 cell = row.getCell(cellNum++); String date = convertCellValueToString(cell); map.put("createTime", date); //获取手机号码 cell = row.getCell(cellNum++); String mobileNum = convertCellValueToString(cell); if(mobileNum.matches(ConstUtil.REGEX_MOBILE)){ map.put("mobileNum", mobileNum); }else{ logger.info("第"+(rowNum+1)+"行手机号格式错误,该数据已忽略"); continue; } //获取开户状态 cell = row.getCell(cellNum++); String openFlag = convertCellValueToString(cell); if("成功".equals(openFlag)){ map.put("openFlag", ConstUtil.SUCCESS); }else if("失败".equals(openFlag)){ map.put("openFlag", ConstUtil.FAILURE); }else{ logger.info("第"+(rowNum+1)+"行开户状态格式错误,该数据已忽略"); continue; } map.put("QSID", QSID); resultDataList.add(map); } } return resultDataList; } /** * 将单元格内容转换为字符串 * @param cell * @return */ private static String convertCellValueToString(Cell cell) { if(cell==null){ return null; } String returnValue = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //数字 if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); returnValue = date.toLocaleString(); } else{ Double doubleValue = cell.getNumericCellValue(); // 格式化科学计数法,取一位整数 DecimalFormat df = new DecimalFormat("0"); returnValue = df.format(doubleValue); } break; case Cell.CELL_TYPE_STRING: //字符串 returnValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: //布尔 Boolean booleanValue = cell.getBooleanCellValue(); returnValue = booleanValue.toString(); break; case Cell.CELL_TYPE_BLANK: // 空值 break; case Cell.CELL_TYPE_FORMULA: // 公式 returnValue = cell.getCellFormula(); break; case Cell.CELL_TYPE_ERROR: // 故障 break; default: break; } return returnValue; } /** * 根据文件后缀名类型获取对应的工作簿对象 * @param inputStream 读取文件的输入流 * @param fileType 文件后缀名类型(xls或xlsx) * @return 包含文件数据的工作簿对象 * @throws IOException */ public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException { Workbook workbook = null; if (fileType.equalsIgnoreCase(ConstUtil.XLS)) { workbook = new HSSFWorkbook(inputStream); } else if (fileType.equalsIgnoreCase(ConstUtil.XLSX)) { workbook = new XSSFWorkbook(inputStream); } return workbook; } }
java利用poi读取excel文件内容
最新推荐文章于 2024-05-01 23:10:25 发布