C# reading Excel cell values using Microsoft.Office.Interop.Excel

Question

I am trying to pull Excel cell values. I am able to pull a row value successfully. What should I do to pull each cell value out of the row?

using Microsoft.Office.Interop.Excel;

string pathToExcelFile = @"C:\Users\MyName\Desktop\Log.xls";

Application xlApp = new Application();
Workbook xlWorkbook = xlApp.Workbooks.Open(pathToExcelFile, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

_Worksheet xlWorksheet = (_Worksheet)xlWorkbook.Sheets[1];
Range xlRange = xlWorksheet.UsedRange;

var rowValue = ((Range)xlRange.Cells[2, 1]).Value2.ToString();

It looks to me like it's stored in the Cells array - so one way would be to loop through each cell [`Row`, `Column`]. In the past, I have just dumped the entire sheet into a datatable in C# and read the values from there - but you can just change `.Cells[2, 1]` to `.Cells[2, 2]` to get the next cell over and continue looping in that way. — user1274820, Nov 01 '16 at 22:08
Duplicate? http://stackoverflow.com/questions/18993735/how-to-read-single-excel-cell-value — TZubiri, Nov 01 '16 at 22:10

user1274820 · Accepted Answer · 2016-11-01T22:37:31.453

6

Try this:

foreach (Range c in xlRange.Cells)
{
    Console.WriteLine("Address: " + c.Address + " - Value: " + c.Value);
}

Output from my test file:

Complete code:

string testingExcel = @"C:\TestingExcel.xlsx";
Application xlApp = new Application();
Workbook xlWorkbook = xlApp.Workbooks.Open(testingExcel, Type.Missing, true);
_Worksheet xlWorksheet = (_Worksheet)xlWorkbook.Sheets[1];
Range xlRange = xlWorksheet.UsedRange;
foreach (Range c in xlRange.Rows.Cells)
{
    Console.WriteLine("Address: " + c.Address + " - Value: " + c.Value);
}
xlWorkbook.Close();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);

Edited Input with multiple rows:

edited Nov 01 '16 at 22:37

answered Nov 01 '16 at 22:20

user1274820

7,786
3
37
74

with the same code I see complete row values displayed instead of each column. Can u paste your complete code which showed the above result. – Kurkula Nov 01 '16 at 22:28
I edited my post, but the code is very similar. Does your input file have all the values in single cells? – user1274820 Nov 01 '16 at 22:35
I have tab seperated file saved as .xls. – Kurkula Nov 01 '16 at 22:37
I tried saving as .xls and got the same results - can you please post an image of your input file? Notice I am using `.Value` instead of `.Value2`, though I doubt this makes much of a difference. http://stackoverflow.com/a/17363466/1274820 – user1274820 Nov 01 '16 at 22:38
let me verify and post – Kurkula Nov 01 '16 at 22:45
Your code works wen I created a seperate excel file and added values. I think the issue is I am dumping csv file to save as .xls and trying to read this xls file. Any suggestion at this point? – Kurkula Nov 01 '16 at 22:58

C# reading Excel cell values using Microsoft.Office.Interop.Excel

1 Answers1