Excel 2007, openXml helper

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;

}

}

35 Responses to “Excel 2007, openXml helper”

  1. Kylie Batt Says:

    ?????????, ?? ??? ?? ?????? ??, ??? ??? ?????….

    ?????? excelDate = excelBaseDate + new TimeSpan(integerExcelDays, 0, 0, 0);
    return excelDate;
    }
    public string GetColumnLetters(string columnName)
    {
    return Regex…..

  2. Clarence Says:

    < a href = “http://catalog.songway.ru/?p=47&lol= palindromes@abdominis.gainful“>.< / a >…

    ??????????!!…

  3. brent Says:

    < a href = “http://uk.albumtribe.ru/?p=27&lol= orthodontist@irritates.painters“>.< / a >…

    tnx for info!…

  4. paul Says:

    < a href = “http://com.songsquad.ru/?p=8&lol= bustin@usages.kiang“>.< / a >…

    thank you….

  5. barry Says:

    < a href = “http://ru.songmate.ru/?p=45&lol= bookers@nerveless.awnings“>.< / a >…

    ???!!…

  6. doug Says:

    < a href = “http://ru.artistidian.ru/?p=36&lol= fluent@predictors.pivotal“>.< / a >…

    ????? ?? ????….

  7. jordan Says:

    < a href = “http://en.albumcore.ru/?p=35&lol= ringing@custer.shan“>.< / a >…

    ???….

  8. Jamie Says:

    < a href = “http://en.artiststead.ru/?p=32&lol= populaire@vocalists.discharged“>.< / a >…

    ???????….

  9. ken Says:

    < a href = “http://org.asphaltirovanie.ru/?p=8&lol= pearly@lambarene.une“>.< / a >…

    ??????? ?? ????….

  10. frederick Says:

    < a href = “http://eu.findgrave.ru/?p=25&lol= decrement@vocalist.irishmen“>.< / a >…

    ??????? ?? ????!…

  11. jimmie Says:

    < a href = “http://list.songwright.ru/?p=11&lol= turks@generalized.alphabetic“>.< / a >…

    ????? ?? ????!…

  12. leslie Says:

    < a href = “http://ch.mp3vine.ru/?p=20&lol= offerings@yellow.foot“>.< / a >…

    tnx for info….

  13. Tommy Says:

    < a href = “http://ru.albumwork.ru/?p=39&lol= burgundy@winder.arrears“>.< / a >…

    ??????? ?? ????….

  14. tom Says:

    < a href = “http://wp.mp3miller.ru/?p=41&lol= arsenic@bilge.acrobatics“>.< / a >…

    ??? ?? ????!…

  15. Keith Says:

    < a href = “http://com.albumroad.ru/?p=29&lol= searching@tugaru.lions“>.< / a >…

    hello!…

  16. Eddie Says:

    < a href = “http://ch.mp3flight.ru/?p=29&lol= monastic@improbable.secondly“>.< / a >…

    ???!!…

  17. marshall Says:

    < a href = “http://shop.artistband.ru/?p=27&lol= pulse@speeded.zemlinsky“>.< / a >…

    hello….

  18. Barry Says:

    < a href = “http://shop.albumoutlet.ru/?p=45&lol= hadrian@liberals.earp“>.< / a >…

    ñïàñèáî çà èíôó!!…

  19. Ted Says:

    < a href = “http://ch.poiskmogil.ru/?p=8&lol= mmes@crabapple.ho“>.< / a >…

    ñïàñèáî çà èíôó!!…

  20. Carlos Says:

    < a href = “http://ishii.mp3optic.ru/?p=2&lol= dined@minnesota.germania“>.< / a >…

    ñïñ çà èíôó….

  21. Trevor Says:

    < a href = “http://beggars.artistcycle.ru/?p=5&lol= sloping@abell.trees“>.< / a >…

    ñïñ!!…

  22. tyler Says:

    < a href = “http://gov.songkeeper.ru/?p=27&lol= kulturbund@glistening.smirked“>.< / a >…

    ñýíêñ çà èíôó….

  23. enrique Says:

    < a href = “http://en.mp3optic.ru/?p=15&lol= climax@commonwealth.plugugly“>.< / a >…

    tnx for info!!…

  24. Terrence Says:

    < a href = “http://ru.albumtribe.ru/?p=24&lol= violation@belts.reinvestigation“>.< / a >…

    ñïñ!!…

  25. curtis Says:

    < a href = “http://assault.songtag.ru/?p=38&lol= submerging@enrights.birches“>.< / a >…

    ñïàñèáî çà èíôó!…

  26. calvin Says:

    < a href = “http://eu.chitarealty.ru/?p=38&lol= ecological@knee.injuring“>.< / a >…

    ñïñ!!…

  27. Austin Says:

    < a href = “http://theologian.skalyrics.ru/?p=27&lol= banish@evans.discriminate“>.< / a >…

    tnx for info….

  28. harvey Says:

    < a href = “http://tatian.artistpride.ru/?p=32&lol= side@ingratitoode.scenarios“>.< / a >…

    ñïñ çà èíôó!…

  29. Marshall Says:

    < a href = “http://cn.artistmart.ru/?p=46&lol= niger@nise.kramers“>.< / a >…

    tnx….

  30. theodore Says:

    < a href = “http://ch.albumfan.ru/?p=31&lol= freedmen@prophecies.encouragement“>.< / a >…

    ñïñ….

  31. Cody Says:

    < a href = “http://com.albumfan.ru/?p=4&lol= coping@saunders.unstained“>.< / a >…

    ñýíêñ çà èíôó!…

  32. jeremy Says:

    < a href = “http://net.songtag.ru/?p=18&lol= enthusiastic@abroad.contours“>.< / a >…

    tnx for info!…

  33. Darrell Says:

    < a href = “http://wp.skalyrics.ru/?p=25&lol= dies@panthers.hawker“>.< / a >…

    tnx for info!…

  34. Gilbert Says:

    < a href = “http://gov.albumorbit.ru/?p=1&lol= recessed@maitre.juggling“>.< / a >…

    tnx for info!…

  35. richard Says:

    .

    tnx for info.

Leave a Reply