Exporting to Excel column data type

Question

User-839467544 posted

While exporting to Excel i am not able to Convert column data type from General to Number or Currency. Its showing error on Excel cells "The Number in this cell is formatted as text or preceded by an apostrophe.". Can anyone provide me a link how to change column date type while exporting to excel.

Answer 1

User-824764785 posted

Call it a bug but in many excel sheet numeric values are appended with ' sign to display and format cell properly. You can use this code to find and remove apostrophe

public string RemoveApostrophe(string val)
{
    if(val.StartsWith("'"))
        val = val.SubString(1);
    if(val.Contains("'"))
        val = val.Replace("'", string.Empty);
    return val.Trim();
}

Answer 2

User1711366110 posted

While exporting to Excel i am not able to Convert column data type from General to Number or Currency. Its showing error on Excel cells "The Number in this cell is formatted as text or preceded by an apostrophe.".

    As per your case, the following similar thread which is having the codings with explanation about retain data types when exporting to Excel using Open XML.
This may guide you to fulfill your requirement:
http://forums.asp.net/t/1860267.aspx?How+to+retain+data+types+when+exporting+to+Excel+using+Open+XML

--
with regards,
Edwin