本方法使用模板页的方式导出数据集。
优点:自定义样式、在排版风格上灵活
缺点:需要定义模板
思路:在模板上打上标识,用数据集替换标识。生成新的文件。
主要程序代码:
1.引用文件操作相关的程序集
using System.IO;
using System.Text;
2.获取数据集,替换模板,生成输出文件
#region 导出客户资料
protected void btnExport_Click(object sender, EventArgs e)
{
//取得数据
Aiwei.DAL.Supplier dalSupplier = new Aiwei.DAL.Supplier();
DataSet dsSupplier = new DataSet();
dsSupplier = dalSupplier.GetExportList("");
DataTable dtExportItemLy = dsSupplier.Tables[0];
if (dtExportItemLy == null) return;
if (dtExportItemLy.Rows.Count <= 0)
{
AlertShow(this.Page, "暂无数据可以导出!");
return;
}
string m_Target = "";
string m_Source = "";
string m_Temp = "";
string m_content = "";
int m_start = 0, m_end = 0;
string[] format = new string[13];//定义和htmlTemplate 标记数目一致的数组
try
{
using (StreamReader sr = new StreamReader(Server.MapPath("ExcepTemplate/UserTemplete.htm")))
{
string line;
while ((line = sr.ReadLine()) != null)
{
m_Source += line;
}
sr.Close();
}
}
catch (System.Exception ex)
{
string strErr = ex.Message;
AlertShow(this.Page, "读取文件错误!");
return;
}
m_start = m_Source.IndexOf("<!--content start-->");
m_end = m_Source.IndexOf("<!--content end-->");
m_content = m_Source.Substring(m_start, m_end - m_start);
m_Source = m_Source.Replace(m_content, "<!--tempitem-->");
m_content = m_content.Replace("<!--content start-->", "");
m_content = m_content.Replace("<!--content end-->", "");
//---------------------给标记字段赋值------------
for (int i = 0; i < dtExportItemLy.Rows.Count; i++)
{
format[0] = dtExportItemLy.Rows[i]["SupplierID"].ToString();//客户编号
format[1] = dtExportItemLy.Rows[i]["SupplierName"].ToString(); //客户名称
format[2] = dtExportItemLy.Rows[i]["Address"].ToString(); //地址
format[3] = dtExportItemLy.Rows[i]["PostCode"].ToString();//邮政编码
format[4] = dtExportItemLy.Rows[i]["HomePage"].ToString();//主页
format[5] = dtExportItemLy.Rows[i]["ArtiPerson"].ToString();//负责人
format[6] = dtExportItemLy.Rows[i]["ContactMan"].ToString();//联系人
format[7] = dtExportItemLy.Rows[i]["ContactMail"].ToString();//电子邮箱
format[8] = dtExportItemLy.Rows[i]["Remark"].ToString(); //备注
format[9] = dtExportItemLy.Rows[i]["CreateTime"].ToString();//创建时间
format[10] = dtExportItemLy.Rows[i]["LastModifyTime"].ToString();//最后修改时间
format[11] = dtExportItemLy.Rows[i]["ContractWay"].ToString();//联系电话
//----------替换htm里的标记为你想加的内容
m_Temp = m_content;
for (int j = 0; j < 12; j++)
{
m_Temp = m_Temp.Replace("$htmlformat[" + j + "]", format[j]);
}
m_Target += m_Temp;
}
m_Target = m_Source.Replace("<!--tempitem-->", m_Target);
m_Target = m_Target.Replace("$htmlformat[12]", "客户总数: " + dtExportItemLy.Rows.Count + "位");
//----------生成htm文件------------------
try
{
using (StreamWriter sw = new StreamWriter(Server.MapPath("ExcepTemplate/UserInfo.htm"), false, System.Text.Encoding.GetEncoding("GB2312")))
{
sw.WriteLine(m_Target);
sw.Flush();
sw.Close();
}
System.IO.FileStream fs = new FileStream(Server.MapPath("ExcepTemplate/UserInfo.htm"), FileMode.Open, FileAccess.Read);
byte[] buf = new byte[Convert.ToInt32(fs.Length)];
fs.Read(buf, 0, Convert.ToInt32(fs.Length));
fs.Close();
File.Delete(Server.MapPath("ExcepTemplate/UserInfo.htm"));
Response.Clear();
Response.ClearHeaders();
Response.Buffer = true;
string strFormat = "excel";
switch (strFormat)
{
case "html":
Response.AddHeader("Content-Disposition", "attachment; filename= File.htm");
//Response.ContentType = "application/ms-html";
Response.ContentType = "application/octet-stream";
break;
case "word":
Response.AddHeader("Content-Disposition", "attachment; filename= File.doc");
//Response.ContentType = "application/msword";
Response.ContentType = "application/octet-stream";
break;
case "excel":
dtExportItemLy.Columns[0].ColumnName = "用户编号";
dtExportItemLy.Columns[1].ColumnName = "用户名称";
dtExportItemLy.Columns[2].ColumnName = "地址";
dtExportItemLy.Columns[3].ColumnName = "邮政编码";
dtExportItemLy.Columns[4].ColumnName = "主页";
dtExportItemLy.Columns[5].ColumnName = "负责人";
dtExportItemLy.Columns[6].ColumnName = "联系人";
dtExportItemLy.Columns[7].ColumnName = "电子邮箱";
dtExportItemLy.Columns[8].ColumnName = "联系电话";
dtExportItemLy.Columns[9].ColumnName = "添加时间";
dtExportItemLy.Columns[10].ColumnName = "最后修改时间";
dtExportItemLy.Columns[11].ColumnName = "备注";
this.CreateExcel(dtExportItemLy, "UserInfo");
break;
default:
AlertShow(this.Page,"目前未支持该类型!");
break;
}
Response.Buffer = true;
Response.Clear();
Response.AddHeader("Content-Length", buf.Length.ToString());
Response.BinaryWrite(buf);
Response.Flush();
AlertShow(this.Page,"导出成功!");
}
catch(Exception ex)
{
AlertShow(this.Page, ""+ex.Message+"导出失败!");
return;
}
finally
{
Response.End();
}
}
public void CreateExcel(DataTable dt, string FileName)
{
System.Web.UI.WebControls.DataGrid m_Datagrid = new DataGrid();
m_Datagrid.DataSource = dt;
m_Datagrid.DataBind();
Response.Buffer = false;
Response.Charset = "gb2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
//Response.ContentType = "application/ms-excel";
Response.ContentType = "application/octet-stream";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
m_Datagrid.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
AlertShow(this.Page, "导出成功!");
Response.End();
}
#endregion
protected void btnExport_Click(object sender, EventArgs e)
{
//取得数据
Aiwei.DAL.Supplier dalSupplier = new Aiwei.DAL.Supplier();
DataSet dsSupplier = new DataSet();
dsSupplier = dalSupplier.GetExportList("");
DataTable dtExportItemLy = dsSupplier.Tables[0];
if (dtExportItemLy == null) return;
if (dtExportItemLy.Rows.Count <= 0)
{
AlertShow(this.Page, "暂无数据可以导出!");
return;
}
string m_Target = "";
string m_Source = "";
string m_Temp = "";
string m_content = "";
int m_start = 0, m_end = 0;
string[] format = new string[13];//定义和htmlTemplate 标记数目一致的数组
try
{
using (StreamReader sr = new StreamReader(Server.MapPath("ExcepTemplate/UserTemplete.htm")))
{
string line;
while ((line = sr.ReadLine()) != null)
{
m_Source += line;
}
sr.Close();
}
}
catch (System.Exception ex)
{
string strErr = ex.Message;
AlertShow(this.Page, "读取文件错误!");
return;
}
m_start = m_Source.IndexOf("<!--content start-->");
m_end = m_Source.IndexOf("<!--content end-->");
m_content = m_Source.Substring(m_start, m_end - m_start);
m_Source = m_Source.Replace(m_content, "<!--tempitem-->");
m_content = m_content.Replace("<!--content start-->", "");
m_content = m_content.Replace("<!--content end-->", "");
//---------------------给标记字段赋值------------
for (int i = 0; i < dtExportItemLy.Rows.Count; i++)
{
format[0] = dtExportItemLy.Rows[i]["SupplierID"].ToString();//客户编号
format[1] = dtExportItemLy.Rows[i]["SupplierName"].ToString(); //客户名称
format[2] = dtExportItemLy.Rows[i]["Address"].ToString(); //地址
format[3] = dtExportItemLy.Rows[i]["PostCode"].ToString();//邮政编码
format[4] = dtExportItemLy.Rows[i]["HomePage"].ToString();//主页
format[5] = dtExportItemLy.Rows[i]["ArtiPerson"].ToString();//负责人
format[6] = dtExportItemLy.Rows[i]["ContactMan"].ToString();//联系人
format[7] = dtExportItemLy.Rows[i]["ContactMail"].ToString();//电子邮箱
format[8] = dtExportItemLy.Rows[i]["Remark"].ToString(); //备注
format[9] = dtExportItemLy.Rows[i]["CreateTime"].ToString();//创建时间
format[10] = dtExportItemLy.Rows[i]["LastModifyTime"].ToString();//最后修改时间
format[11] = dtExportItemLy.Rows[i]["ContractWay"].ToString();//联系电话
//----------替换htm里的标记为你想加的内容
m_Temp = m_content;
for (int j = 0; j < 12; j++)
{
m_Temp = m_Temp.Replace("$htmlformat[" + j + "]", format[j]);
}
m_Target += m_Temp;
}
m_Target = m_Source.Replace("<!--tempitem-->", m_Target);
m_Target = m_Target.Replace("$htmlformat[12]", "客户总数: " + dtExportItemLy.Rows.Count + "位");
//----------生成htm文件------------------
try
{
using (StreamWriter sw = new StreamWriter(Server.MapPath("ExcepTemplate/UserInfo.htm"), false, System.Text.Encoding.GetEncoding("GB2312")))
{
sw.WriteLine(m_Target);
sw.Flush();
sw.Close();
}
System.IO.FileStream fs = new FileStream(Server.MapPath("ExcepTemplate/UserInfo.htm"), FileMode.Open, FileAccess.Read);
byte[] buf = new byte[Convert.ToInt32(fs.Length)];
fs.Read(buf, 0, Convert.ToInt32(fs.Length));
fs.Close();
File.Delete(Server.MapPath("ExcepTemplate/UserInfo.htm"));
Response.Clear();
Response.ClearHeaders();
Response.Buffer = true;
string strFormat = "excel";
switch (strFormat)
{
case "html":
Response.AddHeader("Content-Disposition", "attachment; filename= File.htm");
//Response.ContentType = "application/ms-html";
Response.ContentType = "application/octet-stream";
break;
case "word":
Response.AddHeader("Content-Disposition", "attachment; filename= File.doc");
//Response.ContentType = "application/msword";
Response.ContentType = "application/octet-stream";
break;
case "excel":
dtExportItemLy.Columns[0].ColumnName = "用户编号";
dtExportItemLy.Columns[1].ColumnName = "用户名称";
dtExportItemLy.Columns[2].ColumnName = "地址";
dtExportItemLy.Columns[3].ColumnName = "邮政编码";
dtExportItemLy.Columns[4].ColumnName = "主页";
dtExportItemLy.Columns[5].ColumnName = "负责人";
dtExportItemLy.Columns[6].ColumnName = "联系人";
dtExportItemLy.Columns[7].ColumnName = "电子邮箱";
dtExportItemLy.Columns[8].ColumnName = "联系电话";
dtExportItemLy.Columns[9].ColumnName = "添加时间";
dtExportItemLy.Columns[10].ColumnName = "最后修改时间";
dtExportItemLy.Columns[11].ColumnName = "备注";
this.CreateExcel(dtExportItemLy, "UserInfo");
break;
default:
AlertShow(this.Page,"目前未支持该类型!");
break;
}
Response.Buffer = true;
Response.Clear();
Response.AddHeader("Content-Length", buf.Length.ToString());
Response.BinaryWrite(buf);
Response.Flush();
AlertShow(this.Page,"导出成功!");
}
catch(Exception ex)
{
AlertShow(this.Page, ""+ex.Message+"导出失败!");
return;
}
finally
{
Response.End();
}
}
public void CreateExcel(DataTable dt, string FileName)
{
System.Web.UI.WebControls.DataGrid m_Datagrid = new DataGrid();
m_Datagrid.DataSource = dt;
m_Datagrid.DataBind();
Response.Buffer = false;
Response.Charset = "gb2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
//Response.ContentType = "application/ms-excel";
Response.ContentType = "application/octet-stream";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
m_Datagrid.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
AlertShow(this.Page, "导出成功!");
Response.End();
}
#endregion
3.模板文件的定制UserTemplete.htm
<!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>
<title>用户资料信息</title>
<meta content="text/html; charset=gb2312" http-equiv="Content-Type">
<style type="text/css"> <!-- body { margin-left: 0px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; background-color: #FDFDEE; }
td{ font-size:12px; line-height:normal; color:#5e5e5e; text-decoration: none; background-color: #FFFFFB; }
.border_1 { border: 1px solid #9FAB7A; }
.zi1 { font-size: 15px; text-decoration: none; color: #798554; font-weight: bold; }
--></style>
</head>
<body bgcolor="#ffffff">
<table>
<tr>
<td height="2">
</td>
</tr>
</table>
<table align="center" border="0" cellpadding="0" cellspacing="0" width="95%">
<tr>
<th scope="col">
<span class="zi1">用户资料信息</span></th>
</tr>
</table>
<table>
<tr>
<td height="2">
</td>
</tr>
</table>
<table align="center" border="0" cellpadding="0" cellspacing="0" width="95%">
<tr>
<th align="right" scope="col">
<font size="2"><strong>$htmlformat[12]</strong></font></th>
</tr>
</table>
<table id="Table2" align="center" bgcolor="#9fab7a" border="0" cellpadding="0" cellspacing="1"
class="tabl" width="95%">
<tr align="center">
<td>
<strong>用户编号</strong></td>
<td>
<strong>用户名称</strong></td>
<td>
<strong>地址</strong></td>
<td>
<strong>邮政编码</strong></td>
<td>
<strong>主页</strong></td>
<td>
<strong>负责人</strong></td>
<td>
<strong>联系人</strong></td>
<td>
<strong>电子邮箱</strong></td>
<td>
<strong>联系电话</strong></td>
<td>
<strong>添加时间</strong></td>
<td>
<strong>最后修改时间</strong></td>
<td>
<strong>备注</strong></td>
</tr>
<!--content start-->
<tr>
<td align="center">
$htmlformat[0]</td>
<td align="center">
$htmlformat[1]</td>
<td align="left">
$htmlformat[2]</td>
<td align="center">
$htmlformat[3]</td>
<td align="left">
$htmlformat[4]</td>
<td align="center">
$htmlformat[5]</td>
<td align="center">
$htmlformat[6]</td>
<td align="left">
$htmlformat[7]</td>
<td align="left">
$htmlformat[8]</td>
<td align="left">
$htmlformat[9]</td>
<td align="left">
$htmlformat[10]</td>
<td align="left">
$htmlformat[11]</td>
</tr>
<!--content end-->
</table>
</body>
</html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>用户资料信息</title>
<meta content="text/html; charset=gb2312" http-equiv="Content-Type">
<style type="text/css"> <!-- body { margin-left: 0px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; background-color: #FDFDEE; }
td{ font-size:12px; line-height:normal; color:#5e5e5e; text-decoration: none; background-color: #FFFFFB; }
.border_1 { border: 1px solid #9FAB7A; }
.zi1 { font-size: 15px; text-decoration: none; color: #798554; font-weight: bold; }
--></style>
</head>
<body bgcolor="#ffffff">
<table>
<tr>
<td height="2">
</td>
</tr>
</table>
<table align="center" border="0" cellpadding="0" cellspacing="0" width="95%">
<tr>
<th scope="col">
<span class="zi1">用户资料信息</span></th>
</tr>
</table>
<table>
<tr>
<td height="2">
</td>
</tr>
</table>
<table align="center" border="0" cellpadding="0" cellspacing="0" width="95%">
<tr>
<th align="right" scope="col">
<font size="2"><strong>$htmlformat[12]</strong></font></th>
</tr>
</table>
<table id="Table2" align="center" bgcolor="#9fab7a" border="0" cellpadding="0" cellspacing="1"
class="tabl" width="95%">
<tr align="center">
<td>
<strong>用户编号</strong></td>
<td>
<strong>用户名称</strong></td>
<td>
<strong>地址</strong></td>
<td>
<strong>邮政编码</strong></td>
<td>
<strong>主页</strong></td>
<td>
<strong>负责人</strong></td>
<td>
<strong>联系人</strong></td>
<td>
<strong>电子邮箱</strong></td>
<td>
<strong>联系电话</strong></td>
<td>
<strong>添加时间</strong></td>
<td>
<strong>最后修改时间</strong></td>
<td>
<strong>备注</strong></td>
</tr>
<!--content start-->
<tr>
<td align="center">
$htmlformat[0]</td>
<td align="center">
$htmlformat[1]</td>
<td align="left">
$htmlformat[2]</td>
<td align="center">
$htmlformat[3]</td>
<td align="left">
$htmlformat[4]</td>
<td align="center">
$htmlformat[5]</td>
<td align="center">
$htmlformat[6]</td>
<td align="left">
$htmlformat[7]</td>
<td align="left">
$htmlformat[8]</td>
<td align="left">
$htmlformat[9]</td>
<td align="left">
$htmlformat[10]</td>
<td align="left">
$htmlformat[11]</td>
</tr>
<!--content end-->
</table>
</body>
</html>
另外需要注意一下,因为会读取模板,所以要给模板文件相应的权限。