Format an Excel column (or cell) as Text in C#?

Below is some code to format columns A and C as text in SpreadsheetGear for .NET which has an API which is similar to Excel - except for the fact that SpreadsheetGear is frequently more strongly typed. It should not be too hard to figure out how to convert this to work with Excel / COM:

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);

Disclaimer: I own SpreadsheetGear LLC

I've recently battled with this problem as well, and I've learned two things about the above suggestions.

1. Setting the numberFormatting to @ causes Excel to left-align the value, and read it as if it were text, however, it still truncates the leading zero.
2. Adding an apostrophe at the beginning results in Excel treating it as text and retains the zero, and then applies the default text format, solving both problems.

The misleading aspect of this is that you now have a different value in the cell. Fortuately, when you copy/paste or export to CSV, the apostrophe is not included.

Conclusion: use the apostrophe, not the numberFormatting in order to retain the leading zeros.

Use your WorkSheet.Columns.NumberFormat, and set it to string "@", here is the sample:

Excel._Worksheet workSheet = (Excel._Worksheet)_Excel.Worksheets.Add();
//set columns format to text format
workSheet.Columns.NumberFormat = "@";

Note: this text format will apply for your hole excel sheet!

If you want a particular column to apply the text format, for example, the first column, you can do this:

workSheet.Columns[0].NumberFormat = "@";

or this will apply the specified range of woorkSheet to text format:

workSheet.get_Range("A1", "D1").NumberFormat = "@";

I know this question is aged, still, I would like to contribute.

Applying Range.NumberFormat = "@" just partially solve the problem:

• Yes, if you place the focus on a cell of the range, you will read text in the format menu
• Yes, it align the data to the left
• But if you use the type formula to check the type of the value in the cell, it will return 1 meaning number

Applying the apostroph behave better. It sets the format to text, it align data to left and if you check the format of the value in the cell using the type formula, it will return 2 meaning text

//where [1] - 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);

You need to format the column to be a string.

You can use the link https://supportcenter.devexpress.com/ticket/details/t679279/import-from-excel-to-gridview

For converting the ExcelDataSource, you can also refer to https://supportcenter.devexpress.com/ticket/details/t468253/how-to-convert-exceldatasource-to-datatable