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); } } } }