二、操作NPOI_从数据库中导出数据到Excel_支持.xls格式 

 1 using NPOI.HSSF.UserModel;
 2 using NPOI.SS.UserModel;
 3 using System;
 4 using System.Collections.Generic;
 5 using System.Linq;
 6 using System.Web;
 7 using System.Web.Mvc;
 8 using System.IO;
 9 using System.Data.SqlClient;
10 using System.Data;
11 
12 namespace ExportDataFromDatabaseToExcel.Controllers
13 {
14     public class ExportController : Controller
15     {
16         //视图页
17         public ActionResult DiplayFirstPage()
18         {
19             return View();
20         }
21 
22         /// NPOI下载电子表格.xls版本
23         /// </summary>
24         /// <returns></returns>
25         public ActionResult ExportToExcel02()
26         {
27             //实例化数据库连接对象，构造函数传入数据库链接地址
28             using (SqlConnection connection = new SqlConnection("Data Source=KAKA-20200829GR;Initial Catalog=DbForNPOI;Integrated Security=True"))
29             {
30                 //实例化数据库适配器对象，根据sql进行匹配
31                 using (SqlDataAdapter adapter = new SqlDataAdapter("select * from BookInfo", connection))
32                 {
33                     //实例化数据表对象
34                     DataTable dataTable = new DataTable();
35 
36                     //填充数据表
37                     adapter.Fill(dataTable);
38 
39                     //实例化工作簿对象
40                     HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
41 
42                     //工作簿中创建数据表，并指定名字
43                     ISheet sheet = hSSFWorkbook.CreateSheet("图书信息表");
44 
45                     //生成标题行，下标为0即第一行
46                     IRow excelTile = sheet.CreateRow(0);
47                     excelTile.CreateCell(0).SetCellValue("图书编号");
48                     excelTile.CreateCell(1).SetCellValue("图书名称");
49                     excelTile.CreateCell(2).SetCellValue("图书作者");
50 
51                     //自定义行号，作为自增种子，因为生变定义了标题行，所以这里从第二行开始，即下标从1尅开始
52                     int RowIndex = 1;
53 
54                     //将数据库中的数据导入到Excel中
55                     foreach (DataRow dataRow in dataTable.Rows)
56                     {
57                         IRow rowBody = sheet.CreateRow(RowIndex);
58                         rowBody.CreateCell(0).SetCellValue(int.Parse(dataRow["BookId"] + ""));
59                         rowBody.CreateCell(1).SetCellValue(dataRow["BookName"] + "");
60                         rowBody.CreateCell(2).SetCellValue(dataRow["Author"] + "");
61                         RowIndex++;
62                     }
63 
64                     //实例化内存流
65                     MemoryStream memoryStream = new MemoryStream();
66 
67                     //将数据写入内存流中
68                     hSSFWorkbook.Write(memoryStream);
69                     memoryStream.Seek(0, SeekOrigin.Begin);
70 
71                     //释放适配器对象资源
72                     adapter.Dispose();
73 
74                     //关闭数据库连接
75                     connection.Close();
76 
77                     //返回文件（直接下载）
78                     return File(memoryStream, "application/vnd-excel", "图书信息表.xls");
79                 }
80             }
81         }
82     }
83 }
 1 @{
 2     Layout = null;
 3 }
 4 
 5 <!DOCTYPE html>
 6 
 7 <html>
 8 <head>
 9     <meta name="viewport" content="width=device-width" />
10     <title>DiplayFirstPage</title>
11     <link href="~/Content/bootstrap.css" rel="stylesheet" />
12 </head>
13 <body>
14     <div>
15         <input type="button" name="name" value="ExportToExcel01" onclick="location.href='/Export/ExportToExcel01'" class="btn btn-success" />
16         <hr />
17         <input type="button" name="name" value="ExportToExcel02" onclick="location.href='/Export/ExportToExcel02'" class="btn btn-warning" />
18     </div>
19 </body>
20 </html>

 

posted @ 2020-09-30 16:20  苍煙落照  阅读(158)  评论(0)    收藏  举报