I have created a windows app that processes a set of excel files from a folder looking for some selective information from each of them. Later, it stores all those information into a new excel sheet. It's kind of a report generation tool. I am having a hard time in attempting to reduce the total Excecution time for this app. Currently, it takes around 45-50 secs overall to complete one cycle of successful execution. The code is similar to this:
P.S. I cannot share the actual code to due to restriction policies. But the code shown below is suffcient enough to provide some solution.
#region master_tracking_sheet
public string[] xlsfiles;
public string[] personList = new string[35];
public string[,] excelSheet_det = new string[35, 6];
private void buttonBrowseFolder_Click(object sender, EventArgs e)
{
if (folder_browse_excelSheet.ShowDialog() == DialogResult.OK)
{
this.txt_DirPath.Text = folder_browse_excelSheet.SelectedPath;
gotFile = true;
}
}
private void btnCreateMasterSheet_Click(object sender, EventArgs e)
{
try
{
if (gotFile == false)
{
MessageBox.Show("Please select a folder first!");
}
else
{
var extensions = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
{
".xls",
".xlsx",
};
userNotifier = false;
var baseDir = txt_DirPath.Text;
var file_count = Directory.EnumerateFiles(baseDir).Count(filename => extensions.Contains(Path.GetExtension(filename)));
Excel.Application application = new Excel.Application();
xlsfiles = Directory.GetFiles(folder_browse_excelSheet.SelectedPath, "*.xls");
for (int j = 1; j <= file_count; j++) //Generating list of persons whose excelSheets are there
{
csvFilePath = System.IO.Path.GetFileNameWithoutExtension(xlsfiles[j - 1]);
personList[j - 1] = csvFilePath.ToString();
}
bool check_proceed = checkpersonSheetExist(personList);//Check for any missing excelSheet file as per the given person list
if (check_proceed == false)
{
application.Quit();
killApplication(true);
}
else
{
for (int i = 1; i <= file_count; i++)
{
Excel.Workbook temp_wb;
excelSheet_det[i - 1, 0] = personList[i - 1].ToString(); //Adding person name as first entry
string excelFileName = xlsfiles[i - 1];
excelFilePath = excelFileName; //reassigning the excelFilePath to make an entry in excelSheet Log.txt
findWordInFile("Non Working", excelFileName); //Calling the method to identify Non-working entry if any
//The above method sets flag value to 'y' if true else the flag value remains to 'n'
temp_wb = application.Workbooks.Open(excelFileName);
Excel.Worksheet worksheet;
worksheet = temp_wb.Sheets[1];
var cell_val = 0.0;
var cell_valA = 0.0;
if ((worksheet.Cells[5, 25] as Excel.Range).Value == null || (worksheet.Cells[5, 25] as Excel.Range).Value == 0)
cell_val = 0;
else
cell_val = (double)(worksheet.Cells[5, 25] as Excel.Range).Value;
if ((worksheet.Cells[4, 25] as Excel.Range).Value == null || (worksheet.Cells[4, 25] as Excel.Range).Value==0)
cell_valA = 0;
else
cell_valA = (double)(worksheet.Cells[4, 25] as Excel.Range).Value;
if (cell_val == 0)
{
excelSheet_det[i - 1, 1] = cell_valA.ToString(); //Adding Normal Hours
}
else
{
excelSheet_det[i - 1, 1] = cell_val.ToString(); //Adding Normal Hours
}
if (flag == 'y') //if non-working non billable entry found
{
var cell_val1 = 0.0;
if ((worksheet.Cells[14, 25] as Excel.Range).Value == null || (worksheet.Cells[14, 25] as Excel.Range).Value == 0.0)
cell_val1 = 0;
else
cell_val1 = (double)(worksheet.Cells[14, 25] as Excel.Range).Value;
excelSheet_det[i - 1, 3] = cell_val1.ToString();//Adding Non-working Hours
excelSheet_det[i - 1, 2] = "0"; //Setting working hours to zero
}
else //if non-working non billable entry NOT found
{
var cell_val2 = 0.0;
if ((worksheet.Cells[14, 25] as Excel.Range).Value == null || (worksheet.Cells[14, 25] as Excel.Range).Value == 0)
cell_val2 = 0;
else
cell_val2 = (double)(worksheet.Cells[14, 25] as Excel.Range).Value;
excelSheet_det[i - 1, 2] = cell_val2.ToString();//Adding working Hours
excelSheet_det[i - 1, 3] = "0"; //Setting non-working hours to zero
}
var cell_val3 = 0.0;
var cell_val3_1 = 0.0;
var cell_val3_2 = 0.0;
if ((worksheet.Cells[15, 25] as Excel.Range).Value == null || (worksheet.Cells[15, 25] as Excel.Range).Value == 0)
cell_val3 = 0;
else
cell_val3 = (double)(worksheet.Cells[15, 25] as Excel.Range).Value;
if ((worksheet.Cells[13, 25] as Excel.Range).Value == null || (worksheet.Cells[13, 25] as Excel.Range).Value == 0)
cell_val3_1 = 0;
else
cell_val3_1 = (double)(worksheet.Cells[13, 25] as Excel.Range).Value;
if ((worksheet.Cells[12, 25] as Excel.Range).Value == null || (worksheet.Cells[12, 25] as Excel.Range).Value == 0)
cell_val3_2 = 0;
else
cell_val3_2 = (double)(worksheet.Cells[12, 25] as Excel.Range).Value;
if (cell_val3 == 0 && cell_val3_1 == 0)
{
excelSheet_det[i - 1, 4] = cell_val3_2.ToString();
}
else if (cell_val3_1 == 0 && cell_val3_2 == 0)
{
excelSheet_det[i - 1, 4] = cell_val3.ToString();
}
else
{
excelSheet_det[i - 1, 4] = cell_val3_1.ToString();
}
var cell_val4 = DateTime.MinValue;
if ((worksheet.Cells[3, 24] as Excel.Range).Value == null)
cell_val4 = DateTime.MinValue;
else
cell_val4 = (worksheet.Cells[3, 24] as Excel.Range).Value;
excelSheet_det[i - 1, 5] = cell_val4.ToString();//copy last date of excelSheet entry
Marshal.ReleaseComObject(temp_wb);
}
application.Workbooks.Close();
application.Quit();
}
/*Writing the array contents into the excel master sheet*/
string masterSheetPath = folder_browse_excelSheet.SelectedPath + "\\Master Spreadsheet\\" + "MasterSheet.xls";
if (!Directory.Exists(folder_browse_excelSheet.SelectedPath +
"\\masterSheet"))
{
Directory.CreateDirectory(folder_browse_excelSheet.SelectedPath + "\\Master Spreadsheet");
}
if (!File.Exists(masterSheetPath))
{
FileStream fs = File.Create(masterSheetPath);
fs.Close();
}
Excel.Application excel = new Excel.Application();
Excel.Workbook workBook = excel.Workbooks.Open(masterSheetPath);
Excel.Worksheet sheet = workBook.ActiveSheet;
int numRows = sheet.UsedRange.Rows.Count;
//MessageBox.Show(numRows.ToString());
//Check for any duplicate entries in the mastersheet
string masterDupErrDisplay = string.Empty;
if (numRows > 1)
{
for (int i1 = 0; i1 < xlsfiles.Length; i1++)
{
for (int ch = 0; ch < numRows; ch++)
{
if (excelSheet_det[i1, 5] == (((sheet.Cells[ch + 1, 6] as Excel.Range).Value)).ToString() && excelSheet_det[i1, 0] == ((string)(sheet.Cells[ch + 1, 1] as Excel.Range).Value))
{
if (!masterDupErrDisplay.Contains(excelSheet_det[i1, 0].ToString()))
{
masterDupErrDisplay += string.Join(Environment.NewLine, excelSheet_det[i1, 0].ToString());
masterDupErrDisplay += string.Join(Environment.NewLine, Environment.NewLine);
}
}
}
}
if (masterDupErrDisplay != null)
{
if (MessageBox.Show("Duplicate entries have been found for these people " + Environment.NewLine + masterDupErrDisplay + Environment.NewLine + "Do you wish to Continue?", "Duplicate Mastersheet entry Error", MessageBoxButtons.YesNo) == DialogResult.No)
{
excel.Quit();
killApplication(true);
}
}
}
sheet.Cells[1, 1] = "Name";
sheet.Cells[1, 2] = "Normal Hours";
sheet.Cells[1, 3] = "Working Hours";
sheet.Cells[1, 4] = "Not working Hours";
sheet.Cells[1, 5] = "Total Hours";
sheet.Cells[1, 6] = "excelSheet Stamp";
for (int i = 0; i < xlsfiles.Length; i++)
{
sheet.Cells[numRows + 1 + i, 1] = excelSheet_det[i, 0];
sheet.Cells[numRows + 1 + i, 2] = excelSheet_det[i, 1];
sheet.Cells[numRows + 1 + i, 3] = excelSheet_det[i, 2];
sheet.Cells[numRows + 1 + i, 4] = excelSheet_det[i, 3];
sheet.Cells[numRows + 1 + i, 5] = excelSheet_det[i, 4];
sheet.Cells[numRows + 1 + i, 6] = excelSheet_det[i, 5];
}
workBook.RefreshAll();
excel.Calculate();
workBook.Save();
workBook.Close(true);
excel.Workbooks.Close();
excel.Quit();
application.Quit();
MessageBox.Show("Master track sheet is successfully updated!", "Process Successful");
}
}
catch (Exception ex)
{
throw ex;
}
}
#endregion master_tracking_sheet
public bool checkpersonSheetExist(string[] assocName)
{
try
{
string[,] personListSupply = new string[35, 2];
int counter = 0;
string errorDisplay = string.Empty;
string line;
// Read the file and display it line by line.
System.IO.StreamReader file = new System.IO.StreamReader(folder_browse_excelSheet.SelectedPath + "\\team_members.txt");
while ((line = file.ReadLine()) != null)
{
personListSupply[counter, 0] = line.ToString();
personListSupply[counter, 1] = "n";
counter++;
}
file.Close();
int c = 0;
while (assocName[c] != null)
{
c++;
}
for (int i = 0; i < counter; i++)
{
for (int k = 0; k < c; k++)
{
if (personListSupply[i, 0] == assocName[k])
{
personListSupply[i, 1] = "y";
continue;
}
}
if (personListSupply[i, 1] == "n")
{
errorDisplay += string.Join(Environment.NewLine, personListSupply[i, 0].ToString());
errorDisplay += string.Join(Environment.NewLine, Environment.NewLine);
}
}
if (MessageBox.Show("The excel files are missing for" + Environment.NewLine + errorDisplay + Environment.NewLine + "Do you wish to Continue?", "File Missing Error", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw ex;
}
}
