private void CreateData(ExcelWorksheet ws) { auditid = Request.QueryString["auditid"]; string excel = ""; string status = "true"; int sr = 0; string a = ""; Font f = new Font("Cambria", 11); Font f1 = new Font("Cambria", 10); SqlCommand cnCommand = cn.CreateCommand(); SqlDataReader reader = default(SqlDataReader); try { a = cnCommand.CommandText = @"SELECT Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10,Q11,Q12,Q13,Q14,Q15,Q16,Q17,a.atmid,a.bankid,a.location,vdate,userid from DR_CTP d join atms a on a.atmid = d.ATMID where Vid ='" + auditid + "' "; cn.Open(); reader = cnCommand.ExecuteReader(); // obj.changeformat(ws, f, a, 2, 4, 2, 4); while (reader.Read()) { sr = sr + 1; string q1 = reader[0].ToString().Trim(); string q2 = reader[1].ToString().Trim(); string q3 = reader[2].ToString().Trim(); string q4 = reader[3].ToString().Trim(); string q5 = reader[4].ToString().Trim(); string q6 = reader[5].ToString().Trim(); string q7 = reader[6].ToString().Trim(); string q8 = reader[7].ToString().Trim(); string q9 = reader[8].ToString().Trim(); string q10 = reader[9].ToString().Trim(); string q11 = reader[10].ToString().Trim(); string q12 = reader[11].ToString().Trim(); string q13 = reader[12].ToString().Trim(); string q14 = reader[13].ToString().Trim(); string q15 = reader[14].ToString().Trim(); string q16 = reader[15].ToString().Trim(); string q17 = reader[16].ToString().Trim(); obj.changeformat(ws, f, "", 6, 3, 6, 3, null, null, false, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "", 7, 3, 7, 3, null, null, false, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "", 8, 3, 8, 3, null, null, false, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "", 9, 3, 9, 3, null, null, false, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "", 10, 3, 10, 3, null, null, false, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "Bank Name : " + reader["bankid"].ToString(), 5, 1, 5, 2, null, Color.FromArgb(218, 238, 243), true, false, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "CIT Agency Name : Modern", 6, 1, 6, 2, null, Color.FromArgb(218, 238, 243), true, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "Route Name/No : ", 7, 1, 7, 2, null, Color.FromArgb(218, 238, 243), true, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "Auditor Name : " + reader["USERID"].ToString(), 8, 1, 8, 2, null, Color.FromArgb(218, 238, 243), true, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "ATM ID : " + reader["atmid"].ToString(), 9, 1, 9, 2, null, Color.FromArgb(218, 238, 243), true, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "Date & Time of Audit : " + reader["vdate"].ToString(), 10, 1, 10, 2, null, Color.FromArgb(218, 238, 243), true, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, reader[21].ToString().Trim(), 30, 3, null, null, null, null, false, false, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); checkyesno(ws, q1, 12, 3); checkyesno(ws, q2, 13, 3); checkyesno(ws, q3, 14, 3); checkyesno(ws, q4, 15, 3); checkyesno(ws, q5, 16, 3); checkyesno(ws, q6, 17, 3); checkyesno(ws, q7, 18, 3); checkyesno(ws, q8, 19, 3); checkyesno(ws, q9, 20, 3); checkyesno(ws, q10, 21, 3); checkyesno(ws, q11, 22, 3); checkyesno(ws, q12, 23, 3); checkyesno(ws, q13, 24, 3); checkyesno(ws, q14, 25, 3); checkyesno(ws, q15, 26, 3); checkyesno(ws, q16, 27, 3); checkyesno(ws, q17, 28, 3); ws.Row(30).Height = 25; ws.Row(31).Height = 40.50; } ws.Column(3).Width = 36; ws.Column(5).Width = 17.58; reader.Close(); cn.Close(); AddImage(ws, 31, 3, 31, 3, 150, 50, getimage(auditid, ""), "agscbr1", 935, 90); AddImage(ws, 4, 5, 7, 5, 125, 82, getimage(auditid, "0"), "m1", 935, 90); AddImage(ws, 9, 5, 12, 5, 125, 85, getimage(auditid, "1"), "m2", 935, 90); AddImage(ws, 14, 5, 18, 5, 125, 95, getimage(auditid, "2"), "m3", 935, 90); AddImage(ws, 20, 5, 24, 5, 125, 95, getimage(auditid, "3"), "m4", 935, 90); AddImage(ws, 26, 5, 30, 5, 125, 110, getimage(auditid, "4"), "m5", 935, 90); } catch (Exception ee) { lblexception.Text = a; } finally { if (sr < 1) { lblexception.Text = "No data found"; } else { lblexception.Text = ""; } reader.Close(); cn.Close(); } }
private void CreateData(ExcelWorksheet ws) { auditid = Request.QueryString["auditid"]; string excel = ""; string status = "true"; int sr = 0; string a = ""; Font f = new Font("Cambria", 11); Font f1 = new Font("Cambria", 10); SqlCommand cnCommand = cn.CreateCommand(); SqlDataReader reader = default(SqlDataReader); ws.Column(1).Width = 10; ws.Column(2).Width = 50; try { a = cnCommand.CommandText = @"SELECT Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10,Q11,Q12,Q13,Q14,Q15,Q16,Q17,Q18,Q19,Q20,Q21,Q22,Q23,Q24,Q25,Q26,Q27,Q28,Q29,Q30,Q31,Q32,Q33,Q34,Q35,Q36,Q37,Q38,Q39,Q40, a.atmid,a.bankid,a.location,a.city,vdate,userid,vid from DR_CTP d join atms a on a.atmid = d.ATMID where Vid ='" + auditid + "' "; cn.Open(); reader = cnCommand.ExecuteReader(); // obj.changeformat(ws, f, a, 2, 4, 2, 4); while (reader.Read()) { sr = sr + 1; string q1 = reader[0].ToString().Trim(); string q2 = reader[1].ToString().Trim(); string q3 = reader[2].ToString().Trim(); string q4 = reader[3].ToString().Trim(); string q5 = reader[4].ToString().Trim(); string q6 = reader[5].ToString().Trim(); string q7 = reader[6].ToString().Trim(); string q8 = reader[7].ToString().Trim(); string q9 = reader[8].ToString().Trim(); string q10 = reader[9].ToString().Trim(); string q11 = reader[10].ToString().Trim(); string q12 = reader[11].ToString().Trim(); string q13 = reader[12].ToString().Trim(); string q14 = reader[13].ToString().Trim(); string q15 = reader[14].ToString().Trim(); string q16 = reader[15].ToString().Trim(); string q17 = reader[16].ToString().Trim(); string q18 = reader[17].ToString().Trim(); string q19 = reader[18].ToString().Trim(); string q20 = reader[19].ToString().Trim(); string q21 = reader[20].ToString().Trim(); string q22 = reader[21].ToString().Trim(); string q23 = reader[22].ToString().Trim(); string q24 = reader[23].ToString().Trim(); string q25 = reader[24].ToString().Trim(); string q26 = reader[25].ToString().Trim(); string q27 = reader[26].ToString().Trim(); string q28 = reader[27].ToString().Trim(); string q29 = reader[28].ToString().Trim(); string q30 = reader[29].ToString().Trim(); string q31 = reader[30].ToString().Trim(); string q32 = reader[31].ToString().Trim(); string q33 = reader[32].ToString().Trim(); string q34 = reader[33].ToString().Trim(); string q35 = reader[34].ToString().Trim(); string q36 = reader[35].ToString().Trim(); string q37 = reader[36].ToString().Trim(); string q38 = reader[37].ToString().Trim(); string q39 = reader[38].ToString().Trim(); string q40 = reader[39].ToString().Trim(); //string q41 = reader[40].ToString().Trim(); //string q42 = reader[41].ToString().Trim(); obj.changeformat(ws, f, "", 6, 3, 6, 3, null, null, false, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "", 7, 3, 7, 3, null, null, false, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "", 8, 3, 8, 3, null, null, false, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "", 9, 3, 9, 3, null, null, false, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "", 10, 3, 10, 3, null, null, false, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "", 11, 3, 11, 3, null, null, false, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "Bank Name : " + reader["bankid"].ToString(), 5, 1, 5, 2, null, Color.FromArgb(218, 238, 243), true, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "ATM ID : " + reader["atmid"].ToString(), 6, 1, 6, 2, null, Color.FromArgb(218, 238, 243), true, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "Location : " + reader["location"].ToString(), 7, 1, 7, 2, null, Color.FromArgb(218, 238, 243), true, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "City : " + reader["city"].ToString(), 8, 1, 8, 2, null, Color.FromArgb(218, 238, 243), true, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "VID : " + reader["vid"].ToString(), 9, 1, 9, 2, null, Color.FromArgb(218, 238, 243), true, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "Auditor Name : " + reader["USERID"].ToString(), 10, 1, 10, 2, null, Color.FromArgb(218, 238, 243), true, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); obj.changeformat(ws, f, "Date & Time of Audit : " + reader["vdate"].ToString(), 11, 1, 11, 2, null, Color.FromArgb(218, 238, 243), true, true, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); //obj.changeformat(ws, f, reader[47].ToString().Trim(), 55, 3, null, null, null, null, false, false, false, false, ExcelVerticalAlignment.Center, ExcelHorizontalAlignment.Left, ExcelBorderStyle.Thin); checkyesno(ws, q1, 13, 3); checkyesno(ws, q2, 14, 3); checkyesno(ws, q3, 15, 3); checkyesno(ws, q4, 16, 3); checkyesno(ws, q5, 17, 3); checkyesno(ws, q6, 18, 3); checkyesno(ws, q7, 19, 3); checkyesno(ws, q8, 20, 3); checkyesno(ws, q9, 21, 3); checkyesno(ws, q10, 22, 3); checkyesno(ws, q11, 23, 3); checkyesno(ws, q12, 24, 3); checkyesno(ws, q13, 25, 3); checkyesno(ws, q14, 26, 3); checkyesno(ws, q15, 27, 3); checkyesno(ws, q16, 28, 3); checkyesno(ws, q17, 29, 3); checkyesno(ws, q18, 30, 3); checkyesno(ws, q19, 31, 3); checkyesno(ws, q20, 32, 3); checkyesno(ws, q21, 33, 3); checkyesno(ws, q22, 34, 3); checkyesno(ws, q23, 35, 3); checkyesno(ws, q24, 36, 3); checkyesno(ws, q25, 37, 3); checkyesno(ws, q26, 38, 3); checkyesno(ws, q27, 39, 3); checkyesno(ws, q28, 40, 3); checkyesno(ws, q29, 41, 3); checkyesno(ws, q30, 42, 3); checkyesno(ws, q31, 43, 3); checkyesno(ws, q32, 44, 3); checkyesno(ws, q33, 45, 3); checkyesno(ws, q34, 46, 3); checkyesno(ws, q35, 47, 3); checkyesno(ws, q36, 48, 3); checkyesno(ws, q37, 49, 3); checkyesno(ws, q38, 50, 3); checkyesno(ws, q39, 51, 3); checkyesno(ws, q40, 52, 3); //checkyesno(ws, q41, 53, 3); // checkyesno(ws, q42, 54, 3); ws.Row(55).Height = 90; //ws.Row(56).Height = 40.50; } ws.Column(3).Width = 36; ws.Column(5).Width = 20; ws.Column(7).Width = 20; ws.Column(9).Width = 20; ws.Column(11).Width = 20; reader.Close(); cn.Close(); try { AddImage(ws, 55, 3, 55, 3, 150, 120, getimage(auditid, ""), "agscbr1", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } //for (int i = 4; i <= 21; i = i + 5) //{ // for (int j = 5; j <= 21; j = j + 2) // { // } //} //AddImage(ws, 4, 5, 9, 5, 140, 114, getimage(auditid, "0"), "m1", 935, 90); //AddImage(ws, 4, 5, 9, 5, 140, 114, getimage(auditid, "1"), "m1", 935, 90); //AddImage(ws, 4, 7, 9, 7, 140, 114, getimage(auditid, "2"), "m2", 935, 90); try { AddImage(ws, 4, 5, 9, 5, 140, 114, getimage(auditid, "1"), "m1", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 4, 7, 9, 7, 140, 114, getimage(auditid, "2"), "m2", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 4, 9, 9, 9, 140, 114, getimage(auditid, "3"), "m3", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 4, 11, 9, 11, 140, 114, getimage(auditid, "4"), "m4", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 11, 5, 16, 5, 140, 114, getimage(auditid, "5"), "m5", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 11, 7, 16, 7, 140, 114, getimage(auditid, "6"), "m6", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 11, 9, 16, 9, 140, 114, getimage(auditid, "7"), "m7", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 11, 11, 16, 11, 140, 114, getimage(auditid, "8"), "m8", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 18, 5, 23, 5, 140, 114, getimage(auditid, "9"), "m9", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 18, 7, 23, 7, 140, 114, getimage(auditid, "10"), "m10", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 18, 9, 23, 9, 140, 114, getimage(auditid, "11"), "m11", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 18, 11, 23, 11, 140, 114, getimage(auditid, "12"), "m12", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 25, 5, 30, 5, 140, 114, getimage(auditid, "13"), "m13", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 25, 7, 30, 7, 140, 114, getimage(auditid, "14"), "m14", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 25, 9, 30, 9, 140, 114, getimage(auditid, "15"), "m15", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 25, 11, 30, 11, 140, 114, getimage(auditid, "16"), "m16", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } try { AddImage(ws, 32, 5, 37, 5, 140, 114, getimage(auditid, "17"), "m17", 935, 90); } catch (Exception ex) { ex.Message.ToString(); } //AddImage(ws, 32, 7, 37, 7, 140, 114, getimage(auditid, "17"), "m18", 935, 90); //AddImage(ws, 32, 9, 37, 9, 140, 114, getimage(auditid, "18"), "m19", 935, 90); //AddImage(ws, 32, 11, 37, 11, 140, 114, getimage(auditid, "19"), "m20", 935, 90); //AddImage(ws, 39, 5, 44, 5, 140, 114, getimage(auditid, "20"), "m21", 935, 90); //AddImage(ws, 39, 7, 44, 7, 140, 114, getimage(auditid, "21"), "m22", 935, 90); //AddImage(ws, 39, 9, 44, 9, 140, 114, getimage(auditid, "22"), "m23", 935, 90); //AddImage(ws, 39, 11, 44, 11, 140, 114, getimage(auditid, "23"), "m24", 935, 90); } catch (Exception ee) { lblexception.Text = a; } finally { if (sr < 1) { lblexception.Text = "No data found"; } else { lblexception.Text = ""; } reader.Close(); cn.Close(); } }