printAddressList.aspx :
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PrintAddressList.aspx.cs" Inherits="test_PringAddressList" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
<script language="javascript" type="text/javascript">
function btnExport_onclick() {
hideIframe.location.href = "/Web/handler/ExportXlsHandler.ashx?temp=" + (new Date()).getTime();
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<input id="btnExport" type="button" value="导出通讯录" onclick="return btnExport_onclick()" /><br />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AutoGenerateColumns="False" BackColor="White" BorderColor="#3366CC"
BorderStyle="None" BorderWidth="1px" CellPadding="4" DataSourceID="odsUserInfo"
Width="572px">
<RowStyle BackColor="White" ForeColor="#003399" />
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" SortExpression="Id" />
<asp:BoundField DataField="DutyInfoNo" HeaderText="DutyInfoNo"
SortExpression="DutyInfoNo" />
<asp:BoundField DataField="UserName" HeaderText="UserName"
SortExpression="UserName" />
<asp:BoundField DataField="Mobile" HeaderText="Mobile"
SortExpression="Mobile" />
<asp:BoundField DataField="QQ" HeaderText="QQ" SortExpression="QQ" />
<asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
<asp:BoundField DataField="DeptInfoNo" HeaderText="DeptInfoNo"
SortExpression="DeptInfoNo" />
</Columns>
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
</asp:GridView>
<asp:ObjectDataSource ID="odsUserInfo" runat="server"
SelectMethod="GetUserInfo" TypeName="ExtOA.Biz.UserInfoBiz">
</asp:ObjectDataSource>
</div>
<iframe name="hideIframe" id="hideIframe" src="" width="0" height="0" scrolling="no" frameborder="0" style="display:none"></iframe>
</form>
</body>
</html>
ExportXlsHandler.ashx :
<%@ WebHandler Language="C#" Class="ExportXlsHandler" %>
using System;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Web;
using System.Web.SessionState; // 要使用 Session 必需加入此命名空间
using System.Web.Configuration;
using System.Collections.Generic;
using ExtOA.Biz;
using ExtOA.Ent;
public class ExportXlsHandler : IHttpHandler, IRequiresSessionState
{
string mErr = "", tp_title = "国讯教育员工通讯录", tfile = "", sfile = "", wpath = "";
string BranchName1 = (new BranchInfoBiz()).GetBranchInfoById(1).BranchName;
string BranchName2 = (new BranchInfoBiz()).GetBranchInfoById(2).BranchName;
/// <summary>
/// 保存数据(添加或修改数据)
/// </summary>
/// <param name="oledb_conn">conn</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="type">insert|update</param>
private void SaveData(OleDbConnection oledb_conn, string sheetName, string type)
{
using (OleDbCommand oledb_command = new OleDbCommand())
{
string SqlString = "";
oledb_command.Connection = oledb_conn;
#region 更新‘通讯录名称’
SqlString = "Update [" + sheetName + "$] Set remark = @remark Where remark = '名称';";
oledb_command.CommandText = SqlString;
oledb_command.Parameters.Clear();
oledb_command.Parameters.AddWithValue("remark", string.Format("国讯教育通讯录({0})", sheetName));
oledb_command.ExecuteNonQuery();
#endregion
#region 更新‘制表日期’
SqlString = "Update [" + sheetName + "$] Set remark = @remark Where remark = '制表日期';";
oledb_command.CommandText = SqlString;
oledb_command.Parameters.Clear();
oledb_command.Parameters.AddWithValue("remark", "制表日期：" + DateTime.Now.ToString("yyyy/MM/dd"));
oledb_command.ExecuteNonQuery();
#endregion
IList<UserInfo> userInfos = (new UserInfoBiz()).GetUserInfoByDeptInfoId(1);//根据机构ID得到该机构所有员工列表
int index = 0;
foreach (UserInfo userInfo in userInfos)
{
index++;
if (type == "insert")
SqlString = "insert into [" + sheetName + "$](remark,depart,username,dutyname,mobile,qq,email) values(@remark, @depart, @username, @dutyname, @mobile, @qq,@email)";
else
SqlString = "Update [" + BranchName2 + "$] set remark = @remark,depart=@depart,username=@username,dutyname=@dutyname,mobile=@mobile,qq=@qq,email=@email where remark='" + index + "'";
oledb_command.CommandText = SqlString;
oledb_command.Parameters.Clear();
oledb_command.Parameters.AddWithValue("remark", " " + index.ToString() + " ");
string DeptName = (new DepartInfoBiz()).GetDepartInfoById(userInfo.DeptInfoNo).DepartName;
oledb_command.Parameters.AddWithValue("depart", DeptName);
oledb_command.Parameters.AddWithValue("username", userInfo.UserName);
string dutyName = (new DutyInfoBiz()).GetDutyInfoById(userInfo.DutyInfoNo).DutyName.Split('-')[1];
oledb_command.Parameters.AddWithValue("dutyname", dutyName);
oledb_command.Parameters.AddWithValue("mobile", userInfo.Mobile);
oledb_command.Parameters.AddWithValue("qq", userInfo.QQ);
oledb_command.Parameters.AddWithValue("email", userInfo.Email);
oledb_command.ExecuteNonQuery();
}
}
}
public void ProcessRequest(HttpContext context)
{
#region 复制 Excel 文件，以解决共用问题
wpath = context.Server.MapPath("~/xls/Default/");
sfile = wpath + "addresstemplate.xls";
wpath = context.Server.MapPath("~/xls/Excel/");
tfile = wpath + string.Format("国讯教育员工通讯录({0}{1}).xls", DateTime.Now.Year.ToString("D2"), DateTime.Now.Month.ToString("D2"));
File.Delete(tfile);
if (File.Exists(tfile))
mErr = "文件无法创建!\n";
else
File.Copy(sfile, tfile, true);
#endregion
#region 打开新的 Excel 文件
if (mErr == "")
{
// Excel的第一列为字段名称
string xls_conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ tfile + ";Extended Properties="Excel 8.0;HDR=YES"";
// Excel的第一列没有字段名称
// string xls_conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + tfile + ";Extended Properties="Excel 8.0;HDR=NO"";
using (OleDbConnection oledb_conn = new OleDbConnection(xls_conn))
{
oledb_conn.Open();
SaveData(oledb_conn,BranchName1,"insert");//对上海校区进行操作
SaveData(oledb_conn,BranchName2,"update"); //对北京校区进行操作
oledb_conn.Close();
string filename = string.Format("../xls/Excel/国讯教育员工通讯录({0}{1}).xls", DateTime.Now.Year.ToString("D2"), DateTime.Now.Month.ToString("D2"));
context.Response.Redirect(filename + "?xtemp=" + DateTime.Now.ToString("HHmmss"));
}
#endregion
if (mErr != "")
{
// 设置输出格式
context.Response.ContentType = "text/html";
context.Response.Write("<script type="text/javascript">alert("" + mErr + "");/script>");
context.Response.End();
}
}
}
public bool IsReusable {
get {
return false;
}
}
}