public void OutExcel()
{
try
{
PaperAnswer pa = new PaperAnswer();
_paperAnswerMgr = new PaperAnswerMgr(mySqlConnectionString);
if (!string.IsNullOrEmpty(Request.Params["paper_id"]))
{
pa.paperID = int.Parse(Request.Params["paper_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["user_id"]))
{
pa.userid = int.Parse(Request.Params["user_id"]);
}
DataTable dt = _paperAnswerMgr.Export(pa);
//DataTable ndt = dt.DefaultView.ToTable(false, new string[] { "paperName", "userid", "userMail", "order_id", "className", "classType", "answerContent", "classContent" });
DataTable ndt = dt.DefaultView.ToTable(false, new string[] { "paperName", "userid", "order_id", "className", "classType", "answerContent", "classContent" });
DataTable content = new DataTable();
content = ndt.Clone();
content.Columns.Add("answerDate", typeof(string));
content.Columns.Remove("classContent");
//content.Columns["paperName"].ColumnName = "問卷名稱";
//content.Columns["userid"].ColumnName = "用戶id";
//content.Columns["userMail"].ColumnName = "用戶郵箱";
//content.Columns["className"].ColumnName = "題目名稱";
//content.Columns["classType"].ColumnName = "題目類型";
//content.Columns["answerContent"].ColumnName = "答案";
DataRow dr;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = content.NewRow();
if (i > 0)
{
if (dt.Rows[i]["paperID"].ToString() == dt.Rows[i - 1]["paperID"].ToString())
{
//dr["userid"] = ndt.Rows[i]["userid"];
//dr["userMail"] = ndt.Rows[i]["userMail"];
//content.Rows.Add(dr);
if (dt.Rows[i]["userid"].ToString() == dt.Rows[i - 1]["userid"].ToString() && dt.Rows[i]["answerDate"].ToString() == dt.Rows[i - 1]["answerDate"].ToString())
{
dr["className"] = dt.Rows[i]["className"];
//dr["classType"] = dt.Rows[i]["classType"];
switch (dt.Rows[i]["classType"].ToString())
{
case "SC":
dr["classType"] = "單選";
dr["answerContent"] = dt.Rows[i]["classContent"];
break;
case "MC":
dr["classType"] = "多選";
dr["answerContent"] = dt.Rows[i]["classContent"];
break;
case "SL":
dr["classType"] = "單行";
dr["answerContent"] = dt.Rows[i]["answerContent"];
break;
case "ML":
dr["classType"] = "多行";
dr["answerContent"] = dt.Rows[i]["answerContent"];
break;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["answerDate"].ToString()))
{
dr["answerDate"] = (DateTime.Parse(dt.Rows[i]["answerDate"].ToString())).ToString("yyyy-MM-dd HH:mm:ss");
}
content.Rows.Add(dr);
}
else
{
dr["userid"] = dt.Rows[i]["userid"];
//dr["userMail"] = dt.Rows[i]["userMail"];
dr["order_id"] = dt.Rows[i]["order_id"];
content.Rows.Add(dr);
dr = content.NewRow();
dr["className"] = dt.Rows[i]["className"];
//dr["classType"] = dt.Rows[i]["classType"];
switch (dt.Rows[i]["classType"].ToString())
{
case "SC":
dr["classType"] = "單選";
dr["answerContent"] = dt.Rows[i]["classContent"];
break;
case "MC":
dr["classType"] = "多選";
dr["answerContent"] = dt.Rows[i]["classContent"];
break;
case "SL":
dr["classType"] = "單行";
dr["answerContent"] = dt.Rows[i]["answerContent"];
break;
case "ML":
dr["classType"] = "多行";
dr["answerContent"] = dt.Rows[i]["answerContent"];
break;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["answerDate"].ToString()))
{
dr["answerDate"] = DateTime.Parse(dt.Rows[i]["answerDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
}
content.Rows.Add(dr);
}
}
else
{
dr["paperName"] = dt.Rows[i]["paperName"];
content.Rows.Add(dr);
dr = content.NewRow();
dr["userid"] = dt.Rows[i]["userid"];
//dr["userMail"] = dt.Rows[i]["userMail"];
dr["order_id"] = dt.Rows[i]["order_id"];
content.Rows.Add(dr);
dr = content.NewRow();
dr["className"] = dt.Rows[i]["className"];
//dr["classType"] = dt.Rows[i]["classType"];
switch (dt.Rows[i]["classType"].ToString())
{
case "SC":
dr["classType"] = "單選";
dr["answerContent"] = dt.Rows[i]["classContent"];
break;
case "MC":
dr["classType"] = "多選";
dr["answerContent"] = dt.Rows[i]["classContent"];
break;
case "SL":
dr["classType"] = "單行";
dr["answerContent"] = dt.Rows[i]["answerContent"];
break;
case "ML":
dr["classType"] = "多行";
dr["answerContent"] = dt.Rows[i]["answerContent"];
break;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["answerDate"].ToString()))
{
dr["answerDate"] = DateTime.Parse(dt.Rows[i]["answerDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
}
content.Rows.Add(dr);
}
}
else
{
dr["paperName"] = dt.Rows[i]["paperName"];
content.Rows.Add(dr);
dr = content.NewRow();
dr["userid"] = dt.Rows[i]["userid"];
//dr["userMail"] = dt.Rows[i]["userMail"];
dr["order_id"] = dt.Rows[i]["order_id"];
content.Rows.Add(dr);
dr = content.NewRow();
dr["className"] = dt.Rows[i]["className"];
//dr["classType"] = dt.Rows[i]["classType"];
switch (dt.Rows[i]["classType"].ToString())
{
case "SC":
dr["classType"] = "單選";
dr["answerContent"] = dt.Rows[i]["classContent"];
break;
case "MC":
dr["classType"] = "多選";
dr["answerContent"] = dt.Rows[i]["classContent"];
break;
case "SL":
dr["classType"] = "單行";
dr["answerContent"] = dt.Rows[i]["answerContent"];
break;
case "ML":
dr["classType"] = "多行";
dr["answerContent"] = dt.Rows[i]["answerContent"];
break;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["answerDate"].ToString()))
{
dr["answerDate"] = DateTime.Parse(dt.Rows[i]["answerDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
}
content.Rows.Add(dr);
}
}
content.Columns["paperName"].ColumnName = "問卷名稱";
content.Columns["userid"].ColumnName = "用戶編號";
//content.Columns["userMail"].ColumnName = "用戶郵箱";
content.Columns["order_id"].ColumnName = "訂單編號";
content.Columns["className"].ColumnName = "題目名稱";
content.Columns["classType"].ColumnName = "題目類型";
content.Columns["answerContent"].ColumnName = "答案";
content.Columns["answerDate"].ColumnName = "作答時間";
string fileName = "問卷答案_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(content, "");
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
}
public void OutSinglePaperExcel()
{
try
{
PaperAnswer pa = new PaperAnswer();
PaperClass pc = new PaperClass();
string paper_name = Request.Params["paper_name"];
_paperAnswerMgr = new PaperAnswerMgr(mySqlConnectionString);
if (!string.IsNullOrEmpty(Request.Params["paper_id"]))
{
pa.paperID = int.Parse(Request.Params["paper_id"]);
pc.paperID = int.Parse(Request.Params["paper_id"]);
}
DataTable dtclass = _paperAnswerMgr.GetPaperClassID(pc);
DataTable dtuser = _paperAnswerMgr.GetPaperAnswerUser(pa);
DataTable dtanswer = _paperAnswerMgr.ExportSinglePaperAnswer(pa);
DataTable dt = new DataTable();
dt.Columns.Add("用戶編號", typeof(string));
//dt.Columns.Add("用戶郵箱", typeof(string));
dt.Columns.Add("訂單編號", typeof(string));
dt.Columns.Add("作答時間", typeof(string));
for (int i = 0; i < dtclass.Rows.Count; i++)
{
dt.Columns.Add("題目" + (i + 1), typeof(string));
}
for (int i = 0; i < dtuser.Rows.Count; i++)
{
DataRow dr = dt.NewRow();
dr["用戶編號"] = dtuser.Rows[i]["userid"];
// dr["用戶郵箱"] = dtuser.Rows[i]["userMail"];
dr["訂單編號"] = dtuser.Rows[i]["order_id"];
dr["作答時間"] = DateTime.Parse(dtuser.Rows[i]["answerDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
for (int j = 0; j < dtclass.Rows.Count; j++)
{
DataRow[] drs = dtanswer.Select("userid=" + dtuser.Rows[i]["userid"] + " and answerDate='" + DateTime.Parse(dtuser.Rows[i]["answerDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "' and classID=" + dtclass.Rows[j]["classID"]);
string cname = "題目" + (j + 1);
if (drs.Count() == 0)
{
dr[cname] = "";
}
else if (drs.Count() == 1)
{
if (dtclass.Rows[j]["classType"].ToString() == "MC" || dtclass.Rows[j]["classType"].ToString() == "SC")
{
dr[cname] = drs[0]["classContent"];
}
else if (dtclass.Rows[j]["classType"].ToString() == "ML" || dtclass.Rows[j]["classType"].ToString() == "SL")
{
dr[cname] = drs[0]["answerContent"];
}
}
//多選題目,答案組合
else if (drs.Count() > 1)
{
for (int k = 0; k < drs.Count(); k++)
{
dr[cname] += "|" + drs[k]["classContent"];
}
dr[cname] = dr[cname].ToString().TrimStart('|');
}
}
dt.Rows.Add(dr);
}
if (dt.Rows.Count > 0)
{
string fileName = paper_name + "_答案_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(dt, paper_name);
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
}
else {
Response.Write("會出數據不存在");
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
}
public HttpResponseBase GetPaperAnswerList()
{
string json = string.Empty;
List<PaperAnswer> store = new List<PaperAnswer>();
PaperAnswer pa = new PaperAnswer();
try
{
pa.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
pa.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
_paperAnswerMgr = new PaperAnswerMgr(mySqlConnectionString);
int totalCount = 0;
if (!string.IsNullOrEmpty(Request.Params["paper_id"]))
{
pa.paperID = int.Parse(Request.Params["paper_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["user_id"]))
{
pa.userid = int.Parse(Request.Params["user_id"]);
}
store = _paperAnswerMgr.GetPaperAnswerList(pa, out totalCount);
foreach (var item in store)
{
item.userMail = item.userMail.Split('@')[0] + "@***";
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss ";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
