C# 上传Excel导入数据 SqlBulkCopy

来源：互联网发布：阿里云服务器抢票编辑：程序博客网时间：2024/06/04 17:11

//获取后缀名
String fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();

// 指定上传路径
String filePath = Server.MapPath("../Upload/");
string fileName = string.Empty;

        if (FileUpload1.HasFile && CheckFileType(fileExtension))
        {
            try
            {
                fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + fileExtension;

                // 存在服务器指定目录下
                FileUpload1.PostedFile.SaveAs(filePath + fileName);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message.ToString());
                return;
            }
        }
        else
        {
            MessageBox.Show("上传失败，提示: 上传文件不能为空或者文件格式不正确 ");
            return;
        }

#region 将Excel数据导入DB

//注意，此处使用的Sheet1名称
TransferData(filePath, fileName, "Sheet1", GISSDefinition.LocalConnectionString);

#endregion

private bool CheckFileType(string fileExtension)
{
Boolean fileOK = false;

        if (fileExtension.ToLower().Trim() == ".xls" || fileExtension.ToLower().Trim() == ".xlsx")
        {
            fileOK = true;
        }

return fileOK;
}

DataSet ds = new DataSet();

// 获取全部数据
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " + " Data Source= " + filePath + fileName + " ; " + " Extended Properties=Excel 8.0; ";

OleDbConnection oleConn = new OleDbConnection(strConn);
oleConn.Open();

string strExcel = string.Format("select * from [{0}$] ", sheetName);

OleDbDataAdapter myda = new OleDbDataAdapter(strExcel, strConn);
myda.Fill(ds);

            // 用bcp导入数据
            using (SqlBulkCopy bcp = new SqlBulkCopy(connectionString))
            {
                bcp.DestinationTableName = "Temp_VirLog"; // 目标表
                bcp.BatchSize = 100; // 每次传输的行数

bcp.WriteToServer(ds.Tables[0]);
}

oleConn.Close();

====================================================================================================

            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + oriFileName + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter("select ITEM_NO,VND_CODE,VND_NAME,TAX,CUR,PRICE,PERCENTAGE,DT_FROM,DT_TO,SRC_CODE,REMARK from [Sheet1$] S1", conn);
            da.Fill(ds);
            da.Dispose();
            conn.Close();
            conn.Dispose();

====================================================================================================