//TODO: move to tests
private static void BoolTest()
{
var f = new Workbook();
var sheet = new Sheet("1");
f.Sheets.AddSheet(sheet);
sheet[0, 0].Value = true;
f.Save("bool.xlsx");
f = Workbook.ReadFile("bool.xlsx");
if (!(f.Sheets[0][0, 0].Value is bool))
throw new InvalidOperationException();
}
public static void SheetManipulation()
{
var workbook = new Workbook();
workbook.DefaultFont = new CellFont { Name = "Tahoma", Size = 10 };
var sheet1 = workbook.Sheets.AddSheet("Sheet 1");
var sheet2 = new Sheet("Sheet2");
workbook.Sheets.AddSheet(sheet2);
workbook.Sheets.RemoveSheet(0);
workbook.Sheets.RemoveSheet("Sheet2");
var sheet3 = workbook.Sheets.AddSheet("Sheet 3");
sheet3.DefaultRowHeight = 10;
sheet3.Page.Orientation = PageOrientation.Landscape;
sheet3.ShowGridLines = true;
workbook.Save("SheetManipulation.xlsx");
}
void ProcessAjaxRequest(HttpContext context)
{
String msg = null;
if (context.Request.QueryString["type"] == "xlsx")
{
try
{
var wb = new Workbook();
var sheet = new Sheet("Exported");
sheet[0, 0].Value = "First Name";
sheet[0, 1].Value = "Last Name";
sheet[0, 2].Value = "Company Name";
for (var i = 0; i < crud.Count; i++)
{
sheet[i + 1, 0].Value = crud.Records[i].FirstName;
sheet[i + 1, 1].Value = crud.Records[i].LastName;
sheet[i + 1, 2].Value = crud.Records[i].CompanyName;
}
wb.Sheets.AddSheet(sheet);
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.Buffer = true;
context.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xlsx", DateTime.Now.Ticks));
wb.SaveToStream(context.Response.OutputStream, XlsxFileWriterOptions.AutoFit);
return;
}
catch (Exception ex)
{
msg = ex.ToString();
}
}
context.Response.Clear();
context.Response.ContentType = "text/plain";
context.Response.Output.Write(msg ?? "Invalid params.");
}
public static void WriteToStream(Report report, Theme theme, Stream outputStream)
{
var ef = new Workbook();
var ws = new Sheet("Report");
ef.Sheets.AddSheet(ws);
if (report.MergedCells != null)
{
foreach (var m in report.MergedCells)
ws[m.Row1, m.Col1, m.Row2, m.Col2].Merge();
}
if (report.Cells != null)
{
foreach (var cell in report.Cells)
{
var eStyle = theme.GetCellStyle(cell.CellStyleIndex);
CellData c = null;
object v = cell.Value;
if (eStyle != null)
{
if (eStyle.BorderStyle != null)
{
c = ws.Cells[cell.Row, cell.Column];
if (eStyle.BorderStyle.Left != null)
c.Style.Border.Left = eStyle.BorderStyle.Left.ToBorderEdge();
if (eStyle.BorderStyle.Right != null)
c.Style.Border.Right = eStyle.BorderStyle.Right.ToBorderEdge();
if (eStyle.BorderStyle.Top != null)
c.Style.Border.Top = eStyle.BorderStyle.Top.ToBorderEdge();
if (eStyle.BorderStyle.Bottom != null)
c.Style.Border.Bottom = eStyle.BorderStyle.Bottom.ToBorderEdge();
if (c.IsMerged && c.MergedRange.Cell1.Col == cell.Column && c.MergedRange.Cell1.Row == cell.Row)
{
for (var col = c.MergedRange.Cell1.Col + 1; col <= c.MergedRange.Cell2.Col; col++)
{
if (eStyle.BorderStyle.Top != null)
ws.Cells[cell.Row, col].Style.Border.Top = c.Style.Border.Top;
if (eStyle.BorderStyle.Bottom != null)
ws.Cells[c.MergedRange.Cell2.Row, col].Style.Border.Bottom = c.Style.Border.Bottom;
}
for (var row = c.MergedRange.Cell1.Row + 1; row <= c.MergedRange.Cell2.Row; row++)
{
if (eStyle.BorderStyle.Left != null)
ws.Cells[row, cell.Column].Style.Border.Left = c.Style.Border.Left;
if (eStyle.BorderStyle.Right != null)
ws.Cells[row, c.MergedRange.Cell2.Col].Style.Border.Right = c.Style.Border.Right;
}
}
}
if (eStyle.FontStyle != null)
{
c = c ?? ws.Cells[cell.Row, cell.Column];
if (!String.IsNullOrEmpty(eStyle.FontStyle.FontName))
c.Style.Font.Name = eStyle.FontStyle.FontName;
if (eStyle.FontStyle.FontSize != 0)
c.Style.Font.Size = eStyle.FontStyle.FontSize;
if (eStyle.FontStyle.FontColor != null)
c.Style.Font.Color = eStyle.FontStyle.FontColor.ToColor();
c.Style.Font.Bold = eStyle.FontStyle.Bold;
c.Style.Font.Italic = eStyle.FontStyle.Italic;
if (eStyle.FontStyle.Underline)
c.Style.Font.Underline = FontUnderline.Single;
}
String numberFormat;
if (cell.Format != null)
if (GetNumberFormat(cell.Format, out numberFormat))
c.Style.Format = numberFormat;
else
v = cell.FormattedValue;
if (eStyle.BackgroundColor != null)
c.Style.Fill = new CellFill { Foreground = eStyle.BackgroundColor.ToColor(), Pattern = FillPattern.Solid };
}
ws.Cells[cell.Row, cell.Column].Style.Alignment.HAlign = GetAlignment(cell.Alignment);
ws.Cells[cell.Row, cell.Column].Value = v;
}
}
//if (report.Columns.Count > 0)
//{
// int colIndex = 0;
// foreach (var col in report.Columns)
// {
// if (col.Width.HasValue)
// ws.Columns[colIndex].Width = (int)(col.Width.Value * 256);
// colIndex++;
// }
//}
//if (report.Rows.Count > 0)
//{
// int rowIndex = 0;
// foreach (var row in report.Rows)
// {
// if (row.Height.HasValue)
// ws.Rows[rowIndex].Height = (int)(row.Height.Value * 20);
// rowIndex++;
// }
//}
ef.SaveToStream(outputStream, 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];
}
public SheetRange(Sheet sheet, Range range)
{
Sheet = sheet;
Range = range;
}