Format an Excel Column (Or Cell) as Text in C#

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

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

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.

how to format the entire excel sheet to text in C#

Below solution worked for me..

string l_connectionString = "";

string l_filePath = txtComparisonFile.Text;

string l_fileExt = System.IO.Path.GetExtension(l_filePath);

if(l_fileExt.CompareTo(".xls") == 0)

l_connectionString = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + l_filePath + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';";

else
l_connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + l_filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = false;
string workbookPath = l_filePath;
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = l_selectedSheet;
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range(l_comparisonSheetRange);
excelWorksheet.Columns.NumberFormat = "@";
excelWorkbook.Save();
excelWorkbook.Close();
g_objExcelHelper.g_objDtCompare = g_objExcelHelper.GetDataTable(l_connectionString, l_selectedSheet, l_comparisonSheetRange, g_objExcelHelper.g_objDtCompare);

How to correctly store text as a number in Excel cell

Stay away from doubles and singles if possible.

`= Decimal("86,72",10) where 10 is base 10

should do it.

c# excel cell format to hh:mm:ss

After trying many solutions I found a way :
Create a Style object having a custom type like below :

Style timeStyle = new Style();
NumberFormat format = new NumberFormat(NumberFormatType.Custom);
format.Custom = "hh:mm";
timeStyle.NumberFormat = format;

Then pass this as the second parameter and first parameter as a day (in double)

writer.AddCell(TimeSpan.Parse(columnNames[i]).TotalSeconds / 86400, timeStyle );


Related Topics



Leave a reply



Submit