NPOI生成excel并下载

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

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

 

转载于:https://www.cnblogs.com/sportsky/p/5042510.html