Construct large object in c# with excel values-CodePudding

I need to create collection of arround 900 object, but constructor is taking too much time to read excel value, arround 4 minutes is necessary to construct whole entity. Is there a faster way ? Below is object property

public class entityDataObject
{
    public string var1{ get; set; }
    public string var2 { get; set; }
    public string var3 { get; set; }
    public string var4 { get; set; }
    public string var5 { get; set; }
    public string var6{ get; set; }
    public string var7{ get; set; }
    public string var8{ get; set; }
    public string var9{ get; set; }
    public string var10{ get; set; }
    public string var11{ get; set; }
    public string var12{ get; set; }
}

then I iterate my excel values into below.

for (int i = firstRow   1; i <= lastRow; i  )
{
    entityDataObject newObject = (new entityDataObject()
    {
        var1= currentws.Cells[i, Column1].Value.ToString(),
        var2= currentws.Cells[i, Column2].Value.ToString(),
        var3= currentws.Cells[i, Column3].Value.ToString(),
        var4= currentws.Cells[i, Column4].Value.ToString(),
        var5= currentws.Cells[i, Column5].Value.ToString(),
        var6= currentws.Cells[i, Column6].Value.ToString(),
        var7= currentws.Cells[i, Column7].Value.ToString(),
        var8= currentws.Cells[i, Column8].Value.ToString(),
        var9= currentws.Cells[i, Column9].Value.ToString(),
        var10= currentws.Cells[i, Column10].Value.ToString(),
        var11= currentws.Cells[i, Column11].Value.ToString(),
        var12= currentws.Cells[i, Column12].Value.ToString(),
    });

    entityCollection.Add(newObject);

};

return entityCollection;

CodePudding user response：

The problem is likely not with building the objects, the problem is most likely due to the speed of the Excel file reader being used. The sample below uses the ExcelDataReader Nuget package (I have no affiliation, I've only used it in the past) which I've found to be very performant, especially compared to Microsoft's Office interop libraries.

using (var fs = File.OpenRead("[filePath]"))
{
    using (var reader = ExcelReaderFactory.CreateOpenXmlReader(fs))
    {
        var workbook = reader.AsDataSet();
        var worksheet = workbook.Tables[0];

        for (var i = 1; i < worksheet.Rows.Count; i  ) // start at 1 to skip header
        {
            var dataRow = worksheet.Rows[i].ItemArray.Select(o => o.ToString()).ToArray();
            
            var newObj = new EntityDataObject()
            {
                // populate your object from dataRow
            };
            entityCollection.Add(newObj);
        }

        return entityCollection;
    }
}

CodePudding user response：

The problem is not constructing the objects, the problem is that Excel automation is slow. Automation is when you have to open an Excel instance to work with the worksheet, i.e., this is when you are using Office Interop.

Use a library that doesn't need Microsoft Office to be installed. E.g., the open source library NPOI written in C# or ExcelDataReader (see @Daevin's comments).

CodePudding user response：

Thanks for those who helped, i managed to find a solution that allow me not to change whole code. The idea is to get whole workbook value in an object as below

object[,] virtualCells = new object[lastRow, maxColumnValue];
virtualCells =currentws.Range[currentws.Cells[firstRow,minColumnValue],
              currentws.Cells[lastRow, maxColumnValue]].value;

then just parsing the data within is. It takes 4ms, very fast.

for (int i = firstRow   1; i <= lastRow; i  )
{
    entityDataObject newObject = (new entityDataObject()
    {
        var1= virtualCells[i, Column1].Value.ToString(),
        var2= virtualCells[i, Column2].Value.ToString(),
        var3= virtualCells[i, Column3].Value.ToString(),
        var4= virtualCells[i, Column4].Value.ToString(),
        var5= virtualCells[i, Column5].Value.ToString(),
        var6= virtualCells[i, Column6].Value.ToString(),
        var7= virtualCells[i, Column7].Value.ToString(),
        var8= virtualCells[i, Column8].Value.ToString(),
        var9= virtualCells[i, Column9].Value.ToString(),
        var10= virtualCells[i, Column10].Value.ToString(),
        var11= virtualCells[i, Column11].Value.ToString(),
        var12= virtualCells[i, Column12].Value.ToString(),
    });

    entityCollection.Add(newObject);

};

return entityCollection;