asp.net中Excel的数据导入

Question

怎样将execl数据导入到数据库Sqlserver里面，我关键是要思路， 代码也可以，请教各位大侠了。小弟先谢谢了！

Answer 1

依次遍历读取excel中的数据，然后依次调用sql，插入到sql server中。

Answer 2

这是从excel读取数据保存在datatable中 
public static DataTable CreateExcelDataSource(string url)
        {
            
            DataTable dt = null;
            string connetionStr = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + url + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";
            string strSql = "select * from [excel文件名$]";
            OleDbConnection oleConn = new OleDbConnection(connetionStr);
            OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strSql, connetionStr);
            try
            {
                dt = new DataTable(); 
                oleAdapter.Fill(dt);                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                oleAdapter.Dispose();
                oleConn.Close();
                oleConn.Dispose();
            }
        }       

Answer 3

这里是用一个工具栏按钮（打开excel文件）获取要导入的excel文件的路径，用到了openFileDialog控件
private void toolStripButton1_Click(object sender, EventArgs e)
            {
                openFileDialog1.ShowDialog();               
                openFileDialog1.Filter ="excel文件(*.xls)|*.xls|所有文件（*.*）|*.*";
                url = openFileDialog1.FileName;
                //textBox1.Text = url;
            }

Answer 4

这里又是一个工具栏按钮（执行导入excel文件），批量导入
private void toolStripButton2_Click(object sender, EventArgs e)
            {
                dataGridView1.DataSource = CreateExcelDataSource(url);
                string conString = "data source=.;initial catalog=数据库名;integrated security=true";        //调用Excel转Sql方法 
                string sql = "truncate table 数据库表名";
                SqlConnection con = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand(sql, con);
                if (con.State == ConnectionState.Closed)
                con.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
                DataTable dt = CreateExcelDataSource(url);        //创建批量DataTable导入Sql        
                SqlBulkCopy copy = new SqlBulkCopy(conString, SqlBulkCopyOptions.UseInternalTransaction);        //指定数据库表名        
                copy.DestinationTableName = "数据库表名";        //写入Sql        
                copy.WriteToServer(dt);  
            }

Answer 5

http://blog.csdn.net/taomanman/article/details/6989076

Answer 6

顺便给你将数据库的数据导出到excel，前段时间做过所以有代码，不过我是做的winform，该怎么改，你看着办吧
private void toolStripButton4_Click(object sender, EventArgs e)
            {
                DataTable dt = new DataTable();
                dt=shujuchuli.gethuizongxinxi().Tables[0];
                //printAll(dt);
               // MessageBox.Show("数据导出成功！");
                Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
                SaveFileDialog savefiledialog = new SaveFileDialog();
                System.Reflection.Missing miss = System.Reflection.Missing.Value;
                appexcel = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook workbookdata;
                Microsoft.Office.Interop.Excel.Worksheet worksheetdata;
                Microsoft.Office.Interop.Excel.Range rangedata;
                //设置对象不可见
                appexcel.Visible = false;
                System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");
                workbookdata = appexcel.Workbooks.Add(miss);
                worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);
                //给工作表赋名称
                worksheetdata.Name = "saved";
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheetdata.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
                }
                //因为第一行已经写了表头，所以所有数据都应该从a2开始
                rangedata = worksheetdata.get_Range("a2", miss);
                Microsoft.Office.Interop.Excel.Range xlrang = null;
                //irowcount为实际行数，最大行
                int irowcount = dt.Rows.Count;
                int iparstedrow = 0, icurrsize = 0;
                //ieachsize为每次写行的数值，可以自己设置
                int ieachsize = 10000;
                //icolumnaccount为实际列数，最大列数
                int icolumnaccount = dt.Columns.Count;
                //在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数
                object[,] objval = new object[ieachsize, icolumnaccount];
                icurrsize = ieachsize;
                while (iparstedrow < irowcount)
                {
                    if ((irowcount - iparstedrow) < ieachsize)
                        icurrsize = irowcount - iparstedrow;
                    //用for循环给数组赋值
                    for (int i = 0; i < icurrsize; i++)
                    {
                        for (int j = 0; j < icolumnaccount; j++)
                            objval[i, j] = dt.Rows[i + iparstedrow][j].ToString();
                        System.Windows.Forms.Application.DoEvents();
                    }
                    string X = "A" + ((int)(iparstedrow + 2)).ToString();
                    string col = "";
                    if (icolumnaccount <= 26)
                    {
                        col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
                    }
                    else
                    {
                        col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
                    }
                    xlrang = worksheetdata.get_Range(X, col);
                    // 调用range的value2属性，把内存中的值赋给excel
                    xlrang.Value2 = objval;
                    iparstedrow = iparstedrow + icurrsize;
                }
                //保存工作表
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlrang);
                xlrang = null;
                //调用方法关闭excel进程
                appexcel.Visible = true;
            }
          

