CODEWITHSUNDEEP
c#.netexcelformattingnumeric
Zaveed Abbasi
Zaveed Abbasi

Reputation: 446

Change the Format of an Excel Column programmatically and prevent scientific Notation like exponent (E) in nubmber fields

i am generating an Excel from DataSet using the following code

int ColumnsCount;

        if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
            throw new Exception("ExportToExcel: Null or empty input table!\n");

        // load excel, and create a new workbook
        Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
        Excel.Workbooks.Add();

        // single worksheet
        Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;

        object[] Header = new object[ColumnsCount];

        // column headings               
        for (int i = 0; i < ColumnsCount; i++)
            Header[i] = DataTable.Columns[i].ColumnName;


        Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
        HeaderRange.Value = Header;
        HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BlanchedAlmond); //ConsoleColor.Yellow;//


         HeaderRange.Font.Bold = true;

        // DataCells
        int RowsCount = DataTable.Rows.Count;
        object[,] Cells = new object[RowsCount, ColumnsCount];

        for (int j = 0; j < RowsCount; j++)
            for (int i = 0; i < ColumnsCount; i++)
                Cells[j, i] = (DataTable.Rows[j][i]);

        Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;
        Worksheet.Columns.AutoFit();





                Worksheet.SaveAs(ExcelFilePath);
                Excel.Quit();
                Console.Write("Excel file saved!");

However one of my columns is being shown as 1.004E+12 instead of 1004000003451, although it a string column being returned from sql server as varchar. How can i prevent it? Can any one help?

Upvotes: 0

Views: 2308

Answers (1)

Zaveed Abbasi
Zaveed Abbasi

Reputation: 446

In the end it was simple, just used .columns method

Worksheet.Columns("A").NumberFormat ="@";

Upvotes: 2

Related Questions