winfrom导入excel内容，要求能够excel中多个工作簿的内容。代码如下：
#region 导入excel数据
private void button2_Click(
object sender, EventArgs e)
{
OpenFileDialog openFileDialog =
new OpenFileDialog();
openFileDialog.Filter =
"表格文件 (*.xls)|*.xls";
openFileDialog.RestoreDirectory =
true;
openFileDialog.FilterIndex =
1;
if (openFileDialog.ShowDialog() ==
DialogResult.OK)
{
Import(openFileDialog.FileName);
}
}
/// <summary>
/// 导入excel数据
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static bool Import(
string filePath)
{
try
{
//Excel就好比一个数据源一般使用
//这里可以根据判断excel文件是03的还是07的，然后写相应的连接字符串
string strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filePath +
";" +
"Extended Properties=Excel 8.0;";
OleDbConnection con =
new OleDbConnection(strConn);
con.Open();
string[] names =
GetExcelSheetNames(con);
if (names.Length >
0)
{
foreach (
string name
in names)
{
OleDbCommand cmd =
con.CreateCommand();
cmd.CommandText =
string.Format(
" select * from [{0}]", name);
//[sheetName]要如此格式
OleDbDataReader odr =
cmd.ExecuteReader();
while (odr.Read())
{
if (odr[
0].ToString() ==
"序号")
//过滤列头 按你的实际Excel文件
continue;
//数据库添加操作
/*进行非法值的判断
* 添加数据到数据表中
* 添加数据时引用事物机制，避免部分数据提交
* Add(odr[1].ToString(), odr[2].ToString(), odr[3].ToString());//数据库添加操作，Add方法自己写的
* */
}
odr.Close();
}
}
return true;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 查询表名
/// </summary>
/// <param name="con"></param>
/// <returns></returns>
public static string[] GetExcelSheetNames(OleDbConnection con)
{
try
{
System.Data.DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new[] {
null,
null,
null,
"Table" });
//检索Excel的架构信息
var sheet =
new string[dt.Rows.Count];
for (
int i =
0, j = dt.Rows.Count; i < j; i++
)
{
//获取的SheetName是带了$的
sheet[i] = dt.Rows[i][
"TABLE_NAME"].ToString();
}
return sheet;
}
catch
{
return null;
}
}
//下面这种方法获取excel Worksheets Name时，提示无法访问该exceL文件，所以改为上面获取工作簿名的方式
///// <summary>
///// 获得excel sheet所有工作簿名字
///// </summary>
///// <param name="filePath"></param>
///// <returns></returns>
//public static string[] GetExcelSheetNames(string filePath)
//{
// Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
// Microsoft.Office.Interop.Excel.Workbooks wbs = excelApp.Workbooks;
// Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// int count = wb.Worksheets.Count;
// string[] names = new string[count];
// for (int i = 1; i <= count; i++)
// {
// names[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name;
// }
// return names;
//}
#endregion
