使用asp.net将数据excel文件上传到sql server management studio [英] upload data excel file into sql server management studio using asp.net

问题描述

what I'm doing right now upload the file into folder and display on gridview..
but my main task is to upload it into sql server using asp.net...How to make it??

protected void Button_Upload_Click（object sender，EventArgs e）
{
//从web.config文件获取路径以上传
string FilePath = ConfigurationManager.ConnectionStrings [" connect"]。ToString（）;
string filename = string.Empty;
//检查文件是否被选中
if（FileUpload1.HasFile）
{
try
{
string [] allowdFile = {" ; .xls"，" .xlsx" };
//这里我们只允许excel文件，因此验证所选文件是否为pdf或不是
string FileExt = Path.GetExtension（FileUpload1.PostedFile.FileName）;
//检查所选文件是否为有效扩展名
bool isValidFile = allowdFile.Contains（FileExt）;
if（！isValidFile）
{
lblMessage.ForeColor = System.Drawing.Color.Red;
lblMessage.Text =" Please Upload only Excel" ;;
}
else
{
//获取上传文件的大小，此处限制文件大小
int FileSize = FileUpload1.PostedFile.ContentLength;
if（FileSize< = 1048576）// 1048576 = 1MB
{
//获取所选文件的文件名
filename = Path.GetFileName（Server.MapPath（FileUpload1。文件名））;
//将所选文件保存到服务器位置
FileUpload1.SaveAs（Server.MapPath（FilePath）+ filename）;
//获取文件路径
string filePath = Server.MapPath（FilePath）+ filename;
//基于excel版本打开与excel文件的连接
OleDbConnection con = null;
if（FileExt ==" .xls"）
{
con = new OleDbConnection（@" Provider = Microsoft.Jet.OLEDB.4.0; Data Source = {0}; Extended Properties ='Excel 8.0; HDR = {1}'"）;
}
else if（FileExt ==" .xlsx"）
{
con = new OleDbConnection（@" Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {0};扩展属性='Excel 8.0; HDR = {1}'"）;
}
con.Open（）;
//获取Excel工作表中可用工作表的列表
DataTable dt = con.GetOleDbSchemaTable（OleDbSchemaGuid.Tables，null）;
//获取第一个工作表名称
string getExcelSheetName = dt.Rows [0] [" Table_Name"]。ToString（）;
//从Excel工作表中的第一个工作表中选择行并填入数据集
OleDbCommand ExcelCommand = new OleDbCommand（@" SELECT * FROM [" + getExcelSheetName + @"]"，con）;
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter（ExcelCommand）;
DataSet ExcelDataSet = new DataSet（）;
ExcelAdapter.Fill（ExcelDataSet）;
con.Close（）;
//将数据集绑定到gridview中以显示excel内容
GridView1.DataSource = ExcelDataSet;
GridView1.DataBind（）;
}
else
{
lblMessage.Text ="附件超过1MB的文件";

}
}
}
catch（Exception ex）
{
lblMessage.Text ="上传文件时出错： " + ex.Message;

}
}
其他
{
lblMessage.Text ="请选择要上传的文件";
}
}

protected void Button_Upload_Click(object sender, EventArgs e) { //Get path from web.config file to upload string FilePath = ConfigurationManager.ConnectionStrings["connect"].ToString(); string filename = string.Empty; //To check whether file is selected or not if (FileUpload1.HasFile) { try { string[] allowdFile = { ".xls", ".xlsx" }; //Here we allowing only excel file so verifying selected file pdf or not string FileExt = Path.GetExtension(FileUpload1.PostedFile.FileName); //check whether selected file is valid extension or not bool isValidFile = allowdFile.Contains(FileExt); if (!isValidFile) { lblMessage.ForeColor = System.Drawing.Color.Red; lblMessage.Text = "Please Upload only Excel"; } else { //Get size of uploaded file, here restricting size of file int FileSize = FileUpload1.PostedFile.ContentLength; if(FileSize <= 1048576)//1048576 = 1MB { //Get file name of selected file filename = Path.GetFileName(Server.MapPath(FileUpload1.FileName)); //save selected file into server location FileUpload1.SaveAs(Server.MapPath(FilePath) + filename); //Get file path string filePath = Server.MapPath(FilePath) + filename; //open the connection with excel file based on excel version OleDbConnection con = null; if (FileExt == ".xls") { con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"); } else if (FileExt == ".xlsx") { con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"); } con.Open(); //Get the list of sheet available in excel sheet DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //Get first sheet name string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString(); //select rows from first sheet in excel sheet and fill into dataset OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con); OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand); DataSet ExcelDataSet = new DataSet(); ExcelAdapter.Fill(ExcelDataSet); con.Close(); //Bind the dataset into gridview to display excel contents GridView1.DataSource = ExcelDataSet; GridView1.DataBind(); } else { lblMessage.Text = "Attachment file more than 1MB"; } } } catch (Exception ex) { lblMessage.Text = "Error occured during uploading a file:" + ex.Message; } } else { lblMessage.Text = "Please select a file to upload"; } }

$

推荐答案

你好saddiq01，

Hi saddiq01,

我们可以通过SqlBulkCopy和以下代码导入SQL Server，供你参考。

We could import into SQL Server via SqlBulkCopy and the following code for your reference.

注意：Excel和SQL Server需要具有相同的表结构。

Note: Excel and SQL Server need to have the same table struct.

protected void btnUpload_Click(object sender, EventArgs e)
        {          
            if (!FileUpload1.HasFile)
            {
                try
                {
                    string path = string.Concat(Server.MapPath("~/Uploaded Folder/" + FileUpload1.FileName)); 
                    FileUpload1.SaveAs(path);
 
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
                    OleDbConnection connection = new OleDbConnection();
                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1

"，connection）;
connection.Open（）;
//为数据工作表创建DbDataReader
DbDataReader dr = command.ExecuteReader（）;

// SQL Server连接字符串
字符串sqlConnectionString = @"数据源= MYPC;初始目录=学生;用户ID = sa;密码= wintellect" ;;

//批量复制到SQL Server
SqlBulkCopy bulkInsert = new SqlBulkCopy（sqlConnectionString）;
bulkInsert.DestinationTableName =" Student_Record" ;;
bulkInsert.WriteToServer（dr）;
Label1.Text =" Ho Gaya" ;;
}
catch（exception ex）
{
Label1.Text = ex.Message;
}
}
}

", connection); connection.Open(); // Create DbDataReader to Data Worksheet DbDataReader dr = command.ExecuteReader(); // SQL Server Connection String string sqlConnectionString = @"Data Source=MYPC;Initial Catalog=Student;User ID=sa;Password=wintellect"; // Bulk Copy to SQL Server SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString); bulkInsert.DestinationTableName = "Student_Record"; bulkInsert.WriteToServer(dr); Label1.Text = "Ho Gaya"; } catch(Exception ex) { Label1.Text = ex.Message; } } }

祝你好运，

张龙

这篇关于使用asp.net将数据excel文件上传到sql server management studio的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！