Answer 7

http://space.itpub.net/16436858/viewspace-545029

Answer 8

     DataSet _Table = GetTableName(Server.MapPath(@"upload/" + Request.Cookies["sa"].Value + "/" + this.ListBox1.SelectedValue), this.DropDownList1.SelectedValue); //首先获取EXCEL文件路径
                string[] list = Request["checkbox"].Split(",".ToCharArray()); 要导入的列，代码不贴了
                string CompanyName = string.Empty, Person_in_charge = string.Empty, Mobile = string.Empty, Phone = string.Empty, Fax = string.Empty, E_mail = string.Empty, Address = string.Empty, IMQQ = string.Empty, AliWW = string.Empty, Website = string.Empty;
                if (_Table.Tables[0].Rows.Count > 0)
                { 
                    for (int i = 0; i != _Table.Tables[0].Rows.Count; i++)/
                    {
                        for (int j = 0; j < list.Length; j++)/判断每个列的数据，然后处理
                        {
                            string[] arr = list[j].ToString().Split("|".ToCharArray());
                            if (Request[list[j].ToString()] == "CompanyName")
                            {
                                CompanyName = CompanyName + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
                            }
                            if (Request[list[j].ToString()] == "Person_in_charge")
                            {
                                Person_in_charge = Person_in_charge + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
                            }
                            if (Request[list[j].ToString()] == "Mobile")
                            {
                                Mobile = Mobile + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
                            }
                            if (Request[list[j].ToString()] == "Phone")
                            {
                                Phone = Phone + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
                            }
                            if (Request[list[j].ToString()] == "Fax")
                            {
                                Fax = Fax + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
                            }
                            if (Request[list[j].ToString()] == "E-mail")
                            {
                                E_mail = E_mail + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
                            }
                            if (Request[list[j].ToString()] == "Address")
                            {
                                Address = Address + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
                            }
                            if (Request[list[j].ToString()] == "IMQQ")
                            {
                                IMQQ = IMQQ + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
                            }
                            if (Request[list[j].ToString()] == "AliWW")
                            {
                                AliWW = AliWW + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
                            }
                            if (Request[list[j].ToString()] == "Website")
                            {
                                Website = Website + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
                            }
                        }
                        db.Update_Sql("INSERT INTO Customers([sid],[s_dep],[CompanyName],[Person_in_charge],[Mobile],[Phone],[Fax],[E-mail],[Address],[IMQQ],[AliWW],[Website],[Industry])VALUES('" + Request.Cookies["sa"].Value + "'," + Request.Cookies["dep"].Value + ",'" + CompanyName + "','" + Person_in_charge + "','" + Mobile + "','" + Phone + "','" + Fax + "','" + E_mail + "','" + Address + "','" + IMQQ + "','" + AliWW + "','" + Website + "'," + this.DropDownList2.SelectedValue + ")");
                        CompanyName = ""; Person_in_charge = ""; Mobile = ""; Phone = ""; Fax = ""; E_mail = ""; Address = ""; IMQQ = ""; AliWW = "";
                        this.Label3.Text = "导入成功！";