ASP.NET Large Data Export in Excel For DataTable The Extension

ASP.NET’de UI tarafında , DataTable nesnesi içerisine doldurmuş olduğumuz büyük ölçekli verilerin Excel dosyasına aktarım işlemini yaparken, uzun süre bekleme yada hiç export edememe gibi durumlarla karşılaşabiliriz.

Aşağıda; Microsoft’un “Microsoft.Office.Interop.Excel.dll” API ‘ sini veya GNU Lisanslı ücretsiz EPPlus API’sini kullanarak, DataTable ‘a doldurmuş olduğumuz büyük ölçekli verinin Excel’ dosyasına hızlı export edilip kullanıcıya gösterilmesini sağlayan ve tüm DataTable objeleri için geçerli olan Extension(Eklenti) kodunu(c#) paylaşıyor olacağım. Benim yaptığım testlerde, DataTable ‘daki yaklaşık 50.000 civarındaki veriyi üç ile otuz beş saniye arasında export edebildim. Yanlız, verinin Excel dosyasına export süresi, kullanıcı bilgisayarının konfigrasyonuna bağlı olarak da değişiklik gösterebilir! Makinemde, şu ana kadar ki alabildiğim en kısa export süresini bu iki eklenti ile aldım. Ayrıca bu eklentiler, ikinci bir defa veri tabanından sorgulama işlemi yapılmadan da(SQL Server Reporting Service gibi .net için kullanılan paket rapor toollarında veriler DataTable a doldurulduktan sonra export için ikinci bir defa db ye gidilip sorgu çalıştırıldıktan sonra export sonucu üretilir!)  export etmeye imkan vererek performans kazancı sağlar. Ayrıca , DataTable a doldurulan verilerin veritabanlarından bağımsız olarak export olanağını da sağlamaktadır. Yani DataTable içerisindeki veriyi hangi veritabanından sorgulayarak, veya hangi yöntemle doldurursanız doldurun Excel’e export işlemini yapabilirsiniz.

1)     Interop Excel Yöntemi : İlgili dll dosyasını proje referanslarına ekledikten sonra, önce using kısmına,

using Excel = Microsoft.Office.Interop.Excel;

yazdıktan sonra aşağıdaki extension methodlarını tanımlayıp kullanabiliriz;

    public static void ToExcelFastExport(this DataTable pDataTable, Page pPage)

    {

        string tempFolderPath = System.IO.Path.GetTempPath();

        string filePath = string.Format(tempFolderPath + pDataTable.TableName + @"_{0}.xls", Guid.NewGuid());

        /*DataTable içindeki verileri temp folder ına export ediyoruz*/

        ToExcelFastExport(pDataTable, filePath, pDataTable.TableName);

        /*Sonra da temp folder ındaki excel dosyasını kullanıcıya gösteriyoruz*/

        string filename = Path.GetFileName(filePath);

        pPage.Response.Clear();

        pPage.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);

        pPage.Response.ContentType = "application/vnd.ms-excel";

        pPage.Response.WriteFile(filePath);

        pPage.Response.End();

    }

    public static void ToExcelFastExport(this DataTable pDataTable, Page pPage, string pRaporName)

    {

        string tempFolderPath = System.IO.Path.GetTempPath();

        string filePath = string.Format(tempFolderPath + pRaporName + @"_{0}.xls", Guid.NewGuid());

        /*DataTable içindeki verileri temp folder ına export ediyoruz*/

        ToExcelFastExport(pDataTable, filePath, pRaporName);

        /*Sonra da temp folder ındaki excel dosyasını kullanıcıya gösteriyoruz*/

        string filename = Path.GetFileName(filePath);

        pPage.Response.Clear();

        pPage.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);

        pPage.Response.ContentType = "application/vnd.ms-excel";

        pPage.Response.WriteFile(filePath);

        pPage.Response.End();

    }

    public static void ToExcelFastExport(this DataTable pDataTable, string pSaveFileLocation, string pRaporName)

    {

        try

        {

            object[,] Record = null;

            int TotalRowsCount = 1;

            int TotalColumnCount = 1;

            if (pDataTable.Rows.Count > 0)

            {

                var Items = CopyToArray(pDataTable);

                Record = Items.Item1;

                TotalRowsCount = Items.Item2;

                TotalColumnCount = Items.Item3;

            }

            Excel._Application application = new Excel.Application();

            Excel._Workbook workBook = application.Workbooks.Add(Type.Missing);

            Excel._Worksheet workSheet = (Excel._Worksheet)workBook.Worksheets[1];

            workSheet.Name = pRaporName;

            //-------Header Range--------------------------- 

            Excel.Range headerRg_first = (Excel.Range)workSheet.Cells[1, 1];

            Excel.Range headerRg_last = (Excel.Range)workSheet.Rows.Cells[1, TotalColumnCount];

            Excel.Range headerRange = workSheet.get_Range(headerRg_first, headerRg_last);

            //headerRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.AliceBlue);

            headerRange.EntireRow.Font.Bold = true;

            headerRange.Font.Name = "Verdana";

            headerRange.Font.Size = "10";

            headerRange.RowHeight = 22;

            headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

            headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

            headerRange.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic);

            headerRange.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormat3DEffects1, true, false, true, false, true, true);

            headerRange.Borders.Color = System.Drawing.Color.Black;

            //---------------Record Range-----------------

            Excel.Range RecoredRg_first = (Excel.Range)workSheet.Cells[1, 1];

            Excel.Range RecoredRg_last = (Excel.Range)workSheet.Cells[TotalRowsCount, TotalColumnCount];

            Excel.Range RecordRange = workSheet.get_Range(RecoredRg_first, RecoredRg_last);

            RecordRange.Value = Record;

            RecordRange.Borders.Color = System.Drawing.Color.Black;

            RecordRange.EntireColumn.AutoFit();

            //pSaveFileLocation = string.Format(@"D:\MuhtarListesi_{0}.xls", Guid.NewGuid());

            if (System.IO.File.Exists(pSaveFileLocation)) System.IO.File.Delete(pSaveFileLocation);

            workBook.SaveAs(pSaveFileLocation, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            workBook.Close();

            application.Quit();

        }

        catch (Exception ex)

        {

            /*Buraya error logu yazılabilir...*/

        }

    }

    private static Tuple<Object[,], int, int> CopyToArray(DataTable pDataTable)

    {

        int indexRow = 0;

        int indexCol = 0;

        int totalRows = pDataTable.Rows.Count;

        int countColumns = pDataTable.Columns.Count;

        int startIndex = 0;

        object[,] objRecored = new object[totalRows + 1, countColumns];

        foreach (DataColumn col in pDataTable.Columns)

        {

            objRecored[indexRow, indexCol++] = col.ToString();

        }

        indexCol = 0;

        indexRow += 1;

        for (startIndex = 0; startIndex < totalRows; startIndex++)

        {

            foreach (DataColumn col in pDataTable.Columns)

            {

                objRecored[indexRow, indexCol++] = pDataTable.Rows[startIndex][col].ToString();

            }

            indexCol = 0;

            indexRow++;

        }

        return new Tuple<object[,], int, int>(objRecored, totalRows + 1, countColumns);

    }

