string[] posicionCoordenadas(string[] columnas, Microsoft.Office.Interop.Excel.Worksheet worksheet) { string[] salida = new string [columnas.Length]; for (int i = 0; i < columnas.Length; i++) { Microsoft.Office.Interop.Excel.Range searchedRange = worksheet.get_Range("A1", "Z100"); string obtenerColumna = new string(columnas[i].Split(config)[0].ToArray()); Microsoft.Office.Interop.Excel.Range currentFind = searchedRange.Find(obtenerColumna); if (currentFind != null) { int col = currentFind.Column; int fil = currentFind.Row; var obtenerColorColumna = ""; var obtenerAlignColumna = ""; var obtenerFontColumna = ""; var obtenerTipoColumna = new string(columnas[i].Split(config)[0].Where(Char.IsLetter).ToArray()); string[] tipos = Obtenertipos(columnas[i]); for (int j = 0; j < tipos.Length; j++) { switch (tipos[j]) { case "#": { obtenerColorColumna = tipos[++j]; break; } case "$": { obtenerAlignColumna = tipos[++j]; break; } case "%": { obtenerFontColumna = tipos[++j]; break; } } } string tipoColumna1 = obtenerFormato(obtenerTipoColumna); string tipoColumna2 = obtenerTipoColumna; salida[i] = fil + ";" + col + ";" + tipoColumna1 + ";" + tipoColumna2 + ";" + obtenerColorColumna + ";" + obtenerAlignColumna + ";" + obtenerFontColumna; } } return(salida); }
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); }