C # Reading data in Excel

# Region read data from Excel /// <summary> /// read data from Excel /// </Summary> /// <Param name = "excelfile"> Excel file name and path, eg: C: \ Users \ JK \ Desktop \ import test .xls </param> // <returns> data in Excel </returns> private datatable gettable (string filename) {oledbconnection objconn = NULL; system. data. datatable dt = NULL; string connstring = string. empty; oledbdataadapter da = new oledbdataadapter (); // obtain the stri set of sheet page (worksheet) names in the Excel worksheet NG [] Ss = This. getexcelsheetnames (filename); datatable = new datatable (); try {string filetype = filename. substring (filename. lastindexof (". "); If (filetype = ". xls ") connstring =" provider = Microsoft. jet. oledb.4.0; "+" Data Source = "+ filename +"; extended properties = Excel 8.0; "; else //. XLSX connstring = "provider = Microsoft. ace. oledb.12.0; "+" Data Source = "+ filename +"; "+"; extende D properties = \ "Excel 12.0; HDR = yes; IMEX = 1 \" "; // create the connection object objconn = new oledbconnection (connstring); // open the database connection objconn. open (); string SQL _f = "select * from [{0}]"; for (INT I = 0; I <ss. length; I ++) {da. selectcommand = new oledbcommand (string. format (SQL _f, ss [I]. tostring () + "$"), objconn); DA. fill (datatable); MessageBox. show ("no." + I + "data volume in the secondary table =" + datatable. rows. count. tostring ();} datatable = deleteb Lank (datatable, 9); MessageBox. show ("the data volume in the table =" + datatable. rows. count. tostring (); Return datatable;} catch (exception ex) {MessageBox. show (ex. tostring (); return NULL;} finally {// clear if (objconn! = NULL) {objconn. Close (); objconn. Dispose ();} If (DT! = NULL) {DT. Dispose () ;}}# endregion

# Region Delete blank rows in the specified table /// <summary> /// Delete blank rows in the specified table /// </Summary> /// <Param name = "DT"> table name </param> /// <Param name = "colnum"> Number of columns in Excel </param> /// <returns> datatable after deleting a blank row </returns> private datatable deleteblank (datatable DT, int colnum) {If (Dt = NULL | DT. rows. count = 0) {return DT;} // Delete the empty rows (note the form of a for loop) for (INT I = DT. rows. count-1; I> = 0; I --) {datarow ROW = DT. rows [I]; bool flag = true; // when When the colnum column of a row is empty, the change behavior is empty for (Int J = 0; j <colnum; j ++) {object o = row [J]; if (o! = Dbnull. value & convert. tostring (o ). trim (). length> 0) {flag = false; break;} If (FLAG) {DT. rows [I]. delete () ;}} DT. acceptchanges (); // Replace the dbnull column in the row with an empty string for (int K = DT. rows. count-1; k> = 0; k --) {datarow ROW = DT. rows [k]; for (INT z = 0; Z <colnum; Z ++) {object o = row [Z]; If (O = dbnull. value) {If (DT. columns [Z]. datatype = typeof (string) {row [Z] = "" ;}}} DT. acceptchanges (); Return DT ;}# endregion