How to specify cell format to 'Text' when export Excel file

Question

I'm trying to export Excel file by using .ToString() when I add data to a datatable. The Excel file cell format is not text but is in a General format. This my code.

public static void CreateExcel(string filename,System.Data.DataTable table)
{
    if (filename != "")
    {
        Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        xlApp.DisplayAlerts = false;

        if (xlApp == null)
        {
            return;
        }

        Workbook xlWorkBook;
        Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);

        int Row = table.Rows.Count;
        int Column = table.Columns.Count;

        for (int i = 0; i <= Column - 1; i++)
        {
            xlWorkSheet.Cells[1, i + 1] = table.Columns[i].ToString();
        }

        for (int i = 0; i <= Row - 1; i++)
        {
            for (int j = 0; j <= Column - 1; j++)
            {
                xlWorkSheet.Cells[i + 2, j + 1] = table.Rows[i][j].ToString();
            }
        }

        xlWorkBook.SaveAs(@filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);

        xlWorkBook.Close();
        xlApp.Quit();
        Marshal.FinalReleaseComObject(xlWorkSheet);
        Marshal.FinalReleaseComObject(xlWorkBook);
        Marshal.FinalReleaseComObject(xlApp);

        GC.Collect();
    }
}

Are there any ways to change the cell format?

When you edit cells in Excel UI, you can force text format just by starting your value with the single quote (') character. Perhaps that works when set by code as well? — modal_dialog, Nov 09 '15 at 05:17

score 3 · Answer 1 · answered Nov 09 '15 at 05:42

3

Setting the number format of cell will help you to convert cell type into text.

Excel.Worksheet ws = workBook.Worksheets[1];
ws.Range["A2"].NumberFormat = "0";

answered Nov 09 '15 at 05:42

Deepak gupta

1,938
11
11

score 0 · Answer 2 · edited May 23 '17 at 11:45

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

Here are 2 links that will help you on every condition. Link 1 & Link 2

In your case :

for (int i = 0; i <= Row - 1; i++)
        {
            for (int j = 0; j <= Column - 1; j++)
            {
                xlWorkSheet.Cells[i + 2, j + 1] = table.Rows[i][j].ToString();
               //It will set each cell while iterates, 
               //I suggest, if its for all column, then set it for the excel sheet. 
               //or if you have a known range, then set for the range. 
                xlWorkSheet.Cells[i + 2, j + 1].NumberFormat = "@";
            }
        }

How to specify cell format to 'Text' when export Excel file

2 Answers2