你的位置:首页 > ASP.net教程

[ASP.net教程]NPOI生成excel并下载


NPIO文件下载地址: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

好了,就介绍到这里了,希望对大家也有所帮助~~~~