源地址：http://jackyi.javaeye.com/blog/787442
c#調(diào)用excel模板頁，然后套用模板頁導(dǎo)出多個(gè)sheet
文章分類:.net編程
//明細(xì)導(dǎo)出
protected void btn_DetailExport_Click(object sender, EventArgs e)
{
int count1 = this.ASPxGridView1.VisibleRowCount;
//Response.Redirect("../Quote/QuoteOutput.aspx");
//先判斷網(wǎng)格是否為空，或者當(dāng)數(shù)據(jù)超過255條是提示數(shù)據(jù)過多。
if (count1 == 0)
{
Response.Write("<script>'alert('網(wǎng)格沒有數(shù)據(jù),不能導(dǎo)出')</script>");
}
if (count1 > 255)
{
Response.Write("<script>'alert('數(shù)據(jù)量超過255條,不能導(dǎo)出')</script>");
}
string filepath = null;
GC.Collect();
//獲得模板頁的路徑
string strFileName = Server.MapPath(Request.ApplicationPath) + @"ShoesPic\template.xls";
Application excel = new Application();//用于導(dǎo)出
Workbook xBk;
excel.Visible = true;
excel.UserControl = true;
object missing = System.Reflection.Missing.Value;
//加載Excel模板文件
xBk = excel.Workbooks.Open(strFileName, missing, true, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing);
Worksheet xSt;
// 復(fù)制count1-1個(gè)WorkSheet對(duì)象
for (int i = 1; i < count1; i++)
{
((Worksheet)xBk.Worksheets.get_Item(i)).Copy(missing, xBk.Worksheets[i]);
}
if (excel == null)
{
Response.Write("<script>alert('Can't access excel')</script>");
}
else
{
try
{
//xSt.Name = "報(bào)價(jià)單導(dǎo)出"; //這里修改sheet名稱
//獲得ASPxGridView的所有數(shù)據(jù)
for (int j = 0; j < count1; j++)
{
xSt = (Worksheet)xBk.Worksheets.get_Item(j+1);
//xSt = (Worksheet)xBk.Sheets[j + 1];//第一個(gè)sheet頁
object data = this.ASPxGridView1.GetRowValues(j, "報(bào)價(jià)單編號(hào)");//獲得主鍵數(shù)據(jù)
xSt.Name = data.ToString();
Basic.QuoteManager manager = new Basic.QuoteManager();
Model.Quote quote = manager.getQuotevalueByNo(data.ToString());
string providername = quote.ProviderName;
string brand = quote.Brand;
string colthnob = quote.Colthnob;
string q_user = quote.Q_User;
string phone = quote.Phone;
string mobile = quote.Mobile;
string c_user = quote.C_User;
string principal = quote.Principal;
string leader = quote.Leader;
string picurl = quote.PicUrl;
string user = quote.User;
string remark = quote.Remark;
string q_date = quote.Q_Date.ToString();
string contract_price = quote.Contract_Price.ToString();
//報(bào)價(jià)單位
xSt.get_Range("B2", missing).Value2 = providername;
//品牌
xSt.get_Range("B3", missing).Value2 = brand;
//款號(hào)/工廠貨號(hào)
xSt.get_Range("B4", missing).Value2 = colthnob;
//報(bào)價(jià)人
xSt.get_Range("B5", missing).Value2 = q_user;
//聯(lián)系電話
xSt.get_Range("B6", missing).Value2 = phone;
//移動(dòng)電話
xSt.get_Range("B7", missing).Value2 = mobile;
//日期/報(bào)價(jià)日期
xSt.get_Range("D2", missing).Value2 = q_date;
//核價(jià)人員
xSt.get_Range("D3", missing).Value2 = c_user;
//品牌負(fù)責(zé)人
xSt.get_Range("D4", missing).Value2 = principal;
//分管領(lǐng)導(dǎo)
xSt.get_Range("D5", missing).Value2 = leader;
//圖片/圖片地址
xSt.get_Range("D6", missing).Value2 = picurl;
//制單人
xSt.get_Range("D7", missing).Value2 = user;
//合同價(jià)
xSt.get_Range("F2", missing).Value2 = contract_price;
//備注
xSt.get_Range("F3", missing).Value2 = remark;
////明細(xì)表數(shù)據(jù)
Basic.QuoteManager manager1 = new Basic.QuoteManager();
Model.QuoteDetail[] quotedetail = manager1.getQuoteDetailvalueByNo(data.ToString());
int count = quotedetail.Length;
int rowsint = 10;
foreach (Model.QuoteDetail i in quotedetail)
{
//item 項(xiàng)目
xSt.Cells.get_Range("A" + rowsint, missing).Value2 = i.Item;
//itemname 名稱
xSt.Cells.get_Range("B" + rowsint, missing).Value2 = i.Itemname;
//unit 單位
xSt.Cells.get_Range("C" + rowsint, missing).Value2 = i.Unit;
//q_dosage 單用量
xSt.Cells.get_Range("D" + rowsint, missing).Value2 = i.Q_Dosage;
// q_unitprice 單價(jià)
xSt.Cells.get_Range("E" + rowsint, missing).Value2 = i.Q_UnitPrice;
//q_price 金額
xSt.Cells.get_Range("F" + rowsint, missing).Value2 = i.Q_Price;
rowsint++;
}
//先清除模板頁的數(shù)據(jù)，然后新增一個(gè)sheet
//對(duì)一個(gè)sheet賦值后，需要新增一個(gè)sheet，然后再賦值操作
//xSt = (Worksheet)excel.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
//保存的時(shí)候會(huì)先保存在服務(wù)器上，然后再從服務(wù)器下載下來。
filepath = Server.MapPath(Request.ApplicationPath) + @"ShoesPic\報(bào)價(jià)單導(dǎo)出.xls";
xBk.Saved = true;
xBk.SaveCopyAs(filepath);
//退出excel
excel.Quit();
excel = null;
xBk = null;
xSt = null;
Process[] procs = Process.GetProcessesByName("excel");
foreach (Process pro in procs)
{
pro.Kill();//沒有更好的方法,只有殺掉進(jìn)程
}
GC.Collect();
System.IO.FileInfo file = new System.IO.FileInfo(filepath);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加頭信息，為"文件下載/另存為"對(duì)話框指定默認(rèn)文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加頭信息，指定文件大小，讓瀏覽器能夠顯示下載進(jìn)度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一個(gè)不能被客戶端讀取的流，必須被下載
Response.ContentType = "application/ms-excel";
// 把文件流發(fā)送到客戶端
Response.WriteFile(file.FullName);
Response.Flush();//這個(gè)語句必須有，否則就不回彈出保存的對(duì)話框，搞了N久
// 停止頁面的執(zhí)行
Response.End();
}
catch (Exception ex)
{
}
finally
{
if (File.Exists(filepath))
{
File.Delete(filepath);
}
}
}
}
posted on 2011-03-21 13:57
luis 閱讀(1919)
評(píng)論(1) 編輯 收藏 引用