How to change text format of an Excel cell using C #?

Question

I am currently writing an application (C #) for creating reports in an excel file based on other reports.

The problem is that after receiving a number from a specific sheet and copying to another, the target cell is not formed correctly and the number is not displayed correctly.

Eg : Number from source : 14.34 Number on destination : 14.345661 

How can I format the destination cell so that the number formatting is the same as the original cell.

Thanks!

Answer 1

The format of this cell / range in excel can be set using code.

 public void SetCustomFormat(string format, int r, int c) { ((Excel.Range)xlWks.Cells[r, c]).NumberFormat = format; } 

In your particular case, I believe you will need to use the format "# .00" or "#. ##"

Answer 2

Here is a piece of code that I used to show you a common template for formatting cells. Obviously, there are some declared variables, but they should show you what you need.

 sheet.get_Range("A" + CurrentRowIndex.ToString(), ColPrefix + CurrentRowIndex.ToString()).Font.Bold = true; sheet.get_Range("A" + CurrentRowIndex.ToString(), ColPrefix + CurrentRowIndex.ToString()).Interior.Color = Color.Silver.ToArgb(); sheet.get_Range("A" + CurrentRowIndex.ToString(), ColPrefix + CurrentRowIndex.ToString()).BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); sheet.get_Range("A" + CompetencyStartRowIndex.ToString(), ColPrefix + CurrentRowIndex.ToString()).BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); 

This first line, assuming that CurrentRowIndex = 1 and ColPrefix = "B", replacing the variables with the received values, will translate to

 sheet.get_Range("A1", "B1").Font.Bold = true; 

Anyway, you want to set numberformat. (Coming ..)

 sheet.Cells[Row, Column].NumberFormat = "0.00"