Results 1 to 22 of 22

Thread: [RESOLVED] Excel - create file very slow

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    Resolved [RESOLVED] 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 10:39 AM.

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

    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
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    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
    41,929

    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
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    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
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Excel - create file very slow

    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.

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    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
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    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,684

    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
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    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,684

    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
    41,929

    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
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    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
    gibra
    Guest

    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

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    Re: Excel - create file very slow

    gibra, your code is in VB6, It's a little pain to translate It into in C#. SpreadSheetLisght is still fastest for me, unfortunally I can't style worksheets same as I need them to.

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

    Re: Excel - create file very slow

    Quote Originally Posted by LuckyLuke82 View Post
    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.
    Greetings,

    Sorry for the very late reply, not sure how I missed this. Okay, here a thought, when there is a library this large you need to take time to study what is available. For instance to answer your question about hide and show a work sheet, my initial thought is that this must happen at the document level SLDocument so I did (where doc is a SLDocument) doc.Hide and noted HideWorksheet, then did the same for show and got ShowWorksheet

    Name:  1.jpg
Views: 4309
Size:  28.1 KB

    In regards to exporting, I would not even consider SpreadSheetLight, Excel automation etc but instead simply use OleDb to read a sheet into a DataTable and go from there. Unless I was using Gembox.Spreadsheet.
    Name:  1C.jpg
Views: 4215
Size:  33.0 KB

    In regards to the newer version of DocumentFormat.OpenXML version, I indicated this in my MSDN code sample, under "Building the sample".

    Bottom line is SpreadSheetLight will do the bulk of the work if you take a good deal of time examining it's documentation. When one looks at Gembox and Aspose.Cells (I have both) which of course are paid for libraries they are extremely robust yet are complex and take time to learn so if you really want to get this done spend time with the documentation.

  17. #17
    gibra
    Guest

    Re: Excel - create file very slow

    Quote Originally Posted by LuckyLuke82 View Post
    gibra, your code is in VB6, It's a little pain to translate It into in C#. SpreadSheetLisght is still fastest for me, unfortunally I can't style worksheets same as I need them to.
    Read better! Tehre are a NET version, also.

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    Re: Excel - create file very slow

    Quote Originally Posted by gibra View Post
    Read better! Tehre are a NET version, also.
    Sorry, I didn't notice. I've downloaded your sample, but I'm afraid I can't help much with that, you use Oledb connection which I pressume works better than Oracle, but requires some extra installation on PC which is not desirable (words of our system administrators ). But I've tried same and no better results in C#. Thanks for help though.
    Last edited by LuckyLuke82; Dec 11th, 2017 at 01:58 AM.

  19. #19

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    Re: Excel - create file very slow

    Quote Originally Posted by kareninstructor View Post
    Greetings,

    Sorry for the very late reply, not sure how I missed this. Okay, here a thought, when there is a library this large you need to take time to study what is available. For instance to answer your question about hide and show a work sheet, my initial thought is that this must happen at the document level SLDocument so I did (where doc is a SLDocument) doc.Hide and noted HideWorksheet, then did the same for show and got ShowWorksheet

    Name:  1.jpg
Views: 4309
Size:  28.1 KB

    In regards to exporting, I would not even consider SpreadSheetLight, Excel automation etc but instead simply use OleDb to read a sheet into a DataTable and go from there. Unless I was using Gembox.Spreadsheet.
    Name:  1C.jpg
