本文介绍了一个简单的实体类导出Excel文件的方法，包括创建实体类、核心导出代码实现及示例代码应用。
下面我们介绍一种简单的将实体类导出EXcel文件的方法首先们创建一个实体类Person.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Model { [Serializable] public class Person { private int id; private string firstName; private string lastName; private DateTime? birthDate; private double? weightInKilograms; private double? heightInMeters; public Person() { } public int Id { get { return id; } set { id = value; } } public string FirstName { get { return firstName; } set { firstName = value; } } public string LastName { get { return lastName; } set { lastName = value; } } public DateTime? BirthDate { get { return birthDate; } set { birthDate = value; } } public double? WeightInKilograms { get { return weightInKilograms; } set { weightInKilograms = value; } } public double? HeightInMeters { get { return heightInMeters; } set { heightInMeters = value; } } } }
再创建导出EXcel文件的核心代码
/// <summary> /// 导出excel 简单实现 /// </summary> public static class ExcelUtil { private static Page currentPage = HttpContext.Current.Handler as Page; private static Object sycObj = new Object(); private static int incremental = 10; /// <summary> /// 按照时间生成excel名称 防止生成相同名的excel造成文件覆盖 /// </summary> /// <returns></returns> private static string CreateExcelName() { lock (sycObj) { incremental = incremental + 1; if (incremental > 99) incremental = 10; return Convert.ToInt64(DateTime.Now.ToString("yyyyMMddHHmmssfff") + incremental).ToString(); } } /// <summary> /// 导出excel /// </summary> /// <typeparam name="T">泛型实体</typeparam> /// <param name="response"></param> /// <param name="listColumes">要显示的列名</param> /// <param name="listProperty">要显示的导出属性名 和实体的属性名有关,顺序由显示的列确定 可以同listColumes</param> /// <param name="listModel">实体集合</param> public static void ExportExcel<T>(HttpResponse response, IList<string> listColumns, IList<string> listProperty, IList<T> listModel, IList<string> listOrderColumns, IList<string> listOrderProperty, IList<T> listOrderModel) where T : class, new() { #region 验证 if (listColumns.Count == 0) { throw new IndexOutOfRangeException("No Columnes!"); } if (listColumns.Count != listProperty.Count) { throw new ArgumentException("Columns and properties length are not equal."); } #endregion string sheetName = "sheetName"; using (StringWriter writer = new StringWriter()) { #region Head writer.WriteLine("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">"); writer.WriteLine("<head>"); writer.WriteLine("<!--[if gte mso 9]>"); writer.WriteLine("<xml>"); writer.WriteLine(" <x:ExcelWorkbook>"); writer.WriteLine(" <x:ExcelWorksheets>"); writer.WriteLine(" <x:ExcelWorksheet>"); writer.WriteLine(" <x:Name>" + sheetName + "</x:Name>"); writer.WriteLine(" <x:WorksheetOptions>"); writer.WriteLine(" <x:Print>"); writer.WriteLine(" <x:ValidPrinterInfo />"); writer.WriteLine(" </x:Print>"); writer.WriteLine(" </x:WorksheetOptions>"); writer.WriteLine(" </x:ExcelWorksheet>"); writer.WriteLine(" </x:ExcelWorksheets>"); writer.WriteLine("</x:ExcelWorkbook>"); writer.WriteLine("</xml>"); writer.WriteLine("<![endif]-->"); writer.WriteLine("</head>"); writer.WriteLine("<body>"); #endregion #region 订单详细 writer.WriteLine("<table>"); writer.WriteLine("<tr>"); foreach (string item in listOrderColumns) { writer.WriteLine("<td><b>" + item + "</b></td>"); //列名 } writer.WriteLine("</tr>"); //通过反射 显示要显示的列 BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;//反射标识 Type objType = typeof(T); PropertyInfo[] propInfoArr = objType.GetProperties(bf); foreach (T model in listOrderModel) { writer.WriteLine("<tr>"); foreach (PropertyInfo propInfo in propInfoArr) { foreach (string propName in listOrderProperty) { if (string.Compare(propInfo.Name.ToUpper(), propName.ToUpper()) == 0) { PropertyInfo modelProperty = model.GetType().GetProperty(propName); if (modelProperty != null) { object objResult = modelProperty.GetValue(model, null); writer.WriteLine("<td>" + ((objResult == null) ? string.Empty : objResult) + "</td>"); } else { throw new Exception("Property name may be not exists!"); } } } } writer.WriteLine("</tr>"); } writer.WriteLine("</table>"); #endregion #region 空格 writer.WriteLine("<table>"); writer.WriteLine("<tr>"); writer.WriteLine("<td>"); writer.WriteLine("</td>"); writer.WriteLine("</tr>"); writer.WriteLine("</table>"); #endregion #region Table1 writer.WriteLine("<table>"); writer.WriteLine("<tr>"); foreach (string item in listColumns) { writer.WriteLine("<td><b>" + item + "</b></td>"); //列名 } writer.WriteLine("</tr>"); foreach (T model in listModel) { writer.WriteLine("<tr>"); foreach (PropertyInfo propInfo in propInfoArr) { foreach (string propName in listProperty) { if (string.Compare(propInfo.Name.ToUpper(), propName.ToUpper()) == 0) { PropertyInfo modelProperty = model.GetType().GetProperty(propName); if (modelProperty != null) { object objResult = modelProperty.GetValue(model, null); writer.WriteLine("<td>" + ((objResult == null) ? string.Empty : objResult) + "</td>"); } else { throw new Exception("Property name may be not exists!"); } } } } writer.WriteLine("</tr>"); } writer.WriteLine("</table>"); #endregion writer.WriteLine("</body>"); writer.WriteLine("</html>"); writer.Close(); response.Clear(); response.Buffer = true; response.Charset = "UTF-8"; currentPage.EnableViewState = false; response.AddHeader("Content-Disposition", "attachment; filename=" + CreateExcelName() + ".xls"); response.ContentType = "application/ms-excel"; response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); response.Write(writer); response.End(); } } public static void ExportExcel<T>(HttpResponse response, string tableString) where T : class, new() { #region 验证 if (string.IsNullOrEmpty(tableString)) { throw new IndexOutOfRangeException("No Columnes!"); } #endregion string sheetName = "sheetName"; using (StringWriter writer = new StringWriter()) { #region Head writer.WriteLine("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">"); writer.WriteLine("<head>"); writer.WriteLine("<!--[if gte mso 9]>"); writer.WriteLine("<xml>"); writer.WriteLine(" <x:ExcelWorkbook>"); writer.WriteLine(" <x:ExcelWorksheets>"); writer.WriteLine(" <x:ExcelWorksheet>"); writer.WriteLine(" <x:Name>" + sheetName + "</x:Name>"); writer.WriteLine(" <x:WorksheetOptions>"); writer.WriteLine(" <x:Print>"); writer.WriteLine(" <x:ValidPrinterInfo />"); writer.WriteLine(" </x:Print>"); writer.WriteLine(" </x:WorksheetOptions>"); writer.WriteLine(" </x:ExcelWorksheet>"); writer.WriteLine(" </x:ExcelWorksheets>"); writer.WriteLine("</x:ExcelWorkbook>"); writer.WriteLine("</xml>"); writer.WriteLine("<![endif]-->"); writer.WriteLine("</head>"); writer.WriteLine("<body>"); writer.WriteLine("<meta http-equiv=Content-Type content=text/html;charset=gb2312>"); #endregion writer.WriteLine(tableString); #region Footer writer.WriteLine("</body>"); writer.WriteLine("</html>"); writer.Close(); response.Clear(); response.Buffer = true; response.Charset = "UTF-8"; currentPage.EnableViewState = false; response.AddHeader("Content-Disposition", "attachment; filename=" + CreateExcelName() + ".xls"); response.ContentType = "application/ms-excel"; response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); response.Write(writer); response.End(); #endregion } } public static string GetDataTableString<T>(IList<string> listColumns, IList<string> listProperty, IList<T> listModel, IList<string> listOrderColumns, IList<string> listOrderProperty, IList<T> listOrderModel) where T : class, new() { if(listColumns.Count==0) { return ""; } using (StringWriter writer = new StringWriter()) { #region 订单详细 writer.WriteLine("<table>"); writer.WriteLine("<tr>"); foreach (string item in listOrderColumns) { writer.WriteLine("<td><b>" + item + "</b></td>"); //列名 } writer.WriteLine("</tr>"); //通过反射 显示要显示的列 BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;//反射标识 Type objType = typeof(T); PropertyInfo[] propInfoArr = objType.GetProperties(bf); foreach (T model in listOrderModel) { writer.WriteLine("<tr>"); foreach (PropertyInfo propInfo in propInfoArr) { foreach (string propName in listOrderProperty) { if (string.Compare(propInfo.Name.ToUpper(), propName.ToUpper()) == 0) { PropertyInfo modelProperty = model.GetType().GetProperty(propName); if (modelProperty != null) { object objResult = modelProperty.GetValue(model, null); writer.WriteLine("<td>" + ((objResult == null) ? string.Empty : objResult) + "</td>"); } else { throw new Exception("Property name may be not exists!"); } } } } writer.WriteLine("</tr>"); } writer.WriteLine("</table>"); #endregion #region 空格 writer.WriteLine("<table>"); writer.WriteLine("<tr>"); writer.WriteLine("<td>"); writer.WriteLine("</td>"); writer.WriteLine("</tr>"); writer.WriteLine("</table>"); #endregion #region Table1 writer.WriteLine("<table>"); writer.WriteLine("<tr>"); foreach (string item in listColumns) { writer.WriteLine("<td><b>" + item + "</b></td>"); //列名 } writer.WriteLine("</tr>"); foreach (T model in listModel) { writer.WriteLine("<tr>"); foreach (PropertyInfo propInfo in propInfoArr) { foreach (string propName in listProperty) { if (string.Compare(propInfo.Name.ToUpper(), propName.ToUpper()) == 0) { PropertyInfo modelProperty = model.GetType().GetProperty(propName); if (modelProperty != null) { object objResult = modelProperty.GetValue(model, null); writer.WriteLine("<td>" + ((objResult == null) ? string.Empty : objResult) + "</td>"); } else { throw new Exception("Property name may be not exists!"); } } } } writer.WriteLine("</tr>"); } writer.WriteLine("</table>"); #endregion return writer.ToString(); } } /// <summary> /// 获取单表 /// </summary> /// <param name="listColumns"></param> /// <param name="listProperty"></param> /// <param name="listModel"></param> /// <returns></returns> public static string GetTableString<T>(IList<string> listColumns, IList<string> listProperty, IList<T> listModel) where T : class, new() { if(listColumns.Count==0) { return ""; } using (StringWriter writer = new StringWriter()) { //通过反射 显示要显示的列 BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;//反射标识 Type objType = typeof(T); PropertyInfo[] propInfoArr = objType.GetProperties(bf); #region Table1 writer.WriteLine("<table>"); writer.WriteLine("<tr>"); foreach (string item in listColumns) { writer.WriteLine("<td><b>" + item + "</b></td>"); //列名 } writer.WriteLine("</tr>"); foreach (T model in listModel) { writer.WriteLine("<tr>"); foreach (PropertyInfo propInfo in propInfoArr) { foreach (string propName in listProperty) { if (string.Compare(propInfo.Name.ToUpper(), propName.ToUpper()) == 0) { PropertyInfo modelProperty = model.GetType().GetProperty(propName); if (modelProperty != null) { object objResult = modelProperty.GetValue(model, null); writer.WriteLine("<td>" + ((objResult == null) ? string.Empty : objResult) + "</td>"); } else { throw new Exception("Property name may be not exists!"); } } } } writer.WriteLine("</tr>"); } writer.WriteLine("</table>"); #endregion return writer.ToString(); } } /// <summary> /// 获取空表 /// </summary> /// <returns></returns> public static string GetNoneTableString() { using (StringWriter writer = new StringWriter()) { #region 空格 writer.WriteLine("<table>"); writer.WriteLine("<tr>"); writer.WriteLine("<td>"); writer.WriteLine("</td>"); writer.WriteLine("</tr>"); writer.WriteLine("</table>"); #endregion return writer.ToString(); } } }
新建一个aspx页面如：WebForm.aspx
在页面Page_Load方法中调用ExcelUtil类中的方法
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { List<string> listColumns = new List<string>(); listColumns.Add("ID"); listColumns.Add("姓"); listColumns.Add("名"); listColumns.Add("生日"); List<string> listProperties = new List<string>(); listProperties.Add("Id"); listProperties.Add("FirstName"); listProperties.Add("LastName"); listProperties.Add("BirthDate"); List<Person> listModels = new List<Person>(); Person person = new Person(); person.Id = 1; person.FirstName = "Wong"; person.LastName = "Jeff"; person.BirthDate = DateTime.Now.AddYears(-26); listModels.Add(person); person = new Person(); person.Id = 2; person.FirstName = "Zhao"; person.LastName = "Jeffery"; listModels.Add(person); //导出excel ExcelUtil.ExportExcel<Person>(Response, listColumns, listProperties, listModels); } }
最后就能得到我们想要的结果了。
如若我们js调用该页面，我们就可以在需导出的页面中加入以下代码
<div style="display: none"> <iframe id="frame_ExportExcel"></iframe> </div>
再在Js中加入以下代码
var url = ""; if (query.orderType == "1") { url = $.buildUrl("BMPortal/ExcelOutPut.aspx") + "?" + dateval + "&date=" + new Date().toString(); } else if (query.orderType == "2") { url = $.buildUrl("BMPortal/HostExcelOutPut.aspx") + "?" + dateval + "&date=" + new Date().toString(); } $("#frame_ExportExcel").attr("src", url);
这里的$.buildUrl()是自己定义的方法。如此这样便可以实现Js的调用了。
在这里需要注意的一些问题：
1.传参数为String或StringWriter类型时，导出时会出现乱码，需要加一个标签writer.WriteLine("<meta http-equiv=Content-Type content=text/html;charset=gb2312>");在我们的代码中已被加上。
2.导出的EXcel的标题值显示顺序跟我们创建的Person类属性有关。如我们需要导出BirthDate属性在FirstName属性之前，则在创建时需要将BirthDate属性建在FirstName属性之前，不然有可能会在显示时会出现FirstName值在前，BirthDate值在后。这样便得不到我们想要的效果。
3.在拼接<table>标签时，Excel会支持一些简单的标签如：<b>,<br>等标签。
该种导出功能优点在于，不需要引用任何第三方dll文件，如果使用项目的版本比较低，则可以尝试使用，缺点是功能不强大，在模板设计上会有些麻烦。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
