EPPlus 是使用Open Office XML格式(xlsx)读写Excel 2007 / 2010文件的.net开发库。
Program.cs代码:
class Program
{
static void Main(string[] args)
{
Random r = new Random();
var ds = new DataSet();
var table1 = ds.Tables.Add("aaa");
table1.Columns.Add("Id", typeof(long));
table1.Columns.Add("Name", typeof(string));
table1.Columns.Add("Date", typeof(DateTime));
table1.Columns.Add("Guid", typeof(Guid));
var row1 = table1.NewRow();
row1["Id"] = 1;
row1["Name"] = "阿尔";
row1["Date"] = new DateTime(2015,01,01);
row1["Guid"] = Guid.NewGuid();
table1.Rows.Add(row1);
var row2 = table1.NewRow();
row2["Id"] = 2;
row2["Name"] = "八戒";
row2["Date"] = new DateTime(2015, 02, 02);
row2["Guid"] = Guid.NewGuid();
table1.Rows.Add(row2);
var row3 = table1.NewRow();
row3["Id"] = 3;
row3["Name"] = "雨神";
row3["Date"] = new DateTime(2015, 03, 03);
row3["Guid"] = Guid.NewGuid();
table1.Rows.Add(row3);
var sec = "QWERTYUIOPASDFGHJKLZXCVBNM";
for (int i = 0; i < 20; i++)
{
row3 = table1.NewRow();
row3["Id"] = 3+i;
row3["Name"] = string.Concat(Enumerable.Range(0,r.Next(4,8)).Select(x=>sec[r.Next(sec.Length)]));
row3["Date"] = new DateTime(2015, r.Next(1,12), r.Next(1,28));
row3["Guid"] = Guid.NewGuid();
table1.Rows.Add(row3);
}
File.WriteAllBytes("Den.xlsx", ExcelExporter.GetExcelBytes(ds));
}
}
ExcelExporter.cs代码:
using System;
using System.Data;
using System.Globalization;
using System.Text;
using System.Web;
using OfficeOpenXml;
using OfficeOpenXml.Table;
namespace ExcelExporter
{
public static class ExcelExporter
{
public static byte[] GetExcelBytes(DataSet dataSet)
{
using (var package = new ExcelPackage())
{
var sheetIndex = 1;
foreach (DataTable table in dataSet.Tables)
{
var tableName = table.TableName;
if (String.IsNullOrWhiteSpace(tableName)) tableName = "Sayfa " + sheetIndex;
var worksheet = package.Workbook.Worksheets.Add(tableName);
worksheet.Cells["A1"].LoadFromDataTable(table, true, TableStyles.Medium1);
var i = 1;
foreach (DataColumn column in table.Columns)
{
var excelColumn = worksheet.Column(i);
excelColumn.BestFit = true;
if (column.DataType == typeof(DateTime))
{
excelColumn.Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern + " " + DateTimeFormatInfo.CurrentInfo.ShortTimePattern;
}
excelColumn.AutoFit();
i++;
}
sheetIndex++;
}
return package.GetAsByteArray();
}
}
public static void SendExcel(string fileName, DataSet dataSet, HttpResponse response)
{
var excel = GetExcelBytes(dataSet);
response.ContentEncoding = Encoding.UTF8;
response.Charset = "UTF-8";
response.AddHeader("content-disposition", "attachment;filename=" + fileName);
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
response.BinaryWrite(excel);
}
}
}