Asp.Net 常用工具类之Office—Excel导出（4）
开发过程中各类报表导入导出防不胜防，网上也是各种解决方法层出不穷，比如Excel，CSV，Word，PDF，HTML等等。。。
网上各种导出插件也是层出不穷，NPOI，微软Microsoft.Office.Interop.Excel，EPPlus等等，其实说起来，微软自带的家伙还是不错的，但是必须要安装office组件，这个不能忍，服务器上安装一个office，呵呵哒！
今天给大家介绍的Excel导入导出用的是NPOI，这个组件还是蛮好用的，而且不依赖office组件。可以根据Excel格式进行自定义。根据平常用到的一些用法进行了封装，旨在更方便的进行文档操作。
我们在Ado.Net里面，DataTable用到的非常多；但是在EF里面，我们更多的是针对List进行操作，毕竟Linq的好处大家都是非常明白，下面通过几个示例分别介绍Excel的导出：
DataTable导出Excel
/// <summary> /// DataTable导出到Excel /// </summary> /// <param name="dt"></param> /// <param name="filename">文件名</param> /// <param name="sheetname">表名</param> /// <param name="maxrow">超过行数新建表</param> /// <param name="isweb">是否web导出，默认是</param> /// <returns></returns> public static void DataTableToExcel(string filename, DataTable dt, string sheetname = "", int maxrow = 100, bool isweb = true) { if (dt == null || dt.Rows.Count < 1) return; if (filename.IndexOf(".xls", StringComparison.OrdinalIgnoreCase) < 0 && filename.IndexOf(".xlsx", StringComparison.OrdinalIgnoreCase) < 0) filename += ".xls"; IWorkbook workbook = new HSSFWorkbook(); if (sheetname.IsNullOrEmpty()) sheetname = filename; if (dt.Rows.Count < maxrow) WriteExcel(dt, 0, dt.Rows.Count - 1, workbook, sheetname); else { int page = dt.Rows.Count / maxrow; for (int i = 0; i < page; i++) { int start = i * maxrow; int end = (i * maxrow) + maxrow - 1; WriteExcel(dt, start, end, workbook, sheetname + i); } int lastPageItemCount = dt.Rows.Count % maxrow; WriteExcel(dt, dt.Rows.Count - lastPageItemCount, lastPageItemCount, workbook, sheetname + page); } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); if (!isweb) { if (!filename.Contains("\\")) filename = Config.Config.GenerateFilePath("Office") + filename; if (!File.Exists(filename)) { using (FileStream fs = new FileStream(filename, FileMode.CreateNew)) { fs.Write(ms.ToArray(), 0, ms.ToArray().Length); fs.Close(); } } } else { //Web导出 HttpContext curContext = HttpContext.Current; curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, Encoding.UTF8)); curContext.Response.BinaryWrite(ms.GetBuffer()); curContext.Response.End(); } } } private static void WriteExcel(DataTable dt, int start, int end, IWorkbook book, string sheetName) { ISheet sheet = book.CreateSheet(sheetName); IRow header = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = header.CreateCell(i); string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName; cell.SetCellValue(val); } int rowIndex = 1; for (int i = start; i <= end; i++) { DataRow dtRow = dt.Rows[i]; IRow excelRow = sheet.CreateRow(rowIndex++); for (int j = 0; j < dtRow.ItemArray.Length; j++) excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString().Trim()); } }
调用方式：
public void Test() { Excel.DataTableToExcel("dt7.xls",dt,"人员名录"); }
List导出Excel:
/// <summary> /// List导出Excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="filename">文件名称和路径</param> /// <param name="sheetname">表名称</param> /// <param name="data">数据</param> /// <param name="title">表头</param> /// <param name="isweb">是否web导出，默认是</param> public static void ListToExcel<T>(string filename, string sheetname, List<T> data, List<string> title = null, bool isweb = true) where T : new() { if (data.Count < 1) return; if (filename.IndexOf(".xls", StringComparison.OrdinalIgnoreCase) < 0 && filename.IndexOf(".xlsx", StringComparison.OrdinalIgnoreCase) < 0) filename += ".xls"; var wookbook = new HSSFWorkbook(); var sheet = wookbook.CreateSheet(sheetname); var entity = new T(); var propertys = entity.GetType().GetProperties(); if (title == null || title.Count < 1) { title = new List<string>(); foreach (PropertyInfo item in propertys) { if (!Ignore.IgnoreField(item.Name)) continue; title.Add(item.Name); } } var rowtitle = sheet.CreateRow(0); for (var i = 0; i < title.Count; i++) rowtitle.CreateCell(i).SetCellValue(title[i]); for (var i = 0; i < data.Count; i++) { var row = sheet.CreateRow(i + 1); //因为表头名称占了一行，所以加1 for (var j = 0; j < propertys.Length; j++) { if (!Ignore.IgnoreField(propertys[j].Name)) continue; var obj = propertys[j].GetValue(data[i], null); row.CreateCell(j).SetCellValue(obj.ToString().Trim()); } } if (!isweb) { if (!filename.Contains("\\")) filename = Config.Config.GenerateFilePath("Office") + filename; if (!File.Exists(filename)) { using (var fs = new FileStream(filename, FileMode.CreateNew)) { wookbook.Write(fs); } } } else { using (MemoryStream ms = new MemoryStream()) { wookbook.Write(ms); //Web导出 HttpContext curContext = HttpContext.Current; curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, Encoding.UTF8)); curContext.Response.BinaryWrite(ms.GetBuffer()); curContext.Response.End(); } } }
调用方法：
public void Test() { Excel.ListToExcel(path,"123",list,newList<string>(){"编号","名字","时间"}); }
OK，各位看官，这一期的文章Excel导出写到这里喏，感谢大家的支持，您的支持是我的动力！
下一期给大家带来的是常用的Excel导入，敬请期待！！！
