// repairs sums at bottom of cols A->M then S:
public void repairTimeRecording(Excel.Worksheet Wks, decimal intRowStart, decimal intRowEnd)
{
decimal intCol_Start = CommonExcelClasses.getExcelColumnNumber("A");
decimal intCol_End = CommonExcelClasses.getExcelColumnNumber("M");
// string strDeltaCol, strDeltaRange, strSumString;
string strDeltaCol;
string strSumString;
decimal intDeltaA;
for (intDeltaA = intCol_Start; intDeltaA <= intCol_End; intDeltaA++)
{
strDeltaCol = CommonExcelClasses.getExcelColumnLetter((int)intDeltaA);
strSumString = CommonExcelClasses.createFormula(strDeltaCol, intRowStart, intRowEnd);
Wks.Cells[intRowEnd + 1, intDeltaA].Value = strSumString;
}
// ' finally do S
intDeltaA = 19;
strDeltaCol = CommonExcelClasses.getExcelColumnLetter((int)intDeltaA);
strSumString = CommonExcelClasses.createFormula("S", intRowStart, intRowEnd);
Wks.Cells[intRowEnd + 1, intDeltaA].Value = strSumString;
}
internal void updateTimeSheet(Excel.Application xls)
{
#region [Declare and instantiate variables for process]
myData = myData.LoadMyData(); // read data from settings file
bool boolDisplayInitialMessage = myData.ProduceInitialMessageBox;
bool boolDisplayCompleteMessage = myData.ProduceCompleteMessageBox;
bool booltimeTaken = myData.DisplayTimeTaken;
bool boolTurnOffScreen = myData.TurnOffScreenValidation;
bool boolSaveLastRowNo = myData.TimeSheetGetRowNo;
decimal intRowCount = myData.TimeSheetRowNo;
#endregion
try
{
#region [Declare and instantiate variables for worksheet/book]
// need to loop entire workbook
Excel.Workbook Wkb = xls.ActiveWorkbook;
Excel.Worksheet Wks;
Wks = Wkb.ActiveSheet;
// will define start row later
decimal intLastRow = CommonExcelClasses.getLastRow(Wks);
int intExaminCol = CommonExcelClasses.getExcelColumnNumber("N");
string strMessage;
DialogResult dlgResult = DialogResult.Yes;
#endregion
#region [Display a Message?]
if (boolDisplayInitialMessage)
{
strMessage = "Correct: " + Wks.Name + LF +
" (starting at row:" + intRowCount.ToString() + ")";
if (booltimeTaken)
{
strMessage = strMessage + LF + " and display the time taken";
}
strMessage = strMessage + "?";
dlgResult = MessageBox.Show(strMessage, "Correct Timesheet", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
}
#endregion
#region [Start of work]
if (dlgResult == DialogResult.Yes)
{
DateTime dteStart = DateTime.Now;
// might put this under an option
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("Off", xls, myData.TestCode);
}
#region [Start of loop]
while (intRowCount <= intLastRow)
{
if (!CommonExcelClasses.isEmptyCell(Wks.Cells[intRowCount, intExaminCol]))
{
if (boolSaveLastRowNo)
{
startOfWeekCheck(Wks, intRowCount, myData.TimeSheetRowNo);
}
// is it a valid day
if (CommonExcelClasses.dayCheck(Wks.Cells[intRowCount, intExaminCol].Value.ToString()))
{
decimal intStartOfWeekRow = intRowCount;
string strDay = Wks.Cells[intRowCount, intExaminCol].Value.ToString();
// jump down 1 row
intRowCount++;
while (Wks.Cells[intRowCount, intExaminCol].Value.ToString() == strDay)
{
sortHours(Wks, intRowCount, intStartOfWeekRow);
fixDateCol(Wks, intRowCount, intStartOfWeekRow);
intRowCount++;
}
intRowCount--;
repairTimeRecording(Wks, intStartOfWeekRow + 1, intRowCount);
// this will add validation to the entire date section / range
CommonExcelClasses.addValidationToColumn(Wks, "Q", intStartOfWeekRow + 1, intRowCount, "=rangeCategory");
}
}
intRowCount++;
}
#endregion
#endregion
#region [Display Complete Message]
if (boolDisplayCompleteMessage)
{
strMessage = "";
strMessage = strMessage + "Process Complete ...";
if (booltimeTaken)
{
DateTime dteEnd = DateTime.Now;
int milliSeconds = (int)((TimeSpan)(dteEnd - dteStart)).TotalMilliseconds;
strMessage = strMessage + "that took {TotalMilliseconds} " + milliSeconds;
}
CommonExcelClasses.MsgBox(strMessage); // localisation?
}
}
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("On", xls, myData.TestCode);
}
#endregion
#region [Release memory]
Marshal.ReleaseComObject(Wks);
Marshal.ReleaseComObject(Wkb);
#endregion
}
catch (System.Exception excpt)
{
CommonExcelClasses.MsgBox("Ther was an error - around row number" + intRowCount.ToString(), "Error");
CommonExcelClasses.turnAppSettings("On", xls, myData.TestCode);
Console.WriteLine(excpt.Message);
}
}