public static IWorkbook WriteMeetToExcel(Meet meet)
{
var workbook = new XSSFWorkbook();
CreateMeetSheet(workbook, meet, meet.MeetEventsBest, "All-Time Best Results", string.Empty);
foreach (var season in meet.MeetEventsSeasonBest.Keys)
{
CreateMeetSheet(workbook, meet, meet.MeetEventsSeasonBest[season], $"Virtual Meet({season})", season);
}
foreach (var team in meet)
{
CreateAthleteProfiles(workbook, $"Athlete Profiles - {team.Name}", team);
}
XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
return(workbook);
}
private static Task <Team> TeamCrawler(string url, Meet meet)
{
return(Task.Run(() =>
{
var htmlDocument = Helpers.GetHtmlDoc(url);
var rosterTable = htmlDocument.DocumentNode.Descendants("table").ToList();
var teamName = htmlDocument.DocumentNode.Descendants("h3")
.Where(node => node.Attributes["class"].Value.Equals("panel-title large-title")).ToList()[0]
.InnerHtml;
Team team = new Team(teamName);
var athletes = rosterTable[url.Contains("/xc/") ? 0 : 1].Descendants("tbody").ToList()[0]
.Descendants("tr")
.ToList();
foreach (var athlete in athletes)
{
var name = athlete.Descendants("td").ToList()[0].Descendants("a").ToList()[0].InnerHtml.MyTrim();
var year = athlete.Descendants("td").ToList()[1].InnerHtml.MyTrim();
Athlete theAthlete = new Athlete(name, year, team);
string athleteUrl = athlete.Descendants("td").ToList()[0].Descendants("a").ToList()[0]
.Attributes["href"].Value
.Insert(0, "https:");
var athleteDoc = Helpers.GetHtmlDoc(athleteUrl);
var seasonHistory = athleteDoc.DocumentNode.Descendants("div")
.Where(node =>
node.Attributes.Contains("id") && node.Attributes["id"].Value.Equals("session-history"))
.ToList();
if (seasonHistory.Count < 1)
{
continue;
}
var seasons = seasonHistory[0].Descendants("h3").ToList();
var divs = seasonHistory[0].ChildNodes.Where(node => node.Name == "div").ToList();
foreach (var div in divs)
{
var tables = div.ChildNodes.Where(node => node.Name == "table").ToList();
foreach (var @event in tables)
{
string eventName = @event.Descendants("span").ToList()[0].InnerHtml.MyTrim();
var rows = @event.Descendants("tr").ToList();
foreach (var row in rows)
{
if (row.Attributes.Contains("class") && row.Attributes["class"].Value == "transfer")
{
continue;
}
var cells = row.Descendants("td").ToList();
int counter = 0;
foreach (var cell in cells)
{
if (counter % 3 == 0 && cell.Descendants("a").ToList().Count > 0)
{
theAthlete.AllMarks.Add(new Mark(new MeetEvent(eventName, meet),
cell.Descendants("a").ToList()[0].InnerHtml.MyTrim(),
seasons[divs.IndexOf(div)].InnerHtml.MyTrim(), theAthlete,
cells[2].InnerHtml.MyTrim()));
}
counter++;
}
}
}
}
theAthlete.GenerateBestMarks();
theAthlete.GenerateSeasonBestMarks();
team.Athletes.Add(theAthlete);
}
return team;
}));
}
private static void CreateMeetSheet(IWorkbook workbook, Meet meet, IEnumerable <MeetEvent> events,
string sheetName, string season)
{
var sheet = workbook.CreateSheet(sheetName);
var boldFont = workbook.CreateFont();
boldFont.FontName = "Calibri";
boldFont.FontHeightInPoints = 11;
boldFont.Boldweight = (short)FontBoldWeight.Bold;
var headerStyle = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.FillForegroundColor = IndexedColors.LightCornflowerBlue.Index;
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.SetFont(boldFont);
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
var rowStyle = (XSSFCellStyle)workbook.CreateCellStyle();
rowStyle.BorderLeft = BorderStyle.Thin;
rowStyle.BorderRight = BorderStyle.Thin;
int rowCount = 0;
var rowHeaderTeams = sheet.CreateRow(rowCount++);
rowHeaderTeams.CreateCell(1).SetCellValue("Team");
rowHeaderTeams.CreateCell(2).SetCellValue("(Hypothetical) Scores");
var headerAddressRange = new CellRangeAddress(rowCount - 1, rowCount - 1, 4, 5);
rowHeaderTeams.CreateCell(4).SetCellValue("DATA MAY BE INACCURATE");
sheet.AddMergedRegion(headerAddressRange);
rowHeaderTeams.Cells.ForEach(cell => cell.CellStyle = headerStyle);
foreach (var team in meet)
{
var teamScoreRow = sheet.CreateRow(rowCount++);
teamScoreRow.CreateCell(1).SetCellValue(team.Name);
var scoreCell = teamScoreRow.CreateCell(2);
scoreCell.SetCellType(CellType.Formula);
scoreCell.SetCellFormula($"SUMIF(B:B, B{rowCount}, F:F)");
teamScoreRow.Cells.ForEach(cell => cell.CellStyle = rowStyle);
}
rowCount++;
foreach (var meetEvent in events)
{
int eventStartGroup = rowCount;
var eventRow = sheet.CreateRow(rowCount++);
eventRow.CreateCell(0).SetCellValue(meetEvent.Name);
CellRangeAddress cellRange = new CellRangeAddress(rowCount - 1, rowCount - 1, 0, 4);
sheet.AddMergedRegion(cellRange);
var headerRow = sheet.CreateRow(rowCount++);
headerRow.CreateCell(0).SetCellValue("#");
headerRow.CreateCell(1).SetCellValue("Team");
headerRow.CreateCell(2).SetCellValue("Athlete");
headerRow.CreateCell(3).SetCellValue("Performance");
headerRow.CreateCell(4).SetCellValue("Date");
eventRow.Cells.ForEach(cell => cell.CellStyle = headerStyle);
headerRow.Cells.ForEach(cell => cell.CellStyle = headerStyle);
foreach (var kvPair in meetEvent.Results)
{
var row = sheet.CreateRow(rowCount++);
int cellCount = 0;
row.CreateCell(cellCount++)
.SetCellValue(meetEvent.Results.Keys.ToList().IndexOf(kvPair.Key) + 1);
row.CreateCell(cellCount++).SetCellValue(meetEvent.Results[kvPair.Key].Athlete.Team.Name);
row.CreateCell(cellCount++).SetCellValue(meetEvent.Results[kvPair.Key].Athlete.Name);
row.CreateCell(cellCount++).SetCellValue(meetEvent.Results[kvPair.Key].Result);
row.CreateCell(cellCount++).SetCellValue(meetEvent.Results[kvPair.Key].Date);
int score = meetEvent.Results[kvPair.Key].Score;
if (score != 0)
{
row.CreateCell(cellCount).SetCellValue(score);
}
row.Cells.ForEach(cell => cell.CellStyle = rowStyle);
for (int i = 0; i < row.Cells.Count; i++)
{
sheet.SetColumnWidth(i, 5000);
}
row.Cells[0].CellStyle = headerStyle;
if (row.Cells.Count > 5)
{
row.Cells[5].CellStyle = headerStyle;
}
}
int eventEndGroup = rowCount;
sheet.GroupRow(eventStartGroup + 1, eventEndGroup);
rowCount++;
}
}
public MeetEvent(string name, Meet meet)
{
Meet = meet;
Name = name;
}