/// <summary>
/// Gets the first selected cells in the current workbook
/// </summary>
/// <param name="wb">workbook model</param>
/// <returns>List of Cell or null if no cell is selected</returns>
public Cell GetFirstSelectedCell(WorkbookModel wb)
{
var selectedCell = (wb.Workbook.Application.Selection as MSExcel.Range).Cells.Cells[1] as MSExcel.Range;
var currentLocation = (selectedCell.Parent as MSExcel.Worksheet).Name + "!" + selectedCell.Address;
return(wb.GetCell(currentLocation));
}
/// <summary>
/// Get the a1Adress of a cell name
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="name">cell location name</param>
/// <returns>cell location in a1 style</returns>
public String GetA1Adress(WorkbookModel wb, String name)
{
foreach (Name n in wb.Workbook.Application.Names)
{
if (n.Name == name)
{
return n.RefersTo as String;
}
}
return name;
}
public XElement LoadXMLPart(WorkbookModel workbook, string id)
{
var part = GetCustomXMLPart(workbook, id);
if (part != null)
{
var result = XElement.Parse(part.XML);
Debug.WriteLine("Loaded from the customXMLParts with ID = '" + id + "'");
//Debug.WriteLine(result);
return(result);
}
return(null);
}
public XElement LoadXMLPart(WorkbookModel workbook, string id)
{
var part = this.GetCustomXLPart(workbook, id);
if (part != null)
{
var result = XElement.Parse(part.XML).Elements().First();
Debug.WriteLine(result.ToString());
return result;
}
else
{
return null;
}
}
private XElement CreateRuleElements(WorkbookModel n)
{
if (n.Rules.Count > 0)
{
var rulesPolicy = new XElement("customRulesPolicy");
var rules = new XElement("rules");
foreach (var rule in n.Rules)
{
rules.Add(Visit(rule));
}
rulesPolicy.Add(rules);
return(rulesPolicy);
}
return(null);
}
public object Visit(WorkbookModel n)
{
var root = new XElement("SIF.Scenario");
root.Add(new XAttribute("Title", NullCheck(n.Title)));
root.Add(new XAttribute("Spreadsheet", NullCheck(n.Spreadsheet)));
root.Add(new XAttribute("PolicyPath", NullCheck(n.PolicyPath)));
//save scenarios
foreach (var scen in n.Scenarios)
{
if (scen != null) root.Add(scen.Accept(this) as XElement);
}
return root;
}
private XElement CreateScenarioElements(WorkbookModel n)
{
if (n.Scenarios.Count > 0)
{
var dynamicPolicy = new XElement("dynamicTestingPolicy");
// scenarios
var scenarios = new XElement("scenarios");
foreach (var scenario in n.Scenarios)
{
scenarios.Add(Visit(scenario));
}
dynamicPolicy.Add(scenarios);
return(dynamicPolicy);
}
return(null);
}
private void Application_WorkbookActivate(Microsoft.Office.Interop.Excel.Workbook Wb)
{
// This method is called whenever a workbook comes to the front
// Does not necessarily need to be a workbook that is persisted on the disk
var workbook = DataModel.Instance.WorkbookModels.Where(p => object.ReferenceEquals(p.Workbook, Wb)).FirstOrDefault();
if (workbook == null)
{
workbook = new WorkbookModel(Wb);
DataModel.Instance.WorkbookModels.Add(workbook);
DataModel.Instance.CurrentWorkbook = workbook;
DataModel.Instance.CurrentWorkbook.LoadExtraInformation();
/// create shared pane
var sharedPaneContainer = new SharedPaneContainer();
var sharedPane = this.CustomTaskPanes.Add(sharedPaneContainer, "Inspection");
sharedPaneContainer.VisibleChanged += SharedPaneContainer_VisibleChanged;
sharedPaneContainer.SharedPane.DataContext = workbook;
sharedPane.Width = 340;
this.TaskPanes.Add(new Tuple<WorkbookModel, string>(workbook, "shared Pane"), sharedPane);
// create findings pane
var violationViewContainer = new ViolationsViewContainer();
var taskPane = this.CustomTaskPanes.Add(violationViewContainer, "Violations");
violationViewContainer.VisibleChanged += FindingsPaneContainer_VisibleChanged;
violationViewContainer.ViolationsView.DataContext = workbook;
this.TaskPanes.Add(new Tuple<WorkbookModel, string>(workbook, "Violations"), taskPane);
//create scenario detail pane
var scenarioDetailPainContainer = new ScenarioDetailPaneContainer();
var scenarioDetailPane = this.CustomTaskPanes.Add(scenarioDetailPainContainer, "Scenario");
scenarioDetailPane.Width = 260;
scenarioDetailPainContainer.VisibleChanged += ScenarioDetailPaneContainer_VisibleChanged;
this.TaskPanes.Add(new Tuple<WorkbookModel, string>(workbook, "Scenario Details"), scenarioDetailPane);
//add selection changed event handler for ribbon
Wb.Application.SheetSelectionChange += DataModel.Instance.WorkbookSelectionChangedEventHandler;
workbook.CellDefinitionChange += DataModel.Instance.CellDefinitionChangedEventHandler;
}
DataModel.Instance.CurrentWorkbook = workbook;
}
/// <summary>
/// This async method is called by the workbook model. it will be a silent running request
/// </summary>
internal async void doInspection(WorkbookModel workbook, string policyFile, string spreadsheetFile)
{
// initalize response string
string responseString = null;
// open policy and spreadsheet files save temporarily
var policyStream = File.Open(policyFile, FileMode.Open);
HttpContent policyContent = new StreamContent(policyStream);
var spreadsheetStream = File.Open(spreadsheetFile, FileMode.Open);
HttpContent spreadsheetContent = new StreamContent(spreadsheetStream);
// Submit the form using HttpClient and
// create form data as Multipart (enctype="multipart/form-data")
using (var client = new HttpClient())
using (var formData = new MultipartFormDataContent())
{
// Add the HttpContent objects to the form data
// <input type="text" name="filename" />
formData.Add(policyContent, "policy", policyFile);
formData.Add(spreadsheetContent, "spreadsheet", spreadsheetFile);
// Actually invoke the request to the server
// equivalent to (action="{url}" method="post")
try
{
var response = client.PostAsync(Settings.Default.SifServerUrl + "/ooxml", formData).Result;
if (response.IsSuccessStatusCode)
{
// get the responding xml as string
responseString = await response.Content.ReadAsStringAsync();
var fileName = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) +
Path.DirectorySeparatorChar + "inspectionResponse.xml";
File.WriteAllText(fileName, responseString);
}
}
catch (Exception)
{
ScanHelper.ScanUnsuccessful(Resources.Error_NoConnectionToServer);
return;
}
}
workbook.Load(responseString);
}
/// <summary>
/// Gets the sif cell name of a cell location in a1style
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="a1Adress">cell location in a1 style</param>
/// <returns>sif cell name</returns>
public String GetSIFCellName(WorkbookModel wb, String a1Adress)
{
// Important: There might be more than just one name for this cell!
var name = new CellLocation(wb.Workbook, a1Adress).ScenarioNames.FirstOrDefault();
if (name != null) return name.Name;
else return string.Empty;
//foreach (Name n in wb.Workbook.Application.Names)
//{
// if (n.RefersTo as String == a1Adress
// && n.Name.Contains(Properties.Settings.Default.CellNameTag))
// {
// return n.Name;
// }
//}
//return String.Empty;
}
private XElement CreateSanityElements(WorkbookModel n)
{
var root = new XElement("sanityChecks");
// @TODO
/*
* XElement checking = CreateSanityCheckingCells(n);
* if (!checking.HasElements) return null;
*
* root.Add(checking);
* root.Add(CreateSanityValueCells(n));
* root.Add(CreateSanityConstraintCells(n));
* root.Add(CreateSanityExplanationCells(n));
*
* root.Add(new XElement("sanityWarnings", n.SanityWarnings));
*/
return(root);
}
/// <summary>
/// Determines whether the specified object is equal to the current object.
/// </summary>
/// <param name="obj">The object to compare with the current object.</param>
/// <returns>true if the specified object is equal to the current object; otherwise, false.</returns>
public override bool Equals(object obj)
{
WorkbookModel other = obj as WorkbookModel;
if ((object)other == null)
{
return(false);
}
return(this.Title == other.Title &&
this.Spreadsheet == other.Spreadsheet &&
this.PolicyPath == other.PolicyPath &&
this.Policy == other.Policy &&
this.InputCells.SequenceEqual(other.InputCells) &&
this.IntermediateCells.SequenceEqual(other.IntermediateCells) &&
this.OutputCells.SequenceEqual(other.OutputCells) &&
this.IgnoredViolations.SequenceEqual(other.IgnoredViolations) &&
this.Scenarios.SequenceEqual(other.Scenarios) &&
Object.ReferenceEquals(this.Workbook, other.Workbook));
}
private Microsoft.Office.Core.CustomXMLPart GetCustomXLPart(WorkbookModel workbook, string id)
{
Microsoft.Office.Core.CustomXMLPart resultPart = null;
foreach (Microsoft.Office.Core.CustomXMLPart part in workbook.Workbook.CustomXMLParts)
{
try
{
var xml = XElement.Parse(part.XML);
if (xml.Name == XName.Get(id))
{
resultPart = part;
break;
}
}
catch (Exception e)
{ }
}
return resultPart;
}
public void SaveXMLPart(WorkbookModel workbook, XElement root, string id)
{
if (root == null) return;
var masterRoot = new XElement(id);
masterRoot.Add(new XAttribute("company", "University of Stuttgart, ISTE"));
masterRoot.Add(new XAttribute("product", "Spreadsheet Inspection Framework (SIF"));
masterRoot.Add(root);
//clear old
var oldPart = this.GetCustomXLPart(workbook, id);
if (oldPart != null)
{
oldPart.Delete();
}
//save
var scenarioXMLPart = workbook.Workbook.CustomXMLParts.Add(masterRoot.ToString());
Debug.WriteLine(masterRoot.ToString());
}
public void SaveXMLPart(WorkbookModel workbook, XElement root, string id)
{
if (root == null)
{
return;
}
//clear old
var oldPart = GetCustomXMLPart(workbook, id);
if (oldPart != null)
{
oldPart.Delete();
}
//save
var scenarioXMLPart = workbook.Workbook.CustomXMLParts.Add(root.ToString());
Debug.WriteLine("Saved customXMLPart with ID = '" + id + "'");
//Debug.WriteLine(root.ToString());
}
private CustomXMLPart GetCustomXMLPart(WorkbookModel workbook, string id)
{
CustomXMLPart customPart = null;
foreach (CustomXMLPart part in workbook.Workbook.CustomXMLParts)
{
try
{
var xml = XElement.Parse(part.XML);
if (xml.Name.LocalName.Equals(id))
{
customPart = part;
break;
}
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
}
}
return(customPart);
}
/// <summary>
/// Gets the user name of a cell location in sif name style
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="sifName">sif cell name</param>
/// <returns>if a user name is defined: user name, else cell location</returns>
public String GetUserCellNameWithSIFName(WorkbookModel wb, String sifName)
{
var a1Adress = this.GetA1Adress(wb, sifName);
return this.GetUserCellName(wb, a1Adress);
}
/// <summary>
/// Creates a invisible sif cell name with the propertys cell name tag and a guid (without '-')
/// ex. SIF.Visualisation.0f8fad5bd9cb469fa16570867728950e
/// <returns></returns>
public String CreateSIFCellName(WorkbookModel wb, String a1Adress)
{
return new CellLocation(wb.Workbook, a1Adress).AddName(Settings.Default.CellNameTag, false).Name;
//if (a1Adress == null) return null;
//String sifName = Properties.Settings.Default.CellNameTag + (Guid.NewGuid().ToString()).Replace("-", String.Empty);
//try
//{
// var Name = wb.Workbook.Application.Names.Add(sifName, a1Adress, false);
// return sifName;
//}
//catch (Exception e)
//{
// return null;
//}
}
private XElement CreateSanityValueCells(WorkbookModel n)
{
var root = new XElement("sanityValueCells");
foreach (var cell in n.SanityValueCells)
{
root.Add(new XElement("location", NullCheck(new CellLocation(DataModel.Instance.CurrentWorkbook.Workbook, cell.Location).Location)));
}
return root;
}
/// <summary>
/// Find a document property
/// </summary>
/// <param name="n">Workbook model with the excel workbook</param>
/// <param name="propertyName">name of the requested property</param>
/// <returns></returns>
private string GetDocumentProperty(WorkbookModel n, string propertyName)
{
var properties = (Microsoft.Office.Core.DocumentProperties)n.Workbook.BuiltinDocumentProperties;
string value;
try
{
value = (properties[propertyName].Value != null) ? properties[propertyName].Value.ToString() : String.Empty;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
value = String.Empty;
}
return value;
}
/// <summary>
/// Create the sprudel xml document
/// </summary>
/// <param name="n">WorkbookModel</param>
/// <returns>complete sprudel xml as XElement</returns>
public object Visit(WorkbookModel n)
{
PolicyConfigurationModel settings = n.PolicySettings;
var root = new XElement("policyList");
var dynamicPolicy = new XElement("dynamicPolicy");
//attributes
dynamicPolicy.Add(new XAttribute("name", NullCheck(n.Title) + " Inspection"));
dynamicPolicy.Add(new XAttribute("description", GetDocumentProperty(n, "Comments")));
dynamicPolicy.Add(new XAttribute("author", GetDocumentProperty(n, "Author")));
//rules
var rules = new XElement("rules");
foreach (var scenario in n.Scenarios)
{
rules.Add(scenario.Accept(this) as XElement);
}
dynamicPolicy.Add(rules);
//spreadsheet file path
dynamicPolicy.Add(new XElement("spreadsheetFilePath", NullCheck(n.Spreadsheet)));
//input cells
dynamicPolicy.Add(CreateInputCells(n));
//output cells
dynamicPolicy.Add(CreateOutputCells(n));
// TODO: don't add when no scenario is present
root.Add(dynamicPolicy);
XElement sanityRules = CreateSanityRules(n);
if (sanityRules != null)
{
root.Add(sanityRules);
}
if ((settings.ReadingDirection && type == InspectionType.MANUAL) ||
(settings.ReadingDirection && settings.ReadingDirectionAutomatic))
{
XElement readingDirection = createReadingDirection();
root.Add(readingDirection);
}
if ((settings.NoConstantsInFormulas && type == InspectionType.MANUAL) ||
(settings.NoConstantsInFormulas && settings.NoConstantsInFormulasAutomatic))
{
XElement constants = createNoConstants();
root.Add(constants);
}
if ((settings.FormulaComplexity && type == InspectionType.MANUAL) ||
(settings.FormulaComplexity && settings.FormulaComplexityAutomatic))
{
XElement formulaComplexity = createFormulaComplexity();
root.Add(formulaComplexity);
}
if ((settings.NonConsideredConstants && type == InspectionType.MANUAL) ||
(settings.NonConsideredConstants && settings.NonConsideredConstantsAutomatic))
{
XElement nonConsidered = createNonConsideredValues();
root.Add(nonConsidered);
}
if ((settings.OneAmongOthers && type == InspectionType.MANUAL) ||
(settings.OneAmongOthers && settings.OneAmongOthersAutomatic))
{
XElement oneAmongOthers = createOneAmongOthers();
root.Add(oneAmongOthers);
}
if ((settings.RefToNull && type == InspectionType.MANUAL) ||
(settings.RefToNull && settings.RefToNullAutomatic))
{
XElement refToNull = createRefToNull();
root.Add(refToNull);
}
if ((settings.StringDistance && type == InspectionType.MANUAL) ||
(settings.StringDistance && settings.StringDistanceAutomatic))
{
XElement stringDistance = createStringDistance(settings);
root.Add(stringDistance);
}
if ((settings.MultipleSameRef && type == InspectionType.MANUAL) ||
(settings.MultipleSameRef && settings.MultipleSameRefAutomatic))
{
XElement msr = createMultipleSameRef();
root.Add(msr);
}
if ((settings.ErrorInCells && type == InspectionType.MANUAL) ||
(settings.ErrorInCells && settings.ErrorInCellsAutomatic))
{
XElement eic = createErrorInCells();
root.Add(eic);
}
return root;
}
private XElement CreateSanityRules(WorkbookModel n)
{
var root = new XElement("sanityRules");
XElement checking = CreateSanityCheckingCells(n);
if (!checking.HasElements) return null;
root.Add(checking);
root.Add(CreateSanityValueCells(n));
root.Add(CreateSanityConstraintCells(n));
root.Add(CreateSanityExplanationCells(n));
root.Add(new XElement("sanityWarnings", n.SanityWarnings));
return root;
}
/// <summary>
/// Creates a invisible sif cell name with the properties cell name tag and a guid (without '-')
/// ex. SIF.Visualisation.0f8fad5bd9cb469fa16570867728950e
/// </summary>
/// <returns></returns>
public String CreateSIFCellName(WorkbookModel wb, String a1Adress)
{
return new CellLocation(wb.Workbook, a1Adress).AddName(Settings.Default.CellNameTag, false).Name;
}
private XElement CreateOutputCells(WorkbookModel n)
{
var root = new XElement("outputCells");
foreach (var cell in n.OutputCells)
{
var cellElement = new XElement("outputCell");
cellElement.Add(new XElement("name", NullCheck(new CellLocation(DataModel.Instance.CurrentWorkbook.Workbook, cell.Location).ShortLocation)));
}
return root;
}
/// <summary>
/// Gets the first selected cells in the current workbook
/// </summary>
/// <param name="wb">workbook model</param>
/// <returns>List of Cell or null if no cell is selected</returns>
public Cell GetFirstSelectedCell(WorkbookModel wb)
{
Range selectedCell = (wb.Workbook.Application.Selection as Range).Cells.Cells[1] as Range;
String currentLocation = "=" + (selectedCell.Parent as Worksheet).Name as String + "!" + selectedCell.Address as String;
var resultCell = new Cell()
{
Id = Convert.ToInt32(selectedCell.ID),
Location = GetUserCellName(wb, currentLocation),
SifLocation = GetSIFCellName(wb, currentLocation),
Content = selectedCell.Formula as String
};
// Take the first cell and return it.
return resultCell;
}
/// <summary>
/// Selects a cell in a workbook
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="location">location of the cell to select</param>
public void SelectCell(WorkbookModel wb, string location)
{
new CellLocation(wb.Workbook, location).Select();
}
/// <summary>
/// Create the sprudel xml document
/// </summary>
/// <param name="n">WorkbookModel</param>
/// <returns>complete sprudel xml as XElement</returns>
public object Visit(WorkbookModel n)
{
var settings = n.PolicySettings;
var wrapper = new XElement("inspectionRequest");
var root = new XElement("policies");
var dynamicPolicy = CreateScenarioElements(n);
if (dynamicPolicy != null)
{
root.Add(dynamicPolicy);
}
var sanityChecks = CreateSanityElements(n);
if (sanityChecks != null)
{
root.Add(sanityChecks);
}
var rulePolicy = CreateRuleElements(n);
if (rulePolicy != null)
{
root.Add(rulePolicy);
}
if (settings.ReadingDirection)
{
var readingDirection = createReadingDirection(settings);
root.Add(readingDirection);
}
if (settings.NoConstantsInFormulas)
{
var constants = createNoConstants(settings);
root.Add(constants);
}
if (settings.FormulaComplexity)
{
var formulaComplexity = createFormulaComplexity(settings);
root.Add(formulaComplexity);
}
if (settings.NonConsideredConstants)
{
var nonConsidered = createNonConsideredValues(settings);
root.Add(nonConsidered);
}
if (settings.OneAmongOthers)
{
var oneAmongOthers = createOneAmongOthers(settings);
root.Add(oneAmongOthers);
}
if (settings.RefToNull)
{
var refToNull = createRefToNull(settings);
root.Add(refToNull);
}
if (settings.StringDistance)
{
var stringDistance = createStringDistance(settings);
root.Add(stringDistance);
}
if (settings.MultipleSameRef)
{
var msr = createMultipleSameRef(settings);
root.Add(msr);
}
if (settings.ErrorInCells)
{
var eic = createErrorInCells(settings);
root.Add(eic);
}
wrapper.Add(root);
return(wrapper);
}
/// <summary>
/// Gets the user name of a cell location in a1style
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="a1Adress">cell location in a1 style</param>
/// <returns>if a user name is defined: user name, else cell location</returns>
public String GetUserCellName(WorkbookModel wb, String a1Adress)
{
// Without FalsePositive.* and SIF.Visualization.*
var name = GetUserCellNames(wb.Workbook, a1Adress).FirstOrDefault();
if (name != null) return name.Name;
return a1Adress;
}
private string GetDocumentProperty(WorkbookModel n, string propertyName)
{
var properties = (DocumentProperties)n.Workbook.BuiltinDocumentProperties;
string value;
try
{
value = properties[propertyName].Value.ToString();
}
catch (Exception e)
{
value = String.Empty;
Console.WriteLine(e.Message);
}
return value;
}
/// <summary>
/// Gets the user name of a cell location in a1style
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="a1Adress">cell location in a1 style</param>
/// <returns>if a user name is defined: user name, else cell location</returns>
public String GetUserCellName(WorkbookModel wb, String a1Adress)
{
// Without FalsePositive.* and SIF.Visualization.*
var name = this.GetUserCellNames(wb.Workbook, a1Adress).FirstOrDefault();
if (name != null) return name.Name;
return a1Adress;
//String name = a1Adress;
//foreach (Name n in wb.Workbook.Application.Names)
//{
// if (n.RefersTo as String == a1Adress
// && !n.Name.Contains(Properties.Settings.Default.CellNameTag))
// {
// name = n.Name;
// }
//}
//return name;
}
public InspectionJob(WorkbookModel workbook, string spreadsheetPath, XDocument policyXML)
{
this.workbook = workbook;
this.spreadsheetPath = spreadsheetPath;
this.policyXML = policyXML;
}
/// <summary>
/// Gets the selected cells in the current workbook
/// </summary>
/// <param name="wb">workbook model</param>
/// <returns>List of Cell</returns>
public List<Cell> GetSelectedCells(WorkbookModel wb)
{
var cellList = new List<Cell>();
Range selectedCells = (wb.Workbook.Application.Selection as Range).Cells;
Debug.WriteLine("SELECTED CELLS: Creating List ...");
DateTime start = DateTime.Now;
foreach (var c in selectedCells.Cells)
{
var currentCell = c as Range;
String currentLocation = "=" + (currentCell.Parent as Worksheet).Name as String + "!" + currentCell.Address as String;
var selectedCell = new Cell()
{
Id = Convert.ToInt32(currentCell.ID),
Location = GetUserCellName(wb, currentLocation),
SifLocation = GetSIFCellName(wb, currentLocation),
Content = currentCell.Formula as String
};
cellList.Add(selectedCell);
}
Debug.WriteLine("SELECTED CELLS: List created! Time: " + (DateTime.Now - start).ToString() + ", Items: " + cellList.Count);
return cellList;
}
/// <summary>
/// Selects a cell in a workbook
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="location">location of the cell to select</param>
public void SelectCell(WorkbookModel wb, string location)
{
new CellLocation(wb.Workbook, location).Select();
//try
//{
// var worksheet = wb.Workbook.Sheets[Cells.CellManager.Instance.ParseWorksheetName(location)] as Worksheet;
// (worksheet.Range[ParseCellLocation(location)]).Select();
//}
//catch (Exception)
//{ }
}
/// <summary>
/// Updates the changes in the scenario values into the datamodel. Occurs when the save button is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SaveDataButton_OnClickDataButton_Click(object sender, RoutedEventArgs e)
{
BindingExpression be = TitleTextBox.GetBindingExpression(TextBox.TextProperty);
if (be != null) be.UpdateSource();
be = AuthorTextbox.GetBindingExpression(TextBox.TextProperty);
if (be != null) be.UpdateSource();
be = DescriptionTextBox.GetBindingExpression(TextBox.TextProperty);
if (be != null) be.UpdateSource();
be = CreateDatePicker.GetBindingExpression(DatePicker.SelectedDateProperty);
if (be != null) be.UpdateSource();
be = RatingTextBox.GetBindingExpression(TextBox.TextProperty);
if (be != null) be.UpdateSource();
WorkbookModel workbook = new WorkbookModel(Globals.ThisAddIn.Application.ActiveWorkbook);
workbook.ShouldScanAfterSave = false;
Globals.ThisAddIn.Application.ActiveWorkbook.Save();
workbook.ShouldScanAfterSave = true;
}
/// <summary>
/// Gets the sif cell name of a cell location in a1style
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="a1Adress">cell location in a1 style</param>
/// <returns>sif cell name</returns>
public String GetSIFCellName(WorkbookModel wb, String a1Adress)
{
// Important: There might be more than just one name for this cell!
var name = new CellLocation(wb.Workbook, a1Adress).ScenarioNames.FirstOrDefault();
if (name != null) return name.Name;
else return string.Empty;
}
public InspectionJob(WorkbookModel workbook, string spreadsheetPath, XDocument policyXML)
{
this.workbook = workbook;
this.spreadsheetPath = spreadsheetPath;
this.policyXML = policyXML;
}
public void Start(WorkbookModel wb, string scenarioTitle)
{
if (newScenario != null) return;
lock (syncScenario)
{
if (newScenario != null) return;
newScenario = new Scenario
{
Title = scenarioTitle,
CrationDate = DateTime.Now,
Author = GetDocumentProperty(wb, "Last Author")
};
}
workbook = wb.Workbook;
var workingList = wb.InputCells.Union(wb.IntermediateCells).Union(wb.OutputCells).ToList();
//sort working list column first
#region sort
workingList.Sort(delegate(Cell x, Cell y)
{
//sort by worksheet
var xSheet = workbook.Sheets[CellManager.Instance.ParseWorksheetName(x.Location)] as Worksheet;
var ySheet = workbook.Sheets[CellManager.Instance.ParseWorksheetName(x.Location)] as Worksheet;
if (xSheet.Index < ySheet.Index)
{
return -1;
}
if (xSheet.Index > ySheet.Index)
{
return 1;
}
//sort by column
var xRange = xSheet.Range[CellManager.Instance.ParseCellLocation(x.Location)];
var yRange = ySheet.Range[CellManager.Instance.ParseCellLocation(y.Location)];
if (xRange.Column < yRange.Column)
{
return -1;
}
if (xRange.Column > yRange.Column)
{
return 1;
}
//sort by row
if (xRange.Row < yRange.Row)
{
return -1;
}
return xRange.Row > yRange.Row ? 1 : 0;
});
#endregion
CreateScenarioDataFieldContainer containerFirst = null;
CreateScenarioDataFieldContainer containerBefore = null;
foreach (var c in workingList)
{
//create cell data
CellData cellData;
#region create cell data
if (c is InputCell)
{
cellData = new InputCellData();
cellData.Location = c.Location;
cellData.SifLocation = c.SifLocation;
newScenario.Inputs.Add(cellData as InputCellData);
}
else if (c is IntermediateCell)
{
cellData = new IntermediateCellData();
cellData.Location = c.Location;
cellData.SifLocation = c.SifLocation;
newScenario.Intermediates.Add(cellData as IntermediateCellData);
}
else if (c is OutputCell)
{
cellData = new ResultCellData();
cellData.Location = c.Location;
cellData.SifLocation = c.SifLocation;
newScenario.Results.Add(cellData as ResultCellData);
}
else
{
//abort
lock (syncScenario)
{
//clear this object
containers.Clear();
workbook = null;
newScenario = null;
return;
}
}
#endregion
//get worksheet
var currentWorksheet = workbook.Sheets[CellManager.Instance.ParseWorksheetName(c.Location)] as Worksheet;
var vsto = Globals.Factory.GetVstoObject(currentWorksheet);
//create container
var container = new CreateScenarioDataFieldContainer();
container.createScenarioDataField.DataContext = cellData;
containers.Add(container);
//register for focus handling
#region focus handling
if (c == workingList.First())
{
containerFirst = container;
}
else if (containerBefore != null)
{
containerBefore.createScenarioDataField.RegisterNextFocusField(container.createScenarioDataField);
}
containerBefore = container;
#endregion
//create control
var control = vsto.Controls.AddControl(
container,
currentWorksheet.Range[CellManager.Instance.ParseCellLocation(c.Location)],
Guid.NewGuid().ToString());
control.Placement = XlPlacement.xlMove;
}
//set focus to first control
if (containerFirst != null)
{
containerFirst.createScenarioDataField.SetFocus();
}
}
