public static DataTable GetCamperSummaryReport(int CampYearID, int FedID, string CampID_List, string StatusID_List) { DataTable dt = CamperApplicationDA.GetCamperSummaryReport(CampYearID, FedID, CampID_List, StatusID_List); // we have to calculate total as a new column int total = 0; foreach (DataRow dr in dt.Rows) { for (int i = 1; i < dt.Columns.Count; i++) { if (!(dr[i] == DBNull.Value)) { total += (int)dr[i]; } } dr[dt.Columns.Count - 1] = total; total = 0; } // the default column name is -1 set in store procedure, we have to change it back dt.Columns[dt.Columns.Count - 1].ColumnName = "Total"; return(dt); }
private DataSet GetDataFromRepository() { var dtFedId = GetSelectedItems(chklistFed.Items); var dtCampId = GetSelectedItems(chklistCamp.Items); var dtStatusId = GetSelectedItems(chklistStatus.Items); int TimesReceivedGrant = 0; if (chkAllTimers.Checked) { TimesReceivedGrant += 1; } if (chk1stTimers.Checked) { TimesReceivedGrant += 2; } if (chk2ndTimers.Checked) { TimesReceivedGrant += 4; } if (chk3rdTimers.Checked) { TimesReceivedGrant += 8; } DataSet a = CamperApplicationDA.GetSessionLengthByCamp(Int32.Parse(ddlCampYear.SelectedValue), dtFedId, dtCampId, dtStatusId, TimesReceivedGrant); return(a); }
public static DataTable GetCamperCountByCamp(int CampYearID, ProgramType Program, string CampID_List, string StatusName_List, int UserID) { StatusName_List += ", [Total By Camp]"; DataTable dt = CamperApplicationDA.GetCamperCountByCamp(CampYearID, Program, CampID_List, StatusName_List, UserID); Utility.CreateTotalColumnAndRow(dt, "Total By Status"); return(dt); }
/// <summary> /// This creates each data table + total table for each camp. It's designed to fit the paging mechanism /// </summary> private void CreateMainReport() { lblCampName.Text = param.CampsThatHaveDataDict.ElementAt(Int32.Parse(txtPage.Text) - 1).Value; lblPage.Text = param.CampsThatHaveDataDict.Count.ToString(); lblPage2.Text = lblPage.Text; DataTable dtTotal; if (param.CamperOrg == CamperOrgType.EnrollmentConfirmationFJC || param.CamperOrg == CamperOrgType.EnrollmentConfirmationPartner) { DataTable dt = CamperApplicationDA.GetFJCCamperReport(param.CamperOrg, param.ProgramTypeID, param.FedID, param.CampYearID, param.CampsThatHaveDataDict.ElementAt(Int32.Parse(txtPage.Text) - 1).Key, param.StatusID_List); dtTotal = CreateTotalTablePerCamp(dt); gvPerCamp.DataSource = dt; gvPerCamp.DataBind(); gvTotalPerCamp.DataSource = dtTotal; gvTotalPerCamp.DataBind(); divPerCamp.Visible = true; } else if (param.CamperOrg == CamperOrgType.Synagogue) { DataTable dt = CamperApplicationDA.GetFJCCamperReport(param.CamperOrg, param.ProgramTypeID, param.FedID, param.CampYearID, param.CampsThatHaveDataDict.ElementAt(Int32.Parse(txtPage.Text) - 1).Key, param.StatusID_List); dtTotal = CreateTotalTablePerSynag(dt); gvPerSynag.DataSource = dt; gvPerSynag.DataBind(); gvTotalPerSynag.DataSource = dtTotal; gvTotalPerSynag.DataBind(); divPerSynag.Visible = true; } else if (param.CamperOrg == CamperOrgType.CamperContactInfo) { DataTable dt = CamperApplicationDA.GetCamperContactInfoData(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampsThatHaveDataDict.ElementAt(Int32.Parse(txtPage.Text) - 1).Key, param.StatusID_List, param.TimesReceivedGrant); gvCamperContactInfo.DataSource = dt; gvCamperContactInfo.DataBind(); divCamperContactInfo.Visible = true; } else if (param.CamperOrg == CamperOrgType.CamperDetailReport) { DataTable dt = CamperApplicationDA.GetCamperDetailReport(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampsThatHaveDataDict.ElementAt(Int32.Parse(txtPage.Text) - 1).Key, param.StatusID_List, param.TimesReceivedGrant); gvCamperDetailReport.DataSource = dt; gvCamperDetailReport.DataBind(); divCamperDetailReport.Visible = true; } }
public static DataSet GetCamperByState(int CampYearID, string FedID_List, string CampID_List, string StatusID_List, int TimesReceivedGrant) { using (DataSet ds = CamperApplicationDA.GetCamperByState(CampYearID, FedID_List, CampID_List, StatusID_List, TimesReceivedGrant)) { DataSet dsOutput = TransformTablesForCamperSummaryByState(ds); Utility.AssignTableNames(dsOutput, TimesReceivedGrant); foreach (DataTable dt in dsOutput.Tables) { Utility.CreateTotalColumnAndRow(dt, "Total"); } return(dsOutput); } }
/// <summary> /// The function is created to filter out the selected camps that don't have selected status. Users could get annoyed by seeing /// so man empty camps that has no matching status, so we do the extra step to filter out. /// </summary> /// <returns></returns> private int CreateCampThatHaveDataDictionaryObject() { // Get all the camps that have data with selected status DataTable dtCampHaveData = null; // 2010-01-23 switch explosion. Strategy pattern is needed if one more switch is added if (param.CamperOrg == CamperOrgType.Synagogue) { dtCampHaveData = CamperApplicationDA.GetSynagsListThatHaveData(param.CampYearID, param.CampID_List, param.StatusID_List, param.FedID); } else if (param.CamperOrg == CamperOrgType.EnrollmentConfirmationFJC || param.CamperOrg == CamperOrgType.EnrollmentConfirmationPartner) { // if (UserRole == Role.FJCAdmin) { dtCampHaveData = CamperApplicationDA.GetCampsListThatHaveData(param.CampYearID, param.ProgramTypeID, param.CampID_List, param.StatusID_List, param.FedID, param.TimesReceivedGrant); } else if (UserRole == Role.FederationAdmin) { dtCampHaveData = CamperApplicationDA.GetCampsListThatHaveData(param.CampYearID, param.ProgramTypeID, param.CampID_List, param.StatusID_List, param.FedID, param.TimesReceivedGrant); } else { // 2010-01-20 Haven't tested other roles yet, used the most stringent condition, that is, with FedID dtCampHaveData = CamperApplicationDA.GetCampsListThatHaveData(param.CampYearID, param.ProgramTypeID, param.CampID_List, param.StatusID_List, param.FedID, param.TimesReceivedGrant); } } else if (param.CamperOrg == CamperOrgType.CamperContactInfo || param.CamperOrg == CamperOrgType.CamperDetailReport) { dtCampHaveData = CamperApplicationDA.GetCampsListThatHaveDataDAForCamperContactInfo(param.CampYearID, param.FedID_List, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant); } string campIDHaveData_list = ""; foreach (DataRow dr in dtCampHaveData.Rows) { param.CampsThatHaveDataDict.Add(Convert.ToInt32(dr["CampID"]), dr["Name"].ToString()); campIDHaveData_list += dr["CampID"].ToString(); if (dtCampHaveData.Rows.IndexOf(dr) != dtCampHaveData.Rows.Count - 1) { campIDHaveData_list += ", "; } } param.CampID_HaveData_List = campIDHaveData_list; // Remember to save the changes back to session Session["ReportParamCampersFJC"] = param; return(param.CampsThatHaveDataDict.Count); }
public static DataSet GetCamperSummaryReportByCamp(int CampYearID, string FedID_List, string CampID_List, string StatusID_List, int TimesReceivedGrant) { DataSet ds = CamperApplicationDA.GetCamperSummaryReportByCamp(CampYearID, FedID_List, CampID_List, StatusID_List, TimesReceivedGrant); // Make all the tables have the same number of rows (camps) DataSet dsOutput = TransformTablesForCamperSummaryReportByCamp(ds); Utility.AssignTableNames(dsOutput, TimesReceivedGrant); // Create the total row for each table foreach (DataTable dt in dsOutput.Tables) { Utility.CreateTotalRow(dt, "Total"); } return(dsOutput); }
public static DataSet GetCamperTimesInCampCountByState(int CampYearID, string FedID_List, string StatusName_List, int TimesReceivedGrant) { StatusName_List += ", [Total By Program]"; DataSet ds = CamperApplicationDA.GetCamperCountByFed(CampYearID, FedID_List, StatusName_List, TimesReceivedGrant); var nameList = new List <string>(); if ((TimesReceivedGrant & 1) > 0) { nameList.Add("AllCampers"); } if ((TimesReceivedGrant & 2) > 0) { nameList.Add("1stYearCampers"); } if ((TimesReceivedGrant & 4) > 0) { nameList.Add("2ndYearCampers"); } if ((TimesReceivedGrant & 8) > 0) { nameList.Add("3rdYearCampers"); } int i = 0; foreach (string name in nameList) { ds.Tables[i].TableName = name; i++; } foreach (DataTable dt in ds.Tables) { Utility.CreateTotalColumnAndRow(dt, "Total By Status"); } return(ds); }
public static DataTable GetDuplicateCampers(int CampYearID, string StatusID_List) { DataTable dt = CamperApplicationDA.GetDuplicateCampers(CampYearID, StatusID_List); //var nameList = new List<string>(); //nameList.Add("AllCampers"); //int i = 0; //foreach (string name in nameList) //{ // ds.Tables[i].TableName = name; // i++; //} //foreach (DataTable dt in ds.Tables) //{ // Utility.CreateTotalColumnAndRow(dt, "Total By Status"); //} return(dt); }
public static DataSet GetCamperDetailReportInBatch(Role UserRole, int FedID, string FedID_List, int CampYearID, string CampID_List, string StatusID_List, int TimesReceivedGrant) { DataTable dt; dt = CamperApplicationDA.GetCamperDetailReportInBatch(UserRole, FedID, FedID_List, CampYearID, CampID_List, StatusID_List, TimesReceivedGrant); var ds = new DataSet(); // now, create each table per camp, and store each table into a dataset if (dt.Rows.Count > 0) { int LastCampID = Convert.ToInt32(dt.Rows[0]["CampID"]); int CampID = LastCampID; DataTable dtTemp = dt.Clone(); foreach (DataRow dr in dt.Rows) { CampID = Convert.ToInt32(dr["CampID"]); if (CampID != LastCampID) { ds.Tables.Add(dtTemp); dtTemp = null; dtTemp = dt.Clone(); } LastCampID = CampID; dtTemp.ImportRow(dr); } // the last table must also be added because it never goes into the if condition right above ds.Tables.Add(dtTemp); } // Delete the first column, because we don't need to display it foreach (DataTable dtt in ds.Tables) { dtt.Columns.RemoveAt(0); } return(ds); }
protected void btnReport_Click(object sender, EventArgs e) { DataSet ds; // Work sheet #1 - Data grouped by camp // the data content section of the report if (param.CamperOrg == CamperOrgType.CamperContactInfo) { ds = CamperApplicationBL.GetCamperContactInfoReportInBatch(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant); } else if (param.CamperOrg == CamperOrgType.CamperDetailReport) { ds = CamperApplicationBL.GetCamperDetailReportInBatch(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant); } else { ds = CamperApplicationBL.GetFJCCamperReportInBatch(param.CamperOrg, param.ProgramTypeID, param.FedID, param.CampYearID, param.CampID_HaveData_List, param.StatusID_List, param.TimesReceivedGrant); } string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls"); string workFileDir = Server.MapPath(@"~/Docs"); // Make a excel report ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7"); ExcelFile excel = new ExcelFile(); excel.LoadXls(templateFile); ExcelWorksheet ws = excel.Worksheets["Sheet2"]; //We start at first row, because for ExcelLite control, the header row is not included int BEGIN_COLUMN_INDEX; if (param.CamperOrg == CamperOrgType.CamperContactInfo || param.CamperOrg == CamperOrgType.CamperDetailReport) { BEGIN_COLUMN_INDEX = 0; } else { BEGIN_COLUMN_INDEX = 1; } const int CAMP_NAME_MERGED_CELL_NUMBER = 6; const int REPORT_HEADER_CELL_NUMBER = 6; const int REPORT_SUB_HEADER_CELL_NUMBER = 4; int iRow = 1; // Global artistic seeting ws.Columns[0].Width = 20 * 20; // Create Report Header CellStyle styleReportHeader = new CellStyle(); styleReportHeader.Font.Color = System.Drawing.Color.Blue; styleReportHeader.Font.Size = 22 * 20; styleReportHeader.Font.Weight = ExcelFont.BoldWeight; CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER); ReportHeader.Merged = true; ReportHeader.Style = styleReportHeader; var fileName = "report"; ReportHeader.Value = fileName = GetReportName(param.CamperOrg); ws.Name = "By Camp"; // iRow += 1; // Create Report SubHeader CellStyle styleSubHeader = new CellStyle(); styleSubHeader.Font.Size = 16 * 20; styleSubHeader.Font.Weight = ExcelFont.BoldWeight; CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER); SubHeader.Merged = true; SubHeader.Value = String.Format("Camp Year: {0} Generated on {1}", param.CampYear, DateTime.Now); SubHeader.Style = styleSubHeader; iRow += 2; CellStyle cs = new CellStyle(); //cs.Font.Color = System.Drawing.Color.Red; cs.Font.Size = 18 * 20; cs.Font.Weight = ExcelFont.BoldWeight; CellStyle csFirst = new CellStyle(); csFirst.FillPattern.SetSolid(Color.Yellow); csFirst.Font.Weight = ExcelFont.BoldWeight; csFirst.WrapText = true; ws.Columns[0].Width = 20 * 256; CellStyle tableHeaderStyle = new CellStyle(); tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight; tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray); CellStyle redText = new CellStyle(); redText.Font.Color = Color.Red; foreach (DataTable dt in ds.Tables) { CellRange cr = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER); cr.Merged = true; cr.Value = dt.Rows[0]["Camp Name"].ToString(); //if (param.CamperOrg == CamperOrgType.CamperContactInfo) //{ // cr.Value = dt.Rows[0]["Camp Name"].ToString(); //} //else //{ // cr.Value = param.CampsThatHaveDataDict.ElementAt(ds.Tables.IndexOf(dt)).Value; //} cr.Style = cs; ws.Rows[iRow].Height = 25 * 20; iRow += 1; ws.Rows[iRow].Height = 25 * 20; CellRange crFirst = ws.Cells.GetSubrangeAbsolute(iRow, 0, iRow, 0); if (param.CamperOrg != CamperOrgType.CamperContactInfo && param.CamperOrg != CamperOrgType.CamperDetailReport) { crFirst.Value = "Registered at Camp: Enter a Y / N here"; crFirst.Style = csFirst; } ws.InsertDataTable(dt, iRow, BEGIN_COLUMN_INDEX, true); // 2012-10-28 Need to make allow contact emails stand out so admins won't contact campers who wish not to be contacted if (param.CamperOrg == CamperOrgType.CamperContactInfo) { for (int i = 0; i <= dt.Rows.Count; i++) { if (ws.Cells[i + iRow, 14].Value.ToString() == "No") { ws.Cells[i + iRow, 14].Style = redText; ws.Cells[i + iRow, 15].Style = redText; ws.Cells[i + iRow, 16].Style = redText; } } } else { for (int i = 0; i <= dt.Rows.Count; i++) { //if (ws.Cells[i + iRow, 14].Value.ToString() == "No") //{ // ws.Cells[i + iRow, 14].Style = redText; // ws.Cells[i + iRow, 15].Style = redText; //} } } // loop through each column and 1.set the width of each colum, 2. set the header style of each column for (int i = BEGIN_COLUMN_INDEX; i <= dt.Columns.Count; i++) { if (param.CamperOrg == CamperOrgType.CamperContactInfo) { ws.Cells[iRow, i].Style = tableHeaderStyle; if (i == 2) { ws.Columns[i].Width = 22 * 256; } else if (i == 3) { ws.Columns[i].Width = 16 * 256; } else if (i == 4) { ws.Columns[i].Width = 16 * 256; } else if (i == 5) { ws.Columns[i].Width = 16 * 256; } else if (i == 7) { ws.Columns[i].Width = 23 * 256; } else if (i == 5) { ws.Columns[i].Width = 15 * 256; } else if (i == 11) { ws.Columns[i].Width = 26 * 256; } else if (i == 12) { ws.Columns[i].Width = 15 * 256; } else if (i == 13) { ws.Columns[i].Width = 10 * 256; } else if (i == 14) { ws.Columns[i].Width = 10 * 256; } else { ws.Columns[i].Width = 20 * 256; } } else { ws.Cells[iRow, i].Style = tableHeaderStyle; if (i == 3) { ws.Columns[i].Width = 30 * 256; } else if (i == 4) { ws.Columns[i].Width = 16 * 256; } else if (i == 5) { ws.Columns[i].Width = 16 * 256; } else if (i == 7) { ws.Columns[i].Width = 23 * 256; } else if (i == 5) { ws.Columns[i].Width = 15 * 256; } else if (i == 11) { ws.Columns[i].Width = 26 * 256; } else if (i == 12) { ws.Columns[i].Width = 15 * 256; } else { ws.Columns[i].Width = 20 * 256; } } } iRow += dt.Rows.Count + 2; DataTable dtTotal; if (param.CamperOrg == CamperOrgType.EnrollmentConfirmationFJC || param.CamperOrg == CamperOrgType.EnrollmentConfirmationPartner) { dtTotal = CreateTotalTablePerCamp(dt); } else if (param.CamperOrg == CamperOrgType.Synagogue) { dtTotal = CreateTotalTablePerSynag(dt); } else { dtTotal = null; } if (param.CamperOrg != CamperOrgType.CamperContactInfo && param.CamperOrg != CamperOrgType.CamperDetailReport) { ws.InsertDataTable(dtTotal, iRow, BEGIN_COLUMN_INDEX, true); for (int i = BEGIN_COLUMN_INDEX; i <= dtTotal.Columns.Count; i++) { ws.Cells[iRow, i].Style = tableHeaderStyle; } iRow += dtTotal.Rows.Count + 3; } } // ************************** second tab ******************************** // Second alternate sheet that list all rows as table format // Camper Detail Report (for Program admin) doesn't need to show the whole list if (param.CamperOrg != CamperOrgType.EnrollmentConfirmationFJC && param.CamperOrg != CamperOrgType.EnrollmentConfirmationPartner) { var ws2 = excel.Worksheets["Sheet1"]; ws2.Name = fileName; DataTable dtAlternate; if (param.CamperOrg == CamperOrgType.CamperContactInfo) { dtAlternate = CamperApplicationDA.GetCamperContactInfoReportInBatch(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant); } else if (param.CamperOrg == CamperOrgType.CamperDetailReport) { dtAlternate = CamperApplicationDA.GetCamperDetailReportInBatch(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant); } else { dtAlternate = CamperApplicationDA.GetFJCCamperReportInBatch(param.CamperOrg, param.ProgramTypeID, param.FedID, param.CampYearID, param.CampID_HaveData_List, param.StatusID_List); } dtAlternate.Columns.RemoveAt(0); // We don't want the first column, which is CampID iRow = 1; ReportHeader = ws2.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER); ReportHeader.Merged = true; ReportHeader.Style = styleReportHeader; ReportHeader.Value = GetReportName(param.CamperOrg); ws2.Rows[iRow].Height = 25 * 20; iRow += 1; ws2.Rows[iRow].Height = 25 * 20; // Create Report SubHeader SubHeader = ws2.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER); SubHeader.Merged = true; SubHeader.Value = "Camp Year: " + param.CampYear; SubHeader.Style = styleSubHeader; iRow += 2; ws2.InsertDataTable(dtAlternate, iRow, BEGIN_COLUMN_INDEX, true); // 2012-10-28 Need to make allow contact emails stand out so admins won't contact campers who wish not to be contacted if (param.CamperOrg == CamperOrgType.CamperContactInfo) { for (int i = 0; i <= dtAlternate.Rows.Count; i++) { if (ws2.Cells[i + iRow, 14].Value.ToString() == "No") { ws2.Cells[i + iRow, 14].Style = redText; ws2.Cells[i + iRow, 15].Style = redText; ws2.Cells[i + iRow, 16].Style = redText; } } } ws2.Rows[iRow].Style = tableHeaderStyle; if (param.CamperOrg == CamperOrgType.CamperContactInfo) { ws2.Columns[BEGIN_COLUMN_INDEX].Width = 16 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 1].Width = 28 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 2].Width = 28 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 3].Width = 14 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 4].Width = 16 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 5].Width = 23 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 10].Width = 26 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 11].Width = 15 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 14].Width = 25 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 15].Width = 25 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 16].Width = 25 * 256; } else { // Camper Detail Report ws2.Columns[BEGIN_COLUMN_INDEX].Width = 15 * 256; // CampID ws2.Columns[BEGIN_COLUMN_INDEX + 1].Width = 30 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 2].Width = 25 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 3].Width = 25 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 4].Width = 30 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 5].Width = 18 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 6].Width = 30 * 256; // Camp Name ws2.Columns[BEGIN_COLUMN_INDEX + 9].Width = 22 * 256; // Session Datea ws2.Columns[BEGIN_COLUMN_INDEX + 10].Width = 18 * 256; // Timer ws2.Columns[BEGIN_COLUMN_INDEX + 12].Width = 25 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 13].Width = 25 * 256; ws2.Columns[BEGIN_COLUMN_INDEX + 14].Width = 25 * 256; } } else { // since there is no whole list report, we need to delete sheet1 excel.Worksheets[0].Delete(); } excel.Worksheets.ActiveWorksheet = excel.Worksheets[0]; // Save to a file on the local file system string filename = String.Format("\\{0}{1}{2}{3} {4}.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond, fileName); string newFile = workFileDir + filename; excel.SaveXls(newFile); string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None); //Display excel spreadsheet this.Response.Clear(); this.Response.Buffer = true; this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]); this.Response.ContentType = "application/vnd.ms-excel"; this.Response.Charset = ""; if (newFile.Length == 0) { this.Response.Write("Error encountered - no spreadsheet to display"); } else { this.Response.WriteFile(newFile); } Response.End(); }
private DataTable GenerateDataTable() { DataSet ds = CamperApplicationDA.GetFJCAllocationData(Int32.Parse(ddlCampYear.SelectedValue)); DataTable dtMain = ds.Tables[0]; DataTable dtOverage = ds.Tables[1]; DataTable dtFirstSecondTimers = ds.Tables[2]; dtMain.Columns.Add(new DataColumn("FJC Standard", System.Type.GetType("System.Int32"))); dtMain.Columns.Add(new DataColumn("FJC Overage", System.Type.GetType("System.Int32"))); dtMain.Columns.Add(new DataColumn("Partner Allocated", System.Type.GetType("System.Int32"))); // need this for the correct column order in excel report dtMain.Columns.Add(new DataColumn("Total Campers", System.Type.GetType("System.Int32"))); dtMain.Columns.Add(new DataColumn("First Time", System.Type.GetType("System.Int32"))); dtMain.Columns.Add(new DataColumn("Second Time", System.Type.GetType("System.Int32"))); foreach (DataRow dr in dtMain.Rows) { // This line of code is purely for excel report, because we have to make sure excel report's table columns follow the same order as in html gridview dr["Partner Allocated"] = dr["PartnerAllocated"]; // first step, filter out those data that has not been selected bool isSelected = false; foreach (ListItem li in chklistFed.Items) { if (li.Selected) { if (li.Value == dr["FederationID"].ToString()) { isSelected = true; } } } if (!isSelected) { dr.Delete(); } else { // this federation is selected, so we must process the data DataRow[] drows = dtOverage.Select("FederationID = " + dr["FederationID"]); if (drows.Length > 0) { dr["FJC Overage"] = drows[0]["Overage"]; int FJCStandard = Convert.ToInt32(dr["FJCMatch"]) - Convert.ToInt32(dr["FJC Overage"]); if (FJCStandard != 0) { dr["FJC Standard"] = FJCStandard; } } else { dr["FJC Standard"] = dr["FJCMatch"]; } // First timer and Second timer calculation int firstTimer = 0; int secondTimer = 0; drows = dtFirstSecondTimers.Select("FederationID = " + dr["FederationID"]); if (drows.Length > 0) { foreach (DataRow mydr in drows) { // 2010/01/07 Now there is an easier way to access first/seconder timer data in CamperApplications table if (mydr["TimeInCamp"] is int) { int TimeInCamp = Convert.ToInt32(mydr["TimeInCamp"]); if (TimeInCamp == 1) { firstTimer = Convert.ToInt32(mydr["CamperCount"]); dr["First Time"] = firstTimer; } else if (TimeInCamp == 2) { secondTimer = Convert.ToInt32(mydr["CamperCount"]); dr["Second Time"] = secondTimer; } } } } dr["Total Campers"] = firstTimer + secondTimer; } } DataRow newrow = dtMain.NewRow(); newrow[0] = "Total"; int total = 0; for (int i = 1; i < dtMain.Columns.Count; i++) { try { total = Int32.Parse(dtMain.Compute(String.Format("SUM([{0}])", dtMain.Columns[i].ColumnName), "").ToString()); } catch { total = 0; } newrow[i] = total; } dtMain.Rows.Add(newrow); return(dtMain); }