public bool Load(string key, string svcode)
{
bool rtn = false;
string sql = "SELECT * FROM prd_ans WHERE id='" + key + "' OR (email='" + key + "' and svcode='" + svcode + "')";
YPSCS.Framework.Data.SQLiteData dc = new Framework.Data.SQLiteData();
dc.CommandText = sql;
dc.Connection.Open();
IDataReader dr = dc.ExecuteReader();
if (dr.Read())
{
SurveyCode = dr["svcode"].ToString();
Company = dr["company"].ToString();
Department = dr["department"].ToString();
Email = dr["email"].ToString();
Name = dr["name"].ToString();
People = dr["people"].ToString();
Tel = dr["tel"].ToString();
Title = dr["title"].ToString();
ID = dr["id"].ToString();
rtn = true;
}
dr.Close();
dr.Dispose();
sql = "SELECT * FROM prd_ansdetail WHERE svid='" + ID + "'";
dc.CommandText = sql;
SurveyData = dc.Execute().Tables[0];
dc.Connection.Close();
dc.Connection.Dispose();
return(rtn);
}
private void ExportSurvey()
{
YPSCS.Framework.Data.SQLiteData dc = new Framework.Data.SQLiteData();
string sql = "SELECT X.svname,Y.maxseq FROM prd_survey X INNER JOIN (SELECT svcode,MAX(seq) maxseq FROM prd_surveydetail GROUP BY svcode) Y ON Y.svcode=X.svcode WHERE X.svcode='0000'";
string svname = "";
double maxseq = 0;
dc.CommandText = sql;
dc.Connection.Open();
IDataReader drarg = dc.ExecuteReader();
if (drarg.Read())
{
svname = drarg["svname"].ToString();
maxseq = int.Parse(drarg["maxseq"].ToString());
}
drarg.Close();
drarg.Dispose();
if (!String.IsNullOrEmpty(svname))
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet[] sheet = new NPOI.SS.UserModel.ISheet[(int)Math.Ceiling(maxseq / 256)];
sql = "SELECT seq,title FROM prd_surveydetail WHERE svcode='0000' ORDER BY seq";
dc.CommandText = sql;
DataTable dt = dc.Execute().Tables[0];
int rowidx = 0;
int sheetidx = 0;
for (int x = 0; x < maxseq; x++)
{
int idx = x % 256;
if (idx == 0)
{
sheetidx = (int)Math.Floor((double)x / 256);
sheet[sheetidx] = workbook.CreateSheet(svname + "_" + sheetidx.ToString());
sheet[sheetidx].CreateRow(0).CreateCell(idx).SetCellValue(dt.Rows[rowidx]["title"].ToString());
rowidx++;
}
else if ((x + 1).ToString() == dt.Rows[rowidx]["seq"].ToString())
{
sheet[sheetidx].GetRow(0).CreateCell(idx).SetCellValue(dt.Rows[rowidx]["title"].ToString());
rowidx++;
}
}
sql = "SELECT id,svcode,company,department,name,tel,title,email,people,language,modifydate FROM prd_ans ORDER BY modifydate";
dc.CommandText = sql;
dt.Clear();
dt = dc.Execute().Tables[0];
for (int x = 0; x < dt.Rows.Count; x++)
{
int ansidx = x + 1;
sheetidx = 0;
sheet[sheetidx].CreateRow(ansidx).CreateCell(0).SetCellValue(ansidx.ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(1).SetCellValue(dt.Rows[x]["email"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(2).SetCellValue(dt.Rows[x]["language"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(3).SetCellValue(dt.Rows[x]["company"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(4).SetCellValue(dt.Rows[x]["department"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(5).SetCellValue(dt.Rows[x]["name"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(6).SetCellValue(dt.Rows[x]["tel"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(7).SetCellValue(dt.Rows[x]["title"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(8).SetCellValue(dt.Rows[x]["people"].ToString());
if (dt.Rows[x]["svcode"].ToString() == "0000")
{
sheet[sheetidx].GetRow(ansidx).CreateCell(24).SetCellValue("中文");
}
else
{
sheet[sheetidx].GetRow(ansidx).CreateCell(24).SetCellValue("英文");
}
sheet[sheetidx].GetRow(ansidx).CreateCell(25).SetCellValue(dt.Rows[x]["modifydate"].ToString());
sql = "SELECT seq,ans FROM prd_ansdetail WHERE svid='" + dt.Rows[x]["id"].ToString() + "' ORDER BY seq";
dc.CommandText = sql;
IDataReader dr = dc.ExecuteReader();
while (dr.Read())
{
int seq = int.Parse(dr["seq"].ToString()) - 1;
int idx = seq % 256;
sheetidx = (int)Math.Floor((double)seq / 256);
if (idx == 0)
{
sheet[sheetidx].CreateRow(ansidx).CreateCell(idx).SetCellValue(dr["ans"].ToString());
}
else
{
if (sheet[sheetidx].GetRow(ansidx) != null)
{
sheet[sheetidx].GetRow(ansidx).CreateCell(idx).SetCellValue(dr["ans"].ToString());
}
else
{
sheet[sheetidx].CreateRow(ansidx).CreateCell(idx).SetCellValue("");
}
}
}
dr.Close();
dr.Dispose();
}
string filename = AppDomain.CurrentDomain.BaseDirectory + "\\" + svname + ".xls";
var file = new System.IO.FileStream(filename, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
System.Net.WebClient wc = new System.Net.WebClient();
byte[] bfile = null;
try
{
string sitepath = Request.Url.AbsoluteUri;
bfile = wc.DownloadData(filename);
}
catch (Exception ex)
{
Response.Write("<script>alert('找不到檔案!!')</script>");
return;
}
HttpContext.Current.Response.Clear();
//跳出視窗，讓用戶端選擇要儲存的地方 //使用Server.UrlEncode()編碼中文字才不會下載時，檔名為亂碼
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(svname + ".xls"));
//設定MIME類型為二進位檔案
HttpContext.Current.Response.ContentType = "application/octet-stream";
try
{
HttpContext.Current.Response.BinaryWrite(bfile);
}
catch (Exception ex)
{
HttpContext.Current.Response.Write("檔案輸出有誤，您可以在瀏覽器的URL網址貼上以下路徑嘗試看看。<br/>檔案路徑：" + filename + "<br/>錯誤訊息：" + ex.ToString());
return;
}
HttpContext.Current.Response.End();
}
}