private DataSet GenerateRequiredDataForWordDocCreation(DataSet dsFederationDetails) { DataSet dsDocTables = new DataSet(); string FedName = string.Empty; if (dsFederationDetails.Tables.Count > 0) { if (dsFederationDetails.Tables[1] != null) { DataTable dtBasicFederationDetails = new DataTable(); dtBasicFederationDetails.Columns.Add("PartnerName"); dtBasicFederationDetails.Columns.Add("ContactName"); dtBasicFederationDetails.Columns.Add("ContactEmail"); dtBasicFederationDetails.Columns.Add("ContactPhone"); dtBasicFederationDetails.Columns.Add("SummaryPageContent"); dtBasicFederationDetails.Columns.Add("FirstTimeCamperDefinition"); dtBasicFederationDetails.Columns.Add("SecondTimeCamperDefinition"); dtBasicFederationDetails.Columns.Add("GradeEligibility"); dtBasicFederationDetails.Columns.Add("DaySchoolEligibility"); dtBasicFederationDetails.Columns.Add("FirstQuestion"); dtBasicFederationDetails.Columns.Add("SecondQuestion"); dtBasicFederationDetails.Columns.Add("DaySchoolNames"); dtBasicFederationDetails.Columns.Add("FederationName"); foreach (DataRow dr in dsFederationDetails.Tables[1].Rows) //First 5 fields { int iFederationId = Int32.Parse(dr["FederationID"].ToString()); DataRow drDocData = dtBasicFederationDetails.NewRow(); drDocData["PartnerName"] = dr["PartnerName"].ToString(); FedName = dr["PartnerName"].ToString(); drDocData["ContactName"] = dr["ContactName"].ToString(); drDocData["ContactEmail"] = dr["ContactEmail"].ToString(); drDocData["ContactPhone"] = dr["ContactPhone"].ToString(); if (dr["NavigationURL"].ToString().ToLower().Contains("summary.aspx")) { drDocData["SummaryPageContent"] = GetSummaryPageContent(dr["NavigationURL"].ToString().ToLower(), iFederationId); GetQuestionnaire(dr["NavigationURL"].ToString().ToLower().Replace("Summary.aspx", "Step2_2.aspx"), iFederationId); //added by sandhya string firstquestion = GetfirstQuestionnaire(dr["NavigationURL"].ToString().ToLower().Replace("Summary.aspx", "Step2_2.aspx"), iFederationId); drDocData["FirstQuestion"] = firstquestion; string secondquestion = GetsecondQuestionnaire(dr["NavigationURL"].ToString().ToLower().Replace("Summary.aspx", "Step2_2.aspx"), iFederationId); drDocData["SecondQuestion"] = secondquestion; } drDocData["GradeEligibility"] = CIPMSManager.GetGradeEligibilityRange(iFederationId); //FedGradeEligibility fedGradeEligibility = (FedGradeEligibility)iFederationId; //drDocData["GradeEligibility"] = General.GetEnumDescription(fedGradeEligibility); //FedName =((FedGradeEligibility)iFederationId).ToString(); FedName = ((FedFolderName)iFederationId).ToString(); FedDaySchoolEligibility fedDaySchoolEligibility = (FedDaySchoolEligibility)iFederationId; drDocData["DaySchoolEligibility"] = CIPMSManager.GetEnumDescription(fedDaySchoolEligibility); //string DaySchoolEligibility = drDocData["DaySchoolEligibility"].ToString(); //if (DaySchoolEligibility == "true") //{ // if (dr["NavigationURL"].ToString().ToLower().Contains("summary.aspx")) // { // drDocData["DaySchoolNames"] = GetDaySchoolNames(dr["NavigationURL"].ToString().ToLower().Replace("summary.aspx", "Step2_2.aspx"), iFederationId); // } //} drDocData["FederationName"] = FedName.Trim(); dtBasicFederationDetails.Rows.Add(drDocData); } DataTable dtZipCodes = new DataTable(); dtZipCodes.Columns.Add("FederationID"); dtZipCodes.Columns.Add("ZipCode"); // String FileName = FedName + "" + System.DateTime.Now.Year + "" + System.DateTime.Now.Month+ "" + System.DateTime.Now.Day +".xlsx"; //String FileName = FedName.Trim() + ".xlsx"; String FileName = String.Format(@"{0}\Docs\ProgramProfileDocs\{1}.xlsx", Server.MapPath(HttpContext.Current.Request.ApplicationPath), FedName.Trim()); FileInfo newFile = new FileInfo(FileName); if (newFile.Exists) { newFile.Delete(); } newFile = new FileInfo(FileName); //FileInfo newFile = new FileInfo(@"D:\template.xlsx"); using (ExcelPackage xlPackage = new ExcelPackage(newFile)) { ExcelWorksheet worksheet; int i; if (dsFederationDetails.Tables[7].Rows.Count > 0) { worksheet = xlPackage.Workbook.Worksheets.Add("ZipCodes"); i = 1; worksheet.Cell(i, 1).Value = "ZipCodes"; i = i + 1; worksheet.Cell(i, 1).Value = ""; foreach (DataRow dr in dsFederationDetails.Tables[7].Rows) { DataRow drZipData = dtZipCodes.NewRow(); drZipData["FederationID"] = dr["FederationID"].ToString(); drZipData["ZipCode"] = dr["ZipCode"].ToString(); worksheet.Cell(i + 1, 1).Value = dr["ZipCode"].ToString(); // tins Carrots sold i++; xlPackage.Save(); dtZipCodes.Rows.Add(drZipData); } } DataTable dtGrantAmount = new DataTable(); dtGrantAmount.Columns.Add("FederationID"); dtGrantAmount.Columns.Add("CampName"); dtGrantAmount.Columns.Add("TimeInCamp"); dtGrantAmount.Columns.Add("DaysAtleast"); dtGrantAmount.Columns.Add("GrantAmount"); //newFile = null; // newFile = new FileInfo("D:\\" + FileName); if (dsFederationDetails.Tables[6].Rows.Count > 0) { //worksheet = xlPackage.Workbook.Worksheets.Add("GrantAmount"); //i = 1; //worksheet.Cell(i, 1).Value = "Camp Name"; //worksheet.Cell(i, 2).Value = "TimeInCamp"; //worksheet.Cell(i, 3).Value = "DaysAtLeast"; //worksheet.Cell(i, 4).Value = "Grant Amount"; //i = i + 1; //worksheet.Cell(i, 1).Value = ""; //worksheet.Cell(i, 2).Value = ""; //worksheet.Cell(i, 3).Value = ""; //worksheet.Cell(i, 4).Value = ""; //foreach (DataRow dr in dsFederationDetails.Tables[6].Rows) //{ // DataRow drGrantAmount = dtGrantAmount.NewRow(); // drGrantAmount["CampName"] = dr["Name"].ToString(); // drGrantAmount["TimeInCamp"] = dr["TimeInCamp"].ToString(); // drGrantAmount["DaysAtleast"] = dr["DaysAtleast"].ToString(); // drGrantAmount["GrantAmount"] = dr["GrantAmount"].ToString(); // worksheet.Cell(i + 1, 1).Value = dr["Name"].ToString().Replace('\'', ' '); // worksheet.Cell(i + 1, 2).Value = dr["TimeInCamp"].ToString(); // worksheet.Cell(i + 1, 3).Value = dr["DaysAtleast"].ToString(); // worksheet.Cell(i + 1, 4).Value = dr["GrantAmount"].ToString(); // i++; // xlPackage.Save(); // dtGrantAmount.Rows.Add(drGrantAmount); //} } //added by sandhya 08/07 DataTable dtSynagogues = new DataTable(); dtSynagogues.Columns.Add("FederationID"); dtSynagogues.Columns.Add("SynagogueName"); // newFile = null; //newFile = new FileInfo("D:\\" + FileName); if (dsFederationDetails.Tables[5].Rows.Count > 0) { worksheet = xlPackage.Workbook.Worksheets.Add("Synagogues"); i = 1; worksheet.Cell(i, 1).Value = "Synagogues"; // tins Carrots sold i = i + 1; worksheet.Cell(i, 1).Value = ""; foreach (DataRow dr in dsFederationDetails.Tables[5].Rows) { DataRow drSynagogueData = dtSynagogues.NewRow(); drSynagogueData["FederationID"] = dr["FederationID"].ToString(); drSynagogueData["SynagogueName"] = dr["SynagogueName"].ToString(); worksheet.Cell(i + 1, 1).Value = dr["SynagogueName"].ToString().Replace('\'', ' '); i++; xlPackage.Save(); dtSynagogues.Rows.Add(drSynagogueData); } } DataTable dtReferralCodes = new DataTable(); dtReferralCodes.Columns.Add("FederationID"); dtReferralCodes.Columns.Add("PromotionalCode"); // newFile = null; // newFile = new FileInfo("D:\\" + FileName); if (dsFederationDetails.Tables[4].Rows.Count > 0) { worksheet = xlPackage.Workbook.Worksheets.Add("Referral Codes"); i = 1; worksheet.Cell(i, 1).Value = "Referral Codes"; // tins Carrots sold i = i + 1; worksheet.Cell(i, 1).Value = ""; foreach (DataRow dr in dsFederationDetails.Tables[4].Rows) { DataRow drReferralCodesData = dtReferralCodes.NewRow(); drReferralCodesData["FederationID"] = dr["FederationID"].ToString(); drReferralCodesData["PromotionalCode"] = dr["PromotionalCode"].ToString(); worksheet.Cell(i + 1, 1).Value = dr["PromotionalCode"].ToString(); i++; xlPackage.Save(); dtReferralCodes.Rows.Add(drReferralCodesData); } } DataTable dtCamps = new DataTable(); dtCamps.Columns.Add("FederationID"); dtCamps.Columns.Add("CampName"); // newFile = null; //newFile = new FileInfo("D:\\" + FileName); if (dsFederationDetails.Tables[3].Rows.Count > 0) { worksheet = xlPackage.Workbook.Worksheets.Add("Camps"); i = 1; worksheet.Cell(i, 1).Value = "Eligible Camps"; i = i + 1; worksheet.Cell(i, 1).Value = ""; foreach (DataRow dr in dsFederationDetails.Tables[3].Rows) { DataRow drCampsData = dtCamps.NewRow(); drCampsData["FederationID"] = dr["FederationID"].ToString(); drCampsData["CampName"] = dr["CampName"].ToString(); worksheet.Cell(i + 1, 1).Value = dr["CampName"].ToString().Replace('\'', ' '); i++; xlPackage.Save(); dtCamps.Rows.Add(drCampsData); } } dsDocTables.Tables.Add(dtBasicFederationDetails); dsDocTables.Tables.Add(dtZipCodes); dsDocTables.Tables.Add(dtGrantAmount); dsDocTables.Tables.Add(dtSynagogues); dsDocTables.Tables.Add(dtReferralCodes); dsDocTables.Tables.Add(dtCamps); } //added by sreevani to delete excel created if records does not exist corresponding to that federation if (dsFederationDetails.Tables[3].Rows.Count == 0 && dsFederationDetails.Tables[4].Rows.Count == 0 && dsFederationDetails.Tables[5].Rows.Count == 0 && dsFederationDetails.Tables[7].Rows.Count == 0) { newFile.Delete(); } } } return(dsDocTables); }
private void GenerateDocxReport(string strSelectedFedList) { DataSet dsFederationDetails = CIPMSManager.GetFederationAndQuestionnaireDetails(strSelectedFedList); DataSet dsDocData = GenerateRequiredDataForWordDocCreation(dsFederationDetails); //commented by sandhya 07/07 //HttpContext.Current.Response.Clear(); //HttpContext.Current.Response.Charset = ""; //HttpContext.Current.Response.ContentType = "application/vnd.doc"; //string strFileName = "GenerateDocument" + ".doc"; //HttpContext.Current.Response.AddHeader("Content-Disposition", "inline;filename=" + strFileName); StringBuilder strHTMLContent = new StringBuilder(); strHTMLContent.Replace("{", ""); strHTMLContent.Replace("{", ""); string FederationName = ""; for (int i = 0; i < dsDocData.Tables[0].Rows.Count; i++) { FederationName = dsDocData.Tables[0].Rows[i][12].ToString(); strHTMLContent.Append("<table border='1'><br><br><br>".ToString()); strHTMLContent.Append( "<tr ><td colspan='2'><h1 align='Center'style='font-family: verdana; font-size:16;color: black:width='100%'><u>" + dsDocData.Tables[0].Rows[i][0].ToString() + "</u></h1></td></tr>"); strHTMLContent.Append("<tr ><td>" + dsDocData.Tables[0].Rows[i][4].ToString() + "</td></tr>"); strHTMLContent.Append("<tr width='100%'><td ><b>Contact Name : </b></td><td >" + dsDocData.Tables[0].Rows[i][1].ToString() + "</td></tr>"); strHTMLContent.Append("<tr width='100%'><td ><b>Contact Email : </b></td><td >" + dsDocData.Tables[0].Rows[i][2].ToString() + "</td></tr>"); strHTMLContent.Append("<tr width='100%'><td ><b>Contact Phone : </b></td><td >" + dsDocData.Tables[0].Rows[i][3].ToString() + "</td></tr>"); strHTMLContent.Append("<tr width='100%'><td ><b>Eligible Grade : </b></td><td >" + dsDocData.Tables[0].Rows[i][7].ToString() + "</td></tr>"); string dayschooleligibility = dsDocData.Tables[0].Rows[i][8].ToString(); //if (strSelectedFedList == "48" || strSelectedFedList == "46" || strSelectedFedList == "37" || strSelectedFedList == "11" || strSelectedFedList == "9") if (dayschooleligibility == "true") { strHTMLContent.Append("<tr width='100%'><td ><b>Day School : </b></td><td > Yes </td></tr>"); // strHTMLContent.Append("<tr width='100%'><td>"+dsDocData.Tables[0].Rows[i][11].ToString() +"</td></tr>"); switch (strSelectedFedList) { //case "48": //{ // strHTMLContent.Append("<tr width='100%'><td>Day Schools List</td></tr>"); //} //case "46": //{ //} //case "37": //{ //} case "11": { strHTMLContent.Append("<tr width='100%'><td colspan='2'><b>Day Schools List</b></td></tr>"); strHTMLContent.Append("<tr width='100%'><td>B'Nai Shalom</td></tr>"); strHTMLContent.Append("<tr width='100%'><td>Hebrew Academy</td></tr>"); break; } case "9": { strHTMLContent.Append("<tr width='100%'><td colspan='2'><b>Day Schools List</b></td></tr>"); strHTMLContent.Append("<tr width='100%'><td>Arie Crown Academy</td></tr>"); strHTMLContent.Append("<tr width='100%'><td>Anshe Emet</td></tr>"); strHTMLContent.Append("<tr width='100%'><td>Akiba Schechter</td></tr>"); strHTMLContent.Append("<tr width='100%'><td>Bais Yaakov</td></tr>"); strHTMLContent.Append("<tr width='100%'><td>Cheder Lubovich</td></tr>"); strHTMLContent.Append("<tr width='100%'><td>Chicago Jewish Day</td></tr>"); strHTMLContent.Append("<tr width='100%'><td>Chicagoland Jewish High School</td></tr>"); strHTMLContent.Append("<tr width='100%'><td>Hillel Torah</td></tr>"); strHTMLContent.Append("<tr width='100%'><td>Solomon Schechter</td></tr>"); break; } } } else { strHTMLContent.Append("<tr width='100%'><td ><b>Day School : </b></td><td >No </td></tr>"); } if (dsDocData.Tables[5].Rows.Count > 0) { //strHTMLContent.Append("<tr width='100%'><td>Camps : </td></tr>"); //for (int p = 0; p < dsDocData.Tables[5].Rows.Count; p++) //{ // strHTMLContent.Append("<tr width='100%'><td>" + dsDocData.Tables[5].Rows[p][1].ToString() + // "</td></tr>"); //} } else { strHTMLContent.Append("<tr width='100%'><td ><b>Eligible Camps : </b></td><td >All camps FJC supports as listed on http:\\www.JewishCamps.org </td></tr>"); } strHTMLContent.Append("<tr ><td ><b>First Time Camper Definition : </b></td><td >" + dsDocData.Tables[0].Rows[i][9].ToString() + "</td></tr>"); // strHTMLContent.Append("<tr width='100%'><td>First Time Camper Defination :Will this be the camper’s first time attending a nonprofit Jewish overnight summer camp for 19 days or longer? </td></tr>"); if (dsDocData.Tables[0].Rows[i][10].ToString() != "No") { strHTMLContent.Append("<tr ><td ><b>Second Time Camper Definition : </b></td><td >" + dsDocData.Tables[0].Rows[i][10].ToString() + "</td></tr>"); } //added by sandhya 08/07 //if (dsDocData.Tables[3].Rows.Count > 0) //{ // strHTMLContent.Append("<tr width='100%'><td> Synagogues </td></tr>"); //} //for (int m = 0; m < dsDocData.Tables[3].Rows.Count; m++) //{ // strHTMLContent.Append("<tr width='100%'><td>" + // dsDocData.Tables[3].Rows[m][1].ToString() + // "</td></tr>"); //} //if (dsDocData.Tables[4].Rows.Count > 0) //{ // if (strSelectedFedList == "48") // { // strHTMLContent.Append("<tr width='100%'><td> Referral Codes </td></tr>"); // } // else // { // if(strSelectedFedList == "63") // { // strHTMLContent.Append("<tr width='100%'><td> Promotional Codes </td></tr>"); // } // } //} //for (int n = 0; n < dsDocData.Tables[4].Rows.Count; n++) //{ // strHTMLContent.Append("<tr width='100%'><td>" + // dsDocData.Tables[4].Rows[n][1].ToString() + // "</td></tr>"); //} //strHTMLContent.Append( // "<tr width='100%'><td>Minimum Days at Camp and Associated Grant Amounts</td></tr>"); //strHTMLContent.Append("<tr></table>"); //strHTMLContent.Append( // "<table border='1'><tr><td>Camp Name</td><td>Time In Camp</td><td>DaysAtleast</td><td>Grant Amount</td></tr>"); //for (int k = 0; k < dsDocData.Tables[2].Rows.Count; k++) //{ // strHTMLContent.Append("<tr>"); // for (int l = 1; l < dsDocData.Tables[2].Columns.Count; l++) // { // strHTMLContent.Append("<td>" + dsDocData.Tables[2].Rows[k][l].ToString() + "</td>"); // } // strHTMLContent.Append("</tr>"); //} //strHTMLContent.Append("<tr width='100%'><td>ZipCodes" + // "</td></tr></table><table border='1'>"); //strHTMLContent.Append("<tr>"); //for (int j = 1; j <= dsDocData.Tables[1].Rows.Count; j++) //{ // strHTMLContent.Append("<td>" + dsDocData.Tables[1].Rows[j - 1][1].ToString() + "</td>"); // if (j % 10 == 0) // { // strHTMLContent.Append("</tr><tr>"); // } //} strHTMLContent.Append("</table>".ToString()); } strHTMLContent.Insert(0, "<html><h1 title='Program Profile Information Report' align='Center'style='font-family: verdana; font-size:16;color: black'><u>Program Profile Information Report</u></h1><body>") .Append("</body></html>"); //HttpContext.Current.Response.Write(strHTMLContent.ToString()); //HttpContext.Current.Response.End(); //HttpContext.Current.Response.Flush(); // FedGradeEligibility fedGradeEligibility = (FedGradeEligibility)iFederationId; //to eliminate the / for word document filename if (FederationName.Trim().Equals("BIMA/Genesis at Brandeis University")) { FederationName = "BIMA Genesis at Brandeis University"; } generatedocxfile(strHTMLContent, FederationName); lblReportMessage.Visible = true; lblReportMessage.Text = "Report was generated successfully"; }
protected void Page_Init(object sender, EventArgs e) { _dataSet = CIPMSManager.getAllFederations(); }