C # reads data from Excel

C # reads data from Excel

   #region reading data from Excel///<summary>///reading data in Excel///</summary>//<para M name= "excelfile" >excel file name and path, eg:c:\users\jk\desktop\ Import Test .xls</param>//<returns>excel data </            returns> Private DataTable GetTable (string fileName) {OleDbConnection objconn = null;            System.Data.DataTable dt = null; String connstring = String.            Empty;            OleDbDataAdapter da = new OleDbDataAdapter (); Gets the sheet page (sheet) name collection in the Excel workbook string[] ss = this.            Getexcelsheetnames (FileName);               DataTable 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 + ";" + "; Extended properties=\ "Excel 12.0;                Hdr=yes;imex=1\ "";                Create Connection Object objconn = new OleDbConnection (connstring);                               Open 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 (the amount of data in the "+i+" sub-table = "+datatable.rows.count.tostring ()");                } dataTable = Deleteblank (datatable,9);                MessageBox.Show ("Delete empty rows, the amount of data in the table =" + dataTable.Rows.Count.ToString ());            return dataTable; } catch (Exception ex) {MessageBox.Show (ex.                ToString ());      return null;      } finally {//Cleans if (objconn! = null) {                    Objconn.close ();                Objconn.dispose (); } if (dt! = NULL) {dt.                Dispose (); }}} #endregion

 #region Delete blank lines in the specified table///<summary>//delete blank lines in the specified table///</summary>//<param Nam e= "DT" > Table name </param>//<param name= "Colnum" >excel number of columns </param>///<returns> deleting blank lines  datatable</returns> Private DataTable Deleteblank (DataTable dt,int Colnum) {if (dt = = NULL || Dt.            rows.count==0) {return dt; }//Delete the empty lines (note the For Loop form) for (int i = dt). rows.count-1; I >= 0; i--) {DataRow row = dt.                Rows[i];                BOOL flag = TRUE; When the Colnum column of a row is empty, the line 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

Small bet

When you read Excel, the header is automatically processed.

The above is C # read the data in Excel content, more relevant content please pay attention to topic.alibabacloud.com (www.php.cn)!

