学步园

1.读取Excel文件的数据连接字符串。读取.xls格式文件的Excel文件，可设置连接字符串为：Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/MyExcel.xls;Extended Properties=Excel 8.0;。如果要读取.xlsx和.xls格式文件的Excel文件，则需要将连接字符设置为："Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:/MyExcel.xls;Extended Properties=Excel 12.0。

2.读取Excel表格Sheet的名称。

public static string[] GetExcelSheetNames(string filePath)<br /> {<br /> string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=/"Excel 12.0;HDR=YES/"";<br /> OleDbConnection con = new OleDbConnection(conString);<br /> con.Open();<br /> DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);<br /> con.Close();<br /> if (dt == null)<br /> {<br /> return null;<br /> }<br /> string[] excelSheetNames = new string[dt.Rows.Count];<br /> int i = 0;<br /> foreach (DataRow dr in dt.Rows)<br /> {<br /> excelSheetNames[i++] = dr["TABLE_NAME"].ToString();<br /> }<br /> return excelSheetNames;<br /> }

3.读取Excel文件

public static DataTable ReadExcel(string filePath, string conStr)<br /> {<br /> string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=/"Excel 12.0;HDR=YES/"";<br /> OleDbConnection con = new OleDbConnection(conString);<br /> OleDbDataAdapter oda = new OleDbDataAdapter(conStr, con);<br /> DataTable dt = new DataTable();<br /> con.Open();<br /> oda.Fill(dt);<br /> con.Close();<br /> return dt;<br /> }

4.将数据写入Excel文件。

public static void WriteExcel(string filePath, DataTable dt)<br /> {<br /> if (File.Exists(filePath))<br /> {<br /> File.Delete(filePath);<br /> }<br /> else<br /> {<br /> string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=/"Excel 12.0;HDR=YES/"";<br /> OleDbConnection con = new OleDbConnection(conString);<br /> string createSql = "create table Sheet1 (";<br /> foreach (DataColumn dc in dt.Columns)<br /> {<br /> createSql += dc.ColumnName + " varchar,";<br /> }<br /> createSql = createSql.Substring(0, createSql.Length - 1) + ")";<br /> OleDbCommand cmd = new OleDbCommand(createSql, con);<br /> con.Open();<br /> cmd.ExecuteNonQuery();<br /> foreach (DataRow dr in dt.Rows)<br /> {<br /> string insertSql = "insert into Sheet1 values(";<br /> foreach (DataColumn dc in dt.Columns)<br /> {<br /> insertSql += "'" + dr[dc].ToString() + "',";<br /> }<br /> insertSql = insertSql.Substring(0, insertSql.Length - 1) + ")";<br /> cmd = new OleDbCommand(insertSql, con);<br /> cmd.ExecuteNonQuery();<br /> }<br /> con.Close();<br /> }<br /> }