public void ExcelFile()
{
Models.formDataModel fdData = new Models.formDataModel();
List <Models.formFieldsModel> LstData;
excelDataList = new List <Models.formFieldsModel>();
LstData = fdData.callJotFormAPI();
for (int i = 0; i < LstData.Count; i++)
{
Models.formFieldsModel formFields = new Models.formFieldsModel();
formFields.custId = Int32.Parse(LstData[i].custId.ToString());
formFields.firstName = LstData[i].firstName;
formFields.lastName = LstData[i].lastName;
formFields.country = LstData[i].country;
formFields.email = LstData[i].email;
formFields.favProduct = LstData[i].favProduct;
excelDataList.Add(formFields);
}
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Sheet1");
workSheet.Cells[1, 1].LoadFromCollection(excelDataList, true);
using (var rng = workSheet.Cells["A1:AE" + excelDataList.Count + 1])
{
rng.AutoFitColumns();
}
using (var memoryStream = new MemoryStream())
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + "ExcelReport _" + DateTime.Now.Month + DateTime.Now.Day + DateTime.Now.Year + ".xlsx");
excel.SaveAs(memoryStream);
memoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
public List <Models.formFieldsModel> callJotFormAPI()
{
try
{
SqlDataAdapter adap = new SqlDataAdapter(String.Format(qry_cust), conString);
DataTable dt = new DataTable();
adap.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
int x = Int32.Parse(dr["Customerid"].ToString());
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
System.Net.ServicePointManager.SecurityProtocol |= SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
WebRequest request = WebRequest.Create("https://api.jotform.com/form/203060722539046/submissions?apiKey=4ce97364480710d75dc4ba42c9e6658f&limit=1000"); // formid || APIKEY || limit=1000
HttpWebResponse response = null;
try
{
response = (HttpWebResponse)request.GetResponse();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
Stream dataStream = response.GetResponseStream();
StreamReader reader = new StreamReader(dataStream);
string responseFromServer = reader.ReadToEnd();
JObject c = JObject.Parse(responseFromServer);
JArray items = (JArray)c["content"];
int count = items.Count;
for (int i = 0; i < count; i++)
{
var userSubmission = new Models.formFieldsModel();
// Fetching only required data from Jot form API
if (c["content"][i]["created_at"] != null)
{
userSubmission.createdOn = DateTime.Parse(c["content"][i]["created_at"].ToString());
}
userSubmission.status = c["content"][i]["status"].ToString();
userSubmission.custId = Int32.Parse(c["content"][i]["answers"]["6"]["answer"] != null ? c["content"][i]["answers"]["6"]["answer"].ToString() : "");
userSubmission.firstName = c["content"][i]["answers"]["3"]["answer"] != null ? c["content"][i]["answers"]["3"]["answer"]["first"].ToString() : "";
userSubmission.lastName = c["content"][i]["answers"]["3"]["answer"] != null ? c["content"][i]["answers"]["3"]["answer"]["last"].ToString() : "";
userSubmission.country = c["content"][i]["answers"]["8"]["answer"] != null ? c["content"][i]["answers"]["8"]["answer"].ToString() : "";
userSubmission.favProduct = c["content"][i]["answers"]["7"]["answer"] != null ? c["content"][i]["answers"]["7"]["answer"].ToString() : "";
userSubmission.email = c["content"][i]["answers"]["4"]["answer"] != null ? c["content"][i]["answers"]["4"]["answer"].ToString() : "";
lstformData.Add(userSubmission);
}
lstformData.RemoveAll(x => x.status != "ACTIVE");
lstformData = lstformData.GroupBy(g => new { g.custId, g.favProduct })
.Select(g => g.First())
.ToList();
/*var customerInquiry = lstformData.GroupBy(s => new { s.custId, s.favProduct }).Select(g =>
* new
* {
* custID = g.Key.custId,
* favProd = g.Key.favProduct,
* noOfInquiries = g.Count()
* }).ToList();*/
countUS = lstformData.Where(x => x.country == "United States").Count();
countDE = lstformData.Where(x => x.country == "Germany").Count();
countSG = lstformData.Where(x => x.country == "Singapore").Count();
return(lstformData);
}
