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

Question

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

Answer 1

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>");
}

Answer 2

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();
    }
}

Answer 3

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