public void TableCreatedFromListOfObjectWithPropertyAttributes() { 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"); ws.FirstCell().InsertTable(l); Assert.AreEqual(4, ws.Tables.First().ColumnCount()); Assert.AreEqual("FirstColumn", ws.FirstCell().Value); Assert.AreEqual("SecondColumn", ws.FirstCell().CellRight().Value); Assert.AreEqual("SomeFieldNotProperty", ws.FirstCell().CellRight().CellRight().Value); Assert.AreEqual("UnOrderedColumn", ws.FirstCell().CellRight().CellRight().CellRight().Value); } }
public void CopyConditionalFormatsCount() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().AddConditionalFormat().WhenContains("1").Fill.SetBackgroundColor(XLColor.Blue); ws.Cell("A2").Value = ws.FirstCell(); Assert.AreEqual(2, ws.ConditionalFormats.Count()); }
public void Formula_from_another_sheet() { var wb = new XLWorkbook(); IXLWorksheet ws1 = wb.AddWorksheet("ws1"); ws1.FirstCell().SetValue(1).CellRight().SetFormulaA1("A1 + 1"); IXLWorksheet ws2 = wb.AddWorksheet("ws2"); ws2.FirstCell().SetFormulaA1("ws1!B1 + 1"); object v = ws2.FirstCell().Value; Assert.AreEqual(3.0, v); }
public void OverlappingTablesThrowsException() { var dt = new DataTable("sheet1"); dt.Columns.Add("col1", typeof(string)); dt.Columns.Add("col2", typeof(double)); using (var wb = new XLWorkbook()) { IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().InsertTable(dt, true); Assert.Throws <InvalidOperationException>(() => ws.FirstCell().CellRight().InsertTable(dt, true)); } }
public void WorkbookContainsNamedRange() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().AddToNamed("Name"); Assert.IsTrue(wb.NamedRanges.Contains("Name")); Assert.IsFalse(wb.NamedRanges.Contains("NameX")); Assert.IsNotNull(wb.NamedRange("Name")); Assert.IsNull(wb.NamedRange("NameX")); IXLNamedRange range1; Boolean result1 = wb.NamedRanges.TryGetValue("Name", out range1); Assert.IsTrue(result1); Assert.IsNotNull(range1); IXLNamedRange range2; Boolean result2 = wb.NamedRanges.TryGetValue("NameX", out range2); Assert.IsFalse(result2); Assert.IsNull(range2); }
public void AutoFilterExpandsWithTable() { using (var wb = new XLWorkbook()) { using (IXLWorksheet ws = wb.Worksheets.Add("Sheet1")) { ws.FirstCell().SetValue("Categories") .CellBelow().SetValue("1") .CellBelow().SetValue("2"); IXLTable table = ws.RangeUsed().CreateTable(); var listOfArr = new List <Int32>(); listOfArr.Add(3); listOfArr.Add(4); listOfArr.Add(5); listOfArr.Add(6); table.DataRange.InsertRowsBelow(listOfArr.Count - table.DataRange.RowCount()); table.DataRange.FirstCell().InsertData(listOfArr); Assert.AreEqual("A1:A5", table.AutoFilter.Range.RangeAddress.ToStringRelative()); } } }
public void TableInsertBelowFromRows() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Value"); IXLTable table = ws.Range("A1:A2").CreateTable(); table.SetShowTotalsRow() .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; IXLTableRow row = table.DataRange.FirstRow(); row.Field("Value").Value = 1; row = row.InsertRowsBelow(1).First(); row.Field("Value").Value = 2; row = row.InsertRowsBelow(1).First(); row.Field("Value").Value = 3; Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox.xlsx"); }
public void TableAsDynamicEnumerable() { 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 table = ws.FirstCell().InsertTable(l); foreach (var d in table.AsDynamicEnumerable()) { Assert.DoesNotThrow(() => { object value; value = d.FirstColumn; value = d.SecondColumn; value = d.UnOrderedColumn; value = d.SomeFieldNotProperty; }); } } }
public static IXLRange MoveRange(IXLRange range, IXLCell cell) { if (!IsCellInsideRange(cell, range)) { IXLRange newRange = CopyRange(range, cell); range.Clear(); return(newRange); } // If the cell which the movement occurs to is inside the range then the way above will not work properly, // That's why, copy through the auxiliary sheet IXLWorksheet tempWs = null; try { tempWs = AddTempWorksheet(range.Worksheet.Workbook); IXLRange tempRange = range.CopyTo(tempWs.FirstCell()); range.Clear(); return(tempRange.CopyTo(cell)); } finally { tempWs?.Delete(); } }
public static IXLRange CopyRange(IXLRange range, IXLCell cell) { IXLCell newRangeFirstCell = cell; IXLCell newRangeLastCell = ShiftCell(newRangeFirstCell, new AddressShift(range.RowCount() - 1, range.ColumnCount() - 1)); IXLRange newRange = range.Worksheet.Range(newRangeFirstCell, newRangeLastCell); if (!IsCellInsideRange(cell, range)) { newRange.Clear(); range.CopyTo(newRange); } else { // If the cell which the copy occurs to is inside the range then the copy will be wrong (copying is performed by cells, // copied cells appear in the first range immediately and start copying again) // That's why, copy through the auxiliary sheet IXLWorksheet tempWs = null; try { tempWs = AddTempWorksheet(range.Worksheet.Workbook); IXLRange tempRange = range.CopyTo(tempWs.FirstCell()); newRange.Clear(); tempRange.CopyTo(newRange); } finally { tempWs?.Delete(); } } return(newRange); }
public void TableShowHeader() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Categories") .CellBelow().SetValue("A") .CellBelow().SetValue("B") .CellBelow().SetValue("C"); ws.RangeUsed().CreateTable().SetShowHeaderRow(false); IXLTable table = ws.Tables.First(); //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox1.xlsx"); 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(); //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox2.xlsx"); //wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox3.xlsx"); 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 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 DefaultColorIndex64isTransparentWhite() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); XLColor color = ws.FirstCell().Style.Fill.BackgroundColor; Assert.AreEqual(XLColorType.Indexed, color.ColorType); Assert.AreEqual(64, color.Indexed); Assert.AreEqual(Color.Transparent, color.Color); }
public void TableCreatedFromEmptyListOfObject() { var l = new List <TestObject>(); var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().InsertTable(l); Assert.AreEqual(2, ws.Tables.First().ColumnCount()); }
public void CopyColumn() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Test").Style.Font.SetBold(); ws.FirstColumn().CopyTo(ws.Column(2)); Assert.IsTrue(ws.Cell("B1").Style.Font.Bold); }
public void TableCreatedFromEmptyListOfInt() { var l = new List <Int32>(); using (var wb = new XLWorkbook()) { IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().InsertTable(l); Assert.AreEqual(1, ws.Tables.First().ColumnCount()); } }
public void CanSaveTableCreatedFromSingleRow() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Title"); ws.Range("A1").CreateTable(); using (var ms = new MemoryStream()) wb.SaveAs(ms); }
public void Cell3() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue(1).AddToNamed("Result"); IXLCell cell = wb.Cell("Sheet1!Result"); Assert.IsNotNull(cell); Assert.AreEqual(1, cell.GetValue <Int32>()); }
public void CreatingATableFromHeadersPushCellsBelow() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Title") .CellBelow().SetValue("X"); ws.Range("A1").CreateTable(); Assert.AreEqual(ws.Cell("A2").GetString(), String.Empty); Assert.AreEqual(ws.Cell("A3").GetString(), "X"); }
public void Cells2() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue(1).AddToNamed("Result", XLScope.Worksheet); IXLCells cells = wb.Cells("Sheet1!Result, ABC"); Assert.IsNotNull(cells); Assert.AreEqual(1, cells.Count()); Assert.AreEqual(1, cells.First().GetValue <Int32>()); }
public void Ranges3() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue(1).AddToNamed("Result"); IXLRanges ranges = wb.Ranges("Sheet1!Result, ABC"); Assert.IsNotNull(ranges); Assert.AreEqual(1, ranges.Cells().Count()); Assert.AreEqual(1, ranges.First().FirstCell().GetValue <Int32>()); }
public void Range2() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue(1).AddToNamed("Result", XLScope.Worksheet); IXLRange range = wb.Range("Sheet1!Result"); Assert.IsNotNull(range); Assert.AreEqual(1, range.Cells().Count()); Assert.AreEqual(1, range.FirstCell().GetValue <Int32>()); }
public void TableCreatedFromEmptyDataTable() { var dt = new DataTable("sheet1"); dt.Columns.Add("col1", typeof(string)); dt.Columns.Add("col2", typeof(double)); var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().InsertTable(dt); Assert.AreEqual(2, ws.Tables.First().ColumnCount()); }
public ExcelTabelsController(string fileName, int numberOfWorksheet = 1) { if (fileName.Length != 0) { currentFileName = fileName; workbook = new XLWorkbook(currentFileName); worksheet = workbook.Worksheets.Worksheet(numberOfWorksheet); columnsCount = worksheet.FirstCell().CurrentRegion.ColumnCount(); rowsCount = worksheet.ActiveCell.CurrentRegion.LastRow().RowNumber(); } }
/// <summary> /// Returns VS Project name of worksheet in Excel file /// </summary> /// <param name="worksheet"></param> /// <returns></returns> private string getProjectName(IXLWorksheet worksheet) { string firstCellVal = worksheet.FirstCell().Value.ToString(); // Project names are saved in first cell, because excel worksheet names are limited to 31 characters. Keep old behaviour for compatibility if (firstCellVal == "ID") { return(worksheet.Name); } else { return(firstCellVal); } }
public void Inserting_Column_Sets_Header() { 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(); table.InsertColumnsAfter(1); Assert.AreEqual("Column2", table.HeadersRow().LastCell().GetString()); } }
public void Workdays_MultipleHolidaysGiven() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Date") .CellBelow().SetValue(new DateTime(2008, 10, 1)) .CellBelow().SetValue(151) .CellBelow().SetValue(new DateTime(2008, 11, 26)) .CellBelow().SetValue(new DateTime(2008, 12, 4)) .CellBelow().SetValue(new DateTime(2009, 1, 21)); Object actual = ws.Evaluate("Workday(A2,A3,A4:A6)"); Assert.AreEqual(new DateTime(2009, 5, 5), actual); }
public void Validation_persists_on_Worksheet_DataValidations() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.Worksheets.Add("People"); ws.FirstCell().SetValue("Categories") .CellBelow().SetValue("A"); IXLTable table = ws.RangeUsed().CreateTable(); IXLDataValidation dv = table.DataRange.SetDataValidation(); dv.ErrorTitle = "Error"; Assert.AreEqual("Error", ws.DataValidations.Single().ErrorTitle); }
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); } }
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"); } }