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