NPOI导出Excel表格

1.首先，在项目中引用npoi

 

2.后台代码:

        public void Export()
        {
            using (var conn = new PersonDBContext())
            {
                //获取数据
                List<Person> personList = conn.Persons.ToList();
                var num = personList.Count();

                //获取类型的属性数量
                var typeNum = typeof(Person).GetProperties().Count();

                HSSFWorkbook workbook = new HSSFWorkbook();
                //创建一个页签
                var sheet1 = workbook.CreateSheet("Sheet1");

                //设置表头
                IRow row = sheet1.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("电话");

                //创建行
                for (var i = 0; i < num; i++)
                {
                    IRow row1 = sheet1.CreateRow(i+1);

                    //创建单元格,填充数据
                    row1.CreateCell(0).SetCellValue(personList[i].Id.ToString());
                    row1.CreateCell(1).SetCellValue(personList[i].Name.ToString());
                    row1.CreateCell(2).SetCellValue(personList[i].Age.ToString());
                    row1.CreateCell(3).SetCellValue(personList[i].Sex.ToString());
                    row1.CreateCell(4).SetCellValue(personList[i].Address.ToString());
                    row1.CreateCell(5).SetCellValue(personList[i].Phone.ToString());

                }

                //设置行宽度

                sheet1.SetColumnWidth(2, 10 * 256);

                //获取单元格  并设置样式

                ICellStyle styleCell = workbook.CreateCellStyle();

                //居中
                styleCell.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

                //垂直居中
                styleCell.VerticalAlignment = VerticalAlignment.Top;
                ICellStyle cellstyle = workbook.CreateCellStyle();

                //设置字体

                IFont fontColorRed = workbook.CreateFont();
                fontColorRed.Color = HSSFColor.OliveGreen.Red.Index;

                styleCell.SetFont(fontColorRed);
                
                


                //输出Execl

                string fileName = string.Format("{0:yyyyMMddHHmmssffff}", DateTime.Now).Trim();
                fileName = fileName + ".xls";
                var context = HttpContext;
                context.Response.ContentType = "application/vnd.ms-excel";
                context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", context.Server.UrlEncode(fileName)));
                context.Response.Clear();

                MemoryStream file = new MemoryStream();
                workbook.Write(file);
                context.Response.BinaryWrite(file.GetBuffer());
                context.Response.End();
            }

        }

3.前台调用

<a href="/Home/Export">666</a>

4.结果

 

 

注：好像需要  a标签才能导出，AJAX的异步调取无效，或者 采用  window.open  或者 window.location.href

 

参考：http://www.cnblogs.com/rhythmK/p/3833393.html

 

转载于:https://www.cnblogs.com/dzw159/p/6728545.html