public AST.COMRef makeInputVectorCOMRef(AST.Range rng)
{
// check for the range in the dictionary
AST.COMRef c;
if (!_all_vectors.TryGetValue(rng, out c))
{
// otherwise, create and cache it
Excel.Range com = rng.GetCOMObject(_app);
Excel.Worksheet ws = com.Worksheet;
Excel.Workbook wb = ws.Parent;
string wsname = ws.Name;
string wbname = wb.Name;
var path = new Microsoft.FSharp.Core.FSharpOption <string>(wb.Path);
int width = com.Columns.Count;
int height = com.Rows.Count;
c = new AST.COMRef(rng.getUniqueID(), wb, ws, com, path, wbname, wsname, Microsoft.FSharp.Core.FSharpOption <string> .None, width, height);
_all_vectors.Add(rng, c);
_do_not_perturb.Add(rng, true); // initially mark as not perturbable
}
return(c);
}
private void fastFormulaRead(Excel.Workbook wb)
{
// get names once
var wbfullname = wb.FullName;
var wbname = wb.Name;
var path = wb.Path;
var wbname_opt = new Microsoft.FSharp.Core.FSharpOption <String>(wbname);
var path_opt = String.IsNullOrEmpty(path) ? Microsoft.FSharp.Core.FSharpOption <string> .None : new Microsoft.FSharp.Core.FSharpOption <String>(path);
// init R1C1 extractor
var regex = new Regex("^R([0-9]+)C([0-9]+)$");
// init formula validator
var fn_filter = new Regex("^=", RegexOptions.Compiled);
foreach (Excel.Worksheet worksheet in wb.Worksheets)
{
// get used range
Excel.Range urng = worksheet.UsedRange;
// get dimensions
var left = urng.Column; // 1-based left-hand y coordinate
var right = urng.Columns.Count + left - 1; // 1-based right-hand y coordinate
var top = urng.Row; // 1-based top x coordinate
var bottom = urng.Rows.Count + top - 1; // 1-based bottom x coordinate
// get worksheet name
var wsname = worksheet.Name;
var wsname_opt = new Microsoft.FSharp.Core.FSharpOption <String>(wsname);
// init
int width = right - left + 1;
int height = bottom - top + 1;
// if the used range is a single cell, Excel changes the type
if (left == right && top == bottom)
{
var f = (string)urng.Formula;
if (fn_filter.IsMatch(f))
{
var addr = AST.Address.NewFromR1C1(top, left, wsname_opt, wbname_opt, path_opt);
_formulas.Add(addr, f);
_f2v.Add(addr, new HashSet <AST.Range>());
_f2i.Add(addr, new HashSet <AST.Address>());
}
}
else
{
// array read of formula cells
// note that this is a 1-based 2D multiarray
object[,] formulas = urng.Formula;
// for every cell that is actually a formula, add to
// formula dictionary & init formula lookup dictionaries
for (int c = 1; c <= width; c++)
{
for (int r = 1; r <= height; r++)
{
var f = (string)formulas[r, c];
if (fn_filter.IsMatch(f))
{
var addr = AST.Address.NewFromR1C1(r + top - 1, c + left - 1, wsname_opt, wbname_opt, path_opt);
_formulas.Add(addr, f);
_f2v.Add(addr, new HashSet <AST.Range>());
_f2i.Add(addr, new HashSet <AST.Address>());
}
}
}
}
// for each COM object in the used range, create an address object
// WITHOUT calling any methods on the COM object itself
int x_old = -1;
int x = -1;
int y = 0;
foreach (Excel.Range cell in urng)
{
// The basic idea here is that we know how Excel iterates over collections
// of cells. The Excel.Range returned by UsedRange is always rectangular.
// Thus we can calculate the addresses of each COM cell reference without
// needing to incur the overhead of actually asking it for its address.
x = (x + 1) % width;
// increment y if x wrapped (x < x_old or x == x_old when width == 1)
y = x <= x_old ? y + 1 : y;
int c = x + left;
int r = y + top;
var addr = AST.Address.NewFromR1C1(r, c, wsname_opt, wbname_opt, path_opt);
var formula = _formulas.ContainsKey(addr) ? new Microsoft.FSharp.Core.FSharpOption <string>(_formulas[addr]) : Microsoft.FSharp.Core.FSharpOption <string> .None;
var cr = new AST.COMRef(addr.A1FullyQualified(), wb, worksheet, cell, path_opt, wbname, wsname, formula, 1, 1);
_all_cells.Add(addr, cr);
x_old = x;
}
}
}
private void fastFormulaRead(Excel.Workbook wb)
{
// get names once
var wbfullname = wb.FullName;
var wbname = wb.Name;
var path = wb.Path;
var wbname_opt = new Microsoft.FSharp.Core.FSharpOption<String>(wbname);
var path_opt = String.IsNullOrEmpty(path) ? Microsoft.FSharp.Core.FSharpOption<string>.None : new Microsoft.FSharp.Core.FSharpOption<String>(path);
// init R1C1 extractor
var regex = new Regex("^R([0-9]+)C([0-9]+)$");
// init formula validator
var fn_filter = new Regex("^=", RegexOptions.Compiled);
foreach (Excel.Worksheet worksheet in wb.Worksheets)
{
// get used range
Excel.Range urng = worksheet.UsedRange;
// get dimensions
var left = urng.Column; // 1-based left-hand y coordinate
var right = urng.Columns.Count + left - 1; // 1-based right-hand y coordinate
var top = urng.Row; // 1-based top x coordinate
var bottom = urng.Rows.Count + top - 1; // 1-based bottom x coordinate
// get worksheet name
var wsname = worksheet.Name;
var wsname_opt = new Microsoft.FSharp.Core.FSharpOption<String>(wsname);
// init
int width = right - left + 1;
int height = bottom - top + 1;
// if the used range is a single cell, Excel changes the type
if (left == right && top == bottom)
{
var f = (string)urng.Formula;
if (fn_filter.IsMatch(f))
{
var addr = AST.Address.NewFromR1C1(top, left, wsname_opt, wbname_opt, path_opt);
_formulas.Add(addr, f);
_f2v.Add(addr, new HashSet<AST.Range>());
_f2i.Add(addr, new HashSet<AST.Address>());
}
}
else
{
// array read of formula cells
// note that this is a 1-based 2D multiarray
object[,] formulas = urng.Formula;
// for every cell that is actually a formula, add to
// formula dictionary & init formula lookup dictionaries
for (int c = 1; c <= width; c++)
{
for (int r = 1; r <= height; r++)
{
var f = (string)formulas[r, c];
if (fn_filter.IsMatch(f))
{
var addr = AST.Address.NewFromR1C1(r + top - 1, c + left - 1, wsname_opt, wbname_opt, path_opt);
_formulas.Add(addr, f);
_f2v.Add(addr, new HashSet<AST.Range>());
_f2i.Add(addr, new HashSet<AST.Address>());
}
}
}
}
// for each COM object in the used range, create an address object
// WITHOUT calling any methods on the COM object itself
int x_old = -1;
int x = -1;
int y = 0;
foreach (Excel.Range cell in urng)
{
// The basic idea here is that we know how Excel iterates over collections
// of cells. The Excel.Range returned by UsedRange is always rectangular.
// Thus we can calculate the addresses of each COM cell reference without
// needing to incur the overhead of actually asking it for its address.
x = (x + 1) % width;
// increment y if x wrapped (x < x_old or x == x_old when width == 1)
y = x <= x_old ? y + 1 : y;
int c = x + left;
int r = y + top;
var addr = AST.Address.NewFromR1C1(r, c, wsname_opt, wbname_opt, path_opt);
var formula = _formulas.ContainsKey(addr) ? new Microsoft.FSharp.Core.FSharpOption<string>(_formulas[addr]) : Microsoft.FSharp.Core.FSharpOption<string>.None;
var cr = new AST.COMRef(addr.A1FullyQualified(), wb, worksheet, cell, path_opt, wbname, wsname, formula, 1, 1);
_all_cells.Add(addr, cr);
x_old = x;
}
}
}
public AST.COMRef makeInputVectorCOMRef(AST.Range rng)
{
// check for the range in the dictionary
AST.COMRef c;
if (!_all_vectors.TryGetValue(rng, out c))
{
// otherwise, create and cache it
Excel.Range com = rng.GetCOMObject(_app);
Excel.Worksheet ws = com.Worksheet;
Excel.Workbook wb = ws.Parent;
string wsname = ws.Name;
string wbname = wb.Name;
var path = new Microsoft.FSharp.Core.FSharpOption<string>(wb.Path);
int width = com.Columns.Count;
int height = com.Rows.Count;
c = new AST.COMRef(rng.getUniqueID(), wb, ws, com, path, wbname, wsname, Microsoft.FSharp.Core.FSharpOption<string>.None, width, height);
_all_vectors.Add(rng, c);
_do_not_perturb.Add(rng, true); // initially mark as not perturbable
}
return c;
}
