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();
}
}