public void Create(String filePath) { var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); ws.Cell(2, 1).SetValue(1) .CellRight().SetValue(1) .CellRight().SetValue(2) .CellRight().SetValue(3); var range = ws.RangeUsed(); range.AddConditionalFormat().WhenEquals("1").Font.SetBold(); range.InsertRowsAbove(1); workbook.SaveAs(filePath); }
public void DateAgainstStringComparison() { using (var wb = new XLWorkbook()) { var ws = wb.AddWorksheet("Sheet1"); ws.Cell("A1").Value = new DateTime(2016, 1, 1); ws.Cell("A1").DataType = XLCellValues.DateTime; ws.Cell("A2").FormulaA1 = @"=IF(A1 = """", ""A"", ""B"")"; var actual = ws.Cell("A2").Value; Assert.AreEqual(actual, "B"); ws.Cell("A3").FormulaA1 = @"=IF("""" = A1, ""A"", ""B"")"; actual = ws.Cell("A3").Value; Assert.AreEqual(actual, "B"); } }
public void Create(String filePath) { var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); ws.FirstCell().SetValue(1) .CellBelow().SetValue(1) .CellBelow().SetValue(2) .CellBelow().SetValue(3); ws.RangeUsed().AddConditionalFormat().IconSet(XLIconSetStyle.ThreeTrafficLights2, true, true) .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "0", XLCFContentType.Number) .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "2", XLCFContentType.Number) .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "3", XLCFContentType.Number); workbook.SaveAs(filePath); }
public void DataRange_returns_null_if_empty() { using (var wb = new XLWorkbook()) { IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Categories") .CellBelow().SetValue("A") .CellBelow().SetValue("B") .CellBelow().SetValue("C"); IXLTable table = ws.RangeUsed().CreateTable(); ws.Rows("2:4").Delete(); Assert.IsNull(table.DataRange); } }
/// <summary> /// 将 <see cref="DataTable"/> 中的内容以 Excel2007 的格式写入 <see cref="MemoryStream"/> /// </summary> /// <param name="table">要写入 <see cref="MemoryStream"/> 的 <see cref="DataTable"/> 数据表实例</param> /// <returns>一个可读取的 <see cref="MemoryStream"/> 实例</returns> /// <exception cref="ArgumentNullException">传入的实例为空</exception> static public MemoryStream ToExcel(this DataTable table) { if (table == null) { throw new ArgumentNullException(nameof(table)); } var workbook = new XLWorkbook(); workbook.AddWorksheet(table, "Sheet1"); MemoryStream excelWrite = new MemoryStream(); workbook.SaveAs(excelWrite, true); return(new MemoryStream(excelWrite.ToArray())); }
//public List<Invoice> ImportShipmentResult(string fromDate, string toDate) //{ // DataSet dsResult = new DataSet(); // using (SqlConnection conn = new SqlConnection("data source=198.1.1.3;uid=sa;password=zy01sy?;database=EDIDATA1")) // { // conn.Open(); // var cmd = conn.CreateCommand(); // cmd.CommandType = CommandType.Text; // cmd.CommandText = @"select EntryNo As DecNo, InvNo, ImportCD as CompCd, ImportDV as Div, // (select top 1 H5Trad from HD050 where H5INVN=InvNo) as TradCd // from nmb05 where ClearDate >= @fromDate and ClearDate <= @toDate"; // cmd.Parameters.AddWithValue("@fromdate", fromDate); // cmd.Parameters.AddWithValue("@todate", toDate); // var da = new SqlDataAdapter(cmd); // da.Fill(dsResult); // conn.Close(); // } // var inv = new List<Invoice>(); // foreach (DataRow dr in dsResult.Tables[0].Rows) // { // inv.Add(new Invoice // { // InvNO = dr["InvNO"].ToString(), // DecNo = dr["DecNo"].ToString(), // CompCd = dr["CompCd"].ToString(), // DivCd = dr["Div"].ToString(), // TransportType = dr["TradCd"].ToString() // }); // } // return inv; //} public void WriteExcel(List <Invoice> data, string name, string location) { var workbook = new XLWorkbook(); var worksheet = workbook.AddWorksheet(name); var range = worksheet.Range(worksheet.Cell(1, 1), worksheet.Cell(1, 4)); range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; range.Style.Font.Bold = true; range.Style.Font.FontName = "Tahoma"; range.Style.Font.FontSize = 10; worksheet.Cell(1, 1).Value = "BRANCH"; worksheet.Cell(1, 2).Value = "DECNO"; worksheet.Cell(1, 3).Value = "INVOICE"; worksheet.Cell(1, 4).Value = "STATUS"; for (int i = 1; i < 4; i++) { worksheet.Column(i).Width = 15; worksheet.Column(i).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; worksheet.Column(i).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; } int iRow = 2; for (int i = 0; i < data.Count; i++) { var invoice = data.ElementAt(i); worksheet.Cell(iRow, 1).Value = invoice.Branch; worksheet.Cell(iRow, 2).Value = invoice.DecNo; worksheet.Cell(iRow, 3).Value = invoice.InvNO; worksheet.Cell(iRow, 4).Value = invoice.Status; iRow++; } var destinationFile = Path.Combine(location, name + ".xlsx"); if (File.Exists(destinationFile)) { File.Delete(destinationFile); } workbook.SaveAs(destinationFile); }
public void Create(String filePath) { var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); ws.FirstCell().SetValue(1) .CellBelow().SetValue(1) .CellBelow().SetValue(2) .CellBelow().SetValue(3) .CellBelow().SetValue(4); ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Red, XLColor.Green) .LowestValue() .HighestValue(); workbook.SaveAs(filePath); }
public void Create(String filePath) { var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); using (var range = ws.Range("A1:A10")) { range.AddConditionalFormat().WhenEquals("3") .Fill.SetBackgroundColor(XLColor.Blue); range.AddConditionalFormat().WhenEquals("2") .Fill.SetBackgroundColor(XLColor.Green); range.AddConditionalFormat().WhenEquals("1") .Fill.SetBackgroundColor(XLColor.Red); } workbook.SaveAs(filePath); }
public void DeleteColumnUsedInNamedRange() { using (var wb = new XLWorkbook()) { var ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Column1"); ws.FirstCell().CellRight().SetValue("Column2").Style.Font.SetBold(); ws.FirstCell().CellRight(2).SetValue("Column3"); ws.NamedRanges.Add("MyRange", "A1:C1"); ws.Column(1).Delete(); Assert.IsTrue(ws.Cell("A1").Style.Font.Bold); Assert.AreEqual("Column3", ws.Cell("B1").GetValue <string>()); Assert.IsEmpty(ws.Cell("C1").GetValue <string>()); } }
public void ThenStepAddedSuccessfully() { var excelStepFormatter = Container.Resolve <ExcelStepFormatter>(); var step = new Step { NativeKeyword = "Given", Name = "I have some precondition" }; using (var workbook = new XLWorkbook()) { IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1"); int row = 5; excelStepFormatter.Format(worksheet, step, ref row); Check.That(worksheet.Cell("C5").Value).IsEqualTo(step.NativeKeyword); Check.That(worksheet.Cell("D5").Value).IsEqualTo(step.Name); } }
public void Create(String filePath) { var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); ws.FirstCell().SetValue(1) .CellBelow().SetValue(1) .CellBelow().SetValue(2) .CellBelow().SetValue(3); ws.RangeUsed().AddConditionalFormat().ColorScale() .LowestValue(XLColor.Red) .Midpoint(XLCFContentType.Percent, "50", XLColor.Yellow) .HighestValue(XLColor.Green); workbook.SaveAs(filePath); }
public void RemovedRangeExcludedFromIndex() { using (var wb = new XLWorkbook()) { var ws = wb.AddWorksheet(); var dv = ws.Range("A1:A3").SetDataValidation(); dv.MinValue = "100"; var range = ws.Range("C1:C3"); dv.AddRange(range); dv.RemoveRange(range); var actualResult = ws.DataValidations.TryGet(range.RangeAddress, out var foundDv); Assert.IsFalse(actualResult); Assert.IsNull(foundDv); } }
public void ThenSingleScenarioOutlineAddedSuccessfully() { var excelScenarioFormatter = Container.Resolve <ExcelScenarioOutlineFormatter>(); var exampleTable = new Table(); exampleTable.HeaderRow = new TableRow("Var1", "Var2", "Var3", "Var4"); exampleTable.DataRows = new List <TableRow>(new[] { new TableRow("1", "2", "3", "4"), new TableRow("5", "6", "7", "8") }); var example = new Example { Name = "Examples", Description = string.Empty, TableArgument = exampleTable }; var examples = new List <Example>(); examples.Add(example); var scenarioOutline = new ScenarioOutline { Name = "Test Feature", Description = "In order to test this feature,\nAs a developer\nI want to test this feature", Examples = examples }; using (var workbook = new XLWorkbook()) { IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1"); int row = 3; excelScenarioFormatter.Format(worksheet, scenarioOutline, ref row); Check.That(worksheet.Cell("B3").Value).IsEqualTo(scenarioOutline.Name); Check.That(worksheet.Cell("C4").Value).IsEqualTo(scenarioOutline.Description); Check.That(worksheet.Cell("B6").Value).IsEqualTo("Examples"); Check.That(worksheet.Cell("D7").Value).IsEqualTo("Var1"); Check.That(worksheet.Cell("E7").Value).IsEqualTo("Var2"); Check.That(worksheet.Cell("F7").Value).IsEqualTo("Var3"); Check.That(worksheet.Cell("G7").Value).IsEqualTo("Var4"); Check.That(worksheet.Cell("D8").Value).IsEqualTo(1.0); Check.That(worksheet.Cell("E8").Value).IsEqualTo(2.0); Check.That(worksheet.Cell("F8").Value).IsEqualTo(3.0); Check.That(worksheet.Cell("G8").Value).IsEqualTo(4.0); Check.That(worksheet.Cell("D9").Value).IsEqualTo(5.0); Check.That(worksheet.Cell("E9").Value).IsEqualTo(6.0); Check.That(worksheet.Cell("F9").Value).IsEqualTo(7.0); Check.That(worksheet.Cell("G9").Value).IsEqualTo(8.0); Check.That(row).IsEqualTo(10); } }
public void CanRenderRangeForEmptySet() { using (var wb = new XLWorkbook()) { var ws = wb.AddWorksheet("Sheet1"); ws.Range("A2:B3").AddToNamed("List"); ws.Cell("B2").Value = "{{item}}"; ws.Cell("B4").Value = "Cell below"; var template = new XLTemplate(wb); template.AddVariable("List", new List <string>()); template.Generate(); ws.Cell("B3").Value.Should().Be("Cell below"); ws.Cell("B4").Value.Should().Be(""); } }
public void WorkbookFilterPrivacyIsNotSetByDefault() { using var ms = new MemoryStream(); using (var wb = new XLWorkbook()) { wb.AddWorksheet(); wb.SaveAs(ms); } ms.Seek(0, SeekOrigin.Begin); using (var wb = SpreadsheetDocument.Open(ms, false)) { Assert.IsNull(wb.WorkbookPart.Workbook.WorkbookProperties.FilterPrivacy); } }
private void Init() { if (_sheet == null) { if (!_wb.TryGetWorksheet(SheetName, out _sheet)) { _sheet = _wb.AddWorksheet(SheetName); _sheet.SetCalcEngineCacheExpressions(false); } //_sheet.Visibility = XLWorksheetVisibility.VeryHidden; } _row = 1; _clmn = 1; _maxRow = _prevrow = 1; _maxClmn = _prevclmn = 1; Clear(); }
public void TableRenameTests() { var l = new List <TestObjectWithAttributes>() { new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } }; using (var wb = new XLWorkbook()) { IXLWorksheet ws = wb.AddWorksheet("Sheet1"); var table1 = ws.FirstCell().InsertTable(l); var table2 = ws.Cell("A10").InsertTable(l); Assert.AreEqual("Table1", table1.Name); Assert.AreEqual("Table2", table2.Name); table1.Name = "table1"; Assert.AreEqual("table1", table1.Name); table1.Name = "_table1"; Assert.AreEqual("_table1", table1.Name); table1.Name = "\\table1"; Assert.AreEqual("\\table1", table1.Name); Assert.Throws <ArgumentException>(() => table1.Name = ""); Assert.Throws <ArgumentException>(() => table1.Name = "R"); Assert.Throws <ArgumentException>(() => table1.Name = "C"); Assert.Throws <ArgumentException>(() => table1.Name = "r"); Assert.Throws <ArgumentException>(() => table1.Name = "c"); Assert.Throws <ArgumentException>(() => table1.Name = "123"); Assert.Throws <ArgumentException>(() => table1.Name = new String('A', 256)); Assert.Throws <ArgumentException>(() => table1.Name = "Table2"); Assert.Throws <ArgumentException>(() => table1.Name = "TABLE2"); } }
public override void OutputCSV(string filename) { var steps = Steps.OrderBy(step => step.ReflectionCoefficient).ToList(); var radiusList = (from step in steps from data in step.Datas select data.Radius).Distinct().OrderBy(r => r).ToList(); var book = new XLWorkbook(); var sheet = book.AddWorksheet("Main"); //説明 sheet.Cell(1, 1).Value = "説明"; sheet.Cell(2, 1).Value = Discription; //データの記述 const int dataStartRow = 4; sheet.Cell(dataStartRow, 1).Value = "半径/反射率"; for (var i = 0; i < radiusList.Count; i++) { var radius = radiusList[i]; sheet.Cell(dataStartRow + 1 + i, 1).Value = radius; } for (var i = 0; i < steps.Count; i++) { var step = steps[i]; sheet.Cell(dataStartRow, 2 + i).Value = step.ReflectionCoefficient.ToString("0.00"); } for (var i = 0; i < steps.Count; i++) { var step = steps[i]; var max = step.Datas.Max(data => data.Count); for (var j = 0; j < radiusList.Count; j++) { var radius = radiusList[j]; if (step.Datas.Any(data => data.Radius == radius)) { sheet.Cell(dataStartRow + 1 + j, 2 + i).Value = step.Datas.First(data => data.Radius == radius).Count / (double)max; } } } book.SaveAs(filename); }
public void TestFieldCellTypes() { var l = new List <TestObjectWithAttributes>() { new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } }; using (var wb = new XLWorkbook()) { var ws = wb.AddWorksheet("Sheet1"); var table = ws.Cell("B2").InsertTable(l); Assert.AreEqual(4, table.Fields.Count()); Assert.AreEqual("B2", table.Field(0).HeaderCell.Address.ToString()); Assert.AreEqual("C2", table.Field(1).HeaderCell.Address.ToString()); Assert.AreEqual("D2", table.Field(2).HeaderCell.Address.ToString()); Assert.AreEqual("E2", table.Field(3).HeaderCell.Address.ToString()); Assert.IsNull(table.Field(0).TotalsCell); Assert.IsNull(table.Field(1).TotalsCell); Assert.IsNull(table.Field(2).TotalsCell); Assert.IsNull(table.Field(3).TotalsCell); table.SetShowTotalsRow(); Assert.AreEqual("B5", table.Field(0).TotalsCell.Address.ToString()); Assert.AreEqual("C5", table.Field(1).TotalsCell.Address.ToString()); Assert.AreEqual("D5", table.Field(2).TotalsCell.Address.ToString()); Assert.AreEqual("E5", table.Field(3).TotalsCell.Address.ToString()); var field = table.Fields.Last(); Assert.AreEqual("E2:E5", field.Column.RangeAddress.ToString()); Assert.AreEqual("E3", field.DataCells.First().Address.ToString()); Assert.AreEqual("E4", field.DataCells.Last().Address.ToString()); } }
private void ExportExcelEventHandler(ControllerContext context) { try { var workbook = new XLWorkbook(); if (ExportData != null) { context.HttpContext.Response.Clear(); // Encoding context.HttpContext.Response.ContentEncoding = Encoding.UTF8; // Content-Type context.HttpContext.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; // FileName var browser = context.HttpContext.Request.Browser.Browser; var exportFileName = browser.Equals("FireFox", StringComparison.OrdinalIgnoreCase) ? FileName : HttpUtility.UrlEncode(FileName, Encoding.UTF8); // Header context.HttpContext.Response.Headers.Add("Content-Disposition", $"attachment; filename={exportFileName}"); // Add All DataTable workbook.AddWorksheet(ExportData, SheetName); // Write using (MemoryStream stream = new MemoryStream()) { workbook.SaveAs(stream); stream.WriteTo(context.HttpContext.Response.OutputStream); stream.Close(); } } workbook.Dispose(); } catch (Exception ex) { throw ex; } }
public FileResult Export() { var workbook = new XLWorkbook(); workbook.AddWorksheet("Contacts"); var ws = workbook.Worksheet("Contacts"); var contacts = db.Contacts.ToList(); //insert columns ws.Cell("A1").Value = "First Name"; ws.Cell("B1").Value = "Last Name"; ws.Cell("C1").Value = "Phone Number"; ws.Cell("D1").Value = "Email"; ws.Cell("E1").Value = "Address"; int row = 2; foreach (var contact in contacts) { ws.Cell("A" + row.ToString()).Value = contact.FirstName; ws.Cell("B" + row.ToString()).Value = contact.LastName; ws.Cell("C" + row.ToString()).Value = contact.PhoneNumbers.FirstOrDefault() != null?contact.PhoneNumbers.FirstOrDefault().Number : ""; ws.Cell("D" + row.ToString()).Value = contact.Emails.FirstOrDefault() != null?contact.Emails.FirstOrDefault().EmailAddress : ""; ws.Cell("E" + row.ToString()).Value = contact.Addresses.FirstOrDefault() != null?contact.Addresses.FirstOrDefault().Country + " " + contact.Addresses.FirstOrDefault().City + " " + contact.Addresses.FirstOrDefault().StreetAddress : ""; row++; } //konverton ne byte excelin per ta shkarkuar me metoden File var workbookBytes = new byte[0]; using (var ms = new MemoryStream()) { workbook.SaveAs(ms); workbookBytes = ms.ToArray(); } return(File(workbookBytes, "application/vnd.ms-excel", "Grid.xls")); }
public void TableShowHeader() { using (var wb = new XLWorkbook()) { IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Categories") .CellBelow().SetValue("A") .CellBelow().SetValue("B") .CellBelow().SetValue("C"); IXLTable table = ws.RangeUsed().CreateTable(); Assert.AreEqual("Categories", table.Fields.First().Name); table.SetShowHeaderRow(false); Assert.AreEqual("Categories", table.Fields.First().Name); Assert.IsTrue(ws.Cell(1, 1).IsEmpty(true)); Assert.AreEqual(null, table.HeadersRow()); Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); Assert.AreEqual("C", table.DataRange.LastCell().GetString()); table.SetShowHeaderRow(); IXLRangeRow headerRow = table.HeadersRow(); Assert.AreNotEqual(null, headerRow); Assert.AreEqual("Categories", headerRow.Cell(1).GetString()); table.SetShowHeaderRow(false); ws.FirstCell().SetValue("x"); table.SetShowHeaderRow(); Assert.AreEqual("x", ws.FirstCell().GetString()); Assert.AreEqual("Categories", ws.Cell("A2").GetString()); Assert.AreNotEqual(null, headerRow); Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); Assert.AreEqual("C", table.DataRange.LastCell().GetString()); } }
public void TestCopyIfDataIsSet() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Test"); var excelReport = Substitute.For <object>(); var templateProcessor = Substitute.For <ITemplateProcessor>(); IXLRange range = ws.Range(1, 1, 2, 4); range.AddToNamed("DataPanel", XLScope.Worksheet); IXLNamedRange namedRange = ws.NamedRange("DataPanel"); object[] data = { 1, "One" }; var panel = new ExcelDataSourcePanel(data, namedRange, excelReport, templateProcessor) { RenderPriority = 10, Type = PanelType.Horizontal, ShiftType = ShiftType.NoShift, BeforeRenderMethodName = "BeforeRenderMethod", AfterRenderMethodName = "AfterRenderMethod", BeforeDataItemRenderMethodName = "BeforeDataItemRenderMethodName", AfterDataItemRenderMethodName = "AfterDataItemRenderMethodName", GroupBy = "2,4", }; ExcelDataSourcePanel copiedPanel = (ExcelDataSourcePanel)panel.Copy(ws.Cell(5, 5)); Assert.AreSame(excelReport, copiedPanel.GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel)); Assert.AreSame(templateProcessor, copiedPanel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel)); Assert.IsNull(copiedPanel.GetType().GetField("_dataSourceTemplate", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel)); Assert.AreSame(data, copiedPanel.GetType().GetField("_data", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel)); Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell()); Assert.AreEqual(ws.Cell(6, 8), copiedPanel.Range.LastCell()); Assert.AreEqual(10, copiedPanel.RenderPriority); Assert.AreEqual(PanelType.Horizontal, copiedPanel.Type); Assert.AreEqual(ShiftType.NoShift, copiedPanel.ShiftType); Assert.AreEqual("BeforeRenderMethod", copiedPanel.BeforeRenderMethodName); Assert.AreEqual("AfterRenderMethod", copiedPanel.AfterRenderMethodName); Assert.AreEqual("BeforeDataItemRenderMethodName", copiedPanel.BeforeDataItemRenderMethodName); Assert.AreEqual("AfterDataItemRenderMethodName", copiedPanel.AfterDataItemRenderMethodName); Assert.AreEqual("2,4", copiedPanel.GroupBy); Assert.IsNull(copiedPanel.Parent); //wb.SaveAs("test.xlsx"); }
public void TestInvalidXmlCharacters() { byte[] data; using (var stream = new MemoryStream()) { var wb = new XLWorkbook(); wb.AddWorksheet("Sheet1").FirstCell().SetValue("\u0018"); wb.SaveAs(stream); data = stream.ToArray(); } using (var stream = new MemoryStream(data)) { var wb = new XLWorkbook(stream); Assert.AreEqual("\u0018", wb.Worksheets.First().FirstCell().Value); } }
public void FirstCellUsedPredicateConsidersMergedRanges() { using (XLWorkbook wb = new XLWorkbook()) { var ws = wb.AddWorksheet("Sheet1"); ws.Cell("A1").Style.Fill.BackgroundColor = XLColor.Red; ws.Cell("A2").Style.Fill.BackgroundColor = XLColor.Yellow; ws.Cell("A3").Style.Fill.BackgroundColor = XLColor.Green; ws.Range("A1:C1").Merge(); ws.Range("A2:C2").Merge(); ws.Range("A3:C3").Merge(); var actual = ws.FirstCellUsed(XLCellsUsedOptions.All, c => c.Style.Fill.BackgroundColor == XLColor.Yellow); Assert.AreEqual("A2", actual.Address.ToString()); } }
public IXLWorksheet Create(XLWorkbook workbook) { var returnVar = workbook.Worksheets.FirstOrDefault(ws => ws.Name.Equals(SheetName, StringComparison.OrdinalIgnoreCase)); //.Worksheet(DataType.Name); if (returnVar == null) { returnVar = workbook.AddWorksheet(SheetName); var firstRow = returnVar.Row(1); int i = 1; foreach (var p in GetProperties()) { var cell = firstRow.Cell(i++); cell.Value = p.Name; cell.DataType = XLCellValues.Text; } } return(returnVar); }
public void Create(String filePath) { var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Hello") .CellBelow().SetValue("Hellos") .CellBelow().SetValue("Hell") .CellBelow().SetValue("Holl"); ws.RangeUsed().AddConditionalFormat().WhenStartsWith("Hell") .Fill.SetBackgroundColor(XLColor.Red) .Border.SetOutsideBorder(XLBorderStyleValues.Thick) .Border.SetOutsideBorderColor(XLColor.Blue) .Font.SetBold(); workbook.SaveAs(filePath); }
/// <summary> /// Create sheet describing Stored Procedures and functions /// </summary> /// <param name="workbook">The workbook to create the sheet in</param> /// <param name="storedProcFuncs">The stored procedures and functions to document</param> public void CreateSheetInWorkbook(ref XLWorkbook workbook, IEnumerable <StoredProcFuncInfo> storedProcFuncs) { if (storedProcFuncs == null) { throw new ArgumentNullException(nameof(storedProcFuncs)); } if (storedProcFuncs.Count() == 0) { throw new ArgumentException(nameof(storedProcFuncs)); } _data = storedProcFuncs.ToList(); IXLWorksheet sheet = workbook.AddWorksheet("DbObjects"); CreateTableRowsForObjects(ref sheet); FormatWorksheet(ref sheet); }
public void IsOdd_Simple_true() { using (var wb = new XLWorkbook()) { var ws = wb.AddWorksheet("Sheet"); ws.Cell("A1").Value = 1; ws.Cell("A2").Value = 1.2; ws.Cell("A3").Value = 3; var actual = ws.Evaluate("=IsOdd(A1)"); Assert.AreEqual(true, actual); actual = ws.Evaluate("=IsOdd(A2)"); Assert.AreEqual(true, actual); actual = ws.Evaluate("=IsOdd(A3)"); Assert.AreEqual(true, actual); } }
public void SeriesSum() { object actual = XLWorkbook.EvaluateExpr("SERIESSUM(2,3,4,5)"); Assert.AreEqual(40.0, actual); var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.Cell("A2").FormulaA1 = "PI()/4"; ws.Cell("A3").Value = 1; ws.Cell("A4").FormulaA1 = "-1/FACT(2)"; ws.Cell("A5").FormulaA1 = "1/FACT(4)"; ws.Cell("A6").FormulaA1 = "-1/FACT(6)"; actual = ws.Evaluate("SERIESSUM(A2,0,2,A3:A6)"); Assert.IsTrue(Math.Abs(0.70710321482284566 - (double)actual) < XLHelper.Epsilon); }