首页 > 其他分享 >MyDream

MyDream

时间：2024-11-21 17:55:44浏览次数：1  
标签：sheet MyDream createCell new import setCellValue row 

package org.lottery.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.lottery.model.Match;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

public class MatchUtils {

    private BigDecimal zero = new BigDecimal(0);

    private String empty = "";

    public void execute(String fileName, String leagueData, String leagueName)  throws IOException {
        List<Match> matchList = selectLeagueMatchData(leagueData, leagueName);
        insertLeagueMatchData(matchList, fileName, leagueName);
        createCellStyle(fileName, leagueName);
    }


    public List<Match> selectLeagueMatchData(String leagueData, String leagueName) throws IOException {
        List<Match> matchList =  new ArrayList<Match>();
        Document document = Jsoup.parse(new File(leagueData), "UTF-8");
        Element next = document.getElementById("next");
        Elements rows = next.select("tbody > tr");
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yy/MM/dd HH:mm");
        for (Element row : rows) {
            Elements columns = row.select("td");

            Match match = new Match();
            match.setMatchDateTime(LocalDateTime.parse(columns.get(2).text(), formatter));
            match.setLeagueName(columns.get(0).text().trim());
            match.setHomeTeam(columns.get(3).select("a").text().trim());
            match.setAwayTeam(columns.get(5).select("a").text().trim());

            match.setInitialConcedeGreater(zero);
            match.setInitialConcede(zero);
            match.setInitialConcedeLess(zero);

            match.setFinalConcedeGreater(new BigDecimal(columns.get(7).text()).setScale(3, RoundingMode.HALF_UP));
            match.setFinalConcede(new BigDecimal(columns.get(8).text()).setScale(2, RoundingMode.HALF_UP));
            match.setFinalConcedeLess(new BigDecimal(columns.get(9).text()).setScale(3, RoundingMode.HALF_UP));

            match.setConcedeOfChange(empty);

            match.setInitialGoalsGreater(zero);
            match.setInitialGoals(zero);
            match.setInitialGoalsLess(zero);

            match.setFinalGoalsGreater(new BigDecimal(columns.get(10).text()).setScale(3, RoundingMode.HALF_UP));
            match.setFinalGoals(new BigDecimal(columns.get(11).text()).setScale(2, RoundingMode.HALF_UP));
            match.setFinalGoalsLess(new BigDecimal(columns.get(12).text()).setScale(3, RoundingMode.HALF_UP));

            match.setGoalsOfChange(empty);

            match.setHalfScore("0:0");
            match.setFullScore("0:0");
            matchList.add(match);
        }
        return  matchList;
    }

