C#中excel导入sql

using Microsoft.Office.Interop.Excel;

public int ledinExcel(string file, object sender, EventArgs e)
{
//try
//{
System.Data.DataTable dt = xsldata(file);
int errorcount = ;
int insertcount = ;
//int updatecount = 0; //string strcon = "server=ROCKEN;database=checkatt;uid=sa;pwd=000123";
//SqlConnection conn = new SqlConnection(strcon);
//conn.Open();
for (int i = ; i < dt.Rows.Count; i++)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into checkatt.DBO.att_table (Person_num,Per_name,Work_att,Up1,Off2,Up3,Off4,Up5,Off6)");
strSql.Append(" VALUES (@Person_num,@Per_name,@Work_att,@Up1,@Off2,@Up3,@Off4,@Up5,@Off6)");
SqlParameter[] praexcel = {
new SqlParameter("@Person_num", SqlDbType.Int),
new SqlParameter("@Per_name", SqlDbType.NVarChar,),
new SqlParameter("@Work_att", SqlDbType.Date),
new SqlParameter("@Work_lable", SqlDbType.NVarChar,),
new SqlParameter("@Up1", SqlDbType.Time),
new SqlParameter("@Off2", SqlDbType.Time),
new SqlParameter("@Up3", SqlDbType.Time),
new SqlParameter("@Off4", SqlDbType.Time),
new SqlParameter("@Up5", SqlDbType.Time),
new SqlParameter("@Off6", SqlDbType.Time)};
praexcel[].Precision = ;
praexcel[].Precision = ;
praexcel[].Precision = ;
praexcel[].Precision = ;
praexcel[].Precision = ;
praexcel[].Precision = ; praexcel[].Value = dt.Rows[i][];
praexcel[].Value = dt.Rows[i][].ToString();
praexcel[].Value = Convert.ToDateTime(dt.Rows[i][].ToString());
praexcel[].Value = dt.Rows[i][];
if (dt.Rows[i][].ToString().Length == )
{
praexcel[].Value = DBNull.Value;
}
else if (dt.Rows[i][].ToString().Length > )
{
if (string.IsNullOrEmpty(dt.Rows[i][].ToString().Trim()))
{
praexcel[].Value = DBNull.Value;
}
else
{
praexcel[].Value = dt.Rows[i][].ToString().Trim();
} }
else
{
praexcel[].Value = dt.Rows[i][];
}
//
if (dt.Rows[i][].ToString().Length == )
{
praexcel[].Value = DBNull.Value;
}
else if (dt.Rows[i][].ToString().Length > )
{
if (string.IsNullOrEmpty(dt.Rows[i][].ToString().Trim()))
{
praexcel[].Value = DBNull.Value;
}
else
{
praexcel[].Value = dt.Rows[i][].ToString().Trim();
}
}
else
{
praexcel[].Value = dt.Rows[i][];
}
//
if (dt.Rows[i][].ToString().Length == )
{
praexcel[].Value = DBNull.Value;
}
else if (dt.Rows[i][].ToString().Length > )
{
if (string.IsNullOrEmpty(dt.Rows[i][].ToString().Trim()))
{
praexcel[].Value = DBNull.Value;
}
else
{
praexcel[].Value = dt.Rows[i][].ToString().Trim();
}
}
else
{
praexcel[].Value = dt.Rows[i][];
}
//
if (dt.Rows[i][].ToString().Length == )
{
praexcel[].Value = DBNull.Value;
}
else if (dt.Rows[i][].ToString().Length > )
{
if (string.IsNullOrEmpty(dt.Rows[i][].ToString().Trim()))
{
praexcel[].Value = DBNull.Value;
}
else
{
praexcel[].Value = dt.Rows[i][].ToString().Trim();
}
}
else
{
praexcel[].Value = dt.Rows[i][];
}
//
if (dt.Rows[i][].ToString().Length == )
{
praexcel[].Value = DBNull.Value;
}
else if (dt.Rows[i][].ToString().Length > )
{
if (string.IsNullOrEmpty(dt.Rows[i][].ToString().Trim()))
{
praexcel[].Value = DBNull.Value;
}
else
{
praexcel[].Value = dt.Rows[i][].ToString().Trim();
}
}
else
{
praexcel[].Value = dt.Rows[i][];
}
//
if (dt.Rows[i][].ToString().Length == )
{
praexcel[].Value = DBNull.Value;
}
else if (dt.Rows[i][].ToString().Length > )
{
if (string.IsNullOrEmpty(dt.Rows[i][].ToString().Trim()))
{
praexcel[].Value = DBNull.Value;
}
else
{
praexcel[].Value = dt.Rows[i][].ToString().Trim();
}
}
else
{
praexcel[].Value = dt.Rows[i][];
}
//if (dt.Rows[i][5].ToString().Length == 0) { praexcel[5].Value = DBNull.Value; } else { praexcel[5].Value = dt.Rows[i][5]; }
//if (dt.Rows[i][6].ToString().Length == 0) { praexcel[6].Value = DBNull.Value; } else { praexcel[6].Value = dt.Rows[i][6]; }
//if (dt.Rows[i][7].ToString().Length == 0) { praexcel[7].Value = DBNull.Value; } else { praexcel[7].Value = dt.Rows[i][7]; }
//if (dt.Rows[i][8].ToString().Length == 0) { praexcel[8].Value = DBNull.Value; } else { praexcel[8].Value = dt.Rows[i][8]; }
//if (dt.Rows[i][9].ToString().Length == 0) { praexcel[9].Value = DBNull.Value; } else { praexcel[9].Value = dt.Rows[i][9]; }
//praexcel[4].Value = dt.Rows[i][4];dt.Rows[i][9].ToString().Trim()
//praexcel[5].Value = dt.Rows[i][5];
//praexcel[6].Value = dt.Rows[i][6];
//praexcel[7].Value = dt.Rows[i][7];
//praexcel[8].Value = dt.Rows[i][8];
//praexcel[9].Value = dt.Rows[i][9];
//MessageBox.Show(dt.Rows[i][1].ToString() + dt.Rows[i][2].ToString());
try
{
if (SqlHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), praexcel) > )
{ insertcount++; }
else
{
errorcount++;
}
}
catch (Exception ex)
{
MessageBox.Show("导入成功:" + insertcount.ToString() + "条数据; 共" + dt.Rows.Count.ToString() + "条记录");
return insertcount;
}
}
MessageBox.Show("导入成功:"+insertcount.ToString()+"条数据; 失败"+errorcount.ToString());
return insertcount;
//}
//catch (Exception ex)
//{
//Console.Write(ex.Message);
// return insertcount;
//}
}
上一篇:SpringBoot启动流程分析(三):SpringApplication的run方法之prepareContext()方法


下一篇:Begin to record my bologs....