使用C＃从Excel导入数据到SQL [英] Import Data to Sql From Excel Using C#

问题描述

嗨



我想将数据从Excel导入到Sql Server，我面临的问题我不想从Excel插入Id列我使用OrderBy存储程序要插入ID但是我没有这样做



这里的代码

Hi

I want to Import data From Excel To Sql Server , The Problem I face that I dont Want to Insert Id Column From Excel I use the OrderBy Stored Procedure To Insert Id But I Unbale to do that

Here The Code

//Upload and save the file
string excelPath = Server.MapPath("~/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);

string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
    case ".xls": //Excel 97-03
        conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
        break;
    case ".xlsx": //Excel 07 or higher
        conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
        break;

}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
    excel_con.Open();
    string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
    DataTable dtExcelData = new DataTable();

    //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
    dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(Int64)),
        new DataColumn("Name", typeof(string)),
        new DataColumn("Summary",typeof(string)) });

    using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
    {
        oda.Fill(dtExcelData);
    }
    excel_con.Close();

    string consString = ConfigurationManager.ConnectionStrings["Practice_One"].ConnectionString;
    using (SqlConnection con = new SqlConnection(consString))
    {
        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
        {
            //Set the database table name
            sqlBulkCopy.DestinationTableName = "dbo.Practice_One";

            //[OPTIONAL]: Map the Excel columns with that of the database table
            sqlBulkCopy.ColumnMappings.Add("Id", "Id");
            Practice_One One = new Practice_One();
            One.Order_By_Practice_One(ref consString, out Id);
            Id = Convert.ToInt64(Id + 1);
            sqlBulkCopy.ColumnMappings.Add("Name", "Name");
            sqlBulkCopy.ColumnMappings.Add("Summary", "Summary");
            con.Open();
            sqlBulkCopy.WriteToServer(dtExcelData);
            con.Close();
        }
    }
}

}

我想使用此类检查最后一个ID和根据那个增加

I want To use this Class to Check last Id and Increment according to that

Practice_One One = new Practice_One();
One.Order_By_Practice_One(ref consString, out Id);
Id = Convert.ToInt64(Id + 1);

推荐答案

这是代码 -

Here is the code -

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.IO;
using System.Data;

namespace _997998_Import_Data_to_Sql_Using_Excel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Upload and save the file
            string ExcelPath = "test.xls";
            string conString = string.Empty;
            //string extension = Path.GetExtension(ExcelPath);

            conString = String.Concat("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" , ExcelPath.ToString() , ";Extended Properties='Excel 12.0 Xml;HDR=YES';");
            using (OleDbConnection excel_con = new OleDbConnection(conString))
            {
                excel_con.Open();
                string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                DataTable dtExcelData = new DataTable();

                using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
                {
                    oda.Fill(dtExcelData);
                }
                excel_con.Close();

                Connect();

                string sql1;
                    foreach (DataRow row in dtExcelData.Rows)
                    {
                        sql1 = String.Concat("INSERT INTO Practice_one SELECT (SELECT Isnull(MAX(Id),0) FROM Practice_one) + 1 ,'", row[1].ToString(), "','", row[2].ToString() , "'" );
                        OleDbCommand insertCommand1 = new OleDbCommand(sql1, Conn);
                        int count = insertCommand1.ExecuteNonQuery();
                    }
                
            }


        }
        public static OleDbConnection Conn = new OleDbConnection();

        public static bool Connect()
        {

            string conn = "PROVIDER=SQLOLEDB;Data Source=RAHUL-PC;Initial Catalog=Test;UserId=sa;Password=sa123;Integrated Security=SSPI";
            
            if (Conn.State == System.Data.ConnectionState.Open)
                Conn.Close();
            Conn.ConnectionString = conn;
            Conn.Open();
            return true;

        }
    }

}

这篇关于使用C＃从Excel导入数据到SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！