asp.net中，数据库取出一条数据，导出excel文件。（excel文件为规定模板）

[xiaocongjun37]

各位仁兄，晚上好，
小弟遇到一个难题，asp.net中，数据库取出一条数据，导出excel文件。而excel文件为规定模板。请各位指教

[wuyq11]

public void INSERT_NLAExcel(DataView dvs,string strPath,string Name)
{
string s="";
Excel.Application app=new Application();
Excel._Workbook book;
Excel._Worksheet sheet;
book=(Excel._Workbook)app.Workbooks.Open(strPath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
sheet=(Excel._Worksheet)book.Sheets[1];
int j=dvs.Count;
Excel.Range ran1=app.ActiveCell;
ran1=sheet.get_Range(sheet.Cells[1,1],sheet.Cells[1,9]);
ran1.Value2=Name;
for(int i=0;i<dvs.Count;i++)
{
try
{
s=Convert.ToString(i);
sheet.Cells[i+4,"A"]="";
sheet.Cells[i+4,"B"]="";
}
catch(Exception ex)
{
HttpContext.Current.Response.Write("<script language='javascript'>alert('"+ex.Message+"')</script>");
book.Close(null,null,null);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet=null;
book=null;
app=null;
GC.Collect();
HttpContext.Current.Response.Write("<script language='javascript'>alert('导出失败！')</script>");
return;
}

}
book.Save();
book.Close(null,null,null);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet=null;
book=null;
app=null;
GC.Collect();
GC.Collect();
GC.Collect();
HttpContext.Current.Response.Write("<script language='javascript'>alert('导出成功！')</script>");
HttpContext.Current.Response.Write("<script language='javascript'>window.open('../Template_temp/A.xls','_bank')</script>");
}

[ZJ159]

顶

[wjxluck]

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Text;

public partial class SingleLevelCurrentBOM : System.Web.UI.Page
{
public String proc = "Rpt_SingleLevelCurrentBillOfMaterialSp";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ItemBind();
}
}
protected void ItemBind()
{
SqlConnection MyConn = new SqlConnection(PublicVar.connectionString);
MyConn.Open();

SqlCommand sc = new SqlCommand("select item from item", MyConn);
SqlDataReader sr = sc.ExecuteReader();
int k = 1;
//int n = 1;
dropItemfrom.Items.Insert(0, new ListItem("", "-1"));
DropItemto.Items.Insert(0, new ListItem("", "-1"));
while (sr.Read())
{

dropItemfrom.Items.Insert(k, new ListItem(sr["item"].ToString(), k.ToString()));
DropItemto.Items.Insert(k, new ListItem(sr["item"].ToString(), k.ToString()));
k++;
}
sr.Close();
MyConn.Close();

}
protected void GVBind()
{

SqlParameter[] parms = new SqlParameter[2];
parms[0] = new SqlParameter("@ItemStarting", SqlDbType.VarChar, 50);
parms[1] = new SqlParameter("@ItemEnding", SqlDbType.VarChar, 50);

parms[0].Value = dropItemfrom.SelectedItem.ToString().Trim();
parms[1].Value = DropItemto.SelectedItem.ToString().Trim();

GV.DataSource = DataAccess.DABaseAccess.GetTableByStore(proc, parms);
GV.DataBind();
this.GV.PageSize = 30;

}
protected void ButOK_Click(object sender, EventArgs e)
{
GVBind();
}

public override void VerifyRenderingInServerForm(Control control)
{

}
private void Export(string FileType, string FileName)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
GV.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}


protected void ButExcel_Click(object sender, EventArgs e)
{
Export("application/ms-excel", "物料计划明细表.xls");
}
protected void GV_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
this.GV.PageIndex = e.NewPageIndex;
this.GV.AllowPaging = true;
this.GV.PageSize = 30;
GVBind();
}
}

[trueideal]

这个有几种方法,一上用excel做模板,调用excel appliation这个方法,导出的excel是标准的excel,就是服务器要有excel
二将excel转成mht做模板,用io操作来生成excel,缺点是这个excel样式可能有一些变化(与原.)速度快.
三将excel转成xml做模板,用io操作来生成,优点,样式不会变化,但操作易出错,速度快.