使用EPPLUS快速转换Excel

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

运行结果如图: