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

Question

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
                        {
                            ;
                        }
                    }
                }

Answer 1

CSV olarak text dosyasına kaydedip excel'de açın.

Answer 2

"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.

Answer 3

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

Answer 4

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).

Answer 5

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; }
}

Answer 6

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();

}

Answer 7

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();

}