none
C# Excel veri aktarma çok uzun sürüyor RRS feed

  • Soru

  • merhaba aşağıdaki kod ile datagrid deki verileri excele aktarıyorum fakat aktardığım veri satır sayısı 300 bine yakın haliyle teker teker aktarması çoook uzun sürüyor. nasıl bir yöntem ile hızlandıra bilirim teşekkür ederim.

     Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                excel.Visible = true;
                Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
                Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
                    int StartCol = 1;
                    int StartRow = 1;

                    for (int j = 0; j < DtgVeriSorguGorüntüle.Columns.Count; j++)
                    {
                        Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow, StartCol + j];
                        myRange.Value2 = DtgVeriSorguGorüntüle.Columns[j].HeaderText;
                    }
                    StartRow++;
                    for (int i = 0; i < DtgVeriSorguGorüntüle.Rows.Count; i++)
                    {
                        for (int j = 0; j < DtgVeriSorguGorüntüle.Columns.Count; j++)
                        {
                            try
                            {

                                Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow + i, StartCol + j];
                                myRange.Value2 = DtgVeriSorguGorüntüle[j, i].Value == null ? "" : DtgVeriSorguGorüntüle[j, i].Value.ToString();
                            }
                            catch
                            {
                                ;
                            }
                        }
                    }

    14 Mart 2016 Pazartesi 13:44

