如何将Excel工作表数据导出到MS SQL服务器？

问题描述：

您好，

我想从Excel表格中获取数据并插入我的数据库表格。

有没有最佳选择。

我使用ajax调用来上传文件。并使用网络服务保存数据。

我尝试过：

我想将Excel工作表数据导出到ms sql数据库..

Hello,
I want to get data from Excel sheet and insert into my database table.
Is there any best option to do this.
I am use ajax call to upload the file. and save data using web service.

What I have tried:

I want to export excel sheet data to ms sql database..

答

您可以通过导入和导出向导执行此操作：了解如何将数据从Excel电子表格导入SQL Server [ ^ ]

you can do this by Import and Export Wizard: Know How to Import Data From Excel Spreadsheet to SQL Server[^]

make a linked服务器从您的sql server到excel，数据将使用open row set命令进入sql server。

Make a linked server from your sql server to excel and data will come in sql server using open row set command.

我转换为CSV格式的数据表，但我无法下载csv文件，

请看下面我的代码。

I am convert my datatable in CSV format but I cant able to download the csv file,
please see below my code.

DataTable dt=new DataTable();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
con.Open();
SqlDataAdapter da = new SqlDataAdapter("getContacts", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("@userAutoId", userAutoId);
//cmd.Parameters.Add("@retValue", System.Data.SqlDbType.VarChar).Direction = System.Data.ParameterDirection.ReturnValue;
da.Fill(dt);

//DataTable dt = GetData(cmd);

Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition","attachment;filename=DataTable.csv");
Response.Charset = "";
Response.ContentType = "csv/text";


StringBuilder sb = new StringBuilder();
for (int k = 0; k < dt.Columns.Count; k++)
{
    //add separator
    sb.Append(dt.Columns[k].ColumnName + ',');
}
//append new line
sb.Append("\r\n");
for (int i = 0; i < dt.Rows.Count; i++)
{
    for (int k = 0; k < dt.Columns.Count; k++)
    {
        //add separator
        sb.Append(dt.Rows[i][k].ToString().Replace(",", ";") + ',');
    }
    //append new line
    sb.Append("\r\n");
}
Response.Output.Write(sb.ToString());
Response.Flush();
Response.End();

如何将Excel工作表数据导出到MS SQL服务器？

相关推荐