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 ToStringTest()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLRangeAddress address = ws.Cell(1, 1).AsRange().RangeAddress;
Assert.AreEqual("A1:A1", address.ToString());
Assert.AreEqual("A1:A1", address.ToStringRelative());
Assert.AreEqual("'Sheet1'!A1:A1", address.ToStringRelative(true));
Assert.AreEqual("$A$1:$A$1", address.ToStringFixed());
Assert.AreEqual("$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.A1));
Assert.AreEqual("R1C1:R1C1", address.ToStringFixed(XLReferenceStyle.R1C1));
Assert.AreEqual("$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.Default));
Assert.AreEqual("'Sheet1'!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.A1, true));
Assert.AreEqual("'Sheet1'!R1C1:R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true));
Assert.AreEqual("'Sheet1'!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.Default, true));
}
public void CellsUsed()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
ws.Cell(1, 1);
ws.Cell(2, 2);
int count = ws.Range("A1:B2").CellsUsed().Count();
Assert.AreEqual(0, count);
}
public void IsBlank_false()
{
var ws = new XLWorkbook().AddWorksheet("Sheet");
ws.Cell("A1").Value = " ";
var actual = ws.Evaluate("=IsBlank(A1)");
Assert.AreEqual(false, actual);
}
public void Double_NaN_is_a_string()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell("A1");
var doubleList = new List<Double> {0.0/0.0};
cell.Value = doubleList.AsEnumerable();
Assert.AreNotEqual(XLCellValues.Number, cell.DataType);
}
public void IsEmpty2()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
IXLRange range = ws.Range("A1:B2");
bool actual = range.IsEmpty(true);
bool expected = true;
Assert.AreEqual(expected, actual);
}
public void CellsUsedIncludeStyles2()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
ws.Row(2).Style.Fill.BackgroundColor = XLColor.Red;
ws.Column(2).Style.Fill.BackgroundColor = XLColor.Red;
ws.Cell(3, 3).Value = "ASDF";
var range = ws.RangeUsed(true).RangeAddress.ToString();
Assert.AreEqual("B2:C3", range);
}
public void IsEmpty5()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
cell.Style.Fill.BackgroundColor = XLColor.Red;
IXLRange range = ws.Range("A1:B2");
bool actual = range.IsEmpty(true);
bool expected = false;
Assert.AreEqual(expected, actual);
}
public void AddTextTest1()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
IXLRichText richString = cell.RichText;
string text = "Hello";
richString.AddText(text).SetBold().SetFontColor(XLColor.Red);
Assert.AreEqual(cell.GetString(), text);
Assert.AreEqual(cell.RichText.First().Bold, true);
Assert.AreEqual(cell.RichText.First().FontColor, XLColor.Red);
Assert.AreEqual(1, richString.Count);
richString.AddText("World");
Assert.AreEqual(richString.First().Text, text, "Item in collection is not the same as the one returned");
}
public void AccessRichTextTest1()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
cell.RichText.AddText("12");
cell.DataType = XLCellValues.Number;
Assert.AreEqual(12.0, cell.GetDouble());
IXLRichText richText = cell.RichText;
Assert.AreEqual("12", richText.ToString());
richText.AddText("34");
Assert.AreEqual("1234", cell.GetString());
Assert.AreEqual(XLCellValues.Number, cell.DataType);
Assert.AreEqual(1234.0, cell.GetDouble());
}
public void AddTextTest2()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
Int32 number = 123;
cell.SetValue(number).Style
.Font.SetBold()
.Font.SetFontColor(XLColor.Red);
string text = number.ToString();
Assert.AreEqual(cell.RichText.ToString(), text);
Assert.AreEqual(cell.RichText.First().Bold, true);
Assert.AreEqual(cell.RichText.First().FontColor, XLColor.Red);
Assert.AreEqual(1, cell.RichText.Count);
cell.RichText.AddText("World");
Assert.AreEqual(cell.RichText.First().Text, text, "Item in collection is not the same as the one returned");
}
public void TryGetValue_TimeSpan_BadString()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
TimeSpan outValue;
string timeSpan = "ABC";
bool success = ws.Cell("A1").SetValue(timeSpan).TryGetValue(out outValue);
Assert.IsFalse(success);
}
public void TryGetValue_sbyte_Good2()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
sbyte outValue;
IXLCell cell = ws.Cell("A1").SetValue("5");
bool success = cell.TryGetValue(out outValue);
Assert.IsTrue(success);
Assert.AreEqual(5, outValue);
}
public void TryGetValue_RichText_Good()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLRichText outValue;
IXLCell cell = ws.Cell("A1");
cell.RichText.AddText("Anything");
bool success = cell.TryGetValue(out outValue);
Assert.IsTrue(success);
Assert.AreEqual(cell.RichText, outValue);
}
public void ValueSetToNull()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
cell.Value = new DateTime(2000, 1, 2);
cell.Value = null;
string actual = cell.GetString();
string expected = String.Empty;
Assert.AreEqual(expected, actual);
}
public void IsEmpty4()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
cell.Style.Fill.BackgroundColor = XLColor.Red;
bool actual = cell.IsEmpty(false);
bool expected = true;
Assert.AreEqual(expected, actual);
}
public void ToStringTest()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLRichText richString = ws.Cell(1, 1).RichText;
richString.AddText("Hello");
richString.AddText(" ");
richString.AddText("World");
string expected = "Hello World";
string actual = richString.ToString();
Assert.AreEqual(expected, actual);
richString.AddText("!");
expected = "Hello World!";
actual = richString.ToString();
Assert.AreEqual(expected, actual);
richString.ClearText();
expected = String.Empty;
actual = richString.ToString();
Assert.AreEqual(expected, actual);
}
public void TryGetValue_TimeSpan_GoodString()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
TimeSpan outValue;
var timeSpan = new TimeSpan(1, 1, 1);
bool success = ws.Cell("A1").SetValue(timeSpan.ToString()).TryGetValue(out outValue);
Assert.IsTrue(success);
Assert.AreEqual(timeSpan, outValue);
}
public void ValueSetDateWithShortUserDateFormat()
{
// For this test to make sense, user's local date format should be dd/MM/yy (note without the 2 century digits)
// What happened previously was that the century digits got lost in .ToString() conversion and wrong century was sometimes returned.
var ci = new CultureInfo(CultureInfo.InvariantCulture.LCID);
ci.DateTimeFormat.ShortDatePattern = "dd/MM/yy";
Thread.CurrentThread.CurrentCulture = ci;
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
var expected = DateTime.Today.AddYears(20);
cell.Value = expected;
var actual = (DateTime)cell.Value;
Assert.AreEqual(expected, actual);
}
public void Nan_is_not_a_number()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell("A1");
cell.Value = "Nan";
Assert.AreNotEqual(XLCellValues.Number, cell.DataType);
}
public void IsEmpty1()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
bool actual = cell.IsEmpty();
bool expected = true;
Assert.AreEqual(expected, actual);
}
public void TryGetValue_Boolean_True()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
Boolean outValue;
IXLCell cell = ws.Cell("A1").SetValue(true);
bool success = cell.TryGetValue(out outValue);
Assert.IsTrue(success);
Assert.IsTrue(outValue);
}
public void InsertData1()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLRange range = ws.Cell(2, 2).InsertData(new[] {"a", "b", "c"});
Assert.AreEqual("'Sheet1'!B2:B4", range.ToString());
}
public void Substring_IndexOutsideRange4()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLRichText richString = ws.Cell(1, 1).RichText;
richString.AddText("Hello");
richString.AddText("World");
Assert.That(() => richString.Substring(5, 20), Throws.TypeOf<IndexOutOfRangeException>());
}
public void MMult()
{
IXLWorksheet ws = new XLWorkbook().AddWorksheet("Sheet1");
ws.Cell("A1").SetValue(2).CellRight().SetValue(4);
ws.Cell("A2").SetValue(3).CellRight().SetValue(5);
ws.Cell("A3").SetValue(2).CellRight().SetValue(4);
ws.Cell("A4").SetValue(3).CellRight().SetValue(5);
Object actual;
ws.Cell("A5").FormulaA1 = "MMult(A1:B2, A3:B4)";
actual = ws.Cell("A5").Value;
Assert.AreEqual(16.0, actual);
ws.Cell("A6").FormulaA1 = "Sum(A5)";
actual = ws.Cell("A6").Value;
Assert.AreEqual(16.0, actual);
ws.Cell("A7").FormulaA1 = "Sum(MMult(A1:B2, A3:B4))";
actual = ws.Cell("A7").Value;
Assert.AreEqual(102.0, actual);
}
public void TryGetValue_sbyte_Bad2()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
sbyte outValue;
IXLCell cell = ws.Cell("A1").SetValue("255");
bool success = cell.TryGetValue(out outValue);
Assert.IsFalse(success);
}
public void MInverse()
{
IXLWorksheet ws = new XLWorkbook().AddWorksheet("Sheet1");
ws.Cell("A1").SetValue(1).CellRight().SetValue(2).CellRight().SetValue(1);
ws.Cell("A2").SetValue(3).CellRight().SetValue(4).CellRight().SetValue(-1);
ws.Cell("A3").SetValue(0).CellRight().SetValue(2).CellRight().SetValue(0);
Object actual;
ws.Cell("A5").FormulaA1 = "MInverse(A1:C3)";
actual = ws.Cell("A5").Value;
Assert.IsTrue(XLHelper.AreEqual(0.25, (double) actual));
ws.Cell("A6").FormulaA1 = "Sum(A5)";
actual = ws.Cell("A6").Value;
Assert.IsTrue(XLHelper.AreEqual(0.25, (double) actual));
ws.Cell("A7").FormulaA1 = "Sum(MInverse(A1:C3))";
actual = ws.Cell("A7").Value;
Assert.IsTrue(XLHelper.AreEqual(0.5, (double) actual));
}
public void LastColumnUsed()
{
IXLWorksheet ws = new XLWorkbook().AddWorksheet("Sheet1");
ws.Cell("A1").Value = "A1";
ws.Cell("B1").Value = "B1";
ws.Cell("A2").Value = "A2";
var lastCoUsed = ws.LastColumnUsed().ColumnNumber();
Assert.AreEqual(2, lastCoUsed);
}
public void MDetem()
{
IXLWorksheet ws = new XLWorkbook().AddWorksheet("Sheet1");
ws.Cell("A1").SetValue(2).CellRight().SetValue(4);
ws.Cell("A2").SetValue(3).CellRight().SetValue(5);
Object actual;
ws.Cell("A5").FormulaA1 = "MDeterm(A1:B2)";
actual = ws.Cell("A5").Value;
Assert.IsTrue(XLHelper.AreEqual(-2.0, (double) actual));
ws.Cell("A6").FormulaA1 = "Sum(A5)";
actual = ws.Cell("A6").Value;
Assert.IsTrue(XLHelper.AreEqual(-2.0, (double) actual));
ws.Cell("A7").FormulaA1 = "Sum(MDeterm(A1:B2))";
actual = ws.Cell("A7").Value;
Assert.IsTrue(XLHelper.AreEqual(-2.0, (double) actual));
}
public void CanGetNamedFromAnother()
{
var wb = new XLWorkbook();
var ws1 = wb.Worksheets.Add("Sheet1");
ws1.Cell("A1").SetValue(1).AddToNamed("value1");
Assert.AreEqual(1, wb.Cell("value1").GetValue<int>());
Assert.AreEqual(1, wb.Range("value1").FirstCell().GetValue<int>());
Assert.AreEqual(1, ws1.Cell("value1").GetValue<int>());
Assert.AreEqual(1, ws1.Range("value1").FirstCell().GetValue<int>());
var ws2 = wb.Worksheets.Add("Sheet2");
ws2.Cell("A1").SetFormulaA1("=value1").AddToNamed("value2");
Assert.AreEqual(1, wb.Cell("value2").GetValue<int>());
Assert.AreEqual(1, wb.Range("value2").FirstCell().GetValue<int>());
Assert.AreEqual(1, ws2.Cell("value1").GetValue<int>());
Assert.AreEqual(1, ws2.Range("value1").FirstCell().GetValue<int>());
Assert.AreEqual(1, ws2.Cell("value2").GetValue<int>());
Assert.AreEqual(1, ws2.Range("value2").FirstCell().GetValue<int>());
}
