1.Nuget里添加EPPlus.Core引用。
2前端ajax
/导出高峰论坛excel
$("#toexcel").bind("click", function () {
var data = {};
$.ajax({
url: '/FourmReg/Export',
type: 'POST',
data: JSON.stringify(data),
contentType: 'application/json;charset=UTF-8',
success: function (context) {
if (context != "") {
location.href = context;
}
else {
alert("导出失败!");
}
}
})
});
contoller:
[HttpPost]
public string Export()
{
string sWebRootFolder = hostingEnv.WebRootPath + "/Upload/";
string sFileName ="高峰注册用户表"+ DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
using (ExcelPackage package = new ExcelPackage(file))
{
// 添加worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore");
GfzcDAL fourmdal = new GfzcDAL();
GfzcBLL fourmbll = new GfzcBLL();
List<GfzcDAL> list = fourmbll.GetList(fourmdal, null);
添加头
worksheet.Cells[1, 1].Value = "用户名";
worksheet.Cells[1, 2].Value = "角色";
worksheet.Cells[1, 3].Value = "单位名称";
worksheet.Cells[1, 4].Value = "地址";
worksheet.Cells[1, 5].Value = "名称";
worksheet.Cells[1, 6].Value = "性别";
worksheet.Cells[1, 7].Value = "电话";
worksheet.Cells[1, 8].Value = "职业";
worksheet.Cells[1, 9].Value = "微信";
worksheet.Cells[1, 10].Value = "邮箱";
for (int i = 0; i < list.Count; i++)
{
string type= list[i].Role.ToString();
string sex = list[i].Sex.ToString() == "1" ? "男" : "女";
switch (type)
{
case "1":type = "高校";
break;
case "2":
type = "企业";
break;
case "3":
type = "政府";
break;
}
添加值
worksheet.Cells[i + 2, 1].Value = list[i].UserName;
worksheet.Cells[i + 2, 2].Value = type;
worksheet.Cells[i + 2, 3].Value = list[i].CompanyName;
worksheet.Cells[i + 2, 4].Value = list[i].Address;
worksheet.Cells[i + 2, 5].Value = list[i].Name;
worksheet.Cells[i + 2, 6].Value = sex;
worksheet.Cells[i + 2, 7].Value = list[i].MobPhone;
worksheet.Cells[i + 2, 8].Value = list[i].Job;
worksheet.Cells[i + 2, 9].Value = list[i].Wx;
worksheet.Cells[i + 2, 10].Value = list[i].Email;
}
package.Save();
}
//返回excel路径
return $"{"http://localhost:48768"}/Upload/{sFileName}";
}
list = ForumRegisterBLL.SreachPages(null, forum);
string fileName = ExcelHelper.Export<ForumRegisterDAL>(list, Path.Combine(_hostingEnvironment.WebRootPath,"Files"));
return $"{UserData.userOption.HomeUrl}/Files/{fileName}";
//导出数据表
public static string Export<T>(List<T> list, string host) where T : class, new()
{
string sWebRootFolder =host;
string sFileName = $"{Guid.NewGuid()}.xlsx";
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
using (ExcelPackage package = new ExcelPackage(file))
{
T th = new T();
// 添加worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(th.GetType().Name);
int i = 1;
foreach (var member in th.GetType().GetMembers())
{
if (member.MemberType == MemberTypes.Property)
{
worksheet.Cells[1, i].Value = member.Name;
i++;
}
}
int j = 2;
foreach (T t1 in list)
{
Type t = t1.GetType();
PropertyInfo[] pi = t.GetProperties(BindingFlags.Public | BindingFlags.Instance);
int i1 = 1;
foreach (PropertyInfo p in pi)
{
MethodInfo mi = p.GetGetMethod();
if (mi != null && mi.IsPublic)
{
worksheet.Cells[j, i1].Value = mi.Invoke(t1, new Object[] { });
i1++;
}
}
j++;
}
package.Save();
}
return sFileName;
}