public void CellsAndRanges() { var workbook = new Workbook(); var sheet = workbook.Sheets.AddSheet("Cells and Ranges"); sheet["A1"].Value = "A1"; sheet[0, 1].Value = "B1"; //sheet[row, col] sheet[1, 0].Value = "A2"; sheet["B2"].Value = "B2"; var title = sheet[2, 0, 2, 3].Merge(); title.Value = "Title"; title.Style.Alignment.HAlign = HorizontalAlignment.Center; var headers = sheet["A4", "D4"]; headers.SetBorder(new BorderEdge { Style = BorderStyle.Thin, Color = Color.Black }); //add border to header cells headers[0].Value = "Header 1"; //access a cell relative to range's top-left corner headers[1].Value = "Header 2"; headers[2].Value = "Header 3"; headers[3].Value = "Header 4"; var data = sheet["A5", "D10"]; for (var row = 0; row < 6; row++) { var rowRange = data.GetRow(row); //get row subrange for (var col = 0; col < 4; col++) rowRange[col].Value = String.Format("{0}:{1}", row, col); } data.GetColumn(0).ApplyStyle(new CellStyle { Fill = new CellFill { Foreground = new Color(0xffff00), Pattern = FillPattern.Solid } }); //apply yellow background to first column workbook.Save("CellsAndRanges.xlsx"); }
public static void Run() { var workbook = new Workbook(); var sheet = workbook.Sheets.AddSheet("Demo"); sheet["A1"].Value = "Hello World"; sheet["A1"].Style.Font.Size = 20; workbook.Save(@"FirstWorkbook.xlsx"); }
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); }
//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 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 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 DefinedNamesTest() { { var wb = new Workbook(); var sheet = wb.Sheets.AddSheet(new Sheet("Sheet") { ShowGridLines = true }); sheet.DefinedNames.AddValue("X", "1"); wb.Save("DefinedNames.xlsx"); } { var wb = Workbook.Load("DefinedNames.xlsx"); Assert.AreEqual(1, wb.Sheets[0].DefinedNames.Count); } }
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); }
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); }
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 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); }