C# - Excel - Read number cell as string

[sjfleming]

Hi,

I'm trying to read an excel worksheet that has cells with decimal numbers with exponents of 2 or more.

When I read a cell that has a value of say 1002.00 in the cell, I'm getting 1002 when the code is reading the cell. In the formula bar in excel, the number is also displayed as 1002.

Here's my piece of code.

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

excelApp.Visible = true;
excelApp.ScreenUpdating = false;
excelApp.DisplayAlerts = false;

Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open("C:\test.xls", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;

string currentSheet = "Test Data";
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);

Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)excelWorksheet.UsedRange;

string str = (range.Cells[_row, _column] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();

I've also tried the following.

object[,] values = (object[,])range.Value2;
string str = values[_row, _column].ToString();

Is there any way I can read the value as 1002.00 without changing the cell in the worksheet to Text. I want to avoid this if possible.
Thanks.

[kvprajapati]

string str = ((double)values[_row, _column]).ToString("N2");

[sjfleming]

Thanks for that. That works ok but I need further help on this.

The application I'm developing creates an xml file from the data in the excel sheet. I used to save this sheet off as a csv file and read from that. But now I'm trying to read it from the xls doc instead of the csv file.

Anywho the values with the numbers in the cells can have values like the following. 1001, 1002.00, 1003.1 1004.11, 1005.123.

So this is why I'm trying to read them as strings directly out of the worksheet.

[Geekitygeek]

Whats your question? You said the code adatapost gave you worked ok but havent said what elese you actually need help with :)

[sjfleming]

I'm trying to read the cells, formatted in excel as numbers, with various exponents, like 1001, 1002.00, 1003.1 1004.11, 1005.123

When reading each cell, I want to assign this value to a string variable and then move on to the next row and read the next cell that may have a different exponent.

[apegram]

Try getting the .NumberFormat property of the cell. Consider this adaptation of your code (at line 16) as my test. I created a worksheet and put the value 1 in cell A1. I formatted it to three decimal places.

double d = double.Parse((range.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range).Value2.ToString());
        string s = (range.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range).NumberFormat.ToString();
        Console.WriteLine(d.ToString(s));

Output: 1.000

[sjfleming]

Exactly what I need. Thanks for that.

[Geekitygeek]

I'm trying to read the cells, formatted in excel as numbers, with various exponents, like 1001, 1002.00, 1003.1 1004.11, 1005.123

I'm a little confused. You said that the formula bar shows 1002 but the cell shows 1002.00. That would suggest that the cell was formatted to show 2 decimal places. But if that was the case, you wouldnt have cells showing 1005.123 unless each cell has been formatted differently. If thats the case then apegram's method should extract the formatting.

To satisfy my own curiosity; how are the numbers generated?
It seems odd that you would store 1001 with no decimal places but then store 1002.00. If you dont require a minimum of 2 decimal places, what purpose does it serve to store the .00?

[sjfleming]

I'm a little confused. You said that the formula bar shows 1002 but the cell shows 1002.00. That would suggest that the cell was formatted to show 2 decimal places. But if that was the case, you wouldnt have cells showing 1005.123 unless each cell has been formatted differently. If thats the case then apegram's method should extract the formatting.

To satisfy my own curiosity; how are the numbers generated?
It seems odd that you would store 1001 with no decimal places but then store 1002.00. If you dont require a minimum of 2 decimal places, what purpose does it serve to store the .00?

Each row in my excel sheet contains a single test with various information. All the information in the excel sheet is then generated into an xml file through an app that I've developed. The xml file is run against NUnit automation software. This inturn fires off requests to a web service.

As part of the tests, I need to test values with different exponents so that is why I have numbers displayed above. The numbers are generated sequentially and I change around the exponents to suit my testing needs. Hope this solves your curiosity!