You must Sign In to post a response.
  • Category: .NET

    Change excel sheet column in a "Text" Format

    I am creating a excel sheet and i want to change a particular column format into "Text" so i used below code but its giving me error.

    In below code i am getting error "System.InvalidCastException" on this below line

    ((Microsoft.Office.Interop.Excel.IRange)(excelWorkSheet.Cells[1, i])).EntireColumn.NumberFormat = "Text";

    Please help me how to resolve this. I am using .net framework 4.0 and 3.5 framework i was getting a diffrent error "Csharp.RuntimeBinder is not predefined".

    for (int i = 1; i < table.Columns.Count; i++)
    excelWorkSheet.Cells[1, i] = table.Columns[i].ColumnName;
    if (table.Columns[i].ColumnName.Trim() == "ID1")
    ((Microsoft.Office.Interop.Excel.IRange)(excelWorkSheet.Cells[1, i])).EntireColumn.NumberFormat = "Text";

    if (filetype == "PID")
    //excelWorkSheet.Columns.NumberFormat = "0";
    excelWorkSheet.get_Range("T:T",Type.Missing).NumberFormat = "0";
    excelWorkSheet.get_Range("AA:AA", Type.Missing).NumberFormat = "0";
    excelWorkSheet.get_Range("AH:AH", Type.Missing).NumberFormat = "0";
  • #766485

    Based on your error the problem is "Convert from one format to another format", I suggest you to reformat this line again, and recheck the code.

    excelWorkSheet.Cells[1, i].EntireColumn.NumberFormat = "Text";

    Give respect to your work, Instead of trying to impress your boss.

    Blog :

  • #766491
    Check tutorials on MSDN -

    Or you can change number formatting of specific column using following code:

    using Spire.Xls;

    namespace ChangeNumberFormat
    class Program
    static void Main(string[] args)
    Workbook wb = new Workbook();

    Worksheet sheet = wb.Worksheets[0];
    sheet.Columns[i].NumberFormat = "Text";

    wb.SaveToFile("result.xlsx", ExcelVersion.Version2010);

  • #766492
    you can go through Below url

    References :

    IWorkbook workbook = Factory.GetWorkbook();
    IRange cells = workbook.Worksheets[0].Cells;
    // Format column A as text.
    cells["A:A"].NumberFormat = "@";
    // Set A2 to text with a leading '0'.
    cells["A2"].Value = "01234567890123456789";
    // Format column C as text (SpreadsheetGear uses 0 based indexes - Excel uses 1 based indexes).
    cells[0, 2].EntireColumn.NumberFormat = "@";
    // Set C3 to text with a leading '0'.
    cells[2, 2].Value = "01234567890123456789";
    workbook.SaveAs(@"c:\tmp\TextFormat.xlsx", FileFormat.OpenXMLWorkbook);

    Name : Dotnet Developer-2015
    Email Id :

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766570
    If you set the cell formatting to Text prior to adding a numeric value with a leading zero, the leading zero is retained without having to skew results by adding an apostrophe
    use .NumberFormat property of cell object for it
    see below snippet

    // Pull in all the cells of the worksheet
    Range cells = xlWorkBook.Worksheets[1].Cells;
    // set each cell's format to Text
    cells.NumberFormat = "@";
    // reset horizontal alignment to the right
    cells.HorizontalAlignment = XlHAlign.xlHAlignRight;

    // now add values to the worksheet
    for (i = 0; i <= dataGridView1.RowCount - 1; i++)
    for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
    DataGridViewCell cell = dataGridView1[j, i];
    xlWorkSheet.Cells[i + 1, j + 1] = cell.Value.ToString();

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #766585
    You have to set the cell format for the cell of the excel sheet before changing the values. Following is the sample to change format of the cell.

    MyExcelBook.Worksheets[1].Cells.NumberFormat = <Your format>;

    Then you can add the values so that you will get the formatted value in the excel sheet.

    By Nathan
    Direction is important than speed

  • Sign In to post your comments