//匯出 Excel (直接從資料來源 DataTable 產生 Excel。 此種 Excel 可加入自訂記錄列)
private void exportToExcel(System.Data.DataTable pDataTable)
{
string database = Session["DatabaseName"].ToString();
string SelectSQL = SqlDataSource4.SelectCommand;
int tRowCount = pDataTable.Rows.Count;
int tColumnCount = pDataTable.Columns.Count;
int colspan = tColumnCount - 1;
string filename = LbCompany.Text + "報價單";
string company = LbCompany.Text;
string date = TxLimitDate.Text;
string Bid = WebModel.BID(database, SelectSQL);
string SelectSQL1 = "select * from BidM0 where bid=" + Bid + "";
string BidName = WebModel.BidName(database, SelectSQL1);
Response.Expires = 0;
Response.Clear();
Response.Buffer = true;
Response.Charset = "utf-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".xls");
Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
//Response.Write("<Table borderColor=black border=1 font-family:'" + DDL_font.SelectedItem.Text + "'>");
HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:10.0pt; font-family:細明體; background:white;'>");
Response.Write("\n<th style='font-size:16.0pt;' colspan='8' align=\"center\" x:num>"); //抬頭背景為淺黃色，文字橫向為置中對齊
Response.Write("機關/公司名稱:" + company + " ");
Response.Write("\n</th>");
Response.Write("<TR ><td style='font-size:16.0pt;' colspan='8' align=\"center\" x:num>");
Response.Write("廠 商 報 價 單");
Response.Write("</td>\n</TR>");
Response.Write("<TR ><td style='font-size:16.0pt;' colspan='8' align=\"Left\" x:num>");
Response.Write("工程名稱:" + BidName + "");
Response.Write("</td>\n</TR>");
//Response.Write("<tr><td align='center' valign='middle'>自訂表頭訊息</td></tr>");
Response.Write("\n<TR align=\"center\" x:num>");
Response.Write("<td>項次</td><td style='width:400px'>作業項目</td><td>單位</td><td>數量</td><td style='width:100px'>單價</td><td style='width:100px'>複價</td><td>結算方式</td><td style='width:200px'>備註</td>\n </td>");
Response.Write("\n </TR>");
string l_str2 = "";
int i = 1;
foreach (DataRow l_dr in pDataTable.Rows) //滙出表格資料我
{ //若有數字或日期在滙出時要注意資料型態，我是在TABLE中就設定好了
//l_str2 = "<tr><td>" + l_dr["UID"].ToString() + "";
l_str2 = "<tr><td>" + i.ToString() + "";
l_str2 += "</td><td>" + l_dr["ItemName"].ToString() + "";
l_str2 += "</td><td>" + l_dr["Unit"].ToString() + "";
l_str2 += "</td><td>" + (Convert.ToInt32(l_dr["Amount"])).ToString("N0") + "";
l_str2 += "</td><td>" + (Convert.ToInt32(l_dr["DiscountPrice"])).ToString("N0") + "";
l_str2 += "</td><td>" + (Convert.ToInt32(l_dr["Complexprice"])).ToString("N0") + "";
l_str2 += "</td><td>" + (l_dr["ClearWay"].ToString()) + "";
l_str2 += "</td><td>" + l_dr["Notes"].ToString() + "";
l_str2 += "</td></tr>";
TotalPrice = l_dr["TotalPrice"].ToString() != ""?l_dr["TotalPrice"].ToString():"0";
Tax = l_dr["Tax"].ToString() != ""?l_dr["Tax"].ToString():"0";
TaxTotal = int.Parse(TotalPrice) + int.Parse(Tax);
Response.Write(l_str2);
i++;
}
Response.Write("<tr></tr><tr></tr>");
Response.Write("<TR ><td style='font-size:12.0pt;' colspan='3' align=\"center\" x:num></td><td>");
Response.Write("總價新台幣（未稅）：</td><td>" + Convert.ToInt32(TotalPrice).ToString("N0") + "</td><td>元 </td><td colspan='2'></td >");
Response.Write("</td>\n</TR>");
Response.Write("<TR ><td style='font-size:12.0pt;' colspan='3' align=\"center\" x:num></td><td>");
Response.Write("營業稅：</td><td>" + Convert.ToInt32(Tax).ToString("N0") + "</td><td>元 </td><td colspan='2'></td >");
Response.Write("</td>\n</TR>");
Response.Write("<TR ><td style='font-size:12.0pt;' colspan='3' align=\"center\" x:num></td><td>");
Response.Write("總價新台幣（含稅）：</td><td>" + TaxTotal.ToString("N0") + "</td><td>元 </td><td colspan='2'></td >");
Response.Write("</td>\n</TR>");
Response.Write("</Table>");
Response.End();
//Response.Write("<tr><td align='center' valign='middle'>自訂表尾訊息</td></tr>");
if (pDataTable != null)
{
pDataTable.Dispose(); //註記已可釋放此 DataTable
}
}
