Excel导入数据库SQlSever
aspx文件部分代码
<div style="text-align: center; width: 100%; height: auto; max-width: 1000px;">
<center style="text-align: center; width: 100%; height: auto; min-width: 100%; max-width: 1000px;">
<div style="height: 10px; width: 100%; max-width: 1000px;">
</div>
<div style="width: 100%; height: 28px; text-align: left">
<span class="style1">导入人员信息</span>
</div>
<div style="width: 100%; height: 5px; text-align: left; background-color: #D5E6E4">
</div>
<div style="border: 3px dotted #D5E6E4; width: 90%; height: auto; text-align: left;
min-width: 800px; max-width: 1000px">
<div style="height: 20px; width: 100%;">
</div>
<div style="height: 30px; width: 100%;">
<div style="height: 30px; width: 148px; float: left" class="style2">
选择文件路径：</div>
<div style="height: 30px; width: 255px; float: left">
<asp:FileUpload ID="FileUpload1" runat="server" Width="235px" Height="25px" />
</div>
<div style="height: 30px; width: 28px; float: left">
</div>
<div style="height: 30px; width: 168px; float: left">
</div>
</div>
<div style="height: 30px; width: 100%;">
<div style="height: 30px; width: 148px; float: left" class="style3">
<b>Excel里的表名：</b></div>
<div style="height: 30px; width: 170px; float: left">
<ext:TextField ID="tf_ExcelTableName" runat="server" Width="160px" />
</div>
<div style="height: 30px; width: 481px; float: left" class="style5">
默认是Sheet1,若不是，请填写实际Excel表名(区分大小写).</div>
</div>
<div style="height: 30px; width: 100%;">
<div style="height: 30px; width: 148px; float: left" class="style3">
</div>
<div style="height: 30px; width: 172px; float: left">
</div>
<div style="height: 30px; width: 160px; float: left" class="style4">
<asp:Button ID="bt_import" runat="server" Text="导入" Height="23px"
onclick="bt_import_Click" Width="43px" />
</div>
</div>
<div style="height: 30px; width: 100%;"></div>
</div>
</center>
</div>
aspx.cs文件部分代码
protected void bt_import_Click(object sender, EventArgs e)
{
bt_import.Visible = false;
if (FileUpload1.HasFile)
{
int count = 0;
string path = "~/upfile/" + this.FileUpload1.FileName.ToString().Trim();
path = Server.MapPath(path);
this.FileUpload1.SaveAs(path);
count = bll.GBDataTableToDataBase(bll.uploadFile(path, "Sheet1", tf_ExcelTableName.Text));
if (count != 0)
{
LogAction("导入干部人员信息[" + count.ToString() + "]条");
}
Ext.Net.X.Msg.Alert("提示", "成功导入数据" + count.ToString() + "条").Show();
System.IO.File.Delete(path);
}
bt_import.Visible = true;
}
SQLDAL层.cs文件部分代码
/// <summary>
/// 数据表插入数据库
/// </summary>
/// <param name="dataTable">DataTable</param>
/// <returns>插入条数</returns>
public int GBDataTableToDataBase(DataTable dataTable)
{
string sqlcmd = "";
int count = 0;
foreach (DataRow row in dataTable.Rows)
{
string workcode = "";
string gb_name = "";
string sex = "";
DateTime? birthday;
string jg = "";
string mz = "";
string hyzk = "";
string zzmm = "";
DateTime? cjdpsj;
string xl = "";
string xw = "";
string major = "";
string zjhm = "";
string rzbm = "";
string xrzw = "";
string zwjb = "";
string xsdyjb = "";
string ldzwlx = "";
string bzlx = "";
DateTime? rxzwsj ;
DateTime? rxzjsj ;
DateTime? cjgzsj ;
DateTime? drszsj ;
DateTime? jrbdwsj ;
string salary_level = "";
try
{
if (row["用户名"].ToString().Trim() == "")
{
continue;
}
if (row["姓名"].ToString().Trim() == "")
{
continue;
}
workcode = row["用户名"].ToString().Trim();
gb_name = row["姓名"].ToString().Trim();
if (row["性别"].ToString().Trim() == "")
{
continue;
}
sex = row["性别"].ToString().Trim();
if (!Utils.IsDateString(row["出生日期"].ToString().Trim()))
{
birthday = null;
}
else
{
birthday = Convert.ToDateTime(row["出生日期"].ToString().Trim());
}
jg = row["籍贯"].ToString().Trim();
mz = row["民族"].ToString().Trim();
hyzk = row["婚姻状况"].ToString().Trim();
zzmm = row["政治面貌"].ToString().Trim();
// if (!Utils.IsDateString(row["参加党派时间"].ToString().Trim()))
if (!Utils.IsDateString(row["参加党派时间"].ToString().Trim()))
{
cjdpsj = null;
}
else
{
cjdpsj = Convert.ToDateTime(row["参加党派时间"].ToString().Trim());
}
xl = row["学历"].ToString().Trim();
xw = row["学位"].ToString().Trim();
major = row["专业"].ToString().Trim();
zjhm = row["证件号码"].ToString().Trim();
rzbm = row["任职部门"].ToString().Trim();
xrzw = row["现任职务"].ToString().Trim();
zwjb = row["职务级别"].ToString().Trim();
xsdyjb = row["享受待遇级别"].ToString().Trim();
ldzwlx = row["领导职务类别"].ToString().Trim();
bzlx = row["编制类型"].ToString().Trim();
salary_level=row["薪级"].ToString().Trim();
if (!Utils.IsDateString(row["任现职务时间"].ToString().Trim()))
{
rxzwsj = null;
}
else
{
rxzwsj = Convert.ToDateTime(row["任现职务时间"].ToString().Trim());
}
if (!Utils.IsDateString(row["任现职级时间"].ToString().Trim()))
{
rxzjsj = null;
}
else
{
rxzjsj = Convert.ToDateTime(row["任现职级时间"].ToString().Trim());
}
if (!Utils.IsDateString(row["参加工作时间"].ToString().Trim()))
{
cjgzsj = null;
}
else
{
cjgzsj = Convert.ToDateTime(row["参加工作时间"].ToString().Trim());
}
if (!Utils.IsDateString(row["调入深圳时间"].ToString().Trim()))
{
drszsj = null;
}
else
{
drszsj = Convert.ToDateTime(row["调入深圳时间"].ToString().Trim());
}
if (!Utils.IsDateString(row["进入本单位时间"].ToString().Trim()))
{
jrbdwsj = null;
}
else
{
jrbdwsj = Convert.ToDateTime(row["进入本单位时间"].ToString().Trim());
}
sqlcmd = @" Declare @count int Select @count=count(1) FROM [DB_LtbIM].[dbo].[GB_INFO] where workcode='" + workcode + @"';
IF @count=0 Begin insert [DB_LtbIM].[dbo].[GB_INFO] ([workcode],[gb_name],[gb_sex] ,[gb_birthday] ,[gb_hometown]
,[gb_nation] ,[gb_marital_state] ,[gb_political_status] ,[gb_inparty_date],[gb_edu_bgd] ,[gb_degree],[gb_major] ,[gb_id_no]
,[dept_name],[gb_position_now] ,[gb_position_level],[gb_treatment_level],[gb_leader_level],[gb_formation_type],[gb_position_date]
,[gb_position_level_date],[gb_inwork_date],[gb_insz_date] ,[gb_inbdw_date],salary_level) values ('" + workcode + "' , '" + gb_name + "' , '" + sex + "'," + (birthday == null ? ("NULL") : ("'" + birthday.ToString() + "'")) + ",'" + jg + "','"
+ mz + "', '" + hyzk + "', '" + zzmm + "', " + (cjdpsj == null ? ("NULL") : ("'" + cjdpsj.ToString() + "'")) + ", '" + xl + "', '" + xw + "', '" + major + "', '" + zjhm + "', '"
+ rzbm + "', '" + xrzw + "', '" + zwjb + "', '" + xsdyjb + "', '" + ldzwlx + "', '" + bzlx + "', " + (rxzwsj == null ? ("NULL") : ("'" + rxzwsj.ToString() + "'")) + ", "
+ (rxzjsj == null ? ("NULL") : ("'" + rxzjsj.ToString() + "'")) + ", " + (cjgzsj == null ? ("NULL") : ("'" + cjgzsj.ToString() + "'")) + ", " + (drszsj == null ? ("NULL") : ("'" + drszsj.ToString() + "'")) + ", " + (jrbdwsj == null ? ("NULL") : ("'" + jrbdwsj.ToString() + "'")) + ",'" + salary_level + "')"
+ @"; INSERT INTO [DB_LtbIM].[dbo].[USER] ([workcode],[passwd]) VALUES ('" + workcode + @"','E10ADC3949BA59ABBE56E057F20F883E'); "
+ @" END ELSE Begin UPDATE [DB_LtbIM].[dbo].[GB_INFO] SET [gb_name] ='" + gb_name + "',[gb_sex] ='" + sex + "',[gb_birthday] =" + (birthday == null ? ("NULL") : ("'" + birthday.ToString() + "'"))
+ ",[gb_hometown] ='" + jg + "',[gb_nation] = '" + mz + "' ,[gb_marital_state] ='" + hyzk + "',[gb_political_status] ='" + zzmm
+ "',[gb_inparty_date] =" + (cjdpsj == null ? ("NULL") : ("'" + cjdpsj.ToString() + "'")) + ",[gb_edu_bgd] ='" + xl + "' ,[gb_degree] ='" + xw + "' ,[gb_major] = '" + major
+ "',[gb_id_no] = '" + zjhm + "',[dept_name] ='" + rzbm + "' ,[gb_position_now] ='" + xrzw + "' ,[gb_position_level] = '" + zwjb
+ "',[gb_treatment_level] ='" + xsdyjb + "',[gb_leader_level] ='" + ldzwlx + "',[gb_formation_type] ='" + bzlx + "' ,[gb_position_date] =" + (rxzwsj == null ? ("NULL") : ("'" + rxzwsj.ToString() + "'"))
+ ",[gb_position_level_date] = " + (rxzjsj == null ? ("NULL") : ("'" + rxzjsj.ToString() + "'")) + ",[gb_inwork_date] = " + (cjgzsj == null ? ("NULL") : ("'" + cjgzsj.ToString() + "'")) + ",[gb_insz_date] = " + (drszsj == null ? ("NULL") : ("'" + drszsj.ToString() + "'")) + ",[gb_inbdw_date] =" + (jrbdwsj == null ? ("NULL") : ("'" + jrbdwsj.ToString() + "'")) + ",salary_level='" + salary_level + "'" + " "
+ @"where workcode='" + workcode + "';" +
"End";
SQLServerHelper.ExecuteNonQuery(sqlcmd);
count++;
}
catch (System.Exception)
{
continue;
}
}
return count;
}
/// <summary>
/// 将excel文件中表格数据导入数据表中
/// </summary>
/// <param name="path"></param>
/// <param name="tableName1">默认表名Sheet1</param>
/// <param name="tableName2">不是Sheet1表明，实际传入表名</param>
/// <returns>DataTable</returns>
public DataTable uploadFile(string path, string tableName1,string tableName2)
{
// string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;server=192.168.20.24;database=DB_LtbIM;user id=sa;password=andrew;min pool size=1;max pool size=100;packet size=1024";
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;" + "Data Source=" + path + ";" + "Extended Properties='Excel 8.0; IMEX＝1;'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
string tableName = tableName1;
OleDbDataAdapter mycommand = null;
if (tableName2.Trim().Length > 0 && !tableName2.Equals(string.Empty))
{
tableName = tableName2;
}
strExcel = "select * from [" + tableName + "$] ";
try
{
mycommand = new OleDbDataAdapter(strExcel, conn);
DataTable dataTable = new DataTable();
mycommand.Fill(dataTable);
conn.Close();
return dataTable;
}
catch (System.Exception)
{
conn.Close();
return new DataTable() ;// throw new Exception(ex.Message);
}
finally
{
;
}
}
思路：
1. 将Excel文件数据转成DataTable类型数据集
如方法：
uploadFile
1 /// <summary> 2 /// 将excel文件中表格数据导入数据表中 3 /// </summary> 4 /// <param name="path"></param> 5 /// <param name="tableName1">默认表名Sheet1</param> 6 /// <param name="tableName2">不是Sheet1表明，实际传入表名</param> 7 /// <returns>DataTable</returns> 8 9 public DataTable uploadFile(string path, string tableName1,string tableName2) 10 { 11 // string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;server=192.168.20.24;database=DB_LtbIM;user id=sa;password=andrew;min pool size=1;max pool size=100;packet size=1024"; 12 13 14 string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;" + "Data Source=" + path + ";" + "Extended Properties='Excel 8.0; IMEX＝1;'"; 15 16 OleDbConnection conn = new OleDbConnection(strConn); 17 conn.Open(); 18 string strExcel = ""; 19 string tableName = tableName1; 20 OleDbDataAdapter mycommand = null; 21 if (tableName2.Trim().Length > 0 && !tableName2.Equals(string.Empty)) 22 { 23 tableName = tableName2; 24 } 25 26 strExcel = "select * from [" + tableName + "$] "; 27 try 28 { 29 mycommand = new OleDbDataAdapter(strExcel, conn); 30 DataTable dataTable = new DataTable(); 31 mycommand.Fill(dataTable); 32 33 conn.Close(); 34 return dataTable; 35 } 36 catch (System.Exception) 37 { 38 conn.Close(); 39 return new DataTable() ;// throw new Exception(ex.Message); 40 } 41 finally 42 { 43 ; 44 } 45 46 } 47
注意配置好参数： string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;" + "Data Source=" + path + ";" + "Extended Properties='Excel 8.0; IMEX＝1;'";
具体参数意思请另查询。
2.第二部，只需读取DataTable实例，一行行将输入插入或更新数据库里面的数据记录
如方法:
GBDataTableToDataBase
1 /// <summary> 2 /// 数据表插入数据库 3 /// </summary> 4 /// <param name="dataTable">DataTable</param> 5 /// <returns>插入条数</returns> 6 public int GBDataTableToDataBase(DataTable dataTable) 7 { 8 string sqlcmd = ""; 9 int count = 0; 10 foreach (DataRow row in dataTable.Rows) 11 { 12 string workcode = ""; 13 string gb_name = ""; 14 string sex = ""; 15 DateTime? birthday; 16 string jg = ""; 17 string mz = ""; 18 string hyzk = ""; 19 string zzmm = ""; 20 DateTime? cjdpsj; 21 string xl = ""; 22 string xw = ""; 23 string major = ""; 24 string zjhm = ""; 25 string rzbm = ""; 26 string xrzw = ""; 27 string zwjb = ""; 28 string xsdyjb = ""; 29 string ldzwlx = ""; 30 string bzlx = ""; 31 DateTime? rxzwsj ; 32 DateTime? rxzjsj ; 33 DateTime? cjgzsj ; 34 DateTime? drszsj ; 35 DateTime? jrbdwsj ; 36 string salary_level = ""; 37 38 try 39 { 40 if (row["用户名"].ToString().Trim() == "") 41 { 42 continue; 43 } 44 if (row["姓名"].ToString().Trim() == "") 45 { 46 continue; 47 } 48 49 workcode = row["用户名"].ToString().Trim(); 50 gb_name = row["姓名"].ToString().Trim(); 51 52 53 if (row["性别"].ToString().Trim() == "") 54 { 55 continue; 56 } 57 sex = row["性别"].ToString().Trim(); 58 59 if (!Utils.IsDateString(row["出生日期"].ToString().Trim())) 60 { 61 birthday = null; 62 } 63 else 64 { 65 birthday = Convert.ToDateTime(row["出生日期"].ToString().Trim()); 66 } 67 68 69 jg = row["籍贯"].ToString().Trim(); 70 71 mz = row["民族"].ToString().Trim(); 72 73 hyzk = row["婚姻状况"].ToString().Trim(); 74 75 76 zzmm = row["政治面貌"].ToString().Trim(); 77 78 // if (!Utils.IsDateString(row["参加党派时间"].ToString().Trim())) 79 if (!Utils.IsDateString(row["参加党派时间"].ToString().Trim())) 80 { 81 82 cjdpsj = null; 83 } 84 else 85 { 86 cjdpsj = Convert.ToDateTime(row["参加党派时间"].ToString().Trim()); 87 } 88 89 90 xl = row["学历"].ToString().Trim(); 91 92 93 xw = row["学位"].ToString().Trim(); 94 95 96 major = row["专业"].ToString().Trim(); 97 98 99 zjhm = row["证件号码"].ToString().Trim(); 100 101 rzbm = row["任职部门"].ToString().Trim(); 102 103 104 105 xrzw = row["现任职务"].ToString().Trim(); 106 107 zwjb = row["职务级别"].ToString().Trim(); 108 109 110 xsdyjb = row["享受待遇级别"].ToString().Trim(); 111 112 113 ldzwlx = row["领导职务类别"].ToString().Trim(); 114 115 bzlx = row["编制类型"].ToString().Trim(); 116 salary_level=row["薪级"].ToString().Trim(); 117 118 if (!Utils.IsDateString(row["任现职务时间"].ToString().Trim())) 119 { 120 rxzwsj = null; 121 } 122 else 123 { 124 rxzwsj = Convert.ToDateTime(row["任现职务时间"].ToString().Trim()); 125 } 126 127 128 if (!Utils.IsDateString(row["任现职级时间"].ToString().Trim())) 129 { 130 rxzjsj = null; 131 } 132 else 133 { 134 rxzjsj = Convert.ToDateTime(row["任现职级时间"].ToString().Trim()); 135 } 136 137 if (!Utils.IsDateString(row["参加工作时间"].ToString().Trim())) 138 { 139 cjgzsj = null; 140 } 141 else 142 { 143 cjgzsj = Convert.ToDateTime(row["参加工作时间"].ToString().Trim()); 144 } 145 146 if (!Utils.IsDateString(row["调入深圳时间"].ToString().Trim())) 147 { 148 drszsj = null; 149 } 150 else 151 { 152 drszsj = Convert.ToDateTime(row["调入深圳时间"].ToString().Trim()); 153 } 154 155 if (!Utils.IsDateString(row["进入本单位时间"].ToString().Trim())) 156 { 157 jrbdwsj = null; 158 } 159 else 160 { 161 jrbdwsj = Convert.ToDateTime(row["进入本单位时间"].ToString().Trim()); 162 } 163 164 165 sqlcmd = @" Declare @count int Select @count=count(1) FROM [DB_LtbIM].[dbo].[GB_INFO] where workcode='" + workcode + @"'; 166 IF @count=0 Begin insert [DB_LtbIM].[dbo].[GB_INFO] ([workcode],[gb_name],[gb_sex] ,[gb_birthday] ,[gb_hometown] 167 ,[gb_nation] ,[gb_marital_state] ,[gb_political_status] ,[gb_inparty_date],[gb_edu_bgd] ,[gb_degree],[gb_major] ,[gb_id_no] 168 ,[dept_name],[gb_position_now] ,[gb_position_level],[gb_treatment_level],[gb_leader_level],[gb_formation_type],[gb_position_date] 169 ,[gb_position_level_date],[gb_inwork_date],[gb_insz_date] ,[gb_inbdw_date],salary_level) values ('" + workcode + "' , '" + gb_name + "' , '" + sex + "'," + (birthday == null ? ("NULL") : ("'" + birthday.ToString() + "'")) + ",'" + jg + "','" 170 + mz + "', '" + hyzk + "', '" + zzmm + "', " + (cjdpsj == null ? ("NULL") : ("'" + cjdpsj.ToString() + "'")) + ", '" + xl + "', '" + xw + "', '" + major + "', '" + zjhm + "', '" 171 + rzbm + "', '" + xrzw + "', '" + zwjb + "', '" + xsdyjb + "', '" + ldzwlx + "', '" + bzlx + "', " + (rxzwsj == null ? ("NULL") : ("'" + rxzwsj.ToString() + "'")) + ", " 172 + (rxzjsj == null ? ("NULL") : ("'" + rxzjsj.ToString() + "'")) + ", " + (cjgzsj == null ? ("NULL") : ("'" + cjgzsj.ToString() + "'")) + ", " + (drszsj == null ? ("NULL") : ("'" + drszsj.ToString() + "'")) + ", " + (jrbdwsj == null ? ("NULL") : ("'" + jrbdwsj.ToString() + "'")) + ",'" + salary_level + "')" 173 + @"; INSERT INTO [DB_LtbIM].[dbo].[USER] ([workcode],[passwd]) VALUES ('" + workcode + @"','E10ADC3949BA59ABBE56E057F20F883E'); " 174 + @" END ELSE Begin UPDATE [DB_LtbIM].[dbo].[GB_INFO] SET [gb_name] ='" + gb_name + "',[gb_sex] ='" + sex + "',[gb_birthday] =" + (birthday == null ? ("NULL") : ("'" + birthday.ToString() + "'")) 175 + ",[gb_hometown] ='" + jg + "',[gb_nation] = '" + mz + "' ,[gb_marital_state] ='" + hyzk + "',[gb_political_status] ='" + zzmm 176 + "',[gb_inparty_date] =" + (cjdpsj == null ? ("NULL") : ("'" + cjdpsj.ToString() + "'")) + ",[gb_edu_bgd] ='" + xl + "' ,[gb_degree] ='" + xw + "' ,[gb_major] = '" + major 177 + "',[gb_id_no] = '" + zjhm + "',[dept_name] ='" + rzbm + "' ,[gb_position_now] ='" + xrzw + "' ,[gb_position_level] = '" + zwjb 178 + "',[gb_treatment_level] ='" + xsdyjb + "',[gb_leader_level] ='" + ldzwlx + "',[gb_formation_type] ='" + bzlx + "' ,[gb_position_date] =" + (rxzwsj == null ? ("NULL") : ("'" + rxzwsj.ToString() + "'")) 179 + ",[gb_position_level_date] = " + (rxzjsj == null ? ("NULL") : ("'" + rxzjsj.ToString() + "'")) + ",[gb_inwork_date] = " + (cjgzsj == null ? ("NULL") : ("'" + cjgzsj.ToString() + "'")) + ",[gb_insz_date] = " + (drszsj == null ? ("NULL") : ("'" + drszsj.ToString() + "'")) + ",[gb_inbdw_date] =" + (jrbdwsj == null ? ("NULL") : ("'" + jrbdwsj.ToString() + "'")) + ",salary_level='" + salary_level + "'" + " " 180 + @"where workcode='" + workcode + "';" + 181 "End"; 182 SQLServerHelper.ExecuteNonQuery(sqlcmd); 183 count++; 184 185 } 186 catch (System.Exception) 187 { 188 189 continue; 190 } 191 192 193 194 195 } 196 return count; 197 198 } 199
3.完工。测试吧。慢慢调bug。因为我没有传参数，而是直接字符串拼接的形式。这个是要非常细心的。。。。。
小y同学：终于记录下来啦，虽然都只是粘贴代码。因为太忙了，没办法。而且考完试就飞啦，又没时间。还是想说一句，坑y的实习工作，干嘛来个那么底层的培训。。。
浙公网安备 33010602011771号