close

將資料輸出成Excel檔案有很多方法

如使用NPOI......等

而HTML也可以輸出成Excel檔案

sample code :

protected void btnDownLoadExcel_Click(object sender, EventArgs e)
{
DataTable dt = DB.SelectData(sql.ToString());
String writeToExcel = SetDataTableToShowExcel(dt);
//輸出excel檔案
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=ResultExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
string style = @"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(writeToExcel);
Response.Flush();
Response.End();
}
/// <summary>
/// 輸出結果供寫入Excel用
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public String SetDataTableToShowExcel(DataTable dt)
{
StringBuilder sb = new StringBuilder();
int total = 0;
String userID = String.Empty;
sb.Append("<table border='1'>");
sb.Append("<tr>");
sb.Append("<th style='background-color:#D20B0C;color:#ffffff'>職工編號</th>");
sb.Append("<th style='background-color:#D20B0C;color:#ffffff'>姓名</th>");
sb.Append("<th style='background-color:#D20B0C;color:#ffffff'>電話</th>");
sb.Append("<th style='background-color:#D20B0C;color:#ffffff'>部門</th>");
sb.Append("<th style='background-color:#D20B0C;color:#ffffff'>員購標題</th>");
sb.Append("<th style='background-color:#D20B0C;color:#ffffff'>商品名</th>");
sb.Append("<th style='background-color:#D20B0C;color:#ffffff'>數量</th>");
sb.Append("<th style='background-color:#D20B0C;color:#ffffff'>單價</th>");
sb.Append("<th style='background-color:#D20B0C;color:#ffffff'>小計</th>");
sb.Append("<th style='background-color:#D20B0C;color:#ffffff'>寄送方式</th>");
sb.Append("<th style='background-color:#D20B0C;color:#ffffff'>備註</th>");
sb.Append("</tr>");
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i == 0)
{
userID = dt.Rows[i]["EmployeeID"].ToString();
}
else
{
if (!userID.Equals(dt.Rows[i]["EmployeeID"].ToString()))
{
sb.AppendFormat("<tr><td colspan='11'>總共 : {0}元</tr>", String.Format("{0:n0}", Convert.ToInt64(total.ToString())));
total = 0;
userID = dt.Rows[i]["EmployeeID"].ToString();
}
}
sb.Append("<tr>");
sb.AppendFormat("<td>{0}</td>", dt.Rows[i]["EmployeeID"].ToString());
sb.AppendFormat("<td>{0}</td>", dt.Rows[i]["Name"].ToString());
sb.AppendFormat("<td>{0}</td>", dt.Rows[i]["Phone"].ToString());
sb.AppendFormat("<td>{0}</td>", dt.Rows[i]["Department"].ToString());
sb.AppendFormat("<td>{0}</td>", dt.Rows[i]["Subject"].ToString());
sb.AppendFormat("<td>{0}</td>", GetProductName(dt.Rows[i]["EmployeeShoppingID"].ToString(), dt.Rows[i]["EmployeeShoppingGroupID"].ToString(), dt.Rows[i]["OptionID"].ToString()));
sb.AppendFormat("<td>{0}</td>", dt.Rows[i]["BuyCount"].ToString());
sb.AppendFormat("<td>{0}</td>", String.Format("{0:n0}",Convert.ToInt64(GetProductPrice(dt.Rows[i]["EmployeeShoppingID"].ToString(), dt.Rows[i]["EmployeeShoppingGroupID"].ToString(), dt.Rows[i]["OptionID"].ToString()))));
sb.AppendFormat("<td>{0}</td>", String.Format("{0:n0}",Convert.ToInt64(dt.Rows[i]["TotalMoney"])));
total += int.Parse(dt.Rows[i]["TotalMoney"].ToString()); //計算總金額
sb.AppendFormat("<td>{0}</td>", dt.Rows[i]["DeliveryMethod"].ToString());
sb.AppendFormat("<td>{0}</td>", dt.Rows[i]["Memo"].ToString());
sb.Append("</tr>");
}
//最後一筆員購資料的金額
sb.AppendFormat("<tr><td colspan='11'>總共 : {0}元</tr>", String.Format("{0:n0}", Convert.ToInt64(total.ToString())));
sb.Append("</table>");
return sb.ToString();
}
view raw gistfile1.txt hosted with ❤ by GitHub

結果畫面

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 Kenny Lin 的頭像
    Kenny Lin

    Kenny的程式筆記

    Kenny Lin 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