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
}
}