How C # reads data from Excel

#region read data in Excel
02.///<summary>
03.///read data in Excel
04.///</summary>
05.///<param name= "excelfile" >excel filename and path, eg:c:\users\jk\desktop\ import Test .xls</param>
Data </returns> in 06.///<returns>excel
07.private DataTable gettable (String fileName)
08.{
OleDbConnection objconn = null;
System.Data.DataTable dt = null;
One. String connstring = string. Empty;
OleDbDataAdapter da = new OleDbDataAdapter ();
13.//Get a collection of sheet pages (worksheets) in the Excel workbook
string[] ss = this. Getexcelsheetnames (FileName);
DataTable datatable = new DataTable ();
Try
17. {
A. 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\ "";
24.//Create Connection object
objconn = new OleDbConnection (connstring);
26.//Open database connection
objConn.Open ();
28.
String sql_f = "SELECT * from [{0}]";
for (int i = 0; i < SS. length;i++)
31. {
Da. SelectCommand = new OleDbCommand (String.Format (Sql_f, ss[i). ToString () + "$"), objconn);
A. Da. Fill (dataTable);
MessageBox.Show ("+i+" the amount of data in the secondary table = "+datatable.rows.count.tostring ());"
35.}
dataTable = Deleteblank (datatable,9);
MessageBox.Show ("The amount of data in the table = + dataTable.Rows.Count.ToString ())" After deleting a blank row;
return dataTable;
39.}
catch (Exception ex)
41. {
MessageBox.Show (ex. ToString ());
return null;
44.}
Finally
46. {
47.//Clean up
if (objconn!= null)
49. {
Objconn.close ();
Objconn.dispose ();
52.}
if (dt!= null)
54. {
DT. Dispose ();
56.}
57.}
58.}
59.
#endregion

This paper url:http://www.bianceng.cn/programming/csharp/201410/45598.htm

#region Delete blank rows in the specified table
02.///<summary>
03.///Delete blank rows in the specified table
04.///</summary>
05.///<param name= "dt" > table name </param>
Number of columns in 06.///<param name= "Colnum" >excel </param>
07.///<returns> Delete datatable</returns> after blank lines
08.private DataTable Deleteblank (DataTable Dt,int Colnum)
09.{
if (dt = NULL | | | dt. rows.count==0)
11. {
return DT;
13.}
14.//delete the blank line (note for the form of the loop)
for (int i = dt. rows.count-1; I >= 0; i--)
16. {
DataRow row = dt. Rows[i];
BOOL flag = TRUE;
19.//When the Colnum column of a row is empty, the line is blank
for (int j = 0; J < Colnum; J + +)
21. {
Object o = row[j];
if (o!= dbnull.value && convert.tostring (o). Trim (). Length > 0)
24. {
Flag = false;
-Break;
27.}
28.}
if (flag)
30. {
DT. Rows[i]. Delete ();
32.}
33.}
DT. AcceptChanges ();
35.//Replace the DBNull column in the line with an empty string
for (int k = dt. rows.count-1; K >= 0; k--)
37. {
DataRow row = dt. ROWS[K];
for (int z = 0; z < colnum; z++)
40. {
A. Object o = row[z];
if (o = = DBNull.Value)
43. {
if (dt. COLUMNS[Z]. DataType = = typeof (String)
45. {
Row[z] = "";
47.}
48.}
49.}
50.}
Wuyi dt. AcceptChanges ();
A. return DT;
53.}
#endregion

Note

The table headers are automatically processed when you read Excel.