Excel khi import vào sql server ?

chicks · 24-11-2014, 03:51 PM

Hiện tại mình đang làm 1 chương trình import file excel vào csdl (sql server). Mình đã import được rồi ..Nhưng cái vướng mắc của mình là !
làm thế nào để mình có thể lấy dữ liệu từng dòng trong file excel để so sánh với dữ liệu đã có trong sql trước khi import .
Bạn nào biết chỉ giùm mình với

Mình dùng SqlBulkCopy để import :

Code

private void btnImport_Click(object sender, EventArgs e)
{

if (txtUrl.Text.Trim() == "")
{
MessageBox.Show("Bạn chưa chọn file dữ liệu");

}
//Sau khi upload xong= > đọc dữ liệu trong file này
else
{
string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtUrl.Text.Trim() + ";Extended Properties=Excel 12.0 Xml";
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = excelConnectionString;
OleDbCommand command = new OleDbCommand("Select * from [Sheet1$]", connection);
connection.Open();
DbDataReader dr = command.ExecuteReader();
string sqlConnectionString = "Data Source=LMTS;Initial Catalog=TCAMS;Persist Security Info=True;";
SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
bulkInsert.DestinationTableName = "tbl_orgtokenman";
bulkInsert.WriteToServer(dr);
MessageBox.Show("Dữ liệu đã được thêm thành công");

}
}

thanlong_1189 · 24-11-2014, 04:46 PM

Nguyên bản được gửi bởi chicks

Hiện tại mình đang làm 1 chương trình import file excel vào csdl (sql server). Mình đã import được rồi ..Nhưng cái vướng mắc của mình là !
làm thế nào để mình có thể lấy dữ liệu từng dòng trong file excel để so sánh với dữ liệu đã có trong sql trước khi import .
Bạn nào biết chỉ giùm mình với

Mình dùng SqlBulkCopy để import :

Code

Trước mình cũng dùng kiểu này nhưng không triệt để cho lắm. Bạn có thể tham khảo đoạn code sau:
Code nút Import

Code:

private void btnImportExcel_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn có chắc muốn import dữ liệu này?", "Cảnh báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                if (txtFilePath.Text.Trim() == "")
                {
                    MessageBox.Show("Xin vui lòng chọn tập tin excel cần import", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else
                {
                    // Đọc dữ liệu từ tập tin excel trả về DataTable
                    DataTable data = ReadDataFromExcelFile();

                    // Import dữ liệu đọc được vào database
                    ImportIntoDatabase(data);

                    // Lấy hết dữ liệu import từ database hiển thị lên gridView
                    //ShowData();
                }
            }
            else
            {
                
            }
        }

Hàm đọc tập tin Excel

Code:

