protected void CreateCompetitorListWorksheet(NetOffice.ExcelApi.Worksheet worksheet)
{
var competitors = _reporter.GetCompetitors()
.OrderBy(x => x.LastName);
var row = 1;
worksheet.Name = "Competitor List";
PrintWorksheetTitle("Competitor List", worksheet, row++);
var startRow = row;
PrintTableColumnNames(worksheet, Competitor.TableFullColumnNames(), row++);
foreach (var competitor in competitors)
{
PrintTableColumnData(worksheet, competitor.TableFullRowData(), row++);
}
var endRow = row - 1;
string firstCol = ExcelColumnFromNumber(1);
string lastCol = ExcelColumnFromNumber(Competitor.TableFullColumnNames().Length);
worksheet.Columns[firstCol + ":" + lastCol].AutoFit();
var tableRange = worksheet.Range(
worksheet.Cells[startRow, firstCol],
worksheet.Cells[endRow, lastCol]);
var table = worksheet.ListObjects.Add(
NetOffice.ExcelApi.Enums.XlListObjectSourceType.xlSrcRange,
tableRange,
Type.Missing,
NetOffice.ExcelApi.Enums.XlYesNoGuess.xlYes,
Type.Missing);
table.Name = "CompetitorList";
}
protected void PrintGroupHeader(string groupTitle, NetOffice.ExcelApi.Worksheet worksheet, int row)
{
worksheet.Cells[row, "A"].Value = groupTitle;
worksheet.Cells[row, "A"].Font.Size = GroupFontSize;
worksheet.Cells[row, "A"].Interior.Color = ColorToDouble(Colors.LightGray);
worksheet.Range(worksheet.Cells[row, "A"], worksheet.Cells[row, "D"]).Merge();
}
protected void PrintWorksheetTitle(string title, NetOffice.ExcelApi.Worksheet worksheet, int row)
{
worksheet.Cells[row,"A"].Value = title;
worksheet.Cells[row,"A"].Font.Size = HeaderFontSize;
worksheet.Range(worksheet.Cells[row, "A"], worksheet.Cells[row, "F"]).Merge();
}
private static NetOffice.ExcelApi.Range PutSampleData(NetOffice.ExcelApi.Worksheet workSheet)
{
workSheet.Cells[2, 2].Value = "Datum";
workSheet.Cells[3, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[4, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[5, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[6, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[2, 3].Value = "Columns1";
workSheet.Cells[3, 3].Value = 25;
workSheet.Cells[4, 3].Value = 33;
workSheet.Cells[5, 3].Value = 30;
workSheet.Cells[6, 3].Value = 22;
workSheet.Cells[2, 4].Value = "Column2";
workSheet.Cells[3, 4].Value = 25;
workSheet.Cells[4, 4].Value = 33;
workSheet.Cells[5, 4].Value = 30;
workSheet.Cells[6, 4].Value = 22;
workSheet.Cells[2, 5].Value = "Column3";
workSheet.Cells[3, 5].Value = 25;
workSheet.Cells[4, 5].Value = 33;
workSheet.Cells[5, 5].Value = 30;
workSheet.Cells[6, 5].Value = 22;
return workSheet.Range("$B2:$E6");
}