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();

            row.CreateCell().SetCellValue("用户编号");

            row.CreateCell().SetCellValue("用户名");

            row.CreateCell().SetCellValue("性别");

            row.CreateCell().SetCellValue("年龄");

            row.CreateCell().SetCellValue("电话");

            row.CreateCell().SetCellValue("身份证");

            //行高

            row.HeightInPoints = ;

            //给表头单元格设置样式(对齐方式、边框、字体、背景颜色)

            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 = ; i < list.Count; i++)

                {

                    row = sheet.CreateRow(i + );

                    row.HeightInPoints = ;

                    row.CreateCell().SetCellValue(list[i].UserId);

                    row.CreateCell().SetCellValue(list[i].UserName);

                    row.CreateCell().SetCellValue(list[i].Sex);

                    row.CreateCell().SetCellValue(list[i].Age);

                    row.CreateCell().SetCellValue(list[i].Tel);

                    row.CreateCell().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, , 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(,  * );

            sheet.SetColumnWidth(,  * );

        }

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=,UserName="张三",Sex="男",Age=,Tel="",IdCard=""},

                 new UserInfo{UserId=,UserName="李四",Sex="女",Age=,Tel="",IdCard=""},

                 new UserInfo{UserId=,UserName="王五",Sex="男",Age=,Tel="",IdCard=""},

                 new UserInfo{UserId=,UserName="赵六",Sex="女",Age=,Tel="",IdCard=""},

                 new UserInfo{UserId=,UserName="钱七",Sex="男",Age=,Tel="",IdCard=""},

                 new UserInfo{UserId=,UserName="张三",Sex="女",Age=,Tel="",IdCard=""}

                };

                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

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

巴特西

NPOI生成excel并下载

最新文章

热门文章