如何在sqlserver中导出excel数据 [英] how to export the excel data in sqlserver
本文介绍了如何在sqlserver中导出excel数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有excel sheet.in我有记录。
i想要将excel表数据导出到sql server。
怎么办请帮助我。
解决方案
嗨。
选择您在Sql Server中的数据库名称 - >右键单击>导入数据
1.在数据源中:选择Microsoft Excel
2. Excel文件路径:提供Excel路径
3.版本:选择excel版本
现在给出要导入数据的Db名称和表名。
完成。
你可以参考这篇文章寻找解决方案
http://www.aspdotnet-suresh.com/2010/09/import-data-from-excel -to-sql-database.html [ ^ ]
我认为以下代码可以帮助您:
private void Form1_Load(对象发件人,System.EventArgs e)
{
工作簿workboo k = new Workbook();
workbook.LoadFromFile( @ .. \..\DataTableSample.xls );
工作表表= workbook.Worksheets [ 0 ];
DataTable data = sheet.ExportDataTable();
// 将字符串连接设置为本地数据库字符串
string connectionStr = ;
SqlConnection conn = new SqlConnection(connectionStr);
for ( int i = 0 ; i< data.rows.count; i ++)>
{
DataRow row = data.Rows [i];
int columnCount = data.Columns.Count;
string [] columns = new string < /跨度> [信息columnCount];
for ( int j = 0 ; j< columncount; j ++)>
{
columns [j] = row [j] .ToString();
}
// 假设数据表中只有三列
SqlCommand command = new SqlCommand( 插入DataTableSample(column1,column2,column3)值(' + columns [ 0 ] + ',' + columns [ 1 ] +
',' + columns [ 2 ] + < span class =code-string> '));
command.ExecuteNonQuery();
}
conn.Close();
}
此代码来自同一个问题的答案:
如何将excel文件(.xls& .xlsx)动态导入sql server 2000,而无需使用sqlbulkcopy类或sqlserver导入/导出向导 [ ^ 一>
i have excel sheet.in that i have records.
i want to export the excel sheet data into sql server.
How to do please help me.
解决方案
Hi.
Select your Database name in Sql Server-> right click > Import Data
1. in Data source : select the Microsoft Excel
2. Excel File path : Give your Excel path
3. version : select version of excel
Now give the Db Name and Table name where you want to import data.
and done.
u can refer this article for the solution
http://www.aspdotnet-suresh.com/2010/09/import-data-from-excel-to-sql-database.html[^]
I think below code can help you:
private void Form1_Load(object sender, System.EventArgs e) { Workbook workbook = new Workbook(); workbook.LoadFromFile(@"..\..\DataTableSample.xls"); Worksheet sheet = workbook.Worksheets[0]; DataTable data= sheet.ExportDataTable(); //set the string connection to be the local databasestring string connectionStr=""; SqlConnection conn= new SqlConnection(connectionStr); for (int i=0;i<data.rows.count;i++)> { DataRow row=data.Rows[i]; int columnCount=data.Columns.Count; string[] columns=new string[columnCount]; for(int j=0;j<columncount;j++)> { columns[j]=row[j].ToString(); } // suppose there are only three columns in your datatable SqlCommand command=new SqlCommand ("insert into DataTableSample(column1,column2,column3) values('"+columns[0]+"','"+columns[1]+ " ','"+columns[2]+"')"); command.ExecuteNonQuery(); } conn.Close(); }
This code is from the answer of this similar question:
How to Import excel file (.xls &.xlsx) into sql server 2000 dynamically without using sqlbulkcopy class or sqlserver import/export wizard[^]
这篇关于如何在sqlserver中导出excel数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文