Excel - create file very slow-VBForums
Results 1 to 14 of 14

Thread: Excel - create file very slow

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    496

    Excel - create file very slow

    Can somebody take a look at my code for exporting data into a new Excel file. It works fast in vb.net, but super SLOW in C#:

    Code:
     private void ExportData()
            {
               System.Data.DataTable dt = new System.Data.DataTable();
    
                string Sql = "";
    
                Dictionary<string, Color> column_color = new Dictionary<string, Color>();
                string[] checkSQL = { "Table1", "Table2", "Table3" };
    
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
    
                xlApp.DisplayAlerts = false; 
                xlApp.Visible = false;
               
                Workbook xlWorkBook = xlApp.Workbooks.Add(Type.Missing);
                Worksheet xlWorkSheet = (Worksheet)xlWorkBook.Sheets["Sheet1"];
               
    
                try
                {
                    using (var connection = new OracleConnection(conn))
                    {
                        connection.Open();
    
                        using (OracleCommand export = new OracleCommand())
                        {
                            export.Connection = connection;
                    
                            Sql = RichTextBox1.Text.TrimEnd(Convert.ToChar(";"));
    
                            bool b = checkSQL.Any(s => Sql.ToUpper().Contains(s));
    
                            if (b == true)
                            {
                                export.CommandText = Sql;
    
                                using (OracleDataAdapter dad = new OracleDataAdapter())
                                {
                                    dad.SelectCommand = export;
                                    dad.Fill(dt);
                                }
    
                                for (var j = 0; j <= dt.Columns.Count - 1; j++)
                                {
                                    xlWorkSheet.Cells[3, j + 2] = dt.Columns[j].ColumnName.ToString(); 
                                    
                                    for (var i = 0; i <= dt.Rows.Count - 1; i++)
                                    {
                                        xlWorkSheet.Cells[i + 4, j + 2] = dt.Rows[i][j]; 
                                    }
    
                                }
                       
                                var last_row = xlWorkSheet.UsedRange.Rows.Count;
                                var last_column = xlWorkSheet.UsedRange.Columns.Count;
    
                                xlWorkSheet.Range["B4", xlWorkSheet.Cells[last_row + 2, last_column + 1]].Borders.LineStyle = XlLineStyle.xlDot;
                                xlWorkSheet.Range["B4", xlWorkSheet.Cells[last_row + 2, last_column + 1]].Borders.Weight = XlBorderWeight.xlHairline;
                                xlWorkSheet.Range["B4", xlWorkSheet.Cells[last_row + 2, last_column + 1]].Borders.Color = ColorTranslator.ToOle(Color.LightBlue);
                         
                                xlWorkSheet.Range["B3", xlWorkSheet.Cells[3, xlWorkSheet.UsedRange.Columns.Count + 1]].Borders.LineStyle = XlLineStyle.xlDouble;
                                xlWorkSheet.Range["B3", xlWorkSheet.Cells[3, xlWorkSheet.UsedRange.Columns.Count + 1]].Interior.Color = Color.LightSteelBlue;
                              
                                xlWorkSheet.Columns.AutoFit();
                                xlWorkSheet.Columns.Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                                xlWorkSheet.Application.ActiveWindow.DisplayGridlines = false;
                                xlWorkSheet.Application.ActiveWindow.SplitRow = 3;
                                xlWorkSheet.Application.ActiveWindow.FreezePanes = true;
                                
                                saveFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop).ToString();
                                saveFileDialog1.Filter = "Excel Workbook |*.xlsx|Excel 97-2003 Workbook |*.xls";
                                saveFileDialog1.Title = "export data";
                                saveFileDialog1.FileName = "export_" + DateTime.Now.ToShortDateString();
    
                         
                                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                                {
                                    xlWorkSheet.SaveAs(saveFileDialog1.FileName);
                                
                                    this.Close();
    
                                    DialogResult msg = MessageBox.Show("Export saved. Do you wist to open Excel file ?", "Export", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                                    if (msg == DialogResult.Yes)
                                    {
                                        xlApp.Visible = true;
                                        return;
                                    }
                                    else
                                    {
                                        xlWorkBook.Close();
                                        xlApp.Quit();
                                    }
                                }
                                else
                                {
                                   
                                    xlWorkBook.Close();
                                    xlApp.Quit();
                                }
                            }
                            else
                            {
                                MessageBox.Show("There was an error during exporting data." + Environment.NewLine + Environment.NewLine + "Error description: " + "Wrong SQL.", "Error");
                                xlWorkBook.Close();
                                xlApp.Quit();
                            }
    
    
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("There was an error during exporting data." + Environment.NewLine + Environment.NewLine + "Error description:" + ex.Message, "Error");
                    xlWorkBook.Close();
                    xlApp.Quit();
    
                }
    
            }
    Slow performance is because of all styling (borders etc.), I bolded that lines in red. In Vb.Net code executes in max 1.5 sec, but in C# It takes 16-20 seconds. What could be the cause of this ?

    P.S.: I'm using VS 2015, and I had to download NuGet package for Microsoft.Interop.Excel reference (version 15.0), maybe this library has some issues ?
    Last edited by LuckyLuke82; Oct 27th, 2017 at 11:39 AM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,359

    Re: Excel - create file very slow

    I haven't interacted with Excel from C#, but there are a few things that are unexpected or less than ideal.

    I'm surprised by Type.Missing when adding a new workbook, but that doesn't mean it is the wrong thing to do. I recommend checking.


    In terms of the red section of code, there are several things that are less than ideal that might make a bit of difference. The first is the specifying of ranges here:
    Code:
    xlWorkSheet.Range["B4", xlWorkSheet.Cells[last_row + 2, last_column + 1]].Borders.LineStyle = XlLineStyle.xlDot;
    xlWorkSheet.Range["B4", xlWorkSheet.Cells[last_row + 2, last_column + 1]].Borders.Weight = XlBorderWeight.xlHairline;
    xlWorkSheet.Range["B4", xlWorkSheet.Cells[last_row + 2, last_column + 1]].Borders.Color = ColorTranslator.ToOle(Color.LightBlue);
    As the same range applies to each line, using a variable for it could improve things (as each line could be doing multiple lookups etc). I'm also concerned by the mixing of a String address for the first part and a cell object for the second (if you are expecting 26 or less columns, you can simply add 64 to the column to get the ascii code of the column name, so easily build a String).

    This section should be using the last_column variable:
    Code:
    lWorkSheet.Range["B3", xlWorkSheet.Cells[3, xlWorkSheet.UsedRange.Columns.Count + 1]].Borders.LineStyle = XlLineStyle.xlDouble;
    xlWorkSheet.Range["B3", xlWorkSheet.Cells[3, xlWorkSheet.UsedRange.Columns.Count + 1]].Interior.Color = Color.LightSteelBlue;
    The speed of this can vary massively based on the data, so make sure you are comparing speeds using exactly the same data:
    Code:
    xlWorkSheet.Columns.AutoFit();
    The following section is rather dubious, partly because you use xlWorkSheet.Application (you should be using xlApp ), but also because ActiveWindow might not be what you expect:
    Code:
    xlWorkSheet.Application.ActiveWindow.DisplayGridlines = false;
    xlWorkSheet.Application.ActiveWindow.SplitRow = 3;
    xlWorkSheet.Application.ActiveWindow.FreezePanes = true;
    However... assuming you are using exactly the same data as in the VB version, I wouldn't expect a big change in speed for this section, so have you checked (using a StopWatch etc) that this section is where the speed loss is occurring?


    Quote Originally Posted by LuckyLuke82 View Post
    P.S.: I'm using VS 2015, and I had to download NuGet package for Microsoft.Interop.Excel reference (version 15.0), maybe this library has some issues ?
    Which version of Excel have you got installed?

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    496

    Re: Excel - create file very slow

    I'm surprised by Type.Missing when adding a new workbook, but that doesn't mean it is the wrong thing to do. I recommend checking.
    That was just one of my attempts to see If It will work any better, and It didn't.

    Thanks for all tips, you are right, I'll change everything and see what happens.

    However... assuming you are using exactly the same data as in the VB version, I wouldn't expect a big change in speed for this section, so have you checked (using a StopWatch etc) that this section is where the speed loss is occurring?
    Yes It's strange to me too, and yes I tried Stopwatch to monitor elapsed time. All "slow code" is happening in lines which I marked with red colour, so only styling of the sheets. Exactly same code in VB.NET runs fast.

    Which version of Excel have you got installed?
    Microsoft office 2010, but I will need to run this code on Excel 2013 too.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,359

    Re: Excel - create file very slow

    According to a web search Excel 2010 is version 14, so referencing version 15 (Excel 2013) could well be causing speed issues.

    As you want it to run against multiple versions of Excel it would be wise to use Late-Binding, but I don't have experience of that within C# so can't really help there... I expect there are lots of examples of it around.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    496

    Re: Excel - create file very slow

    ok, I'll try to test this too. However, in VB.NET I only added reference to Office 14.0 objects and everything worked fine. In C# this didn't work, so I manually added reference from Nuget package.

    About Late-binding - some example, explanation or maybe just a link with a complete code ? I've found some examples with only bits of code. But that looks like something that I would definitely need.

  6. #6

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    496

    Re: Excel - create file very slow

    Quote Originally Posted by kareninstructor View Post
    If open to other ways to do this, take a look at SpreadSheetLight. On the link below search for DataTable for an example.

    http://spreadsheetlight.com/sample-code/

    Excel need not even be installed and the library is free.
    Hi Karen, I've tried but I have troubles with referencing DocumentFormat.OpenXml. SpreadsheetLight keeps annoying me that I need a reference to assembly 'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'. But I can't get that Library in NuGet packages, only version 2.7.2.... Reference is needed to do any styling with cells, which I have a lot as you see in my post#1 code.

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    496

    Re: Excel - create file very slow

    si_the_geek, I also tried your suggestions but speed didn't improve, It takes 22 seconds to export minor data. I even changed Interop.Excel reference to 14.0. Late-binding is probably only thing left for me, but not much examples to find.

  9. #9
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,315

    Re: Excel - create file very slow

    Quote Originally Posted by LuckyLuke82 View Post
    Hi Karen, I've tried but I have troubles with referencing DocumentFormat.OpenXml. SpreadsheetLight keeps annoying me that I need a reference to assembly 'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'. But I can't get that Library in NuGet packages, only version 2.7.2.... Reference is needed to do any styling with cells, which I have a lot as you see in my post#1 code.
    See the first section of my code sample for SpreadSheetLight (ignore the language).
    https://code.msdn.microsoft.com/Alte...52c4a2?redir=0

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    496

    Re: Excel - create file very slow

    thanks for that, I guess I'll have to do It once more. Although I didn't really notice on how I could do these things (from post#1):

    Code:
    xlApp.DisplayAlerts = false; 
    xlApp.Visible = false;
    
    var last_row = xlWorkSheet.UsedRange.Rows.Count;
    var last_column = xlWorkSheet.UsedRange.Columns.Count;              
    
    xlWorkSheet.Range["B4", xlWorkSheet.Cells[last_row + 2, last_column + 1]].Borders.LineStyle = XlLineStyle.xlDot;
    xlWorkSheet.Range["B4", xlWorkSheet.Cells[last_row + 2, last_column + 1]].Borders.Weight = XlBorderWeight.xlHairline;
    xlWorkSheet.Range["B4", xlWorkSheet.Cells[last_row + 2, last_column + 1]].Borders.Color = ColorTranslator.ToOle(Color.LightBlue);
                       
    
    xlWorkSheet.Columns.AutoFit();
    xlWorkSheet.Columns.Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
    xlWorkSheet.Application.ActiveWindow.DisplayGridlines = false;
    xlWorkSheet.Application.ActiveWindow.SplitRow = 3;
    xlWorkSheet.Application.ActiveWindow.FreezePanes = true;
    Beside that, example project doesn't work for me until I downgrade DocumentFormat.OpenXml library to 2.5. I guess that would work same for me, too bad i allready deleted my sample project

  11. #11
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,315

    Re: Excel - create file very slow

    In regards to how to do things in this library, best to download the help file then if something is not apparent google it e.g.

    SpreadSheetLight Borders would get you this
    http://spreadsheetlight.com/download...ode/Borders.cs

    In help under SLPageSettings you will find ShowGridLines setter/getter

  12. #12
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,359

    Re: Excel - create file very slow

    Quote Originally Posted by LuckyLuke82 View Post
    si_the_geek, I also tried your suggestions but speed didn't improve, It takes 22 seconds to export minor data. I even changed Interop.Excel reference to 14.0. Late-binding is probably only thing left for me, but not much examples to find.
    I would be very surprised if Late Binding made it faster, but I wouldn't rule out the possibility so it may be worth trying.

    Quote Originally Posted by LuckyLuke82 View Post
    About Late-binding - some example, explanation or maybe just a link with a complete code ? I've found some examples with only bits of code. But that looks like something that I would definitely need.
    The examples will be short, because there is very little to change.

    To see the kind of difference it would make, take a quick look at my VB6 tutorial for working with Excel (link in my signature).

    The section on converting all Excel based code to Late Binding is about the same length as the section "Creating/opening a Workbook" (which just explains Workbooks.Add and Workbooks.Open). Converting C# based code to Late Binding will be slightly different to the VB6 version, but not much.

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    496

    Re: Excel - create file very slow

    In regards to how to do things in this library, best to download the help file then if something is not apparent google it e.g.
    Karen I'm stiil struggling with SpreadSheetLight, but I think that isn't an option for me. There's a lof of features, but a lot which I need are missing. For example I couldn't hide and show Excel WorkBook - only method I found for this is IsworkSheetHidden(string) and ShowWorksheet(string) which doesn't work even If I specify sheet name correctly. I'm offering user to open Excel file immidiately after export is done, so I need this.

    Secondly, more important I couldn't find a way to export from a datatable vertically, which I also need in one of my exports. Only method for export is ImportDataTable(), but I tried with this in a loop where I need It and It didn't work out for me.

    Am I missing something out here ?

    P.S.: doing tests with regular horizontal export everything worked fine and very fast, but only with a 2.5 DocumentFormat.OpenXML version, newer versions don't work.
    Last edited by LuckyLuke82; Nov 7th, 2017 at 03:16 AM.

  14. #14
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,423

    Re: Excel - create file very slow

    Quote Originally Posted by LuckyLuke82 View Post
    Microsoft office 2010, but I will need to run this code on Excel 2013 too.
    Then you have to use a LATE BINDING methods.
    See the topic:
    EARLY binding and LATE binding: using together!-VBForums
    http://www.vbforums.com/showthread.p...arly%20binding

    where you can find a link for VB.NET project too.

    N.B.
    To more speed, you should also set xlApp.Calculation = xlCalculationManual before and reset to xlCalculationAutomatic after

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.