急求Excel问题

[wym3587]

asp.net 如何批量将Excel导入到数据库中


批量Excel就是一个文件夹中有N个Excel文件

然后把这个文件夹所有的Excel文件的内容导入到SQL中

谢谢大家。。。。

[wym3587]

最好把源码写上。。。

[理不完的逻辑]

string[] strs = Directory.GetFileSystemEntries(m_filePath, "*.xls");

//循环文件夹中的 excel 文件
foreach (string s in strs)
{
//.........
insert into A from Sheet
}

[wym3587]

问题已解决，谢谢大家，辛苦了。。。

[酷儿]

就是你一个普通的导入excel然后 去循环文件夹下的所有excel一一导入 最好加个事务 只要有一个导入不成功 则进行回滚 所以的都不执行

[wym3587]

[Quote=引用 3 楼 longlong881129 的回复:]
就是你一个普通的导入excel然后 去循环文件夹下的所有excel一一导入 最好加个事务 只要有一个导入不成功 则进行回滚 所以的都不执行
[/Quote]

有源码不？没有用过事务。。。

[wym3587]

[Quote=引用 2 楼 ywp258 的回复:]
string[] strs = Directory.GetFileSystemEntries(m_filePath, "*.xls");

//循环文件夹中的 excel 文件
foreach (string s in strs)
{
//.........
insert into A from Sheet
}
[/Quote]
能写全吗？看不懂。。

[myhope88]

遍历一下所有的excel文件呀，然后依次读取进行操作就行了呀

[小土bibox]

  /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }

动软自动生成的事务。自己编译sql语句就好了。编好自己传进来

[Cosmo]

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strpath & "; Extended Properties=Excel 8.0;"
Dim oleDbConnection As New System.Data.OleDb.OleDbConnection(sConnectionString)
Try
oleDbConnection.Open()
conn.Open()
Dim sheetdataTable As DataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim tableName As String = sheetdataTable.Rows(3)(2).ToString.Trim()
tableName = "[" & tableName.Replace(" ' ", " ") & "]"


Dim query As String = "select Country,Comp_ID,BankName,Facility,Role,ParticipationAmt,Curr,Amount,CurrAlt,AmountAlt,IssueDate,MaturityDate,CloseOutDate,ContNo,TranType,BUnit,TMISBankCode from" & tableName
Dim exceldataset As DataSet = New DataSet()
Dim exceloleAdapter As OleDbDataAdapter = New OleDbDataAdapter(query, sConnectionString)
exceloleAdapter.Fill(exceldataset, "intUploadFacility")

Dim dataTable As DataTable = New DataTable()
Dim sqlDA1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("select UploadFacilityID,UserID,Facility,Curr,TotalAmount,MaturityDate,IssueDate,Curr_Alt,TotalAmount_Alt,Comp_ID,Role,ParticipationAmt,IntStatus,ContNo from intUploadFacility", conn)
Dim sqlCB1 As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(sqlDA1)
sqlDA1.Fill(dataTable)

Dim exceldataRow As DataRow
Dim UploadFacilityID As Int32 = 0
For Each exceldataRow In exceldataset.Tables("intUploadFacility").Rows
Dim dataRow As DataRow = dataTable.NewRow()
UploadFacilityID = UploadFacilityID + 1
dataRow("UploadFacilityID") = UploadFacilityID

dataRow("UserID") = Me.UserID
dataRow("Facility") = exceldataRow("Facility")
dataRow("Curr") = exceldataRow("Curr")
dataRow("TotalAmount") = exceldataRow("Amount")
dataRow("MaturityDate") = exceldataRow("MaturityDate")
dataRow("IssueDate") = exceldataRow("IssueDate")

If Not exceldataRow("CurrAlt") Is DBNull.Value Then
dataRow("Curr_Alt") = exceldataRow("CurrAlt")
Else
dataRow("Curr_Alt") = ""
End If


If Not exceldataRow("AmountAlt") Is DBNull.Value Then
dataRow("TotalAmount_Alt") = Convert.ToDouble(exceldataRow("AmountAlt"))
Else
dataRow("TotalAmount_Alt") = 0
End If

If Not exceldataRow("Comp_ID") Is DBNull.Value Then
dataRow("Comp_ID") = exceldataRow("Comp_ID")
Else
dataRow("Comp_ID") = 0
End If

dataRow("Role") = exceldataRow("Role")
' Dim particpationAmt As String = Convert.ToString(dataRow11("ParticipationAmt") + "")

If Not exceldataRow("ParticipationAmt") Is DBNull.Value Then
dataRow("ParticipationAmt") = Convert.ToDouble(exceldataRow("ParticipationAmt"))
Else
dataRow("ParticipationAmt") = 0
End If
dataRow("IntStatus") = ""
dataRow("ContNo") = exceldataRow("ContNo")
dataTable.Rows.Add(dataRow)
Next


' conn.State.ToString()

sqlDA1.Update(dataTable)

[YnSky]

没弄过.不过一般SQL自带的导入就不错!