(二)C#使用DsoFramer操作Excel实现查找、替换的功能

た入场券 2022-08-14 02:36 338阅读 0赞

该查找、替换功能区分了大小写，如需不区分大小写，再读懂代码后可自行添加

1.查找全部待查询的关键字

/// <summary>
    /// 获得全部查询结果
    /// </summary>
    /// <param name="document">当前打开的文档</param>
    /// <param name="keyWords">查询关键字</param>
    /// <returns></returns>
    private System.Data.DataTable GetSearchResult(object document, string keyWords)
    {
    	try
    	 {
    	    	新建一个虚拟表，用于存放查找到的记录
    		System.Data.DataTable dt = new System.Data.DataTable();
    		dt.Columns.Add("工作簿");
    		dt.Columns.Add("工作表");
    		dt.Columns.Add("单元格");
    		dt.Columns.Add("值");
    
    		if (!string.IsNullOrEmpty(keyWords)) 
    		{
    				获取工作簿
    			Workbook workbook = (Microsoft.Office.Interop.Excel.Workbook)document;
    			int iSheetCount = workbook.Worksheets.Count;
    			int iRowCount = 0, iColCount = 0, iBeginRow = 0, iBeginCol = 0, iResultCount = 0;
    
    			    遍历工作表
    			for (int i = 1; i <= iSheetCount; i++)
    			{
    				Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i];
    				string sName = worksheet.Name;
    					表页下使用区域的行数、列数
    				iRowCount = worksheet.UsedRange.Cells.Rows.Count;
    				iColCount = worksheet.UsedRange.Cells.Columns.Count;
    					表页下使用区域的起始行列号
    				iBeginRow = worksheet.UsedRange.Cells.Row;
    				iBeginCol = worksheet.UsedRange.Cells.Column;
    
    				    遍历每张工作表下的行、列
    				for (int Row = iBeginRow; Row < iRowCount + iBeginRow; Row++)
    				{
    					for (int Col = iBeginCol; Col < iColCount + iBeginCol; Col++)
    					{
    						string s=worksheet.Name;
    						string sCurrentText = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[Row, Col]).Text.ToString();
    												
    						if (sCurrentText.IndexOf(keyWords) >= 0)
    						{
    							 获取查询到的单元格
    							Range range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[Row, Col];
    
    							 获得单元格编号
    							string sAddress = range.get_Address(Row, Col, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
    							DataRow dr = dt.NewRow();
    							dr["工作簿"] = workbook.Name != null ? workbook.Name : string.Empty;
    							dr["工作表"] = worksheet.Name != null ? worksheet.Name : string.Empty;
    							dr["单元格"] = sAddress;
    							dr["值"] = keyWords;
    							dt.Rows.Add(dr);
    							iResultCount++;
    						}
    					}
    				}
    			}
    		}
    		return dt;
    	}
    	catch
    	{
    		return null;
    	}
    }

2.全部替换

/// <summary>
    /// 全部替换
    /// </summary>
    /// <param name="document">打开的文档</param>
    /// <param name="keyWords">查找关键字</param>
    /// <param name="replaceWords">替换成的关键字</param>
    /// <returns></returns>
    private int ReplaceAll(object document, string keyWords, string replaceWords)
    {
    	try
    	{
    		int iResultCount = 0;
    		if (!string.IsNullOrEmpty(keyWords))
    		{
    			Workbook workbook = (Microsoft.Office.Interop.Excel.Workbook)document;
    			int iSheetCount = workbook.Worksheets.Count;
    			int iRowCount = 0, iColCount = 0, iBeginRow = 0, iBeginCol = 0;
    
    			 遍历工作表
    			for (int i = 1; i <= iSheetCount; i++)
    			{
    				Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i];
    				iRowCount = worksheet.UsedRange.Cells.Rows.Count;
    				iColCount = worksheet.UsedRange.Cells.Columns.Count;
    				iBeginRow = worksheet.UsedRange.Cells.Row;
    				iBeginCol = worksheet.UsedRange.Cells.Column;
    
    				 遍历每张工作表下的行、列
    				for (int Row = iBeginRow; Row < iRowCount + iBeginRow; Row++)
    				{
    					for (int Col = iBeginCol; Col < iColCount + iBeginCol; Col++)
    					{
    						string sCurrentText = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[Row, Col]).Text.ToString();
    
    						 判断遍历到的单元格是否包含要替换的 文本
    						if (sCurrentText.IndexOf(keyWords) >= 0)
    						{
    							Range range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[Row, Col];
    							 替换
    							string sNewText = sCurrentText.Replace(keyWords, replaceWords);
    							range.Value2 = sNewText;
    							iResultCount++;
    						}
    					}
    				}
    			}
    		}
    		return iResultCount;
    	}
    	catch
    	{
    		return 0;
    	}
    }