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
Getting Type T from Ienumerable<T>
Determine If a Sequence Contains All Elements of Another Sequence Using Linq
How to Create a Wpf Usercontrol with Named Content
Entitytype Has No Key Defined Error
Why Is Httpcontext.Current Null After Await
Get the Value of Displayname Attribute
Generate and Sign Certificate Request Using Pure .Net Framework
How to Store Int[] Array in Application Settings
Why C# Doesn't Implement Indexed Properties
Why Does C# Xmldocument.Loadxml(String) Fail When an Xml Header Is Included
C# 6.0 Features Not Working with Visual Studio 2015