C#からExcelをエクスポートする一般的な方法
46719 ワード
ASP.NETプロジェクトでは、Excelのエクスポート機能を処理するためのいくつかの簡単な方法がよく使われています.参考にしてください.
/*=============================================================
* Report Excel Helper
* Author ： Danny,Li
* E-mail ： [email protected]
* Edition： V-101014
*=============================================================*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Web;
namespace IRCommon
{
public class ExcelHelper
{
/// <summary>
/// ，
/// </summary>
private ExcelHelper()
{
// TODO: Add constructor logic here
}
/// <summary>
/// DataTable To Excel (Encoding:GB2312)
/// </summary>
/// <param name="dtData">DataTable</param>
/// <param name="filename">Excel Name</param>
public static void DataTableToExcel(DataTable dtData, string filename)
{
System.Web.UI.WebControls.DataGrid dgExport = null;
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
curContext.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=gb2312>");
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
curContext.Response.Charset = "GB2312";
HttpContext.Current.Response.AppendHeader("content-disposition", "inline; filename=" + filename + ".xls");
HttpContext.Current.Response.Flush();
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dtData;
dgExport.AllowPaging = false;
dgExport.DataBind();
dgExport.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
curContext.Response.End();
}
/// <summary>
/// DataTable To Excel ( Excel ; Encoding:UTF-8)
/// </summary>
/// <param name="page">System.Web.UI.Page</param>
/// <param name="dtData">DataTable</param>
/// <param name="filename">Excel Name</param>
public static void DataTableToExcel(System.Web.UI.Page page, DataTable dtData, string filename)
{
page.Response.Clear();
page.Response.Buffer = true;
page.Response.Charset = "utf-8";
page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
page.Response.AppendHeader("Content-Disposition", "Attachment;fileName=" + filename);
page.Response.ContentType = "application/vnd.ms-excel";
page.Response.Charset = "";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlTxtWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
htmlTxtWrite.Write("<html>\r
<meta content=\"text/html;charset=utf-8\"></meta>\r
<body>\r
");
htmlTxtWrite.Write("<table cellspacing=\"0\" border=\"1\">\r
");
htmlTxtWrite.Write("<tr>\r
");
for (int i = 0; i < dtData.Columns.Count; i++)
{
string strTitle = ConvertToHtmlCode(dtData.Columns[i].Caption.Trim());
htmlTxtWrite.Write("\t<td bgcolor=\"lightblue\">" + strTitle + "</td>\r
");
}
htmlTxtWrite.Write("</tr>\r
");
foreach (DataRow dr in dtData.Rows)
{
htmlTxtWrite.Write("<tr>\r
");
for (int i = 0; i < dtData.Columns.Count; i++)
{
string strValue = "";
if (dtData.Columns[i].DataType == typeof(System.DateTime))
strValue = DateTimeConvertToString(dr[i], false);
else
strValue = ConvertToHtmlCode(dr[i].ToString().Trim());
htmlTxtWrite.Write("\t<td>" + strValue + "</td>\r
");
}
htmlTxtWrite.Write("</tr>\r
");
}
htmlTxtWrite.Write("\r
</table>\r
</body>\r
</html>");
page.Response.Write(stringWrite.ToString());
page.Response.End();
}
private static string ConvertToHtmlCode(string source)
{
string result = "";
result = source.Replace("<", "<");
result = result.Replace(">", ">");
result = result.Replace("\r
", "<BR>");
result = result.Replace(" ", " ");
return result;
}
private static string DateTimeConvertToString(Object obj, bool bShow)
{
DateTime defaultDate = DateTime.Parse("1990-01-01");
DateTime dtDateTime = ConvertToDateTime(obj, defaultDate);
if (dtDateTime == defaultDate && bShow == false)
return "";
if (dtDateTime.Hour == 0 && dtDateTime.Minute == 0 && dtDateTime.Second == 0)
return dtDateTime.ToString("yyyy-MM-dd");
return dtDateTime.ToString("yyyy-MM-dd hh:mm:ss");
}
private static DateTime ConvertToDateTime(Object obj, DateTime dateDefault)
{
try
{
DateTime dateRet = DateTime.Parse(obj.ToString().Trim());
if ((dateRet > DateTime.Parse("1753-01-01 12:00:00")) && (dateRet < DateTime.Parse("9999-01-01 23:59:59")))
return dateRet;
}
catch
{
}
return dateDefault;
}
/// <summary>
/// DataGrid To Excel (Encoding:GB2312)
/// </summary>
/// <param name="dgData">DataGrid</param>
/// <param name="fileName">Excel Name</param>
public static void DataGridToExcel(System.Web.UI.WebControls.DataGrid dgData, string fileName)
{
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
curContext.Response.Clear();
fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
curContext.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
curContext.Response.Charset = "GB2312";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
curContext.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=gb2312>");
curContext.Response.Cache.SetCacheability(HttpCacheability.NoCache);
curContext.Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
dgData.RenderControl(htmlWrite);
curContext.Response.Write(stringWrite.ToString());
curContext.Response.End();
}
/// <summary>
/// GridView To Excel ( VerifyRenderingInServerForm )
/// </summary>
/// <param name="gvList">GridView</param>
/// <param name="fileName">Excel Name</param>
public static void GridViewToExcel(System.Web.UI.WebControls.GridView gvList, string fileName)
{
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
curContext.Response.ClearContent();
curContext.Response.AddHeader("content-disposition", "attachment; filename=" + fileName + ".xls");
curContext.Response.ContentEncoding = System.Text.Encoding.Default;
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
gvList.RenderControl(htw);
curContext.Response.Write(sw.ToString());
curContext.Response.End();
}
#region GridView To Excel (.aspx.cs)
/*public override void VerifyRenderingInServerForm(System.Web.UI.Control control)
{
//GridView To Excel
}*/
#endregion
/// <summary>
/// Excel ( ： )
/// </summary>
/// <param name="dtData">DataTable</param>
/// <param name="filename">Excel Name</param>
public static void DataTableToExcelAsText(DataTable dtData, string filename)
{
System.Web.UI.WebControls.DataGrid dgExport = null;
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
//Response.ContentType application/ms-excel、application/ms-word、application/ms-txt、application/ms-html
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
curContext.Response.Charset = "GB2312";
// ， attachment ， online
//filename=FileFlow.xls ， ， ：.doc .xls .txt .htm
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
// excel
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
// dgData ， DataGrid
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dtData.DefaultView;
dgExport.AllowPaging = false;
// ( )
dgExport.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(gridHrInfo_ItemDataBound1);
dgExport.DataBind();
//
dgExport.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
//curContext.flush();
curContext.Response.End();
}
protected static void gridHrInfo_ItemDataBound1(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
// datagrid
if (e.Item.ItemType == System.Web.UI.WebControls.ListItemType.Item || e.Item.ItemType == System.Web.UI.WebControls.ListItemType.AlternatingItem)
{
//cells[n] （ n , 0 ）
e.Item.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
/*
：
1） ：vnd.ms-excel.numberformat:@
2） ：vnd.ms-excel.numberformat:yyyy/mm/dd
3） ：vnd.ms-excel.numberformat:#,##0.00
4） ：vnd.ms-excel.numberformat:￥#,##0.00
5） ：vnd.ms-excel.numberformat: #0.00%
*/
}
}
/// <summary>
/// Excel Sheet DataSet
/// </summary>
/// <param name="strPath">Excel Path (Excel 8.0 2003)</param>
/// <param name="strSheetName">Excel Sheet Name</param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string strPath, string strSheetName)
{
DataSet ds = null;
try
{
string strConn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + strPath + "; Extended Properties = Excel 8.0";
System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection(strConn);
oconn.Open();
string strSql = "SELECT * FROM [" + strSheetName + "$]";
System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter(strSql, oconn);
ds = new DataSet();
oda.Fill(ds);
}
catch
{
}
return ds;
}
}
}