Click here to Skip to main content
14,545,989 members
Rate this:
Please Sign up or sign in to vote.
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
                       rng.Style.Fill.BackgroundColor.SetColor(Color.Silver);
                       ws.Row(1).Height = 30;

                       rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                       rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                       rng.Style.Font.Color.SetColor(Color.Black);
                   }

                   //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 !!!
Posted
Comments
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
Comments
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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100