CODEWITHSUNDEEP
sql-serverxmlexcelt-sqlasp.net-mvc-4
Flow
Flow

Reputation: 143

Export Excel file from database

I'm searching way how to generate XLS (XLSX) file from SQL Server in an ASP.NET MVC application.

Now I use EPPlus where I executing SQL query and result is saved to xlsx by this library. In this case I have performance issue. If I have many data so, generation time is longer (150 rows in average about 10 sec and it's long time).

First idea is execute query that return XML and then transform to xls by xslt template but here I have problem with open through Excel (format and extension doesn't match).

Second idea is execute query that return XSL (or XSLX) from DB but I don't know how do it because file is saved on server and I don't know how to send to client to download.

Third idea Have you someone experience with similar problems and can you help me?

Thanks for any ideas.

//EDIT Here is short example of my code:

int row = 0;

foreach(var obj1 in objList1)
{
    WriteObj1(ref row, obj1);

    var objList2 = GetObj2(obj1.Id);
    foreach(var obj2 in objList2)
    {
        WriteObj2(ref row, obj2);

        var objList3 = GetObj3(obj2.Id);
        foreach(var obj3 in objList3)
        {
            WriteObj3(ref row, obj3);

            var objList4 = GetObj4(obj3.Id);
            foreach(var obj4 in objList4)
            {
                WriteObj4(ref row, obj4);

                var objList5 = GetObj5(obj4.Id);
                foreach(var obj5 in objList5)
                {
                    WriteObj5(ref row, obj5);
                }
            }
        }
    }
}

and inner method for write is this code:

// create header in Excel
...

workSheet.Cells[row, column++].Value = someValue;

Upvotes: 0

Views: 333

Answers (2)

hash
hash

Reputation: 131

Since your updated question differs from the original one, I'm writing a new answer:

The problem with your code are the nested loops. This way you have thousands of accesses to the file. Instead try to generate e.g. an array with all the values and afterwards use Excel.Range to update/set the whole array at once.

Next time please use the search function first. There are dozens of (answered) questions about a slow excel insert.

Upvotes: 0

hash
hash

Reputation: 131

You can do this with a simple INSERT INTO.

Something like INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=[path];','SELECT * FROM [table]')

Where [path] is the desired directory and the SELECT statement should be self explaining.

For more information on OPENROWSET you can check out the Microsoft documentation

Upvotes: 1

Related Questions