Click here to Skip to main content
14,928,376 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello !

Recently I incorporated my website from .net 3.5 framework to 2.0 as per company requirements. I am making use of EPPlus library to export data into excel sheets. So i had keyword var in my code which used to work fine on framework 3.5 but it is not supported by 2.0 so I changed some of my code and it started working fine. However the problem is that now when i try to download the data in my excel sheet it takes a lot of time. Earlier I was getting a exception of timeout so i increased the async-postback timeout of my script manager to 600 secs. Than it allowed me to download the data. Now when all things are wired up why does it takes so much time to write the data in excel sheet and to produce it ??? Here is my code (the commented section is my prev code which used to work super quick for me):
using (ExcelRange rng = ws.Cells[cellRange])
                       rng.Style.WrapText = true;
                       rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                       rng.Style.Font.Bold = false;
                       rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
                       ws.Row(1).Height = 30;

                       rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                       rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                   //prepare the range for the rows
                   string rowsCellRange = "A2:" + Convert.ToChar('A' + dtResult.Columns.Count - 1) + dtResult.Rows.Count * dtResult.Columns.Count;

                   //Format the rows
                   using (ExcelRange rng = ws.Cells[rowsCellRange])
                       rng.Style.WrapText = true;

                       rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                   //foreach (DataColumn dc in dtResult.Columns) //Creating Headings
                   //    var cell = ws.Cells[rowIndex, colIndex];

                   //    //Setting the background color of header cells to Gray

                   //    var fill = cell.Style.Fill;
                   //    var vallign = cell.Style.VerticalAlignment;
                   //    var hallign = cell.Style.HorizontalAlignment;

                   //    fill.PatternType = ExcelFillStyle.Solid;

                   //    cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                   //    cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                   //    cell.Style.Fill.BackgroundColor.SetColor(Color.Silver);
                   //    ws.Row(1).Height = 30;

                   //    //Setting Top/left,right/bottom borders.

                   //        var border = cell.Style.Border;

                   //        border.Bottom.Style =

                   //        border.Top.Style =

                   //        border.Left.Style =

                   //        border.Right.Style = ExcelBorderStyle.Thin;

                   //    //Setting Value in cell

                   //    cell.Value = dc.ColumnName;

                   //    colIndex++;


                   //foreach (DataRow dr in dtResult.Rows) // Adding Data into rows

                   //    colIndex = 1;

                   //    rowIndex++;

                   //    foreach (DataColumn dc in dtResult.Columns)
                   //    {

                   //        var cell = ws.Cells[rowIndex, colIndex];

                   //        //Setting Value in cell

                   //        cell.Value = (dr[dc.ColumnName]);

                   //        //Setting borders of cell

                   //        var border = cell.Style.Border;

                   //        border.Left.Style =

                   //            border.Right.Style = ExcelBorderStyle.Thin;

                   //        colIndex++;

                   //    }

                   //colIndex = 0;

                   Byte[] bin = pck.GetAsByteArray();

Please help me on this. The time lag is annoying and I am trying removing it but all in vain !
Thanx in advance !!!
n.podbielski 16-Oct-12 5:26am
Convert.ToChar('A' + dtResult.Columns.Count - 1)

Why are you converting to char char + int?
Taresh Uppal 16-Oct-12 5:29am
I am a newbie in this library. I guess we are making use of excel range which will allow creating headers as column names for our excel sheet. So by using this excel range is defined. Correct me if I am wrong somewhere !
Taresh Uppal 16-Oct-12 5:44am
I tried modifying my code with out that and it gave an error in cell range.This is ti specify that from A:XXX , Here XXX is the range of cell up to which data needs to be written !
Taresh Uppal 16-Oct-12 6:09am
will anybody help ??

1 solution

1) Use your debugger to identify where the slowdown occurs. Set a breakpoint at the beginning of the method and step through until you find out exactly which line(s) are causing the delay.
2) Figure out how to speed those lines up.
Taresh Uppal 17-Oct-12 1:18am
i have done it already

using (ExcelRange rng = ws.Cells[rowsCellRange])
rng.Style.WrapText = true;

rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

here it makes a delay and i have no other option to write and guess the excel row range ...:(

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900