using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
/// <summary>
///ExcelUtil 的摘要说明
/// </summary>
public class ExcelUtil
{
public ExcelUtil()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
string _Author;
string _Company;
StringBuilder sbBody = new StringBuilder();
StringBuilder sbSheet = new StringBuilder();
public ExcelUtil(string Author, string Company)
{
_Author = Author;
_Company = Company;
sbBody.AppendFormat(
"MIME-Version: 1.0\r\n" +
"X-Document-Type: Workbook\r\n" +
"Content-Type: multipart/related; boundary=\"-=BOUNDARY_EXCEL\"\r\n\r\n" +
"---=BOUNDARY_EXCEL\r\n" +
"Content-Type: text/html; charset=\"big5\"\r\n\r\n" +
"<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n" +
"xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n\r\n" +
"<head>\r\n" +
"<xml>\r\n" +
"<o:DocumentProperties>\r\n" +
"<o:Author>...{0}</o:Author>\r\n" +
"<o:LastAuthor>...{0}</o:LastAuthor>\r\n" +
"<o:Created>...{1}</o:Created>\r\n" +
"<o:LastSaved>...{1}</o:LastSaved>\r\n" +
"<o:Company>...{2}</o:Company>\r\n" +
"<o:Version>11.5606</o:Version>\r\n" +
"</o:DocumentProperties>\r\n" +
"</xml>\r\n" +
"<xml>\r\n" +
"<x:ExcelWorkbook>\r\n" +
"<x:ExcelWorksheets>\r\n"
, _Author
, DateTime.Now.ToString()
, _Company);
}
private string ExportExcel()
{
StringBuilder sb = new StringBuilder(sbBody.ToString());
sb.Append("</x:ExcelWorksheets>\r\n" +
"</x:ExcelWorkbook>\r\n" +
"</xml>\r\n" +
"</head>\r\n" +
"</html>\r\n\r\n");
sb.Append(sbSheet.ToString());
sb.Append("---=BOUNDARY_EXCEL--");
return sb.ToString();
}
public void AddGrid(DataGrid grid, string sheetName)
{
string gid = Guid.NewGuid().ToString();
sbBody.AppendFormat("<x:ExcelWorksheet>\r\n" +
"<x:Name>...{0}</x:Name>\r\n" +
"<x:WorksheetSource HRef=\"cid:...{1}\"/>\r\n" +
"</x:ExcelWorksheet>\r\n"
, sheetName.Replace(":", "").Replace("\\", "").Replace("/", "").Replace
("?", "").Replace("*", "").Replace("[", "").Replace("]", "").Trim()
, gid);
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);
sbSheet.AppendFormat("---=BOUNDARY_EXCEL\r\n" +
"Content-ID: ...{0}\r\n" +
"Content-Type: text/html; charset=\"big5\"\r\n\r\n" +
"<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n" +
"xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n\r\n" +
"<head>\r\n" +
"<xml>\r\n" +
"<x:WorksheetOptions>\r\n" +
"<x:ProtectContents>False</x:ProtectContents>\r\n" +
"<x:ProtectObjects>False</x:ProtectObjects>\r\n" +
"<x:ProtectScenarios>False</x:ProtectScenarios>\r\n" +
"</x:WorksheetOptions>\r\n" +
"</xml>\r\n" +
"</head>\r\n" +
"<body>\r\n"
, gid);
sbSheet.Append(sw.ToString());
sbSheet.Append("</body>\r\n" +
"</html>\r\n\r\n");
sw.Close();
htw.Close();
}
public void Export(Page page, string FileName)
{
page.Response.Clear();
page.Response.Buffer = true;
page.Response.Charset = "";
page.Response.AddHeader("content-disposition", string.Format
("attachment;filename=...{0}.xls", FileName));
page.Response.ContentEncoding = Encoding.GetEncoding("big5");
page.Response.ContentType = "application/vnd.ms-excel";
page.Response.Write(ExportExcel());
page.Response.End();
}
public void Clear()
{
sbBody.Remove(0, sbBody.Length);
sbSheet.Remove(0, sbBody.Length);
}
}
调用方法:
ExcelUtil eu=new ExcelUtil("Jeff_Yeh","隨手記");
eu.AddGrid(givw, "TestA");
eu.AddGrid(givw, "TestB");
eu.Export(this,"Jeff");