Excel到SQL Server 2007的数据传输 [英] excel to sql server 2007 data transfer

问题描述

我如何使用c#asp.net

解决方案

1.从Microsoft Excel到datadase传输数据.下面的功能..

 公共 静态 DataTable exceldata( string  filePath)
        {
            DataTable dtexcel = 新 DataTable();
            布尔 hasHeaders =  false ;
            字符串 HDR = hasHeaders吗? " :" 否";
            字符串 strConn;
            如果(filePath.Substring(filePath.LastIndexOf(' . ')).ToLower()== " )
                strConn = "  + filePath + " "  ; IMEX = 0 \" ;
            其他
                strConn = "  + filePath + " "  ; IMEX = 0 \" ;
            OleDbConnection conn = 新 OleDbConnection(strConn);
            conn.Open();
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables，新建 对象 [] {空，"   TABLE"})；
            //  XL文件总表
            /*   foreach(schemaTable.Rows中的DataRow schemaRow)
            {
            } */
            // 环绕Xl文件的第一张纸
            DataRow schemaRow = schemaTable.Rows [ 0 ];
            字符串 sheet = schemaRow ["  ] .ToString();
            如果(！sheet.EndsWith(" ))
            {
                字符串查询= "  +工作表+ " ;
                OleDbDataAdapter daexcel = 新 OleDbDataAdapter(query，conn);
                dtexcel.Locale = CultureInfo.CurrentCulture;
                daexcel.Fill(dtexcel);
            }
            conn.Close();
            返回 dtexcel；

        } 

2.第二步，使用大容量复制过程将数据表值插入数据库...

 公共  void  BulkImport(DataTable ExcelDatatable，如果(ExcelDatatable.Rows.Count >   1 )
        {
            尝试
            {
                如果(con.State == ConnectionState.已关闭)
                    con.Open();
                使用(SqlBulkCopy bulkCopy = 新 SqlBulkCopy
                (
                骗子
                SqlBulkCopyOptions.TableLock |
                SqlBulkCopyOptions.FireTriggers |
                SqlBulkCopyOptions.UseInternalTransaction，
                空
                ))
                {
                   
                    
                    bulkCopy.DestinationTableName = TABLENAME;
                    
                     for ( int  i =  0 ; i <  = ExcelDatatable.Columns.Count- 1 ; i ++)
                    {
                        字符串 colname = ExcelDatatable.Columns [i] .ColumnName.ToString();
                       
                        
                            bulkCopy.ColumnMappings.Add(ExcelDatatable.Columns [i] .ColumnName.ToString()，列名)；
                       

                    }

                    bulkCopy.WriteToServer(ExcelDatatable);
                }
                con.Close();
            }
            捕获(例外ee)
            {
            }
        }
    }
} 

此链接将向您展示如何执行此操作

将数据从Excel导入到SQL Server [ ^ ]

尝试一下...

使用Excel提供程序将数据从excel获取到数据集中.
现在使用数据集将数据发布到sql server.

Excel 7的连接字符串

 Provider = Microsoft.ACE.OLEDB.12.0; Data Source = c:\ myFolder \ myExcel2007file.xlsx;扩展属性="Excel 12.0 Xml; HDR = YES"; 

希望这会有所帮助.
欢呼

how can i transfer data from microsoft Excel to datadase by using c# asp.net

解决方案

1.Get data from from Excel file in to data table by using below function..

public static DataTable exceldata(string filePath)
        {
            DataTable dtexcel = new DataTable();
            bool hasHeaders = false;
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn;
            if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
            else
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            //Looping Total Sheet of Xl File
            /*foreach (DataRow schemaRow in schemaTable.Rows)
            {
            }*/
            //Looping a first Sheet of Xl File
            DataRow schemaRow = schemaTable.Rows[0];
            string sheet = schemaRow["TABLE_NAME"].ToString();
            if (!sheet.EndsWith("_"))
            {
                string query = "SELECT  * FROM [" + sheet + "]";
                OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
                dtexcel.Locale = CultureInfo.CurrentCulture;
                daexcel.Fill(dtexcel);
            }
            conn.Close();
            return dtexcel;

        }

2.In second step insert the data table value into data base by using Bulk copy process...

public void BulkImport(DataTable ExcelDatatable, string TABLENAME)
  {

        if (ExcelDatatable.Rows.Count > 1)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                    con.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy
                (
                con,
                SqlBulkCopyOptions.TableLock |
                SqlBulkCopyOptions.FireTriggers |
                SqlBulkCopyOptions.UseInternalTransaction,
                null
                ))
                {
                   
                    
                    bulkCopy.DestinationTableName = TABLENAME;
                    
                    for (int i = 0; i <= ExcelDatatable.Columns.Count - 1; i++)
                    {
                        string colname = ExcelDatatable.Columns[i].ColumnName.ToString();
                       
                        
                            bulkCopy.ColumnMappings.Add(ExcelDatatable.Columns[i].ColumnName.ToString(), colname);
                       

                    }

                    bulkCopy.WriteToServer(ExcelDatatable);
                }
                con.Close();
            }
            catch (Exception ee)
            {
            }
        }
    }
}

This link will show you how to do this

Import Data from Excel to SQL Server[^]

Try this...

Use Excel provider to get data from excel into Dataset.
Now use the Dataset to post data to sql server.

Connection string for excel 7

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

Hope this helps.
cheers

这篇关于Excel到SQL Server 2007的数据传输的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！