none
I need Fast Method of export data from Datase to Excel RRS feed

  • Question

  • Hi

    below is my code to make Excel file and writing data in it from dataset,But this method is very slow when huge data enter because I have 5000 Records and it take about 30 mins to complete. is there another method to make Quick export to Excel from dataset.thanks

     class CreatingExcelFile
        {
            private Excel.Application app = null;
            private Excel.Workbook workbook = null;
            private Excel.Worksheet worksheet = null;
            private Excel.Range workSheet_range = null;     


            public void Savefile(string Filename)
            {
                workbook.SaveAs(Filename, Excel.XlFileFormat.xlWorkbookNormal,
                             null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,
                             false, false, null, null, null);

                workbook.Close(null,Filename,null);
               
               workbook = null;
                app = null;
            }
            public void AddData(int row, int col, string data, string cell1, string cell2, string format)
            {
                worksheet.Cells[row, col] = data;
                workSheet_range = worksheet.get_Range(cell1, cell2);
                workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();

                if(format=="yes")
                     workSheet_range.NumberFormat = "####";

            }

            //----------------------------------------
            public void CreateHeaders(int row, int col, string htext, string cell1, string cell2, int mergeColumns, string b, bool font, int size, string fcolor)
            {
                try
                {
                    worksheet.Cells[row, col] = htext;
                   
                    workSheet_range = worksheet.get_Range(cell1, cell2);
                    workSheet_range.Merge(mergeColumns);
                    switch (b)
                    {
                        case "YELLOW":
                            workSheet_range.Interior.Color = System.Drawing.Color.Yellow.ToArgb();
                            break;
                        case "GRAY":
                            workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
                            break;
                        case "GAINSBORO":
                            workSheet_range.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
                            break;
                        case "Turquoise":
                            workSheet_range.Interior.Color = System.Drawing.Color.Turquoise.ToArgb();
                            break;
                        case "PeachPuff":
                            workSheet_range.Interior.Color = System.Drawing.Color.PeachPuff.ToArgb();
                            break;
                        default:
                            //  workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
                            break;

                    }

                    workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
                    workSheet_range.Font.Bold = font;
                    workSheet_range.ColumnWidth = size;
                    if (fcolor.Equals(""))
                    {
                        workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb();
                    }
                    else
                    {
                        workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
                    }
                }

                catch (SystemException ex) { }

            }

            //------------------------------------------------  Create Doc
            public void CreateDoc()
            {
                try
                {
                  
                    app = new Excel.Application();
                    // app.Visible = true;
                    workbook = app.Workbooks.Add(1);
                    worksheet = (Excel.Worksheet)workbook.Sheets[1];

                  

                }
                catch (Exception e)
                {
                    Console.Write("Error");
                }
                finally
                {

                }
            } 


    Imtiaz Ahmad
    • Moved by Bob Beauchemin Tuesday, December 13, 2011 5:16 PM Moved to a more relevent forum for best response (From:.NET Framework inside SQL Server)
    Tuesday, December 13, 2011 9:03 AM

Answers

All replies