将数据从Excel文件导出到SQL Server数据库的最佳方法

I have an Excel file which is already open. I want to write C# code that can export the data of that file to an SQL Server database table without giving the path of that opened file.

我有一个已打开的Excel文件。我想编写C＃代码，可以将该文件的数据导出到SQL Server数据库表，而不提供该打开文件的路径。

Excel.Application sourceApp;
sourceApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
sourceApp.Visible = true;
Excel.Workbook sourceWorkBook = sourceApp.ActiveWorkbook;

Excel.Worksheet sourceWorkSheet = sourceWorkBook.ActiveSheet;

Excel.Range sourceRange = sourceWorkSheet.UsedRange;

This is the code I am using for an open Excel file.

这是我用于打开Excel文件的代码。

How to connect it to the database and an appropriate way to import it?

如何将其连接到数据库并以适当的方式导入它？

1 个解决方案

#1

It really depends on many factors, however you can create a Datatable, map the values to a SQL table and then bulk insert the Datatable into the table. Below there's a generic snippet of this code.

这实际上取决于许多因素，但是您可以创建数据表，将值映射到SQL表，然后将数据表批量插入表中。下面是这段代码的通用代码段。

public static void insertToDB(List<YourModel> list)
    {

        DataTable table = new DataTable("table");

        DataColumn colA = new DataColumn("Column_A", Type.GetType("System.String"));
        table.Columns.Add(colA);
        DataColumn colB = new DataColumn("Column_B", Type.GetType("System.DateTime"));
        table.Columns.Add(colB);
        DataColumn colC = new DataColumn("Column_C", Type.GetType("System.TimeSpan"));
        table.Columns.Add(colC);
        DataColumn colD = new DataColumn("Column_D", Type.GetType("System.Int32"));
        table.Columns.Add(colD);

        foreach (var item in list)
        {
            DataRow newRow = table.NewRow();
            newRow["Column_A"] = item.colA == null ? DBNull.Value : (object)item.Celda_Destino;
            newRow["Column_B"] = item.colB == null ? DBNull.Value : (object)item.Celda_Origen; ;
            newRow["Column_C"] = item.colC == null ? DBNull.Value : (object)item.Celda_Destino;
            newRow["Column_D"] = item.colD;

            table.Rows.Add(newRow);
        }


        using (SqlConnection conn = new SqlConnection("YOUR CONNECTION STRING"))
        {
            conn.Open();
            using (SqlBulkCopy s = new SqlBulkCopy(conn))
            {
                s.DestinationTableName = table.TableName;
                foreach (DataColumn dc in table.Columns)
                {
                    s.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                }
            }
        }

    }

Depending on your Excel files you will have to implement the logic for getting the List with "YourModel" objects

根据您的Excel文件，您必须实现使用“YourModel”对象获取List的逻辑

#1