MVC 中通用导出页面数据到Excel

 

1、这个功能就是在MVC框架中，将页面绑定的数据直接导出到Excel  中，数据源借助绑定页面的数据源，数据源需要在下载Excel 的地方按照ListJson 的方式加载上去。

1.1 准备工作（只读）：ListJson 绑定数据方式如下：

此处显示到页面的数据我改成了下面的PageShoModel 中，方便导出Excel 的时候通用，如下：

修改后的ListJson 如下

 

 1.2：前台 js 点击下载按钮设置将页面的列（cols） 传递向后台

 

 

代码：

//下载Excel
$("#btndownexcel").click(function () {

// cols 为页面绑定的列信息
var jsondata = {
Id: $("#Id").val(),
files: cols,
Values: {}
};

$(".inputform .datafield").each(function () {
jsondata.Values[$(this).attr("data-fieldname")] = $(this).val();
});
location.href = "/Plan/BigRepair/DownExcel?jsondata=" + JSON.stringify(jsondata);
})

 

2、后台通用导出方法

using Aspose.Cells;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace WY.ViewModels.Base
{
public class ComExportData
{
#region ExportExlData

/// <summary>
/// 将页面数据导出成 Excel
/// </summary>
/// <typeparam name="T"> list 显示转换的实体</typeparam>
/// <param name="jsondata">页面传递Json 有格式要求</param>
/// <param name="list">list 实体数据</param>
/// <returns></returns>
public static byte[] ExportExlData<T>(string jsondata, List<T> list)
{
byte[] bt = null;

JsonExportDataModel model = Newtonsoft.Json.JsonConvert.DeserializeObject<JsonExportDataModel>(jsondata);

//页面传递的数据转换为需要的格式数据
string files_str = JsonConvert.SerializeObject(model.files);
files_str = files_str.Substring(1, files_str.Length - 2);

//标题
var listheader = JsonConvert.DeserializeObject<List<JsonExportHeaderModel>>(files_str);

//过滤掉隐藏列
listheader = listheader.Where(p => p.hidden == false && p.field != "op" && p.title != "").ToList();

Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
Cells cells = sheet.Cells;//单元格

#region 样式

Style style1 = workbook.Styles[workbook.Styles.Add()];//新增样式
Style style2 = workbook.Styles[workbook.Styles.Add()];//新增样式
Style style4 = workbook.Styles[workbook.Styles.Add()];//新增样式

style1.HorizontalAlignment = TextAlignmentType.Center;
style1.Font.Name = "宋体";//文字字体
style1.Font.Size = 12;//文字大小
style1.Font.IsBold = true;//粗体
style1.Font.Color = Color.Black;//粗体
style1.BackgroundColor = Color.Green;
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

style2.HorizontalAlignment = TextAlignmentType.Left;
style2.Font.Name = "宋体";//文字字体
style2.Font.Size = 12;//文字大小
style2.Font.IsBold = true;//粗体
style2.Font.Color = Color.Black;//粗体
style2.BackgroundColor = Color.Green;
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

style4.HorizontalAlignment = TextAlignmentType.Center;//
style4.Font.Size = 10;
style4.Font.IsBold = false;
style4.Font.Color = Color.Black;//粗体
style4.ForegroundColor = Color.SlateBlue;
style4.ForegroundColor = Color.FromArgb(153, 204, 0);
style4.IsTextWrapped = true;
style4.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

#endregion

#region 标题

//填写标题
for (int i = 0; i < listheader.Count(); i++)
{
cells[0, i].PutValue(listheader[i].title); //填写内容
cells[0, i].SetStyle(style1); //给单元格关联样式
cells.SetRowHeight(i, 20);
cells.SetColumnWidth(i, 25);
}

#endregion

#region 绑定数据

//var service = new TB_RepairPlanService();
////数据源
//var list = service.PageList3All().Select(p => new ShowClass(p)).ToList();

//将需要导出的数据源按照列绑定成一个datatable
DataTable table = SetData<T>(list, listheader);

//绑定数据(第一行列已经绑定)
for (int row = 1; row < table.Rows.Count + 1; row++)
{
for (int col = 0; col < listheader.Count; col++)
{
cells[row, col].PutValue(table.Rows[row - 1][listheader[col].field]);//内容
cells[row, col].SetStyle(style4);
}
}

#endregion

#region 输出文件流

cells = sheet.Cells;
int columnCount = cells.MaxColumn; //获取表页的最大列数
int rowCount = cells.MaxRow; //获取表页的最大行数

for (int col = 0; col < columnCount; col++)
{
sheet.AutoFitColumn(col, 0, rowCount);
}
for (int col = 0; col < columnCount; col++)
{
cells.SetColumnWidthPixel(col, cells.GetColumnWidthPixel(col) + 30);
}

System.IO.MemoryStream ms = workbook.SaveToStream();//生成数据流
bt = ms.ToArray();

#endregion

return bt;
}

#endregion

#region SetData

/// <summary>
/// 将数据转换为DataTable 方便绑定到excel 中
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="items"></param>
/// <param name="jsonheader"></param>
/// <returns></returns>
private static DataTable SetData<T>(List<T> items, List<JsonExportHeaderModel> jsonheader)
{
List<string> listHeader = new List<string>();

//标题field
foreach (var header in jsonheader)
{
listHeader.Add(header.field.ToLower());
}

DataTable tb = new DataTable();

PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

//总列数
int total_cols = 0;
//table 添加 columns
foreach (PropertyInfo prop in props)
{
//匹配列
if (listHeader.Contains(prop.Name.ToLower()))
{
Type t = GetCoreType(prop.PropertyType);
tb.Columns.Add(prop.Name, t);
total_cols++;
}
}

var values = new object[total_cols];

//列数
int col_index = 0;

//table 添加 rows
foreach (T item in items)
{
for (int i = 0; i < props.Length; i++)
{
//匹配列
if (listHeader.Contains(props[i].Name.ToLower()))
{
values[col_index] = props[i].GetValue(item, null);
col_index++;
}
}

tb.Rows.Add(values);
}

return tb;
}

/// <summary>
/// Determine of specified type is nullable
/// </summary>
private static bool IsNullable(Type t)
{
return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
}

/// <summary>
/// Return underlying type if type is Nullable otherwise return the type
/// </summary>
private static Type GetCoreType(Type t)
{
if (t != null && IsNullable(t))
{
if (!t.IsValueType)
{
return t;
}
else
{
return Nullable.GetUnderlyingType(t);
}
}
else
{
return t;
}
}

#endregion

}
}

 

//类 JsonExportDataModel

public class JsonExportDataModel
{
public int Id { get; set; }

public dynamic files { get; set; }

public JObject Values { get; set; }

}

 

//类 JsonExportHeaderModel（此处可更具cols 扩展）

public class JsonExportHeaderModel
{
/// <summary>
/// 数据源 列名
/// </summary>
public string field { get; set; }

/// <summary>
/// Page 列名
/// </summary>
public string title { get; set; }

/// <summary>
/// Page 是否隐藏
/// </summary>
public bool hidden { get; set; }

}

 

3、后台通用导出调用。

 

[IgnoreAuthorizeFilter]
public ActionResult DownExcel(string jsondata)
{
var service = new TB_RepairPlanService();
//数据源
var list = service.PageList3All().Select(p => new PageShowClass(p)).ToList();
var bt = ComExportData.ExportExlData<PageShowClass>(jsondata, list);

//客户端保存的文件名
string fileName = "工作票列表.xls";

Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
Response.ContentType = "application/octet-stream";

Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(fileName));
Response.BinaryWrite(bt);
Response.Flush();
Response.End();

return new EmptyResult();
}

 

 

4、注意：导出的地方需要使用到  Aspose.Cells.dll  、 Newtonsoft.Json.dll