Java学习之apache poi操作Excel文件

创建Java项目，然后加入Maven依赖如下：

读取指定的Excel文件

写入既存的Excel文件

 创建新的Excel，添加文件内容

public void insertMatch(List<Match> matchList, String fileName) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(fileName));
    XSSFSheet sheet = workbook.getSheet("英超");
    for(Match m : matchList) {
        boolean rowHas = false;
        for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
            Row row = sheet.getRow(i);
            LocalDateTime rowTime = LocalDateTime.parse(row.getCell(0).getStringCellValue());
            String homeTeam = row.getCell(2).getStringCellValue();
            String awayTeam = row.getCell(3).getStringCellValue();
            if (rowTime.compareTo(m.getTime()) == 0 && homeTeam.equals(m.getHomeTeam()) && awayTeam.equals(m.getAwayTeam())) {
                row.createCell(10).setCellValue(m.getHalfScore());
                row.createCell(11).setCellValue(m.getFullScore());
                rowHas = true;
                break;
            }
        }
        if (!rowHas) {
            /** 记录不存在插入新的一条*/
            Row row = sheet.createRow(sheet.getLastRowNum() + 1);
            row.createCell(0).setCellValue(m.getTime().toString());
            row.createCell(1).setCellValue(m.getMatch());
            row.createCell(2).setCellValue(m.getHomeTeam());
            row.createCell(3).setCellValue(m.getAwayTeam());
            row.createCell(4).setCellValue(m.getConcedeGreater().toString());
            row.createCell(5).setCellValue(m.getConcede().toString());
            row.createCell(6).setCellValue(m.getConcedeLess().toString());
            row.createCell(7).setCellValue(m.getGoalsGreater().toString());
            row.createCell(8).setCellValue(m.getGoals().toString());
            row.createCell(9).setCellValue(m.getGoalsLess().toString());
            row.createCell(10).setCellValue(m.getHalfScore());
            row.createCell(11).setCellValue(m.getFullScore());
        }
    }
    FileOutputStream outputStream = new FileOutputStream(fileName);
    workbook.write(outputStream);
    outputStream.close();
}

/**
 * 将数据存入excel中
 * @param rankList
 * @param fileName
 * @throws IOException
 */
public void insertRank(List<Rank> rankList, String fileName, String sheetName) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(fileName));
    XSSFSheet sheet = workbook.createSheet(sheetName);
    sheet.setColumnWidth(0, 10*256);
    sheet.setColumnWidth(1, 18*256);
    sheet.setColumnWidth(2, 10*256);
    sheet.setColumnWidth(3, 12*256);
    sheet.setColumnWidth(4, 12*256);
    sheet.setColumnWidth(5, 12*256);
    sheet.setColumnWidth(6, 12*256);
    sheet.setColumnWidth(7, 12*256);
    for(Rank r : rankList) {
        /** 记录不存在插入新的一条*/
        Row row = sheet.createRow(sheet.getLastRowNum() + 1);t
        row.createCell(0).setCellValue(r.getRank().toString());
        row.createCell(1).setCellValue(r.getTeamName());
        row.createCell(2).setCellValue(r.getLeagueMatch());
        row.createCell(3).setCellValue(r.getWin().toString());
        row.createCell(4).setCellValue(r.getDraw().toString());
        row.createCell(5).setCellValue(r.getLost().toString());
        row.createCell(6).setCellValue(r.getGa().toString());
        row.createCell(7).setCellValue(r.getGs().toString());
    }
    FileOutputStream outputStream = new FileOutputStream(fileName);
    workbook.write(outputStream);
    outputStream.close();
    createCellStyle(fileName, "英超");
}


public void createCellStyle(String fileName, String sheetName) throws IOException {
    Workbook workbook = new XSSFWorkbook(new FileInputStream(fileName));
    Sheet sheet = workbook.getSheet(sheetName);
    Font font = workbook.createFont();
    font.setFontName("Consolas");
    // font.setFontHeightInPoints((short) 12);
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setFont(font);
    for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
        Row row = sheet.getRow(i);
        row.getCell(0).setCellStyle(cellStyle);
        row.getCell(1).setCellStyle(cellStyle);
        row.getCell(2).setCellStyle(cellStyle);
        row.getCell(3).setCellStyle(cellStyle);
        row.getCell(4).setCellStyle(cellStyle);
        row.getCell(5).setCellStyle(cellStyle);
        row.getCell(6).setCellStyle(cellStyle);
        row.getCell(7).setCellStyle(cellStyle);
    }
    FileOutputStream outputStream = new FileOutputStream(fileName);
    workbook.write(outputStream);
    outputStream.close();
}