Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User

Solved: Reading Excel is very slow

30.10.2018 (👁29708)


 

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 == nullreturn;

            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 == nullbreak;

                    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