Views: 4215
Size:  33.0 KB

    In regards to the newer version of DocumentFormat.OpenXML version, I indicated this in my MSDN code sample, under "Building the sample".

    Bottom line is SpreadSheetLight will do the bulk of the work if you take a good deal of time examining it's documentation. When one looks at Gembox and Aspose.Cells (I have both) which of course are paid for libraries they are extremely robust yet are complex and take time to learn so if you really want to get this done spend time with the documentation.
    Yes Karen, I't s quite huge documentation and I will need time to study It, in future, now I'm in a kind of hurry. I just never thought that working with Excel is such a wide problem, what I've done with Interop in VB.Net worked allways fine but not in C#. I've read something about that this problem exists because of totally different environments of C# and Interop which they are running on.

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    Re: Excel - create file very slow

    I've solved my speed issues a bit. What I did was stick with fammilliar Interop. It's not kind of ideal solution I reckon, but here is what I did:

    1. Instead of doing loop and write data to each cell I reconstructed code to write data in array:

    Code:
      private void WriteData(System.Data.DataTable dt, Worksheet ws)
            {
                //Column names         
                object[] col = new object[dt.Columns.Count];
                for (var j = 0; j <= dt.Columns.Count - 1; j++)
                {
                   col[j] = dt.Columns[j].ColumnName.ToString();
                }
    
                Range a1 = (Range)ws.Cells[3, 2];
                Range a2 = (Range)ws.Cells[3, dt.Columns.Count];
                Range range_names = ws.get_Range(a1, a2);
    
                //borders
                range_names.Borders.LineStyle = XlLineStyle.xlDouble;
                range_names.Interior.Color = Color.LightSteelBlue;
    
                range_names.Value = col;
    
                //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    
                //Data
                object[,] xdata = new object[dt.Rows.Count, dt.Columns.Count];
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    DataRow dr = dt.Rows[r];
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        xdata[r, c] = dr[c];
                    }
                }
              
                Range c1 = (Range)ws.Cells[4, 2];
                Range c2 = (Range)ws.Cells[4 + dt.Rows.Count-1, dt.Columns.Count];
                Range range = ws.get_Range(c1, c2);
    
                //borders
                range.Borders.LineStyle = XlLineStyle.xlDot;
                range.Borders.Weight = XlBorderWeight.xlHairline;
                range.Borders.Color = ColorTranslator.ToOle(Color.LightBlue);
    
                range.Value = xdata;
    
                //some more styling
                ws.Columns.AutoFit();
                ws.Columns.Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                ws.Application.ActiveWindow.DisplayGridlines = false;
                ws.Application.ActiveWindow.SplitRow = 3;
                ws.Application.ActiveWindow.FreezePanes = true;
    
            }
    2. When I load data to export from Oracle DB, I call this void in another thread:

    Code:
        using (OracleDataAdapter dad = new OracleDataAdapter())
               {
                   dad.SelectCommand = MyOracleCommand;
                   dad.Fill(dt);
                }
               
                var c = new Thread(() => WriteData(dt,xlWorkSheet));
                c.Start();
    
                //...Remaining code

    As mentioned I'm not sure If this is an ideal solution, but It accelerated my performance a lot - from almost 20 seconds for exporting data to 0.4 sec. But that was testing with a small amount of data, I have to test It in larger scale. And It still doesn't solve multiple Excel version problem, so I guess I'll have to try same with late binding. It might be worth for someone to try same approach though.
    Last edited by LuckyLuke82; Dec 11th, 2017 at 01:48 AM.

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

    Re: Excel - create file very slow

    You might find a solution in the following link for multiple versions

    https://docs.microsoft.com/en-us/dot...semblies-in-vs

    In regards to working with Excel between C# and VB.NET, in many cases there are slight differences to get to the same result which as I'm sure you have notice can be troublesome yet with a little time spent on a conversion it's not that bad.

    Also I wanted to toss in that Gembox SpreadSheet library (a paid for library) makes this easy too.
    Code:
    Public Sub ImportFromDataTable(ByVal pDataTable As DataTable, ByVal pFileName As String, ByVal pWorksheetName As String, Optional ByVal pColumnHeaders As Boolean = True)
    	Dim excelFile As New ExcelFile()
    	Dim workSheet As ExcelWorksheet = excelFile.Worksheets.Add(pWorksheetName)
    	workSheet.InsertDataTable(pDataTable, New InsertDataTableOptions() With {.ColumnHeaders = pColumnHeaders, .StartRow = 0})
    
    	If pColumnHeaders Then
    		Dim style As New CellStyle(excelFile)
    		style.Font.Color = System.Drawing.Color.Blue
    		style.Font.Weight = ExcelFont.MaxWeight
    		style.WrapText = True
    
    		' non intrustive bottom border
    		style.Borders.SetBorders(MultipleBorders.Bottom, SpreadsheetColor.FromName(ColorName.Text2Lighter80Pct), LineStyle.Medium)
    
    		workSheet.Cells.GetSubrangeAbsolute(0, 0, 0, pDataTable.Columns.Count - 1).Style = style
    	End If
    
    	workSheet.AutoFitColumnsCalculateMaxUsedColumns()
    
    	excelFile.Save(pFileName)
    
    End Sub

    Thinking about speed test, many times as a payload increases so does the time to complete the task so I would load more data than you expect for a proper test.

  22. #22

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    Re: Excel - create file very slow

    Thanks for link, I will try that out. By the views of this thread I reckon that this problem is facing a lot of programmers , so for everyone else - I tested with larger amount of data and tests were reasonable for me in C#. So writing data in arrays instead looping each cells combined with executing code in new thread speeds things up significantly. I even tested writing data vertically in worksheet cells, for my master-detail export from 4 tables, and I must say that I'm satisfied.

Posting Permissions

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



Click Here to Expand Forum to Full Width