public void Load()
{
using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(_filename, false))
{
WorkbookPart workbook = xlPackage.WorkbookPart;
_workbook = LoadWorkbookDOM(workbook);
Dictionary<string, string> allParameters = LoadDefinedFundParameterNames(_workbook);
Dictionary<string, HashSet<string>> cellsByTable = new Dictionary<string, HashSet<string>>();
foreach (string k in allParameters.Keys)
{
string range = allParameters[k];
string[] rangeParts = range.Split('!');
string table = rangeParts[0];
string cellinfo = rangeParts[1];
if (!cellsByTable.ContainsKey(table))
{
cellsByTable.Add(table, new HashSet<string>());
}
HashSet<string> cellsToLookFor = cellsByTable[table];
if (cellinfo.Contains(":"))
{
string[] cells = cellinfo.Split(':');
string startcell = cells[0].Replace("$", "");
string endcell = cells[1].Replace("$", "");
int startcellrowindex = startcell.IndexOfAny(new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' });
string startcellcol = startcell.Substring(0, startcellrowindex);
string startcellrow = startcell.Substring(startcellrowindex);
int endcellrowindex = endcell.IndexOfAny(new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' });
string endcellcol = endcell.Substring(0, endcellrowindex);
string endcellrow = endcell.Substring(endcellrowindex);
if (startcellrow != endcellrow && startcellcol != endcellcol)
{
List<string> columns = new List<string>(EnumerateExcelColRange(startcellcol, endcellcol));
foreach (string column in columns)
{
for (int i = Convert.ToInt32(startcellrow); i <= Convert.ToInt32(endcellrow); i++)
{
cellsToLookFor.Add(column + i.ToString());
}
}
}
else if (startcellrow == endcellrow)
{
foreach (string s in EnumerateExcelColRange(startcellcol, endcellcol))
{
cellsToLookFor.Add(s + startcellrow);
}
}
else if (startcellcol == endcellcol)
{
for (int i = Convert.ToInt32(startcellrow); i <= Convert.ToInt32(endcellrow); i++)
{
cellsToLookFor.Add(startcellcol + i.ToString());
}
}
}
else
{
cellsToLookFor.Add(cellinfo.Replace("$", ""));
}
}
const string worksheetSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
const string sharedStringSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
LoadSheets(workbook, allParameters);
Dictionary<string, Dictionary<string, string>> allNeededValues = new Dictionary<string, Dictionary<string, string>>();
Dictionary<int, List<KeyValuePair<string, string>>> thingsThatNeedToBeLoadedFromTheStringTable = new Dictionary<int, List<KeyValuePair<string, string>>>();
foreach (string sheetname in _sheets.Keys)
{
allNeededValues.Add(sheetname, new Dictionary<string, string>());
Dictionary<string, string> currentAllNeededValues = allNeededValues[sheetname];
HashSet<string> currentCellsToLookFor = cellsByTable[sheetname];
// Return the value of the specified cell.
// Create a namespace manager, so you can search.
// Add a prefix (d) for the default namespace.
NameTable nt = new NameTable();
XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);
nsManager.AddNamespace("d", worksheetSchema);
using (XmlTextReader r = new XmlTextReader(_sheets[sheetname].GetStream(FileMode.Open, FileAccess.Read), nt))
{
while (r.Read())
{
if (r.NodeType == XmlNodeType.Element && r.Name == "c")
{
string cellReference = r.GetAttribute("r");
if (currentCellsToLookFor.Contains(cellReference))
{
string cellType = r.GetAttribute("t");
if (r.ReadToDescendant("v"))
{
string cellValue = r.ReadElementContentAsString();
if (cellType != "s")
{
currentAllNeededValues.Add(cellReference, cellValue);
}
else
{
int stringTableIndex = Convert.ToInt32(cellValue);
if (!thingsThatNeedToBeLoadedFromTheStringTable.ContainsKey(stringTableIndex))
{
thingsThatNeedToBeLoadedFromTheStringTable.Add(stringTableIndex, new List<KeyValuePair<string, string>>());
}
thingsThatNeedToBeLoadedFromTheStringTable[stringTableIndex].Add(new KeyValuePair<string, string>(sheetname, cellReference));
}
}
}
}
}
}
}
// Create a namespace manager, so you can search.
// Add a prefix (d) for the default namespace.
NameTable nts = new NameTable();
XmlNamespaceManager nsManager2 = new XmlNamespaceManager(nts);
nsManager2.AddNamespace("s", sharedStringSchema);
using (XmlTextReader r = new XmlTextReader(workbook.SharedStringTablePart.GetStream(FileMode.Open, FileAccess.Read), nts))
{
int currentStringTableIndex = -1;
while (r.Read())
{
if (r.NodeType == XmlNodeType.Element && r.Name == "si")
{
currentStringTableIndex++;
if (thingsThatNeedToBeLoadedFromTheStringTable.ContainsKey(currentStringTableIndex))
{
if (r.ReadToDescendant("t"))
{
string cellValue = r.ReadElementContentAsString();
foreach (KeyValuePair<string, string> tt in thingsThatNeedToBeLoadedFromTheStringTable[currentStringTableIndex])
{
allNeededValues[tt.Key].Add(tt.Value, cellValue);
}
}
}
}
}
}
foreach (string k in allParameters.Keys)
{
string range = allParameters[k];
string[] rangeParts = range.Split('!');
string table = rangeParts[0];
string cellinfo = rangeParts[1];
if (cellinfo.Contains(":"))
{
string[] cells = cellinfo.Split(':');
string startcell = cells[0].Replace("$", "");
string endcell = cells[1].Replace("$", "");
int startcellrowindex = startcell.IndexOfAny(new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' });
string startcellcol = startcell.Substring(0, startcellrowindex);
string startcellrow = startcell.Substring(startcellrowindex);
int endcellrowindex = endcell.IndexOfAny(new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' });
string endcellcol = endcell.Substring(0, endcellrowindex);
string endcellrow = endcell.Substring(endcellrowindex);
if (startcellrow != endcellrow && startcellcol != endcellcol)
{
List<string> columns = new List<string>(EnumerateExcelColRange(startcellcol, endcellcol));
string[,] data = new string[Convert.ToInt32(endcellrow) - Convert.ToInt32(startcellrow) + 1, columns.Count];
foreach (string column in columns)
{
for (int i = Convert.ToInt32(startcellrow); i <= Convert.ToInt32(endcellrow); i++)
{
data[i - Convert.ToInt32(startcellrow), columns.IndexOf(column)] = allNeededValues[table][column + i.ToString()];
}
}
TwoDimensionalParameter p = new TwoDimensionalParameter(data);
string nameAndType = k.Substring(5);
if (nameAndType.Contains("."))
{
int indexOfNameTypeSeperator = nameAndType.IndexOf('.');
p.Name = nameAndType.Substring(0, indexOfNameTypeSeperator);
p.Type = nameAndType.Substring(indexOfNameTypeSeperator + 1);
}
else
{
p.Name = nameAndType;
p.Type = null;
}
_parameters.Add(p);
}
else if (startcellrow == endcellrow)
{
List<string> data = new List<string>();
foreach (string s in EnumerateExcelColRange(startcellcol, endcellcol))
{
string v = allNeededValues[table][s + startcellrow];
data.Add(v);
}
OneDimensionalParameter p = new OneDimensionalParameter(data);
string nameAndType = k.Substring(5);
if (nameAndType.Contains("."))
{
int indexOfNameTypeSeperator = nameAndType.IndexOf('.');
p.Name = nameAndType.Substring(0, indexOfNameTypeSeperator);
p.Type = nameAndType.Substring(indexOfNameTypeSeperator + 1);
}
else
{
p.Name = nameAndType;
p.Type = null;
}
_parameters.Add(p);
}
else if (startcellcol == endcellcol)
{
List<string> data = new List<string>();
for (int i = Convert.ToInt32(startcellrow); i <= Convert.ToInt32(endcellrow); i++)
{
string v = allNeededValues[table][startcellcol + i.ToString()];
data.Add(v);
}
OneDimensionalParameter p = new OneDimensionalParameter(data);
string nameAndType = k.Substring(5);
if (nameAndType.Contains("."))
{
int indexOfNameTypeSeperator = nameAndType.IndexOf('.');
p.Name = nameAndType.Substring(0, indexOfNameTypeSeperator);
p.Type = nameAndType.Substring(indexOfNameTypeSeperator + 1);
}
else
{
p.Name = nameAndType;
p.Type = null;
}
_parameters.Add(p);
}
}
else
{
NonDimensionalParameter p = new NonDimensionalParameter();
string nameAndType = k.Substring(5);
if (nameAndType.Contains("."))
{
int indexOfNameTypeSeperator = nameAndType.IndexOf('.');
p.Name = nameAndType.Substring(0, indexOfNameTypeSeperator);
p.Type = nameAndType.Substring(indexOfNameTypeSeperator + 1);
}
else
{
p.Name = nameAndType;
p.Type = null;
}
p.Value = allNeededValues[table][cellinfo.Replace("$", "")];
_parameters.Add(p);
}
}
}
}
public void Load()
{
using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(_filename, false))
{
WorkbookPart workbook = xlPackage.WorkbookPart;
_workbook = LoadWorkbookDOM(workbook);
Dictionary <string, string> allParameters = LoadDefinedFundParameterNames(_workbook);
Dictionary <string, HashSet <string> > cellsByTable = new Dictionary <string, HashSet <string> >();
foreach (string k in allParameters.Keys)
{
string range = allParameters[k];
string[] rangeParts = range.Split('!');
string table = rangeParts[0];
string cellinfo = rangeParts[1];
if (!cellsByTable.ContainsKey(table))
{
cellsByTable.Add(table, new HashSet <string>());
}
HashSet <string> cellsToLookFor = cellsByTable[table];
if (cellinfo.Contains(":"))
{
string[] cells = cellinfo.Split(':');
string startcell = cells[0].Replace("$", "");
string endcell = cells[1].Replace("$", "");
int startcellrowindex = startcell.IndexOfAny(new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' });
string startcellcol = startcell.Substring(0, startcellrowindex);
string startcellrow = startcell.Substring(startcellrowindex);
int endcellrowindex = endcell.IndexOfAny(new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' });
string endcellcol = endcell.Substring(0, endcellrowindex);
string endcellrow = endcell.Substring(endcellrowindex);
if (startcellrow != endcellrow && startcellcol != endcellcol)
{
List <string> columns = new List <string>(EnumerateExcelColRange(startcellcol, endcellcol));
foreach (string column in columns)
{
for (int i = Convert.ToInt32(startcellrow); i <= Convert.ToInt32(endcellrow); i++)
{
cellsToLookFor.Add(column + i.ToString());
}
}
}
else if (startcellrow == endcellrow)
{
foreach (string s in EnumerateExcelColRange(startcellcol, endcellcol))
{
cellsToLookFor.Add(s + startcellrow);
}
}
else if (startcellcol == endcellcol)
{
for (int i = Convert.ToInt32(startcellrow); i <= Convert.ToInt32(endcellrow); i++)
{
cellsToLookFor.Add(startcellcol + i.ToString());
}
}
}
else
{
cellsToLookFor.Add(cellinfo.Replace("$", ""));
}
}
const string worksheetSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
const string sharedStringSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
LoadSheets(workbook, allParameters);
Dictionary <string, Dictionary <string, string> > allNeededValues = new Dictionary <string, Dictionary <string, string> >();
Dictionary <int, List <KeyValuePair <string, string> > > thingsThatNeedToBeLoadedFromTheStringTable = new Dictionary <int, List <KeyValuePair <string, string> > >();
foreach (string sheetname in _sheets.Keys)
{
allNeededValues.Add(sheetname, new Dictionary <string, string>());
Dictionary <string, string> currentAllNeededValues = allNeededValues[sheetname];
HashSet <string> currentCellsToLookFor = cellsByTable[sheetname];
// Return the value of the specified cell.
// Create a namespace manager, so you can search.
// Add a prefix (d) for the default namespace.
NameTable nt = new NameTable();
XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);
nsManager.AddNamespace("d", worksheetSchema);
using (XmlTextReader r = new XmlTextReader(_sheets[sheetname].GetStream(FileMode.Open, FileAccess.Read), nt))
{
while (r.Read())
{
if (r.NodeType == XmlNodeType.Element && r.Name == "c")
{
string cellReference = r.GetAttribute("r");
if (currentCellsToLookFor.Contains(cellReference))
{
string cellType = r.GetAttribute("t");
if (r.ReadToDescendant("v"))
{
string cellValue = r.ReadElementContentAsString();
if (cellType != "s")
{
currentAllNeededValues.Add(cellReference, cellValue);
}
else
{
int stringTableIndex = Convert.ToInt32(cellValue);
if (!thingsThatNeedToBeLoadedFromTheStringTable.ContainsKey(stringTableIndex))
{
thingsThatNeedToBeLoadedFromTheStringTable.Add(stringTableIndex, new List <KeyValuePair <string, string> >());
}
thingsThatNeedToBeLoadedFromTheStringTable[stringTableIndex].Add(new KeyValuePair <string, string>(sheetname, cellReference));
}
}
}
}
}
}
}
// Create a namespace manager, so you can search.
// Add a prefix (d) for the default namespace.
NameTable nts = new NameTable();
XmlNamespaceManager nsManager2 = new XmlNamespaceManager(nts);
nsManager2.AddNamespace("s", sharedStringSchema);
using (XmlTextReader r = new XmlTextReader(workbook.SharedStringTablePart.GetStream(FileMode.Open, FileAccess.Read), nts))
{
int currentStringTableIndex = -1;
while (r.Read())
{
if (r.NodeType == XmlNodeType.Element && r.Name == "si")
{
currentStringTableIndex++;
if (thingsThatNeedToBeLoadedFromTheStringTable.ContainsKey(currentStringTableIndex))
{
if (r.ReadToDescendant("t"))
{
string cellValue = r.ReadElementContentAsString();
foreach (KeyValuePair <string, string> tt in thingsThatNeedToBeLoadedFromTheStringTable[currentStringTableIndex])
{
allNeededValues[tt.Key].Add(tt.Value, cellValue);
}
}
}
}
}
}
foreach (string k in allParameters.Keys)
{
string range = allParameters[k];
string[] rangeParts = range.Split('!');
string table = rangeParts[0];
string cellinfo = rangeParts[1];
if (cellinfo.Contains(":"))
{
string[] cells = cellinfo.Split(':');
string startcell = cells[0].Replace("$", "");
string endcell = cells[1].Replace("$", "");
int startcellrowindex = startcell.IndexOfAny(new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' });
string startcellcol = startcell.Substring(0, startcellrowindex);
string startcellrow = startcell.Substring(startcellrowindex);
int endcellrowindex = endcell.IndexOfAny(new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' });
string endcellcol = endcell.Substring(0, endcellrowindex);
string endcellrow = endcell.Substring(endcellrowindex);
if (startcellrow != endcellrow && startcellcol != endcellcol)
{
List <string> columns = new List <string>(EnumerateExcelColRange(startcellcol, endcellcol));
string[,] data = new string[Convert.ToInt32(endcellrow) - Convert.ToInt32(startcellrow) + 1, columns.Count];
foreach (string column in columns)
{
for (int i = Convert.ToInt32(startcellrow); i <= Convert.ToInt32(endcellrow); i++)
{
data[i - Convert.ToInt32(startcellrow), columns.IndexOf(column)] = allNeededValues[table][column + i.ToString()];
}
}
TwoDimensionalParameter p = new TwoDimensionalParameter(data);
string nameAndType = k.Substring(5);
if (nameAndType.Contains("."))
{
int indexOfNameTypeSeperator = nameAndType.IndexOf('.');
p.Name = nameAndType.Substring(0, indexOfNameTypeSeperator);
p.Type = nameAndType.Substring(indexOfNameTypeSeperator + 1);
}
else
{
p.Name = nameAndType;
p.Type = null;
}
_parameters.Add(p);
}
else if (startcellrow == endcellrow)
{
List <string> data = new List <string>();
foreach (string s in EnumerateExcelColRange(startcellcol, endcellcol))
{
string v = allNeededValues[table][s + startcellrow];
data.Add(v);
}
OneDimensionalParameter p = new OneDimensionalParameter(data);
string nameAndType = k.Substring(5);
if (nameAndType.Contains("."))
{
int indexOfNameTypeSeperator = nameAndType.IndexOf('.');
p.Name = nameAndType.Substring(0, indexOfNameTypeSeperator);
p.Type = nameAndType.Substring(indexOfNameTypeSeperator + 1);
}
else
{
p.Name = nameAndType;
p.Type = null;
}
_parameters.Add(p);
}
else if (startcellcol == endcellcol)
{
List <string> data = new List <string>();
for (int i = Convert.ToInt32(startcellrow); i <= Convert.ToInt32(endcellrow); i++)
{
string v = allNeededValues[table][startcellcol + i.ToString()];
data.Add(v);
}
OneDimensionalParameter p = new OneDimensionalParameter(data);
string nameAndType = k.Substring(5);
if (nameAndType.Contains("."))
{
int indexOfNameTypeSeperator = nameAndType.IndexOf('.');
p.Name = nameAndType.Substring(0, indexOfNameTypeSeperator);
p.Type = nameAndType.Substring(indexOfNameTypeSeperator + 1);
}
else
{
p.Name = nameAndType;
p.Type = null;
}
_parameters.Add(p);
}
}
else
{
NonDimensionalParameter p = new NonDimensionalParameter();
string nameAndType = k.Substring(5);
if (nameAndType.Contains("."))
{
int indexOfNameTypeSeperator = nameAndType.IndexOf('.');
p.Name = nameAndType.Substring(0, indexOfNameTypeSeperator);
p.Type = nameAndType.Substring(indexOfNameTypeSeperator + 1);
}
else
{
p.Name = nameAndType;
p.Type = null;
}
p.Value = allNeededValues[table][cellinfo.Replace("$", "")];
_parameters.Add(p);
}
}
}
}
