public void BlankSheetTest()
{
var wb = new Workbook();
var sheet = wb.Sheets.AddSheet(new Sheet("Sheet 1"));
sheet.ShowGridLines = true;
using (var f = File.Open("Blank.xlsx", FileMode.Create))
wb.SaveToStream(f);
}
public void Save(IRun run, Stream stream)
{
var workbook = new Workbook();
var splitTimesSheet = workbook.Sheets.AddSheet("Splits");
var attemptHistorySheet = workbook.Sheets.AddSheet("Attempt History");
var segmentHistorySheet = workbook.Sheets.AddSheet("Segment History");
FillSplitTimesSheet(splitTimesSheet, run);
FillAttemptHistorySheet(attemptHistorySheet, run);
FillSegmentHistorySheet(segmentHistorySheet, run);
workbook.SaveToStream(stream, Codaxy.Xlio.IO.XlsxFileWriterOptions.AutoFit);
}
public void DefaultFont()
{
var wb = new Workbook();
wb.DefaultFont = new CellFont { Name = "Arial", Size = 20 };
var sheet1 = wb.Sheets.AddSheet(new Sheet("Sheet 1") { ShowGridLines = true });
sheet1["A1"].Value = "Arial 20";
sheet1["A2"].Value = "Bold Arial 20";
sheet1["A2"].Style.Font.Bold = true;
using (var f = File.Open("DefaultFont.xlsx", FileMode.Create))
wb.SaveToStream(f);
}
public void DoubleBorderMergedHVC()
{
var wb = new Workbook();
var sheet1 = wb.Sheets.AddSheet(new Sheet("Sheet 1") { ShowGridLines = true });
var sheet2 = wb.Sheets.AddSheet(new Sheet("Sheet 2") { ShowGridLines = true });
var sheet3 = wb.Sheets.AddSheet(new Sheet("Sheet 3") { ShowGridLines = true });
var range = sheet3[0, 0, 10, 10];
range.SetOutsideBorder(new BorderEdge { Color = Color.Black, Style = BorderStyle.Double });
var area = range.Merge();
area.Value = "Hello";
area.Style.Alignment.VAlign = VerticalAlignment.Center;
area.Style.Alignment.HAlign = HorizontalAlignment.Center;
using (var f = File.Open("DoubleBorderMergedHVC.xlsx", FileMode.Create))
wb.SaveToStream(f);
}
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);
}
public void ProblematicTest()
{
var PhaseWidth = 13;
var phaseStartCol = 0;
var VOffset = 10;
var TableHeight = 10;
var wb = new Workbook();
var sheet = wb.Sheets.AddSheet(new Sheet("Sheet") { ShowGridLines = true });
for (var i = 0; i < 3; i++)
{
var phaseArea = sheet[0, phaseStartCol, 100, phaseStartCol + PhaseWidth - 1];
phaseArea.SetOutsideBorder(new BorderEdge { Style = BorderStyle.Double, Color = Color.Black });
var title = phaseArea[0, 3, 2, PhaseWidth - 4].Merge();
title.Value = "Costs Budget";
title.Style.Alignment.HAlign = HorizontalAlignment.Center;
title.Style.Alignment.VAlign = VerticalAlignment.Center;
title.Style.Font.Size = 16;
title.Style.Font.Bold = true;
var tableArea = phaseArea[VOffset, 0, VOffset + TableHeight, PhaseWidth - 1];
var square = tableArea[0, 0, 4, 1].Merge();
var header = tableArea[0, 2, 0, PhaseWidth - 1].Merge();
header.Value = "Something";
//var iCosts = tableArea[1, 2, 1, 4].Merge();
//iCosts.Value = "Incurred Costs";
phaseStartCol += PhaseWidth;
}
using (var f = File.Open("ProblematicTest.xlsx", FileMode.Create))
wb.SaveToStream(f);
}
public void PageBreakTests()
{
var wb = new Workbook();
var sheet = wb.Sheets.AddSheet(new Sheet("Sheet") { ShowGridLines = true });
sheet.RowBreaks.AddRange(new[] { 1, 5, 13 });
sheet.ColumnBreaks.AddRange(new[] { 1, 5, 13 });
sheet[10, 10].Value = 100;
using (var f = File.Open("PageBreak.xlsx", FileMode.Create))
wb.SaveToStream(f);
}
public void RowHeightTest()
{
var wb = new Workbook();
var sheet1 = wb.Sheets.AddSheet(new Sheet("Sheet 1") { ShowGridLines = true });
sheet1[0].Height = 200;
using (var f = File.Open("RowHeightTest.xlsx", FileMode.Create))
wb.SaveToStream(f);
}
