How to Improve the Performance of importing data to excel in C #

C # importing table data into Excel usually inserts data into Excel one by one cell. As follows (onlyCodeSegment ):

...

Object missing = missing. value;

Excel. Application myexcel = new excel. Application (); // create an Excel instance

Myexcel. application. workbooks. open (filename, missing, and missing); // open the filename table

Myexcel. Visible = false;

Excel. Workbook myworkbook = myexcel. workbooks [1];

Excel. worksheet myworksheet = (Excel. worksheet) myexcel. worksheets [1]; // declare a page of instances

...

For (INT I = 0; I <rownum; I ++)

{

Gridviewrow gvr = mydata. Rows [I];

For (Int J = 0; j <colnum; j ++)

{

Myexcel. cells [I + 8, J + 1] = "'" + gvr. cells [J]. Text. Replace ("& nbsp ;","");

}

}

...

However, the efficiency of cell assignment to excel is very low (because every time a cell value is assigned back, an Excel COM + component interface is called). If there is more data, access to the Excel COM + component is frequent, resulting inProgramThe operation efficiency is extremely low.

To solve this problem, we need to reduce the number of cell visits. According to the Excel API, you can assign a value (array) to its attribute value2 to achieve the same effect as the above Code. This avoids the sharp reduction in performance caused by frequent cell access.

The improved code is as follows:

...

Array arr = array. createinstance (typeof (string), rownum, colnum );

For (I = 0; I <rownum; I ++)

{

For (j = 0; j <colnum; j ++)

{

Arr. setvalue (mydata. Rows [I]. cells [J]. Text. Replace ("& nbsp", ""), I, j );

}

}

Excel. Range = myworksheet. get_range (myworksheet. cells [5, 1], myworksheet. cells [rownum + 4, colnum]);

Range. value2 = arr;

...

BTW: I always think that the name of the Excel attribute value2 looks uncomfortable, but it is not very standard. I wonder why they should name it like this.