SpreadhsheetGear to read bulk excel file

Question

I want to read a bulk excel file using SpreadhsheetGear

    public ValueFormulaSet ReadData(string sheetName, string address)
    {
        var wrksh = this.Workbook.Worksheets[sheetName];

        var range = wrksh.Range[address];

        var result = new ValueFormulaSet();

        result.RowCount = range.RowCount;

        result.Values = (object[,])range.Value;

        result.Formulas = (object[,])GetFormulas(range);

        return result;
    }

I am wondering that this approach will read the whole data and populate it in the result.Values as I want to use bulk data file, this approach may consume much memory.

Is there any other way by which I can improve the approach for better memory optimization ?

I don't know how SpreadhsheetGear handles bulk files. I am looking for some lazy loading approach by which I can do some buffering, or I wish to do something like implementing IDataReader and use SqlBulkCopy.WriteToServerAsync Method (IDataReader).

Answer 1

SpreadsheetGear loads the whole excel file entirely. I confirmed this thing with their Technical support guys, they replied me

"our product is a full-fledged spreadsheet component with a calculation engine and rich workbook manipulation capabilities. Because of this, we must load an Excel file in its entirety—unlike other third-party spreadsheet products out there which might be able to get away with only partially loading parts of the file here or there"

SpreadsheetGear is good for the cases where there is requirement to perform some calculations and workbook manipulation. My requirement is just to import data from bulk excel files so I switched to OLE DB and used SQLBulkCopy class with the data reader.