用于比较两个 Excel 工作表的 Excel 工具

问：

这是我当前的代码。打开以接收任何注释以改进内存优化。

当我使用 1000000*8 数据获取 1000000 * 8 的样本时，它会导致内存不足异常。很想得到关于优化内存使用的建议。

比较名为“Before”和“After”的数据集中的两个表，并填充所有结果表。

private bool CompareAndFillResultTable(DataSet ds)
    {
        Stopwatch stopWatch = new Stopwatch(); stopWatch.Start();
        System.Data.DataTable dt_copy;
        dt_copy = new System.Data.DataTable();
        dt_copy = ds.Tables["Before"].Copy();
        dt_copy.TableName = "BeforeBackup";
        ds.Tables.Add(dt_copy);
        dt_copy = new System.Data.DataTable();
        dt_copy = ds.Tables["After"].Copy();
        dt_copy.TableName = "AfterBackup";
        ds.Tables.Add(dt_copy);
        dt_copy = new System.Data.DataTable();
        dt_copy = ds.Tables["Before"].Clone();
        dt_copy.TableName = "BeforeSingular";
        ds.Tables.Add(dt_copy);
        dt_copy = new System.Data.DataTable();
        dt_copy = ds.Tables["Before"].Clone();
        dt_copy.TableName = "AfterSingular";
        ds.Tables.Add(dt_copy);
        dt_copy = new System.Data.DataTable();
        dt_copy = ds.Tables["Before"].Clone();
        dt_copy.TableName = "Duplicates";
        ds.Tables.Add(dt_copy);
        dt_copy = new System.Data.DataTable();
        dt_copy = ds.Tables["Before"].Clone();
        dt_copy.TableName = "Mismatch";
        ds.Tables.Add(dt_copy);

        foreach (System.Data.DataTable table in ds.Tables)
        {
            table.Columns.Add("Source_Label");
        }

        //Remove identical from before, then after
        for (int i = 0; i < ds.Tables["Before"].Rows.Count; i++)
        {
            string BeforeCompareKeyVal = ds.Tables["Before"].Rows[i][Inputs.SortColumn].ToString();
            if (ds.Tables["After"].Rows.Count > 0)
            {
                for (int j = 0; j < ds.Tables["After"].Rows.Count; j++)
                {
                    string AfterCompareKeyVal = ds.Tables["After"].Rows[j][Inputs.SortColumn].ToString();

                    if (ds.Tables["Before"].Rows[i].ItemArray.SequenceEqual(ds.Tables["After"].Rows[j].ItemArray))
                    {
                        //copy Aftter row to duplicate Table and Remove row from After
                        DataRow rw = ds.Tables["After"].Rows[j];
                        rw[ds.Tables["After"].Columns.Count - 1] = "NA";
                        ds.Tables["Duplicates"].ImportRow(rw);
                        ds.Tables["After"].Rows.RemoveAt(j);
                        j--;
                        break;
                    }
                    if (Int64.Parse(BeforeCompareKeyVal) > Int64.Parse(AfterCompareKeyVal))// Review - 7
                    {
                        if (true)//all dup after + a before - set logic
                        {
                            //Copy After row to AfterSingular Table and Remove row from After
                            DataRow rw = ds.Tables["After"].Rows[j];
                            rw[ds.Tables["After"].Columns.Count - 1] = "After";
                            ds.Tables["AfterSingular"].ImportRow(rw);
                            ds.Tables["After"].Rows.RemoveAt(j);
                            j--;
                            if (ds.Tables["After"].Rows.Count == 0)
                            {
                                rw = ds.Tables["Before"].Rows[i];
                                rw[ds.Tables["Before"].Columns.Count - 1] = "Before";
                                ds.Tables["BeforeSingular"].ImportRow(rw);
                            }
                            continue;
                        }

                    }
                    if (Int64.Parse(BeforeCompareKeyVal) < Int64.Parse(AfterCompareKeyVal))// Review - 7
                    {
                        if (true)//all dup after and a before set logic
                        {
                            //Copy Before row to BeforeSingular Table
                            DataRow rw = ds.Tables["Before"].Rows[i];
                            rw[ds.Tables["Before"].Columns.Count - 1] = "Before";
                            ds.Tables["BeforeSingular"].ImportRow(rw);
                            break;
                        }
                    }
                    if (Int64.Parse(BeforeCompareKeyVal) == Int64.Parse(AfterCompareKeyVal))// Review - 7
                    {
                        //Copy Before, After row to Mismatch Table and Remove row from After
                        if (true)//all dup after and a before set logic
                        {
                            DataRow rwB = ds.Tables["Before"].Rows[i];
                            rwB[ds.Tables["Before"].Columns.Count - 1] = "Before";
                            DataRow rwA = ds.Tables["After"].Rows[j];
                            rwA[ds.Tables["After"].Columns.Count - 1] = "After";
                            ds.Tables["Mismatch"].ImportRow(rwB);
                            ds.Tables["Mismatch"].ImportRow(rwA);
                            ds.Tables["After"].Rows.RemoveAt(j);
                            j--;
                            break;
                        }
                    }
                }
            }
            else
            {
                DataRow rw = ds.Tables["Before"].Rows[i];
                rw[ds.Tables["Before"].Columns.Count - 1] = "Before";
                ds.Tables["BeforeSingular"].ImportRow(rw);
                continue;
            }
        }
        //Add remaining after table rows to AfterSingular table
        ds.Tables["AfterSingular"].Merge(ds.Tables["After"]);

        //ds.Tables["AfterSingular"].Columns.Add("Source_Label", System.Type.GetType("System.String"), "After_Singular");
        //ds.Tables["BeforeSingular"].Columns.Add("Source_Label", System.Type.GetType("System.String"), "Before_Singular");

        //foreach (System.Data.DataTable table in ds.Tables)
        //{
        //    DataRow colNames = table.NewRow();
        //    //foreach (var col in table.Columns)
        //    //{

        //    //}                
        //    for (int i = 0; i < table.Columns.Count; i++)
        //        colNames[i] = table.Columns[i].ColumnName;
        //    table.Rows.InsertAt(colNames, 0);
        //}

        foreach (System.Data.DataTable table in ds.Tables)
        {
            table.Columns.Remove(Inputs.SortColumn);
            table.AcceptChanges();
        }

        stopWatch.Stop(); lbAlert.Text = lbAlert.Text + "\n\n" + "Total Comparison time for B: " + Inputs.RowNoBeforeTable + " x " + Inputs.ColumnNoBeforeTable + " A: " + Inputs.RowNoAfterTable + " x " + Inputs.ColumnNoAfterTable + " is " + stopWatch.ElapsedMilliseconds + " ms, " + stopWatch.ElapsedMilliseconds / 1000 + " s";
        return true;
    }