How to read data fast from an excel and convert it to list from file stream

Question

I am using EPPlus.

The excel I am uploading has column headers in row number 2 . And from row 4 onward it has the data which may vary up to 2k records.

The way I am doing it , it takes a lot of time for reading 2k records and putting to a list .

   using (var excel = new ExcelPackage(hpf.InputStream))
    {                          

        var ws = excel.Workbook.Worksheets["Sheet1"];

        //Read the file into memory

        for (int rw = 4; rw <= ws.Dimension.End.Row; rw++)
        {
            if (!ws.Cells[rw, 1, rw, 24].All(c => c.Value == null))
            {
                int headerRow = 2;

                GroupMembershipUploadInput gm = new GroupMembershipUploadInput();

                for (int col = ws.Dimension.Start.Column; col <= ws.Dimension.End.Column; col++)
                {
                    var s = ws.Cells[rw, col].Value;

                    if (ws.Cells[headerRow, col].Value.ToString().Equals("Existing Constituent Master Id"))
                    {
                        gm.cnst_mstr_id = (ws.Cells[rw, col].Value ?? (Object)"").ToString();
                    }
                    else if (ws.Cells[headerRow, col].Value.ToString().Equals("Prefix of the constituent(Mr, Mrs etc)"))
                    {
                        gm.cnst_prefix_nm = (ws.Cells[rw, col].Value ?? (Object)"").ToString();
                    }

                }
                lgl.GroupMembershipUploadInputList.Add(gm);
            }
        }

GroupMembershipUploadInputList is the list of objects of type GroupMembershipUploadInput that I am adding the excel values to after reading from the cell wise.

Can it be done faster ? What am I missing here ?

Please help to improve the performance.

Check this: http://stackoverflow.com/questions/7613898/how-to-read-an-excel-spreadsheet-in-c-sharp-quickly — rochastuff, Jul 08 '16 at 04:29

score 0 · Answer 1 · answered Dec 25 '16 at 18:01

You are making a lot iterations there, for each row, you visit each column twice. I assume that you only need those two values per row and if so the following code would reduce time drastically:

using (var excel = new ExcelPackage(hpf.InputStream))
{

    var ws = excel.Workbook.Worksheets["Sheet1"];
    int headerRow = 2;
    // hold the colum index based on the value in the header
    int col_cnst_mstr_id = 2;
    int col_cnst_prefix_nm = 4;
    // loop once over the columns to fetch the column index
    for (int col = ws.Dimension.Start.Column; col <= ws.Dimension.End.Column; col++)
    {
        if ("Existing Constituent Master Id".Equals(ws.Cells[headerRow, col].Value))
        {
            col_cnst_mstr_id = col;
        }
        if ("Prefix of the constituent(Mr, Mrs etc)".Equals(ws.Cells[headerRow, col].Value))
        {
            col_cnst_prefix_nm = col;
        }
    }
    //Read the file into memory
    // loop over all rows
    for (int rw = 4; rw <= ws.Dimension.End.Row; rw++)
    {
        // check if both values are not null
        if (ws.Cells[rw, col_cnst_mstr_id].Value != null &&
            ws.Cells[rw, col_cnst_prefix_nm].Value != null)
        {
            // the correct cell will be selcted based on the column index
            var gm = new GroupMembershipUploadInput
            {
                cnst_mstr_id = (string) ws.Cells[rw, col_cnst_mstr_id].Value ?? String.Empty,
                cnst_prefix_nm = (string) ws.Cells[rw, col_cnst_prefix_nm].Value ?? String.Empty
            };

            lgl.GroupMembershipUploadInputList.Add(gm);
        }
    }
}

I removed the inner column loop and moved it to the start of the method. There it is used to just get the columnindex for each field you're interested in. The expensive null check can now also be reduced. To fetch the value, all that is now needed is a simple index lookup in the row.

How to read data fast from an excel and convert it to list from file stream

1 Answers1