web开发数据导入Excel&CSV

1、Excel文件导入

1-1、gav引入

  <dependency>
     <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
  </dependency>
  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml-schemas</artifactId>
      <version>3.17</version>
  </dependency>

1-2、java代码解析

 public static final String[] RE_STR = {"(1", "(2", "(3", "(4", "(5", "(6", "(7", "(8", "(9", "000)", "00)", "0)", "1)", "2)", "3)", "4)", "5)", "6)", "7)", "8)", "9)", ")"};

    public static List readExcel(String filePath, Class object) throws IOException {
        List dataList = new ArrayList();
        try (XSSFWorkbook xssfWorkbook = new XSSFWorkbook(filePath);) {
            int numberSheets = xssfWorkbook.getNumberOfSheets();
            String[] cellsNames = null;
            for (int i = 0; i < numberSheets; i++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(i);
                for (int row = 0; row <= xssfSheet.getLastRowNum(); row++) {
                    XSSFRow xssfRow = xssfSheet.getRow(row);
                    if (row == 0 && cellsNames != null) {
                        continue;
                    }
                    if (row == 0) {
                        cellsNames = new String[xssfRow.getLastCellNum() + 1];
                        for (int cellnum = 0; cellnum < xssfRow.getLastCellNum(); cellnum++) {
                            XSSFCell xssfCell = xssfRow.getCell(cellnum);
                            cellsNames[cellnum] = getColumnName(xssfCell.getStringCellValue());
                        }
                        continue;
                    }
                    Map<String, Object> dataMap = new HashMap<>();
                    for (int cellnum = 0; cellnum < xssfRow.getLastCellNum(); cellnum++) {
                        XSSFCell xssfCell = xssfRow.getCell(cellnum);
                        try {
                            dataMap.put(cellsNames[cellnum], xssfCell.getStringCellValue());
                        } catch (IllegalStateException e) {
                            dataMap.put(cellsNames[cellnum], xssfCell.getNumericCellValue());
                        }
                    }
                    dataList.add(dataMap);
                }
            }
        }
        return JSONObject.parseArray(JSON.toJSONString(dataList),object);
    }

2、CSV文件导入

1-1、gav引入

<dependency>
    <groupId>com.opencsv</groupId>
    <artifactId>opencsv</artifactId>
    <version>5.4</version>
</dependency>

2-2、java代码解析

 public static final String[] RE_STR = {"(1", "(2", "(3", "(4", "(5", "(6", "(7", "(8", "(9", "000)", "00)", "0)", "1)", "2)", "3)", "4)", "5)", "6)", "7)", "8)", "9)", ")"};
private static List readExcel(String filePath, Class object) throws IOException {
        List dataList = new ArrayList();
        try (InputStream inputStream = new FileInputStream(new File(filePath));
             BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream));) {
            CSVReader csvReader = new CSVReaderBuilder(bufferedReader).build();
            Iterator<String[]> iterator = csvReader.iterator();
            int row = 0;
            String[] cellsNames = null;
            while (iterator.hasNext()) {
                String[] dataCellValues = iterator.next();
                if (row == 0 && cellsNames == null) {
                    cellsNames = getCellsNames(dataCellValues);
                    row++;
                    continue;
                }
                Map<String, Object> dataMap = new HashMap<>();
                for (int cellnum = 0; cellnum < dataCellValues.length; cellnum++) {
                    dataMap.put(cellsNames[cellnum], dataCellValues[cellnum]);
                }
                dataList.add(dataMap);
                row++;
            }
        }
        return JSONObject.parseArray(JSON.toJSONString(dataList),object);
    }

    private static String[] getCellsNames(String[] dataCellValues) {
        String[] cellsNames = new String[dataCellValues.length];
        for (int cellnum = 0; cellnum < dataCellValues.length; cellnum++) {
            cellsNames[cellnum] = getColumnName(dataCellValues[cellnum]);
        }
        return cellsNames;
    }

    private static String getColumnName(String columnName) {
        String name = columnName.toLowerCase().replace(" ", "_");
        for (String str : RE_STR) {
            name = name.replace(str, "");
        }
        return name;
    }