Excel 2007, openXml helper
June 15th, 2009public class ExcelMapperHelper
{
public DateTime ConvertExcelDateToDateTime(string date)
{
DateTime excelBaseDate = new DateTime(1899, 12, 30);
DateTime excelDate = DateTime.MinValue;
int integerExcelDays;
integerExcelDays = Convert.ToInt32(System.Math.Floor(Convert.ToDouble(date)));
excelDate = excelBaseDate + new TimeSpan(integerExcelDays, 0, 0, 0);
return excelDate;
}
public string GetColumnLetters(string columnName)
{
return Regex.Replace(columnName, @”(\d+\.?\d*|\.\d+)”, string.Empty);
}
public string NamespacePrefix
{
get
{
return “xlsx”;
}
}
public XmlNamespaceManager GetNamespaceManager(XmlDocument xDoc, string namespacePrefix)
{
XmlNamespaceManager namespaceManager = new XmlNamespaceManager(xDoc.NameTable);
namespaceManager.AddNamespace(namespacePrefix, @”http://schemas.openxmlformats.org/spreadsheetml/2006/main”);
return namespaceManager;
}
public void GetExcelRows(XmlDocument xDoc, out string namespacePrefix, out XmlNamespaceManager namespaceManager, out XmlNodeList rowList)
{
namespacePrefix = NamespacePrefix;
namespaceManager = GetNamespaceManager(xDoc, namespacePrefix);
string workSheetXPath = string.Format(”{0}:worksheet/{0}:sheetData/{0}:row”, namespacePrefix);
rowList = xDoc.SelectNodes(workSheetXPath, namespaceManager);
}
public List<string> ReadStringTable(Stream input)
{
List<string> stringTable = new List<string>();
using (XmlTextReader reader = new XmlTextReader(input))
{
for (reader.MoveToContent(); reader.Read(); )
if (reader.NodeType == XmlNodeType.Element && reader.Name == “t”)
stringTable.Add(reader.ReadElementString());
}
return stringTable;
}
public int GetColumnNumber(string columnName)
{
string letters = GetColumnLetters(columnName);
double columnNumber = 0;
int counter = letters.Length - 1;
foreach (char c in letters)
{
if (Char.IsLetter(c))
{
int charSequence = (int)(c) - (int)(’A') + 1;
columnNumber += Math.Pow(26, counter) * charSequence;
counter–;
}
}
return Convert.ToInt32(columnNumber);
}
public int GetNextColumnNumber(string columnName, int nextCount)
{
int number = GetColumnNumber(columnName) + nextCount;
return number;
}
public string GetColumnName(int columnNo)
{
int dividend = columnNo;
string columnName = String.Empty;
int modulo;
while (dividend > 0)
{
modulo = (dividend - 1) % 26;
columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
dividend = (int)((dividend - modulo) / 26);
}
return columnName;
}
}
).
