Saving Data From DataGrid to Excel, How can I Optimise the code to make the process faster ?

Question

0.00/5 (No votes)

See more:

I have a windows application in C#. One of the function it has is to extract data from the Database and save it in excel so It can be sent to headquarters to be imported into their database.

The program works fine and does as is required, However it takes a long time to save the data to excel, It extracts from the Database just fine and loads the data into a data grid view, but saving from the datagridview to Excel takes long, sometimes up to 7 minutes. How can I speed up this process ?

Also, why does the Excel process keep running ? Isn't EXL.Quit() as in my code, supposed to kill the process ?

Below is the code I am using to save from DataGridView to Excel.

C#

private void btnSavetoExcel_Click(object sender, EventArgs e)
       {
            //Save Extracted Report to Microsoft Excel

           //If No Data is there in the grid
           if (gridExtractReport.RowCount < 1)
           {
               MessageBox.Show("No Data to Save.\r\nPlease Try Again",
            "Distributor Sales Management System", MessageBoxButtons.OK, MessageBoxIcon.Error);
               return;
           }

           //New Excel Application
           ExcelSpreadsheet.Application EXL = new ExcelSpreadsheet.Application();

           //data table
           DataSet dset = new DataSet();
           dset.Tables.Add();

           //add columns to table
           for (int x = 0; x <= (gridExtractReport.ColumnCount - 1); x++)
           {
               dset.Tables[0].Columns.Add(gridExtractReport.Columns[x].HeaderText);
           }

           //datarow
           DataRow drow1;

           //add rows to table
           for (int x = 0; x <= (gridExtractReport.RowCount - 1); x++)
           {
               drow1 = dset.Tables[0].NewRow();

               for (int y = 0; y <= (gridExtractReport.ColumnCount - 1); y++)
               {
                   drow1[y] = gridExtractReport.Rows[x].Cells[y].Value;
               }

               dset.Tables[0].Rows.Add(drow1);

           }

           //Create Worksheet
           ExcelSpreadsheet.Workbook Wbook;
           Wbook = EXL.Workbooks.Add(ExcelSpreadsheet.XlWBATemplate.xlWBATWorksheet);
           ExcelSpreadsheet.Worksheet WSheet = (ExcelSpreadsheet.Worksheet)Wbook.ActiveSheet;
           WSheet.Cells.Font.Size = 10;
           WSheet.Cells.Font.Name = "Arial";

           //Tables and Columns
           DataTable dt = dset.Tables[0];
           DataColumn dc = new DataColumn();
           //DataRow dr = new DataRow();
           int colIndex = 0, rowIndex = 0;
           foreach (DataColumn dcol in dt.Columns)
           {
               colIndex = colIndex + 1;
               EXL.Cells[1, colIndex] = dcol.ColumnName;
           }
           foreach (DataRow drow in dt.Rows)
           {
               rowIndex = rowIndex + 1;
               colIndex = 0;

               foreach (DataColumn dcol in dt.Columns)
               {
                   colIndex = colIndex + 1;
                   EXL.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];

               }

           }

           saveReport.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
           saveReport.Filter = "Microsoft Excel | *.xls";
           saveReport.FileName = "DSDPD2extract";

           //Format Column Titles
           WSheet.get_Range("A1:Q1", Type.Missing).BorderAround(ExcelSpreadsheet.XlLineStyle.xlContinuous,ExcelSpreadsheet.XlBorderWeight.xlThin,ExcelSpreadsheet.XlColorIndex.xlColorIndexAutomatic,System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));
           WSheet.get_Range("A1:Q1", Type.Missing).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
           WSheet.get_Range("A1:Q1", Type.Missing).ColumnWidth = 13.29;
           WSheet.get_Range("A1:Q1", Type.Missing).HorizontalAlignment = ExcelSpreadsheet.XlHAlign.xlHAlignCenter;

           if (saveReport.ShowDialog() == DialogResult.OK)
           {
               try
               {
                   WSheet.Name = "DSDPD2extract";
                   WSheet.get_Range("A1:Q1", Type.Missing).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(192, 192, 192));

                   EXL.ActiveWorkbook.SaveAs(saveReport.FileName, ExcelSpreadsheet.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, false, false, ExcelSpreadsheet.XlSaveAsAccessMode.xlShared, ExcelSpreadsheet.XlSaveConflictResolution.xlLocalSessionChanges, false, Type.Missing, Type.Missing, false);
               }
               catch (Exception)
               {
                   MessageBox.Show("Error Exporting Report.\r\nPlease Try Again", "Distributor Sales Management System", MessageBoxButtons.OK, MessageBoxIcon.Error);
                   return; // if Command Fails
               }
               MessageBox.Show("Report Succesfully Saved !", "Distributor Sales Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
           }

           EXL.ActiveWorkbook.Close(false, null, null);
           EXL.Quit();
       }

Posted 10-Sep-12 23:42pm

Peter Bamuhigire

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Himanshu Yadav · Answer 1 · 2012-09-11T00:07:00

Call this method on any button to export to excel and pass Datatable as input parameter Also add reference of introp dll.

public void Export(System.Data.DataTable dt1)
{
Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel.Workbook oWB;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRange;
// Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
// Set some properties
oXL.Visible = true;
oXL.DisplayAlerts = false;

// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);

// Get the active sheet
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.ActiveSheet;
oSheet.Name = "Customers";
System.Data.DataTable dt = dt1;
int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == 2)
{
oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
}
oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
}
}

// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[rowCount, dt.Columns.Count]);
oRange.EntireColumn.AutoFit();

// Save the sheet and close
oSheet = null;
oRange = null;
//SaveFileDialog f = new SaveFileDialog();
//f.Filter = "Excel Worksheets|*.xls";
//if (f.ShowDialog() == DialogResult.Cancel)
// return;

// string fileName = f.FileName;
oWB.SaveAs(ConfigurationSettings.AppSettings["excelLocation"], Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);//Missing.Value system.Reflection.missing
// oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
//oXL.Quit();

// Clean up
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}

Hope this will solve your problem