static ArrayList Sheets(stringfilepath)
{
ArrayList al= newArrayList();string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
OleDbConnection conn= newOleDbConnection(strconn);
conn.Open();
DataTable sheetnames= conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE"});
conn.Close();foreach (DataRow row in sheetnames.Rows) { al.Add(row["table_name"].ToString()); }returnal;
}static DataTable ExcelDataSource(string filepath, stringsheetname)
{string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection conn= newOleDbConnection(strconn);
OleDbDataAdapter oada= new OleDbDataAdapter("select * from [" + sheetname + "]", strconn);// DataTable dt = newDataTable();
oada.Fill(dt);returndt;
}static voidIn(DataTable dt)
{
SqlConnection conn= new SqlConnection("server=.;database=ManageDatas;uid=sa;pwd=sa;");
SqlCommand cmd= newSqlCommand();try{
cmd.Connection=conn;
cmd.CommandType=CommandType.Text;
conn.Open();foreach (DataRow row indt.Rows)
{
cmd.CommandText= @"if (select count(chvBandName) from dbo.tblBand WHERE chvBandName=@name)=1
begin
update dbo.tblBand set chvBandName=@name WHERE chvBandName=@name
end
else
begin
insert into dbo.tblBand values(@name)
end";
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = row[0].ToString();int i =cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}catch(Exception ex) { }finally{ conn.Close(); }
}public static voidImportExcel()
{//获取Excel文件的路径
OpenFileDialog ofd1 = newOpenFileDialog();
ofd1.ShowDialog();string FilePath =ofd1.FileName;if (!string.IsNullOrEmpty(FilePath))
{
In(ExcelDataSource(FilePath, Sheets(FilePath)[0].ToString()));
}
}