操作Excel 导入数据库

 最近在公司遇到相关Excel导入数据库的操作功能，是直接用 SqlBulkCopy 批量插入操作， 写了具体的方法可以直接调用 。代码如下：

/// <summary> /// 把Excel数据导入数据库 /// </summary> /// <param name="excelFile">文件路径</param> /// <param name="sheetName">工作表名</param> /// <param name="sheetName1">导入数据中的表名</param> /// <param name="connectionString">数据库连接字符串</param> public void TransferData(string excelFile, string sheetName,string sheetName1, string connectionString) { DataSet ds = new DataSet(); try { //获取全部数据 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = string.Format("select * from [{0}$]", sheetName); myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, sheetName); //如果目标表不存在则创建 //string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName); string strSql=@"if object_id('"+sheetName1+@"') is not null drop table "+sheetName1 +@" create table "+sheetName1+" ("; foreach (System.Data.DataColumn c in ds.Tables[0].Columns) { strSql += string.Format("[{0}] varchar(255),", c.ColumnName);//在此我都以varchar(255)字段类型为类的，其它可根据自己的需求进行判断 } //strSql = strSql.Trim(',') + ")"; strSql = strSql.Substring(0, strSql.LastIndexOf(','))+")"; using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString)) { sqlconn.Open(); System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand(); command.CommandText = strSql; command.ExecuteNonQuery(); sqlconn.Close(); } //用bcp导入数据 using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString)) { bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.BatchSize = 100;//每次传输的行数 bcp.NotifyAfter = 100;//进度提示的行数 bcp.DestinationTableName = sheetName1;//目标表 bcp.WriteToServer(ds.Tables[0]); } } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } } //进度显示 void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e) { this.Text = e.RowsCopied.ToString(); this.Update(); }

 

如果遇到在Excel的一列里既有数字类型又有字符串类型的在导入数据的时候可能会不完整，出现null

从网上搜了一下说只要把链接 Excel 的字符串中加上HDR=YES;IMEX=1' 的时候会都当做字符处理

但是经过多次试验还是有一些问题，最后我找到了两种解决方案：

1：就是在导入数据库前自己手动更改Excel里数据混乱的列 可以这样操作

选中一列-----数据-----分列------下一步------下一步----选择文本------确定就都转换为文本了。

如果直接 设置单元格格式---文本 这样好像是不可以的。

2：可以先把excel数据分析到datatable里然后再用SqlBulkCopy倒入数据库

具体操作可以参考：MR_ke 写的关于Excel的操作