/// <summary>
/// Create data serial by specified worksheet instance and data range.
/// </summary>
/// <param name="dataSource">Data source to read chart data from worksheet.</param>
/// <param name="worksheet">Instance of worksheet that contains the data to be read.</param>
/// <param name="labelAddress">The address to locate label of serial on worksheet.</param>
/// <param name="addressOrName">Serial data specified by address position or range's name.</param>
public WorksheetChartDataSerial(WorksheetChartDataSource dataSource, Worksheet worksheet, string labelAddress, string addressOrName)
: this(dataSource, worksheet, new CellPosition(labelAddress))
{
if (RangePosition.IsValidAddress(addressOrName))
{
this.dataRange = new RangePosition(addressOrName);
}
else if (NamedRange.IsValidName(addressOrName))
{
if (this.worksheet != null)
{
if (worksheet.TryGetNamedRange(addressOrName, out var range))
{
this.dataRange = range;
}
else
{
throw new InvalidAddressException(addressOrName);
}
}
else
{
throw new ReferenceObjectNotAssociatedException("Data source must associate to valid worksheet instance.");
}
}
else
{
throw new InvalidAddressException(addressOrName);
}
}
private void btnOK_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtName.Text))
{
MessageBox.Show(LangRes.LangResource.Msg_Input_Name_Of_Range);
txtName.Focus();
return;
}
if (string.IsNullOrEmpty(txtRange.Text) ||
!RangePosition.IsValidAddress(txtRange.Text))
{
MessageBox.Show(LangRes.LangResource.Msg_Invalid_Address_Of_Range);
txtRange.Focus();
return;
}
RangeName = txtName.Text;
Comment = txtComment.Text;
Range = new RangePosition(txtRange.Text);
DialogResult = System.Windows.Forms.DialogResult.OK;
Close();
}
/// <summary>
/// Get range information from script value
/// </summary>
/// <param name="sheet">worksheet instance</param>
/// <param name="arg">script object to be converted</param>
/// <returns></returns>
public static RangePosition GetRangeFromValue(Worksheet sheet, object arg)
{
if (arg is RangePosition)
{
return((RangePosition)arg);
}
else if (arg is string)
{
var addr = (string)arg;
NamedRange namedRange;
if (RangePosition.IsValidAddress(addr))
{
return(new RangePosition(addr));
}
else if (NamedRange.IsValidName(addr) &&
sheet.TryGetNamedRange(addr, out namedRange))
{
return((RangePosition)namedRange);
}
else
{
throw new InvalidAddressException(addr);
}
}
else if (arg is ReferenceRange)
{
return(((ReferenceRange)arg).Position);
}
else if (arg is RSSelectionObject)
{
return(sheet.SelectionRange);
}
else if (arg is RSRangeObject)
{
return(((RSRangeObject)arg).Range);
}
ObjectValue obj = arg as ObjectValue;
if (obj == null)
{
return(RangePosition.Empty);
}
RangePosition range = RangePosition.Empty;
range.Row = ScriptRunningMachine.GetIntValue(obj["row"]);
range.Col = ScriptRunningMachine.GetIntValue(obj["col"]);
range.Rows = ScriptRunningMachine.GetIntValue(obj["rows"]);
range.Cols = ScriptRunningMachine.GetIntValue(obj["cols"]);
return(range);
}
/// <summary>
/// Create an action to set styles into specified range
/// </summary>
/// <param name="address">Address to locate the cell or range on spreadsheet (Cannot specify named range for this method)</param>
/// <param name="style">Styles to be set</param>
/// <exception cref="InvalidAddressException">Throw if specified address or name is invalid</exception>
public SetRangeStyleAction(string address, WorksheetRangeStyle style)
{
if (RangePosition.IsValidAddress(address))
{
this.Range = new RangePosition(address);
}
else
{
throw new InvalidAddressException(address);
}
this.style = style;
}
void txtAddress_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
string id = addressBox.Text;
// avoid to directly use trim, it will create new string even nothing to be trimmed
if (id.StartsWith(" ") || id.EndsWith(" "))
{
id = id.Trim();
}
if (RangePosition.IsValidAddress(id))
{
this.worksheet.SelectionRange = new RangePosition(id);
workbook.Focus();
}
else if (RGUtility.IsValidName(id))
{
var refRange = this.worksheet.GetNamedRange(id);
if (refRange != null)
{
this.worksheet.SelectionRange = refRange;
workbook.Focus();
}
else
{
try
{
this.worksheet.DefineNamedRange(id, this.worksheet.SelectionRange);
workbook.Focus();
}
catch (NamedRangeAlreadyDefinedException)
{
// should be not reached
MessageBox.Show("Another range with same name does already exist.");
}
}
}
}
else if (e.KeyCode == Keys.Down)
{
PushDown();
}
else if (e.KeyCode == Keys.Escape)
{
workbook.Focus();
}
}
public static FormulaValue Indirect(Cell cell, FormulaValue[] args)
{
if (args[0].type != FormulaValueType.String)
{
throw new FormulaTypeMismatchException(cell);
}
string address = (string)args[0].value;
if (CellPosition.IsValidAddress(address))
{
var pos = new CellPosition(address);
return(cell == null || cell.Worksheet == null ? null : Evaluator.CreateFormulaValue(cell.Worksheet.GetCell(pos)));
}
else if (RangePosition.IsValidAddress(address))
{
return(new RangePosition(address));
}
else
{
throw new FormulaTypeMismatchException(cell);
}
}
public void ValidRelativeAddress()
{
// cell
AssertEquals(CellPosition.IsValidAddress("x1"), false, "x1");
AssertEquals(CellPosition.IsValidAddress("-x1"), false);
AssertEquals(CellPosition.IsValidAddress("A"), false);
AssertEquals(CellPosition.IsValidAddress("1"), false);
AssertEquals(CellPosition.IsValidAddress("B1:"), false);
AssertEquals(CellPosition.IsValidAddress(":A1"), false);
AssertEquals(CellPosition.IsValidAddress("A1"), true);
AssertEquals(CellPosition.IsValidAddress(" ZZZ777 "), true);
AssertEquals(CellPosition.IsValidAddress("AZHL1048576"), true);
// range address
AssertEquals(RangePosition.IsValidAddress("x1:C1"), false, "x1:C1");
AssertEquals(RangePosition.IsValidAddress("-x1:C2"), false);
AssertEquals(RangePosition.IsValidAddress("A:B"), true);
AssertEquals(RangePosition.IsValidAddress("1"), false);
AssertEquals(RangePosition.IsValidAddress("1:2"), true, "1:2");
AssertEquals(RangePosition.IsValidAddress("1:X2"), false, "1:X2");
AssertEquals(RangePosition.IsValidAddress("-A1:X2"), false);
AssertEquals(RangePosition.IsValidAddress("$A1:X2"), true);
AssertEquals(RangePosition.IsValidAddress("B1:"), false);
AssertEquals(RangePosition.IsValidAddress(":A1"), false);
AssertEquals(RangePosition.IsValidAddress("A1:C3"), true);
AssertEquals(RangePosition.IsValidAddress("A1:A1"), true);
AssertEquals(RangePosition.IsValidAddress(" A1:D5 "), true);
AssertEquals(RangePosition.IsValidAddress("A1:AZHL1048576"), true);
AssertEquals(RangePosition.IsValidAddress("A1"), true);
AssertEquals(RangePosition.IsValidAddress(" A1 "), true);
AssertEquals(RangePosition.IsValidAddress("AZHL1048576 "), true);
}
public void Base64Load(string data)
{
var sheets = data.Split(';');
foreach (var sheetdata in sheets)
{
var nameAndSheet = sheetdata.Split(':');
var name = nameAndSheet[0];
var sheetbase64 = nameAndSheet[1];
var sheet = Grid.CreateWorksheet(name);
var databytes = Convert.FromBase64String(sheetbase64);
var xmltext = Encoding.UTF8.GetString(databytes);
var xel = XElement.Parse(xmltext);
AddWorksheetData(sheet, xdata => xdata.Xml = xel);
var xml = new XmlDocument();
xml.LoadXml(xmltext);
var origdecsep = xml["grid"]?["head"]?["meta"]?["decimalchar"]?.InnerText;
if (origdecsep == null)
{
var origcultureid = xml["grid"]?["head"]?["meta"]?["culture"]?.InnerText ?? "en-US";
var origculture = new CultureInfo(origcultureid);
origdecsep = origculture.NumberFormat.NumberDecimalSeparator;
}
ConvertCulture(xml, origdecsep);
databytes = Encoding.UTF8.GetBytes(xml.InnerXml);
var datastream = new MemoryStream(databytes);
sheet.LoadRGF(datastream);
Grid.AddWorksheet(sheet);
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;
}
if (btxattr.Value.Equals("VerticalProgressCell"))
{
SetCellBodyFromXml(sheet, element, new VerticalProgressCell());
}
if (btxattr.Value.Equals("HorizontalProgressCell"))
{
SetCellBodyFromXml(sheet, element, new HorizontalProgressCell());
}
if (btxattr.Value.Equals("RadioButtonCell"))
{
var rc = ParseRowCol(element);
var celldata = sheet.Cells[rc.r, rc.c].Data?
.ToString()
.Equals("True", StringComparison.InvariantCultureIgnoreCase) ?? false;
var button = new RadioButtonCell
{
IsChecked = celldata
};
var groupattr = element.Attribute(XName.Get("radio-group-range"));
if (groupattr != null && RangePosition.IsValidAddress(groupattr.Value))
{
var range = new RangePosition(groupattr.Value);
if (wsxd.RadioGroups.ContainsKey(range))
{
button.RadioGroup = wsxd.RadioGroups[range];
}
else
{
var radiogroup = new RadioButtonGroup();
button.RadioGroup = radiogroup;
wsxd.RadioGroups.Add(range, radiogroup);
}
}
button.Click += (sender, eventArgs) => SaveChanges();
SetCellBodyFromXml(sheet, element, button);
}
if (btxattr.Value.Equals("CheckBoxCell"))
{
var rc = ParseRowCol(element);
var celldata = sheet.Cells[rc.r, rc.c].Data?
.ToString()
.Equals("True", StringComparison.InvariantCultureIgnoreCase) ?? false;
var button = new CheckBoxCell
{
IsChecked = celldata
};
button.Click += (sender, eventArgs) => SaveChanges();
SetCellBodyFromXml(sheet, element, button);
}
if (btxattr.Value.Equals("DropdownListCell"))
{
var enumattr = element.Attribute(XName.Get("enum-name"));
if (enumattr != null)
{
var rc = ParseRowCol(element);
if (rc.r >= 0 && rc.c >= 0)
{
var button = new DropdownListCell(GetEnumEntries(enumattr.Value));
button.SelectedItemChanged += (sender, args) => SaveChanges();
sheet.Cells[rc.r, rc.c].Body = button;
if (wsxd.EnumCells.ContainsKey(rc))
{
wsxd.EnumCells[rc] = enumattr.Value;
}
else
{
wsxd.EnumCells.Add(rc, enumattr.Value);
}
}
}
}
var formulael = element.Element(XName.Get("formula"));
if (formulael != null)
{
var rc = ParseRowCol(element);
if (rc.r >= 0 && rc.c >= 0)
{
sheet.SetCellFormula(rc.r, rc.c, formulael.Value);
}
}
}
}
}