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