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

I am losing the leading zeros when I copy values from a datatable to an Excel sheet. That's because probably Excel treats the values as a number instead of text.

I created the worksheet in C# and I am copying the values like so:

myWorksheet.Cells[i + 2, j] = dtCustomers.Rows[i][j - 1].ToString();

How do I format a whole column or each cell as Text? A related question, how to cast myWorksheet.Cells[i + 2, j] to show a style property in Intellisense?


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

[Solved] How to format excel cells to text while exporting data grid to , Add the Style to each item instead of row. Try like. Hide Copy Code. item.Cells[i]. Attributes.Add("style", "textmode"); Or. Hide Copy Code. row. I have one grid view called grdBeneficiary in that Beneficiary ID column contains very long number I am trying to export this grid to excel. after exporting to excel the Beneficiary ID is looking like 9.04103E+13 can any one please help me format the number to text in excel using c# code .presently am using below mentioned code.


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. If you try and manually add a leading zero value to a default sheet in Excel and then convert it to text, the leading zero is removed. If you convert the cell to Text first, then add your value, it is fine. Same principle applies when doing it programatically.

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

How to format an Excel file using C - CSharp, Format Excel Column (or Cell). Format Excel cells to store values as text. Formating Excel cells to text format will solve the problem of losing leading zeo values� 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:


Before your write to Excel need to change the format:

xlApp = New Excel.Application
xlWorkSheet = xlWorkBook.Sheets("Sheet1")

Dim cells As Excel.Range = xlWorkSheet.Cells

'set each cell's format to Text
cells.NumberFormat = "@"

'reset horizontal alignment to the right
cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

Formatting text in Excel in C, Xls offers ExcelFont class and developers can format the text of cells easily. By using RichText, we can add the text into cells and set font for it. Here comes to the � Using GemBox.Spreadsheet you can style and format an Excel file in C# and VB.NET. You can set custom number formatting, cell styles (alignment, indentation, rotation, borders, shading, protection, text wrapping, shrinking, etc.) and font formatting (size, color, font type, italic and strikeout properties, different levels of boldness, underlining, subscript and superscript).


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.

How to format an Excel cell with Text (not General) using c# openxml?, /how-to-format-an-excel-cell-with-text-not-general-using-c-openxml?forum= How to format an Excel cell with Text (not General) programmatically from Visual It seems the ExcelStyleSheet FormatID for both Text and General is 0, but to format cells in excel you could try something like this (requires a� How to Set Number Format in Excel using Spire.XLS in C#, VB.NET Number formatting is a way to control how a number is displayed using numeric format string. For example, you can use format string '0.00' to format 1234.5678 as 1234.57.


Solution that worked for me for Excel Interop:

myWorksheet.Columns[j].NumberFormat = "@";      // column as a text
myWorksheet.Cells[i + 2, j].NumberFormat = "@"; // cell as a text

This code should run before putting data to Excel. Column and row numbers are 1-based.

A bit more details. Whereas accepted response with reference for SpreadsheetGear looks almost correct, I had two concerns about it:

  1. I am not using SpreadsheetGear. I was interested in regular Excel communication thru Excel interop without any 3rdparty libraries,
  2. I was searching for the way to format column by number, not using ranges like "A:A".

Style and format Excel from C# / VB.NET applications, Spreadsheet you can style and format an Excel file in C# and VB.NET. Value = ".Style.Font.Color ="; worksheet.Cells[row, 2].Value = "Color. This method will guide you to format cells to prevent cell contents from overflowing in Excel. Please do as follows: 1. Select the column/list that you will prevent cell contents from overflowing, right click and select the Format Cells from the right-clicking menu. 2.


Retrieving the Cell Text as Displayed in Excel (Get Cell Text , It access display text of cells in region on your C hard drive, at C:\ Each cell has a different cell formatting, as shown on screen-shot below. 1. Select a column. Select a column with this problem. If you don't want to convert the whole column, you can select one or more cells instead. Just be sure the cells you select are in the same column, otherwise this process won't work. (See "Other ways to convert" below if you have this problem in more than one column.)


100 - Format Excel cells to store values as text in C, When programming with Microsoft Office Excel, you can interact with the objects provided by the Duration: 3:16 Posted: Aug 31, 2017 In the Text that Contains dialogue box write Red in Format cells that contain the text option and select Light Red Fill with Dark Red Text option and press OK. You will get to see the below result after that. Now, to highlight the Yellow text in the sheet do the following after you open the Text That Contain dialogue box. Don’t forget to


formating a cell to a string in excel when creating an excel in C# , I would like the first column of my excel to be a string. http://stackoverflow.com/ questions/2067926/format-an-excel-column-or-cell-as-text-in-c. Hi Guys. I just want to format the Excel sheet rows in C#. I am using Excel Object Library 12.0. What I actually need is, I want to select 3 entire rows together to set the properties only for the selected 3 rows. The same things for the column also. Is it possible? Actually <pre>WorkSheet.Rows</pre>