其他

package tool;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;

public class ExcelTools {
    private String filePath =  "C:\\Users\\mengchen.shao\\Desktop\\data.xlsx";

    public void clearGame() throws IOException {
        String sheetName = "比赛";
        /**创建Excel*/
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(filePath));
        /**删除sheet*/
        workbook.removeSheetAt(workbook.getSheetIndex(sheetName));
        FileOutputStream out = new FileOutputStream(filePath);
        workbook.write(out);
        out.close();
    }


    public void initGame(List<HashMap<String, Object>> games) throws IOException {
        String sheetName = "比赛";
        String headers[] = new String[]{"时间","赛事","主队","客队","主队 让球 客队","高于 大小球 低于","高于 角球 低于"};
        String keys[] = new String[]{"gameTime", "gameName","homeTeam","awayTeam","rang","goal","corner"};
        /**创建Excel*/
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(filePath));
        /**创建sheet*/
        XSSFSheet sheet = workbook.getSheet(sheetName);
        if(sheet == null) {
            sheet = createSheet(workbook, sheetName);
            /**创建header*/
            XSSFRow header = sheet.createRow(0);
            for(int i =0; i < headers.length; i++) {
                /**创建cell*/
                createCell(workbook, header.createCell(i), headers[i], CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
            }
            /**创建row*/
            for(int i =0; i < games.size(); i++) {
                XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
                /**创建cell*/
                for (int n = 0; n < keys.length; n++) {
                    if (n == 2 || n == 3) {
                        createCell(workbook, row.createCell(n), games.get(i).get(keys[n]).toString(), CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER);
                    } else {
                        createCell(workbook, row.createCell(n), games.get(i).get(keys[n]).toString(), CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                    }
                }
            }
        } else {
            for(int i = 1; i < sheet.getLastRowNum(); i++){
                /**获得既存row*/
                XSSFRow row = sheet.getRow(i);
                for(HashMap<String, Object> map : games) {
                    String homeTeam = row.getCell(2).getStringCellValue();
                    String awayTeam = row.getCell(3).getStringCellValue();
                    if (homeTeam.equals(map.get("homeTeam").toString()) && awayTeam.equals(map.get("awayTeam").toString())) {
                        createCell(workbook, row.getCell(4), map.get("rang").toString(), CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                        createCell(workbook, row.getCell(5), map.get("goal").toString(), CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                        createCell(workbook, row.getCell(6), map.get("corner").toString(),CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                    }
                }
            }
        }
        /** 创建写入流*/
        FileOutputStream out = new FileOutputStream(filePath);
        workbook.write(out);
        out.close();
    }

    public void clearRank() throws IOException {
        String sheetName = "排名";
        /**创建Excel*/
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(filePath));
        /**删除sheet*/
        workbook.removeSheetAt(workbook.getSheetIndex(sheetName));
        FileOutputStream out = new FileOutputStream(filePath);
        workbook.write(out);
        out.close();
    }


    public void initRank(List<HashMap<String, Object>> ranks) throws IOException {
        String sheetName = "排名";
        //String headers[] = new String[]{"排名","赛事","球队","胜平负","进球","失球","场均进球","场均失球",};
        //String keys[] = new String[]{"rank", "gameName","teamName","WDL","GS","GA","avgGS","avgGA"};
        String headers[] = new String[]{"时间","赛事","主队","客队","主队 让球 客队","高于 角球 低于","高于 角球 低于"};
        String keys[] = new String[]{"gameTime", "gameName","homeTeam","awayTeam","rang","goal","corner"};
        /**创建Excel*/
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(filePath));
        /**创建sheet*/
        XSSFSheet sheet = createSheet(workbook, sheetName);
        /**创建header*/
        XSSFRow header = sheet.createRow(0);
        for(int i =0; i < headers.length; i++) {
            /**创建cell*/
            createCell(workbook, header.createCell(i), headers[i], CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
        }
        /**创建row*/
        for(int i =0; i < ranks.size(); i++) {
            XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
            /**创建cell*/
            for (int n = 0; n < keys.length; n++) {
                if (n == 2) {
                    createCell(workbook, row.createCell(n), ranks.get(i).get(keys[n]).toString(), CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER);
                } else {
                    createCell(workbook, row.createCell(n), ranks.get(i).get(keys[n]).toString(), CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                }
            }
        }
        /** 创建写入流*/
        FileOutputStream out = new FileOutputStream(filePath);
        workbook.write(out);
        out.close();
    }


    public void updateTeam(List<HashMap<String, Object>> teams) throws IOException {
        String headers[] = new String[]{"时间","赛事","主队","客队","主队 让球 客队","高于 大小球 低于","高于 角球 低于", "比分(半)","比分(半)","角球(半)","角球(半)"};
        String keys[] = new String[]{"gameTime","gameName","homeTeam","awayTeam","rang","goal","corner","halfScore","fullScore","halfCorner","fullCorner"};
        /**创建Excel*/
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(filePath));
        for(HashMap<String, Object> map : teams) {
            String homeSheetName = map.get("homeTeam").toString();
            String awaySheetName = map.get("awayTeam").toString();
            /**创建sheet*/
            XSSFSheet homeSheet = workbook.getSheet(homeSheetName);
            XSSFSheet awaySheet = workbook.getSheet(awaySheetName);
            /**主队sheet*/
            if(homeSheet == null) {
                homeSheet = createSheet(workbook, homeSheetName);
                /**创建header*/
                XSSFRow header = homeSheet.createRow(0);
                for(int i =0; i < headers.length; i++) {
                    /**创建cell*/
                    createCell(workbook, header.createCell(i), headers[i], CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                }
                XSSFRow row = homeSheet.createRow(homeSheet.getLastRowNum() + 1);
                /**创建cell*/
                for (int n = 0; n < keys.length; n++) {
                    if (n == 2 || n == 3) {
                        createCell(workbook, row.createCell(n), map.get(keys[n]).toString(), CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER);
                    } else {
                        createCell(workbook, row.createCell(n), map.get(keys[n]).toString(), CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                    }
                }
            } else {
                int rowNum = homeSheet.getLastRowNum();
                for(int i = 0; i < rowNum; i++){
                    /**获得既存row*/
                    XSSFRow row = homeSheet.getRow(i + 1);
                    String homeTeam = row.getCell(2).getStringCellValue();
                    String awayTeam = row.getCell(3).getStringCellValue();
                    if (homeTeam.equals(map.get("homeTeam").toString()) && awayTeam.equals(map.get("awayTeam").toString())) {
                        createCell(workbook, row.getCell(7), map.get("halfScore").toString(), CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                        createCell(workbook, row.getCell(8), map.get("fullScore").toString(), CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                        createCell(workbook, row.getCell(9), map.get("halfCorner").toString(),CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                        createCell(workbook, row.getCell(10), map.get("fullCorner").toString(),CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                    }
                }
            }
            /**客队sheet*/
            if(awaySheet == null) {
                awaySheet = createSheet(workbook, awaySheetName);
                /**创建header*/
                XSSFRow header = awaySheet.createRow(0);
                for(int i =0; i < headers.length; i++) {
                    /**创建cell*/
                    createCell(workbook, header.createCell(i), headers[i], CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                }
                XSSFRow row = awaySheet.createRow(awaySheet.getLastRowNum() + 1);
                /**创建cell*/
                for (int n = 0; n < keys.length; n++) {
                    if (n == 2 || n == 3) {
                        createCell(workbook, row.createCell(n), map.get(keys[n]).toString(), CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER);
                    } else {
                        createCell(workbook, row.createCell(n), map.get(keys[n]).toString(), CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                    }
                }
            } else {
                int rowNum = awaySheet.getLastRowNum();
                for(int i = 0; i < rowNum; i++){
                    /**获得既存row*/
                    XSSFRow row = awaySheet.getRow(i + 1);
                    String homeTeam = row.getCell(2).getStringCellValue();
                    String awayTeam = row.getCell(3).getStringCellValue();
                    if (homeTeam.equals(map.get("homeTeam").toString()) && awayTeam.equals(map.get("awayTeam").toString())) {
                        createCell(workbook, row.getCell(7), map.get("halfScore").toString(), CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                        createCell(workbook, row.getCell(8), map.get("fullScore").toString(), CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                        createCell(workbook, row.getCell(9), map.get("halfCorner").toString(),CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                        createCell(workbook, row.getCell(10), map.get("fullCorner").toString(),CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
                    }
                }
            }
        }
        /** 创建写入流*/
        FileOutputStream out = new FileOutputStream(filePath);
        workbook.write(out);
        out.close();
    }




    private XSSFSheet createSheet(XSSFWorkbook workbook, String sheetName){
        XSSFSheet sheet = workbook.createSheet(sheetName);
        sheet.setColumnWidth(0, 18*256);
        sheet.setColumnWidth(1, 14*256);
        sheet.setColumnWidth(2, 18*256);
        sheet.setColumnWidth(3, 18*256);
        sheet.setColumnWidth(4, 24*256);
        sheet.setColumnWidth(5, 24*256);
        sheet.setColumnWidth(6, 24*256);
        sheet.setColumnWidth(7, 14*256);
        sheet.setColumnWidth(8, 14*256);
        sheet.setColumnWidth(9, 14*256);
        sheet.setColumnWidth(10, 14*256);
        return sheet;
    }

    private void createCell(XSSFWorkbook workbook, XSSFCell cell, String value, short align, short vertical) {
        //设置字体
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName("Consolas");
        //设置字体大小
        font.setFontHeightInPoints((short)11);
        //选择需要用到的字体格式
        style.setFont(font);
        //水平对齐
        style.setAlignment(align);
        //垂直对齐
        style.setVerticalAlignment(vertical);
        //列头添加背景色
        if (cell.getRowIndex() == 0) {
            style.setFillForegroundColor(IndexedColors.LIME.getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
            style.setBorderBottom(CellStyle.BORDER_THIN);
            style.setBorderLeft(CellStyle.BORDER_THIN);
            style.setBorderRight(CellStyle.BORDER_THIN);
            style.setBorderTop(CellStyle.BORDER_THIN);
        }
        cell.setCellStyle(style);
        cell.setCellValue(value);
    }
}