在这里,我用OWC组件实现了生成EXCEL表格,并下载到客户端
下面说一下实现的过程:
1、用VS 2005新建一个网站
2、添加OWC组件的引用。右键网站根目录,在弹出菜单中选择“添加引用”,打开添加引用对话框
3、在COM选项卡里选“Microsoft Office Web Components 11.0”,单击确定回到VS工程界面。
4、添加一个Web窗体,自己给他命名,如"Excelout",用来导出Excel表格。
5、在Excelout.aspx.cs文件中包含命名空间
using Microsoft.Office.Interop.Owc11;
6、添加一个Button,在其click事件里添加代码如下:
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 Microsoft.Office.Interop.Owc11;
using System.Data.SqlClient;
using System.IO;
public partial class ChartReport : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
//创建一个图表空间
ChartSpace mychartSpace = new ChartSpace();
//创建表容器
SpreadsheetClass myexl = new SpreadsheetClass();
//获取当前表
Worksheet mysheet = myexl.ActiveSheet;
//添加表标题
mysheet.Cells[1, 1] = "用户ID";
mysheet.Cells[1, 2] = "用户名";
mysheet.Cells[1, 3] = "学校名";
mysheet.Cells[1, 4] = "城市";
//连接数据库
string conn = ConfigurationManager.AppSettings["ConnectionString"].ToString();
using (SqlConnection myconn = new SqlConnection(conn))
{
DataSet objDataSet = new DataSet();
string sqlStr;
sqlStr = "select UsersID,userLog,schname,chengshi from users where leixing=1";
SqlDataAdapter adapter = new SqlDataAdapter(sqlStr,myconn);
adapter.Fill(objDataSet,"Sheet1");
DataTable dt = objDataSet.Tables[0];
//循环添加数据
for (int i = 0; i < 8; i++)
{
for (int j = 0; j < 4; j++)
{
mysheet.Cells[i + 2, j+1] =dt.Rows[i][j].ToString();
}
}
}
string fullname = Server.MapPath(".") + "//picture//test1.xls";
string filename = "test1.xls";
//导出表格
myexl.Export(fullname, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportXMLSpreadsheet);
//创建文件实体,方便对文件操作
FileInfo finfo = new FileInfo(fullname);
//清空输出流
Response.Clear();
Response.Charset="utf-8";
Response.Buffer=true;
//关闭ViewState以提高速度
this.EnableViewState=false;
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AppendHeader("Content-Disposition","attachment;filename="+filename);
//保存文件的类型不限
Response.ContentType = "application/unknown";
Response.WriteFile(fullname);
//清空并关闭输出流
Response.Flush();
Response.Close();
Response.End();
}
}
在这里我显示把Excel表导出到服务器端picture文件夹下,然后
再下载到客户端,要是有哪位同行有更好的方法
一定要告诉我啊。