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