static private List <sheetNameData> GetSheetNames(XmlNode sheets)
{
List <sheetNameData> sheetNames = new List <sheetNameData>();
XmlNodeList sheetList = sheets.ChildNodes;
foreach (XmlNode sheet in sheetList)
{
XmlElement subSheet = (XmlElement)sheet;
sheetNameData snd = new sheetNameData(subSheet.GetAttribute("name").ToString().ToLower(), subSheet.GetAttribute("r:id").ToString());
sheetNames.Add(snd);
}
return(sheetNames);
}
static private List <List <string> > GetSheetContents(SpreadsheetDocument xlDoc, sheetNameData nameData)
{
XmlDocument sheetDoc = new XmlDocument();
sheetDoc.Load(xlDoc.WorkbookPart.GetPartById(nameData.rid).GetStream());
XmlNode sheetSize = null;
XmlNode sheetData = null;
XmlNode sheetRoot = null;
XmlNodeList nodeList = sheetDoc.ChildNodes;
foreach (XmlNode node in nodeList)
{
if (node.LocalName == "worksheet")
{
sheetRoot = node;
break;
}
}
if (sheetRoot == null)
{
return(null);
}
nodeList = sheetRoot.ChildNodes;
foreach (XmlNode node in nodeList)
{
if (node.LocalName == "dimension")
{
sheetSize = node;
}
if (node.LocalName == "sheetData")
{
sheetData = node;
}
}
if (sheetSize == null || sheetData == null)
{
return(null);
}
XmlElement sheetSizeElement = (XmlElement)sheetSize;
string refStr = sheetSizeElement.GetAttribute("ref").ToString();
if (string.IsNullOrEmpty(refStr))
{
return(null);
}
string sizeStr = StringTools.GetFirstMatch(refStr, "(?<=:)[^:]+");
if (string.IsNullOrEmpty(sizeStr))
{
return(null);
}
string rowStr = StringTools.GetFirstMatch(sizeStr, "[a-z,A-Z]+");
int rowSize = GetRealNum(rowStr);
int lineSize = int.Parse(StringTools.GetFirstMatch(sizeStr, "[0-9]+"));
NameTable nt = new NameTable();
XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);
nsManager.AddNamespace("d", worksheetSchema);
nsManager.AddNamespace("s", sharedStringSchema);
List <List <string> > sData = new List <List <string> >();
for (int i = 0; i < lineSize; i++)
{
List <string> lineData = new List <string>();
for (int j = 0; j < rowSize; j++)
{
lineData.Add(GetCellValue(sheetDoc, nsManager, nt, xlDoc, j, i + 1));
}
sData.Add(lineData);
}
return(sData);
}