import کردن اطلاعات از اکسل به دیتا بیس SQL با کد نویسی در سی شارپ

Question

سلام:

با کدنویسی در محیط سی شارپ چطور میشه اطلاعات را از اگسل به دیتا بیس SQL وارد کرد؟؟؟

Answer 1

سلام 

اینجا پاسخ داده شده و تست شده

http://www.w3-farsi.com/forum/index.php/5974/%D8%AE%D8%B1%D9%88%D8%AC-%D9%88-%D9%88%D8%B1%D9%88%D8%AF-%D9%81%D8%A7%DB%8C%D9%84-word-%D9%88-excell

 

  try
            {
                 
 
                _Application app = new Microsoft.Office.Interop.Excel.Application();
 
 
               
                _Workbook workbook = app.Workbooks.Add(Type.Missing);
 
 
              
                _Worksheet worksheet = null;
 
             
                app.Visible = true;
                
               
                worksheet = (Worksheet)workbook.Sheets["Sheet1"];
 
                worksheet = (Worksheet)workbook.ActiveSheet;
 
               
                worksheet.Name = "Exported from gridview";
 
             
 
                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
 
                {
 
                    worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
 
                }
 
              
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
 
                {
 
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
 
                    {
 
                        worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
 
                    }
 
                }
 
 
              
          
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Filter = "Excel Document(*.xlsx)|*.xlsx";
                // sfd.FileName = "Export";
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    workbook.SaveAs(sfd.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }
 
                // Exit from the application 
                wait();//threadشروع
                app.Quit();
 
            }
            catch (Exception)
            {
 
                throw;
            }
            finally
            {
               endwait();//هست threadاین
            }

 

Comments

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void insertdata_Click(object sender, EventArgs e)
    {
        OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("example.xls") + ";Extended Properties=Excel 8.0");
        try
        {
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            oconn.Open();
            OleDbDataReader odr = ocmd.ExecuteReader();
            string fname = "";
            string lname = "";
            string mobnum = "";
            string city = "";
            string state = "";
            string zip = "";
            while (odr.Read())
            {
                fname = valid(odr, 0);
                lname = valid(odr, 1);
                mobnum = valid(odr, 2);
                city = valid(odr, 3);
                state = valid(odr, 4);
                zip = valid(odr, 5);
                insertdataintosql(fname, lname, mobnum, city, state, zip);
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
    }
    protected string valid(OleDbDataReader myreader, int stval)//if any columns are found null then they are replaced by zero
    {
        object val = myreader[stval];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString(0);
    }
    protected void viewdata_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
        try
        {
            SqlDataAdapter sda = new SqlDataAdapter("select * from emp", conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
        catch (DataException de)
        {
            lblmsg.Text = de.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Shown Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
    }
    public void insertdataintosql(string fname, string lname, string mobnum, string city, string state, string zip)
    {
        SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "insert into emp(fname,lname,mobnum,city,state,zip) values(@fname,@lname,@mobnum,@city,@state,@zip)";
        cmd.Parameters.Add("@fname", SqlDbType.NVarChar).Value = fname;
        cmd.Parameters.Add("@lname", SqlDbType.NVarChar).Value = lname;
        cmd.Parameters.Add("@mobnum", SqlDbType.NVarChar).Value = mobnum;
        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = city;
        cmd.Parameters.Add("@state", SqlDbType.NVarChar).Value = state;
        cmd.Parameters.Add("@zip", SqlDbType.Int).Value = Convert.ToInt32(zip);
        cmd.CommandType = CommandType.Text;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }
    protected void deletedata_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "delete from emp";
            cmd.CommandType = CommandType.Text;
            conn.Open();
            cmd.ExecuteScalar();
            conn.Close();
        }
        catch (DataException de1)
        {
            lblmsg.Text = de1.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Deleted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
    }

}

 

 

نمونه فایل اکسل

 

دارای دیدگاه Feb 23, 2018 - 15:34:01

توسط Dany (7,164 امتیاز) ●4 ●7 ●141

---

یه پشنهاد برات دارم

 

میتونی اطلاعات رو توی دیتابیس لود کنی بعد از اونجا ذخیرشون کنی

 

            string filePath = string.Empty;
            string fileExt = string.Empty;
            OpenFileDialog file = new OpenFileDialog();//open dialog to choose file
            if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK)//if there is a file choosen by the user
            {
                filePath = file.FileName;//get the path of the file
                fileExt = Path.GetExtension(filePath);//get the file extension
                if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
                {
                    try
                    {
                        System.Data.DataTable dtExcel = new System.Data.DataTable();
                        dtExcel = ReadExcel(filePath, fileExt);//read excel file
                        dataGridView1.Visible = true;
                        dataGridView1.DataSource = dtExcel;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message.ToString());
                    }
                }
                else
                {
                    MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);//custom messageBox to show error
                }
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();//to close the window(Form1)
        }

        public System.Data.DataTable ReadExcel(string fileName, string fileExt)
        {
            string conn = string.Empty;
            System.Data.DataTable dtexcel = new System.Data.DataTable();
            if (fileExt.CompareTo(".xls") == 0)//compare the extension of the file
                conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';";//for below excel 2007
            else
                conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";//for above excel 2007
            using (OleDbConnection con = new OleDbConnection(conn))
            {
                try
                {
                    OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con);//here we read data from sheet1
                    oleAdpt.Fill(dtexcel);//fill excel data into dataTable
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }
            return dtexcel;

 

دارای دیدگاه Feb 23, 2018 - 15:38:44

توسط Dany (7,164 امتیاز) ●4 ●7 ●141