/// <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);
}
}
/// <summary>
/// Call Web service and display the results to the NameRange control
/// </summary>
/// <param name="city">Search City</param>
/// <param name="country">Search Country</param>
public void DisplayWebServiceResult(string city, string country)
{
// Get Name Range and Clear current display
NamedRange range = (NamedRange)this.Controls["Data"];
range.Clear();
// Initialize the value of x
int x = 0;
try
{
// Initialize a new instance of Service Client
using (GlobalWeatherSoapClient weatherclien = new GlobalWeatherSoapClient())
{
// Call Web service method to Get Weather Data
string xmlweatherresult = weatherclien.GetWeather(city, country);
// Load an XElement from a string that contains XML data
var xmldata = XElement.Parse(xmlweatherresult);
// Query the Name and value of Weather
var query = from weather in xmldata.Elements()
select new
{
weather.Name,
weather.Value
};
if (query.Count() > 0)
{
foreach (var item in query)
{
// Use RefersToR1C1 property to change the range that a NameRange control refers to
range.RefersToR1C1 = String.Format("=R1C1:R{0}C2", query.Count());
// Update data in range.
// Excel uses 1 as the base for index.
((Excel.Range)range.Cells[x + 1, 1]).Value2 = item.Name.ToString();
((Excel.Range)range.Cells[x + 1, 2]).Value2 = item.Value.ToString();
x++;
if (x == query.Count() - 1)
{
break;
}
}
}
}
}
catch
{
this.Range["A1"].Value2 = "Input City or Country is error, Please check them again";
// -16776961 is represent for red
this.Range["A1"].Font.Color = -16776961;
}
}
/// <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);
}
internal static NamedRange DefineNamedRange(IWin32Window owner, Worksheet sheet, string name, string comment, RangePosition range)
{
NamedRange namedRange = null;
try
{
namedRange = sheet.DefineNamedRange(name, range);
namedRange.Comment = comment;
}
catch
{
MessageBox.Show(owner, LangRes.LangResource.Msg_Create_Named_Range_Failed,
LangRes.LangResource.Msg_Create_Named_Range_Failed_Title, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(namedRange);
}
static void Main(string[] args)
{
Spreadsheet spreadsheet = new Spreadsheet();
Table sheet1 = new Table("Sheet1");
spreadsheet.Tables.Add(sheet1);
NamedRange range1 = new NamedRange("MyRange");
range1.CellRangeAddress = "$Sheet1.$A$1:.$C$1";
range1.BaseCellAddress = "$Sheet1.$C$1";
spreadsheet.NamedElements.Add(range1);
spreadsheet.Save("c:\\test\\output.ods", true);
}
private void Sheet1_Startup(object sender, System.EventArgs e)
{
string rangeA = $"A1:A{symbols.Length+1}";
string rangeB = $"B1:B{symbols.Length+1}";
namedRangeA = this.Controls.AddNamedRange(this.Range[rangeA], "NamedRangeA");
namedRangeB = this.Controls.AddNamedRange(this.Range[rangeB], "NamedRangeB");
namedRangeA.Cells[1].Value2 = "Symbol";
namedRangeB.Cells[1].Value2 = "Price";
for (int i = 0; i < symbols.Length; i++)
{
namedRangeA.Cells[i + 2].Value2 = symbols[i];
namedRangeB.Cells[i + 2].NumberFormat = "$#,##0.00";
}
Button button = new Button();
this.Controls.AddControl(button, 200, 5, 100, 30, "MyButton");
button.Text = "Update Stock";
button.Click += new EventHandler(button_Click);
}
private void btnNew_Click(object sender, EventArgs e)
{
using (var dlg = new DefineNamedRangeDialog())
{
if (dlg.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
{
NamedRange range = DefineNamedRange(this, this.grid.CurrentWorksheet,
dlg.RangeName, dlg.Comment, dlg.Range);
if (range != null)
{
lstRanges.Items.Add(new ListViewItem(new string[] { range.Name, range.Position.ToAddress() })
{
Tag = range
});
if (range.Worksheet == this.grid.CurrentWorksheet)
{
this.grid.CurrentWorksheet.SelectionRange = range;
}
}
}
}
}
/// <summary>
/// Create event argument instance with named range instance
/// </summary>
/// <param name="namedRange">named range instance</param>
public NamedRangeAddedEventArgs(NamedRange namedRange)
: this(namedRange, namedRange.Name)
{
this.NamedRange = namedRange;
}
public object Evaluate(ReoGridCell cell, ICompiledFormula cformula)
{
var grid = this.Workbook;
if (grid == null)
{
return(null);
}
var formulaContext = (ReoScriptCompiledFormula)cformula;
//var cell = formulaContext.Cell;
var formula = formulaContext.Formula;
List <ReferenceRange> referencedRanges = formulaContext.ReferencedCellOrRanges as List <ReferenceRange>;
if (referencedRanges == null)
{
formulaContext.ReferencedCellOrRanges = referencedRanges = new List <ReferenceRange>();
}
else
{
referencedRanges.Clear();
}
// todo: improve: only create script context once
// when set data to a range
var ctx = grid.Srm.CreateContext();
// create an global variable getter
ctx.ExternalVariableGetter = (id) =>
{
#if FORMULA_CELL_INSTANCE_REF
if (id.StartsWith("$"))
{
var address = id.Substring(1);
if (ReoGridPos.IsValidAddress(address))
{
var pos = new ReoGridPos(address);
return(new RSCellObject(this, pos, cells[pos.Row, pos.Col]));
}
else
{
return(null);
}
}
else
#endif // FORMULA_CELL_INSTANCE_REF
if (ReoGridPos.IsValidAddress(id))
{
var pos = new ReoGridPos(id);
referencedRanges.Add(new ReferenceRange(grid, pos));
var cell = grid.GetCell(pos);
return(cell == null ? 0 : cell.InnerData);
}
else
{
NamedRange range = grid.GetNamedRange(id);
if (range != null)
{
referencedRanges.Add(range);
var referencedCell = grid.GetCell(range.StartPos);
return((referencedCell == null || referencedCell.InnerData == null) ? 0 : referencedCell.InnerData);
}
else
{
return(null);
}
}
};
try
{
// preprocess range syntax
formula = RGUtility.RangeReferenceRegex.Replace(formula, (m) =>
{
if (m.Groups["to_col"].Length > 0 && m.Groups["to_row"].Length > 0 &&
m.Groups["from_col"].Length > 0 && m.Groups["from_row"].Length > 0)
{
// range
int fromRow = -1;
if (!int.TryParse(m.Groups["from_row"].Value, out fromRow))
{
return("null");
}
fromRow--;
int toRow = -1;
if (!int.TryParse(m.Groups["to_row"].Value, out toRow))
{
return("null");
}
toRow--;
int fromCol = RGUtility.GetNumberOfChar(m.Groups["from_col"].Value);
int toCol = RGUtility.GetNumberOfChar(m.Groups["to_col"].Value);
if (fromRow < 0)
{
fromRow = 0;
}
if (fromCol < 0)
{
fromCol = 0;
}
if (toRow > grid.RowCount - 1)
{
toRow = grid.RowCount - 1;
}
if (toCol > grid.RowCount - 1)
{
toCol = grid.ColumnCount - 1;
}
ReoGridRange range = new ReoGridRange(fromRow, fromCol, toRow - fromRow + 1, toCol - fromCol + 1);
referencedRanges.Add(new ReferenceRange(grid, range));
return(string.Format("new Range({0},{1},{2},{3})", range.Row, range.Col, range.Rows, range.Cols));
}
else
{
return(m.Value);
}
});
return(grid.Srm.CalcExpression(formula, ctx));
}
catch (ReoScriptException ex)
{
Logger.Log("formula", string.Format("error to evaluate formula: ", ex.Message));
throw new FormulaEvalutionException(ex, "#ERR: " + ex.Message);
}
}
public DirtyValue(DirtyFlag dirtyFlag, NamedRange range, float val)
{
this.range = range;
this.dirtyFlag = dirtyFlag;
this.val = val;
}
public DirtyValue(DirtyFlag dirtyFlag, NamedRange range, float val ) {
this.range = range;
this.dirtyFlag = dirtyFlag;
this.val = val;
}
public void UpdateSpreadSheetData()
{
UserCredential credential;
using (var stream =
new FileStream("Google/credentials.json", FileMode.Open, FileAccess.Read))
{
// The file token.json stores the user's access and refresh tokens, and is created
// automatically when the authorization flow completes for the first time.
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Define request parameters.
String spreadsheetId = "1BODs_1wmA62KoZUN0dalOCTIPQpvy4YaxffSKY07I2A";
NamedRange nr = new NamedRange();
nr.Name = "LastNonEmptyCell";
SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, nr.Name);
ValueRange response = request.Execute();
string cell = (string)response.Values[0][0];
String range = "Data!" + cell;
ValueRange vr = new ValueRange();
vr.MajorDimension = "COLUMNS";
vr.Range = range;
Quotes.Add(DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss"));
vr.Values = new List <IList <Object> >()
{
Quotes
};
SpreadsheetsResource.ValuesResource.AppendRequest update =
service.Spreadsheets.Values.Append(vr, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
update.Execute();
nr.Name = "LastUpdatedOn";
request = service.Spreadsheets.Values.Get(spreadsheetId, nr.Name);
response = request.Execute();
vr.Range = response.Range;
var time = new List <object>()
{
DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss")
};
vr.Values = new List <IList <Object> >()
{
time
};
SpreadsheetsResource.ValuesResource.UpdateRequest updateTime =
service.Spreadsheets.Values.Update(vr, spreadsheetId, vr.Range);
updateTime.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
updateTime.Execute();
Console.WriteLine("Done");
}
public static string Ones(NamedRange range)
{
return("=1");
}
