|
 |
| |
Export DataSet, DataTable to Excel File (.xlsx) using Epplus |
| |
 |
|
|
 |
 |
|
Code by พี่หมี
EPPlus-Create advanced Excel 2007 spreadsheets on the server
download epplus http://epplus.codeplex.com/
Excel2007.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using OfficeOpenXml;
using OfficeOpenXml.Style;
/// <summary>
/// Summary description for Excel2007
/// </summary>
public class Excel2007
{
public Excel2007()
{
//
// TODO: Add constructor logic here
//
}
public byte[] Export(DataSet DsData)
{
int i = 1;
ExcelPackage xlApp = new ExcelPackage();
foreach (DataTable DtData in DsData.Tables)
{
AddSheet(xlApp, DtData, i);
i++;
}
return xlApp.GetAsByteArray();
}
public byte[] Export(DataTable DtData)
{
ExcelPackage xlApp = new ExcelPackage();
AddSheet(xlApp, DtData);
return xlApp.GetAsByteArray();
}
public byte[] Export(DataSet DsData, string TableName)
{
return Export(DsData.Tables[TableName]);
}
protected void AddSheet(ExcelPackage xlApp, DataTable DtData)
{
AddSheet(xlApp, DtData, 1);
}
protected void AddSheet(ExcelPackage xlApp, DataTable DtData, int Index)
{
ExcelWorksheet xlSheet = xlApp.Workbook.Worksheets.Add((DtData.TableName != string.Empty) ? DtData.TableName : string.Format("Sheet{0}", Index.ToString()));
xlSheet.Cells["A1"].LoadFromDataTable(DtData, true);
int rowCount = DtData.Rows.Count;
IEnumerable<int> dateColumns = from DataColumn d in DtData.Columns
where d.DataType == typeof(DateTime) || d.ColumnName.Contains("Date")
select d.Ordinal + 1;
foreach (int dc in dateColumns)
{
xlSheet.Cells[2, dc, rowCount + 1, dc].Style.Numberformat.Format = "dd/MM/yyyy";
}
(from DataColumn d in DtData.Columns select d.Ordinal + 1).ToList().ForEach(dc =>
{
//background color
xlSheet.Cells[1, 1, 1, dc].Style.Fill.PatternType = ExcelFillStyle.Solid;
xlSheet.Cells[1, 1, 1, dc].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightYellow);
//border
xlSheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Top.Style = ExcelBorderStyle.Thin;
xlSheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Right.Style = ExcelBorderStyle.Thin;
xlSheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
xlSheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Left.Style = ExcelBorderStyle.Thin;
xlSheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Top.Color.SetColor(System.Drawing.Color.LightGray);
xlSheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Right.Color.SetColor(System.Drawing.Color.LightGray);
xlSheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Bottom.Color.SetColor(System.Drawing.Color.LightGray);
xlSheet.Cells[1, dc, rowCount + 1, dc].Style.Border.Left.Color.SetColor(System.Drawing.Color.LightGray);
});
}
}
ExportExcel.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportExcel.aspx.cs" Inherits="ExportExcel" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>
ExportExcel.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
public partial class ExportExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xlsx", DateTime.Now.ToString("ddMMyyyy")));
//The following directive causes a open/save/cancel dialog for Excel to be displayed
Response.Cache.SetCacheability(HttpCacheability.Private);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Write to response
Response.BinaryWrite(GetData());
//Response.Flush() 'Do not flush if using compression
//Response.Close()
Response.End();
}
protected byte[] GetData()
{
Excel2007 excel = new Excel2007();
DataSet Ds = new DataSet();
Enumerable.Range(0, 2).Select(c => c).ToList().ForEach(c =>
{
DataTable Dt = new DataTable();
Dt.Columns.Add(new DataColumn("Int", typeof(int)));
Dt.Columns.Add(new DataColumn("String", typeof(string)));
Dt.Columns.Add(new DataColumn("Double", typeof(double)));
Dt.Columns.Add(new DataColumn("DateTime", typeof(DateTime)));
Dt.Columns.Add(new DataColumn("Boolean", typeof(bool)));
for (int i = 0; i < 10; i++)
{
DataRow DrNew = Dt.NewRow();
DrNew["Int"] = i + 1;
DrNew["String"] = string.Format("tungman{0}", i.ToString());
DrNew["Double"] = Convert.ToDouble(1.0f / (1.0f + i));
DrNew["DateTime"] = DateTime.Now.AddDays(i + 1).ToShortDateString();
DrNew["Boolean"] = (i % 2 == 0) ? true : false;
Dt.Rows.Add(DrNew);
}
Ds.Tables.Add(Dt);
});
return excel.Export(Ds);
}
}
Tag : .NET, Excel (Excel.Application), Web (ASP.NET), C#, VS 2010 (.NET 4.x)
|
|
|
 |
 |
 |
 |
| Date :
2012-06-27 16:49:00 |
By :
อิอิ |
View :
10931 |
Reply :
4 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
แจ่มครับ ว่าแต่คุณ อิอิ ไม่ลองเปิดเผยตัวตนดูเหรอครับ เห้นหลายกระทู้ล่ะ อิอิ 
|
| |
|
|
| |
| Date :
2012-06-27 17:08:18 |
By :
mr.win |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
โจทก์เยอะ เดี๋ยวโดนประชาทัณฑ์
|
| |
|
|
| |
| Date :
2012-06-28 08:50:59 |
By :
อิอิ |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
พี่อิอิ เผยตัวเถอะอยากรู้จัก
ผมจะได้ถามเยอะๆ
|
| |
|
|
| |
| Date :
2012-06-28 09:09:30 |
By :
SeedNew |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
อืม ผมไม่ค่อยได้รู้เรื่องด้วยซิ
|
| |
|
|
| |
| Date :
2012-06-28 17:15:00 |
By :
mr.win |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
| |
|
 Load balance : Server 02
|
