从Excel导入数据到多个表中

Question

我正在开发一个离线的C＃应用程序，该程序将从电子表格中导入数据并将其存储在我创建的SQL数据库中（项目内部）。通过一些研究，我已经能够使用一些代码将静态表导入到与工作表列布局完全相同的数据库中。

我希望特定列根据名称进入其正确的表格，这样我就可以正确设计数据库，而不仅仅是拥有一个大表来存储所有内容。

下面是我正在使用的代码，将几个静态字段导入一个表格中，我想能够将导入的数据分割成多个表格。

最佳方法是什么？

public partial class Form1 : Form
    {
        string strConnection = ConfigurationManager.ConnectionStrings
        ["Test3.Properties.Settings.Test3ConnectionString"].ConnectionString;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {


            //Create connection string to Excel work book
            string excelConnectionString =
            @"Provider=Microsoft.Jet.OLEDB.4.0;
            Data Source=C:\Test.xls;
            Extended Properties=""Excel 8.0;HDR=YES;""";

            //Create Connection to Excel work book
            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

            //Create OleDbCommand to fetch data from Excel
            OleDbCommand cmd = new OleDbCommand
            ("Select [Failure_ID], [Failure_Name], [Failure_Date], [File_Name], [Report_Name], [Report_Description], [Error] from [Failures$]", excelConnection);

            excelConnection.Open();
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();

            SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
            sqlBulk.DestinationTableName = "Failures";
            sqlBulk.WriteToServer(dReader);

        }

- JoshG

Answer 1

你可以尝试使用ETL（抽取-转换-加载）架构：

抽取：一个类将打开文件并获取所有数据块，你可以知道如何使用这些块（通常你从文件中取出一行数据，并将其解析为POCO对象，该对象包含保存相关数据的字段），并将它们放入队列中以供其他工作进程使用。在这种情况下，也许你首先要做的是让Excel打开文件并将其重新保存为CSV格式，以便在进程中将其重新打开为基本文本并高效地切割它。你还可以读取列名并构建“映射字典”；这个列是这样命名的，所以它去到数据对象的这个属性。此过程应尽快发生，唯一导致失败的原因应是行的格式与给定文件结构不匹配。

转换：一旦文件内容已经被提取成为基本的行实例，就执行任何必要的验证、计算或其他业务规则，将文件中的行转换成符合你领域模型的一组域对象。这个过程可以像你需要的那样复杂，但是它应该是尽可能简单明了的，同时遵守你的需求中给出的所有业务规则。

加载：现在在你自己的域对象中有了一个对象图，你可以使用与处理任何其他方式创建的域对象相同的持久性框架进行处理。这可以是基本的ADO、一个ORM（如NHibernate或MSEF）或一个Active Record模式，其中对象知道如何自己持久化。它不是批量加载，但是它可以节省你实现完全不同的持久化模型以将基于文件的数据输入到数据库中的麻烦。

ETL工作流可以帮助你将重复的任务分成简单的工作单元，然后你可以识别需要大量时间的任务并考虑并行处理。

或者，你可以通过检测想要处理的列，并将它们排列成与你的批量输入规范相匹配的格式，来处理文件的格式，然后调用批量插入例程来处理数据。这个文件处理程序例程可以执行任何你想要做的事情，包括将数据分离成几个文件。但是，它是一个整个文件一次处理的大型进程，具有有限的优化或并行处理机会。然而，如果你的加载机制很慢，或者你有很多简单易懂的数据，那么它可能比设计良好的ETL甚至更快。

无论如何，我会尽可能快地从Office格式转换为纯文本（或XML）格式，并且我绝对会避免在服务器上安装Office。如果有任何方法可以要求在加载之前文件以某种易于解析的格式（如CSV），那就更好了。在服务器上安装Office一般是非常糟糕的事情，而在服务器应用程序中进行OLE操作也不会好多少。该应用程序将非常脆弱，任何 Office 想告诉你的东西都会导致应用程序挂起，直到你登录到服务器并清除对话框为止。

- KeithS

---

@KeithS：我正在考虑这种方法，用于一个单独工作表中有多个表格的Excel文件。你有什么建议吗？ - Robin Maben

Answer 2

如果您正在寻找更多与代码相关的答案，您可以使用以下方法修改您的代码以适应困难的列名/不同的表：

    private void button1_Click(object sender, EventArgs e)
    {
        //Create connection string to Excel work book
        string excelConnectionString =
        @"Provider=Microsoft.Jet.OLEDB.4.0;
        Data Source=C:\Test.xls;
        Extended Properties=""Excel 8.0;HDR=YES;""";

        //Create Connection to Excel work book
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

        //Create OleDbCommand to fetch data from Excel
        OleDbCommand cmd = new OleDbCommand
        ("Select [Failure_ID], [Failure_Name], [Failure_Date], [File_Name], [Report_Name], [Report_Description], [Error] from [Failures$]", excelConnection);

        excelConnection.Open();

        DataTable dataTable = new DataTable();
        dataTable.Columns.Add("Id", typeof(System.Int32));
        dataTable.Columns.Add("Name", typeof(System.String));
        // TODO: Complete other table columns
        using(OleDbDataReader dReader = cmd.ExecuteReader())
        {
            DataRow dataRow = dataTable.NewRow();
            dataRow["Id"] = dReader.GetInt32(0);
            dataRow["Name"] = dReader.GetString(1);
            // TODO: Complete other table columns
            dataTable.Rows.Add(dataRow);
        }

        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
        sqlBulk.DestinationTableName = "Failures";
        sqlBulk.WriteToServer(dataTable);
    }

现在你可以控制列的名称和数据导入到哪些表中。SqlBulkCopy适用于插入大量数据。如果您只有少量行，则最好创建标准数据访问层来插入记录。

- Neil Bostrom

Answer 3

如果您只对文本内容感兴趣（而不是格式等），可以将Excel文件另存为CSV文件，然后解析CSV文件，这样会更简单。

- Louis Rhys

Answer 4

根据程序的生命周期，我建议两种选择之一。

1. 如果程序的使用寿命较短，或者通常是一个“丢弃式”项目，我建议使用一系列例程，使用标准SQL和一些字符串处理将数据解析并输入到另一组表中。

2. 如果程序将在更长时间内存在，并且/或者在日常使用中更加常见，我建议实施类似于@KeithS推荐的解决方案。通过一套明确定义的与数据处理相关的步骤，可以获得更大的灵活性。具体而言，.NET Entity Framework可能非常适合。 作为额外的好处，如果您还不熟悉这个领域，您可能会在首次使用EF等ORM时学到很多关于在边界之间处理数据（xls -> sql -> etc.）的知识。

- Aaron Ransley