引用 the Microsoft Excel 9.0 Object Library
源代码如下:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
namespace ExeciseAsp.MakeExcel
{
/// <summary>
/// index
/// </summary>
public class index : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button btnOk;
protected System.Web.UI.WebControls.Label lblInfo;
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.TextBox txtCustNum;
private void Page_Load(object sender, System.EventArgs e)
{
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.btnOk.Click += new System.EventHandler(this.btnOk_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void btnOk_Click(object sender, System.EventArgs e)
{
string sConnString="Data Source=.;initial catalog=test;User ID=sa;Password=";
SqlConnection conn= new SqlConnection(sConnString);
try
{
// Process Excel
Excel.Application oExcel = new Excel.Application();
Excel.Workbooks oBooks;
Excel.Workbook oBook;
Excel.Sheets oSheets;
Excel.Worksheet oSheet;
Excel.Range oCells;
String sFile;
sFile = Server.MapPath(Request.ApplicationPath) + @"/MyExcel.xls" ;
oExcel.Visible = false;
oExcel.DisplayAlerts = false ;
// 'add a new workbook
oBooks = oExcel.Workbooks;
oBooks.Add(Type.Missing);
oBook=oExcel.ActiveWorkbook;
//oBooks.Open(Server.MapPath( Request.ApplicationPath) + @"/MyTemplate.xls",Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing) ;
//oBook = oBooks[1];
oSheets = oBook.Worksheets;
string sqlstr;
DataTable dt;
//NO 1 query
//retrieve the sql query result
sqlstr="select fldCustID,fldCustName from tblCustomer where fldCustID="+ QuoStr(txtCustNum.Text) ;
dt=GetData(sqlstr,conn);
//Add a sheet
oSheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing) ;
oSheet = (Excel.Worksheet)oSheets[1];
//name the sheet
oSheet.Name = "First Sheet";
oCells = oSheet.Cells;
//call the dumpdata function,import the data to Excel
DumpData(dt,oCells);
//NO 2 query
//retrieve the sql query result
sqlstr="select fldCustID,fldCustAge from tblCustomer where fldCustID="+ QuoStr(txtCustNum.Text) ;
dt=GetData(sqlstr,conn);
//Add a sheet
oSheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing) ;
oSheet = (Excel.Worksheet)oSheets[1];
//name the sheet
oSheet.Name = "Second Sheet";
oCells = oSheet.Cells;
//call the dumpdata function,import the data to Excel
DumpData(dt,oCells);
//NO 3 query
//retrieve the sql query result
sqlstr="select fldCustID,fldCustAddress from tblCustomer where fldCustID="+ QuoStr(txtCustNum.Text) ;
dt=GetData(sqlstr,conn);
//Add a sheet
oSheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing) ;
oSheet = (Excel.Worksheet)oSheets[1];
//name the sheet
oSheet.Name = "Third Sheet";
oCells = oSheet.Cells;
//call the dumpdata function,import the data to Excel
DumpData(dt,oCells);
//save the .xls file
oSheet.SaveAs(sFile ,Type.Missing,Type.Missing,Type.Missing,Type.Missing ,Type.Missing,Type.Missing,Type.Missing,Type.Missing );
oBook.Close(null,null,null);
//quit Excel,release the resource
oExcel.Quit();
Marshal.ReleaseComObject(oCells);
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oSheets);
Marshal.ReleaseComObject(oBook) ;
Marshal.ReleaseComObject(oBooks) ;
Marshal.ReleaseComObject(oExcel);
oExcel = null ;
oBooks = null ;
oBook = null;
oSheets = null ;
oSheet = null ;
oCells = null;
System.GC.Collect();
//show info
lblInfo.Text="the results are saved into the following file:" + sFile ;
}
catch (Exception ex)
{
lblInfo.Text=ex.Message ;
}
finally
{
if (conn.State==ConnectionState.Open)
conn.Close();
}
}
//retreave the query result
private DataTable GetData(string sqlstr, SqlConnection conn)
{
SqlCommand customer =new SqlCommand(sqlstr,conn);
SqlDataAdapter da = new SqlDataAdapter(customer);
DataTable dt=new DataTable() ;
da.Fill(dt);
return dt;
}
//import the data from datatable to Excel
private void DumpData(DataTable dt, Excel.Range oCells)
{
DataRow dr;
object[] ary;
int iRow;
int iCol;
//import the fields' name
for ( iCol = 0;iCol<= dt.Columns.Count - 1;iCol++)
{
oCells[2, iCol + 1] = dt.Columns[iCol].ToString();
}
//import the values
for ( iRow = 0; iRow<=dt.Rows.Count - 1; iRow++)
{
dr = dt.Rows[iRow];
ary = dr.ItemArray ;
for(iCol = 0;iCol<= ary.GetLength(0) - 1;iCol++)
{
oCells[iRow + 3, iCol + 1] = ary[iCol].ToString();
//Response.Write(ary[iCol].ToString() + (char)9);
}
}
}
//double the single quote in string
public static string QuoStr(string Str)
{
string VarTemp="";
if (Str==null)
VarTemp="Null";
else
{
VarTemp=Str;
int i = VarTemp.IndexOf("'");//return -1 if not found
while (i>=0)
{
VarTemp=VarTemp.Substring(0,i)+VarTemp.Substring(i);
i = VarTemp.IndexOf("'",i+2);
}
}
return "'"+VarTemp+"'";
}
}
}