public class ExcelOperatorClass { private static final String EXCEL2003L = ".xls"; private static final String EXCEL2007U = ".xlsx"; /** * 描述:获取IO流中的数据,组装成List<List<Object>>对象 * * @param fileName * @return * @throws Exception */ public List<List<Object>> getSheetData(String fileName) throws Exception { List<List<Object>> list = null; //创建Excel工作薄 Workbook work = this.getWorkbook2007(fileName); if (null == work) { throw new Exception("创建Excel工作薄为空!"); } Sheet sheet = null; Row row = null; Cell cell = null; list = new ArrayList<List<Object>>(); //遍历Excel中所有的sheet for (int i = 0; i < work.getNumberOfSheets(); i++) { sheet = work.getSheetAt(i); if (sheet == null) { continue; } //遍历当前sheet中的所有行 for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) { row = sheet.getRow(j); if (row == null || row.getFirstCellNum() == j) { continue; } //遍历所有的列 List<Object> li = new ArrayList<Object>(); for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) { cell = row.getCell(y); li.add(this.getCellValue(cell)); } list.add(li); } } work.close(); return list; } /** * 描述:获取IO流中的数据,组装成List<List<Object>>对象 * * @param in,fileName * @return * @throws Exception */ public List<List<Object>> getSheetData(InputStream in, String fileName, int page) throws Exception { List<List<Object>> list = null; //创建Excel工作薄 Workbook work = this.getWorkbook(in, fileName); if (null == work) { throw new Exception("创建Excel工作薄为空!"); } Sheet sheet = null; Row row = null; Cell cell = null; list = new ArrayList<List<Object>>(); if (work.getNumberOfSheets() >= page) { sheet = work.getSheetAt(page - 1); //遍历当前sheet中的所有行 for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) { row = sheet.getRow(j); if(row == null || row.getCell(0) == null || CommonUtils.isEmptyString(row.getCell(0).getStringCellValue())) { break; } //遍历所有的列 List<Object> li = new ArrayList<Object>(); for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) { cell = row.getCell(y); if(cell == null) { return null; } li.add(this.getCellValue(cell)); } list.add(li); } } work.close(); return list; } /** * 描述:根据文件后缀,自适应上传文件的版本 * * @param inStr,fileName * @return * @throws Exception */ public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception { Workbook wb = null; String fileType = fileName.substring(fileName.lastIndexOf(".")); if (EXCEL2003L.equals(fileType)) { wb = new HSSFWorkbook(inStr); //2003- } else if (EXCEL2007U.equals(fileType)) { wb = new XSSFWorkbook(inStr); //2007+ } else { throw new Exception("解析的文件格式有误!"); } return wb; } /** * 描述:根据文件后缀,自适应上传文件的版本 * * @param fileName * @return * @throws Exception */ public Workbook getWorkbook2007(String fileName) throws Exception { return new XSSFWorkbook(fileName); //2007+; } /** * 描述:对表格中数值进行格式化 * * @param cell * @return */ public Object getCellValue(Cell cell) { Object value = null; DecimalFormat df = new DecimalFormat("0"); //格式化number String字符 SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化 DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字 switch (cell.getCellTypeEnum()) { case STRING: value = cell.getRichStringCellValue().getString(); break; case NUMERIC: if ("General".equals(cell.getCellStyle().getDataFormatString())) { value = df.format(cell.getNumericCellValue()); } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) { value = sdf.format(cell.getDateCellValue()); } else { value = df2.format(cell.getNumericCellValue()); } break; case BOOLEAN: value = cell.getBooleanCellValue(); break; case BLANK: value = ""; break; default: break; } return value; } }
java下对excel文件的上传
最新推荐文章于 2024-01-04 15:00:45 发布