Problem:
Slow Excel
When reading Excel files from Word or other applications via the COM Interop access, the read-out is very slow.
It seems that every single evaluation of value or text from a cell takes so long.
Especially when using C # Interop Visual Studio Interop the very slow access is disadvantageous.
Solution:
When accessing Excel externally, you should not use Excel Range to read Excel cell values, but transfer the entire reading range to an Object Array and read it out.
Subject:
vsto interop access to excel files, excel sheet,
Reading Excel Range Cells Value
Old:
Slow Excel Access to Excel Cells with Range
//*slow Excel: Excel.Range usedRange = _worksheet.UsedRange; .. .. Excel.Range cell = usedRange.Cells[1, iColumn] as Excel.Range; String sValue = Convert.ToString(cell.Value2); |
New:
Quick reading of Excel with object array
//*fast Excel: object[,] values = usedRange.Value2; .. .. string sValue = Convert.ToString(values[1, iColumn]); |
Example:
Import Excel values into a selection file
For the example, references to Excel and Windows Forms are needed
using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; |
C # code example
Reading Excel values from an Excel file, Excel sheet
Output of the values into a data grid
File: frmExcel_Data.cs
using Microsoft.Office.Interop.Word; using System;
using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; using Word = Microsoft.Office.Interop.Word;
namespace addin_Excel { public partial class frmExcel_Data : Form { //< variables > private DataGridView _dataGridView; private Excel.Application _excel_App; private Excel.Worksheet _worksheet; private Excel.Workbook _workbook;
Document _doc = null; Word.Application _app = null; //</ variables >
//< leeres Delegate erstellen > private delegate void DoEvents_EmptyDelegate(); //</ leeres Delegate erstellen >
#region Region: Form //===================< Form >=================== public frmExcel_Data() { InitializeComponent(); _dataGridView = dataGrid_Excel; _dataGridView.AllowUserToAddRows = false; _doc = Globals.ThisAddIn.Application.ActiveDocument; _app = Globals.ThisAddIn.Application; } private void frmExcel_Data_Shown(object sender, EventArgs e) { DateTime dtStart = DateTime.Now;
_worksheet = open_Excel_File();
if (_worksheet == null) return; read_Excel_File_into_DataGridView();
TimeSpan tsDauer = DateTime.Now.Subtract(dtStart); string sDauer = tsDauer.Milliseconds + " msek."; lblStatus.Text = sDauer; try { _workbook.Close(); _excel_App.Quit(); } finally {
} } //===================</ Form >=================== #endregion /Region: Form
#region --Buttons-- //===================< Buttons >=================== private void btnSelect_Click(object sender, EventArgs e) { replace_Serial_Fields(); Close(); } //===================</ Buttons >=================== #endregion --/ Buttons--
#region --Methods Excel-- //===================< Methods Excel >=================== public Excel.Worksheet open_Excel_File() { //-----------------< open_Excel_File() >----------------- //< init > String sFilename = Settings1.Default.Excel_Filename; if (sFilename.IndexOf(":") < 0) { string sWordPath = Globals.ThisAddIn.Application.ActiveDocument.Path; sFilename = sWordPath + "\\" + sFilename; } //</ init > DateTime dtStart = DateTime.Now; lblStatus.Text = "opening excel file.."; _excel_App = new Microsoft.Office.Interop.Excel.Application(); lblStatus.Text = "file open. "; log_with_Date(lblStatus.Text, dtStart);
lblLog.Text += lblStatus.Text; try { //----< open_Excel >---- dtStart = DateTime.Now; _workbook = _excel_App.Workbooks.Open(sFilename, UpdateLinks: false, ReadOnly: true, AddToMru: false); lblStatus.Text = "open sheet "; log_with_Date(lblStatus.Text, dtStart); //----</ open_Excel >---- return _workbook.Worksheets[Settings1.Default.Excel_Sheet]; ; } catch (Exception ex) { MessageBox.Show(ex.Message); return null; } //-----------------</ open_Excel_File() >----------------- }
public void read_Excel_File_into_DataGridView() { //----------------< read_Excel_File_into_DataGridView() >------------
//< init > DataGridView dataGridView = dataGrid_Excel; //</ init >
DateTime dtStart = DateTime.Now; Excel.Range usedRange = _worksheet.UsedRange; log_with_Date("get UsedRange", dtStart);
//*fast Excel-Read: object[,] values = usedRange.Value2;
int nColumnsMax = 0; if (usedRange.Rows.Count > 0) { //----< Read_Header >---- dtStart = DateTime.Now; for (int iColumn = 1; iColumn <= usedRange.Columns.Count; iColumn++) { //*slow Excel: //Excel.Range cell = usedRange.Cells[1, iColumn] as Excel.Range; //String sValue = Convert.ToString(cell.Value2);
//*fast Excel: string sValue = Convert.ToString(values[1, iColumn]);
if (sValue == "" || sValue == null) break; dataGridView.Columns.Add("column_" + iColumn, sValue);
nColumnsMax = iColumn; } log_with_Date("HeadLines= " , dtStart); //----</ Read_Header >----
//----< Read_DataRows >---- dtStart = DateTime.Now; for (int iRow = 2; iRow <= usedRange.Rows.Count; iRow++) { lblStatus.Text = "row " + iRow + "/" + usedRange.Rows.Count; //< add_Row > int iNewRow = dataGridView.Rows.Add(new DataGridViewRow()); DataGridViewRow newRow = dataGridView.Rows[iNewRow]; //</ add_Row >
if (iRow > 20) break;
for (int iColumn = 1; iColumn <= nColumnsMax; iColumn++) { //--< Transfer Cells >-- //< read >
//*slow Excel: Excel.Range cell = usedRange.Cells[iRow, iColumn] as Excel.Range; //*slow Excel: String sValue = Convert.ToString(cell.Value2);
//*fast Excel: string sValue = Convert.ToString(values[iRow, iColumn]);
if (iColumn == 1) { if (sValue == "" || sValue == null) { log_with_Date("rows= ", dtStart); return; // break; }
} //</ read >
//< write > newRow.Cells[iColumn - 1].Value = sValue; //</ write > //--</ Transfer Cells > -- }
} log_with_Date("rows= ", dtStart); //----</ Read_DataRows >---- }
//----------------</ read_Excel_File_into_DataGridView() >------------ }
private void log_with_Date(string sText, DateTime dtStart) { TimeSpan tsDauer = DateTime.Now.Subtract(dtStart); lblLog.Text += Environment.NewLine + sText + " " + tsDauer.Milliseconds + " msek."; System.Windows.Forms.Application.DoEvents(); } //===================</ Methods >=================== #endregion /Methods
#region --Methods Word -- //===================< Methods Word >=================== private void replace_Serial_Fields() { //-----------------< suche_Address_Felder() >----------------- //*Serienbrieffelder=ActiveDocument.FormFields[] //--< @Loop: SerialFields >-- foreach (object varField in _doc.MailMerge.Fields) { //«KDNR» MailMergeField field = (MailMergeField) varField ; string sField_Content = field.Code.Text; int posField = sField_Content.IndexOf("MERGEFIELD "); if ( posField > -1) // MERGEFIELD SANr { //---< IsMail_Field >--- //< get fieldname > posField = posField + "MERGEFIELD ".Length; string sField = sField_Content.Substring(posField); sField = sField.Trim(); //</ get fieldname >
//< get grid > int intColumn = get_Column_with_Header(sField); //</ get grid >
if(intColumn>=0) { string sReplace = _dataGridView.Rows[1].Cells[intColumn].Value.ToString();
//< replace field > field.Select(); _app.Selection.Text = sReplace; //field.Delete(); //</ replace field > } //---</ IsMail_Field >--- } } //--</ @Loop: SerialFields >-- //-----------------</ suche_Address_Felder() >----------------- }
private int get_Column_with_Header(string sHeader) { //-----------------< get_Column_with_Header() >----------------- //< init > DataGridView dataGridView = dataGrid_Excel; //</ init >
//--< @Loop: SerialFields >-- foreach (DataGridViewColumn col in dataGridView.Columns) { if (col.HeaderText == sHeader) return col.Index; } //--</ @Loop: SerialFields >-- return -1; //-----------------</ get_Column_with_Header() >----------------- }
//===================</ Methods Word >=================== #endregion /Methods Word
//DataGridViewColumn column = new DataGridViewColumn(); //column.HeaderText = sValue; //column.CellTemplate = new DataGridViewTextBoxCell(); //dataGridView.Columns.Add(column); } }
|
Excel output form
File: frmExcel_Data.cs Design.cs code
Example: Excel reading in and out in a Forms DataGridView table
Design code not listed here.
Search references:
· c # - Slow Performance When Reading Excel - Stack Overflow
· Why is it so slow to read on Excel file with Powershell?
· vba - Slow reading from Excel
· Excel Work Book - Read from C # substantially slow?
· OleDb - slow reading from Excel
· Microsoft Excel: Why your spreadsheet is so slow | PCWorld
· c # - Excel slow reading - Code Review Stack Exchange
· C # Read Excel Sheet Interop - Very slow reading - MSDN - Microsoft