Archive for June, 2009

Excel 2007, openXml helper

Monday, June 15th, 2009

public 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;

}

}