How can I extract a string from an excel cell?

Question

I know how to write to a cell, but how do I get an already existing string written inside a cell in an excel file into a string object so I can use it to generate data in other cells?

My code so far:

        Excel.ApplicationClass excelApp = new Excel.ApplicationClass();

        excelApp.Visible = true;

        Excel.Workbook excelWorkbook = excelApp.Workbooks.Open("C:\\Users\\user\\Desktop\\list.xls", 0, false, 5, "", "",
        false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

        Excel.Sheets excelSheets = excelWorkbook.Worksheets;

        string currentSheet = "Sheet1";
        Excel.Worksheet xlws = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

I can manipulate cell contents using something like:

xlws.Cells[1,1] = "foo";

But I'm having trouble doing the opposite, that is reading cell contents into a string in my program.

Any help would be appreciated.

score 11 · Answer 1 · edited May 23 '17 at 12:10

To avoid NullReferenceException, don't use .ToString() directly on types that can be null (object, dynamic, string, etc.). Here are few safer ways to convert object to string:

Convert.ToString(object) - returns String.Empty when object is null
object?.ToString() - VS 2015 Null-conditional Operator returns null if object is null
object?.ToString() ?? "" - C# null-coalescing operator ?? to return "" when object is null
object + "" - String.Concat to return "" when object is null
$"{object}" - VS 2015 string interpolation is transformed at compile time to invoke an equivalent String.Format("{0}", object) call, and returns "" when object is null

Posts like this make me miss the SO Documentation. This would have been a perfect entry. Thanks! — webworm, Jan 08 '18 at 14:11

Neil Knight · Accepted Answer · 2011-04-13T08:19:27.080

8

~~string myString = xlws.Cells[1, 1].Value as string;~~

string myString = ((Excel.Range)workSheet.Cells[1, 1]).Value2.ToString();

edited Apr 13 '11 at 08:19

answered Apr 13 '11 at 08:08

Neil Knight

47,437
25
129
188

I gave Slai an upvote because he is right when dealing with strings, you should use `Convert.ToString(object)` instead of `ToString()`. Switching this in my project just now fixed my null reference. – BrewMate Feb 02 '17 at 16:59

score 2 · Answer 3 · answered Apr 13 '11 at 08:11

2

xlws.Cells[1, 1].Value has type object. So you should indicate the type For examples:

xlws.Cells[1,1].Value.ToString();

answered Apr 13 '11 at 08:11

Robert Tyson

71
2

score 2 · Answer 4 · answered Apr 13 '11 at 08:12

There are a couple of ways to get the values from an excel docuemnt but the way I think you're looking for is:

Range range = xlws.get_range("A1", "A1");
object[] data = range.get_Value(XlRangeValueDataType.xlRangeValueDefault);
string str = (string)data[0];

A nice article that explains the other ways too is available here

Hope that helps.

score 1 · Answer 5 · answered Mar 11 '17 at 04:06

1

string val = sheet.Cells[1, 6].Text;

This was the best method to get text from cell. I could get just same text without any change.

answered Mar 11 '17 at 04:06

Billy

11
1

score 0 · Answer 6 · answered Feb 28 '14 at 10:09

I can suggest one more way is (If you need to read string from row 3 column "I":
    Excel.Application objExcel = new Excel.Application();
    objExcel.Visible = true;
    Excel.Workbook objBook = o     objExcel.Workbooks.Open("C:\\myexcel.xlsx");
    Excel.Worksheet objsheet =  objBook.Worksheets["Sheet1"];
    string a = Convert.ToString(objsheet.Cells.get_Item(3,"I").Value);

How can I extract a string from an excel cell?

6 Answers6

Linked