using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
npoi是重点。
定义一个exporttoexcel的类
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WindowsServer.Log; namespace Zige.Wechat.Common
{
public class ExportToExcel
{
private static readonly Logger _logger = LogManager.GetCurrentClassLogger(); public static void RenderToExcel2003<T>(List<T> datas, Stream stream)
{
IWorkbook workbook = null;
ISheet sheet = null;
IRow dtRow = null; try
{
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet("会员表");
dtRow = sheet.CreateRow();
Type type = typeof(T);
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(type); //Data Header
ICell cell1 = dtRow.CreateCell();
cell1.SetCellValue("创建时间");
ICell cell2 = dtRow.CreateCell();
cell2.SetCellValue("昵称");
ICell cell3 = dtRow.CreateCell();
cell3.SetCellValue("手机号");
ICell cell4 = dtRow.CreateCell();
cell4.SetCellValue("安全密码");
ICell cell5 = dtRow.CreateCell();
cell5.SetCellValue("手机验证");
ICell cell6 = dtRow.CreateCell();
cell6.SetCellValue("状态"); int rowIndex = ;
foreach (T data in datas)
{
object o = data;
dtRow = sheet.CreateRow(rowIndex);
for (int j = ; j < properties.Count; j++)
{
PropertyDescriptor property = properties[j];
object value = property.GetValue(o);
ICell cell = dtRow.CreateCell(j);
if (j == )
{
if (value.ToString()== "true")
{
value = "是";
}
else
{
value = "否";
}
}
if (j == )
{
if (value.ToString() == "true")
{
value = "是";
}
else
{
value = "否";
}
}
SetCellValue(cell, value);
}
rowIndex++;
} workbook.Write(stream);
stream.Flush();
}
catch (Exception ex)
{
_logger.ErrorException("Failed to export to excel.", ex);
}
finally
{
stream.Close();
sheet = null;
workbook = null;
}
} public static void SetCellValue(ICell cell, object value)
{
try
{
if (value is DateTime)
{
cell.SetCellType(CellType.String);
cell.SetCellValue(((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss"));
}
else if (value is TimeSpan)
{
cell.SetCellValue(new DateTime(, , ) + (TimeSpan)value);
}
else if (value is int || value is short || value is float || value is double)
{
cell.SetCellValue(Convert.ToDouble(value));
}
else
{
cell.SetCellValue(value.ToString());
}
}
catch(Exception ex)
{
_logger.ErrorException("Failed to set cell value.", ex);
}
} }
}
然后在api中调用方法
using (ZigeDbContext db = Heart.CreateZigeDbContext())
{
try
{
//var orders_1 = db.Database.SqlQuery<Order>(sqlText).ToList();
var parents = db.Parents.Select(p => new ParentInfo
{
CreatedTime = p.CreatedTime,
NickName=p.NickName,
PhoneNumber=p.PhoneNumber,
ProtectionPassword = p.ProtectionPassword,
IsPhoneNumberVerified=p.IsPhoneNumberVerified,
IsEnabled=p.IsEnabled,
}).OrderBy(or => or.CreatedTime).ToList();
//List<UserAccountInfo> users = db.Database.SqlQuery<UserAccountInfo>(sqlText, sqlParamterStartTime, sqlParameterEndTime).ToList(); string filePath = "~/Content/UploadFolder/" + "会员表" + "(导出时间" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm") + ").xls";
if (System.IO.File.Exists(Server.MapPath(filePath)))
{
System.IO.File.Delete(Server.MapPath(filePath));
} FileStream file = new FileStream(Server.MapPath(filePath), FileMode.Create);
ExportToExcel.RenderToExcel2003<ParentInfo>(parents, file);
var url = filePath.Substring(, filePath.Length - );
var description = "导出日期：" + DateTime.Now.ToString();
//CMSLogUtility.WriteLog("导出OLO新注册用户", Guid.Empty, string.Empty, description);
return Json(new { Code = , Message = url }, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{ throw (ex);
}
}
在js调用api
$("#exportToExcel").on("click", function () {
var url = getRawUrl("Parents/ExportToExcel");
// $("#loading").show();
$.ajax({
url: url,
type: "GET",
cache: false,
success: function (result) {
$("#loading").hide();
location.href = "http://" + location.host + baseServerUrl + result.Message;
}, error: function () {
//$("#loading").hide();
alert("由于网络原因，导出失败！");
}
});
});