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

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

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;
	}
}