Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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.
 
 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 11-Sep-12 0:42am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  

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



Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 11 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100