如何使用 C# Interop 保留最新记录并删除旧记录

Question

我有一个包含四列的 xls 文件，

ID          Name           Date       File
1           charlie        01/09/2011 1.txt
2           charlie        12/25/2005 2.txt
3           nero           11/11/2011 3.txt
4           charlie        12/09/2011 4.txt

该过程将能够删除 charlie 的旧记录并保留最新的记录 基于名称和日期的其他列将被忽略。

在此示例中，预期 xls 将包含以下记录：

ID          Name           Date       File
3           nero           11/11/2011 3.txt
4           charlie        12/09/2011 4.txt

以 MM/DD/YYYY 表示的日期。

任何建议将不胜感激

Answer 1

我相信您需要按名称分组，并获取每个分组的最近日期，然后从结果中仅选择 ID。 从 Table1 GROUP BY Table1.Name 中选择 Table1.ID、MAX(Table1.Date)。因此，在获得此查询后，只需使用 ID 字段即可。

接下来执行删除查询（伪代码）DELETE FROM [Table] WHERE [Table].ID NOT IN（上面的结果）

以下是使用 LINQ to SQL 完成的：

static void Main(string[] args)
    {
        using(var db = new DataClasses1DataContext())
        {
            var query = db.Table1s.GroupBy(x => x.Name)
                .Select(x => new
                                 {
                                     ID = x.Max(t => t.ID),
                                     Name = x.Max(t => t.Name),
                                     Date = x.Max(t => t.Date)

                                 });

            foreach (var n in query)
            {
                Console.WriteLine(n.ID + " " + n.Name + " " + n.Date);
            }
            Console.WriteLine("");
            //Result:
            //4 charlie 12/9/2011
            //3 nero 11/11/2011

            var deleteQuery = db.Table1s.Where(x => !query.Select(t=> t.ID)
                                        .Contains(x.ID));

            db.Table1s.DeleteAllOnSubmit(deleteQuery);
            db.SubmitChanges();

            var testDeletion = db.Table1s;

            foreach (var n in testDeletion)
            {
                Console.WriteLine(n.ID + " " + n.Name + " " + n.Date);   
            }
        }
    }

此代码是针对数据库完成的，但基本逻辑仍然适用于 Excel 文件。

Answer 2

如果您不确定是否希望将其作为使用 C# 和 COM Interop to Excel 的外部程序，您可能会在此处找到一些有关宏的有用信息：

http://office.microsoft.com/en-gb/excel-help/create-or-delete-a-macro-HP010014111.aspx

否则，您需要创建一个新的Visual Studio 中的 C# 项目并添加对 Microsoft.Office.Core 和 Microsoft.Office.Interop.Excel 的引用。

完成后，您可以执行以下操作（注意：您需要更好的错误清理）：

using System;
using System.Collections.Generic;
using System.IO;

using Microsoft.Office.Interop.Excel;

namespace ExcelInterop
{
    class Program
    {
        static void Main(string[] args)
        {
            Application app = new Application();
            try
            {
                FileInfo fiSource = new FileInfo(args[0]);
                FileInfo fiDest = new FileInfo(args[1]);
                Workbook wb = app.Workbooks.Open(fiSource.FullName,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                Worksheet sheet = (Worksheet)wb.Sheets[1];

                Dictionary<string, DateTime> hashNewest = new Dictionary<string, DateTime>();
                for (int iRow = 1; iRow < (double)sheet.Cells.Height; ++iRow )
                {
                    object oID = sheet.get_Range("A" + (iRow + 1), Type.Missing).get_Value(Type.Missing);
                    if (oID == null || oID.ToString().Trim().Length <= 0)
                        break;

                    object oName = sheet.get_Range("B" + (iRow + 1), Type.Missing).get_Value(Type.Missing);
                    string strName = "" + oName;
                    object oDate = sheet.get_Range("C" + (iRow + 1), Type.Missing).get_Value(Type.Missing);
                    DateTime dt = Convert.ToDateTime(oDate);
                    if( !hashNewest.ContainsKey(strName) )
                        hashNewest.Add(strName, dt);
                    else if (hashNewest[strName].CompareTo(dt) < 0)
                        hashNewest[strName] = dt;
                }

                for (int iRow = 1; iRow < (double)sheet.Cells.Height; ++iRow)
                {
                    object oID = sheet.get_Range("A" + (iRow + 1), Type.Missing).get_Value(Type.Missing);
                    if (oID == null || oID.ToString().Trim().Length <= 0)
                        break;

                    object oName = sheet.get_Range("B" + (iRow + 1), Type.Missing).get_Value(Type.Missing);
                    string strName = "" + oName;
                    object oDate = sheet.get_Range("C" + (iRow + 1), Type.Missing).get_Value(Type.Missing);
                    DateTime dt = Convert.ToDateTime(oDate);
                    if (!hashNewest[strName].Equals(dt))
                    {
                        sheet.get_Range(
                            string.Format("A{0}:D{0}", iRow+1),
                            Type.Missing
                            ).Delete(XlDeleteShiftDirection.xlShiftUp);
                        --iRow;
                    }
                }

                File.Delete(fiDest.FullName);
                wb.SaveAs(fiDest.FullName, Type.Missing, Type.Missing, Type.Missing, 
                    Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, 
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing );

                wb.Close(false, Type.Missing, Type.Missing);
            }
            finally
            {
                app.Workbooks.Close();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            }
            Console.WriteLine("Hit any key to continue");
            Console.ReadKey();
        }
    }
}