Export to excel c# issue

Question

5.00/5 (1 vote)

See more:

C#

DataSet ds = new DataSet();
System.Data.DataTable dtcdts = (System.Data.DataTable)ViewState["gvcdts"];
dtcdts.TableName="FACTORY";
System.Data.DataTable dtnonfac = (System.Data.DataTable)ViewState["dtnonfac"];
dtnonfac.TableName = "NON-FACTORY";
System.Data.DataTable dtrans = (System.Data.DataTable)ViewState["dttrans"];
dtrans.TableName = "TRANSISTION";
ds.Tables.Add(dtcdts);
ds.Tables.Add(dtnonfac);
ds.Tables.Add(dtrans);
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

// Loop over DataTables in DataSet.
DataTableCollection collection = ds.Tables;
for (int i = collection.Count; i > 0; i--)
{
    Sheets xlSheets = null;
    Worksheet xlWorksheet = null;
    //Create Excel Sheets
    xlSheets = ExcelApp.Sheets;
    xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
    System.Data.DataTable table = collection[i - 1];
    xlWorksheet.Name = table.TableName;
    for (int j = 1; j < table.Columns.Count + 1; j++)
    {
        ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;
    }
    // Storing Each row and column value to excel sheet
    for (int k = 0; k < table.Rows.Count; k++)
    {
        for (int l = 0; l < table.Columns.Count; l++)
        {
            ExcelApp.Cells[k + 2, l + 1] =
            table.Rows[k].ItemArray[l].ToString();
        }
    }
    ExcelApp.Columns.AutoFit();
}

this is how i am exporting data to excel because i have to show data in three tabs of excel sheet.
But excel cell by cell exporting taking tOO long time in server like 10 mins please guide me where i can increase performance here
altogather i have 8000 line items i have to export to excel

Posted 15-Apr-14 21:22pm

Member 10057465

Updated 15-Apr-14 21:46pm

lukeer

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CBadger · Answer 1 · 2014-04-15T21:45:00

I will have to take a look at the code more intently. And as such will update this solution as needed. The first thing I can see by just scanning the code:

Have you thought of using a excel template. This way you do not need to create a new excel sheet etc from scratch but rather use a template. Beside this will be much faster even if the template is a sheet with no data etc.

2nd.

Quote:

C#

for (int j = 1; j < table.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < table.Rows.Count; k++)
{
for (int l = 0; l < table.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
table.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();

}

easier way to do this will be to rather just add cell for cell by using a counter and increasing the row with each new line. Basically setting the dataset table = to excel 'table'/sheet

so rather use the loop you have to get the data from dt but then set it = to the cell. The nice feature of EPPlus is null will fill in a ""(Blank) value into cell

basically.

C#

foreach(DataRow row in table)
{
    int row = 1 // Starting row in excel
    for (int l = 0; l < table.Columns.Count; l++)
    {
        ExcelApp.Cells[row, l + 1] =
row.ItemArray[l].ToString();
    } 
    row++;
}

Hope this will get you started on a solution to help make the code better/faster

nwdei · Answer 2 · 2014-04-16T05:56:00

I have run into this

SQL

problem in the past.  My solution was to copy an empty template Excel file as a starting point.  Using the Microsoft.ACE.OLEDB provider you can then Add Tables to the OLEDB "database" which is your Excel file.  Each table becomes a WorkSheet.  Records are added to the Table (e.g. WorkSheet) with normal SQL Insert commands.

The performance improvement achieved with this approach was anywhere from 10 to 15 times the cell-by-cell approach.

Here is the connection string used to create a connection to your copy of the template file

connExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\";Excel 12.0;HDR=YES\"";

Export to excel c# issue

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0