Extract an excel sheet row to an array

Question

I need to extract a row from an excel file and store it in an array. I have written the following code. But this seems not a good code as the execution time will increase drastically as the number of columns will increase. Is there any better way?

public static System.Array eEPlueExtractOneRowDataAgainstTSAndTCIDFromAnExcelSheet(string fullExcelFilePath, string excelSheetName, string testScenarioId, string testCaseId)
    {
        //Define variables
        System.Array myArray = null;

        //Define the excel file
        FileInfo desiredExcelFile = new FileInfo(fullExcelFilePath);


        //Manipulate Excel file using EPPlus
        ExcelPackage excelPkg = new ExcelPackage(desiredExcelFile);
        ExcelWorksheet workSheet = excelPkg.Workbook.Worksheets[excelSheetName];
        int totalRows = workSheet.Dimension.End.Row;
        int totalColums = workSheet.Dimension.End.Column;
        Console.WriteLine("Total Rows & Colums - " + totalRows + ":" + totalColums);
        Console.WriteLine("");


        for (int i = 1; i <= totalRows; i++)
        {
            if ( (workSheet.Cells[i, 1].Value.ToString() == testScenarioId) && (workSheet.Cells[i, 2].Value.ToString() == testCaseId) )
            {
                //Console.Write("Desired Row is: " + i);
                myArray = new string[totalColums];
                for (int j = 1; j < totalColums; j++)
                {
                    myArray.SetValue(workSheet.Cells[i, j].Value.ToString(), (j - 1));
                }
            }
        }


        return myArray;
    }

I dont want to do it using Microsoft.Office.Interop.Excel. I have to use EPPlus

you might consider posting this on http://codereview.stackexchange.com/ — Jeff Puckett, May 23 '16 at 15:59
Yes, each row will be having TestScenarioID as a must. Actually, the IDs themselves will be extracted from some other Excel sheet and will be used in this function. (Extracting scenarioIds that need to be executed from one sheet, and extracting data against those ids from another sheet) — oshirwani, May 23 '16 at 16:32
Refer to this link https://github.com/pruiz/EPPlus/blob/master/SampleApp/Sample8.cs you shall have better option using linq — Abbas, May 24 '16 at 16:32

score 0 · Answer 1 · edited May 23 '17 at 10:30

There is not much you can do, except bailing out early when you have found your row and maybe prevent creating too many strings in the if statement:

for (int i = 1; i <= totalRows; i++)
{
    if (testScenarioId.Equals(workSheet.Cells[i, 1].Value) && 
        testCaseId.Equals(workSheet.Cells[i, 2].Value) )
    {
        //Console.Write("Desired Row is: " + i);
        myArray = new string[totalColums];
        for (int j = 1; j < totalColums; j++)
        {
            myArray.SetValue(workSheet.Cells[i, j].Value.ToString(), (j - 1));
        }
        // stop iterating the for loop
        break;
    }
}

If the values in either column1 or column2 are sorted you implement a BinarySearch but if the data doesn't come sorted and you can't keep the sorted result stored somewhere it is useless to sort it first.

Extract an excel sheet row to an array

1 Answers1