public void MergeDTfromFiles(IProgress <SpreadsheetCollectionProgressModel> progress, CancellationToken cancellationToken)
{
SpreadsheetCollectionProgressModel report = new SpreadsheetCollectionProgressModel(ProcessState.IsRunning);
List <FileInfo> files = StringToFileInfo(Options.Files);
string dir = files[0].DirectoryName;
List <string> sheets = GenerateSheets();
List <DataTable> tables = new List <DataTable>();
DataTable errorTable = new DataTable();
foreach (FileInfo file in files)
{
report.steps.Add($"Loading {file.Name}...");
progress.Report(report);
var package = SpreadsheetUtilities.GetDTfromExcel(file, Convert.ToInt32(Options.StartRow), sheets, Options.Passwords);
tables.AddRange(package.data);
errorTable.Merge(package.errors);
cancellationToken.ThrowIfCancellationRequested();
}
//Combine all tables into first table in list
for (int i = 1; i < tables.Count; i++)
{
report.steps.Add($"Merging {tables[i].TableName}...");
progress.Report(report);
tables[0].Merge(tables[i], false, MissingSchemaAction.Add);
tables[i] = null;
cancellationToken.ThrowIfCancellationRequested();
}
report.IsCancellable = false;
report.steps.Add("Sending to Excel...");
progress.Report(report);
try
{
cancellationToken.ThrowIfCancellationRequested();
SpreadsheetUtilities.DTtoExcel(tables[0], dir, errorTable);
}
catch (ArgumentOutOfRangeException e)
{
throw new ArgumentOutOfRangeException("Sheet(s) not found in any files!", e);
}
catch (OperationCanceledException)
{
throw;
}
finally
{
report.State = ProcessState.Completed;
report.steps.Add("Process Completed!");
progress.Report(report);
}
}
public void GetDTFromExcel_HeaderPatterns_ReturnTables(string file)
{
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
var mockFile = new FileInfo($"resources/{file}.xlsx");
var headerRow = 1;
var sheets = new List <string>()
{
"Sheet1"
};
var passwords = new List <string>();
var(data, err) = SpreadsheetUtilities.GetDTfromExcel(mockFile, headerRow, sheets, passwords);
Assert.Equal(0, err.Rows.Count);
Assert.Single(data);
Assert.Equal(8, data[0].Columns.Count);
Assert.Equal(10, data[0].Rows.Count);
}
/// <summary>
/// If formula parameter is null, throws an ArgumentNullException.
///
/// Otherwise, if name is null or invalid, throws an InvalidNameException.
///
/// Otherwise, if changing the contents of the named cell to be the formula would cause a
/// circular dependency, throws a CircularException.
///
/// Otherwise, the contents of the named cell becomes formula. The method returns a
/// Set consisting of name plus the names of all other cells whose value depends,
/// directly or indirectly, on the named cell.
///
/// For example, if name is A1, B1 contains A1*2, and C1 contains B1+A1, the
/// set {A1, B1, C1} is returned.
/// </summary>
protected override ISet<string> SetCellContents(string cell_name, SpreadsheetUtilities.Formula formula)
{
IEnumerable<string> backup_variables = null;
object backup_contents = null;
bool had_name = false;
string name = Normalize(cell_name);
//if the formula passed in is null this throws an ArgumentNullException();
if (formula == null)
{
throw new ArgumentNullException("Formula cannot be null");
}
//checks if the name is null or invalid if so throws a new InvalidNameException();
if (name == null || IsValid(name) == false)
{
throw new InvalidNameException();
}
//checks if the cell with the name has been set up, if so this sets the contents of the cell to the formula passed in.
if (spreadsheet.ContainsKey(name))
{
backup_variables = dependency_graph.GetDependees(name);
backup_contents = spreadsheet[name].get_contents();
spreadsheet[name].set_contents(formula);
had_name = true;
}
//if the cell hasn't been set up this creates a new cell and sets its contents to the text passed in.
else
{
backup_contents = " ";
backup_variables = new HashSet<string> { };
spreadsheet.Add(name, new Cell(name, formula));
}
IEnumerable<string> variables = formula.GetVariables(); //Gets all the variables
dependency_graph.ReplaceDependees(name, variables); //Replace the dependees of the name with all the variables in the formula passed in.
//creates a hash set for the return ISet.
ISet<string> depends = new HashSet<string> { };
//looks through each item in the dependency graph and add them to the hash set to return.
try
{
IEnumerable<string> recalculate_cells = GetCellsToRecalculate(name);
foreach (string item in recalculate_cells)
{
depends.Add(item);
}
}
catch (CircularException)
{
spreadsheet[name].set_contents(backup_contents);
dependency_graph.ReplaceDependees(name, backup_variables);
throw new CircularException();
}
//sets the value of the cell
IEnumerable<string> evaluate_cells = GetCellsToRecalculate(name);
foreach (string cell in evaluate_cells)
{
Formula value_of_formula = (Formula)GetCellContents(cell);
try
{
spreadsheet[cell].set_value(value_of_formula.Evaluate(Lookup));
}
catch(ArgumentException)
{
}
}
//returns the hash set with all the dependents of name.
return depends;
}
/// <summary>
/// Creates a cell with a name and contents.
/// </summary>
/// <param name="name"></param>
/// <param name="formula">formula</param>
public Cell(String name, SpreadsheetUtilities.Formula formula)
{
cell_name = name;
contents = formula;
}
/// <summary>
/// If the formula parameter is null, throws an ArgumentNullException.
///
/// Otherwise, if name is null or invalid, throws an InvalidNameException.
///
/// Otherwise, if changing the contents of the named cell to be the formula would cause a
/// circular dependency, throws a CircularException. (No change is made to the spreadsheet.)
///
/// Otherwise, the contents of the named cell becomes formula. The method returns a
/// Set consisting of name plus the names of all other cells whose value depends,
/// directly or indirectly, on the named cell.
///
/// For example, if name is A1, B1 contains A1*2, and C1 contains B1+A1, the
/// set {A1, B1, C1} is returned.
/// </summary>
/// <param name="name"></param>
/// <param name="formula"></param>
/// <returns></returns>
protected override ISet<string> SetCellContents(string name, SpreadsheetUtilities.Formula formula)
{
//first of all normalize name
name = Normalize(name);
if (formula == null)
throw new ArgumentNullException();
if (name == null || !Name_Verify(name))
throw new InvalidNameException();
//HadAdd is used to monitoring which dependent is added in DependGraph, if circularexception occur, delete all the
//dependent which is been add in the DependGraph
List<string> HadAdd = new List<string>();
//check if the new formula contain a circulardepent
foreach (string s in formula.GetVariables())
{
//make sure that dependent(s,name) is not in the graph_dependent then we add them into the graph_dependent
if (!new HashSet<string>(Spreadsheet_DependGraph.GetDependents(s)).Contains(name))
{
HadAdd.Add(s);
Spreadsheet_DependGraph.AddDependency(s, name);
}
try
{
//use getCellTorecalcualte function to test if the circular dependency exist
GetCellsToRecalculate(name);
}
catch (CircularException ce)
{
//if the circular dependency exist remove the dependency which you have add and throw an exception
foreach (string s1 in HadAdd)
{
Spreadsheet_DependGraph.RemoveDependency(s1, name);
}
throw ce;
}
}
//set the named cell's value to the value.
cell A_cell;
Spreadsheet_cells.TryGetValue(name, out A_cell);
if (A_cell == null)
{
A_cell = new cell();
A_cell.name = name;
A_cell.content = (object)formula;
A_cell.value = formula.Evaluate(lookup);
Spreadsheet_cells.Add(A_cell.name, A_cell);
}
else
{
A_cell.content = (object)formula;
A_cell.value = formula.Evaluate(lookup);
Spreadsheet_cells.Remove(A_cell.name);
Spreadsheet_cells.Add(A_cell.name, A_cell);
}
//recalculate all the direct and indirect cells' value
foreach (string s in GetCellsToRecalculate(name))
{
if (s == name)
continue;
cell cell1;
Spreadsheet_cells.TryGetValue(s, out cell1);
cell1.value = ((Formula)cell1.content).Evaluate(lookup);
Spreadsheet_cells.Remove(s);
Spreadsheet_cells.Add(s, cell1);
}
//define that the spreadsheet is changed
Changed = true;
return new HashSet<string>(GetCellsToRecalculate(name));
}
// MODIFIED PROTECTION FOR PS5
/// <summary>
/// If formula parameter is null, throws an ArgumentNullException.
///
/// Otherwise, if name is null or invalid, throws an InvalidNameException.
///
/// Otherwise, if changing the contents of the named cell to be the formula would cause a
/// circular dependency, throws a CircularException.
///
/// Otherwise, the contents of the named cell becomes formula. The method returns a
/// Set consisting of name plus the names of all other cells whose value depends,
/// directly or indirectly, on the named cell.
///
/// For example, if name is A1, B1 contains A1*2, and C1 contains B1+A1, the
/// set {A1, B1, C1} is returned.
/// </summary>
protected override ISet<string> SetCellContents(string name, SpreadsheetUtilities.Formula formula)
{
if (formula == null)
throw new ArgumentNullException("Error: Formula paramter was null.");
// Checks if cell name is valid and not null
if (name == null || !ValidName(name))
throw new InvalidNameException();
List<string> cellNames = new List<string>();
Cell cellValue;
// Holds the cell names that will be returned
HashSet<string> result = new HashSet<string>();
// Holds the contents of the original cell
object o = null;
if (cells.ContainsKey(name)) {
o = cells[name].Content;
RemoveDependency(name);
}
// Save the content of the cell to store in the dictionary
cellValue = new Cell(formula, GetCell);
cells.Add(name, cellValue);
// cellNames gets a list of all of the cell names in the formula and creates a list
// to add to the dependency graph
cellNames = formula.GetVariables().ToList();
// Add the values associated to name to the dependency graph
foreach (string n in cellNames) {
dg.AddDependency(name, n);
}
try {
//saves the direct and indirect values into tempI and then put them into my returnSet
//also checks for circular dependency and recalculates the values in the cells associate with the name
IEnumerable<string> tempI = GetCellsToRecalculate(name);//recalculates the values and checks for circular dependency
foreach (string n in tempI)
result.Add(n);
}
catch (CircularException c) {
//method comes here if a circularexeption is found when calling the GetCellsToRecalculate method
//originalCellContent is saved earlier in the code to allow us to go back to the original values to get
//rid of the circular dependency
if (o != null) {
if (o is Formula) {
Formula originalFormula = new Formula(o.ToString());
SetCellContents(name, originalFormula);
}
else if (o is String) {
SetCellContents(name, o.ToString());
}
else if (o is double) {
SetCellContents(name, Convert.ToDouble(o));
}
}
else
SetCellContents(name, "");
throw c;
}
return result;
}
/// <summary>
/// Sets the contents of the specified cell to the Formula specified
/// </summary>
/// <param name="name">The cell we want to set the contents of</param>
/// <param name="formula">The contents we want the cell to hold</param>
/// <returns>The other cells whose value depends on this cell</returns>
protected override ISet<string> SetCellContents(string name, SpreadsheetUtilities.Formula formula)
{
if (formula == null)
throw new ArgumentNullException();
if (name == null || !isValidName(name))
throw new InvalidNameException();
object oldContents = formula;
bool isDouble = false;
bool isString = false;
if (cells.ContainsKey(name))
{
oldContents = cells[name].CellContents;
if (cells[name].CellContents is double)
isDouble = true;
if (cells[name].CellContents is string)
isString = true;
try
{
//if (!(cells[name].CellContents is double) && !(cells[name].CellContents is string))
//{
Formula oldValue = (Formula)cells[name].CellContents;
foreach (string s in oldValue.GetVariables())
{
dependencyGraph.RemoveDependency(s, name);
}
// }
cells[name].CellContents = formula;
}
catch {
cells[name].CellContents = oldContents;
}
}
foreach (string v in formula.GetVariables())
{
dependencyGraph.AddDependency(v, name);
}
HashSet<string> dependentCells = new HashSet<string>();
try
{
dependentCells = new HashSet<string>(GetCellsToRecalculate(name));
cells.Remove(name);
cells.Add(name, new Cell(formula, lookup));
}
catch
{
cells.Remove(name);
if (isDouble)
cells.Add(name, new Cell((double)oldContents));
else if (isString)
cells.Add(name, new Cell((string)oldContents));
else
cells.Add(name, new Cell((Formula)oldContents, lookup));
throw new CircularException();
//cells[name].CellContents = oldContents;
}
foreach (string s in dependentCells)
{
if (cells.ContainsKey(s))
{
if (cells[s].CellContents is Formula)
{
Formula oldValue = (Formula)cells[s].CellContents;
cells.Remove(s);
cells.Add(s, new Cell(oldValue, lookup));
}
}
}
hasChanged = true;
return dependentCells;
}
protected override ISet<string> SetCellContents(string name, SpreadsheetUtilities.Formula formula)
{
if (name==null) //if the name is null, throw ArgumentNullException.
{
throw new ArgumentNullException();
}
if (!IsValid(name)) //if name is invalid, throw InvalidNameException.
{
throw new InvalidNameException();
}
String cellName = Normalize(name);
HashSet<string> dependents = new HashSet<string>(); //A SortedSet to hold the dependents.
foreach (Cell cell in cells) //for each cell in the hashset.
{
if (cell.getName().Equals(cellName)) //if the cell name equals the parameter name.
{
cell.setFormula(formula); //set the cell formula to the parameter.
dg.ReplaceDependees(cellName, formula.GetVariables()); //replace the dependees.
foreach (String s in GetCellsToRecalculate(cellName)) //for each string in getcellstorecalculate.
{
dependents.Add(s); //add string to the sorted set.
}
return (dependents); //return the sorted set.
}
}
Formula cellForm = new Formula("="+formula.ToString(), s=>true);
cells.Add(new Cell(cellName, cellForm)); //otherwise, create a new cell using the parameters.
foreach (string s in formula.GetVariables()) //for each string in getvariables.
{
dg.AddDependency(s, cellName); //add a new dependency.
}
foreach (String s in GetCellsToRecalculate(cellName)) //for each string in getcellstorecalculate.
{
dependents.Add(s); //add the string to the sorted set.
}
return (dependents); //return the sorted set.
}
