protected virtual void AddEntry(
int entryIndex,
decimal billableHours,
decimal ratePerHour,
int weekNumber,
DateTime startDate,
DateTime endDate,
InvoiceConfiguration invoiceConfiguration,
bool isSplitRate = false)
{
Excel.Application excelApplication = ExcelGenerator.GetExcelApplication();
// Qty...
ExcelCell qtyStartCell = Helpers.Excel.ToCell(invoiceConfiguration.QtyStartCell);
excelApplication.Cells[qtyStartCell.Row + entryIndex, qtyStartCell.Column].Value = billableHours;
// Description...
ExcelCell descriptionStartCell = Helpers.Excel.ToCell(invoiceConfiguration.DescriptionStartCell);
string description = string.Format("{0} | {1}", invoiceConfiguration.Description, this.GetTimesheetWeekString(weekNumber, startDate, endDate, isSplitRate));
excelApplication.Cells[descriptionStartCell.Row + entryIndex, descriptionStartCell.Column].Value = description;
// PO Number...
int PONumber = invoiceConfiguration.PONumber;
ExcelCell POStartCell = Helpers.Excel.ToCell(invoiceConfiguration.PONumberStartCell);
excelApplication.Cells[POStartCell.Row + entryIndex, POStartCell.Column].Value = invoiceConfiguration.PONumber;
// Rate...
ExcelCell rateStartCell = Helpers.Excel.ToCell(invoiceConfiguration.RateStartCell);
excelApplication.Cells[rateStartCell.Row + entryIndex, rateStartCell.Column].Value = ratePerHour;
}
// Abstract Overrides
// --------------------------------------------------------------------------
#region Abstract Overrides
protected override bool UpdateWorkbook()
{
Excel.Application excelApplication = ExcelGenerator.GetExcelApplication();
InvoiceConfiguration invoiceConfiguration = Configuration.Configuration.Instance.InvoiceConfiguration;
PersonalConfiguration personalConfiguration = Configuration.Configuration.Instance.PersonalConfiguration;
// Name...
ExcelCell nameCell = Helpers.Excel.ToCell(invoiceConfiguration.NameCell);
excelApplication.Cells[nameCell.Row, nameCell.Column].Value = personalConfiguration.Name;
// Address...
ExcelCell addressCell = Helpers.Excel.ToCell(invoiceConfiguration.AddressCell);
excelApplication.Cells[addressCell.Row, addressCell.Column].Value = personalConfiguration.Address;
// Invoice Number...
ExcelCell invoiceNumberCell = Helpers.Excel.ToCell(invoiceConfiguration.CurrentInvoiceNumberCell);
excelApplication.Cells[invoiceNumberCell.Row, invoiceNumberCell.Column].Value = this.MonthlyTimesheets.InvoiceNumber;
int rowIndex = -1;
//for (int i = 0; i < this.MonthlyTimesheets.Count; i++) {
foreach (Timesheet timesheet in this.MonthlyTimesheets)
{
//Timesheet timesheet = this.MonthlyTimesheets[i];
if (timesheet.IsSplitRate())
{
// If we have a Timesheet that has split rates (e.g. all TimesheetDates do not have the same
// RatePerHour), we need to handle this separately so it is properly reflected on the Invoice.
AddSplitRateEntries(ref rowIndex, timesheet, invoiceConfiguration);
}
else
{
// Note: If timesheet.IsSplitRate() == false, all the TimesheetDates within the Timesheet
// have the same rate/hour, so all we need to do is get the RatePerHour from any TimesheetDate
// within the Timesheet and we're good to go...
decimal ratePerHour = timesheet[0].RatePerHour;
AddEntry(++rowIndex, timesheet.GetBillableHours(), ratePerHour, timesheet.WeekNumber, timesheet.StartDate.Date, timesheet.EndDate.Date, invoiceConfiguration);
}
}
return(true);
}
public virtual bool Generate(bool promptOnOverwrite)
{
string outputFileName = this.GetFullOutputFileName();
if (promptOnOverwrite)
{
if (File.Exists(outputFileName))
{
if (MessageBox.Show(string.Format("The output file [{0}] already exists. If you continue, this file will be overwritten. Do you want to overwrite this file?", Helpers.Paths.MinifyPath(outputFileName)),
"Attention", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
{
return(false);
}
}
}
File.Copy(this.ExcelTemplate, outputFileName, true);
Excel.Application excelApplication = ExcelGenerator.GetExcelApplication(true);
excelApplication.Visible = true;
this.ExcelWorkbook = excelApplication.Workbooks.Open(
outputFileName,
Type.Missing, // UpdateLinks
false,
Type.Missing, // Format
Type.Missing, // Password
Type.Missing, // WriteResPassword
true, // IgnoreReadOnlyRecommented
Type.Missing, // Origin
Type.Missing, // Delimited
Type.Missing, // Editable
Type.Missing, // Notify
Type.Missing, // Converter
Type.Missing, // AddToMru
Type.Missing, // Local
Type.Missing // CorruptLoad
);
return(true);
}
// Abstract Overrides
// --------------------------------------------------------------------------
#region Abstract Overrides
protected override bool UpdateWorkbook()
{
Excel.Application excelApplication = ExcelGenerator.GetExcelApplication();
TimesheetConfiguration timesheetConfiguration = Configuration.Configuration.Instance.TimesheetConfiguration;
PersonalConfiguration personalConfiguration = Configuration.Configuration.Instance.PersonalConfiguration;
ExcelCell cell = null;
// Notes and remarks cell
ExcelCell notesAndRemarksCell = Helpers.Excel.ToCell(timesheetConfiguration.NotesAndRemarksCell);
// Timesheet name...
cell = Helpers.Excel.ToCell(timesheetConfiguration.NameCell);
excelApplication.Cells[cell.Row, cell.Column].Value = personalConfiguration.Name;
// From date...
cell = Helpers.Excel.ToCell(timesheetConfiguration.StartDateCell);
excelApplication.Cells[cell.Row, cell.Column].Value = this.Timesheet.StartDate.Date;
// Timesheet project description...
cell = Helpers.Excel.ToCell(timesheetConfiguration.ProjectDescriptionCell);
excelApplication.Cells[cell.Row, cell.Column].Value = timesheetConfiguration.ProjectDescription;
// Billable hours...
// -------------------------------------------------------------------
string hourCellString = string.Empty;
DayOfWeek dayOfWeek;
TimesheetDate timesheetDate = null;
// Monday...
hourCellString = timesheetConfiguration.MondayHourCell;
dayOfWeek = DayOfWeek.Monday;
cell = Helpers.Excel.ToCell(hourCellString);
timesheetDate = this.Timesheet.Dates.First(p => p.Date.DayOfWeek == dayOfWeek);
excelApplication.Cells[cell.Row, cell.Column].Value = timesheetDate.BillableHours;
if (timesheetDate.HasNotes)
{
AddNote(excelApplication, timesheetDate.Date, timesheetDate.Notes, notesAndRemarksCell);
}
// Tuesday...
hourCellString = timesheetConfiguration.TuesdayHourCell;
dayOfWeek = DayOfWeek.Tuesday;
cell = Helpers.Excel.ToCell(hourCellString);
timesheetDate = this.Timesheet.Dates.First(p => p.Date.DayOfWeek == dayOfWeek);
excelApplication.Cells[cell.Row, cell.Column].Value = timesheetDate.BillableHours;
if (timesheetDate.HasNotes)
{
AddNote(excelApplication, timesheetDate.Date, timesheetDate.Notes, notesAndRemarksCell);
}
// Wednesday...
hourCellString = timesheetConfiguration.WednesdayHourCell;
dayOfWeek = DayOfWeek.Wednesday;
cell = Helpers.Excel.ToCell(hourCellString);
timesheetDate = this.Timesheet.Dates.First(p => p.Date.DayOfWeek == dayOfWeek);
excelApplication.Cells[cell.Row, cell.Column].Value = timesheetDate.BillableHours;
if (timesheetDate.HasNotes)
{
AddNote(excelApplication, timesheetDate.Date, timesheetDate.Notes, notesAndRemarksCell);
}
// Thursday...
hourCellString = timesheetConfiguration.ThursdayHourCell;
dayOfWeek = DayOfWeek.Thursday;
cell = Helpers.Excel.ToCell(hourCellString);
timesheetDate = this.Timesheet.Dates.First(p => p.Date.DayOfWeek == dayOfWeek);
excelApplication.Cells[cell.Row, cell.Column].Value = timesheetDate.BillableHours;
if (timesheetDate.HasNotes)
{
AddNote(excelApplication, timesheetDate.Date, timesheetDate.Notes, notesAndRemarksCell);
}
// Friday...
hourCellString = timesheetConfiguration.FridayHourCell;
dayOfWeek = DayOfWeek.Friday;
cell = Helpers.Excel.ToCell(hourCellString);
timesheetDate = this.Timesheet.Dates.First(p => p.Date.DayOfWeek == dayOfWeek);
excelApplication.Cells[cell.Row, cell.Column].Value = timesheetDate.BillableHours;
if (timesheetDate.HasNotes)
{
AddNote(excelApplication, timesheetDate.Date, timesheetDate.Notes, notesAndRemarksCell);
}
// Saturday...
hourCellString = timesheetConfiguration.SaturdayHourCell;
dayOfWeek = DayOfWeek.Saturday;
cell = Helpers.Excel.ToCell(hourCellString);
timesheetDate = this.Timesheet.Dates.First(p => p.Date.DayOfWeek == dayOfWeek);
excelApplication.Cells[cell.Row, cell.Column].Value = timesheetDate.BillableHours;
if (timesheetDate.HasNotes)
{
AddNote(excelApplication, timesheetDate.Date, timesheetDate.Notes, notesAndRemarksCell);
}
// Sunday...
hourCellString = timesheetConfiguration.SundayHourCell;
dayOfWeek = DayOfWeek.Sunday;
cell = Helpers.Excel.ToCell(hourCellString);
timesheetDate = this.Timesheet.Dates.First(p => p.Date.DayOfWeek == dayOfWeek);
excelApplication.Cells[cell.Row, cell.Column].Value = timesheetDate.BillableHours;
if (timesheetDate.HasNotes)
{
AddNote(excelApplication, timesheetDate.Date, timesheetDate.Notes, notesAndRemarksCell);
}
return(true);
}
static protected Excel.Application GetExcelApplication()
{
return(ExcelGenerator.GetExcelApplication(false));
}
