求助！急~~~将excel数据导入sql server数据表中

SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\a.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]
上传excel文档到服务器，再打开服务器上excel导入数据到数据库。
遍历excel,实现数据集更新，插入数据到数据库
using(OleDbConnection myConn = new OleDbConnection(""))
  {
  myConn.Open();
  string str = "SELECT * FROM [Sheet1$]";
  OleDbDataAdapter myCommand = new OleDbDataAdapter(str, myConn);
  DataSet ds= new DataSet();
  myCommand.Fill(ds);
  myConn.Close();
  }

百度 google 我都不知道多少回了，都是窗体的，也运行不了啊~~~

与webform和winform没关系..
excel中数据可以用oledb方式读到.
也就是select * from [工作表]读到之后,怎么导入sql,那就是很多方法了.
SqlBulkCopy类就很不错.

好像要用控件啊，前幾天我也發過類似的東西，我把代碼貼出來你看下吧。用的是fileupload控件。
  Dim myapp As New Excel.Application()
            Dim xlsbook1 As Excel.Workbook
            Dim mrbNo As String
            Dim savepath, exname, fileName As String
            mrbNo = MRB_no.Text
            If FileUpload1.HasFile = True Then
                savepath = Server.MapPath("~\upload")
                If (System.IO.Directory.Exists(savepath)) = False Then
                    System.IO.Directory.CreateDirectory(savepath)
                End If
                exname = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower
                fileName = mrbNo + "." + exname
                savepath = savepath + "\\" + fileName
                FileUpload1.SaveAs(savepath)
                '判斷user是否選擇excel文件
                If savepath.ToUpper = "FALSE" Then
                    MsgBox("請選擇要添加的文件")
                    Exit Sub
                End If
                xlsbook1 = myapp.Workbooks.Open(savepath)
                Dim xlssheet1 As Excel.Worksheet
                xlssheet1 = xlsbook1.Sheets("sheet1")
                Dim strSql As String
                Dim i As Integer = 1
                Dim j As Integer
                While (Convert.ToString(xlssheet1.Cells(i, 1).Value)) <> ""
                    i += 1
                End While
                For j = 2 To (i - 1)
                    strSql = "insert into mrb_wip (mrb_no,wip_no,wip_PN,wip_LN,wip_qty,wip_unit,wip_deal_kind,wip_deal_detail) values ( "
                    strSql = strSql + " '" + mrbNo + "',(" + j.ToString + " - 2), '" + getCellValue(xlssheet1.Cells(j, 1).Value) + "' ,'" + getCellValue(xlssheet1.Cells(j, 2).Value) + "', '" + getCellValue(xlssheet1.Cells(j, 3).Value) + "','" + getCellValue(xlssheet1.Cells(j, 4).Value) + "','" + getCellValue(xlssheet1.Cells(j, 5).Value) + "','" + getCellValue(xlssheet1.Cells(j, 6).Value) + "' )  "
                    runSQL(strSql)
                Next
                xlssheet1 = Nothing
                xlsbook1 = Nothing
                myapp.Quit()
                myapp = Nothing

代碼是用VB寫的，其實和C#一樣的，你自己研究下，不要我直接拷貝，我少貼了。看懂邏輯，很簡單的，就是一個控件，用控件獲取文件路徑，然后再用路徑對excel中的數據進行讀取插入到數據庫。getcellvalue是我自己寫的函數，功能是判斷cell中的值，有則轉換成string返回（vb中視object類型的，要轉換），空則返回“”，runsql（）也是函數，什么功能我不說你也能明白的。好了，希望以上的代碼和解說對你能有幫助。臺資企業，繁體漢字，帶來不便，還請見諒

有没有不用控件的，直接读取excel里的数据
DataBase DB = new DataBase();
        DataSet ds = DB.GetDataSet("select 序号,姓名,性别,成绩 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=E:\\xinxi.xls',Sheet1$)");
        DB.Close();  DataTable dt = new DataTable();
        dt = ds.Tables[0];
        string sql = "";
        if (ds.Tables[0].Rows.Count != 0)
        {            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                sql = "insert into xinxi(id,username,sex,cj) values ('" + ds.Tables[0].Rows[i]["序号"].ToString() + "','" + ds.Tables[0].Rows[i]["姓名"].ToString() + "','" + ds.Tables[0].Rows[i]["性别"].ToString() + "','" + ds.Tables[0].Rows[i]["成绩"].ToString() + "')";            Common.get_alert("导入成功！", "window.close();");
        }
请问一下这个有什么问题呢？出错··

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using Microsoft.Office.Interop.Excel;namespace Algorithm
{
    public class TestExcel
    {
        public string _excelPath = "";
        private const string _weekDay = "January";
        private const string _weekDay1 = "2005";
        private const string _weekDay2 = "1991";
        private const string _weekDay3 = "1971";
        private const string _rate = "Rate";
        private const string _pts = "Pts";
        private const string _margin = "Margin";        public static List<string> ErrorList = null;        public TestExcel()
        { }
        public TestExcel(string excelPath)
        {
            this._excelPath = excelPath;
        }
        public void ValidateExcel()
        {
            ErrorList = new List<string> { };        }        public void Sheet1()
        {
            Application app = new Application();
            Workbooks wbs = app.Workbooks;
            _Workbook wb = wbs.Add(_excelPath);
            Sheets shs = wb.Sheets;
            _Worksheet sh = (_Worksheet)shs.get_Item(1);            for (int n = 5; n < 6; n++)
            {
                for (int k = 0; k < 6; k++)
                {
                    Range range1 = sh.get_Range("A8", "X8");
                    Range range2 = sh.get_Range("A6", "X6");
                    Object[,] saRet2 = (Object[,])range1.get_Value(System.Reflection.Missing.Value);
                    Object[,] saRet3 = (Object[,])range2.get_Value(System.Reflection.Missing.Value);
                    if (saRet2[1, 2] == null || !saRet2[1, 2].Equals("Rate"))
                    {
                        break;
                    }
                    if (saRet2[1, 2 + k * 4] != null)
                    {
                        TestExcel.CompareExcel(_weekDay, saRet2[1, 1].ToString(), "B5");
                        TestExcel.CompareExcel(_rate, saRet3[1, 2].ToString(), "B6");
                        TestExcel.CompareExcel(_pts, saRet3[1, 3].ToString(), "C6");
                        TestExcel.CompareExcel(_margin, saRet3[1, 20].ToString(), "T6");
                    }
                }
            }
        }
        public void Sheet2()
        {
            Application app = new Application();
            Workbooks wbs = app.Workbooks;
            _Workbook wb = wbs.Add(_excelPath);
            Sheets shs = wb.Sheets;
            _Worksheet sh = (_Worksheet)shs.get_Item(1);            for (int n = 7; n < 9; n++)
            {
                for (int k = 0; k < 5; k++)
                {
                    Range range1 = sh.get_Range("A7", "T7");
                    Range range2 = sh.get_Range("A8", "T8");
                    Object[,] saRet1 = (Object[,])range1.get_Value(System.Reflection.Missing.Value);
                    Object[,] saRet2 = (Object[,])range2.get_Value(System.Reflection.Missing.Value);
                    if (saRet1[1, 2 + k * 4] == null)
                    {
                        break;
                    }
                    if (saRet1[1, 2 + k * 4] != null)
                    {
                        TestExcel.CompareExcel(_weekDay1, saRet1[1, 2].ToString(), "B7");
                        TestExcel.CompareExcel(_rate, saRet2[1, 2].ToString(), "B8");
                        TestExcel.CompareExcel(_pts, saRet2[1, 3].ToString(), "C8");
                        TestExcel.CompareExcel(_margin, saRet2[1, 4].ToString(), "D8");
                    }
                }
            }
        }
        public void Sheet3()
        {
            Application app = new Application();
            Workbooks wbs = app.Workbooks;
            _Workbook wb = wbs.Add(_excelPath);
            Sheets shs = wb.Sheets;
            _Worksheet sh = (_Worksheet)shs.get_Item(1);            for (int n = 5; n < 7; n++)
            {
                for (int k = 0; k < 7; k++)
                {
                    Range range1 = sh.get_Range("A5", "U5");
                    Range range2 = sh.get_Range("A6", "U6");
                    Object[,] saRet1 = (Object[,])range1.get_Value(System.Reflection.Missing.Value);
                    Object[,] saRet2 = (Object[,])range2.get_Value(System.Reflection.Missing.Value);
                    if (saRet1[1, 2] == null || !saRet1[1, 2].Equals("Rate"))
                    {
                        break;
                    }
                    if (saRet1[1, 2 + k * 3] == null)
                    {
                        break;
                    }
                    if (saRet1[1, 2 + k * 3] != null)
                    {
                        TestExcel.CompareExcel(_weekDay2, saRet1[1, 2].ToString(), "B5");
                        TestExcel.CompareExcel(_rate, saRet2[1, 2].ToString(), "B6");
                        TestExcel.CompareExcel(_pts, saRet2[1, 3].ToString(), "C6");
                    }
                }
            }
        }
        public void Sheet4()
        {
            Application app = new Application();
            Workbooks wbs = app.Workbooks;
            _Workbook wb = wbs.Add(_excelPath);
            Sheets shs = wb.Sheets;
            _Worksheet sh = (_Worksheet)shs.get_Item(1);            for (int n = 5; n < 7; n++)
            {
                for (int k = 0; k < 7; k++)
                {
                    Range range1 = sh.get_Range("A5", "U5");
                    Range range2 = sh.get_Range("A6", "U6");
                    Object[,] saRet1 = (Object[,])range1.get_Value(System.Reflection.Missing.Value);
                    Object[,] saRet2 = (Object[,])range2.get_Value(System.Reflection.Missing.Value);
                    if (saRet1[1, 2] == null || !saRet1[1, 2].Equals("Rate"))
                    {
                        break;
                    }
                    try
                    {
                        Double a = (Double)saRet1[1, 2 + k * 3];
                    }
                    catch (Exception)
                    {
                        break;
                    }
                    if (saRet1[1, 2 + k * 3] != null)
                    {
                        TestExcel.CompareExcel(_weekDay3, saRet1[1, 2].ToString(), "B5");
                        TestExcel.CompareExcel(_rate, saRet2[1, 2].ToString(), "B6");
                        TestExcel.CompareExcel(_pts, saRet2[1, 3].ToString(), "C6");
                    }
                }
            }
        }
        public static void CompareExcel(string str1, string str2, string errorMessage)
        {
            if (!str1.Equals(str2))
            {
                ErrorList.Add("Error:" + str1 + "Message:" + errorMessage);
            }
        }
    }
}
看下这个

我也嘗試過不用控件，但是好像是不行的（至少我沒有實現），我要先把文件上傳到服務器，然后在服務器上讀取數據，從本地請求服務器，服務器在從本地讀數據，好像路徑怎么弄都不行啊，別的辦法我也想了很多，都沒有能實現，只能用控件了。你的代碼么，你要判斷下cell中的數據是不是為空，為空的話對應數據庫中的字段不允許空是不能插入的（我這么理解的），呵呵

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
把這行改下，改成
for (int i = 0; i < ds.Tables[0].Rows.Count-1; i++)

调试易

求助！急~~~将excel数据导入sql server数据表中

解决方案 »