Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to iterate through Excel Worksheets only extracting data from specific columns

How do you iterate through an excel workbook with multiple worksheets only extracting data from say columns "C", "E" & "F"?

Here is the code I have thus far:

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;
    }

Thank you very much for your time, any help is appreciated.

like image 609
RobHurd Avatar asked Apr 22 '13 21:04

RobHurd


1 Answers

Editing your method, here's something should do what you're 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've created a int array to indicate which columns you'd like to read from, and then on each row we just loop through the array.

HTH, Z

like image 111
Zack Kay Avatar answered Oct 13 '22 01:10

Zack Kay