public string find_once(string Text, string adrStart, string adrFinish, Excel.Range StartFrom = null)
{
Excel.Range area = appExcel.get_Range(adrStart, adrFinish);
Excel.Range firstFind = null;
int min = getRow(adrStart);
int max = getRow(adrFinish);
if (StartFrom != null)
{
firstFind = area.Find(Text, StartFrom);
}
else
{
firstFind = area.Find(Text);
}
if (firstFind != null) //проверка вхождения в диапазон
{
int currRow = getRow(addressDollarClear(firstFind.get_Address()));
if (currRow < min || currRow > max)
{
firstFind = null;
}
}
return((firstFind != null) ? addressDollarClear(firstFind.get_Address()) : null);
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application excel = null;
excel = new Excel.Application();
excel.Visible = true;
Excel.Workbook wkb = null;
wkb = Open(excel, lblPath.Text);
Excel.Range searchedRange = excel.get_Range("A1", "XFD1048576");
Excel.Range currentFind = searchedRange.Find(tbInput.Text);
string displayResult = "";
if (currentFind != null)
{
displayResult = "Found at \ncolumn - " + currentFind.Column +
"\nrow - " + currentFind.Row;
}
else
{
displayResult = "The searched string \"" + tbInput.Text +
"\" is not found.";
}
lblResult.Text = displayResult;
wkb.Close(true);
excel.Quit();
}
internal static bool IsQuoteSht(Excel.Worksheet XlSh) // Returns true if a sheet complies with all the quote requirements
{
string[] LineCodes = { "#BillStart", "BillEnd", "#BSTStart", "#BSTEnd" };
string[] CellNames = { "PoDate", "PoNo", "PoStatus", "PoAmount", "QBranch", "InvNo", "InvDate", "InvNo" }; //Check only required names
Excel.Range LineCodeRange = XlSh.Range["A:A"];
Excel.XlColorIndex TabColor = XlSh.Tab.ColorIndex;
foreach (string LineCode in LineCodes) // Loop through line codes
{
var FindRange = LineCodeRange.Find(What: LineCode);
if (FindRange == null)
{
XlSh.Tab.ColorIndex = TabColor;
return(false);
}
}
foreach (string CellName in CellNames) // Loop through cell names
{
try
{
var NamedRange = XlSh.Names.Item(CellName);
}
catch
{
XlSh.Tab.ColorIndex = TabColor;
return(false);
}
}
XlSh.Tab.Color = Excel.XlRgbColor.rgbAquamarine;
return(true);
}
public void findAndReplaceValue(Excel.Range searchRange, String searchTerm, String replacement)
{
Excel.Range currentFind = null;
Excel.Range firstFind = null;
currentFind = searchRange.Find(searchTerm, Missing.Value,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Missing.Value, Missing.Value);
int i = 1;
while (currentFind != null)
{
// 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(Excel.XlReferenceStyle.xlA1)
== firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
{
break;
}
currentFind.Value2 = Regex.Replace(currentFind.Value2, searchTerm, replacement);
currentFind = searchRange.FindNext(currentFind);
i++;
}
}
public static List <int> GetRowsIndexesByText(Excel.Range startRange, Excel.Range searchRange, string search)
{
List <int> matches = new List <int>();
int i = 0;
try
{
searchRange.Find(What: search, LookIn: Excel.XlFindLookIn.xlValues, LookAt: Excel.XlLookAt.xlPart, SearchOrder: Excel.XlSearchOrder.xlByColumns);
Excel.Range next = startRange;
//Logger.Log("searchRange count" + searchRange.Count);
while (i++ < searchRange.Count)
{
//next = searchRange.FindNext(next.Offset[1, 0]);
next = searchRange.FindNext(next);
if (next != null && !matches.Contains(next.Row))
{
matches.Add(next.Row);
}
//if (!matches.Add(next.Row))
//break;
}
}
catch (Exception ex)
{
var sRange = startRange.Columns.Count + "," + startRange.Rows.Count;
var eRange = searchRange.Columns.Count + "," + searchRange.Rows.Count;
Logger.Log(string.Format("Error in GetRowsIndexByText ({0}) [{1}] : [{2}], i = {3} >> {4}", search, sRange, eRange, i, ex.Message + " - " + ex.StackTrace));
}
return(matches);
}
private void LlenaCeldas(string _Saldo, string _Indice, string _Anexo, string _Columna)
{
int _MaxRow = 0;
try
{
Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Worksheet sheet = (Excel.Worksheet)wb.Worksheets.get_Item(_Anexo);
_MaxRow = sheet.UsedRange.Count + 1;
if (sheet != null)
{
Excel.Range range = (Excel.Range)sheet.get_Range("A1:A" + _MaxRow.ToString());
Excel.Range findValue = range.Find(_Indice, Type.Missing, Excel.XlFindLookIn.xlValues,
Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows,
Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);
if (findValue != null)
{
range = (Excel.Range)sheet.Cells[findValue.Row, _Columna];
range.Value = _Saldo;
}
}
}
catch (Exception ex)
{
MessageBox.Show($"Error al llenar la hoja {_Anexo}: {ex.Message.ToString()}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private Excel.Worksheet GetValue(Excel.Workbook WB, Excel.Worksheet WS, string key, int index, int Row)
{
Excel.Range last = WS.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = WS.get_Range("A1", last);
var findResult = range.Find(key, Missing.Value,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
Missing.Value, Missing.Value);
var firstFindResult = findResult;
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
if (j == index)
{
var val1 = dataGridView1[j, Row].Value;
bool isFirst = false;
while (findResult != null)
{
var result = findResult;
string loc = result.Address[Excel.XlReferenceStyle.xlA1];
if (isFirst && firstFindResult.Address[Excel.XlReferenceStyle.xlA1] == findResult.Address[Excel.XlReferenceStyle.xlA1])
{
findResult = null;
}
isFirst = true;
findResult = range.FindNext(findResult);
WS.Cells[int.Parse(loc.Split('$')[2]), loc.Split('$')[1]] = val1;
}
}
}
return(WS);
}
public static int searchForValue(Excel.Worksheet Wks2, string searchString, int intStartColumToCheck)
{
int intRetVal;
Excel.Range colRange = Wks2.Columns["A:A"]; //get the range object where you want to search from
Excel.Range resultRange = colRange.Find(
What: searchString,
LookIn: Excel.XlFindLookIn.xlValues,
LookAt: Excel.XlLookAt.xlPart,
// SearchOrder: Excel.XlSearchOrder.xlByRows,
SearchOrder: Excel.XlSearchOrder.xlByColumns,
SearchDirection: Excel.XlSearchDirection.xlNext
); // search searchString in the range, if find result, return a range
if (resultRange is null)
{
intRetVal = 0;
}
else
{
intRetVal = resultRange.Row;
}
return(intRetVal);
}
public string FindExcelData(string filePath, string SearchData)
{
Excel.Application objExcel;
Excel.Workbook objBook;
Excel._Worksheet objSheet;
Excel.Range rowRange;
SetupExcel(filePath, out objExcel, out objBook, out objSheet, out rowRange);
Excel.Range objRange = objSheet.get_Range("A1", rowRange);
Excel.Range currentFind = null;
Excel.Range firstFind = null;
string location = "";
currentFind = objRange.Find(SearchData, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);
while (currentFind != null)
{
if (firstFind == null)
{
firstFind = currentFind;
}
else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1) == firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
{
break;
}
location = (firstFind.Row).ToString();
}
ReleaseExcelObject(filePath, objExcel, objBook, objSheet);
return(location);
}
public int[] FindDataColumns(string findString, int searchRow = 1)
{
List <int> retList = new List <int>();
if (string.IsNullOrEmpty(findString))
{
return(null);
}
Range searchRange = SetRange(searchRow, 1, searchRow, Worksheet.Columns.Count);
Range currentFind = searchRange.Find(What: findString, LookIn: XlFindLookIn.xlFormulas, LookAt: XlLookAt.xlWhole, SearchDirection: XlSearchDirection.xlNext, MatchCase: true);
Range firstFind = null;
while (currentFind != null)
{
if (firstFind == null)
{
firstFind = currentFind;
}
retList.Add(currentFind.Column);
currentFind = searchRange.FindNext(currentFind);
if (currentFind.get_Address() == firstFind.get_Address())
{
break;
}
}
Cleanup.ReleaseObject(currentFind);
Cleanup.ReleaseObject(firstFind);
Cleanup.ReleaseObject(searchRange);
return(retList.ToArray());
}
/// <summary>
///
/// </summary>
/// <param name="pColNameBegin"></param>
/// <param name="pColNameFinish"></param>
/// <param name="pSearchString"></param>
/// <returns></returns>
public int searchRowIndexInColRange(string pColNameBegin, string pColNameFinish, string pSearchString)
{
int rowIndex = -1;
string strColRange = string.Format("{0}:{1}", pColNameBegin, pColNameFinish);
Excel.Range colRange = xlWorkSheet.Columns[strColRange];//get the range object where you want to search from
Excel.Range resultRange = colRange.Find(What: pSearchString,
LookIn: Excel.XlFindLookIn.xlValues,
LookAt: Excel.XlLookAt.xlPart,
SearchOrder: Excel.XlSearchOrder.xlByRows,
SearchDirection: Excel.XlSearchDirection.xlNext,
MatchCase: false,
MatchByte: Type.Missing,
SearchFormat: Type.Missing);// search searchString in the range, if find result, return a range
if (!(resultRange is null))
{
rowIndex = resultRange.Row;
}
Marshal.ReleaseComObject(colRange);
Marshal.ReleaseComObject(resultRange);
return(rowIndex);
}
private static int findCodeRowIndex(Excel.Range xlRange, string search)
{
int code = -1;
try
{
var currentFind = xlRange.Find(search,
xlRange.Cells[1, 1],
Excel.XlFindLookIn.xlValues,
Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows,
Excel.XlSearchDirection.xlNext,
false,
false,
false);
code = currentFind.Row;
}
catch {
}
return(code);
// string sAddress = currentFind.Row.ToString();//.Address;
//~~> Display the found Address
//MessageBox.Show(sAddress).ToString();
}
public List <int> findConditionIndex(string unKnowCardNumber, int unknowCardCol)
{
string unknowCardNumberCol = GetExcelColumnName(unknowCardCol);
List <int> tempIndex = new List <int>();
Excel.Range currentFind = null;
Excel.Range firstFind = null;
Excel.Range unknowCardNumber = myExcel.get_Range(unknowCardNumberCol + ":" + unknowCardNumberCol);
currentFind = unknowCardNumber.Find(unKnowCardNumber, System.Reflection.Missing.Value,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
System.Reflection.Missing.Value, System.Reflection.Missing.Value);
while (currentFind != null)
{
// 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(Excel.XlReferenceStyle.xlA1)
== firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
{
break;
}
tempIndex.Add(currentFind.Row);
currentFind = unknowCardNumber.FindNext(currentFind);
}
return(tempIndex);
}
public void findAndHighlightValue(Excel.Range searchRange, String searchTerm, System.Drawing.Color color)
{
Excel.Range currentFind = null;
Excel.Range firstFind = null;
currentFind = searchRange.Find(searchTerm, Missing.Value,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Missing.Value, Missing.Value);
int i = 1;
while (currentFind != null)
{
// 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(Excel.XlReferenceStyle.xlA1)
== firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
{
break;
}
currentFind.Rows.Font.Color = System.Drawing.ColorTranslator.ToOle(color);
currentFind.Rows.Font.Bold = true;
currentFind = searchRange.FindNext(currentFind);
i++;
}
}
public static string DeleteRowsFromTable(this Excel.Worksheet sheet, string tableName, bool refresh)
{
Excel.Range primaryKeyColumnRange = null;
Excel.Range columnRange = null;
Excel.Range sheetCellsRange = null;
string primaryKey = string.Empty;
string sql = string.Empty;
Excel.Range deletedRows = null;
double deletedRowNum;
bool rowsDeleted = false;
try
{
deletedRows = sheet.Range["A:A"].SpecialCells(Excel.XlCellType.xlCellTypeBlanks).EntireRow;
//got at least 1 empty row
foreach (Excel.Range row in deletedRows)
{
deletedRowNum = sheet.Application.WorksheetFunction.CountA(row);
if (deletedRowNum == 0)
{
rowsDeleted = true;
}
}
}
catch (System.Runtime.InteropServices.COMException e)
{
rowsDeleted = false;
}
if (rowsDeleted == true | refresh == true)
{
primaryKey = sheet.PrimaryKey();
columnRange = sheet.Range["A1:CV1"];
sheetCellsRange = sheet.Cells;
primaryKeyColumnRange = columnRange.Find(primaryKey, LookAt: Excel.XlLookAt.xlWhole);
object[,] pkValues = (object[, ])sheet.Columns[primaryKeyColumnRange.Column].Cells.Value;
List <string> primaryKeyValues = pkValues.Cast <object>().ToList().ConvertAll(x => Convert.ToString(x));
primaryKeyValues.RemoveAt(0);
primaryKeyValues.RemoveAll(str => String.IsNullOrEmpty(str));
string primaryKeyValuesJoined = string.Join(",", primaryKeyValues);
primaryKeyValuesJoined = "'" + primaryKeyValuesJoined.Replace(",", "','") + "'";
if (refresh == false)
{
sql = "Delete from " + tableName + " Where " + primaryKey + " NOT IN( " + primaryKeyValuesJoined + ")";
}
else if (refresh == true)
{
sql = "Select " + primaryKey + " from " + tableName + " Where " + primaryKey + " NOT IN( " + primaryKeyValuesJoined + ")";
}
}
//Debug.WriteLine(sql);
return(sql);
}
// A9: Number of classroom teachers by teaching level of education, employment status, type of institution and sex
void sheetA9(Excel.Application excelApp, SqlConnection sqlConn, string year, string country)
{
//Constant references for columns and rows
const int FEMALE_OFFSET = 1; //row offset
const int PUBLIC = 14;
const int PRIVATE = 17;
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets["A9"];
workSheet.Activate();
Excel.Range usedRange = workSheet.UsedRange;
Func <string, int> getCol = null;
getCol = n => usedRange.Find(n).Column;
getCol.Memoize();
SqlCommand cmd = new SqlCommand(
@"select ISCED, SCHOOLTYPE, GENDER, sum(COUNT) as COUNT
from #TeacherBaseTable
group by ISCED, SCHOOLTYPE, GENDER",
sqlConn);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
string isced = rdr.GetString(0);
string schoolType = rdr.GetString(1);
string gender = rdr.GetString(2);
int count = rdr.GetInt32(3);
Console.WriteLine(String.Format("{0}, {1}, {2}, {3}", isced, gender, schoolType, count.ToString()));
int rowOffset = gender == "M" ? 0 : FEMALE_OFFSET;
int row = schoolType == "PUBLIC" ? PUBLIC : PRIVATE + rowOffset;
List <string> columns = new List <string>();
if (isced == "ISCED 24" || isced == "ISCED 34")
{
columns.Add("ISCED 24+34");
columns.Add(isced.Substring(0, 7));
}
else if (isced == "ISCED 25" || isced == "ISCED 35")
{
columns.Add("ISCED 25+35");
columns.Add(isced.Substring(0, 7));
}
else
{
columns.Add(isced);
}
foreach (string column in columns)
{
workSheet.Cells[row, getCol(column)] = workSheet.get_Range(helpers.GetCellAddress(getCol(column), row)).Value2 + count;
}
}
}
}
private void GetMergedAreas()
{
_mergedAreas = new ArrayList();
if (_target.Count == 1)
{
Reportor.Report("只选择了一个单元格，自动将搜寻区域拓展至其所在的整张工作表");
_target = _target.Worksheet.UsedRange;//这样的设定会使我们开发出更便于使用的VSTO
}
_application.FindFormat.MergeCells = true;
Excel.Range result = _target.Find(What: "", After: _target.Cells[1, 1], SearchFormat: true);
Excel.Range firstResult = result;
if (firstResult == null)
{
Reportor.Report("没有发现合并单元格！");
_mergedAreas = null;
return;
}
else
{
//卧槽，还是要else，太坑了。随便查找一下，如果只选中一块合并单元格，竟然是会跳出当前选区的……
//excel这种设定，倒也合理，直接把整个合并单元格区域当成起点，跑下一段去了。但是，它跟vba不一致啊……
if (firstResult.Row > (_target.Row + _target.Rows.Count - 1))
{
_mergedAreas.Add(_target);
Found.Invoke(this, null);
return;
}
}
Excel.Range mergedArea = firstResult.MergeArea;
int t = 0;
do
{
if (_cancellationToken.IsCancellationRequested)
{
return;
}
t++;
Reportor.Report($"搜寻中，已找到{t}处合并区域，总进度未知...");
_mergedAreas.Add(mergedArea);
result = _target.Find(What: "", After: result, SearchFormat: true);;//这里的接龙很巧妙，但也很坑。我还尝试着用FindNext，但是出现了一点问题。
mergedArea = result.MergeArea;
} while (mergedArea != null && mergedArea.Cells[1, 1].Address != firstResult.Address);
Reportor.Report($"搜寻完毕，共发现{t}处合并区域");
Found?.Invoke(this, null);
}
/// <summary>
/// 保存Excel文件
/// </summary>
public Excel.Range FindFirstRange(Excel.Range xlRange, string FindText)//查找//没有测试
{
//查找第一个满足的区域
//Search for the first match
Excel.Range firstFind = null;
firstFind = xlRange.Find(FindText, Missing.Value, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Missing.Value, Missing.Value);
return(firstFind); //如果没找到，返回空
}
public BOM getQuantityFromInvRepforBomComponents(BOM bom, string column)
{
//int number_of_rows = ws.UsedRange.Rows.Count;
column = column + ":" + column;
int[] columns = { 12, 15, 18, 33, 36 }; //brojevi kolona koje treba da saberemo
for (int i = 0; i < bom.item.Length; i++)
{
string item = bom.item[i].name;
_Excel.Range colRange = ws.Columns[column];//get the range object where you want to search from
_Excel.Range resultRange = colRange.Find(
What: item,
LookIn: _Excel.XlFindLookIn.xlValues,
LookAt: _Excel.XlLookAt.xlWhole,
SearchOrder: _Excel.XlSearchOrder.xlByRows,
SearchDirection: _Excel.XlSearchDirection.xlNext
);
if (resultRange == null)
{
//Program.mainForm.addToErrorList = "Did not found " + item + " in PO - column " + column.Substring(1);
}
else
{
_Excel.Range firstRange = resultRange;
while (resultRange != null)
{
for (int j = 0; j < columns.Length; j++) //columns[j]////l,o,r,u,ag,aj kolona = definisani brojevi kolona u nizu u vrhu funkcije
{
if (ws.Cells[resultRange.Row, columns[j]].Value2 != null) //sabiramo ako nisu null
{
bom.item[i].stockqty += ws.Cells[resultRange.Row, columns[j]].Value2;
}
}
_Excel.Range temp = resultRange;
resultRange = colRange.FindNext(temp);
if (resultRange.Address == firstRange.Address)
{
resultRange = null;
}
}
}
}
return(bom);
}
private void PrivateGetSpecificDataFromExcelDatabaseMethod(string ERID)
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Visible = false;
string excelDBFilePath = @"C:\Users\14025\Documents\File Consultants\ImpList.xls";
ExcelApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(excelDBFilePath);
ExcelApp.Visible = false;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorksheet = ExcelWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range colRange = ExcelWorksheet.Columns["B:B"];//get the range object where you want to search from
MessageBox.Show("ERID inside PrivateGetSpecificDataFromExcelDatabaseMethod: " + ERID);
string searchString = ERID;
ExcelApp.Visible = false;
Microsoft.Office.Interop.Excel.Range resultRange = colRange.Find(
What: searchString,
LookIn: Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
LookAt: Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
SearchOrder: Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
SearchDirection: Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext
);// search searchString in the range, if find result, return a range
if (ERID == "")
{
MessageBox.Show("ERID " + searchString + " not provided.");
}
else
{
MessageBox.Show("ERID inside else statement" + ERID);
string[] row = resultRange.EntireRow.Parse();
foreach (string cell in row)
{
MessageBox.Show(cell);
}
ExcelApp.Quit();
}
}
public int FindLastUsedColumn(int searchRow = 1)
{
Range searchRange = SetRange(searchRow, 1, searchRow, Worksheet.Columns.Count);
Range lastCell = searchRange.Find(What: "*", After: Worksheet.Cells[searchRow, 1], LookIn: XlFindLookIn.xlFormulas, LookAt: XlLookAt.xlWhole, SearchDirection: XlSearchDirection.xlPrevious, MatchCase: true);
int retInt = lastCell == null ? 1 : lastCell.Column;
Cleanup.ReleaseObject(lastCell);
Cleanup.ReleaseObject(searchRange);
return(retInt);
}
/*void findInWorksheet(Excel.Range range, string searchTerm)
* {
* Excel.Range currentFind = null;
* Excel.Range firstFind = null;
* int count = 0;
*
* ArrayList readingTimes = new ArrayList();
*
*
* //the problem is that the date is matched in columns B, C, and AG, so we get three matches for every patient arrival.
* //we need to restrict the range to column B
* range.va
*
*
* range = (Excel.Range)_sheet.Cells[2, 35000];
*
* //
* firstFind = range.Find(searchTerm, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, Type.Missing,Type.Missing, Type.Missing);
* if (firstFind != null)
* {
* count++;
* //string firstFindString = (string)firstFind.Cells[firstFind.Row, Columns.ADT_ARRIVAL_TIME].Value;
* Console.WriteLine(firstFind.Address);
* //what was the read time for this event? It's in column Q
* //readingTimes.Add(sheetRange.Cells[firstFind.Row, "Q"].Value);
* currentFind = range.FindNext(firstFind);
* while ((currentFind != null) & (currentFind.Address != firstFind.Address))
* {
* currentFind = range.FindNext(currentFind);
* if (currentFind.Address != firstFind.Address)
* {
* //readingTimes.Add(sheetRange.Cells[currentFind.Row, "Q"].Value);
* count++;
* Console.WriteLine(currentFind.Address);
* }
* }
* }
* Console.WriteLine("Number of patients on " + searchTerm + ": " + count);
*
* double sum = 0;
* int count = 0;
* foreach (Object time in readingTimes)
* {
* count++;
* sum += (double)time;
* }
* Console.WriteLine(sum / count);
*
* }*/
public void findInWorksheet(string searchTerm)
{
Excel.Range range = null;
Excel.Range currentFind = null;
Excel.Range firstFind = null;
int count = 0;
ArrayList readingTimes = new ArrayList();
//restrict range to our desired search column
range = _sheet.Columns[Columns.ADT_ARRIVAL_DATE];
firstFind = range.Find(searchTerm, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, Type.Missing, Type.Missing, Type.Missing);
if (firstFind != null)
{
if (_sheet.Cells[firstFind.Row, Columns.ATTND_PROV_NAME].Value == Physicians.ruiz)
{
count++;
displayFindResult(firstFind);
}
currentFind = range.FindNext(firstFind);
if (_sheet.Cells[currentFind.Row, Columns.ATTND_PROV_NAME].Value == Physicians.ruiz)
{
count++;
displayFindResult(currentFind);
}
if (currentFind != null)
{
if (_sheet.Cells[currentFind.Row, Columns.ATTND_PROV_NAME].Value == Physicians.ruiz)
{
count++;
displayFindResult(currentFind);
}
}
while ((currentFind != null) & (currentFind.Address != firstFind.Address))
{
currentFind = range.FindNext(currentFind);
if (currentFind.Address != firstFind.Address)
{
if ((string)_sheet.Cells[currentFind.Row, Columns.ATTND_PROV_NAME].Value == Physicians.ruiz)
{
count++;
displayFindResult(currentFind);
}
}
}
}
Console.WriteLine("Total, " + count);
}
public void Populate(string iExcelFile)
{
Excel.Application xlApp = null;
Excel.Workbook xlWorkbook = null;
Excel.Worksheet xlWorksheet = null;
try
{
xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Open(iExcelFile);
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets["MainSheet"];
//iterate columns
Excel.Range partNumberColumn = xlWorksheet.UsedRange.Columns[PART_NUMBER_INDEX];
//the only constrain here is that the part name should be unique+
Excel.Range partCell = partNumberColumn.Find(Data.PartNumber);
if (partCell != null)
{
Excel.Range thisRow = xlWorksheet.UsedRange.Rows[partCell.Row];
//::refactor:: is ToString() Needed?
thisRow.Cells[MATIERAL_COST_INDEX].Value = Data.MaterialCost.ToString();
thisRow.Cells[TOTAL_COST_INDEX].Value = Data.TotalCost.ToString();
thisRow.Cells[SETUP_COST_INDEX].Value = Data.SetupCost.ToString();
thisRow.Cells[TOOLING_COST_INDEX].Value = Data.ToolingCost.ToString();
thisRow.Cells[COOLING_TIME_INDEX].Value = Data.CoolingTime.ToString();
xlWorkbook.Save();
}
}
catch (Exception ex)
{
Console.WriteLine("exception");
Console.WriteLine(ex.Message);
}
finally
{
if (xlWorkbook != null)
{
xlWorkbook.Close(false, Type.Missing, Type.Missing);
}
if (xlApp != null)
{
xlApp.Quit();
}
//Marshal.FinalReleaseComObject(range1);
//Marshal.FinalReleaseComObject(range2);
Marshal.FinalReleaseComObject(xlWorksheet);
Marshal.FinalReleaseComObject(xlWorkbook);
//Marshal.FinalReleaseComObject(xlWorkBooks);
Marshal.FinalReleaseComObject(xlApp);
}
}
public void getQuantityFromOP_POforAssembly(List <Assembly> assembly, string column)
{
column = column + ":" + column;
for (int i = 0; i < assembly.Count; i++)
{
string item = assembly[i].name;
_Excel.Range colRange = ws.Columns[column];//get the range object where you want to search from
_Excel.Range resultRange = colRange.Find(
What: item,
LookIn: _Excel.XlFindLookIn.xlValues,
LookAt: _Excel.XlLookAt.xlWhole,
SearchOrder: _Excel.XlSearchOrder.xlByRows,
SearchDirection: _Excel.XlSearchDirection.xlNext
);
if (resultRange == null)
{
///Program.mainForm.addToErrorList = "Did not found " + item + " in WIP - column " + column.Substring(1);
}
else
{
_Excel.Range firstRange = resultRange;
while (resultRange != null)
{
try
{
assembly[i].opPO_quantity += ws.Cells[resultRange.Row, 11].Value2;
}
catch
{
}
_Excel.Range temp = resultRange;
resultRange = colRange.FindNext(temp);
if (resultRange.Address == firstRange.Address)
{
resultRange = null;
}
}
}
}
}
/// <summary>
/// Do <paramref name="action"/> on each cell in <paramref name="range"/>.
/// </summary>
/// <param name="range">The range contains cells to action.</param>
/// <param name="action">The action to do on each cell.</param>
/// <param name="callback">Callback function before visiting each cell.</param>
public static void EnumerateCells(Excel.Range range, Action <Excel.Range> action, Action callback = null)
{
// Enumerate non-empty cells by Excel.Range.Find function.
// However the first found cell is not range.Cells[1] when cell is not empty.
// So range[1] is treated individually.
var cellsCount = range.Count;
Tuple <int, int> firstCellInfo, firstFoundCellInfo;
{
var firstCell = (Excel.Range)range.Cells[1];
firstCellInfo = new Tuple <int, int>(firstCell.Column, firstCell.Row);
if (!string.IsNullOrEmpty((string)firstCell.Text))
{
action(firstCell);
}
}
// The Following is to avoid Excel's bug,
// ie, range.Find("*") returns cell outside range when range.Count == 1.
if (range.Count == 1)
{
return;
}
Excel.Range foundCell = range.Find("*");
firstFoundCellInfo = new Tuple <int, int>(foundCell.Column, foundCell.Row);
if (foundCell == null)
{
return;
}
if (firstFoundCellInfo.Equals(firstCellInfo))
{
return;
}
action(foundCell);
Excel.Range newfoundCell = null;
for (int i = 0; i < cellsCount; i++)
{
callback?.Invoke();
newfoundCell = range.FindNext(foundCell);
var cellInfo = new Tuple <int, int>(newfoundCell.Column, newfoundCell.Row);
if (firstCellInfo.Equals(cellInfo) ||
firstFoundCellInfo.Equals(cellInfo))
{
break;
}
action(newfoundCell);
foundCell = newfoundCell;
}
}
public static void CopyToSummaryPanelExcel(Excel.Application app, List <SummaryPanel> panelList, string fileName)
{
if (!File.Exists(fileName))
{
throw new Exception("File " + fileName + " not exists");
}
Excel.Workbook workbook = app.Workbooks.Open(fileName);
Excel.Worksheet sheet = null;
foreach (Excel.Worksheet s in workbook.Sheets)
{
if (s.Name == "sanitary")
{
sheet = s;
}
}
if (sheet == null)
{
workbook.Close(false);
throw new Exception("Cannot find sheet : sanitary");
}
Excel.Range cityColumn = sheet.Cells[1, 1].EntireColumn;
Excel.Range yearRow = sheet.Cells[1, 3].End[Excel.XlDirection.xlDown].EntireRow;
yearRow.Select();
foreach (SummaryPanel sp in panelList)
{
var cityPinyin = KAO.cityTable.FirstOrDefault(x => x.Value == sp.city).Key.ToLower();
Excel.Range cityCell = cityColumn.Find(cityPinyin,
Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlNext,
false, Type.Missing, Type.Missing);
Excel.Range yearCell = yearRow.Find(sp.yearStr,
Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext,
false, Type.Missing, Type.Missing);
if (cityCell == null)
{
throw new Exception(string.Format("Cannot find city {0}{1}", sp.city, cityPinyin));
}
if (yearCell == null)
{
throw new Exception(string.Format("Cannot find year {0}", sp.yearStr));
}
Excel.Range pasteStartCell = sheet.Cells[cityCell.Row + 2, yearCell.Column];
for (int row = 0; row < sp.tableData.Count; row++)
{
for (int col = 0; col < sp.tableData[row].Length; col++)
{
pasteStartCell.Offset[row, col].Value = sp.tableData[row][col];
}
}
}
}
public BOM getQuantityFromSSforBomComponents(BOM bom, string column)
{
column = column + ":" + column;
for (int i = 0; i < bom.item.Length; i++)
{
string item = bom.item[i].name;
_Excel.Range colRange = ws.Columns[column];//get the range object where you want to search from
_Excel.Range resultRange = colRange.Find(
What: item,
LookIn: _Excel.XlFindLookIn.xlValues,
LookAt: _Excel.XlLookAt.xlWhole,
SearchOrder: _Excel.XlSearchOrder.xlByRows,
SearchDirection: _Excel.XlSearchDirection.xlNext
);
if (resultRange == null)
{
//Program.mainForm.addToErrorList = "Did not found " + item + " in SafetyStock - column " + column.Substring(1);
}
else
{
_Excel.Range firstRange = resultRange;
while (resultRange != null)
{
if (ws.Cells[resultRange.Row, 9].Value2 == null)
{
}
else
{
bom.item[i].safetystock += ws.Cells[resultRange.Row, 9].Value2;
}
_Excel.Range temp = resultRange;
resultRange = colRange.FindNext(temp);
if (resultRange.Address == firstRange.Address)
{
resultRange = null;
}
}
}
}
return(bom);
}
public String getValueForSearchKey(Excel.Worksheet ws, String key)
{
int keyColum = 1;
Excel.Range range1 = ws.Range["A:A"];
Excel.Range range2 = range1.Find(key);
string msg1 = range2.Count + " records found.";
int row = range2.Row;
string msg2 = "1st in row " + range2.Row;
string msg3 = ws.Cells[row, keyColum + 1].value;
return(msg3);
}
private string FindUserClass(Excel.Worksheet sheet)
{
Excel.Range currentFind = null;
Excel.Range firstFind = null;
string strTest = "";
Excel.Range range = sheet.Range["A1", "Y7"];
// You should specify all these parameters every time you call this method,
// since they can be overridden in the user interface.
currentFind = range.Find("UserClassName", Missing.Value,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, false, Missing.Value);
while (currentFind != null)
{
dynamic obj = currentFind.Column;
int col = (int)obj;
obj = currentFind.Row;
int row = (int)obj;
for (int i = 1; i < 5; i++)
{
//dynamic testout = xlWorkSheets[16].Cells(row+i, col);
dynamic testout = ((Excel.Range)sheet.Cells[row + i, col]);
// Keep track of the first range you find.
strTest = testout.text;
if (strTest != null && strTest != "")
{
currentFind = null; break;
}
}
currentFind = null;
//if (firstFind == null)
//{
// firstFind = currentFind;
//}
//// If you didn't move to a new range, you are done.
//else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1)
// == firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
//{
// break;
//}
//currentFind.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
//currentFind.Font.Bold = true;
//currentFind = range.FindNext(currentFind);
}
return(strTest);
}
private void Button_FindText(object sender, RoutedEventArgs e)
{
Excel.Range currentFind = null;
Excel.Range firstFind = null;
Excel.Worksheet ws = _xlApp.ActiveSheet;
Excel.Range Fruits = _xlApp.get_Range("E1", "G5");
// You should specify all these parameters every time you call this method,
// since they can be overridden in the user interface.
//currentFind = Fruits.Find(What : "apples", After: null,
// LookIn:Excel.XlFindLookIn.xlValues, LookAt:Excel.XlLookAt.xlPart,
// SearchOrder : Excel.XlSearchOrder.xlByRows, SearchDirection:Excel.XlSearchDirection.xlNext, MatchCase:false,
// MatchByte:null, SearchFormat:null);
//e Find(object What, object After, object LookIn, object LookAt, object SearchOrder, XlSearchDirection SearchDirection = XlSearchDirection.xlNext,
// object MatchCase = null, object MatchByte = null, object SearchFormat = null);
currentFind = Fruits.Find("apples", Type.Missing,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
Type.Missing, Type.Missing);
while (currentFind != null)
{
// 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(Excel.XlReferenceStyle.xlA1)
== firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
{
break;
}
currentFind.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
currentFind.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
currentFind.Font.Bold = true;
//sheet.get_Range("7:9,12:12,14:14", Type.Missing) // range of rows
//sheet.get_Range("7:9,12:12,14:14", Type.Missing) // range of rows
var row = currentFind.Row;
var col = currentFind.Column;
Excel.Range Numbers = ws.Range[ws.Cells[row, col + 1], ws.Cells[row, col + 1]];
// Excel.Range Numbers = ws.get_Range(ws.Cells[row, col+1], ws.Cells[row, col + 1]);
Numbers.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange);
currentFind = Fruits.FindNext(currentFind);
}
}
