Проблема с производительностью взаимодействия между Microsoft Office

Я пытаюсь экспортировать данные datagridview в файл excel в моем приложении Windows C # 4.0.

мы использовали версию Microsoft.Office.Interop.Excel dll версии 12.0.0.0. Это хорошо работает и все

Это хорошо. Но я, когда я пытаюсь экспортировать более 1000 записей datagridview, занимает слишком много времени

время. Как я могу улучшить производительность.

См. Этот хелперный код Excel ниже.

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 } 

У меня есть несколько предложений по улучшению производительности. В одиночку они могут не иметь большого влияния, но вместе они должны улучшить общую производительность.

  • Скрыть Excel (если его еще нет) EXL.Visible = false; , Выключите Calculation ( Application.Calculation = xlCalculationManual , если она не нужна) и ScreenUpdating .
  • Используйте Excel.Workbooks.Worksheets а не коллекцию Sheets .
  • Вместо того, чтобы перебирать все листы, попробуйте обратиться к тому, который вы хотите, используя обработку ошибок, чтобы определить, существует ли лист:

     Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets["SheetName"]; 

Избегайте Select , это редко необходимо – и медленно. Заменить,

 //Select all cells, and clear the contents Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells; myAllRange.Select(); myAllRange.CurrentRegion.Select(); myAllRange.ClearContents(); 

с

 worksheet.UsedRange.ClearContents(); 

Вы должны полностью удалить свою функцию SelectAllUsedCells() . Если вам все еще нужно выбрать их, то:

 worksheet.UsedRange.Select(); // but shouldn't be necessary 

В противном случае, если вы придерживаетесь петли через листы, используйте break; как только вы найдете лист, чтобы выйти из цикла.

Удалить rng.Select(); от вашей функции fillExcelCell () . Однако вы вызываете эту функцию для каждой ячейки; это верно? После этого я буду делать все форматирование. В частности, применение AutoFit ко всему диапазону.

Я бы создал форму один раз и скопировал / вставил ее. (Не уверен, что он может быть клонирован ?)

По завершении установите режим калькуляции в исходную настройку.

Вы обрабатываете одну ячейку за раз. Объект Range Excel может представлять собой двумерную сетку.

  1. Вы можете либо построить массив в .NET, либо установить Value в один снимок.
  2. Я предпочитаю делать это по столбцам. Таким образом, данные и форматирование могут содержаться в одной логической единице.

Я прошу прощения за то, что не ответил на ваш точный вопрос, но я хотел бы передать здесь совет.

Использование Interop таким образом очень медленное по своей природе: связь между приложениями не самая быстрая вещь в Windows, и Excel делает много вещей, которые вам не нужны при каждой операции, даже если Andy G предоставил вам некоторые подсказки для ограничения его накладные расходы.

Твердое решение не должно использовать Excel для экспорта данных: используйте библиотеку .net, такую ​​как Aspose.Cells или любой другой. Aspose.Cells немного дороже, но это очень хорошо. Обратите внимание, что я не заинтересован в Aspose, я только что использовал его в нескольких моих проектах. Я также использовал Syncfusion, более дешевый и хороший, но менее интуитивно понятный API. Существуют и другие, включая бесплатную библиотеку MS OpenXml, которая является бесплатной, но очень низкой (я бы не советовал ее).

С такими библиотеками очень легко экспортировать данные в файл Excel, и производительность отличная.

Interesting Posts
Давайте будем гением компьютера.