public static void PrintExcel(ExportData data, Boolean NeedFacilities, Boolean NeedPrograms, Boolean NeedIncome, Boolean NeedOptItems, Boolean NeedTasks
, Boolean NeedGuest, Boolean NeedParticipant)
{
try
{
//myHashtable = excelprocess.CheckExcellProcesses(myHashtable);
Excel.Application oXL;
oXL = new Excel.Application();
Excel.Workbook excelWorkbook = oXL.Workbooks.Add();
// oXL.Workbooks.Open(workbookPath, false, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
int needextra = 0;
if (NeedFacilities)
needextra++;
if (NeedPrograms)
needextra++;
if (NeedIncome)
needextra++;
if (NeedOptItems)
needextra++;
if (NeedTasks)
needextra++;
if (NeedGuest)
needextra++;
if (NeedParticipant)
needextra++;
//prep
//Excel._Worksheet eventSheet = (Excel._Worksheet)(excelWorkbook.Sheets[1]);
while (excelWorkbook.Sheets.Count != (needextra + 1))
{
if (excelWorkbook.Sheets.Count < (needextra + 1))
{
excelWorkbook.Sheets.Add(excelWorkbook.Sheets[excelWorkbook.Sheets.Count]);
//++
}
else if (excelWorkbook.Sheets.Count > (needextra + 1))
{
((Excel._Worksheet)(excelWorkbook.Sheets[excelWorkbook.Sheets.Count - 1])).Delete();
//--
}
}
Excel._Worksheet sheetEvent = null;
Excel._Worksheet sheetFacility = null;
Excel._Worksheet sheetProgram = null;
Excel._Worksheet sheetIncome = null;
Excel._Worksheet sheetOptItem = null;
Excel._Worksheet sheetTask = null;
Excel._Worksheet sheetGuest = null;
Excel._Worksheet sheetParticipant = null;
sheetEvent = (Excel._Worksheet)(excelWorkbook.Sheets[1]);
int sheetcount = 2;
//
//populate event
//
sheetEvent.Activate();
sheetEvent.Name = "Event";
PrintToCell(sheetEvent, 1, 1, "EventName:");
PrintToCell(sheetEvent, 2, 1, data.evnts.Name);
PrintToCell(sheetEvent, 1, 2, "Organizer Name:");
AdminHelper client = new AdminHelper();
PrintToCell(sheetEvent, 2, 2, client.GetUserName(data.evnts.Organizerid));
client.Close();
PrintToCell(sheetEvent, 1, 3, "Start:");
PrintToCell(sheetEvent, 2, 3, data.evnts.StartDateTime.ToString());
PrintToCell(sheetEvent, 1, 4, "End:");
PrintToCell(sheetEvent, 2, 4, data.evnts.EndDateTime.ToString());
PrintToCell(sheetEvent, 1, 5, "WebSite:");
PrintToCell(sheetEvent, 2, 5, data.evnts.Website);
PrintToCell(sheetEvent, 1, 6, "Description:");
PrintToCell(sheetEvent, 2, 6, data.evnts.Description);
PrintToCell(sheetEvent, 1, 7, "Tag:");
PrintToCell(sheetEvent, 2, 7, data.evnts.Tag);
BoldCellsRange(sheetEvent, "A", 1, "A", 7);
EventDay[] days = data.days;
//populate days
//PrintToCell(sheetEvent, 1, 9, "Days:");
//for (int i = 0; i < days.Count(); i++)
//{
//}
sheetEvent.Columns.AutoFit();
//sheetEvent.Rows.AutoFit();
if (NeedFacilities)
{
sheetFacility = (Excel._Worksheet)(excelWorkbook.Sheets[sheetcount]);
sheetcount++;
sheetFacility.Name = "Facilities";
int facicount = 0;
foreach (FacilityBookingConfirmed[] fbc in data.facilities)
{
facicount = facicount + fbc.Count();
}
if (data.facilities != null && facicount != 0)
{
int col = 1;
for (int i = 0; i < data.days.Count(); i++)
{
PrintToCell(sheetFacility, col, 2, "Date:");
PrintToCell(sheetFacility, col + 1, 2, data.days[i].StartDateTime.Date.ToShortDateString());
Excel.Range range2;
Excel.Range r12 = sheetFacility.Cells[2, col];
Excel.Range r22 = sheetFacility.Cells[2, col + 1];
range2 = sheetFacility.get_Range(r12.Address, r22.Address);
range2.Font.Bold = true;
int row = 3;
if (data.programs[i].Count() == 0)
{
PrintToCell(sheetFacility, col, row, "No Programs Yet");
}
for (int j = 0; j < data.facilities[i].Count(); j++)
{
PrintToCell(sheetFacility, col, row, "StartTime");
PrintToCell(sheetFacility, col, row + 1, "EndTime");
PrintToCell(sheetFacility, col, row + 2, "Venue");
PrintToCell(sheetFacility, col, row + 3, "Purpose");
PrintToCell(sheetFacility, col, row + 4, "Remark");
PrintToCell(sheetFacility, col + 1, row, data.facilities[i][j].RequestStartDateTime.ToShortTimeString());
PrintToCell(sheetFacility, col + 1, row + 1, data.facilities[i][j].RequestEndDateTime.ToShortTimeString());
PrintToCell(sheetFacility, col + 1, row + 2, data.facilities[i][j].Venue);
PrintToCell(sheetFacility, col + 1, row + 3, data.facilities[i][j].Purpose);
PrintToCell(sheetFacility, col + 1, row + 4, data.facilities[i][j].Remarks);
Excel.Range range;
Excel.Range r1 = sheetFacility.Cells[1, col];
Excel.Range r2 = sheetFacility.Cells[row + 6, col];
range = sheetFacility.get_Range(r1.Address, r2.Address);
range.Font.Bold = true;
row = row + 6;
}
col = col + 3;
BoldCellsRange(sheetFacility, "A", 1, "A", 1);
}
//PrintToCell(sheetFacility, 1, 1, "StartTime");
//PrintToCell(sheetFacility, 2, 1, "EndTime");
//PrintToCell(sheetFacility, 3, 1, "Venue");
//PrintToCell(sheetFacility, 4, 1, "Purpose");
//PrintToCell(sheetFacility, 5, 1, "Remark");
//BoldCellsRange(sheetFacility, "A", 1, "E", 1);
//int row = 2;
//for (int i = 0; i < data.facilities.Count(); i++)
//{
// PrintToCell(sheetFacility, 1, row, "Date:");
// PrintToCell(sheetFacility, 2, row, data.days[i].StartDateTime.Date.ToShortDateString());
// row++;
// PrintToCell(sheetFacility, 1, row, "Day");
// PrintToCell(sheetFacility, 2, row, data.days[i].DayNumber.ToString());
// row++;
// if (data.facilities[i].Count() == 0)
// {
// PrintToCell(sheetFacility, 1, row, "There are no comfirmed booking facilities on this day");
// row++;
// }
// for (int j = 0; j < data.facilities[i].Count(); j++)
// {
// PrintToCell(sheetFacility, 1, row, data.facilities[i][j].RequestStartDateTime.ToShortTimeString());
// PrintToCell(sheetFacility, 2, row, data.facilities[i][j].RequestEndDateTime.ToShortTimeString());
// PrintToCell(sheetFacility, 3, row, data.facilities[i][j].Venue);
// PrintToCell(sheetFacility, 4, row, data.facilities[i][j].Purpose);
// PrintToCell(sheetFacility, 5, row, data.facilities[i][j].Remarks);
// row++;
// }
//}
}
else
{
PrintToCell(sheetFacility, 1, 1, "No Facilities Booking Comfirmed");
}
}
if (NeedPrograms)
{
sheetProgram = (Excel._Worksheet)(excelWorkbook.Sheets[sheetcount]);
sheetcount++;
int progcount = 0;
foreach (Program[] ps in data.programs)
{
progcount = progcount + ps.Count();
}
sheetProgram.Name = "Programmes";
if (data.programs != null && progcount != 0)
{
int col = 1;
for(int i=0;i< data.days.Count();i++)
{
PrintToCell(sheetProgram, col, 2, "Date:");
PrintToCell(sheetProgram, col+1, 2, data.days[i].StartDateTime.Date.ToShortDateString());
Excel.Range range2;
Excel.Range r12 = sheetProgram.Cells[2, col];
Excel.Range r22 = sheetProgram.Cells[2, col+1];
range2 = sheetProgram.get_Range(r12.Address, r22.Address);
range2.Font.Bold = true;
int row = 3;
if (data.programs[i].Count() == 0)
{
PrintToCell(sheetProgram, col, row, "No Programs Yet");
}
for(int j=0;j< data.programs[i].Count();j++)
{
PrintToCell(sheetProgram, col, row, "StartTime");
PrintToCell(sheetProgram, col, row+1, "EndTime");
PrintToCell(sheetProgram, col, row+2, "Name");
PrintToCell(sheetProgram, col, row+3, "Description");
PrintToCell(sheetProgram, col, row+4, "Venue");
PrintToCell(sheetProgram, col+1, row, data.programs[i][j].StartDateTime.ToShortTimeString());
PrintToCell(sheetProgram, col + 1, row + 1, data.programs[i][j].EndDateTime.ToShortTimeString());
PrintToCell(sheetProgram, col+1, row + 2, data.programs[i][j].Name);
PrintToCell(sheetProgram, col + 1, row + 3, data.programs[i][j].Description);
PrintToCell(sheetProgram, col + 1, row + 4, data.programs[i][j].Location);
Excel.Range range;
Excel.Range r1 = sheetProgram.Cells[1, col];
Excel.Range r2 = sheetProgram.Cells[row + 6, col];
range = sheetProgram.get_Range(r1.Address,r2.Address);
range.Font.Bold = true;
row = row + 6;
}
col = col + 3;
BoldCellsRange(sheetProgram, "A", 1, "A", 1);
}
//PrintToCell(sheetProgram, 1, 1, "StartTime");
//PrintToCell(sheetProgram, 2, 1, "EndTime");
//PrintToCell(sheetProgram, 3, 1, "Name");
//PrintToCell(sheetProgram, 4, 1, "Location");
//PrintToCell(sheetProgram, 5, 1, "Description");
//BoldCellsRange(sheetProgram, "A", 1, "E", 1);
//int row = 2;
//for (int i = 0; i < data.programs.Count(); i++)
//{
// PrintToCell(sheetProgram, 1, row, "Date:");
// PrintToCell(sheetProgram, 2, row, data.days[i].StartDateTime.Date.ToShortDateString());
// row++;
// PrintToCell(sheetProgram, 1, row, "Day");
// PrintToCell(sheetProgram, 2, row, data.days[i].DayNumber.ToString());
// row++;
// if (data.programs[i].Count() == 0)
// {
// PrintToCell(sheetProgram, 1, row, "There are no Programs Added on this day");
// row++;
// }
// for (int j = 0; j < data.programs[i].Count(); j++)
// {
// PrintToCell(sheetProgram, 1, row, data.programs[i][j].StartDateTime.ToShortTimeString());
// PrintToCell(sheetProgram, 2, row, data.programs[i][j].EndDateTime.ToShortTimeString());
// PrintToCell(sheetProgram, 3, row, data.programs[i][j].Name);
// PrintToCell(sheetProgram, 4, row, data.programs[i][j].Location);
// PrintToCell(sheetProgram, 5, row, data.programs[i][j].Description);
// row++;
// }
}
else
{
PrintToCell(sheetProgram, 1, 1, "No Program added");
}
}
if (NeedIncome)
{
sheetIncome = (Excel._Worksheet)(excelWorkbook.Sheets[sheetcount]);
sheetcount++;
sheetIncome.Name = "Income";
if (data.budgetincomes != null && data.budgetincomes.Count() != 0)
{
PrintToCell(sheetIncome, 1, 1, "Name");
PrintToCell(sheetIncome, 2, 1, "Source");
PrintToCell(sheetIncome, 3, 1, "Date Received");
PrintToCell(sheetIncome, 4, 1, "GST Liable");
PrintToCell(sheetIncome, 5, 1, "Before GST");
PrintToCell(sheetIncome, 6, 1, "GST Value");
PrintToCell(sheetIncome, 7, 1, "After GST");
BoldCellsRange(sheetProgram, "A", 1, "G", 1);
decimal total = 0;
int row = 2;
for (int i = 0; i < data.budgetincomes.Count(); i++)
{
PrintToCell(sheetIncome, 1, row, data.budgetincomes[i].Name);
PrintToCell(sheetIncome, 2, row, data.budgetincomes[i].Source);
PrintToCell(sheetIncome, 3, row, data.budgetincomes[i].DateReceived.ToShortDateString());
PrintToCell(sheetIncome, 4, row, data.budgetincomes[i].IsGstLiable.ToString());
PrintToCell(sheetIncome, 5, row, data.budgetincomes[i].IncomeBeforeGST.ToString());
PrintToCell(sheetIncome, 6, row, data.budgetincomes[i].GstValue.ToString());
PrintToCell(sheetIncome, 7, row, data.budgetincomes[i].IncomeAfterGST.ToString());
total = data.budgetincomes[i].IncomeAfterGST + total;
row++;
}
PrintToCell(sheetIncome, 6, row, "total:");
PrintToCell(sheetIncome, 7, row, total.ToString());
}
else
{
PrintToCell(sheetIncome, 1, 1, "No Income added");
}
}
if (NeedOptItems)
{
sheetOptItem = (Excel._Worksheet)(excelWorkbook.Sheets[sheetcount]);
sheetcount++;
sheetOptItem.Name = "OptimizedItems";
if (data.optitems != null && data.optitems.BudgetItemsList.Count() != 0)
{
PrintToCell(sheetOptItem, 1, 1, "Generated Date:");
PrintToCell(sheetOptItem, 2, 1, data.optitems.GeneratedDate.ToString());
PrintToCell(sheetOptItem, 1, 2, "Total Estmiated Price:");
PrintToCell(sheetOptItem, 2, 2, data.optitems.TotalEstimatedPrice.ToString());
PrintToCell(sheetOptItem, 1, 3, "Total Satisfaction:");
PrintToCell(sheetOptItem, 2, 3, data.optitems.TotalSatisfaction.ToString());
PrintToCell(sheetOptItem, 1, 4, "Name");
PrintToCell(sheetOptItem, 2, 4, "Type");
PrintToCell(sheetOptItem, 3, 4, "Is Bought");
BoldCellsRange(sheetProgram, "A", 4, "C", 4);
int row = 5;
for (int i = 0; i < data.optitems.BudgetItemsList.Count(); i++)
{
PrintToCell(sheetOptItem, 1, row, data.optitems.BudgetItemsList[i].ItemName);
PrintToCell(sheetOptItem, 2, row, data.optitems.BudgetItemsList[i].typeString);
PrintToCell(sheetOptItem, 3, row, data.optitems.BudgetItemsList[i].IsBought.ToString());
}
}
else
{
PrintToCell(sheetOptItem, 1, 1, "No Optimized Items added");
}
}
if (NeedTasks)
{
sheetTask = (Excel._Worksheet)(excelWorkbook.Sheets[sheetcount]);
sheetcount++;
sheetTask.Name = "Task";
if (data.tasks != null && data.tasks.Count() != 0)
{
PrintToCell(sheetTask, 1, 1, "name");
PrintToCell(sheetTask, 2, 1, "Description");
PrintToCell(sheetTask, 3, 1, "DueDate");
PrintToCell(sheetTask, 4, 1, "Completion(%)");
int row = 2;
for (int i = 0; i < data.tasks.Count(); i++)
{
PrintToCell(sheetTask, 1, row, data.tasks[i].TaskName);
PrintToCell(sheetTask, 2, row, data.tasks[i].TaskDesc);
PrintToCell(sheetTask, 3, row, data.tasks[i].DueDate.ToString());
PrintToCell(sheetTask, 4, row, data.tasks[i].PercentageCompletion.ToString() + "%");
row++;
for (int j = 0; j < data.tasks[i].TasksAssignments.Count(); j++)
{
TaskAssignment t = data.tasks[i].TasksAssignments[j];
PrintToCell(sheetTask, 1, row, t.RoleName);//shld be role name
PrintToCell(sheetTask, 2, row, t.RoleUserID);//shld be role assigned name
PrintToCell(sheetTask, 3, row, t.Remarks);
PrintToCell(sheetTask, 4, row, t.IsRead.ToString());
PrintToCell(sheetTask, 5, row, t.IsCompleted.ToString());
}
}
}
else
{
PrintToCell(sheetTask, 1, 1, "No Task added");
}
}
if (NeedGuest)
{
sheetGuest = (Excel._Worksheet)(excelWorkbook.Sheets[sheetcount]);
sheetcount++;
int guestcount = 0;
foreach (Guest[] gs in data.guests)
{
guestcount = guestcount + gs.Count();
}
sheetGuest.Name = "Guest";
if (data.guests != null && guestcount != 0)
{
int col = 1;
for (int i = 0; i < data.days.Count(); i++)
{
PrintToCell(sheetGuest, col, 2, "Date:");
PrintToCell(sheetGuest, col + 1, 2, data.days[i].StartDateTime.Date.ToShortDateString());
Excel.Range range2;
Excel.Range r12 = sheetGuest.Cells[2, col];
Excel.Range r22 = sheetGuest.Cells[2, col + 1];
range2 = sheetGuest.get_Range(r12.Address, r22.Address);
range2.Font.Bold = true;
int row = 3;
if (data.guests[i].Count() == 0)
{
PrintToCell(sheetGuest, col, row, "No Guests Yet");
}
for (int j = 0; j < data.guests[i].Count(); j++)
{
PrintToCell(sheetGuest, col, row, "Name");
PrintToCell(sheetGuest, col, row + 1, "Description");
PrintToCell(sheetGuest, col, row + 2, "Contact");
PrintToCell(sheetGuest, col + 1, row, data.guests[i][j].Name);
PrintToCell(sheetGuest, col + 1, row + 1, data.guests[i][j].Description);
PrintToCell(sheetGuest, col + 1, row + 2, data.guests[i][j].Contact);
Excel.Range range;
Excel.Range r1 = sheetGuest.Cells[1, col];
Excel.Range r2 = sheetGuest.Cells[row + 3, col];
range = sheetGuest.get_Range(r1.Address, r2.Address);
range.Font.Bold = true;
row = row + 4;
}
col = col + 3;
BoldCellsRange(sheetGuest, "A", 1, "A", 1);
}
//PrintToCell(sheetGuest, 1, 1, "Name");
//PrintToCell(sheetGuest, 2, 1, "Description");
//PrintToCell(sheetGuest, 3, 1, "Contact");
//BoldCellsRange(sheetGuest, "A", 1, "C", 1);
//int row = 2;
//for (int i = 0; i < data.programs.Count(); i++)
//{
// PrintToCell(sheetGuest, 1, row, "Date:");
// PrintToCell(sheetGuest, 2, row, data.days[i].StartDateTime.Date.ToShortDateString());
// row++;
// PrintToCell(sheetGuest, 1, row, "Day");
// PrintToCell(sheetGuest, 2, row, data.days[i].DayNumber.ToString());
// row++;
// if (data.guests.Count() == 0)
// {
// PrintToCell(sheetGuest, 1, row, "There are no Guests Added on this day");
// row++;
// }
// for (int j = 0; j < data.guests[i].Count(); j++)
// {
// PrintToCell(sheetGuest, 1, row, data.guests[i][j].Name);
// PrintToCell(sheetGuest, 2, row, data.guests[i][j].Description);
// PrintToCell(sheetGuest, 3, row, data.guests[i][j].Contact);
// row++;
// }
//}
}
else
{
PrintToCell(sheetGuest, 1, 1, "No Guest added");
}
}
if (NeedParticipant)
{
sheetParticipant = (Excel._Worksheet)(excelWorkbook.Sheets[sheetcount]);
sheetcount++;
sheetParticipant.Name = "Participant";
if (data.participants != null && data.participants.Count() != 0 && data.field != null)
{
int col = 1;
for (int j = 0; j < data.field.Count(); j++)
{
PrintToCell(sheetParticipant, col, 1, data.field[j].FieldName);
col++;
}
int row = 2;
for (int i = 0; i < data.participants.Count(); i++)
{
for (int j = 0; j < data.field.Count(); j++)
{
for (int k = 0; k < data.participants[i].Answer.Count(); k++)
{
if (data.participants[i].Answer[k].FieldID == data.field[j].FieldID)
{
PrintToCell(sheetParticipant, j + 1, row, data.participants[i].Answer[k].Answer);
break;
}
}
}
PrintToCell(sheetParticipant, 1, row, data.participants[i].ParticipantID.ToString());//
row++;
}
}
else
{
PrintToCell(sheetParticipant, 1, 1, "No Participant added");
}
}
//PrintToCell(oSheet, 1, 1, "1");
//PrintToCell(oSheet, 1, 2, "1");
//PrintToCell(oSheet, 1, 3, "1");
//PrintToCell(oSheet, 1, 4, "1");
//BoldCellsRange(oSheet,"A",1,"A",1);
oXL.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
//excelprocess.KillExcel(myHashtable);
//return;
}
}
private void lstRequestor_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (lstRequestor.SelectedIndex == -1)
{
//MessageBox.Show("Please select a booking request", "No request selected",
//MessageBoxButton.OK, MessageBoxImage.Exclamation);
return;
}
EventHelper client = new EventHelper();
AdminHelper admHelper = new AdminHelper();
try
{
FacilityBookingRequest fbr = (FacilityBookingRequest)lstRequestor.SelectedItem;
txtEventName.Text = client.GetEventName(fbr.EventID);
txtRequestor.Text = admHelper.GetUserName(fbr.RequestorID);
lblStartTime.Content = fbr.RequestStartDateTime.ToString("dd MMM yyyy HH:mm");
lblEndTime.Content = fbr.RequestEndDateTime.ToString("dd MMM yyyy HH:mm");
dgLocation.ItemsSource = fbr.RequestDetails;
lvTimeslot.Reset();
}
catch (Exception ex)
{
MessageBox.Show("An error has occured: " + ex.Message, "Error",
MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
client.Close();
admHelper.Close();
}
}
private void lstRequest_SelectionChanged(object sender, System.Windows.Controls.SelectionChangedEventArgs e)
{
if (lstRequest.SelectedIndex == -1)
{
//MessageBox.Show("Please Select a request!", "Invalid input",
//MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
else
{
FacilityBookingRequest fbr = (FacilityBookingRequest)lstRequest.SelectedItem;
EventHelper client = new EventHelper();
AdminHelper admHelper = new AdminHelper();
txtEventName.Text = client.GetEventName(fbr.EventID);
txtRemarks.Text = fbr.Remarks;
txtRequestor.Text = admHelper.GetUserName(fbr.RequestorID);
txtStatus.Text = fbr.Status.ToString();
lblStartTime.Content = fbr.RequestStartDateTime.ToString("dd MMM yyyy HH:mm");
lblEndTime.Content = fbr.RequestEndDateTime.ToString("dd MMM yyyy HH:mm");
lvCurrentBooking.setSource(fbr);
client.Close();
admHelper.Close();
if ((fbr.Status == BookingStatus.Pending) || (fbr.Status == BookingStatus.Approved))
{
btnDrop.IsEnabled = true;
}
else
{
btnDrop.IsEnabled = false;
}
}
}