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

c# excel interop
Closeratio picture Closeratio · Sep 28, 2011 · Viewed 26k times · Source

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?

Answer

Alex K. picture Alex K. · Sep 28, 2011

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.

Related questions

How do I properly clean up Excel interop objects?

I'm using the Excel interop in C# (ApplicationClass) and have placed the following code in my finally clause: while (System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet) != 0) { } excelSheet = null; GC.Collect(); GC.WaitForPendingFinalizers(); Although this kind of works, the Excel.exe process …

Read More
Exception from HRESULT: 0x800A03EC Error

I am getting "HRESULT: 0x800A03EC" error when running Excel add-in with following code: Excel.Range rng = ActiveSheet.Cells[x, y] as Excel.Range; string before = rng.Value2; string cleanV = System.Text.RegularExpressions.Regex.Replace(before, @"\s+", ""); rng.set_…

Read More
How to use Microsoft.Office.Interop.Excel on a machine without installed MS Office?

I'm writing an application which works with excel files. I need a feature to delete a sheet. I have to use an assembly Microsoft.Office.Interop.Excel.dll. It's running fine on developer machine but when I try to deploy …

Read More