/// <summary> /// Поиск на листе Excel по частичным текстовым совпадениям строк и ключей /// </summary> /// <param name="sourceSheet">Лист для поиска, источник</param> /// <param name="key">Ключ для текстового поиска</param> /// <returns>Список строк, преобразованный в список элементов класса символьной таблицы</returns> private List <SymbolTableItemModel> OneClick_SearchKey(string sourceSheet, string key) { var sheets = excelbook.Worksheets; var sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(sourceSheet); sheet.Select(); Microsoft.Office.Interop.Excel.Range currentFind = null; Microsoft.Office.Interop.Excel.Range firstFind = null; Microsoft.Office.Interop.Excel.Range oneRow = null; var s7_list = new List <SymbolTableItemModel>(); //------ Поиск ------------------------------------------ currentFind = excelapp.Columns.Find(key, Type.Missing, Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlPart, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false); while (currentFind != null) //&(final<10) ) { // Keep track of the first range you find. if (firstFind == null) { firstFind = currentFind; } // If you didn't move to a new range, you are done. else if (currentFind.get_Address(Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing) == firstFind.get_Address(Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing)) { break; } var r = currentFind.Row; var s7_item = new SymbolTableItemModel(); // Строка символьной таблицы, [А234:K234] oneRow = sheet.Range["A" + r.ToString() + ":K" + r.ToString()]; //s7_item = generateListFromRange(oneRow); s7_list.Add(s7_item); currentFind.EntireRow.Clear(); currentFind = excelapp.Columns.FindNext(currentFind); } return(s7_list); }
private void tsbtn_Query_Click(object sender, EventArgs e) { CloseProcess("EXCEL"); //关闭所有Excel进程 string P_str_Excel = tstxt_Excel.Text; //记录Excel文件路径 string P_str_SheetName = tscbox_Sheet.Text; //记录选择的工作表名称 object P_obj_Start = tstxt_Start.Text; //记录开始单元格 object P_obj_End = tstxt_End.Text; //记录结束单元格 object missing = System.Reflection.Missing.Value; //定义object缺省值 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //实例化Excel对象 //打开Excel文件 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(P_str_Excel, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Worksheet worksheet; //声明工作表 worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[P_str_SheetName]); //获取选择的工作表 Microsoft.Office.Interop.Excel.Range searchRange = worksheet.get_Range(P_obj_Start, P_obj_End); //定义查找范围 Microsoft.Office.Interop.Excel.Range currentRange = null; //定义当前找到的范围 Microsoft.Office.Interop.Excel.Range firstRange = null; //定义找到的第一个范围 object P_obj_Text = tstxt_Text.Text; //记录要搜索的文本 //搜索第一个匹配项,指定从其后开始搜索的单元格以外的所有参数 currentRange = searchRange.Find(P_obj_Text, missing, Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlPart, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing); //一直搜索,直到没有匹配项 while (currentRange != null) { if (firstRange == null) //如果第一个范围不包含任何值 { firstRange = currentRange; //记录当前范围 } //如果查找范围的地址与第一个查找范围的地址匹配 else if (currentRange.get_Address(missing, missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, missing, missing) == firstRange.get_Address(missing, missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, missing, missing)) { break;//退出 } //为单元格加边框 currentRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Color.Black.ToArgb()); currentRange.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Red); //设置搜索到的文本颜色 currentRange.Font.Bold = true; //设置搜索到的文本为粗体 currentRange = searchRange.FindNext(currentRange); //查找下一处 } MessageBox.Show("搜索完毕!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); excel.DisplayAlerts = false; //设置保存Excel时不显示对话框 workbook.Save(); //保存工作表 CloseProcess("EXCEL"); //关闭所有Excel进程 WBrowser_Excel.Navigate(P_str_Excel); //在窗体中重新显示Excel文件内容 }
public static bool ConnectToExcelRegisterAndGetInformation(string dateToFind) { // Создаём приложение ExcelApp = new Interop.Excel.Application(); ExcelApp.Visible = false; // Открываем книгу Register = ExcelApp.Workbooks.Open(Properties.Settings.Default.RegisterPath, 0, true, 5, "", "", false, Interop.Excel.XlPlatform.xlWindows, "", false, false, 0, false, false, false); // Выбираем таблицу(лист) Interop.Excel.Worksheet RegisterSheet = (Interop.Excel.Worksheet)Register.Worksheets[Properties.Settings.Default.RegisterSheetName]; registerPart = new DataTable(); // Выполняем поиск диапазона нужных строк (по дате выписки) и заполняем данными registerPart Interop.Excel.Range range = RegisterSheet.get_Range(excelCellStart, excelCellEnd + RegisterSheet.UsedRange.Rows.Count); Interop.Excel.Range firstFind = null; Interop.Excel.Range currentFind; Interop.Excel.Range usedRange = RegisterSheet.UsedRange; int usedColumnsCount = RegisterSheet.UsedRange.Columns.Count; Interop.Excel.Range columnNamesRange = RegisterSheet.get_Range("A1", "A" + usedColumnsCount); bool found = false; for (int i = 2; i <= usedColumnsCount; i++) { String columnName = (columnNamesRange.Cells[1, i] as Interop.Excel.Range).Value2.ToString(); if (!found) { if (columnName.Contains(filialNames.First())) { found = true; filialRegisterIndent = i; } } registerPart.Columns.Add(new DataColumn(columnName)); } currentFind = range.Find(dateToFind, Type.Missing, Interop.Excel.XlFindLookIn.xlValues, Interop.Excel.XlLookAt.xlPart, Interop.Excel.XlSearchOrder.xlByRows, Interop.Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing); while (currentFind != null) { // Сохраняем первый найденный if (firstFind == null) { firstFind = currentFind; } // Если мы не сдвинулись в поиске, значит это конец поиска else if (currentFind.get_Address(Interop.Excel.XlReferenceStyle.xlA1) == firstFind.get_Address(Interop.Excel.XlReferenceStyle.xlA1)) { break; } // помещаем данные в registerPart List <String> paramsList = new List <String>(); int row = currentFind.Row; int columnsNeed = filialRegisterIndent + filialNames.Length - 1; for (int i = 2; i <= columnsNeed; i++) { Object value = (usedRange.Cells[row, i] as Interop.Excel.Range).Value; if (value == null) { paramsList.Add(String.Empty); } else { paramsList.Add(value.ToString()); } } registerPart.Rows.Add(paramsList.ToArray()); // ищем дальше, начиная с последней найденной ячейки currentFind = range.FindNext(currentFind); } if (firstFind == null) { return(false); } filialRegisterIndent -= 2; return(true); }