Format an Excel column or cell as Text in C

Question

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

User · Accepted Answer

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

User · Answer

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  lt   dataGridView1 RowCount - 1  i                          for  j   0  j  lt   dataGridView1 ColumnCount - 1  j                                  DataGridViewCell cell   dataGridView1 j  i                   xlWorkSheet Cells i   1  j   1    cell Value ToString

User · Answer

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

User · Answer

I ve recently battled with this problem as well  and I ve learned two things about the above suggestions      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    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

User · Answer

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

User · Answer

if  dtCustomers Columns j - 1  DataType    typeof decimal   amp  amp  dtCustomers Columns j - 1  DataType    typeof int              myWorksheet Cells i   2  j  NumberFormat

User · Answer

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

User · Answer

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    I am not using SpreadsheetGear  I was interested in regular Excel communication thru Excel interop without any 3rdparty libraries  I was searching for the way to format column by number  not using ranges like  A A

User · Answer

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

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

Examples related to c#

Examples related to excel

Examples related to export-to-excel