查看: 2426|回复: 0

[Java语言] java实现excel解析方法

发表于 2018-1-18 08:00:02

解析excel文件

  1. //fileName为文件的名称,path为文件的路径,nameRow为excel中列的名称的起始行数,valueRow为值的起始行数,excel行数是从0开始的
  2. public static ArrayList<Map<String, Object>> readExcelrowKuaiBao(String fileName, String path, int nameRow, int valueRow) throws IOException {
  3. ArrayList<Map<String, Object>> Row = new ArrayList<Map<String, Object>>();
  4. try {
  5. Workbook workBook = null;
  6. try {
  7. System.out.println(path + "/" + fileName);
  8. workBook = new XSSFWorkbook(path + "/" + fileName);
  9. } catch (Exception ex) {
  10. workBook = new HSSFWorkbook(new FileInputStream(path + "/" + fileName));
  11. }
  12. for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
  13. Sheet sheet = workBook.getSheetAt(numSheet);
  14. if (sheet == null) {
  15. continue;
  16. }
  17. Row row0 = sheet.getRow(nameRow);
  18. if (null == row0) {
  19. continue;
  20. }
  21. int colum = row0.getPhysicalNumberOfCells();
  22. List<String> keys = new ArrayList<String>();
  23. for (int i = 0; i < colum; i++) {
  24. Cell cell = row0.getCell(i);
  25. keys.add("column" + i);
  26. }
  27. // 循环行Row
  28. for (int rowNum = valueRow; rowNum <= sheet.getPhysicalNumberOfRows(); rowNum++) {
  29. //是否到达文件末尾
  30. boolean isEnd = true;
  31. Row row = sheet.getRow(rowNum);
  32. if (row == null) {
  33. continue;
  34. }
  35. // 循环列Cell
  36. Map<String, Object> arrCell = new HashMap<String, Object>();
  37. for (int cellNum = 0; cellNum < colum; cellNum++) {
  38. Cell cell = row.getCell(cellNum);
  39. if (cell == null) {
  40. arrCell.put(keys.get(cellNum), " ");
  41. } else {
  42. //System.out.println(getValue(cell));
  43. arrCell.put(keys.get(cellNum), getValue(cell));
  44. }
  45. }
  46. for (Object obj : keys) {//遍历key
  47. isEnd = arrCell.get(obj) != null && !"".equals(arrCell.get(obj).toString()) ? false : true;
  48. if (!isEnd)
  49. break;
  50. }
  51. if (isEnd) {
  52. break;
  53. }
  54. Row.add(arrCell);
  55. }
  56. }
  57. } catch (IOException e) {
  58. System.out.println("e:" + e);
  59. throw e;
  60. }
  61. return Row;
  62. }
  63. private static String getValue(Cell cell) {
  64. String result = new String();
  65. switch (cell.getCellType()) {
  66. case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
  67. System.out.println(cell.getCellStyle().getDataFormat());
  68. if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
  69. SimpleDateFormat sdf = null;
  70. if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
  71. sdf = new SimpleDateFormat("HH:mm");
  72. } else {// 日期
  73. sdf = new SimpleDateFormat("yyyy-MM-dd");
  74. }
  75. Date date = cell.getDateCellValue();
  76. result = sdf.format(date);
  77. } else if (cell.getCellStyle().getDataFormat() == 181) {
  78. SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
  79. double value = cell.getNumericCellValue();
  80. Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
  81. result = sdf.format(date);
  82. } else if (cell.getCellStyle().getDataFormat() == 58) {
  83. // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
  84. SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
  85. double value = cell.getNumericCellValue();
  86. Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
  87. result = sdf.format(date);
  88. } else {
  89. double value = cell.getNumericCellValue();
  90. CellStyle style = cell.getCellStyle();
  91. // DecimalFormat format = new DecimalFormat();
  92. // String temp = style.getDataFormatString();
  93. // // 单元格设置成常规
  94. // if (temp.equals("General")) {
  95. // format.applyPattern("#");
  96. // }
  97. // result = format.format(value);
  98. result = value + "";
  99. }
  100. break;
  101. case HSSFCell.CELL_TYPE_STRING:// String类型
  102. result = cell.getRichStringCellValue().toString();
  103. break;
  104. case HSSFCell.CELL_TYPE_FORMULA:
  105. result = cell.getNumericCellValue() + "";
  106. break;
  107. case HSSFCell.CELL_TYPE_BLANK:
  108. result = "";
  109. default:
  110. result = "";
  111. break;
  112. }
  113. return result;
  114. }
  115. ```excel数据入库
复制代码

//readExcelrowKuaiBao是调用上面的方法
ArrayList> row = this.readExcelrowKuaiBao(fileName, filePath, 3, 4);
for (int i = 0; i < row.size(); i++) {
//获取第0列的值,也就是excel中第一列的值
row.get(i).get("column0").toString();
//获取第1列的值,列数依次累加
row.get(i).get("column1").toString();

}

[code][/code]


回复

使用道具 举报