Yanıtlar

  • Ornek kodlar ayni yerde kafa karistirabilir, onun icin her birini ayri mesaj olarak, soyledigim 3 yontem icin kod orneklerini veriyorum. Orneklerde ayni 500,000 satirlik veri kullanip zamanlandi.

    Excel'e veri aktarmanin N tane yolu var ve bunlar sadece 3 tanesi. Bu 3'unu oncelikli secmemin nedenleri var:

    • Epplus kullanan yontem, digerlerinden cok daha yavas (55 sn) ancak hic excel kullanmadan dosyayi olusturma avantaji var (ornegin web server).
    • QueryTables.Add bildigim en hizli yontem (5 sn). Canli dataya baglanip guncellenebilir. OLEDB ya da ODBC ile calisiyor. Data, dosyanin parcasi olmadigindan -dataya baglaniyor-,  tasinabilirlik problemi var. QueryTables yerine, o zaman cozum olarak ADODB.Recordset yaratilip, recordset dosyasi ya da CopyFromRecordset() kullanilabilir. 
    • Array seklinde transfer, yine en hizli yontemlerden (17 sn). QueryTables.Add, CopyFromRecordSet() kullaniminin yapilamadigi durumlarda dusunulebilir (duzgun baglanilabilen bir data kaynagi yoksa ornegin, ya da data cok duzenli, tablo gibi degilse).
    • Yukaridakilerin disinda veriyi dis bir dosyaya CSV, DBF, XML, HTML, TXT ... olarak kopyalayip, Excel'de acmak, dogrudan sablon tarzindaki bir excel'e ADO ile yazmak ... gibi yontemler var. Bunlarin icerisinde CSV ve TXT, ancak ozel durumlarda ise yarar ve guvenilir. CSV, TXT formatlarin en buyuk derdi, data tiplerinin kayipsiz islenebilmesi. Eger sadece "sayi" ve "kisa basit yazi" gibi datatipleri yoksa, CSV-TXT kullanmayin derim. CSV ile ornek vermek gerekirse:
    Id,Kategoriler,Tarih
    1,"Icecek, Aperatif",1/1/2016 
    Id,Kategoriler,Tarih
    1,"Icecek
    Aperatif",1/1/2016

    Yukaridakilerin ikisi de gecerli CSV. Ancak Microsoft teknolojileri (Excel, MS SQL, access ...) sadece ilkini dogru parse "etme sansina sahip" (dogru parse etmeyebilir). Ikincisi de gecerli oysa ki.

    Jet ve ACE driverlari, eski dBaseIII formatindaki DBF'leri taniyor. CSV-TXT yerine tercih edilmeli. XML ve HTML formatlar excel tarafindan taniniyor, onlar da secenek (XML'in şema avantaji var).

    Bu aciklamalari yaptiktan sonra kod orneklerini vereyim :)

    Not: Tum orneklerde, arada SQL server kullaniliyor. Bu yaniltmasin, orada MS SQL'in tek amaci hizli bir sekilde data yaratmaktan ibaret (QueryTables.Add haric, orada yaratilan dataya baglaniyor).


    15 Mart 2016 Salı 12:31
    Yanıtlayıcı
  • Codeplex'ten Epplus kullanan yontem:

    void Main()
    {
      Stopwatch sw = new Stopwatch();
      sw.Start();
    
      string query = @"
      WITH  tally ( SiraNo, UniqueId, RandNumber )
            AS (
                 SELECT TOP 500000
                        ROW_NUMBER() OVER ( ORDER BY t1.object_id ), 
                        NEWID(),
                        CAST(CAST(CAST(NEWID() AS VARBINARY(4)) AS INT) AS DECIMAL) / 1000
                 FROM   master.sys.all_columns t1
                 CROSS JOIN master.sys.all_columns t2
               )
      SELECT  SiraNo, DATEADD(Minute, -SiraNo, GETDATE()) as OrnekDate, 
        UniqueId, RandNumber, 
        abs(RandNumber)%100 / 100 as pct
      FROM    [tally];";
    
      DataContext db = new DataContext(@"server=.\SQLexpress;trusted_connection=yes;database=Master");
      var data = db.ExecuteQuery<MyData>(query).ToList();
    
      ExcelPackage pck = new ExcelPackage();
    
      var wsEnum = pck.Workbook.Worksheets.Add("Sayfa Adi");
      wsEnum.Cells["A1"].LoadFromCollection(data, true, TableStyles.Medium9);
      wsEnum.Cells[wsEnum.Dimension.Address].AutoFitColumns();
    
      var fi = new FileInfo(@"d:\temp\ExcelOrnek.xlsx");
      if (fi.Exists)
      {
        fi.Delete();
      }
      pck.SaveAs(fi);
    
      sw.Stop();
      sw.Dump();
    }
    
    public class MyData
    {
      public Int64 SiraNo { get; set; }
      public DateTime? OrnekDate { get; set; }
      public Guid UniqueId { get; set; }
      public decimal RandNumber { get; set; }
      public decimal Pct { get; set; }
    }

    • Yanıt Olarak İşaretleyen ahmt15 17 Mart 2016 Perşembe 07:11
    15 Mart 2016 Salı 12:32
    Yanıtlayıcı
  • Array kullanan yontem:

    void Main()
    {
    
      Stopwatch sw = new Stopwatch();
      sw.Start();
      // ornek datatable
      var tbl = new System.Data.DataTable();
      new SqlDataAdapter(@"
      WITH  tally ( SiraNo, UniqueId, RandNumber )
            AS (
                 SELECT TOP 500000
                        ROW_NUMBER() OVER ( ORDER BY t1.object_id ), 
                        NEWID(),
                        CAST(CAST(CAST(NEWID() AS VARBINARY(4)) AS INT) AS DECIMAL) / 1000
                 FROM   master.sys.all_columns t1
                 CROSS JOIN master.sys.all_columns t2
               )
      SELECT  SiraNo, DATEADD(Minute, -SiraNo, GETDATE()) as OrnekDate, 
        UniqueId, RandNumber, 
        abs(RandNumber)%100 / 100 as pct
      FROM    [tally];", @"server=.\SQLExpress;Database=master;Trusted_Connection=yes;").Fill( tbl );
      
      object[,] arr = new object[tbl.Rows.Count+1,tbl.Columns.Count];
      for (int i = 0; i < tbl.Columns.Count; i++)
      {
        arr[0, i] = tbl.Columns[i].Caption;
      }
      for (int i = 0; i < tbl.Rows.Count; i++)
      {
        for (int j = 0; j < tbl.Columns.Count; j++)
        {
          arr[i+1,j] = tbl.Rows[i][j].ToString();
        }
      }
      
      // Excel dosya yarat ve arrayi koy
      Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
      var workbook = xl.Workbooks.Add();
      xl.Visible = true;
      
     Worksheet sht = ((Worksheet)workbook.ActiveSheet);
     Range target = (Range)sht.Range[ (Range)sht.Cells[1,1], (Range)sht.Cells[arr.GetUpperBound(0)+1,arr.GetUpperBound(1)+1] ];
     target.Value = arr;
     ((Range)sht.Range["D:D"]).NumberFormat = "$#,##0.0000";
     ((Range)sht.Range["E:E"]).NumberFormat = "0.00%";
     
     sw.Stop();
     sw.Dump();
    
    }

    • Yanıt Olarak İşaretleyen ahmt15 17 Mart 2016 Perşembe 07:11
    15 Mart 2016 Salı 12:32
    Yanıtlayıcı
  • QueryTables.Add kullanan yontem:

    void Main()
    {
    
      Stopwatch sw = new Stopwatch();
      sw.Start();
      
      string conStr = @"server=.\SQLExpress;Trusted_Connection=yes;Database=master";
    
      // ornek datatable
      using (var con = new SqlConnection(conStr))
      {
        con.Open();
        new SqlCommand(@"
      WITH  tally ( SiraNo, UniqueId, RandNumber )
            AS (
                 SELECT TOP 500000
                        ROW_NUMBER() OVER ( ORDER BY t1.object_id ), 
                        NEWID(),
                        CAST(CAST(CAST(NEWID() AS VARBINARY(4)) AS INT) AS DECIMAL) / 1000
                 FROM   master.sys.all_columns t1
                 CROSS JOIN master.sys.all_columns t2
               )
      SELECT  SiraNo, DATEADD(Minute, -SiraNo, GETDATE()) as OrnekDate, 
        UniqueId, RandNumber, 
        abs(RandNumber)%100 / 100 as pct
      into ##tmpTable
      FROM    [tally];", con).ExecuteNonQuery();
    
        // Excel dosya yarat
        Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
        var workbook = xl.Workbooks.Add();
        xl.Visible = true;
    
        string strCon = @"OLEDB;Provider=SQLNCLI11.0;" + conStr;
    
        Worksheet sht = ((Worksheet)workbook.ActiveSheet);
        Range target = (Range)sht.Range["A1"];
        sht.QueryTables.Add(strCon, target, "Select * from ##tmpTable").Refresh();
    
        ((Range)sht.Range["D:D"]).NumberFormat = "$#,##0.0000";
        ((Range)sht.Range["E:E"]).NumberFormat = "0.00%";
    
        con.Close();
      } 
     
     sw.Stop();
     sw.Dump();
    
    }

    • Yanıt Olarak İşaretleyen ahmt15 17 Mart 2016 Perşembe 07:11
    15 Mart 2016 Salı 12:33
    Yanıtlayıcı

Tüm Yanıtlar

  • CSV olarak text dosyasına kaydedip excel'de açın.
    14 Mart 2016 Pazartesi 13:45
  • "satır sayısı 300 bine yakın haliyle teker teker aktarması çoook uzun sürüyor" seklinde cok dogru bir tespite ulasmissiniz :) Kullandiginiz kod COM komutlarini cagiriyor ve Excel COM'un yavasligi ilk caglardan beri bilinen bir sey. Gereksiz yere cok sayida cagirirsaniz tabii ki yavas olacak. Ya COM kullanmayin, ya da gereginden fazla COM komutu cagirmayin.

    Elinizdeki o datagrid'in kaynagi her ne ise dogrudan ondan aktarin, boyle hucre hucre aktarmaya kalkarsaniz bitmez. En pratik cozumlerden birisi, codeplex'ten Epplus kutuphanesini kullanarak collection aktarmak. Eger otomasyon kullanmak istiyorsaniz, o zaman asil kaynagi kullanarak QueryTables.Add ile ekleyin. Bu soylediklerimin her ikisi de bu forumda defalarca orneklendi. 

    Not: Aktarma icin baska hizli bir yontem de, bir array olarak kopyala yapistir yapmak.
    14 Mart 2016 Pazartesi 14:40
    Yanıtlayıcı
  • cevap için teşekkür ederim fakat bir türlü yapamadım kod olarak örnek verme şansınız varmı ben de ona göre kendime göre uyarlıyayım
    15 Mart 2016 Salı 06:55
  • Ornek kodlar ayni yerde kafa karistirabilir, onun icin her birini ayri mesaj olarak, soyledigim 3 yontem icin kod orneklerini veriyorum. Orneklerde ayni 500,000 satirlik veri kullanip zamanlandi.

    Excel'e veri aktarmanin N tane yolu var ve bunlar sadece 3 tanesi. Bu 3'unu oncelikli secmemin nedenleri var:

    • Epplus kullanan yontem, digerlerinden cok daha yavas (55 sn) ancak hic excel kullanmadan dosyayi olusturma avantaji var (ornegin web server).
    • QueryTables.Add bildigim en hizli yontem (5 sn). Canli dataya baglanip guncellenebilir. OLEDB ya da ODBC ile calisiyor. Data, dosyanin parcasi olmadigindan -dataya baglaniyor-,  tasinabilirlik problemi var. QueryTables yerine, o zaman cozum olarak ADODB.Recordset yaratilip, recordset dosyasi ya da CopyFromRecordset() kullanilabilir. 
    • Array seklinde transfer, yine en hizli yontemlerden (17 sn). QueryTables.Add, CopyFromRecordSet() kullaniminin yapilamadigi durumlarda dusunulebilir (duzgun baglanilabilen bir data kaynagi yoksa ornegin, ya da data cok duzenli, tablo gibi degilse).
    • Yukaridakilerin disinda veriyi dis bir dosyaya CSV, DBF, XML, HTML, TXT ... olarak kopyalayip, Excel'de acmak, dogrudan sablon tarzindaki bir excel'e ADO ile yazmak ... gibi yontemler var. Bunlarin icerisinde CSV ve TXT, ancak ozel durumlarda ise yarar ve guvenilir. CSV, TXT formatlarin en buyuk derdi, data tiplerinin kayipsiz islenebilmesi. Eger sadece "sayi" ve "kisa basit yazi" gibi datatipleri yoksa, CSV-TXT kullanmayin derim. CSV ile ornek vermek gerekirse:
    Id,Kategoriler,Tarih
    1,"Icecek, Aperatif",1/1/2016 
    Id,Kategoriler,Tarih
    1,"Icecek
    Aperatif",1/1/2016

    Yukaridakilerin ikisi de gecerli CSV. Ancak Microsoft teknolojileri (Excel, MS SQL, access ...) sadece ilkini dogru parse "etme sansina sahip" (dogru parse etmeyebilir). Ikincisi de gecerli oysa ki.

    Jet ve ACE driverlari, eski dBaseIII formatindaki DBF'leri taniyor. CSV-TXT yerine tercih edilmeli. XML ve HTML formatlar excel tarafindan taniniyor, onlar da secenek (XML'in şema avantaji var).

    Bu aciklamalari yaptiktan sonra kod orneklerini vereyim :)

    Not: Tum orneklerde, arada SQL server kullaniliyor. Bu yaniltmasin, orada MS SQL'in tek amaci hizli bir sekilde data yaratmaktan ibaret (QueryTables.Add haric, orada yaratilan dataya baglaniyor).


    15 Mart 2016 Salı 12:31
    Yanıtlayıcı
  • Codeplex'ten Epplus kullanan yontem:

    void Main()
    {
      Stopwatch sw = new Stopwatch();
      sw.Start();
    
      string query = @"
      WITH  tally ( SiraNo, UniqueId, RandNumber )
            AS (
                 SELECT TOP 500000
                        ROW_NUMBER() OVER ( ORDER BY t1.object_id ), 
                        NEWID(),
                        CAST(CAST(CAST(NEWID() AS VARBINARY(4)) AS INT) AS DECIMAL) / 1000
                 FROM   master.sys.all_columns t1
                 CROSS JOIN master.sys.all_columns t2
               )
      SELECT  SiraNo, DATEADD(Minute, -SiraNo, GETDATE()) as OrnekDate, 
        UniqueId, RandNumber, 
        abs(RandNumber)%100 / 100 as pct
      FROM    [tally];";
    
      DataContext db = new DataContext(@"server=.\SQLexpress;trusted_connection=yes;database=Master");
      var data = db.ExecuteQuery<MyData>(query).ToList();
    
      ExcelPackage pck = new ExcelPackage();
    
      var wsEnum = pck.Workbook.Worksheets.Add("Sayfa Adi");
      wsEnum.Cells["A1"].LoadFromCollection(data, true, TableStyles.Medium9);
      wsEnum.Cells[wsEnum.Dimension.Address].AutoFitColumns();
    
      var fi = new FileInfo(@"d:\temp\ExcelOrnek.xlsx");
      if (fi.Exists)
      {
        fi.Delete();
      }
      pck.SaveAs(fi);
    
      sw.Stop();
      sw.Dump();
    }
    
    public class MyData
    {
      public Int64 SiraNo { get; set; }
      public DateTime? OrnekDate { get; set; }
      public Guid UniqueId { get; set; }
      public decimal RandNumber { get; set; }
      public decimal Pct { get; set; }
    }

    • Yanıt Olarak İşaretleyen ahmt15 17 Mart 2016 Perşembe 07:11
    15 Mart 2016 Salı 12:32
    Yanıtlayıcı
  • Array kullanan yontem:

    void Main()
    {
    
      Stopwatch sw = new Stopwatch();
      sw.Start();
      // ornek datatable
      var tbl = new System.Data.DataTable();
      new SqlDataAdapter(@"
      WITH  tally ( SiraNo, UniqueId, RandNumber )
            AS (
                 SELECT TOP 500000
                        ROW_NUMBER() OVER ( ORDER BY t1.object_id ), 
                        NEWID(),
                        CAST(CAST(CAST(NEWID() AS VARBINARY(4)) AS INT) AS DECIMAL) / 1000
                 FROM   master.sys.all_columns t1
                 CROSS JOIN master.sys.all_columns t2
               )
      SELECT  SiraNo, DATEADD(Minute, -SiraNo, GETDATE()) as OrnekDate, 
        UniqueId, RandNumber, 
        abs(RandNumber)%100 / 100 as pct
      FROM    [tally];", @"server=.\SQLExpress;Database=master;Trusted_Connection=yes;").Fill( tbl );
      
      object[,] arr = new object[tbl.Rows.Count+1,tbl.Columns.Count];
      for (int i = 0; i < tbl.Columns.Count; i++)
      {
        arr[0, i] = tbl.Columns[i].Caption;
      }
      for (int i = 0; i < tbl.Rows.Count; i++)
      {
        for (int j = 0; j < tbl.Columns.Count; j++)
        {
          arr[i+1,j] = tbl.Rows[i][j].ToString();
        }
      }
      
      // Excel dosya yarat ve arrayi koy
      Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
      var workbook = xl.Workbooks.Add();
      xl.Visible = true;
      
     Worksheet sht = ((Worksheet)workbook.ActiveSheet);
     Range target = (Range)sht.Range[ (Range)sht.Cells[1,1], (Range)sht.Cells[arr.GetUpperBound(0)+1,arr.GetUpperBound(1)+1] ];
     target.Value = arr;
     ((Range)sht.Range["D:D"]).NumberFormat = "$#,##0.0000";
     ((Range)sht.Range["E:E"]).NumberFormat = "0.00%";
     
     sw.Stop();
     sw.Dump();
    
    }

    • Yanıt Olarak İşaretleyen ahmt15 17 Mart 2016 Perşembe 07:11
    15 Mart 2016 Salı 12:32
    Yanıtlayıcı
  • QueryTables.Add kullanan yontem:

    void Main()
    {
    
      Stopwatch sw = new Stopwatch();
      sw.Start();
      
      string conStr = @"server=.\SQLExpress;Trusted_Connection=yes;Database=master";
    
      // ornek datatable
      using (var con = new SqlConnection(conStr))
      {
        con.Open();
        new SqlCommand(@"
      WITH  tally ( SiraNo, UniqueId, RandNumber )
            AS (
                 SELECT TOP 500000
                        ROW_NUMBER() OVER ( ORDER BY t1.object_id ), 
                        NEWID(),
                        CAST(CAST(CAST(NEWID() AS VARBINARY(4)) AS INT) AS DECIMAL) / 1000
                 FROM   master.sys.all_columns t1
                 CROSS JOIN master.sys.all_columns t2
               )
      SELECT  SiraNo, DATEADD(Minute, -SiraNo, GETDATE()) as OrnekDate, 
        UniqueId, RandNumber, 
        abs(RandNumber)%100 / 100 as pct
      into ##tmpTable
      FROM    [tally];", con).ExecuteNonQuery();
    
        // Excel dosya yarat
        Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
        var workbook = xl.Workbooks.Add();
        xl.Visible = true;
    
        string strCon = @"OLEDB;Provider=SQLNCLI11.0;" + conStr;
    
        Worksheet sht = ((Worksheet)workbook.ActiveSheet);
        Range target = (Range)sht.Range["A1"];
        sht.QueryTables.Add(strCon, target, "Select * from ##tmpTable").Refresh();
    
        ((Range)sht.Range["D:D"]).NumberFormat = "$#,##0.0000";
        ((Range)sht.Range["E:E"]).NumberFormat = "0.00%";
    
        con.Close();
      } 
     
     sw.Stop();
     sw.Dump();
    
    }

    • Yanıt Olarak İşaretleyen ahmt15 17 Mart 2016 Perşembe 07:11
    15 Mart 2016 Salı 12:33
    Yanıtlayıcı