科研路上的有用的小工具

读取excel文件内容

pom文件

<!-- Apache POI for Excel -->
  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version> <!-- 使用适当的版本 -->
  </dependency>
  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version> <!-- 使用适当的版本 -->
  </dependency>

脚本如下，写的有点乱，不过自己能看懂是什么意思就行，可能就是一次性的脚本，下次拿来改改还能用

  public static void write(String filePath1, String filePath2) throws IOException{
        try (FileInputStream fileIn = new FileInputStream(filePath1)) { // 替换为您的Excel文件路径
            Workbook workbook = new XSSFWorkbook(fileIn);
            HashMap<String, String> cache = getData(filePath1, filePath2);
            // 获取第二个工作表（Sheet2）
            Sheet sheet = workbook.getSheetAt(1); // Sheet2的索引是1
            // 遍历工作表的每一行

            // 获取第一个工作表（Sheet2）
            sheet = workbook.getSheetAt(0); // Sheet2的索引是1
            // 在第E列（第5列）写入数据
            int columnEIndex = 4; // 列的索引，从0开始计数
            int rowIndex = 5; // 从第一行开始，可以根据需要更改行索引
            for (rowIndex = 5; rowIndex < 237; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }
                // 获取指定单元格并设置数据
                Cell cell = row.getCell(columnEIndex);
                if (cell == null) {
                    cell = row.createCell(columnEIndex);
                }

                // 获取第一列的app名称
                Row row1 = sheet.getRow(rowIndex);
                Cell cell1 = row1.getCell(1);
                String name = cell1.getStringCellValue();
                // wechatv8.0.11 格式,使用split获取前面的名字
                if (name.contains("v")) {
                    name = name.split("v")[0];
                } else if (name.contains("V")) {
                    name = name.split("V")[0];
                }

                // 根据app的名称获取下载量,并对数据进行处理
                String value = cache.get(name);
                if (value == null || "".equals(value)) {
                    value = "";
                } else if (value.contains("E")) {
                    value = science2String(value);
                    value = simplifyNumber(Long.parseLong(value));
                } else if (!value.equals("0")) {
                    value = simplifyNumber(Long.parseLong(value));
                }
                System.out.println(name + ":" + value);
//                cellValue = cell.getStringCellValue();
                cell.setCellValue(value);
//                     设置单元格颜色 可设可不设
//                    cell.setCellValue(value);
//                    CellStyle cellStyle = workbook.createCellStyle();
//                    cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
//                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//                    cell.setCellStyle(cellStyle);

            }
            // 保存工作簿到文件
            try (FileOutputStream fileOut = new FileOutputStream(filePath1)) {
                workbook.write(fileOut);
                System.out.println("数据已成功覆盖Excel文件中的单元格！");
            } catch (IOException e) {
                e.printStackTrace();
            }
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static HashMap<String, String> getData(String filePath, String filePath2) throws IOException {
        HashMap<String, String> cache = new HashMap<>();
        try (FileInputStream fileIn = new FileInputStream(filePath)) { // 替换为您的Excel文件路径
            Workbook workbook = new XSSFWorkbook(fileIn);

            // 获取第二个工作表（Sheet2）
            Sheet sheet = workbook.getSheetAt(5); // Sheet2的索引是1
            System.out.println(sheet.getSheetName());
            // 遍历工作表的每一行
            int[] rows = getRowNum(filePath2);
            for (int k = 0; k < rows.length; k += 2) {
                    for (int j = 2; j < rows[k]; j++) {
                        Row row = sheet.getRow(j);
                        // 假设第一列是名称，第二列是下载量
                        Cell nameCell = row.getCell(k);
                        Cell downloadCell = row.getCell(k + 1);

                        // 检查单元格类型，确保是字符串和数值
                        if (nameCell != null &&
                                downloadCell != null) {
                            String name = nameCell.getStringCellValue();
                            String download = "";

                            if (downloadCell.getCellTypeEnum() == CellType.NUMERIC) {
                                download = String.valueOf(downloadCell.getNumericCellValue());
                            }
                            if (downloadCell.getCellTypeEnum() == CellType.STRING) {
                                download = downloadCell.getStringCellValue();
                            }
                            cache.put(name, download);
                            // 打印名称和下载量
                            System.out.println("名称: " + name);
                            System.out.println("下载量: " + download);
                        }
                    }
                }
            }
        return cache;
    }

    /**
     * 将具体数字转换成 K,M,E 为单位的数字
     * @param number
     * @return
     */
    public static String simplifyNumber(long number) {
        if (number < 1_000) return String.valueOf(number);
        if (number < 1_000_000) return String.format("%.1fK+", number / 1_000.0);
        if (number < 1_000_000_000) return String.format("%.1fM+", number / 1_000_000.0);
        if (number < 1_000_000_000_000L) return String.format("%.1fE+", number / 1_000_000_000.0);
        return String.format("%.1fB+", number / 1_000_000_000.0);
    }

    /**
     * 获取每列有多少行有效数据
     *
     * @return
     * @throws IOException
     */
    public static int[] getRowNum(String filePath) throws IOException {
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(new File(filePath));
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        }
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(5); // Assuming you're reading the first sheet

        Iterator<Row> rowIterator = sheet.iterator();
        int[] columnRowCounts = new int[sheet.getRow(0).getLastCellNum()]; // Assuming the first row has the maximum number of columns

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            for (int i = 0; i < columnRowCounts.length; i += 2) {
                Cell cell = row.getCell(i, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                if (cell == null || cell.toString().trim().isEmpty()) {
                    continue;
                }
                columnRowCounts[i]++;
            }
        }
        for (int i = 0; i < columnRowCounts.length; i += 2) {
            System.out.println("Column " + (i + 1) + " has " + columnRowCounts[i] + " rows.");
        }
        workbook.close();
        fis.close();
        return columnRowCounts;
    }

    public static String science2String(String scientificNumber) {
        BigDecimal number = new BigDecimal(scientificNumber);
        return number.toPlainString();
    }