/*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 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++;
}
}
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 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());
}
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 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);
}
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);
}
/// <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 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 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;
}
}
}
}
}
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);
}
}
public int getPatientsPerDay(DateTime date)
{
Excel.Range range = null;
Excel.Range currentFind = null;
Excel.Range firstFind = null;
int count = 0;
range = _sheet.Columns[Columns.ADT_ARRIVAL_DATE];
string dateString = date.ToString("M/d/yyyy");
firstFind = range.Find(dateString, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, Type.Missing, Type.Missing, Type.Missing);
if (firstFind != null)
{
count++;
displayFindResult(firstFind);
currentFind = range.FindNext(firstFind);
if (currentFind != null)
{
count++;
displayFindResult(currentFind);
}
while ((currentFind != null) & (currentFind.Address != firstFind.Address))
{
currentFind = range.FindNext(currentFind);
if (currentFind.Address != firstFind.Address)
{
count++;
displayFindResult(currentFind);
}
}
}
Console.WriteLine(dateString + ", " + count);
return(count);
}
//</Snippet49>
//---------------------------------------------------------------------
//<Snippet57>
private void DemoFind()
{
//<Snippet58>
Excel.Range currentFind = null;
Excel.Range firstFind = null;
//</Snippet58>
Excel.Range Fruits = Application.get_Range("A1", "B3");
// You should specify all these parameters every time you call this method,
// since they can be overridden in the user interface.
//<Snippet59>
currentFind = Fruits.Find("apples", missing,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
missing, missing);
//</Snippet59>
//<Snippet60>
while (currentFind != null)
//</Snippet60>
{
// Keep track of the first range you find.
//<Snippet61>
if (firstFind == null)
{
firstFind = currentFind;
}
//</Snippet61>
// If you didn't move to a new range, you are done.
//<Snippet62>
else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1)
== firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
{
break;
}
//</Snippet62>
//<Snippet63>
currentFind.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
currentFind.Font.Bold = true;
//</Snippet63>
//<Snippet64>
currentFind = Fruits.FindNext(currentFind);
//</Snippet64>
}
}
private IEnumerable <IExcelTemplateDetails> GetTemplateDetails(ExcelInterop.Worksheet sheetContainer)
{
var result = new List <IExcelTemplateDetails>();
var i = 0;
var searchedPattern = string.Format(TEMPLATE_START_FORMAT, "*");
ExcelInterop.Range range =
sheetContainer.Cells
.Find(searchedPattern, Type.Missing, ExcelInterop.XlFindLookIn.xlValues, ExcelInterop.XlLookAt.xlPart, ExcelInterop.XlSearchOrder.xlByRows, ExcelInterop.XlSearchDirection.xlNext, false);
if (null != range)
{
do
{
Match match = Regex.Match(range.Value, "<Template[ \\w]* Name='(\\w+)'.*");
if (match.Success)
{
var templateName = match.Groups[1].Value;
var startRowIndex = range.Row;
var startColIndex = range.Column;
var endCell =
sheetContainer.Cells
.Find(string.Format(TEMPLATE_END_FORMAT, templateName), Type.Missing, ExcelInterop.XlFindLookIn.xlValues, ExcelInterop.XlLookAt.xlPart, ExcelInterop.XlSearchOrder.xlByRows, ExcelInterop.XlSearchDirection.xlNext, false);
if (null != endCell)
{
var endRowIndex = endCell.Row;
var endColIndex = endCell.Column;
if (result.Any(_ => _.Name == templateName))
{
break;
}
var details = new ExcelTemplateDetails
{
Name = templateName,
StartLocation = new Point(startRowIndex, startColIndex),
EndLocation = new Point(endRowIndex, endColIndex)
};
result.Add(details);
}
range = range.FindNext(range);
}
}while (null != range);
}
return(result);
}
private static int CountWord(Excel.Range rng, string word)
{
int i = 0;
Excel.Range rng1 = rng.Find(word);
if (rng1 != null)
{
i++;
}
else
{
return(0);
}
string address = rng1.Address;
Console.WriteLine(address);
while (true)
{
rng1 = rng.FindNext(rng1);
if (rng1 == null)
{
return(i);
}
Console.WriteLine(rng1.Address);
if (rng1.Address == address)
{
return(i);
}
else
{
i++;
}
}
}
internal static List <string>[] RetrieveColumnByHeader(Excel.Worksheet sheet, string FindWhat)
{
Excel.Range rngHeader = sheet.Rows[1] as Excel.Range;
int rowCount = sheet.UsedRange.Rows.Count;
int columnCount = sheet.UsedRange.Columns.Count;
int index = 0;
Excel.Range rngResult = null;
string FirstAddress = null;
List <string>[] columnValue = new List <string> [columnCount];
rngResult = rngHeader.Find(What: FindWhat, LookIn: Excel.XlFindLookIn.xlValues,
LookAt: Excel.XlLookAt.xlWhole, SearchOrder: Excel.XlSearchOrder.xlByColumns, MatchCase: true);
if (rngResult != null)
{
FirstAddress = rngResult.Address;
Excel.Range cRng = null;
do
{
columnValue[index] = new List <string>();
for (int i = 1; i <= rowCount; i++)
{
cRng = sheet.Cells[i, rngResult.Column] as Excel.Range;
if (cRng.Value != null)
{
columnValue[index].Add(cRng.Value.ToString());
}
}
index++;
rngResult = rngHeader.FindNext(rngResult);
} while (rngResult != null && rngResult.Address != FirstAddress);
}
Array.Resize(ref columnValue, index);
return(columnValue);
}
private int CheckEmployeeID(Excel.Worksheet wshTEList, string sSearch)
{
//string[] aName = sName.Split(' ');
//string sSearch = aName[1] + ", " + aName[0];
Excel.Range currentFind = null;
Excel.Range firstFind = null;
int iId = 0;
Excel.Range oTEList = wshTEList.get_Range("A1", "C30");
// You should specify all these parameters every time you call this method,
// since they can be overridden in the user interface.
currentFind = oTEList.Find(sSearch, 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))
{
int iRow = currentFind.Row;
iId = (int)wshTEList.Cells[iRow, 1].Value;
break;
}
currentFind = oTEList.FindNext(currentFind);
}
return(iId);
}
public string[] find(string text, string adr1, string adr2, bool ADR = true)
{
Excel.Range area = appExcel.get_Range(adr1, adr2);
Excel.Range firstFind = null;
Excel.Range currentFind = null;
int cnt = 0;
string[] array = new string[1];
currentFind = area.Find(text);
while (currentFind != null)
{
if (firstFind == null)
{
firstFind = currentFind;
}
else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1) == firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
{
break;
}
Array.Resize <string>(ref array, ++cnt);
if (ADR)
{
array[cnt - 1] = addressDollarClear(currentFind.get_Address(Excel.XlReferenceStyle.xlA1));
}
else
{
array[cnt - 1] = currentFind.Text;
}
currentFind = area.FindNext(currentFind);
}
return(array);
}
public static List <Excel.Range> SearchAllValuesInRow(Excel.Worksheet worksheet, object rowIndex, object searchValue)
{
List <Excel.Range> results = new List <Excel.Range>();
bool hasNext = true;
Excel.Range searchRange = worksheet.Range[worksheet.Cells[rowIndex, 1], worksheet.Cells[rowIndex, worksheet.UsedRange.Columns.Count]];
Excel.Range tempCell = null;
try
{
tempCell = searchRange.Find(What: searchValue, LookIn: Excel.XlFindLookIn.xlValues, LookAt: Excel.XlLookAt.xlPart);
if (tempCell != null)
{
results.Add(tempCell);
do
{
tempCell = searchRange.FindNext(After: results.LastOrDefault());
if (results.First().Column != tempCell.Column)
{
results.Add(tempCell);
}
else
{
hasNext = false;
}
} while (hasNext);
}
}
catch (Exception)
{
throw;
}
return(results);
}
//Run through all the files in the directory, and find and replace
public void find_replace()
{
string path = "";
// prompt the user to select the folder path
using (var fbd = new FolderBrowserDialog())
{
DialogResult result = fbd.ShowDialog();
if (result == DialogResult.OK && !string.IsNullOrWhiteSpace(fbd.SelectedPath))
{
string[] files = Directory.GetFiles(fbd.SelectedPath);
path = fbd.SelectedPath;
System.Windows.Forms.MessageBox.Show("Files found: " + files.Length.ToString() + " @ path: " + path, "Message");
}
}
object m = Type.Missing;
// Interaction.Inputbox is a user prompt, the user chooses the string he/she would like to replace
string replace = Interaction.InputBox("Type the text you would like to replace", "Find text", "Default", -1, -1);
string replacement = Interaction.InputBox("Replace that text with", "Replace text", "Default", -1, -1);
var xlApp = new Microsoft.Office.Interop.Excel.Application();
// grab all the excel files at that directory
DirectoryInfo d = new DirectoryInfo(@path);
FileInfo[] listOfFiles_1 = d.GetFiles("*.xlsx").ToArray();
FileInfo[] listOfFiles_2 = d.GetFiles("*.xls").ToArray();
FileInfo[] listOfFiles_3 = d.GetFiles("*.xlsm").ToArray();
FileInfo[] listOfFiles_4 = d.GetFiles("*.xltx").ToArray();
FileInfo[] listOfFiles_5 = d.GetFiles("*.xltm").ToArray();
FileInfo[] listOfFiles_6 = d.GetFiles("*.xlt").ToArray();
// produce a list of files in an array
FileInfo[] listOfFiles = (listOfFiles_1.Concat(listOfFiles_2)).ToArray();
listOfFiles = (listOfFiles.Concat(listOfFiles_3)).ToArray();
listOfFiles = (listOfFiles.Concat(listOfFiles_4)).ToArray();
listOfFiles = (listOfFiles.Concat(listOfFiles_5)).ToArray();
listOfFiles = (listOfFiles.Concat(listOfFiles_6)).ToArray();
xlApp.DisplayAlerts = false;
// traverse through each file
int count = 0;
foreach (FileInfo file in listOfFiles)
{
var xlWorkBook = xlApp.Workbooks.Open(file.FullName);
string file_name = file.FullName.Remove(file.FullName.Length - 5);
string file_ext = file.FullName.Substring(file.FullName.Length - 4);
// traverse through each worksheet in each file
foreach (Excel.Worksheet xlWorkSheet in xlWorkBook.Worksheets)
{
// get the used range.
Excel.Range r = (Excel.Range)xlWorkSheet.UsedRange;
Excel.Range first = r.Find(replace, m, m, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, m, m, m);
if (first != null)
{
count++;
Excel.Range start = first;
do
{
start.Value = start.Value.Replace(replace, replacement);
count++;
start = r.FindNext(m);
// if file is xltx, has to save as to overwrite the new changes
if (file_ext == "xltx")
{
xlWorkBook.SaveAs(@file_name, Excel.XlFileFormat.xlOpenXMLTemplate,
missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
}
// if file is xltm, has to save as to overwrite the new changes
else if (file_ext == "xltm")
{
xlWorkBook.SaveAs(@file_name, Excel.XlFileFormat.xlOpenXMLTemplateMacroEnabled,
missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
}
else
{
xlWorkBook.Save();
}
}while (start != first && start != null);
}
/*
* // call the replace method to replace instances.
* bool success = (bool)r.Replace(
* replace,
* replacement,
* Excel.XlLookAt.xlPart,
* Excel.XlSearchOrder.xlByRows, false, m, m, m);
*/
}
xlWorkBook.Close();
}
MessageBox.Show(count + " replacements has been made");
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
private void AlterEdgeCases(Excel.Worksheet oWS)
{
var lastUsedRow = GetLastUsedRow(oWS);
//
// MCO
//
//Find edge cases
Excel.Range currentFind = null;
Excel.Range firstFind = null;
Excel.Range searchRange = oWS.get_Range("K2", "K" + lastUsedRow);
// You should specify all these parameters every time you call this method,
// since they can be overridden in the user interface.
currentFind = searchRange.Find("MCO", Missing.Value,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
Missing.Value, 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;
}
currentFind.FormulaR1C1 = "=LEFT(RC[-8],4)";
currentFind = searchRange.FindNext(currentFind);
}
//
// OMV
//
currentFind = null;
firstFind = null;
// You should specify all these parameters every time you call this method,
// since they can be overridden in the user interface.
currentFind = searchRange.Find("OMV", Missing.Value,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
Missing.Value, 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;
}
currentFind.FormulaR1C1 = "=LEFT(RC[-8],4)";
currentFind = searchRange.FindNext(currentFind);
}
//
// XCF
//
currentFind = null;
firstFind = null;
// You should specify all these parameters every time you call this method,
// since they can be overridden in the user interface.
currentFind = searchRange.Find("XCF", Missing.Value,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
Missing.Value, 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;
}
currentFind.FormulaR1C1 = "=LEFT(RC[-8],5)";
currentFind = searchRange.FindNext(currentFind);
}
//
// BSL
//
currentFind = null;
firstFind = null;
// You should specify all these parameters every time you call this method,
// since they can be overridden in the user interface.
currentFind = searchRange.Find("BSL", Missing.Value,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
Missing.Value, 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;
}
currentFind.FormulaR1C1 = "=LEFT(RC[-8],4)";
currentFind = searchRange.FindNext(currentFind);
}
//
// BRE
//
currentFind = null;
firstFind = null;
// You should specify all these parameters every time you call this method,
// since they can be overridden in the user interface.
currentFind = searchRange.Find("BRE", Missing.Value,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
Missing.Value, 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;
}
currentFind.FormulaR1C1 = "=LEFT(RC[-8],4)";
currentFind = searchRange.FindNext(currentFind);
}
//
// BDB
//
currentFind = null;
firstFind = null;
// You should specify all these parameters every time you call this method,
// since they can be overridden in the user interface.
currentFind = searchRange.Find("BDB", Missing.Value,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
Missing.Value, 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;
}
currentFind.FormulaR1C1 = "=LEFT(RC[-8],6)";
currentFind = searchRange.FindNext(currentFind);
}
}
//Devuelve en un array de ints los valores sumados de cada profesor
private void getTotal(int filastart, string formato)
{
object misValue = System.Reflection.Missing.Value;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook;
Excel._Worksheet xlWorksheet;
try
{
if (formato == "dia")
{
File.Copy(Global.DiarioServer + Path.GetFileName(this.Archivo) + ".xlsx", Global.Diario + Path.GetFileName(this.Archivo) + ".xlsx", true);
}
else if (formato == "mes")
{
File.Copy(Global.MensualServer + Path.GetFileName(this.Archivo) + ".xlsx", Global.Mensual + Path.GetFileName(this.Archivo) + ".xlsx", true);
}
}
catch (System.IO.FileNotFoundException)
{
}
xlWorkbook = xlApp.Workbooks.Open(this.Archivo, Type.Missing, true, Type.Missing, Global.Connect());
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);
Excel.Range c1 = xlWorksheet.Cells[filastart, 1];
Excel.Range c2 = xlWorksheet.Cells[200, 200];
Excel.Range xlRange = xlWorksheet.get_Range(c1, c2);
int rowNumber = xlRange.Rows.Count + 1;
int lastrow = xlWorksheet.Cells.Find("*", System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious,
false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
guardarCierreParcial(this.CierreParcialDia, lastrow);
foreach (var profesor in this.ProfesoresCortos)
{
Excel.Range firstFind = null;
Excel.Range fila = xlRange.Find(profesor.ProfeNombre,
Type.Missing, /* After */
Excel.XlFindLookIn.xlValues, /* LookIn */
Excel.XlLookAt.xlWhole, /* LookAt */
Excel.XlSearchOrder.xlByColumns, /* SearchOrder */
Excel.XlSearchDirection.xlNext, /* SearchDirection */
true, /* MatchCase */
Type.Missing, /* MatchByte */
Type.Missing /* SearchFormat */);
while (fila != null)
{
if (firstFind == null)
{
firstFind = fila;
}
else if (fila.get_Address(Excel.XlReferenceStyle.xlA1)
== firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
{
break;
}
var filabien = fila.Row.ToString();
profesor.Total += Convert.ToInt32(xlRange.Cells[filabien, "F"].Value);
//MessageBox.Show(filabien);
fila = xlRange.FindNext(fila);
}
}
xlApp.DisplayAlerts = false;
xlWorkbook.Close();
xlApp.Quit();
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
}
// edit to suit needs
// perhaps should return a set of rows/addresses?
internal System.Data.DataTable DemoFind()
{
System.Data.DataTable dt = new System.Data.DataTable();
//this seems messy/unwieldy to add each column like this... mark for future consideration
dt.Columns.Add("Date Submitted");
dt.Columns.Add("Effective Date");
dt.Columns.Add("Insured Name");
dt.Columns.Add("U/W");
dt.Columns.Add("A/U");
dt.Columns.Add("Broker");
dt.Columns.Add("Broker Name");
dt.Columns.Add("Retail Broker");
dt.Columns.Add("Practice Policy");
dt.Columns.Add("Designated Project");
dt.Columns.Add("Project Address");
dt.Columns.Add("GC");
dt.Columns.Add("Owner's Interest");
dt.Columns.Add("Owner/GC");
dt.Columns.Add("OCP");
dt.Columns.Add("Trade");
dt.Columns.Add("Products");
dt.Columns.Add("Other");
dt.Columns.Add("Declined/Blocked/DEAD");
dt.Columns.Add("Indication");
dt.Columns.Add("SNIC USIC CBIC CBSIC");
dt.Columns.Add("PRIMARY Quoted (Yes)");
dt.Columns.Add("EXCESS Quoted (Yes)");
dt.Columns.Add("Date Quote Sent");
dt.Columns.Add("BOUND");
dt.Columns.Add("BOUND Policy Number");
dt.Columns.Add("BOUND Policy Premium");
dt.Columns.Add("TRIA");
dt.Columns.Add("XPCO");
dt.Columns.Add("OL&T");
dt.Columns.Add("Other AP's");
dt.Columns.Add("TOTAL PREMIUM CHARGED");
dt.Columns.Add("In-House Loss Fee");
dt.Columns.Add("BOUND Policy Sent to Broker");
dt.Columns.Add("NOTES");
Excel.Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range currentFind = null;
Excel.Range firstFind = null;
List <Excel.Range> matchingRows = new List <Excel.Range>();
// last is last used range before cells get empty
Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = sheet.get_Range("A1", last);
Excel.Range InsuredNames = range.Columns["C"];
// keep these two for later
int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;
// this method searches through InsuredNames (column C) for the first param
currentFind = InsuredNames.Find("construction", missing,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
missing, missing);
while (currentFind != null)
{
// Keep track of the first range you find.
if (firstFind == null)
{
firstFind = currentFind;
// use EntireRow to return entire row of cell containing "construction"
// Cell.EntireRow.Row to get index? need a way to launch datagridview...
}
// 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;
}
// action taken after match(es) found
matchingRows.Add(currentFind.EntireRow);
currentFind.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
currentFind.Font.Bold = true;
// for (int i; i < lastUsedRow; i++)
//{
//Form1.dataGridView1.Rows[i].Cells["Column1"].Value = sheet.Cells[i + 1, 1].Value;
//dataGridView1.Rows[i].Cells["Column2"].Value = sheet.Cells[i + 1, 2].Value;
//dataGridView1.Rows.Add(sheet.Cells[i + 1, 1].Value, sheet.Cells[i + 1, 2].Value);
//}
// at end, before executing FindNext, CREATE ARRAY CONTAINING ALL VALUES OF ROW
// PASS THIS ARRAY INTO FORM BY CALLING METHOD IN FORM THAT ACCEPTS IN PARAMS/ CREATES NEW ROW IN DT WHEN CALLED
// FindNext uses previous search settings to repeat search
currentFind = InsuredNames.FindNext(currentFind);
// necessary to loop thru FindNext until finished with range?
}
//could be simplified/consolidated into while(currentFind) loop - could remove unnecessary matchingRows array?
for (int r = 0; r < matchingRows.Count; r++)
{
System.Data.DataRow dtRow = dt.NewRow();
for (int c = 1; c < 35; c++)
{
dtRow[c] = sheet.Cells[matchingRows[r].Row, c].Value;
}
dt.Rows.Add(dtRow);
}
return(dt);
}
