How to iterate over Excel worksheets only by retrieving data from specific columns

Question

How do you iterate over an Excel workbook with multiple sheets, just extract data from columns "C", "E" and "F"?

Here is the code that I still have:

public static string ExtractData(string filePath) { Excel.Application excelApp = new Excel.Application(); Excel.Workbook workBook = excelApp.Workbooks.Open(filePath); string data = string.Empty; int i = 0; foreach (Excel.Worksheet sheet in workBook.Worksheets) { data += "******* Sheet " + i++.ToString() + " ********\n"; //foreach (Excel.Range row in sheet.UsedRange.Rows) //{ // data += row.Range["C"].Value.ToString(); //} foreach (Excel.Range row in sheet.UsedRange.Rows) { foreach (Excel.Range cell in row.Columns) { data += cell.Value + " "; } data += "\n"; } } excelApp.Quit(); return data; } 

Thanks so much for your time, any help is appreciated.

Answer 1

Editing your method, here something should do what you are looking for:

 public static string ExtractData(string filePath) { Excel.Application excelApp = new Excel.Application(); Excel.Workbook workBook = excelApp.Workbooks.Open(filePath); int[] Cols = { 3, 5, 6 }; //Columns to loop //C, E, F string data = string.Empty; int i = 0; foreach (Excel.Worksheet sheet in workBook.Worksheets) { data += "******* Sheet " + i++.ToString() + " ********\n"; foreach (Excel.Range row in sheet.UsedRange.Rows) { foreach (int c in Cols) //changed here to loop through columns { data += sheet.Cells[row.Row, c].Value2.ToString() + " "; } data += "\n"; } } excelApp.Quit(); return data; } 

I created an int array to indicate in which columns you want to read, and then in each row we just look at the array.

NTN, Z

Answer 2

You can use something like this to get column C, for example:

 var numberOfRows = sheet.UsedRange.Columns[3, Type.Missing].Rows.Count; var values = sheet.Range["C1:C" + numberOfRows].Value2; 

numberOfRows contains the number of lines on the sheet (I think it does not skip empty lines at the top, not sure). After that, you select a range from C1 to CN and get Value2, which contains the values. Keep in mind that an array of values ​​is actually a two-dimensional array. Now you can easily execute a for loop to get the elements:

 for (int i = 1; i <= values.Length; i++){ sb.Append(values[i, 1] + " "); } 

This can be optimized if the columns are next to each other and such, but the above code should start working.