通用解析器设计

我认为访问者模式可能很适合这里。您可以修改上面的函数，使其包含一个名为visitor的参数。然后修改for循环，将相关数据传递给访问者对象:

for (int i = 2; i <= rowCount; i++)
{
    visitor.VisitRow(xlRange.Cells, i);
} 

visitor.VisitRow()函数将提取它需要的数据，并在内部保持对提取对象的引用。你会有不同的访客，一个是雇主，一个是销售，客户等。

最后，您将编写如下内容:

Visitor employerVisitor = new EmployerVisitor();
Visitor salesVisitor = new SalesVisitor();
Parse("workbook-employers.xls", employerVisitor);
Parse("workbook-sales.xls", salesVisitor);
List<Employee> employers = employerVisitor.GetData();
List<Sale> sales = salesVisitor.GetData();

您可以从泛型类中公开它，如下所示:

public class ObjectParser<T>
{
    public List<T> ParseObject(string filePath, Func<Range, T> f)       
    {       
        Application _excelApp = null;       
        Workbooks workBooks = null;       
        Workbook workBook = null;       
        Sheets wSheets = null;       
        Worksheet wSheet = null;       
        Range xlRange = null;       
        Range xlRowRange = null;       
        Range xlcolRange = null;       
        List<T> lst= new List<T>();       
        try       
        {       
            _excelApp = new Application();       
            workBooks = _excelApp.Workbooks;       
            workBook = workBooks.Open(filePath, 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);       

            wSheets = (Sheets)workBook.Sheets;       
            wSheet = (Worksheet)wSheets.get_Item(1);       

            xlRange = wSheet.UsedRange;       
            xlRowRange  = xlRange.Rows;       
            xlcolRange = xlRange.Columns;       
            int rowCount = xlRowRange.Count;       
            int colCount = xlcolRange.Count;       
            for (int i = 2; i <= rowCount; i++)       
            {          
               lst.Add(f(xlRange));
            }       
        }       
        catch (Exception exp)       
        {       
        }       
        finally       
        {       
            GC.Collect();       
            GC.WaitForPendingFinalizers();       
            workBook.Close(false, Type.Missing, Type.Missing);                       
            _excelApp.Quit();       

            Marshal.ReleaseComObject(xlRowRange);       
            Marshal.ReleaseComObject(xlRange);       
            Marshal.ReleaseComObject(xlcolRange);       
            Marshal.ReleaseComObject(wSheet);       
            Marshal.ReleaseComObject(wSheets);                       
            Marshal.ReleaseComObject(workBook);       
            Marshal.ReleaseComObject(workBooks);       
            Marshal.ReleaseComObject(_excelApp);       
        }       

        return lst;       
    }       
}

使用:

ObjectParser<Employee> op = new ObjectParser<Employee>()
op.Parse(filepath, r => /* insert code to handle Employee here */)

我这里担心的是一些Marshall.ReleaseComObject()调用被推到传入的lambda上，这使得它有点重。您能告诉我们更多关于Employee和其他类型之间使用的单元格的差异吗?

我已经将我的代码重构为如下内容

   class ExcelParser : IDisposable
{
    bool disposed = false;
    Application _excelApp = null;
    Workbooks workBooks = null;
    Workbook workBook = null;
    Sheets wSheets = null;
    Worksheet wSheet = null;
    Range xlRange = null;
    Range xlRowRange = null;
    Range xlcolRange = null;
    public bool Load(string filePath)
    {
        bool bFlag = true;
        try
        {
            _excelApp = new Application();
            workBooks = _excelApp.Workbooks;
            workBook = workBooks.Open(filePath, 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);
            wSheets = (Sheets)workBook.Sheets;
            wSheet = (Worksheet)wSheets.get_Item(1);
            xlRange = wSheet.UsedRange;
            xlRowRange = xlRange.Rows;
            xlcolRange = xlRange.Columns;
        }
        catch (Exception exp)
        {
            throw;
        }
        return bFlag;
    }
    public int GetRowCount()
    {
        int rowCount = 0;
        if(xlRowRange != null)
            rowCount = xlRowRange.Count;
        return rowCount;
    }
    public string GetValue(int rowIndex, int colIndex)
    {
        string value = "";
        Range cell = null;
        try
        {
            cell = xlRange.Cells[rowIndex, colIndex] as Range;
            object val = cell.Value2;
            value = val.ToString();                
        }
        catch (Exception exp)
        {
        }
        finally
        {
            Marshal.ReleaseComObject(cell);
        }
        return value;
    }
    protected virtual void Dispose(bool disposing)
    {
        if (!this.disposed)
        { // don't dispose more than once
            if (disposing)
            {
                // disposing==true means you're not in the finalizer, so
                // you can reference other objects here
                GC.Collect();
                GC.WaitForPendingFinalizers();
                if (workBook != null)
                    workBook.Close(false, Type.Missing, Type.Missing);
                if (_excelApp != null)
                    _excelApp.Quit();
                if (xlRowRange != null)
                    Marshal.ReleaseComObject(xlRowRange);
                if (xlRange != null)
                    Marshal.ReleaseComObject(xlRange);
                if (xlcolRange != null)
                    Marshal.ReleaseComObject(xlcolRange);
                if (wSheet != null)
                    Marshal.ReleaseComObject(wSheet);
                if (wSheets != null)
                    Marshal.ReleaseComObject(wSheets);
                if (workBook != null)
                    Marshal.ReleaseComObject(workBook);
                if (workBooks != null)
                    Marshal.ReleaseComObject(workBooks);
                if (_excelApp != null)
                    Marshal.ReleaseComObject(_excelApp);
            }              
        }
        this.disposed = true;
    }
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);            
    }
    ~ExcelParser()
    {
        Dispose(false);
    }
}

和调用代码看起来像这样

public List<Employee> Handle(string filePath)
        {
            List<Employee> empLst = new List<Employee>();
            ExcelParser exlParser = new ExcelParser();
            try
            {
                if (exlParser.Load(filePath))
                {
                    int rowCount = exlParser.GetRowCount();
                    for (int i = 2; i <= rowCount; i++)
                    {
                        Employee emp = new Employee();
                        emp.FirstName = exlParser.GetValue(i, 1);
                        emp.LastName  = exlParser.GetValue(i, 2);
                        emp.EmpID     = exlParser.GetValue(i, 3);
                        empLst.Add(emp);                       
                    }
                }
            }
            catch (Exception exp)
            {
            }
            finally
            {
                exlParser.Dispose();
            }
            return empLst;
        }

所以现在我可以在任何我想使用的地方重用解析器。请评论这是否正确