private DataTable ReadDataFromExcelFile()
        {
            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFilePath.Text.Trim() + ";Extended Properties=Excel 8.0";
            // Tạo đối tượng kết nối
            OleDbConnection oledbConn = new OleDbConnection(connectionString);
            DataTable data = null;
            try
            {
                // Mở kết nối
                oledbConn.Open();

                // Tạo đối tượng OleDBCommand và query data từ sheet có tên "Sheet1"
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);

                // Tạo đối tượng OleDbDataAdapter để thực thi việc query lấy dữ liệu từ tập tin excel
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Tạo đối tượng DataSet để hứng dữ liệu từ tập tin excel
                DataSet ds = new DataSet();

                // Đổ đữ liệu từ tập excel vào DataSet
                oleda.Fill(ds);
                data = ds.Tables[0];//đổ dữ liệu vào Table data
                dgvData.DataSource = data;//hiển thị lên datagridview trước
                oledbConn.Close();
            }
            catch
            {
                MessageBox.Show("Tập tin không đúng! Vui lòng chọn lại tập tin file excel 2003.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return data;
        }

Hàm Import

Code:

int i;

        private void ImportIntoDatabase(DataTable data)
        {
            SqlConnection conn = new SqlConnection(@"Data Source='" + txtserver.Text + "';Initial Catalog='" + txtDb.Text + "';Persist Security Info=True;User ID=sa;Password=123456");
            //Login.ketnoi();
            if (data == null || data.Rows.Count == 0)
            {
                MessageBox.Show("Không có dữ liệu để import", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            int count = 0;
            string j = "";
            //ImportKMTableAdapters.PAC_KhuyenMaiTableAdapter adapter = new ImportKMTableAdapters.PAC_KhuyenMaiTableAdapter();
            DateTime validfrom;
            DateTime validto;
            string ItemCode = "", ItemCodeRelated = "", description = "", description_1 = "";
            int Type = 0, Assortment10 = 0, Division = 0;
            float Quantity = 0;
            //validfrom.ToString("yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
            try
            {

            for (i = 0; i < data.Rows.Count; i++)
            {

                j = "ItemCode";
                ItemCode = data.Rows[i]["ItemCode"].ToString().Trim();
                if (checkitemcode(ItemCode) == true)
                {
                    //Type = int.Parse(data.Rows[i].Cells["Type"].Value.ToString());
                    j = "Type";
                    Type = int.Parse(data.Rows[i]["Type"].ToString().Trim());
                    j = "ItemCodeRelated";
                    ItemCodeRelated = data.Rows[i]["ItemCodeRelated"].ToString().Trim();
                    if (checkitemcode(ItemCodeRelated) == true)
                    {
                        j = "Quantity";
                        Quantity = float.Parse(data.Rows[i]["Quantity"].ToString().Trim());
                        j = "Division";
                        Division = int.Parse(data.Rows[i]["Division"].ToString().Trim());
                        j = "Validfrom";
                        validfrom = Convert.ToDateTime(data.Rows[i]["Validfrom"].ToString().Trim());
                        j = "Validto";
                        //String.Format("{0:MM/dd/yyyy}", validfrom);
                        validto = Convert.ToDateTime(data.Rows[i]["Validto"].ToString().Trim());
                        if (validfrom <= validto)
                        {
                            //String.Format("{0:MM/dd/yyyy}", validto);
                            //validfrom = DateTime.Parse(data.Rows[i]["Validfrom"].ToString().Trim());
                            //validfrom.ToString("yyyy/MM/dd");
                            //validto = DateTime.Parse(data.Rows[i]["Validto"].ToString().Trim());
                            //validto.ToString("yyyy/MM/dd");
                            j = "Assortment10";
                            Assortment10 = int.Parse(data.Rows[i]["Assortment10"].ToString().Trim());

                            //createddate = Convert.ToDateTime(data.Rows[i]["Createddate"].ToString().Trim());
                            j = "Description";
                            description = data.Rows[i]["Description"].ToString().Trim();
                            j = "Description_1";
                            description_1 = data.Rows[i]["Description_1"].ToString().Trim();
                            if (checkimport(ItemCode, ItemCodeRelated, Type, validfrom, validto) == false)
                            {
                                count++;

                                //string sql = @"INSERT INTO PAC_KhuyenMai  (ItemCode ,Type ,ItemCodeRelated,Quantity ,Division,validfrom,validto,Assortment10,description,createddate) VALUES ('" + ItemCode + "','" + Type + "','" + ItemCodeRelated + "','" + Quantity + "','" + Division + "','" + validfrom + "','" + validto + "','" + Assortment10 + "','" + description + "',getdate())";
                                string sql = @"INSERT INTO PAC_KhuyenMai  (ItemCode ,Type ,ItemCodeRelated,Quantity ,Division,validfrom,validto,Assortment10,[description],[description_1],createddate,modifieddate) VALUES (@ItemCode ,@Type ,@ItemCodeRelated,@Quantity ,@Division,@validfrom,@validto,@Assortment10,N'" + @description + "',N'" + @description_1 + "',getdate(),getdate())";

                                SqlCommand cmd = new SqlCommand(sql, conn);
                                DataTable dt = new DataTable();
                                conn.Open();
                                cmd.Parameters.Add("@ItemCode", SqlDbType.NVarChar).Value = ItemCode;
                                cmd.Parameters.Add("@Type", SqlDbType.Int).Value = Type;
                                cmd.Parameters.Add("@ItemCodeRelated", SqlDbType.NVarChar).Value = ItemCodeRelated;
                                cmd.Parameters.Add("@Quantity", SqlDbType.Float).Value = Quantity;
                                cmd.Parameters.Add("@Division", SqlDbType.Int).Value = Division;
                                cmd.Parameters.Add("@validfrom", SqlDbType.DateTime).Value = validfrom;
                                cmd.Parameters.Add("@validto", SqlDbType.DateTime).Value = validto;
                                cmd.Parameters.Add("@Assortment10", SqlDbType.Int).Value = Assortment10;
                                cmd.Parameters.Add("@description", SqlDbType.Text).Value = description;
                                cmd.Parameters.Add("@description_1", SqlDbType.Text).Value = description_1;
                                //cmd.Parameters.Add("@createddate", SqlDbType.Date).Value = HolidayStart;

                                cmd.ExecuteNonQuery();
                                conn.Close();
                                //count = 1;

                                //count = data.Rows.Count;
                                //da.Fill(dt);
                                //dgvData.DataSource = dt;
                                //conn.Close();
                                //da.Dispose();//m muon insert 1 dong hien thi luon hay xong 1 excel mơi hien thixong moi hien thi chu
                            }
                            else
                            {
                                continue;
                            }
                        }

                        else
                        {
                            MessageBox.Show("Lỗi dòng " + (i + 1) + ", cột " + j + ". Xem lại dữ liệu ngày tháng.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                            break;
                        }
                    }
                    else
                    {
                        MessageBox.Show("Lỗi dòng " + (i + 1) + ", cột " + j + ".  Mã ItemCodeRelated: '" + ItemCodeRelated + "' không tồn tại trong data.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        break;
                    }
                }
                else
                {
                    MessageBox.Show("Lỗi dòng " + (i + 1) + ", cột " + j + ". Mã Itemcode: '" + ItemCode + "' không tồn tại trong data.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    break;
                }

            }//for

            MessageBox.Show("Import thành công '" + count + "' dòng.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            catch
            {
                i += 1;
                MessageBox.Show("Có 1 lỗi ở file excel, dòng " + i + ", cột " + j + ". Vui lòng xem lại định dạng kiểu dữ liệu.", "Thông báo");
            }

            MessageBox.Show("Kết thúc import", "Thông báo");
            ShowData();
        }

trong này có 1 vài hàm kiểm tra, vd như chỗ

Code:

if (checkitemcode(ItemCodeRelated) == true)
                    {

hoặc chỗ

Code:

if (checkitemcode(ItemCodeRelated) == true)
                    {

thì hàm kiểm tra như sau

Code:

public bool checkitemcode(string _ItemCode)
        {
            SqlConnection conn = new SqlConnection(@"Data Source='" + txtserver.Text + "';Initial Catalog='" + txtDb.Text + "';Persist Security Info=True;User ID=sa;Password=123456");
            string qur = "SELECT itemcode from items where itemcode='" + _ItemCode + "'";
            SqlDataAdapter _da = new SqlDataAdapter(qur, conn);
            DataTable dt = new DataTable();
            _da.Fill(dt);
            if (dt.Rows.Count > 0)
                return true;
            return false;
        }

hoặc chỗ

Code:

if (checkimport(ItemCode, ItemCodeRelated, Type, validfrom, validto) == false)
                            {

thì hàm của nó là:

Code:

public bool checkimport(string _ItemCode, string _ItemCodeRelated, int _Type, DateTime _validfrom, DateTime _validto)
        {
            SqlConnection conn = new SqlConnection(@"Data Source='" + txtserver.Text + "';Initial Catalog='" + txtDb.Text + "';Persist Security Info=True;User ID=sa;Password=123456");
            string qur = @"SELECT * from pac_khuyenmai where itemcode=@itemcode 
                                        and ItemCodeRelated=@ItemCodeRelated 
                                        and [Type]=@Type and validfrom=@validfrom and validto=@validto";
            SqlCommand cmd = new SqlCommand(qur, conn);

            DataTable dt = new DataTable();
            conn.Open();
            SqlDataAdapter _da = new SqlDataAdapter(cmd);
            cmd.Parameters.Add("@ItemCode", SqlDbType.NVarChar).Value = _ItemCode;
            cmd.Parameters.Add("@Type", SqlDbType.Int).Value = _Type;
            cmd.Parameters.Add("@ItemCodeRelated", SqlDbType.NVarChar).Value = _ItemCodeRelated;
            //cmd.Parameters.Add("@Quantity", SqlDbType.Float).Value = Quantity;
            //cmd.Parameters.Add("@Division", SqlDbType.Int).Value = Division;
            cmd.Parameters.Add("@validfrom", SqlDbType.DateTime).Value = _validfrom;
            cmd.Parameters.Add("@validto", SqlDbType.DateTime).Value = _validto;
            //cmd.Parameters.Add("@Assortment10", SqlDbType.Int).Value = Assortment10;
            //cmd.Parameters.Add("@description", SqlDbType.Text).Value = description;
            cmd.ExecuteNonQuery();

            _da.Fill(dt);
            conn.Close();
            if (dt.Rows.Count > 0)
                return true;
            return false;


        }

Hàm Showdata()

Code:

 private void ShowData()
        {
            try
            {
                SqlConnection conn = new SqlConnection(@"Data Source='" + txtserver.Text + "';Initial Catalog='" + txtDb.Text + "';Persist Security Info=True;User ID=sa;Password=123456");
                string sql = "select * from pac_khuyenmai";
                SqlCommand cmd = new SqlCommand(sql, conn);
                DataTable dt = new DataTable();
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                dgvData.DataSource = dt;
                conn.Close();
            }
            catch
            {
                MessageBox.Show("Server,database hoặc tài khoản không tồn tại!", "Thông báo");
            }
        }

chicks · 25-11-2014, 10:08 AM

Thanks bạn !! mình sẽ thử dùng các của bạn xem thế nào !!
Bạn cho mình hỏi lun nhé .. Lấy ví dụ trong đoạn code của bạn nhé

Ví dụ : Trong file excel với sql đều có các trường : ItemCode,Type,ItemCodeRelated
Bây giờ trong file excel mình chỉ cần nhập Trường ItemCode (các trường khác sẽ k nhập bằng tay) .. và lấy 3 ký tự đầu của nó để kiểm tra nếu 3 ký tự đầu (u11) trc khi import thì Type gán =2 chẳng hạn rồi ms import !

thanlong_1189 · 25-11-2014, 10:28 AM

ý bạn là chỗ này ý hả

Code:

if (checkitemcode(ItemCodeRelated) == true)
                    {

nếu thế thì bạn thử:

Code:

int Type =2;
if (checkitemcode(ItemCodeRelated) == true)
   { 
     data.Rows[i]["Type"].Value = Type;
    }

chicks · 25-11-2014, 04:09 PM

Nguyên bản được gửi bởi thanlong_1189

ý bạn là chỗ này ý hả

Code:

if (checkitemcode(ItemCodeRelated) == true)
                    {

nếu thế thì bạn thử:

Code:

int Type =2;
if (checkitemcode(ItemCodeRelated) == true)
   { 
     data.Rows[i]["Type"].Value = Type;
    }

Ở đây mình vẫn chưa thấy nếu 3 ký tự đầu của ô ItemCode vd (u111) thì Type =2 .. mà ở đây chỉ là kiểm tra nếu ItemCode (excel) = ItemCode (sql)....thôi

thanlong_1189 · 25-11-2014, 04:35 PM

Trong excel cũng có lệnh lấy 3 kí tự đầu được mà bạn, sao bạn không làm trong Excel đi rồi hãy Import vào

chicks · 25-11-2014, 09:23 PM

Nguyên bản được gửi bởi thanlong_1189

Trong excel cũng có lệnh lấy 3 kí tự đầu được mà bạn, sao bạn không làm trong Excel đi rồi hãy Import vào

Ừ nhỉ .. mình cũng đảng trí quá .. cảm ơn bạn nhiều nhé.. có code nào ở trên mình không hiểu mình xin hỏi thêm nhé !! mong bạn giúp đỡ thêm

chicks · 17-12-2014, 02:40 PM

Bạn ơi mình code theo cách của bạn mà chẳng hạn mình đặt ItemCode là Primary key mà khi mình insert 1 IntemCode đã có trong CSDL thì mình sẽ xử lý như thế nào được mình đang rối đoạn này ! :(

thanlong_1189 · 18-12-2014, 09:34 AM

Theo mình thì bạn nên bỏ qua nó

hoặc là thông bảo lỗi ở dòng nào để còn biết mà xóa cái Itemcode đó đi khỏi Excel

hoangthi · 24-12-2014, 12:09 AM

Hi all!

Mình thấy bạn chicks dùng SqlBulkCopy ngay từ ban đầu là bạn đúng hướng rồi đó. nhưng vấn đề là cách xử lý kỹ thuật đoạn này thôi. Làm như bạn than_long cũng được nhưng mà mình đọc cũng thấy chán chứ sau này mà sửa nữa thì chắc cận thêm, hihi. Bạn Chicks thay vì bạn insert vào bảng gốc luôn thì bạn insert vào một bảng tạm khác có cùng cấu trúc với bảng bạn cần đưa dữ liệu. Sau khi import xong, bạn viết 1 Store Procedure nho nhỏ lấy ra dòng nào có trong bảng tạm mà không có trong bảng chính rồi insert, tương tự sẽ dễ dàng tìm ra dòng nào trùng và bạn có thể update tùy ý. Xử lý bằng code C# mình khẳng định là không thể nào nhanh hơn cách này. Chúc 2 bạn tìm được cảm hứng làm thử, bye

Đề tài: Excel khi import vào sql server ?

Các công cụ đề tài

Display

Excel khi import vào sql server ?

Excel khi import vào sql server ?

Quyền hạn của bạn