Daha sonra Code Behind’ da, yukarıda yazmış olduğumuz eklentiyi, var olan sayfadaki “MuhtarListDataTable” DataTable ‘ı için;

MuhtarListDataTable.ToExcelFastExport(this.Page);

Şeklinde çağırabiliriz.

2)     EPPlus Yöntemi : Bu yöntemde IO işlemi yerine MemoryStream kullanılıyor, Interop.Excel’ den biraz daha yavaş çalışıyor olsa da sunucu tarafına dosyayı kopyalamadan, ilgili dosyayı sunucunun Memory ‘sinde tutarak client ‘ın excel dosyasını indirmesini sağlamaktadır. GNU Lisanslı , ücretsiz olan bu API ‘yi , “http://epplus.codeplex.com/” linkinden download edip proje referansımıza ekliyoruz. Sonra extension olarak aşağıdaki methodlar ile kullanabiliriz;

    public static void ToExcelExport(this DataTable pDataTable, Page pPage)

    {

        ToExcelExport(pDataTable, pPage, pDataTable.TableName);

    }

    public static void ToExcelExport(this DataTable pDataTable, Page pPage, string pRaporName)

    {

        string fileName = string.Format(pDataTable.TableName + @"_{0}.xls", Guid.NewGuid());

        ToExcelExport(pDataTable, pPage, pRaporName, fileName);

    }

    public static void ToExcelExport(this DataTable pDataTable, Page pPage, string pRaporName, string pFileName)

    {

        using (ExcelPackage pck = new ExcelPackage())

        {

            using (ExcelWorksheet workSheet = pck.Workbook.Worksheets.Add(pRaporName))

            {

                /*Load edilirken başlanacak hücreyi belirterek load ediyoruz*/

                workSheet.Cells["A1"].LoadFromDataTable(pDataTable, true);

                using (ExcelRange RecordRange = workSheet.Cells[workSheet.Dimension.Address])

                {

                    /*Hücre kenarlıklarını ayarlıyoruz*/

                    RecordRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;

                    RecordRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;

                    RecordRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;

                    RecordRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

                    RecordRange.AutoFitColumns();

                    /*Header ı formatlıyoruz*/

                    using (ExcelRange range = workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])

                    {

                        range.Style.Font.Bold = true;

                        range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;

                        range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DodgerBlue);

                        range.Style.Font.Color.SetColor(System.Drawing.Color.White);

                    }               

                    pPage.Response.Clear();

                    pPage.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                    pPage.Response.AddHeader("Content-Disposition", "attachment;filename=" + pFileName + ".xlsx");

                    pPage.Response.BinaryWrite(pck.GetAsByteArray());

                    pPage.Response.End();

                }

            }

        }

    }

Daha sonra Code Behind’ da, yukarıda yazmış olduğumuz eklentiyi, var olan sayfadaki “MuhtarListDataTable” DataTable ‘ı için;

MuhtarListDataTable.ToExcelExport(this.Page);

Şeklinde çağırabiliriz.

Kaynaklar:

•  http://www.c-sharpcorner.com/code/791/larger-data-writes-in-excel-file-faster.aspx
• http://epplus.codeplex.com/documentation