C# Excel Interop: How to format cells to store values as text

Question

I'm writing numbers to an Excel spreadsheet from a DataTable and all of these numbers are 5 digits long with preceding 0s if the number itself is less than 5 digits long (so 395 would be stored as 00395, for example).

When entering these numbers into Excel (using C#), it's storing them as numbers and eliminating the preceding 0s. Is there any way I can format the cells from C# in order to get the values to be stored as text rather than numbers?

See this answer http://stackoverflow.com/questions/2067926/format-an-excel-column-or-cell-as-text-in-c — katit, Sep 28 '11 at 13:24
@Katit: While the question matches, the answer here is better IMHO as it doesn't rely on third-party libraries. — Jeff Yates, Sep 28 '11 at 14:08

score 28 · Accepted Answer · answered Sep 28 '11 at 13:23

28

You can SomeRange.NumberFormat = "@"; or if you prefix the value with a ' and write it to the cell excel will treat it as a number-stored-as-text and provide a visual cue.

answered Sep 28 '11 at 13:23

Alex K.

171,639
30
264
288

score 2 · Answer 2 · edited Feb 20 '15 at 04:58

2

This answer just solved a major problem with a solution from one of our company's software, I had to retrieve the value as displayed, but once I set it to the new sheet, it was being inserted as a number. Simple solution. I cant vote up as yet, but down follows how it ended up.

for (int h = 1; h <= 1; h++)
{
    int col = lastColl(sheets);
    for (int r = 1; r <= src.Count; r++)
    {
        sheets.Cells[r, col + 1] = "'"+src.Cells[r, h].Text.ToString().Trim();
    }
}

edited Feb 20 '15 at 04:58

Alpha

7,586
8
59
92

answered Feb 19 '15 at 22:43

Khamul

51
5

This works whereas the solution to change the NumberFormat does not work in my case with a FedEx tracking code (14 digits). – Rob Oct 08 '18 at 18:17

score 0 · Answer 3 · answered Nov 10 '17 at 06:08

//where [1] is column number which you want to make text

ExcelWorksheet.Columns[1].NumberFormat = "@";

//If you want to format a particular column in all sheets in a workbook - use below code. Remove loop for single sheet along with slight changes.

//path were excel file is kept

string ResultsFilePath = @"C:\Users\krakhil\Desktop\TGUW EXCEL\TEST";

    Excel.Application ExcelApp = new Excel.Application();
    Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(ResultsFilePath);
    ExcelApp.Visible = true;

    //Looping through all available sheets
    foreach (Excel.Worksheet ExcelWorksheet in ExcelWorkbook.Sheets)
    {                
        //Selecting the worksheet where we want to perform action
        ExcelWorksheet.Select(Type.Missing);
        ExcelWorksheet.Columns[1].NumberFormat = "@";
    }

    //saving excel file using Interop
    ExcelWorkbook.Save();

    //closing file and releasing resources
    ExcelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
    Marshal.FinalReleaseComObject(ExcelWorkbook);
    ExcelApp.Quit();
    Marshal.FinalReleaseComObject(ExcelApp);

C# Excel Interop: How to format cells to store values as text

3 Answers3

Linked