using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Web;using System.Drawing;using System.Collections;using System.Text.RegularExpressions;namespace SupplyData{ public class ExcelHelp { #region Jieen 2008.3.4 #region 操作說明 //聲明ExcelHelp //LogisticsManage.ExcelHelp excel = new LogisticsManage.ExcelHelp(); //指定當前Page對象 //excel.Page = this.Page; //指定Excel數據源(DataTable) //excel.DataSource = ExcelTable; //指定Excel文件名稱(全名) //excel.FileName = "MyExcel.xls"; //調用導出方法 //excel.OutExcel(); //------------------------------------------------------------- //如何修改列標題 //------------------------------------------------------------- //指定列標題為自定義列 //excel.ThisColumns = true; //設置列標題列表(備注:按順序修改列標題) //excel.ColumnsName = new string[] { "", "", "服務編碼", "問題類型", "問題描述", "發問人", "責任單位", "回復狀態", "詢問日期" }; //設置列標題背景顔色 //excel.HeaderBackColor = System.Drawing.Color.DarkBlue; //設置文檔字體顔色 //excel.HeaderTextColor = System.Drawing.Color.White; #endregion #region Excel私有屬性 #region 數據源 private DataTable m_DataSource; /// /// Excel數據源 /// public DataTable DataSource { get { return this.m_DataSource; } set { this.m_DataSource = value; } } #endregion #region 文件名稱 private string m_FileName; /// /// 文件名稱 /// public string FileName { get { return this.m_FileName; } set { this.m_FileName = value; } } #endregion #region 頁面Page對象 private System.Web.UI.Page m_Page; /// /// 頁面Page對象 /// public System.Web.UI.Page Page { set { this.m_Page = value; } get { return this.m_Page; } } #endregion #region 是否按指定列名輸出 private bool m_thisColumus = false; /// /// 是否按指定列名輸出 /// public bool ThisColumns { get { return this.m_thisColumus; } set { this.m_thisColumus = value; } } #endregion #region 列標題文本顔色 private System.Drawing.Color m_HeaderTextColor = System.Drawing.Color.White; /// /// 列標題文本顔色 /// public System.Drawing.Color HeaderTextColor { set { this.m_HeaderTextColor = value; } get { return this.m_HeaderTextColor; } } #endregion #region 列標題背景色 private System.Drawing.Color m_HeaderBackColor = System.Drawing.Color.Gray; /// /// 列標題背景色 /// public System.Drawing.Color HeaderBackColor { set { this.m_HeaderBackColor = value; } get { return this.m_HeaderBackColor; } } #endregion #region 標題列表 private string[] m_ColumnsName; /// /// 標題列表 /// public string[] ColumnsName { get { return m_ColumnsName; } set { m_ColumnsName = value; } } #endregion #region 流字符集 private System.Text.Encoding m_Encoding = System.Text.Encoding.GetEncoding("utf-8"); public Encoding EEncoding { get { return m_Encoding; } set { m_Encoding = value; } } #endregion #endregion #region Excel公共方法 #region 導出Excel公共方法 /// /// 導出Excel公共方法 /// public void OutExcel() { //DataGrid對象 System.Web.UI.WebControls.DataGrid dgExport = null; //Http輸出流對象 System.Web.HttpResponse httpResponse = Page.Response; //設置輸出文件名稱 httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); httpResponse.ContentEncoding = EEncoding; httpResponse.ContentType = "application/ms-excel"; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); dgExport = new System.Web.UI.WebControls.DataGrid(); //是否按指定列名輸出 if (ThisColumns) { DataTable tempTable = new DataTable(); tempTable.Columns.Clear(); tempTable.Rows.Clear(); for (int i = 0; i < DataSource.Columns.Count; i++) { try { tempTable.Columns.Add(ColumnsName[i]); } catch (Exception ex) { tempTable.Columns.Add("TempName" + i); } } for (int i = 0; i < DataSource.Rows.Count; i++) { DataRow dr = tempTable.NewRow(); for (int j = 0; j < DataSource.Columns.Count; j++) { string tempDate = DataSource.Rows[i][j].ToString(); dr[j] = tempDate; } tempTable.Rows.Add(dr); } dgExport.DataSource = tempTable.DefaultView; } else//按源輸出 { dgExport.DataSource = DataSource.DefaultView; } dgExport.AllowPaging = false; dgExport.HeaderStyle.ForeColor = HeaderTextColor; dgExport.HeaderStyle.BackColor = HeaderBackColor; dgExport.DataBind(); // 返回客户端 dgExport.RenderControl(hw); //httpResponse.Write(tw.ToString()); //httpResponse.End(); string filePath = Page.Server.MapPath("..") + "//" + FileName; System.IO.StreamWriter sw = System.IO.File.CreateText(filePath); //修改栏位格式 string s = FormatDateHTML(tw.ToString()); sw.Write(s); sw.Close(); DownFile(httpResponse, FileName, filePath); httpResponse.End(); } /// /// 導出CSV公共方法 /// public void OutCSV() { System.Web.HttpResponse httpResponse = Page.Response; System.Text.StringBuilder strData = new StringBuilder(); for (int i = 0; i < DataSource.Columns.Count; i++) { try { strData.Append(ColumnsName[i]); strData.Append(","); } catch (Exception ex) { strData.Append("TempName" + i); } } strData.Append("/n"); for (int i = 0; i < DataSource.Rows.Count; i++) { for (int j = 0; j < DataSource.Columns.Count; j++) { strData.Append(DataSource.Rows[i][j].ToString()); strData.Append(","); } strData.Append("/n"); } string temp = string.Format("attachment;filename={0}","ExportData.csv"); httpResponse.ClearHeaders(); httpResponse.AppendHeader("Content-disposition", temp); httpResponse.Write(strData); httpResponse.End(); } private bool DownFile(System.Web.HttpResponse Response, string fileName, string fullPath) { System.IO.FileStream fs = null; try { Response.ContentType = "application/octet-stream"; Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ";charset=utf-8"); fs = System.IO.File.OpenRead(fullPath); long fLen = fs.Length; int size = 102400;//每100K同时下载数据 byte[] readData = new byte[size];//指定缓冲区的大小 if (size > fLen) size = Convert.ToInt32(fLen); long fPos = 0; bool isEnd = false; while (!isEnd) { if ((fPos + size) > fLen) { size = Convert.ToInt32(fLen - fPos); readData = new byte[size]; isEnd = true; } fs.Read(readData, 0, size);//读入一个压缩块 Response.BinaryWrite(readData); fPos += size; } fs.Close(); System.IO.File.Delete(fullPath); return true; } catch { fs.Close(); System.IO.File.Delete(fullPath); return false; } } #endregion #endregion #region Excel格式轉換私有方法 Jieen 2008.6.5 添加 /// /// 修改栏位格式 /// /// 文本内容 /// public static string FormatDateHTML(string _html) { //数字转换 string ReplaceAll = "| (?[0]+//w) | "; Regex r1 = new Regex(ReplaceAll, RegexOptions.None); string[] i1 = System.Text.RegularExpressions.Regex.Split(_html, ReplaceAll); Match mc1 = r1.Match(_html); string s1 = mc1.Groups[1].Value; s1 = "" + s1 + " | "; //mso-number-format:"/@"; //日期转换 string ReplaceReg = "(?[0-9]{1,4}/[0-9]{1,2}/[0-9]{1,2}) | "; Regex r = new Regex(ReplaceReg, RegexOptions.None); string[] i = System.Text.RegularExpressions.Regex.Split(_html, ReplaceReg); Match mc = r.Match(_html); string s = mc.Groups[1].Value; s = "" + s + " | "; _html = System.Text.RegularExpressions.Regex.Replace(_html, "[0-9]{1,4}/[0-9]{1,2}/[0-9]{1,2} | ", s); return System.Text.RegularExpressions.Regex.Replace(_html, "[0]+//w | ", s1); } #endregion /// /// 將HTML串導出為Excel /// 撰寫人： /// 時間：2008-06-11 /// /// public void OutHtmlToExcel(string html) { //Http輸出流對象 System.Web.HttpResponse httpResponse = this.Page.Response; //設置輸出文件名稱 httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); httpResponse.ContentEncoding = EEncoding; httpResponse.ContentType = "application/ms-excel"; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); string filePath = Page.Server.MapPath("..") + "//" + FileName; System.IO.StreamWriter sw = System.IO.File.CreateText(filePath); // string s = FormatDateHTML(tw.ToString()); sw.Write(html); sw.Close(); //DownFile(httpResponse, FileName, filePath); httpResponse.Write(html); httpResponse.End(); } #endregion }}
