NPOI文件下载地址:http://npoi.codeplex.com/
将文件直接引用至项目中即可,,,,,
虽然网上资料很多,但有可能并找不到自己想要的功能,今天闲的没事,所以就稍微整理了一个简单的例子,希望自己在以后的项目中用得着,到时候就不用在网上查找了,直接把代码copy过来就可以啦~~~~主要是在mvc控制器里面敲的,直接贴代码了。。。。。
1、逻辑代码
public EmptyResult ExportExcle() { HSSFCellStyle style; HSSFFont font; #if DEBUG UserInfo u = new UserInfo(); List<UserInfo> list = u.GetDate(); string url = Server.MapPath(@"\Content\File\MyBook.xls"); string sheetName = "MySheet"; #endif HSSFWorkbook hssfworkbook = new HSSFWorkbook(); FileStream filecreate = new FileStream(url, FileMode.Create, FileAccess.ReadWrite); //创建工作表 HSSFSheet sheet = hssfworkbook.CreateSheet(sheetName) as HSSFSheet; IRow row = sheet.CreateRow(0); row.CreateCell(0).SetCellValue("用户编号"); row.CreateCell(1).SetCellValue("用户名"); row.CreateCell(2).SetCellValue("性别"); row.CreateCell(3).SetCellValue("年龄"); row.CreateCell(4).SetCellValue("电话"); row.CreateCell(5).SetCellValue("身份证"); //行高 row.HeightInPoints = 20; //给表头单元格设置样式(对齐方式、边框、字体、背景颜色) List<ICell> cell = row.Cells; style = hssfworkbook.CreateCellStyle() as HSSFCellStyle; font = hssfworkbook.CreateFont() as HSSFFont; font.IsBold = true;//加粗 font.FontName = "宋体"; font.Color = HSSFColor.Red.Index;//字体颜色 style.SetFont(font); this.CellStyle(style, sheet); style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index; style.FillPattern = FillPattern.SolidForeground; cell.ForEach(delegate(ICell c) { c.CellStyle = style; }); //加载内容 if (list.Any()) { style = hssfworkbook.CreateCellStyle() as HSSFCellStyle; this.CellStyle(style, sheet); for (int i = 0; i < list.Count; i++) { row = sheet.CreateRow(i + 1); row.HeightInPoints = 20; row.CreateCell(0).SetCellValue(list[i].UserId); row.CreateCell(1).SetCellValue(list[i].UserName); row.CreateCell(2).SetCellValue(list[i].Sex); row.CreateCell(3).SetCellValue(list[i].Age); row.CreateCell(4).SetCellValue(list[i].Tel); row.CreateCell(5).SetCellValue(list[i].IdCard); cell = row.Cells; cell.ForEach(p => p.CellStyle = style); } } //将流写入excel文件 hssfworkbook.Write(filecreate); filecreate.Close(); #region 下载文件 FileStream fileopen = new FileStream(url, FileMode.Open); byte[] bytes = new byte[(int)fileopen.Length]; fileopen.Read(bytes, 0, bytes.Length); Response.ContentType = "application/octet-stream"; Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("MyBook.xls", System.Text.Encoding.UTF8)); Response.BinaryWrite(bytes); fileopen.Close(); Response.Flush(); Response.End(); return new EmptyResult(); #endregion }
2、单元格样式
/// <summary> /// 样式 /// </summary> /// <param name="style"></param> /// <param name="sheet"></param> private void CellStyle(HSSFCellStyle style, HSSFSheet sheet) { //自动换行 style.WrapText = true; //边框 style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; //对齐方式 style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; //设置第四列、第五列的宽度 sheet.SetColumnWidth(4, 20 * 256); sheet.SetColumnWidth(5, 30 * 256); }
3、数据源
private class UserInfo { public int UserId { get; set; } public string UserName { get; set; } public string Sex { get; set; } public int Age { get; set; } public string Tel { get; set; } public string IdCard { get; set; } public List<UserInfo> GetDate() { List<UserInfo> list = new List<UserInfo>() { new UserInfo{UserId=1,UserName="张三",Sex="男",Age=20,Tel="18217722343",IdCard="150726198810235436"}, new UserInfo{UserId=2,UserName="李四",Sex="女",Age=23,Tel="18217722343",IdCard="150726198810235436"}, new UserInfo{UserId=3,UserName="王五",Sex="男",Age=21,Tel="18217722343",IdCard="150726198810235436"}, new UserInfo{UserId=4,UserName="赵六",Sex="女",Age=30,Tel="18217722343",IdCard="150726198810235436"}, new UserInfo{UserId=5,UserName="钱七",Sex="男",Age=45,Tel="18217722343",IdCard="150726198810235436"}, new UserInfo{UserId=6,UserName="张三",Sex="女",Age=18,Tel="18217722343",IdCard="150726198810235436"} }; return list; } }
4、前端代码
之前遇到了一个问题,因为刚开始我使用的是Ajax方式提交的,所以在浏览器上无法看到下载提示框,最后搞了半天,原来是因为提交方式不对,换成location就可以了。
<!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Index</title> <script src="~/Scripts/jquery-1.10.2.min.js"></script> <script type="text/javascript"> $(function () { $("#btnExportExcle").click(function () { //var options = { // type: 'get', // cache: false, // async:false, // url: '/Index/ExportExcle', // success: function (data) { // } //}; //$.ajax(options); location = "/Index/ExportExcle"; }); }); </script> </head> <body> <input type="button" value="导出Excel" id="btnExportExcle" /> </body> </html>
5、前台页面效果
6、打开Excel
好了,就介绍到这里了,希望对大家也有所帮助~~~~