Category: .NET

15 Jun 2016 07:37

Joined:	24/08/2009
Level:	Silver
Points:	358

Points:	5

Change excel sheet column in a "Text" Format

Hi,
I am creating a excel sheet and i want to change a particular column format into "Text" so i used below code but its giving me error.

In below code i am getting error "System.InvalidCastException" on this below line

((Microsoft.Office.Interop.Excel.IRange)(excelWorkSheet.Cells[1, i])).EntireColumn.NumberFormat = "Text";

Please help me how to resolve this. I am using .net framework 4.0 and 3.5 framework i was getting a diffrent error "Csharp.RuntimeBinder is not predefined".

for (int i = 1; i < table.Columns.Count; i++)
{
excelWorkSheet.Cells[1, i] = table.Columns[i].ColumnName;
if (table.Columns[i].ColumnName.Trim() == "ID1")
{
((Microsoft.Office.Interop.Excel.IRange)(excelWorkSheet.Cells[1, i])).EntireColumn.NumberFormat = "Text";
}

if (filetype == "PID")
{
//excelWorkSheet.Columns.NumberFormat = "0";
excelWorkSheet.get_Range("T:T",Type.Missing).NumberFormat = "0";
excelWorkSheet.get_Range("AA:AA", Type.Missing).NumberFormat = "0";
excelWorkSheet.get_Range("AH:AH", Type.Missing).NumberFormat = "0";
}
}

#766485

15 Jun 2016 23:28

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	2

Hi,

Based on your error the problem is "Convert from one format to another format", I suggest you to reformat this line again, and recheck the code.


excelWorkSheet.Cells[1, i].EntireColumn.NumberFormat = "Text";

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#766491

16 Jun 2016 00:58

Joined:	24/07/2014
Level:	Silver
Points:	107

Points:	4

Check tutorials on MSDN -

https://msdn.microsoft.com/en-us/library/office/ff196401.aspx

Or you can change number formatting of specific column using following code:

using Spire.Xls;

namespace ChangeNumberFormat
{
class Program
{
static void Main(string[] args)
{
Workbook wb = new Workbook();
wb.LoadFromFile(fileName);

Worksheet sheet = wb.Worksheets[0];
sheet.Columns[i].NumberFormat = "Text";

wb.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
}
}
}

#766492

16 Jun 2016 01:22

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	4

Hi
you can go through Below url

References :
"stackoverflow.com/questions/2067926/format-an-excel-column-or-cell-as-text-in-c"



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

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#766570

20 Jun 2016 23:24

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	4

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
use .NumberFormat property of cell object for it
see below snippet


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

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

#766585

21 Jun 2016 00:42

Joined:	05/06/2009
Level:	Gold
Points:	10245

Points:	3

You have to set the cell format for the cell of the excel sheet before changing the values. Following is the sample to change format of the cell.

MyExcelBook.Worksheets[1].Cells.NumberFormat = <Your format>;

Then you can add the values so that you will get the formatted value in the excel sheet.

By Nathan
Direction is important than speed

Change excel sheet column in a "Text" Format

About dotnetspider.com

Quick Links

General

Services