如何用excel做报表的实例asp.net
在添加一个输出的aspx页面：Output.aspx，在页面上放置一个label，来显示input框架传递过来的参数，在cs文件中添加代码如下：
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 Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data.OleDb;
using System.IO;
using System.Diagnostics;
public partial class Output : System.Web.UI.Page
{
OleDbConnection Olecn;
OleDbCommand OleCamm;
DataTable DT = new DataTable();
private string StrTime = "";
protected void Page_Load(object sender, EventArgs e)
{
//接收Input框架传递的参数
if (!IsPostBack)
{
StrTime = Request.QueryString["Time"];
if (StrTime != null)
Label1.Text = "时间：" + StrTime;
}
//在页面加载的时候将数据取出
if (ConnectionDataBase())
{
OleCamm = new OleDbCommand();
OleCamm.Connection = Olecn;
Olecn.Open();
OleCamm.CommandText = "select * from pl where date = " + "'" + StrTime + "'";
OleDbDataAdapter OleDAdp = new OleDbDataAdapter(OleCamm);
OleDAdp.Fill(DT);
Olecn.Close();
}
Exceltest();
}
/**//// <summary>
/// 连接数据库
/// </summary>
/// <returns></returns>
private bool ConnectionDataBase()
{
try
{
string DataSource = Server.MapPath("~") + "\\App_Data\\Excel.mdb";
Olecn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+DataSource);
Olecn.Open();
}
catch (Exception e1)
{
Response.Write(e1.Message);
return false;
}
finally
{
Olecn.Close();
}
return true;
}
/**//// <summary>
/// 对Excel的操作
/// </summary>
private void Exceltest()
{
//在创建Excel Application前先将已经打开的Excel资源释放掉
EndExcelProcesse();
//指定模板文件
FileInfo mode = new FileInfo(Server.MapPath("~") + "\\yydw.xls");
//打开复制后的文件X
object missing = Missing.Value;
Excel.Application myExcel = new Excel.Application();
//打开模板文件
myExcel.Application.Workbooks.Open(mode.FullName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//选中有数据的Cells
Excel.Workbook myBook = myExcel.Workbooks[1];
Excel.Worksheet mySheet = (Excel.Worksheet)myBook.Worksheets[1];
Excel.Range r = mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[DT.Rows.Count + 2, DT.Columns.Count - 3]);
r.Select();
//不单独显示Excel，最后在IE中显示
myExcel.Visible = false;
//第一行为报表的标题
myExcel.Cells[1, 1] = "用模板导出的报表";
//逐行写入数据，数组中第一行为报表的列标题
for (int i = 0; i < DT.Columns.Count - 3; i++)
{
myExcel.Cells[2, 1 + i] = DT.Columns[i].Caption; ;
}
//为报表填充数据并设置显示上下标格式
for (int i = 0; i < DT.Rows.Count; i++)
{
for (int j = 0; j < DT.Columns.Count - 4; j++)
{
myExcel.Cells[3 + i, 1 + j] = DT.Rows[i][j];
}
string a = DT.Rows[i][DT.Columns.Count-4].ToString();
string b = DT.Rows[i][DT.Columns.Count-3].ToString();
string c = DT.Rows[i][DT.Columns.Count -2].ToString();
myExcel.Cells[3 + i, DT.Columns.Count - 3] = a + b + c;
//控制输出样式为下标
mySheet.get_Range(mySheet.Cells[i + 3, DT.Columns.Count - 3], mySheet.Cells[i + 3, DT.Columns.Count - 3]).get_Characters(a.Length + 1, b.Length).Font.Subscript = true;
//控制输出样式为上标
mySheet.get_Range(mySheet.Cells[i + 3, DT.Columns.Count - 3], mySheet.Cells[i + 3, DT.Columns.Count - 3]).get_Characters(a.Length + b.Length + 1, c.Length).Font.Superscript = true;
mySheet.Columns.AutoFit();
}
//在当前目录下指定一个临时文件
string FileName = Server.MapPath("~") + "\\Temp.xls";
if (File.Exists(FileName))
{
File.Delete(FileName);
}
myExcel.Save(FileName);
mySheet.Cells.Clear() ;
//设置不出现保存提示框
myBook.Saved = true;
myExcel.Application.Workbooks.Close();
//将Excel文件嵌入在IE里面，也可以在aspx页面用
//<iframe id="myExcelHtml" src ="E:\\练习\\excell\\WebSite2\\Temp.xls" width="100%" height="100%" runat ="server"></iframe>
//标签来嵌入
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "inline;filename='我的文件'");
Response.WriteFile(FileName);
Response.Flush();
Response.Close();
}
/**//// <summary>
/// 当报表数据更新时,先强制结束前一个报表的数据源
/// 这种方法会同时杀死掉用户的excel进程
/// </summary>
protected void EndExcelProcesse()
{
try
{
Process[] myProcesses = Process.GetProcessesByName("excel");
foreach (Process instance in myProcesses)
{
instance.Kill(); //强制终止
}
}
catch { }
//在网上查的用下面的方法结束Excel进程表较好,但一直没有效果
//try
//{
// System.Runtime.InteropServices.Marshal.ReleaseComObject(r);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
// myBook = null;
// mySheet = null;
// myExcel = null;
// r = null;
// GC.Collect();
//}
//catch { }
}
}