    /**
     * 将数据存入excel中
     * @param matchList
     * @param fileName
     * @throws IOException
     */
    public void insertLeagueMatchData(List<Match> matchList, String fileName, String sheetName) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(fileName));
        XSSFSheet sheet = createMySheet(workbook, sheetName);
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm");
        for(Match m : matchList) {
            boolean rowHas = false;
            for (int i = 1; i < sheet.getLastRowNum(); i++) {
                XSSFRow row = sheet.getRow(i);
                LocalDateTime rowTime = LocalDateTime.parse(row.getCell(0).getStringCellValue(), formatter);
                String homeTeam = row.getCell(2).getStringCellValue();
                String awayTeam = row.getCell(3).getStringCellValue();
                if (rowTime.compareTo(m.getMatchDateTime()) == 0 && homeTeam.equals(m.getHomeTeam()) && awayTeam.equals(m.getAwayTeam())) {
                    row.createCell(4).setCellValue(m.getFinalConcedeGreater().toString());
                    row.createCell(5).setCellValue(m.getFinalConcede().toString());
                    row.createCell(6).setCellValue(m.getFinalConcedeLess().toString());

                    row.createCell(7).setCellValue(m.getFinalGoalsGreater().toString());
                    row.createCell(8).setCellValue(m.getFinalGoals().toString());
                    row.createCell(9).setCellValue(m.getFinalGoalsLess().toString());

                    row.getCell(10).setCellValue(m.getHalfScore());
                    row.getCell(11).setCellValue(m.getFullScore());
                    rowHas = true;
                    break;
                }
            }
            if (!rowHas) {
                /** 记录不存在插入新的一条*/
                Row row = sheet.createRow(sheet.getLastRowNum() + 1);
                row.createCell(0).setCellValue(m.getMatchDateTime().format(formatter));
                row.createCell(1).setCellValue(m.getLeagueName());
                row.createCell(2).setCellValue(m.getHomeTeam());
                row.createCell(3).setCellValue(m.getAwayTeam());

                row.createCell(4).setCellValue(m.getFinalConcedeGreater().toString());
                row.createCell(5).setCellValue(m.getFinalConcede().toString());
                row.createCell(6).setCellValue(m.getFinalConcedeLess().toString());

                row.createCell(7).setCellValue(m.getFinalGoalsGreater().toString());
                row.createCell(8).setCellValue(m.getFinalGoals().toString());
                row.createCell(9).setCellValue(m.getFinalGoalsLess().toString());

                row.createCell(10).setCellValue(m.getHalfScore());
                row.createCell(11).setCellValue(m.getFullScore());
            }
        }

        FileOutputStream outputStream = new FileOutputStream(fileName);
        workbook.write(outputStream);
        outputStream.close();
    }


    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("Lucida Console");
        font.setFontHeightInPoints((short) 10);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        for (int i = 0; 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);
            row.getCell(8).setCellStyle(cellStyle);
            row.getCell(9).setCellStyle(cellStyle);
            row.getCell(10).setCellStyle(cellStyle);
            row.getCell(11).setCellStyle(cellStyle);
        }
        FileOutputStream outputStream = new FileOutputStream(fileName);
        workbook.write(outputStream);
        outputStream.close();
    }

    public XSSFSheet createMySheet(XSSFWorkbook workbook, String sheetName) throws IOException  {
        XSSFSheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            sheet = workbook.createSheet(sheetName);
            sheet.setColumnWidth(0, 22*256);
            sheet.setColumnWidth(1, 10*256);
            sheet.setColumnWidth(2, 18*256);
            sheet.setColumnWidth(3, 18*256);
            sheet.setColumnWidth(4, 10*256);
            sheet.setColumnWidth(5, 12*256);
            sheet.setColumnWidth(6, 10*256);
            sheet.setColumnWidth(7, 10*256);
            sheet.setColumnWidth(8, 12*256);
            sheet.setColumnWidth(9, 10*256);
            sheet.setColumnWidth(10, 10*256);
            sheet.setColumnWidth(11, 10*256);

            XSSFRow row = sheet.createRow(0);
            row.createCell(0).setCellValue("时间");
            row.createCell(1).setCellValue("联赛");
            row.createCell(2).setCellValue("主队");
            row.createCell(3).setCellValue("客队");

            row.createCell(4).setCellValue("高");
            row.createCell(5).setCellValue("让球");
            row.createCell(6).setCellValue("低");

            row.createCell(7).setCellValue("高");
            row.createCell(8).setCellValue("大小球");
            row.createCell(9).setCellValue("低");

            row.createCell(10).setCellValue("比分(半)");
            row.createCell(11).setCellValue("比分(全)");
        }
        return sheet;
    }
}


package org.lottery.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.lottery.model.Rank;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.List;

public class RankUtils {

    private String sheetName = "排名";

    public void execute(String fileName, String leagueData, String leagueName) throws IOException {
        List<Rank> ratchList = selectLeagueRankData(leagueData, leagueName);
        insertLeagueRankData(ratchList, fileName, sheetName);
        createCellStyle(fileName, sheetName);
    }


