Microsoft Office interop performance issue
I am try to exporting datagridview data to excel file in my C# 4.0 Windows application.
we used Microsoft.Office.Interop.Excel dll version 12.0.0.0. It's works well and everything
is fine. But i when i try to export more than 1000 datagridview records it's take too long
time.How can i improve the performance.
Please see this Excel helper code below.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using System.Runtime.InteropServices; // For COMException
using System.Reflection; // For Missing.Value and BindingFlags
using System.Diagnostics; // to ensure EXCEL process is really killed
namespace Export.Excel
{
#region InstanceFields
//Instance Fields
//public delegate void ProgressHandler(object sender, ProgressEventArgs e);
//public event ProgressHandler prg;
private System.Data.DataView dv;
private Style styleRows;
private Style styleColumnHeadings;
private Microsoft.Office.Interop.Excel.Application EXL;
private Workbook workbook;
private Sheets sheets;
private Worksheet worksheet;
private string[,] myTemplateValues;
private int position;
private System.Globalization.CultureInfo cl;
private Type _ResourceType;
#endregion
#region Constructor
//Constructs a new export2Excel object. The user must
//call the createExcelDocument method once a valid export2Excel
//object has been instantiated
public ExportExcelFormat(string culture, Type type)
{
cl = new System.Globalization.CultureInfo(culture);
_ResourceType = type;
}
#endregion
#region EXCEL : ExportToExcel
//Exports a DataView to Excel. The following steps are carried out
//in order to export the DataView to Excel
//Create Excel Objects
//Create Column & Row Workbook Cell Rendering Styles
//Fill Worksheet With DataView
//Add Auto Shapes To Excel Worksheet
//Select All Used Cells
//Create Headers/Footers
//Set Status Finished
//Save workbook & Tidy up all objects
//@param dv : DataView to use
//@param path : The path to save/open the EXCEL file to/from
//@param sheetName : The target sheet within the EXCEL file
public void ExportToExcel(System.Data.DataView dv, string path, string sheetName, string[] UnWantedColumns)
{
try
{
//Assign Instance Fields
this.dv = dv;
#region NEW EXCEL DOCUMENT : Create Excel Objects
//create new EXCEL application
EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
//index to hold location of the requested sheetName in the workbook sheets
//collection
int indexOfsheetName;
#region FILE EXISTS
//Does the file exist for the given path
if (File.Exists(path))
{
//Yes file exists, so open the file
workbook = EXL.Workbooks.Open(path,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
//get the workbook sheets collection
sheets = workbook.Sheets;
//set the location of the requested sheetName to -1, need to find where
//it is. It may not actually exist
indexOfsheetName = -1;
//loop through the sheets collection
for (int i = 1; i <= sheets.Count; i++)
{
//get the current worksheet at index (i)
worksheet = (Worksheet)sheets.get_Item(i);
//is the current worksheet the sheetName that was requested
if (worksheet.Name.ToString().Equals(sheetName))
{
//yes it is, so store its index
indexOfsheetName = i;
//Select all cells, and clear the contents
Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells;
myAllRange.Select();
myAllRange.CurrentRegion.Select();
myAllRange.ClearContents();
}
}
//At this point it is known that the sheetName that was requested
//does not exist within the found file, so create a new sheet within the
//sheets collection
if (indexOfsheetName == -1)
{
//Create a new sheet for the requested sheet
Worksheet sh = (Worksheet)workbook.Sheets.Add(
Type.Missing, (Worksheet)sheets.get_Item(sheets.Count),
Type.Missing, Type.Missing);
//Change its name to that requested
sh.Name = sheetName;
}
}
#endregion
#region FILE DOESNT EXIST
//No the file DOES NOT exist, so create a new file
else
{
//Add a new workbook to the file
workbook = EXL.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//get the workbook sheets collection
sheets = workbook.Sheets;
//get the new sheet
worksheet = (Worksheet)sheets.get_Item(1);
//Change its name to that requested
worksheet.Name = sheetName;
}
#endregion
#region get correct worksheet index for requested sheetName
//get the workbook sheets collection
sheets = workbook.Sheets;
//set the location of the requested sheetName to -1, need to find where
//it is. It will definately exist now as it has just been added
indexOfsheetName = -1;
//loop through the sheets collection
for (int i = 1; i <= sheets.Count; i++)
{
//get the current worksheet at index (i)
worksheet = (Worksheet)sheets.get_Item(i);
//is the current worksheet the sheetName that was requested
if (worksheet.Name.ToString().Equals(sheetName))
{
//yes it is, so store its index
indexOfsheetName = i;
}
}
//set the worksheet that the DataView should write to, to the known index of the
//requested sheet
worksheet = (Worksheet)sheets.get_Item(indexOfsheetName);
#endregion
#endregion
// Set styles 1st
SetUpStyles();
//Fill EXCEL worksheet with DataView values
fillWorksheet_WithDataView(UnWantedColumns);
//Add the autoshapes to EXCEL
//AddAutoShapesToExcel();
//Select all used cells within current worksheet
SelectAllUsedCells();
try
{
workbook.Close(true, path, Type.Missing);
EXL.UserControl = false;
EXL.Quit();
EXL = null;
//kill the EXCEL process as a safety measure
killExcel();
}
catch (COMException cex)
{
}
catch (Exception ex)
{
}
}
catch (Exception ex)
{
}
}
#endregion
#region EXCEL : UseTemplate
//Exports a DataView to Excel. The following steps are carried out
//in order to export the DataView to Excel
//Create Excel Objects And Open Template File
//Select All Used Cells
//Create Headers/Footers
//Set Status Finished
//Save workbook & Tidy up all objects
//@param path : The path to save/open the EXCEL file to/from
public void UseTemplate(string path, string templatePath, string[,] myTemplateValues)
{
try
{
this.myTemplateValues = myTemplateValues;
//create new EXCEL application
EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
//Yes file exists, so open the file
workbook = EXL.Workbooks.Open(templatePath,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
//get the workbook sheets collection
sheets = workbook.Sheets;
//get the new sheet
worksheet = (Worksheet)sheets.get_Item(1);
//Change its name to that requested
worksheet.Name = "ATemplate";
//Fills the Excel Template File Selected With A 2D Test Array
fillTemplate_WithTestValues();
//Select all used cells within current worksheet
SelectAllUsedCells();
try
{
workbook.Close(true, path, Type.Missing);
EXL.UserControl = false;
EXL.Quit();
EXL = null;
//kill the EXCEL process as a safety measure
killExcel();
}
catch (COMException)
{
}
}
catch (Exception ex)
{
}
}
#endregion
#region STEP 1 : Create Column & Row Workbook Cell Rendering Styles
//Creates 2 Custom styles for the workbook These styles are
// styleColumnHeadings
// styleRows
//These 2 styles are used when filling the individual Excel cells with the
//DataView values. If the current cell relates to a DataView column heading
//then the style styleColumnHeadings will be used to render the current cell.
//If the current cell relates to a DataView row then the style styleRows will
//be used to render the current cell.
private void SetUpStyles()
{
// Style styleColumnHeadings
try
{
styleColumnHeadings = workbook.Styles["styleColumnHeadings"];
}
// Style doesn't exist yet.
catch
{
styleColumnHeadings = workbook.Styles.Add("styleColumnHeadings", Type.Missing);
styleColumnHeadings.Font.Name = "Arial";
styleColumnHeadings.Font.Size = 12;
styleColumnHeadings.Font.Bold = true;
}
// Style styleRows
try
{
styleRows = workbook.Styles["styleRows"];
}
// Style doesn't exist yet.
catch
{
styleRows = workbook.Styles.Add("styleRows", Type.Missing);
styleRows.Font.Name = "Verdana";
styleRows.Font.Size = 9;
}
}
#endregion
#region STEP 2 : Fill Worksheet With DataView
//Fills an Excel worksheet with the values contained in the DataView
//parameter
private void fillWorksheet_WithDataView(string[] UnWantedColumns)
{
position = 0;
//Add DataView Columns To Worksheet
int row = 1;
int col = 1;
// Remove unwanted columns in the loop
int total = dv.Table.Columns.Count - UnWantedColumns.Count();
// Loop thought the columns
for (int i = 0; i < total; i++)
{
fillExcelCell(worksheet, row, col++, dv.Table.Columns[i].ToString(), styleColumnHeadings.Name, UnWantedColumns);
}
//Add DataView Rows To Worksheet
row = 2;
col = 1;
for (int i = 0; i < dv.Table.Rows.Count; i++)
{
for (int j = 0; j < dv.Table.Columns.Count; j++)
{
fillExcelCell(worksheet, row, col++, dv[i][j].ToString(), styleRows.Name, UnWantedColumns);
}
col = 1;
row++;
position = (100 / dv.Table.Rows.Count) * row + 2;
}
}
#endregion
#region STEP 3 : Fill Individual Cell and Render Using Predefined Style
//Formats the current cell based on the Style setting parameter name
//provided here
//@param worksheet : The worksheet
//@param row : Current row
//@param col : Current Column
//@param Value : The value for the cell
//@param StyleName : The style name to use
private void fillExcelCell(Worksheet worksheet, int row, int col, Object Value, string StyleName, string[] UnWantedColumns)
{
if (!UnWantedColumns.Contains(Value.ToString()))
{
Range rng = (Range)worksheet.Cells[row, col];
rng.NumberFormat = "@";
rng.Select();
rng.Value2 = Value.ToString();
rng.Style = StyleName;
rng.Columns.EntireColumn.AutoFit();
}
}
#endregion
#region STEP 4 : Add Auto Shapes To Excel Worksheet
//Add some WordArt objecs to the Excel worksheet
private void AddAutoShapesToExcel()
{
//Method fields
float txtSize = 80;
float Left = 100.0F;
float Top = 100.0F;
//Have 2 objects
int[] numShapes = new int[2];
Microsoft.Office.Interop.Excel.Shape[] myShapes = new Microsoft.Office.Interop.Excel.Shape[numShapes.Length];
try
{
//loop through the object count
for (int i = 0; i < numShapes.Length; i++)
{
//Add the object to Excel
myShapes[i] = worksheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect1, "DRAFT", "Arial Black",
txtSize, MsoTriState.msoFalse, MsoTriState.msoFalse, (Left * (i * 3)), Top);
//Manipulate the object settings
myShapes[i].Rotation = 45F;
myShapes[i].Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;
myShapes[i].Fill.Transparency = 0F;
myShapes[i].Line.Weight = 1.75F;
myShapes[i].Line.DashStyle = MsoLineDashStyle.msoLineSolid;
myShapes[i].Line.Transparency = 0F;
myShapes[i].Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
myShapes[i].Line.ForeColor.RGB = (0 << 16) | (0 << 8) | 0;
myShapes[i].Line.BackColor.RGB = (255 << 16) | (255 << 8) | 255;
}
}
catch (Exception ex)
{
}
}
#endregion
#region STEP 5 : Select All Used Cells
//Selects all used cells for the Excel worksheet
private void SelectAllUsedCells()
{
Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells;
myAllRange.Select();
myAllRange.CurrentRegion.Select();
}
#endregion
#region STEP 6 : Fill Template With Test Values
//Fills the Excel Template File Selected With A 2D Test Array parameter
private void fillTemplate_WithTestValues()
{
//Initilaise the correct Start Row/Column to match the Template
int StartRow = 3;
int StartCol = 2;
position = 0;
// Display the array elements within the Output window, make sure its correct before
for (int i = 0; i <= myTemplateValues.GetUpperBound(0); i++)
{
//loop through array and put into EXCEL template
for (int j = 0; j <= myTemplateValues.GetUpperBound(1); j++)
{
//update position in progress bar
position = (100 / myTemplateValues.Length) * i;
//put into EXCEL template
Range rng = (Range)worksheet.Cells[StartRow, StartCol++];
rng.Select();
rng.Value2 = myTemplateValues[i, j].ToString();
rng.Rows.EntireRow.AutoFit();
}
//New row, so column needs to be reset
StartCol = 2;
StartRow++;
}
}
#endregion
#region Kill EXCEL
//As a safety check go through all processes and make
//doubly sure excel is shutdown. Working with COM
//have sometimes noticed that the EXL.Quit() call
//does always do the job
private void killExcel()
{
try
{
Process[] ps = Process.GetProcesses();
foreach (Process p in ps)
{
if (p.ProcessName.ToLower().Equals("excel"))
{
p.Kill();
}
}
}
catch (Exception ex)
{
}
}
#endregion
}
Answer
I have a few suggestions to improve the performance. Singly they may not have much impact, but together they should improve overall performance.
- Hide Excel (if it isn't already)
EXL.Visible = false;. Turn offCalculation(Application.Calculation = xlCalculationManual, if it isn't needed) andScreenUpdatingas well. - Use
Excel.Workbooks.Worksheetsrather than theSheetscollection. Rather than looping through all the worksheets, try to reference the one you want, using error-handling to determine if the sheet exists:
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets["SheetName"];
Avoid Select, it is rarely necessary - and slow. Replace,
//Select all cells, and clear the contents
Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells;
myAllRange.Select();
myAllRange.CurrentRegion.Select();
myAllRange.ClearContents();
with
worksheet.UsedRange.ClearContents();
You should be able to remove your function SelectAllUsedCells() entirely. If you still needed to select them, then:
worksheet.UsedRange.Select(); // but shouldn't be necessary
Otherwise, if you stick with looping through the worksheets, use break; once you've found the sheet, to exit the loop.
Delete rng.Select(); from your fillExcelCell() function. However, you appear to be calling this function for every cell; is this correct? I would do all the formatting in one go afterwards. In particular, applying AutoFit to the entire range.
I would create the shape once and copy/paste it. (Not sure if it can be cloned?)
Put the Calcuation mode back to its original setting when complete.