Efficient data export from DataTable to Excel; efficient data export from datatable

Efficient data export from DataTable to Excel; efficient data export from datatable

First, read data from the database to the DataTable, which I will not mention. Everyone understands. The following describes how to export data from DataTable to Excel efficiently. The Code is as follows:

1 using Microsoft. office. interop. excel; 2 using System. runtime. interopServices; 3 4 [DllImport ("User32.dll", CharSet = CharSet. auto)] 5 public static extern int GetWindowThreadProcessId (IntPtr hwnd, out int pid); 6 // function prototype; DWORD GetWindowThreadProcessld (HWND hwnd, LPDWORD lpdwProcessld); 7 // parameter: hWnd: Window handle 8 // parameter: lpdwProcessld: Address of the 32-bit value of the receiving process identifier. If this parameter is not NULL, GetWindwThreadProcessld copies the process ID to this 32-bit value; otherwise, the process ID is not copied. // return value: the return value is the thread ID of the Creation window. 10 11 // dt: data read from the database; file_name: Save path; sheet_name: form name 12 private void DataTableToExcel (DataTable dt, string file_name, string sheet_name) 13 {14 Microsoft. office. interop. excel. application Myxls = new Microsoft. office. interop. excel. application (); 15 Microsoft. office. interop. excel. workbook Mywkb = Myxls. workbooks. add (); 16 Microsoft. office. interop. excel. worksheet MySht = Mywkb. activeSheet; 17 MySht. name = s Heet_name; 18 Myxls. visible = false; 19 Myxls. displayAlerts = false; 20 try21 {22 // write header 23 object [] arrHeader = new object [dt. columns. count]; 24 for (int I = 0; I <dt. columns. count; I ++) 25 {26 arrHeader [I] = dt. columns [I]. columnName; 27} 28 MySht. range [Mysht. cells [1, 1], MySht. cells [1, dt. columns. count]. value2 = arrHeader; 29 // write table body data 30 object [,] arrBody = new object [dt. rows. count, dt. columns. count]; 31 f Or (int I = 0; I <dt. rows. count; I ++) 32 {33 for (int j = 0; j <dt. columns. count; j ++) 34 {35 arrBody [I, j] = dt. rows [I] [j]. toString (); 36} 37} 38 MySht. range [MySht. cells [2, 1], MySht. cells [dt. rows. count + 1, dt. columns. count]. value2 = arrBody; 39 if (Mywkb! = Null) 40 {41 Mywkb. saveAs (file_name); 42 Mywkb. close (Type. missing, Type. missing, Type. missing); 43 Mywkb = null; 44} 45} 46 catch (Exception ex) 47 {48 MessageBox. show (ex. message, "system prompt"); 49} 50 finally51 {52 // close the Excel process 53 if (Myxls! = Null) 54 {55 Myxls. Quit (); 56 try57 {58 if (Myxls! = Null) 59 {60 int pid; 61 GetWindowThreadProcessId (new IntPtr (Myxls. hwnd), out pid); 62 System. diagnostics. process p = System. diagnostics. process. getprocpolicyid (pid); 63 p. kill (); 64} 65} 66 catch (Exception ex) 67 {68 MessageBox. show ("failed to end the current EXCEL process:" + ex. message); 69} 70 Myxls = null; 71} 72 GC. collect (); 73} 74}

Note:

1) in the above method,Write the content of the able cell into the array and assign the value to the Range of Excel at a time.The efficiency is very high. It is much faster than the Cell method assigned to Excel one by the cyclic able cells;

2) It is worth noting that the Excel process is permanently closed in the above method.