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");
}
}
