public void AddressConvert()
{
var pos1 = new CellPosition(0, 0);
var pos2 = new CellPosition(10, 20);
AssertEquals(pos1.ToAddress(), "A1");
AssertEquals(pos2.ToAddress(), "U11");
AssertEquals(pos1, new CellPosition("A1"));
AssertEquals(pos2, new CellPosition("U11"));
var r1 = new RangePosition(0, 0, 1, 1);
var r2 = new RangePosition(0, 0, 3, 3);
var r3 = new RangePosition(10, 20, 5, 5);
AssertEquals(r1.ToAddress(), "A1:A1");
AssertEquals(r2.ToAddress(), "A1:C3");
AssertEquals(r3.ToAddress(), "U11:Y15");
AssertEquals(r1, new RangePosition("A1:A1"));
AssertEquals(r2, new RangePosition("A1:C3"));
AssertEquals(r3, new RangePosition("U11:Y15"));
AssertEquals(new RangePosition("A1:A1"), new RangePosition(0, 0, 1, 1));
}
void MixedRelativeAndAbsolute()
{
CellPosition pos;
RangePosition range;
pos = new CellPosition("$A1");
AssertEquals(pos.Col, 0);
AssertEquals(pos.Row, 0);
AssertEquals("$A1", pos.ToAddress());
AssertEquals("A1", pos.ToRelativeAddress());
AssertEquals("$A$1", pos.ToAbsoluteAddress());
pos = new CellPosition("Z$10");
AssertEquals(pos.Col, 25);
AssertEquals(pos.Row, 9);
AssertEquals("Z$10", pos.ToAddress());
AssertEquals("Z10", pos.ToRelativeAddress());
AssertEquals("$Z$10", pos.ToAbsoluteAddress());
// full cols
range = new RangePosition("$A1:B2");
AssertSame(range.Row, 0);
AssertSame(range.Rows, 2);
AssertSame(range.Col, 0);
AssertSame(range.Cols, 2);
AssertEquals("$A1:B2", range.ToAddress());
AssertEquals("A1:B2", range.ToRelativeAddress());
AssertEquals("$A$1:$B$2", range.ToAbsoluteAddress());
}
public void RangeCalc()
{
var range = new RangePosition(10, 0, 3, 3);
worksheet[range] = new object[, ] {
{ 1, 2, 3 },
{ 4, 5, 6 },
{ 7, 8, 9 },
};
worksheet[10, 5] = "=SUM(" + range.ToAddress() + ")";
AssertEquals(worksheet.GetCellData <int>(10, 5), 45);
#if NO_LONGER_SUPPORTED_V088
worksheet[10, 6] = "=SUM(new Range(10, 0, 3, 3))";
AssertEquals(worksheet.GetCellText(10, 6), "45");
AssertEquals(worksheet.GetCellText(10, 5), worksheet.GetCellText(10, 6));
#endif // NO_LONGER_SUPPORTED_V088
}
void RangeCalc()
{
var range = new RangePosition("A2:C5");
worksheet[range] = new object[, ] {
{ 1, 2, 3 },
{ 4, 5, 6 },
{ 7, 8, 9 },
};
// sum range equals to data range
worksheet["E2"] = "=SUM(" + range.ToAddress() + ")";
AssertEquals(worksheet.GetCellData <int>("E2"), 45);
// sum range is larger than data range
worksheet["F2"] = "=SUM(A2:C7)";
AssertEquals(worksheet.GetCellText("F2"), "45");
// sum result should be same
AssertEquals(worksheet.GetCellText("E2"), worksheet.GetCellText("F2"));
}
public void Generic()
{
RangePosition range;
// 1,1
range = new RangePosition("A1");
AssertSame(range.Row, 0);
AssertSame(range.Col, 0);
AssertSame(range.Rows, 1);
AssertSame(range.Cols, 1);
AssertEquals(range.ToAddress(), "A1:A1");
AssertEquals(range.ToRelativeAddress(), "A1:A1");
AssertEquals(range.ToAbsoluteAddress(), "$A$1:$A$1");
AssertEquals(range.StartPos.ToAddress(), "A1");
AssertEquals(range.StartPos.ToRelativeAddress(), "A1");
AssertEquals(range.StartPos.ToAbsoluteAddress(), "$A$1");
AssertEquals(range.EndPos.ToAddress(), "A1");
AssertEquals(range.EndPos.ToRelativeAddress(), "A1");
AssertEquals(range.EndPos.ToAbsoluteAddress(), "$A$1");
range = new RangePosition("$A$1");
AssertSame(range.Row, 0);
AssertSame(range.Col, 0);
AssertSame(range.Rows, 1);
AssertSame(range.Cols, 1);
AssertEquals(range.ToAddress(), "$A$1:$A$1");
AssertEquals(range.ToRelativeAddress(), "A1:A1");
AssertEquals(range.ToAbsoluteAddress(), "$A$1:$A$1");
AssertEquals(range.StartPos.ToAddress(), "$A$1");
AssertEquals(range.StartPos.ToRelativeAddress(), "A1");
AssertEquals(range.StartPos.ToAbsoluteAddress(), "$A$1");
AssertEquals(range.EndPos.ToAddress(), "$A$1");
AssertEquals(range.EndPos.ToRelativeAddress(), "A1");
AssertEquals(range.EndPos.ToAbsoluteAddress(), "$A$1");
range = new RangePosition("C53:C60");
AssertSame(range.Row, 52);
AssertSame(range.Col, 2);
AssertSame(range.Rows, 8);
AssertSame(range.Cols, 1);
AssertEquals(range.ToAddress(), "C53:C60");
AssertEquals(range.ToRelativeAddress(), "C53:C60");
AssertEquals(range.ToAbsoluteAddress(), "$C$53:$C$60");
AssertEquals(range.StartPos.ToAddress(), "C53");
AssertEquals(range.StartPos.ToRelativeAddress(), "C53");
AssertEquals(range.StartPos.ToAbsoluteAddress(), "$C$53");
AssertEquals(range.EndPos.ToAddress(), "C60");
AssertEquals(range.EndPos.ToRelativeAddress(), "C60");
AssertEquals(range.EndPos.ToAbsoluteAddress(), "$C$60");
range = new RangePosition("$C$53:$C$60");
AssertSame(range.Row, 52);
AssertSame(range.Col, 2);
AssertSame(range.Rows, 8);
AssertSame(range.Cols, 1);
AssertEquals(range.ToAddress(), "$C$53:$C$60");
AssertEquals(range.ToRelativeAddress(), "C53:C60");
AssertEquals(range.ToAbsoluteAddress(), "$C$53:$C$60");
AssertEquals(range.StartPos.ToAddress(), "$C$53");
AssertEquals(range.StartPos.ToRelativeAddress(), "C53");
AssertEquals(range.StartPos.ToAbsoluteAddress(), "$C$53");
AssertEquals(range.EndPos.ToAddress(), "$C$60");
AssertEquals(range.EndPos.ToRelativeAddress(), "C60");
AssertEquals(range.EndPos.ToAbsoluteAddress(), "$C$60");
range = new RangePosition("$C$53:C60");
AssertSame(range.Row, 52);
AssertSame(range.Col, 2);
AssertSame(range.Rows, 8);
AssertSame(range.Cols, 1);
AssertEquals(range.ToAddress(), "$C$53:C60");
AssertEquals(range.ToRelativeAddress(), "C53:C60");
AssertEquals(range.ToAbsoluteAddress(), "$C$53:$C$60");
AssertEquals(range.StartPos.ToAddress(), "$C$53");
AssertEquals(range.StartPos.ToRelativeAddress(), "C53");
AssertEquals(range.StartPos.ToAbsoluteAddress(), "$C$53");
AssertEquals(range.EndPos.ToAddress(), "C60");
AssertEquals(range.EndPos.ToRelativeAddress(), "C60");
AssertEquals(range.EndPos.ToAbsoluteAddress(), "$C$60");
range = new RangePosition("C53:$C$60");
AssertSame(range.Row, 52);
AssertSame(range.Col, 2);
AssertSame(range.Rows, 8);
AssertSame(range.Cols, 1);
AssertEquals(range.ToAddress(), "C53:$C$60");
AssertEquals(range.ToRelativeAddress(), "C53:C60");
AssertEquals(range.ToAbsoluteAddress(), "$C$53:$C$60");
AssertEquals(range.StartPos.ToAddress(), "C53");
AssertEquals(range.StartPos.ToRelativeAddress(), "C53");
AssertEquals(range.StartPos.ToAbsoluteAddress(), "$C$53");
AssertEquals(range.EndPos.ToAddress(), "$C$60");
AssertEquals(range.EndPos.ToRelativeAddress(), "C60");
AssertEquals(range.EndPos.ToAbsoluteAddress(), "$C$60");
// 32768,1048576
range = new RangePosition("$XFD$1048576");
AssertSame(range.Row, 1048575);
AssertSame(range.Col, 16383);
AssertSame(range.Rows, 1);
AssertSame(range.Cols, 1);
AssertEquals(range.ToAddress(), "$XFD$1048576:$XFD$1048576");
AssertEquals(range.ToRelativeAddress(), "XFD1048576:XFD1048576");
AssertEquals(range.ToAbsoluteAddress(), "$XFD$1048576:$XFD$1048576");
AssertEquals(range.StartPos.ToAddress(), "$XFD$1048576");
AssertEquals(range.StartPos.ToRelativeAddress(), "XFD1048576");
AssertEquals(range.StartPos.ToAbsoluteAddress(), "$XFD$1048576");
AssertEquals(range.EndPos.ToAddress(), "$XFD$1048576");
AssertEquals(range.EndPos.ToRelativeAddress(), "XFD1048576");
AssertEquals(range.EndPos.ToAbsoluteAddress(), "$XFD$1048576");
// full rows
range = new RangePosition("1:12");
AssertSame(range.Row, 0);
AssertSame(range.Rows, 12);
AssertSame(range.Col, 0);
AssertSame(range.Cols, -1);
AssertEquals(range.ToAddress(), "1:12");
AssertEquals(range.ToRelativeAddress(), "1:12");
AssertEquals(range.ToAbsoluteAddress(), "$1:$12");
AssertEquals(range.StartPos.ToAddress(), "A1");
AssertEquals(range.StartPos.ToRelativeAddress(), "A1");
AssertEquals(range.StartPos.ToAbsoluteAddress(), "$A$1");
AssertEquals(range.EndPos.ToAddress(), "AVLI12");
AssertEquals(range.EndPos.ToRelativeAddress(), "AVLI12");
AssertEquals(range.EndPos.ToAbsoluteAddress(), "$AVLI$12");
// full rows
range = new RangePosition("$1:$12");
AssertSame(range.Row, 0);
AssertSame(range.Rows, 12);
AssertSame(range.Col, 0);
AssertSame(range.Cols, -1);
AssertEquals(range.ToAddress(), "$1:$12");
AssertEquals(range.ToRelativeAddress(), "1:12");
AssertEquals(range.ToAbsoluteAddress(), "$1:$12");
AssertEquals(range.StartPos.ToAddress(), "A$1");
AssertEquals(range.StartPos.ToRelativeAddress(), "A1");
AssertEquals(range.StartPos.ToAbsoluteAddress(), "$A$1");
AssertEquals(range.EndPos.ToAddress(), "AVLI$12");
AssertEquals(range.EndPos.ToRelativeAddress(), "AVLI12");
AssertEquals(range.EndPos.ToAbsoluteAddress(), "$AVLI$12");
// full rows
range = new RangePosition("$1:12");
AssertSame(range.Row, 0);
AssertSame(range.Rows, 12);
AssertSame(range.Col, 0);
AssertSame(range.Cols, -1);
AssertEquals(range.ToAddress(), "$1:12");
AssertEquals(range.ToRelativeAddress(), "1:12");
AssertEquals(range.ToAbsoluteAddress(), "$1:$12");
AssertEquals(range.StartPos.ToAddress(), "A$1");
AssertEquals(range.StartPos.ToRelativeAddress(), "A1");
AssertEquals(range.StartPos.ToAbsoluteAddress(), "$A$1");
AssertEquals(range.EndPos.ToAddress(), "AVLI12");
AssertEquals(range.EndPos.ToRelativeAddress(), "AVLI12");
AssertEquals(range.EndPos.ToAbsoluteAddress(), "$AVLI$12");
// full rows
range = new RangePosition("1:$12");
AssertSame(range.Row, 0);
AssertSame(range.Rows, 12);
AssertSame(range.Col, 0);
AssertSame(range.Cols, -1);
AssertEquals(range.ToAddress(), "1:$12");
AssertEquals(range.ToRelativeAddress(), "1:12");
AssertEquals(range.ToAbsoluteAddress(), "$1:$12");
// full cols
range = new RangePosition("B:C");
AssertSame(range.Row, 0);
AssertSame(range.Rows, -1);
AssertSame(range.Col, 1);
AssertSame(range.Cols, 2);
AssertEquals(range.ToAddress(), "B:C");
AssertEquals(range.ToRelativeAddress(), "B:C");
AssertEquals(range.ToAbsoluteAddress(), "$B:$C");
// full cols
range = new RangePosition("$B:C");
AssertSame(range.Row, 0);
AssertSame(range.Rows, -1);
AssertSame(range.Col, 1);
AssertSame(range.Cols, 2);
AssertEquals(range.ToAddress(), "$B:C");
AssertEquals(range.ToRelativeAddress(), "B:C");
AssertEquals(range.ToAbsoluteAddress(), "$B:$C");
// full cols
range = new RangePosition("B:$C");
AssertSame(range.Row, 0);
AssertSame(range.Rows, -1);
AssertSame(range.Col, 1);
AssertSame(range.Cols, 2);
AssertEquals(range.ToAddress(), "B:$C");
AssertEquals(range.ToRelativeAddress(), "B:C");
AssertEquals(range.ToAbsoluteAddress(), "$B:$C");
// full cols
range = new RangePosition("$A:$G");
AssertSame(range.Row, 0);
AssertSame(range.Rows, -1);
AssertSame(range.Col, 0);
AssertSame(range.Cols, 7);
AssertEquals(range.ToAddress(), "$A:$G");
AssertEquals(range.ToRelativeAddress(), "A:G");
AssertEquals(range.ToAbsoluteAddress(), "$A:$G");
}
public string Base64Save()
{
var res = "";
foreach (var sheet in Grid.Worksheets)
{
if (res != "")
{
res += ";";
}
res += $"{sheet.Name}:";
var datastream = new MemoryStream();
sheet.SaveRGF(datastream);
datastream.Position = 0;
var databytes = new byte[datastream.Length];
datastream.Read(databytes, 0, (int)datastream.Length);
var xmltext = Encoding.UTF8.GetString(databytes);
var xel = XElement.Parse(xmltext);
var metael = xel.XPathSelectElement("//head/meta");
metael?.SetElementValue(XName.Get("decimalchar"), CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator);
AddWorksheetData(sheet, data => data.Xml = xel);
var wsxd = _wsxdata[sheet];
foreach (var element in xel.XPathSelectElements("//cell[@body-type]"))
{
var btxattr = element.Attribute(XName.Get("body-type"));
if (btxattr == null)
{
continue;
}
var rc = ParseRowCol(element);
if (rc.r < 0 || rc.c < 0)
{
continue;
}
var cell = sheet.Cells[rc.r, rc.c];
if (btxattr.Value.Equals("RadioButtonCell"))
{
RangePosition crange = RangePosition.Empty;
foreach (var range in wsxd.RadioGroups.Keys)
{
if (range.Contains(rc.r, rc.c))
{
crange = range;
break;
}
}
if (crange == RangePosition.Empty)
{
continue;
}
element.SetAttributeValue(XName.Get("radio-group-range"), crange.ToAddress());
}
if (btxattr.Value.Equals("DropdownListCell"))
{
if (!wsxd.EnumCells.ContainsKey(rc))
{
continue;
}
element.SetAttributeValue(XName.Get("enum-name"), wsxd.EnumCells[rc]);
}
}
res += Convert.ToBase64String(Encoding.UTF8.GetBytes(xel.ToString()));
}
return(res);
}