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();
}
}
