namespace 将Excle中的数据批量导入数据库
{
class Program
{
static void Main(string[] args)
{
SqlConnection sqlconn = new SqlConnection("Data Source=ZHANG-PC;Initial Catalog=config;Integrated Security=True");
sqlconn.Open();
DataSet ds = ExecleDs(@"C:\Users\zhang\Desktop\a.xls", "a");
DataRow[] rows = ds.Tables[0].Select();
int rowssun = ds.Tables[0].Rows.Count;
if (rowssun <= 0)
{
Console.WriteLine("Excle中没有数据");
return;
}
else
{
for (int i = 0; i < rows.Length; i++)
{
string YHMC = rows[i]["地区编码"].ToString();
string YHMM = rows[i]["上级编码"].ToString();
string DQRQ = rows[i]["地区名称"].ToString();
string ZT = rows[i]["地区级别"].ToString();
string TJSJ = rows[i]["是否末级"].ToString();
string insertstr = "insert into Dic_CheckArea(地区编码,上级编码,地区名称,地区级别,是否末级) values('" +
YHMC + "','" + YHMM + "','" + DQRQ + "','" + ZT + "','" + TJSJ + "')";
SqlCommand cmd = new SqlCommand(insertstr, sqlconn);
try
{
cmd.ExecuteNonQuery();
}
catch
{
throw new Exception("插入出错了");
}
}
}
Console.WriteLine("导入成功！共导入："+rowssun+"条数据");
}
public static DataSet ExecleDs(string filenameurl, string table)
{
try
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
DataSet ds = new DataSet();
odda.Fill(ds, table);
return ds;
}
catch
{
throw new Exception("出错了");
}
}
}
}