[转]Epplus 导出Excel 示例

部分内容比较生硬，还需要继续优化

try
{
    using (var package = new ExcelPackage())
    {
        ExcelHelper.CreateExcel<ExcelInfo>(package, sheetName, title, titlesName, axs);

        return File(package.GetAsByteArray(), "text/plain", fileName);
    }
}
catch (Exception ex)
{
    return File(Encoding.UTF8.GetBytes(ex.Message), "text/plain", "Error.txt");
}

 

public static ExcelWorksheet CreateExcel<T>(ExcelPackage package, string sheetName, string title, string[] titles, IEnumerable<T> axs)
{
    // 导出 Excel 
    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);
    // 第一行
    worksheet.Cells[1, 1].Value = title;

    // 标题
    var rCount = 2; // 行数
    var cCount = 1; // 列数
    Dictionary<string, string> tis = new Dictionary<string, string>();
    Type type = typeof(T);
    foreach (var item in titles)
    {
        var index = item.IndexOf(':');
        if (index > 0)
        {
            var key = item.Substring(0, index);
            var value = item.Substring(index + 1);
            tis.Add(key, value);
            worksheet.Cells[rCount, ++cCount].Value = value;
        }
        else
        {
            var col = type.GetProperty(item, BindingFlags.Instance | BindingFlags.Public);
            if (col != null && !tis.ContainsKey(item))
            {
                var colName = item;
                var atts = col.GetCustomAttributes(typeof(DisplayNameAttribute), false);
                if (atts != null && atts.Length > 0)
                {
                    var att = atts[0] as DisplayNameAttribute;
                    colName = att.DisplayName;
                }
                tis.Add(item, colName);
                worksheet.Cells[rCount, ++cCount].Value = colName;
            }
        }
    }
    // 第1行格式
    rCount = 1;
    worksheet.Cells[rCount, 1, rCount, cCount].Merge = true;
    AddBorder(worksheet.Cells[rCount, 1, rCount, cCount]);
    AlignmentCenter(worksheet.Cells[rCount, 1, rCount, cCount]);
    worksheet.Row(rCount).Height = 27;
    // 第2行格式
    rCount = 2;
    AddBorder(worksheet.Cells[rCount, 1, rCount, cCount]);
    AlignmentCenter(worksheet.Cells[rCount, 1, rCount, cCount]);
    worksheet.Cells[rCount, 1, rCount, cCount].Style.Fill.PatternType = ExcelFillStyle.Solid;
    worksheet.Cells[rCount, 1, rCount, cCount].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(83, 141, 213));
    worksheet.Cells[rCount, 1, rCount, cCount].Style.Font.Color.SetColor(Color.White);
    worksheet.Cells[rCount, 1, rCount, cCount].Style.Font.Bold = true;
    worksheet.Row(rCount).Height = 27;

    rCount = 3;
    foreach (var row in axs)
    {
        worksheet.Cells[rCount, 1].Value = rCount - 2;
        cCount = 2;
        foreach (var item in tis)
        {
            worksheet.Cells[rCount, cCount].Value = ConvertHelper.GetString(type.GetProperty(item.Key).GetValue(row, null));
            cCount++;
        }
        AddBorder(worksheet.Cells[rCount, 1, rCount, cCount]);
        AlignmentCenter(worksheet.Cells[rCount, 1, rCount, cCount]);
        worksheet.Row(rCount).Height = 23;

        rCount++;
    }

    // 设置列宽
    for (int i = 0; i <= tis.Count; i++)
    {
        worksheet.Column(i + 1).AutoFit();
    }
    return worksheet;
    //return package.GetAsByteArray();
}