exceltest.aspx.cs

一个ASP.NET操作Excel文档的类,CSharp实现

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.Reflection;
using System.Diagnostics;
using cfg = System.Configuration;
using System.IO;

namespace ExcelHelperTest
{
	/// <summary>
	/// ExcelTest 的摘要说明。
	/// </summary>
	public class ExcelTest : System.Web.UI.Page
	{
		protected System.Web.UI.WebControls.Button Button2;
		protected System.Web.UI.WebControls.Button Button3;
	
		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.Button2.Click += new System.EventHandler(this.Button2_Click);
			this.Button3.Click += new System.EventHandler(this.Button3_Click);
			this.Load += new System.EventHandler(this.Page_Load);

		}
		#endregion

		private string [,] arr = 
			{
			  {"1","苹果汁","每箱24瓶","18.00","12"},

			  {"2","牛奶","每箱24瓶","15.00","22"},

			  {"3","蕃茄酱","每箱24瓶","18.00","32"},

			  {"4","盐","每箱24瓶","14.00","32"},

			  {"5","麻油","每箱24瓶","18.50","12"},

			  {"6","海鲜粉","每箱24瓶","48.00","34"},

			  {"7","大众奶酪","每箱24瓶","38.00","34"},

			  {"8","德国奶酪","每箱24瓶","12.00","45"},

			  {"9","龙虾","每箱24瓶","13.00","21"},

			  {"10","墨鱼","每箱24瓶","16.00","45"}

			};

		protected string templetFilePath = cfg.ConfigurationSettings.AppSettings["TempletFilePath"];
		protected string saveFilePath = cfg.ConfigurationSettings.AppSettings["SaveFilePath"];

		private void Button2_Click(object sender, System.EventArgs e)
		{
			string templetFile = templetFilePath + "Templet.xls";
			string outFile = saveFilePath + Guid.NewGuid().ToString() + ".xls";
			DataTable dt = DataEntity.GetProducts();
			ExcelHelper eh = new ExcelHelper(templetFile,outFile);

			eh.DataTableToExcel(dt,25,6,2,"页");
			this.DownloadExcelFile(outFile);
		}

		private void Button3_Click(object sender, System.EventArgs e)
		{
			string templetFile = templetFilePath + "Templet.xls";
			string outFile = saveFilePath + Guid.NewGuid().ToString() + ".xls";
			DataTable dt = DataEntity.GetProducts();
			ExcelHelper eh = new ExcelHelper(templetFile,outFile);

			eh.ArrayToExcel(arr,5,6,2,"页");
			this.DownloadExcelFile(outFile);

		}

		protected void DownloadExcelFile(string fileName)
		{
			HttpResponse response = HttpContext.Current.Response;
			string strJs = "<script language=javascript>window.open('{0}');</script>";
			strJs = string.Format(strJs, GetRelativeFileName(fileName));
			response.Write(strJs);
		}

		/// <summary>
		/// 将物理路径转换为虚拟路径
		/// 这段代码是从ExcelQuicker控件源码里面弄过来的，这段代码有点问题
		/// </summary>
		/// <param name="fileName"></param>
		/// <returns></returns>
		
		protected string GetRelativeFileName(string fileName)
		{
			string strRequestPage = Path.GetDirectoryName(HttpContext.Current.Request.Url.AbsoluteUri.ToLower()); //Get request uri
			string strApplicationName = HttpRuntime.AppDomainAppVirtualPath.ToLower().Replace("/","").Replace("\\","");	//Get application name
			int pos = strRequestPage.IndexOf(strApplicationName);
			strRequestPage = strRequestPage.Substring(pos);
			int iLevel = 0;	//The level of request page to application virtual path
			string strParentPath = strRequestPage;

						while(strParentPath != strApplicationName && iLevel<100)	//If iLevel reach 100, I think may be there is same problem of this arithmetic
						{
							iLevel ++;
							strParentPath = Directory.GetParent(strRequestPage).Name;
						}
			//上面注释掉的部分内容换成下面语句
//			iLevel = strRequestPage.Split('/').Length + 1;

			string strFileName = fileName.ToLower();	//The report file name
			pos = strFileName.IndexOf(strApplicationName);
			strFileName = strFileName.Substring(pos + strApplicationName.Length + 1);
			string strRelativePath = string.Empty;	//The relative path of report file to the current request uri
			while(iLevel-- > 0)
				strRelativePath += "..\\";
			strRelativePath += strFileName;
			strRelativePath = strRelativePath.Replace("\\","/");
			return strRelativePath;
		}
		
	}
}