The following program takes in an excel file with product IDs (beginning with MT or MU) in the 1st column. It then goes through purchase orders (POs) that contain those product IDs in folders by supplier which have sub folders by year. The user enters the folder path and specifies the years it wants to search. The program then returns the most recent supplier, price and date in columns next to the product ID in the original spreadsheet specified by the user. The program takes about 3 to 4 hours to run for 10,000 product IDs and a years worth of POs (hundreds of POs). Sometimes it finishes (there's a message box that shows done when finished), and sometimes it doesn't given the same exact inputs. There are no errors, but CPU usage suddenly goes from about 40% to 1% Anyone know why?
string filepath = "";
string folderpath = "";
private void button1_Click(object sender, EventArgs e)
{
//New Excel App
Excel._Application oApp = new Excel.Application();
oApp.Visible = true;
//Opens Worksheet to be updated
Excel.Workbook oWorkbook = oApp.Workbooks.Open(filepath);
Excel.Worksheet oWorksheet = oWorkbook.Worksheets["Sheet1"];
//Takes all MT/MU numbers from first column of worksheet needing updating and puts them into a string
Excel.Range firstColumn = oWorksheet.UsedRange.Columns[1];
System.Array myvalues = (System.Array)firstColumn.Cells.Value;
string[] strArray = myvalues.OfType<object>().Select(o => o.ToString()).ToArray();
//Specify what year to begin and end looking at POs
int beginYear = Convert.ToInt32(textBox2.Text);
beginYear = int.Parse(textBox2.Text);
int endYear = Convert.ToInt32(textBox3.Text);
endYear = int.Parse(textBox3.Text);
int count = 0;
List<string> yearList = new List<string>();
while (count <= endYear-beginYear )
{
int addYear = beginYear + count;
string addYearString = addYear.ToString();
yearList.Add(addYearString);
count++;
}
string[] years = yearList.ToArray();
foreach (string year in years)
{
//Creates array of all excel files existing in path (including subdirectories) in folder chosen by user
IEnumerable<string> files =
from f in Directory.GetFiles(folderpath, "*.xls", SearchOption.AllDirectories)
where Path.GetDirectoryName(f).Contains(year)
select f;
foreach (string file in files)
{
//Opens file
Excel._Application oApp2 = new Excel.Application();
oApp2.Visible = false;
Excel.Workbook PO = oApp2.Workbooks.Open(file);
Excel.Worksheet oWorksheet2 = PO.Worksheets["Sheet1"];
foreach (string item in strArray)
{
Excel.Range currentFind = null;
string newestDateSoFar = "01/01/1900";
DateTime newestDateTimeSoFar = Convert.ToDateTime(newestDateSoFar);
object misValue = System.Reflection.Missing.Value;
Excel.Range xlRange = oWorksheet2.get_Range("C1");
//Looks through column for MT/MU number
currentFind = xlRange.EntireColumn.Find(item,
misValue, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlNext,
true, misValue, misValue);
//If there is a match in the PO
if (currentFind != null)
{
//Get Date on PO
string currentDate = null;
DateTime currentDateTime = Convert.ToDateTime(currentDate);
if (oWorksheet2.Cells[6, 6].Value != null)
{
currentDate = oWorksheet2.Cells[6, 6].Value.ToString();
currentDateTime = Convert.ToDateTime(currentDate);
}
Excel.Range xlRange2 = oWorksheet.get_Range("A1");
//Looks through column of worksheet needing updating for MT/MU number, so can compare date
Excel.Range needsUpdatingFind = xlRange2.EntireColumn.Find(item,
misValue, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlNext,
true, misValue, misValue);
int dateColumn = Convert.ToInt32(textBox4.Text);
dateColumn = int.Parse(textBox4.Text);
if (needsUpdatingFind.get_Offset(0, dateColumn-1).Value != null)
{
newestDateSoFar = needsUpdatingFind.Offset[0, dateColumn-1].Value.ToString();
newestDateTimeSoFar = Convert.ToDateTime(newestDateSoFar);
}
//If Date on PO is most recent, get information that needs updating
if (currentDateTime > newestDateTimeSoFar || needsUpdatingFind.get_Offset(0, dateColumn - 1).Value == null)
{
//Gets assosciated price and sets it in worksheet that needs updating
string price = null;
int priceColumn = Convert.ToInt32(textBox6.Text);
priceColumn = int.Parse(textBox6.Text);
if (currentFind.get_Offset(0, 2).Value != null)
{
price = currentFind.get_Offset(0, 2).Value.ToString();
needsUpdatingFind.Offset[0, priceColumn-1].Value = price;
}
else if (currentFind.get_Offset(-1, 2).Value != null)
{
price = currentFind.get_Offset(-1, 2).Value.ToString();
needsUpdatingFind.Offset[0, priceColumn-1].Value = price;
}
else if (currentFind.get_Offset(-2, 2).Value != null)
{
price = currentFind.get_Offset(-2, 2).Value.ToString();
needsUpdatingFind.Offset[0, priceColumn-1].Value = price;
}
else if (currentFind.get_Offset(-3, 2).Value != null)
{
price = currentFind.get_Offset(-3, 2).Value.ToString();
needsUpdatingFind.Offset[0, priceColumn-1].Value = price;
}
else if (currentFind.get_Offset(-4, 2).Value != null)
{
price = currentFind.get_Offset(-4, 2).Value.ToString();
needsUpdatingFind.Offset[0, priceColumn-1].Value = price;
}
else if (currentFind.get_Offset(-5, 2).Value != null)
{
price = currentFind.get_Offset(-5, 2).Value.ToString();
needsUpdatingFind.Offset[0, priceColumn-1].Value = price;
}
//Gets assosciated supplier and sets it in worksheet that needs updating
int supplierColumn = Convert.ToInt32(textBox5.Text);
supplierColumn = int.Parse(textBox5.Text);
string supplier = oWorksheet2.Cells[6, 2].Value.ToString();
needsUpdatingFind.Offset[0, supplierColumn-1].Value = supplier;
//Gets assosciated PO date and sets it in worksheet that needs updating
needsUpdatingFind.Offset[0, dateColumn-1].Value = currentDateTime.ToString();
//Resets current find
currentFind = null;
needsUpdatingFind = null;
}
while (Marshal.ReleaseComObject(xlRange2) != 0) { }
xlRange2 = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
while (Marshal.ReleaseComObject(xlRange) != 0) { }
xlRange = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
//Closes PO
object missing = System.Reflection.Missing.Value;
PO.Close(false, missing, missing);
//Quits Excel
oApp2.Quit();
//Manual disposal because of COM
while (Marshal.ReleaseComObject(oApp2) != 0) { }
while (Marshal.ReleaseComObject(PO) != 0) { }
while (Marshal.ReleaseComObject(oWorksheet2) != 0) { }
oApp2 = null;
PO = null;
oWorksheet2 = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
MessageBox.Show("Done");
}
private void button2_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
filepath = openFileDialog1.FileName;
textBox1.Text = filepath;
}
}
private void button3_Click(object sender, EventArgs e)
{
FolderBrowserDialog openFileDialog2 = new FolderBrowserDialog();
if (openFileDialog2.ShowDialog() == DialogResult.OK)
{
folderpath = openFileDialog2.SelectedPath;
textBox7.Text = folderpath;
}
}
}
}
