如何在sqlserver中导出excel数据 [英] how to export the excel data in sqlserver

问题描述

我有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屋！