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

运行结果如图: