100分求读取EXCEL的完整方法.

Question

要把EXCEL数据导入SQLSERVER.但EXCEL数据是不规则的,不能直接插入数据库,要先在前台分析/处理后,才生成相应INSERT语句.请问前台怎样得到,比如:A5,B3单元格的值?本人.NET水平烂,所以需要贴完整代码.

Answer 1

http://sourceforge.net/projects/smartexcel/

Answer 2

String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + Server.MapPath("ExcelData.xls") + ";" +
                "Extended Properties=Excel 8.0;";            OleDbConnection objConn = new OleDbConnection(connString);
            
            try
            {
                objConn.Open();                OleDbCommand objCommSelect = new OleDbCommand("select * from MyRange", objConn);                OleDbDataAdapter objAdapter = new OleDbDataAdapter();                objAdapter.SelectCommand = objCommSelect;                DataSet ds = new DataSet();                objAdapter.Fill(ds, "XLData");                DataGrid1.DataSource = ds.Tables[0].DefaultView;
                DataGrid1.DataBind();
            }
            catch(Exception ex)
            {
                System.Console.WriteLine(ex.ToString());
            }
            finally
            {
                objConn.Close();
            }

Answer 3

http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_wrcore/html/wroriautomatingexcelusingexcelobjectmodel.asp

Answer 4

Private Function mysub(ByVal strname As String) As String
        '''''''''intoexcel
        mysub = ""
        Dim myOleDbConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source='" & strname & "';" & _
        "Extended Properties=""Excel 8.0;""")
        Dim myOleDbCommand As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", myOleDbConnection)
        '"如果你想读出Sheet2的内容
        '，把Sheet1$改成Sheet2$即可
        Dim myData As OleDbDataAdapter = New OleDbDataAdapter(myOleDbCommand)
        Dim myDataset As New DataSet
        myData.Fill(myDataset)        Dim sqltempstr As String
        Dim myconn As New SqlConnection
        Dim mycomm As New SqlCommand
        Dim mytrans As SqlTransaction
        myconn.ConnectionString = ConfigurationSettings.AppSettings("sqlconn")        Dim errmsg As String = ""
        Dim tempsql As String = ""
        Dim i, j As Int16
        For i = 0 To myDataset.Tables(0).Rows.Count - 1
            If checkprod_id(IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(2)), "", myDataset.Tables(0).Rows(i).Item(2))) = False Or checkmachine(IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(1)), "", myDataset.Tables(0).Rows(i).Item(1))) = False Then
                j = i + 2
                errmsg &= "第" & j.ToString & "有错！"
                GoTo lable
            End If
            tempsql = "insert into tcq_sc_outputFXS_infor " & _
               "values('" & Me.lblScrq.Text.Trim & "'," & _
               "'" & lblBBdm.Text.Trim() & "'," & _
               "'" & lblBCdm.Text.Trim() & "'," & _
               "'" & myDataset.Tables(0).Rows(i).Item(0) & "'," & _
               "'" & IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(1)), 0, myDataset.Tables(0).Rows(i).Item(1)) & "'," & _
               "'" & IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(2)), 0, myDataset.Tables(0).Rows(i).Item(2)) & "'," & _
               IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(4)), 0, myDataset.Tables(0).Rows(i).Item(4)) & "," & _
               IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(5)), 0, myDataset.Tables(0).Rows(i).Item(5)) & "," & _
               IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(6)), 0, myDataset.Tables(0).Rows(i).Item(6)) & "," & _
               IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(7)), 0, myDataset.Tables(0).Rows(i).Item(7)) & "," & _
               IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(8)), 0, myDataset.Tables(0).Rows(i).Item(8)) & "," & _
               IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(9)), 0, myDataset.Tables(0).Rows(i).Item(9)) & "," & _
               IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(10)), 0, myDataset.Tables(0).Rows(i).Item(10)) & "," & _
               IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(11)), 0, myDataset.Tables(0).Rows(i).Item(11)) & "," & _
               IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(12)), 0, myDataset.Tables(0).Rows(i).Item(12)) & "," & _
               IIf(IsDBNull(myDataset.Tables(0).Rows(i).Item(13)), 0, myDataset.Tables(0).Rows(i).Item(13)) & ")"
            sqltempstr = tempsql
            If myconn.State <> ConnectionState.Closed Then myconn.Close()
            myconn.Open()
            mytrans = myconn.BeginTransaction
            mycomm.Connection = myconn
            mycomm.Transaction = mytrans
            Try
                mycomm.CommandText = sqltempstr
                mycomm.ExecuteNonQuery()
                mytrans.Commit()
            Catch ex As Exception
                errmsg &= "第" & i.ToString & "行出错了！"
            End Try
lable:
        Next
        mycomm = Nothing
        mytrans = Nothing
        If myconn.State <> ConnectionState.Closed Then myconn.Close()
        myconn = Nothing
        If errmsg <> "" Then
            Response.Write("<script javascript>window.alert(" & errmsg.Trim & ");</script>")
        End If
        myDataset.Clear()
        myDataset = Nothing
        myData.Dispose()
        myData = Nothing
        myOleDbCommand.Dispose()
        myOleDbCommand = Nothing
        If myOleDbConnection.State = ConnectionState.Open Then myOleDbConnection.Close()
        myOleDbConnection = Nothing
        mysub = errmsg
    End Function

Answer 5

http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_wrcore/html/wroriautomatingexcelusingexcelobjectmodel.asp

Answer 6

DataSet ds = new DataSet();
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source="+ Server.MapPath(filePath).ToString() + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; 
OleDbConnection oleConn = new OleDbConnection(strConn);
if (oleConn.State == ConnectionState.Closed)
{
oleConn.Open();
}
DataTable schemaTable = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
string tableName=schemaTable.Rows[0][2].ToString;
string strSql= "Select * From ["+ tableName +"]"; OleDbCommand oleComm = new OleDbCommand(strSql,oleConn);
OleDbDataAdapter oleDA = new OleDbDataAdapter();
oleDA.SelectCommand = oleComm;

oleDA.Fill(ds);
if (oleConn.State == ConnectionState.Open)
{
oleConn.Close();
}