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

    Change excel sheet column in a "Text" Format

    Hi,
    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
    Hi,

    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.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #766491
    Check tutorials on MSDN -

    https://msdn.microsoft.com/en-us/library/office/ff196401.aspx

    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();
    wb.LoadFromFile(fileName);

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

    wb.SaveToFile("result.xlsx", ExcelVersion.Version2010);
    System.Diagnostics.Process.Start("result.xlsx");
    }
    }
    }

  • #766492
    Hi
    you can go through Below url

    References :
    "stackoverflow.com/questions/2067926/format-an-excel-column-or-cell-as-text-in-c"



    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 :kumaraspcode2009@gmail.com

    '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();
    }
    }

    Thanks
    Koolprasd2003
    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