public void Fills()
{
var whiteFill = CellFill.Solid(new Color("#fff")); //white fill
var blackFill = new CellFill {
Pattern = FillPattern.Solid, Foreground = Color.Black
}; //make sure you set the foreground color
var wb = new Workbook();
var sheet = wb.Sheets.AddSheet("Chessboard");
var cells = sheet[1, 1, 8, 8];
cells.SetOutsideBorder(new BorderEdge {
Color = Color.Black, Style = BorderStyle.Medium
});
for (var i = 0; i < cells.Range.Width; i++)
{
for (var j = 0; j < cells.Range.Height; j++)
{
cells[j, i].Style.Fill = (i + j) % 2 == 0 ? whiteFill : blackFill;
}
}
var rows = cells.GetColumn(-1);
for (var i = 0; i < rows.Range.Height; i++)
{
rows[i].Value = 8 - i;
rows[i].Style.Alignment.Horizontal = HorizontalAlignment.Center;
rows[i].Style.Alignment.Vertical = VerticalAlignment.Center;
}
var columns = cells.GetRow(8);
for (var i = 0; i < columns.Range.Width; i++)
{
columns[i].Value = new string(new char[] { (Char)('A' + i) });
columns[i].Style.Alignment.Horizontal = HorizontalAlignment.Center;
columns[i].Style.Alignment.Vertical = VerticalAlignment.Center;
}
sheet.DefaultRowHeight = 40; //px
wb.Save("Fills.xlsx");
}
public static void Run()
{
var workbook = Workbook.Load(@"C:\Work\Test.xlsx");
var sheet = workbook.Sheets[0];
sheet["A1"].Value = "Hello";
sheet["A1"].Style.Fill = new CellFill
{
Foreground = new Color(255, 255, 0, 0),
Pattern = FillPattern.Solid
};
sheet[0, 1].Value = "World";
sheet["A3"].Value = "This is a merged cell with border.";
sheet["A3"].Style.Alignment.WrapText = true;
sheet["A3", "B5"].Merge();
sheet["A3", "B5"].SetOutsideBorder(new BorderEdge
{
Style = BorderStyle.Thick,
Color = Color.Black
});
sheet["A6"].Value = "Formatted:";
var b6 = sheet["B6"];
b6.Value = 5;
b6.Style.Format = "#,#.00";
for (var c = 0; c < 5; c++)
{
var bgColor = new Color((byte)(100 + c * 30), 255, (byte)(100 + c * 30));
for (var r = 0; r < 50; r++)
{
sheet[r, c].Style.Fill = CellFill.Solid(bgColor);
}
}
workbook.Save(@"C:\Work\Xlio.xlsx", XlsxFileWriterOptions.AutoFit);
}
private void FillAttemptHistorySheet(Sheet sheet, IRun run)
{
var attemptIdColumn = 0;
var startedColumn = 1;
var endedColumn = 2;
var timeColumn = 3;
var header = sheet.Data.Rows[0];
header[attemptIdColumn].Value = "Attempt ID";
header[attemptIdColumn].Style.Font.Bold = true;
header[attemptIdColumn].Style.Font.Color = Color.White;
header[attemptIdColumn].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[attemptIdColumn].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[attemptIdColumn].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
header[startedColumn].Value = "Started";
header[startedColumn].Style.Font.Bold = true;
header[startedColumn].Style.Font.Color = Color.White;
header[startedColumn].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[startedColumn].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[startedColumn].Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
header[startedColumn].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
header[endedColumn].Value = "Ended";
header[endedColumn].Style.Font.Bold = true;
header[endedColumn].Style.Font.Color = Color.White;
header[endedColumn].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[endedColumn].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[endedColumn].Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
header[endedColumn].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
header[timeColumn].Value = "Time";
header[timeColumn].Style.Font.Bold = true;
header[timeColumn].Style.Font.Color = Color.White;
header[timeColumn].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[timeColumn].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[timeColumn].Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
header[timeColumn].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
var rowIndex = 1;
var bestTime = TimeSpan.MaxValue;
foreach (var attempt in run.AttemptHistory)
{
var row = sheet.Data.Rows[rowIndex];
row[attemptIdColumn].Value = attempt.Index;
row[attemptIdColumn].Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
var startedCell = row[startedColumn];
startedCell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
startedCell.Style.Alignment.Horizontal = HorizontalAlignment.Right;
startedCell.Style.Format = "dd mmm yy hh:mm:ss";
startedCell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
if (attempt.Started.HasValue)
{
startedCell.Value = attempt.Started.Value;
}
var endedCell = row[endedColumn];
endedCell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
endedCell.Style.Alignment.Horizontal = HorizontalAlignment.Right;
endedCell.Style.Format = "dd mmm yy hh:mm:ss";
endedCell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
if (attempt.Ended.HasValue)
{
endedCell.Value = attempt.Ended.Value;
}
var timeCell = row[timeColumn];
timeCell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
var time = attempt.Time.RealTime;
if (time.HasValue)
{
timeCell.Value = time.Value.TotalDays;
if (time.Value < bestTime)
{
bestTime = time.Value;
timeCell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(201, 231, 201)
: new Color(218, 248, 218));
}
}
timeCell.Style.Alignment.Horizontal = HorizontalAlignment.Right;
timeCell.Style.Format = "[HH]:MM:SS.00";
timeCell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
++rowIndex;
}
sheet.AutoFilter = sheet[0, 0, rowIndex - 1, 3];
}
private static void FillSplitTimesSheet(Sheet sheet, IRun run)
{
var header = sheet.Data.Rows[0];
header[0].Value = "Segment";
header[0].Style.Font.Bold = true;
header[0].Style.Font.Color = Color.White;
header[0].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[0].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[0].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
var columnIndex = 1;
foreach (var comparisonName in run.Comparisons.Where(x => x != NoneComparisonGenerator.ComparisonName))
{
var cell = header[columnIndex];
cell.Value = comparisonName;
cell.Style.Font.Bold = true;
cell.Style.Font.Color = Color.White;
cell.Style.Alignment.Horizontal = HorizontalAlignment.Center;
cell.Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
cell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
cell.Style.Fill = CellFill.Solid(new Color(128, 128, 128));
columnIndex++;
}
var lastTime = TimeSpan.Zero;
var rowIndex = 1;
foreach (var segment in run)
{
var row = sheet.Data.Rows[rowIndex];
row[0].Value = segment.Name;
row[0].Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
columnIndex = 1;
foreach (var comparisonName in run.Comparisons.Where(x => x != NoneComparisonGenerator.ComparisonName))
{
var cell = row[columnIndex];
var time = segment.Comparisons[comparisonName].RealTime;
if (time.HasValue)
{
cell.Value = time.Value.TotalDays;
}
cell.Style.Alignment.Horizontal = HorizontalAlignment.Right;
cell.Style.Format = "[HH]:MM:SS.00";
cell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
if (comparisonName == Run.PersonalBestComparisonName && time.HasValue && segment.BestSegmentTime.RealTime == (time.Value - lastTime))
{
cell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(241, 231, 181)
: new Color(255, 245, 198));
}
else
{
cell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
}
if (comparisonName == Run.PersonalBestComparisonName && time.HasValue)
{
lastTime = time.Value;
}
columnIndex++;
}
++rowIndex;
}
sheet.AutoFilter = sheet[0, 0, rowIndex - 1, columnIndex - 1];
}
private static void FillSegmentHistorySheet(Sheet sheet, IRun run)
{
var header = sheet.Data.Rows[0];
header[0].Value = "Attempt ID";
header[0].Style.Font.Bold = true;
header[0].Style.Font.Color = Color.White;
header[0].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[0].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[0].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
var columnIndex = 1;
foreach (var segment in run)
{
var cell = header[columnIndex];
cell.Value = segment.Name;
cell.Style.Font.Bold = true;
cell.Style.Font.Color = Color.White;
cell.Style.Alignment.Horizontal = HorizontalAlignment.Center;
cell.Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
cell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
cell.Style.Fill = CellFill.Solid(new Color(128, 128, 128));
columnIndex++;
}
var rowIndex = 1;
foreach (var attempt in run.AttemptHistory)
{
var row = sheet.Data.Rows[rowIndex];
row[0].Value = attempt.Index;
row[0].Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
columnIndex = 1;
foreach (var segment in run)
{
var cell = row[columnIndex];
var segmentHistoryElement = segment.SegmentHistory.FirstOrDefault(x => x.Index == attempt.Index);
if (segmentHistoryElement != null)
{
var time = segmentHistoryElement.Time.RealTime;
if (time.HasValue)
{
cell.Value = time.Value.TotalDays;
}
}
cell.Style.Alignment.Horizontal = HorizontalAlignment.Right;
cell.Style.Format = "[HH]:MM:SS.00";
cell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
cell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
columnIndex++;
}
++rowIndex;
}
sheet.AutoFilter = sheet[0, 0, rowIndex - 1, columnIndex - 1];
}
private void FillRunHistorySheet(Sheet sheet, IRun run)
{
var header = sheet.Data.Rows[0];
header[0].Value = "Run ID";
header[0].Style.Font.Bold = true;
header[0].Style.Font.Color = Color.White;
header[0].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[0].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[0].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
header[1].Value = "Time";
header[1].Style.Font.Bold = true;
header[1].Style.Font.Color = Color.White;
header[1].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[1].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[1].Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
header[1].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
var rowIndex = 1;
var bestTime = TimeSpan.MaxValue;
foreach (var runHistoryElement in run.RunHistory)
{
var row = sheet.Data.Rows[rowIndex];
row[0].Value = runHistoryElement.Index;
row[0].Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
var cell = row[1];
cell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
var time = runHistoryElement.Time.RealTime;
if (time.HasValue)
{
cell.Value = time.Value.TotalDays;
if (time.Value < bestTime)
{
bestTime = time.Value;
cell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(201, 231, 201)
: new Color(218, 248, 218));
}
}
cell.Style.Alignment.Horizontal = HorizontalAlignment.Right;
cell.Style.Format = "[HH]:MM:SS.00";
cell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
++rowIndex;
}
sheet.AutoFilter = sheet[0, 0, rowIndex - 1, 1];
}
