C# 查找EXCEL的两种方法比较

第一种方法是才用COM一个个CELL比较，代码如下：

 

for (int row = 1; row < xsl.workSheet.UsedRange.Rows.Count; row++)
                {
                    bool matched = false;
                    for (int col = 1; col < xsl.workSheet.UsedRange.Columns.Count; col++)
                    {
                        String val = xsl.workSheet.Cells[row, col].Text;
                        if (val != "")
                        {
                            if (val.IndexOf(searchString) >= 0)
                            {
                                //System.Console.WriteLine("{0}, {1}, {2}", row, col, val);
                                matched = true;
                                break;
                            }
                        }
                    }
                    lock (searchResult)
                    {
                        searchResult.doneRows = row;
                        if (matched)
                        {
                            searchResult.matchRows++;
                            searchResult.totalMatchRows++;

                            // 复制匹配行到结果集里
                            xslResult.workSheet.Cells[searchResult.totalMatchRows, 1] = searchResult.currentFileName;
                            xslResult.workSheet.Cells[searchResult.totalMatchRows, 2] = searchResult.currentSheetName;

                            for (int col = 1; col < xsl.workSheet.UsedRange.Columns.Count; col++)
                            {
                                xslResult.workSheet.Cells[searchResult.totalMatchRows, col + 2] = xsl.workSheet.Cells[row, col];
                            }

                        }
                    }
                }

第二种方法是先把EXCEL复制到数组里，再和数组一个个比较，代码如下：

 

Object[,] saRet = (System.Object[,])xsl.workSheet.UsedRange.get_Value();
                for (int row = 1; row < saRet.GetUpperBound(0); row++)
                {
                    bool matched = false;
                    for (int col = 1; col < saRet.GetUpperBound(1); col++)
                    {                        
                        if (saRet[row, col] != null)
                        {
                            String val = saRet[row, col].ToString();
                            if (val.IndexOf(searchString) >= 0)
                            {
                                //System.Console.WriteLine("{0}, {1}, {2}", row, col, val);
                                matched = true;
                                break;
                            }
                        }
                    }
                    lock (searchResult)
                    {
                        searchResult.doneRows = row;
                        if (matched)
                        {
                            searchResult.matchRows++;
                            searchResult.totalMatchRows++;

                            // 复制匹配行到结果集里
                            xslResult.workSheet.Cells[searchResult.totalMatchRows, 1] = searchResult.currentFileName;
                            xslResult.workSheet.Cells[searchResult.totalMatchRows, 2] = searchResult.currentSheetName;

                            for (int col = 1; col < xsl.workSheet.UsedRange.Columns.Count; col++)
                            {
                                xslResult.workSheet.Cells[searchResult.totalMatchRows, col + 2] = saRet[row, col];
                            }

                        }
                    }
                }

这两种方法一比较，第二种方法竟然比较第一种方法快100倍左右。