    /**
     * @return
     * @throws IOException
     */
    public List<Rank> selectLeagueRankData(String leagueData, String leagueName) throws IOException {
        List<Rank> ratchList =  new ArrayList<Rank>();
        Document document = Jsoup.parse(new File(leagueData), "UTF-8");
        Elements tbody = document.getElementsByClass("teams_all");
        Elements rows = tbody.get(0).select("tr");
        for (Element row : rows) {
            Rank rank = new Rank();
            Elements columns = row.select("td");
            rank.setLeagueNo(Integer.valueOf(columns.get(1).text()));
            rank.setLeagueMatch(leagueName);
            rank.setTeamName(columns.get(3).select("a").text());
            rank.setTotal(new BigDecimal(columns.get(4).text()));
            rank.setWin(new BigDecimal(columns.get(5).text()));
            rank.setDraw(new BigDecimal(columns.get(6).text()));
            rank.setLost(new BigDecimal(columns.get(7).text()));

            BigDecimal total = new BigDecimal(columns.get(4).text());
            BigDecimal ga = new BigDecimal(columns.get(8).text().split(":")[0].trim());
            BigDecimal gs = new BigDecimal(columns.get(8).text().split(":")[1].trim());
            BigDecimal avgGa = ga.divide(total, 2, RoundingMode.HALF_UP);
            BigDecimal avgGs = gs.divide(total, 2, RoundingMode.HALF_UP);
            rank.setGa(ga);
            rank.setGs(gs);
            rank.setAvgGa(avgGa);
            rank.setAvgGs(avgGs);
            ratchList .add(rank);
        }
        return  ratchList;
    }


    /**
     * 将数据存入excel中
     * @param rankList
     * @param fileName
     * @throws IOException
     */
    public void insertLeagueRankData(List<Rank> rankList, String fileName, String sheetName) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(fileName));
        XSSFSheet sheet = createMySheet(workbook, sheetName);
        for(Rank r : rankList) {
            /** 记录不存在插入新的一条*/
            Row row = sheet.createRow(sheet.getLastRowNum() + 1);
            row.createCell(0).setCellValue(r.getLeagueNo());
            row.createCell(1).setCellValue(r.getTeamName());
            row.createCell(2).setCellValue(r.getTotal().toString());
            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());
            row.createCell(8).setCellValue(r.getAvgGa().toString());
            row.createCell(9).setCellValue(r.getAvgGs().toString());
        }
        FileOutputStream outputStream = new FileOutputStream(fileName);
        workbook.write(outputStream);
        outputStream.close();
    }

    public XSSFSheet createMySheet(XSSFWorkbook workbook, String sheetName) throws IOException  {
        XSSFSheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            sheet = workbook.createSheet(sheetName);
            sheet.setColumnWidth(0, 10*256);
            sheet.setColumnWidth(1, 18*256);
            sheet.setColumnWidth(2, 10*256);
            sheet.setColumnWidth(3, 10*256);
            sheet.setColumnWidth(4, 10*256);
            sheet.setColumnWidth(5, 10*256);
            sheet.setColumnWidth(6, 12*256);
            sheet.setColumnWidth(7, 12*256);
            sheet.setColumnWidth(8, 12*256);
            sheet.setColumnWidth(9, 12*256);

            XSSFRow row = sheet.createRow(0);
            row.createCell(0).setCellValue("排名");
            row.createCell(1).setCellValue("球队");
            row.createCell(2).setCellValue("赛");
            row.createCell(3).setCellValue("胜");
            row.createCell(4).setCellValue("平");
            row.createCell(5).setCellValue("负");
            row.createCell(6).setCellValue("进球");
            row.createCell(7).setCellValue("失球");
            row.createCell(8).setCellValue("场均进球");
            row.createCell(9).setCellValue("场均失球");
        }
        return sheet;
    }

    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("Lucida Console");
        font.setFontHeightInPoints((short) 10);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        for (int i = 0; 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);
            row.getCell(8).setCellStyle(cellStyle);
            row.getCell(9).setCellStyle(cellStyle);
        }
        FileOutputStream outputStream = new FileOutputStream(fileName);
        workbook.write(outputStream);
        outputStream.close();
    }
}



标签：sheet,MyDream,createCell,new,import,setCellValue,row
From： https://www.cnblogs.com/michaelShao/p/18561248

相关文章

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99