运用NPOI操作EXCEL_.NET_编程开发_程序员俱乐部

一、引入NPOI程序集

下载地址：http://pan.baidu.com/s/1qWI3Vgo

 

二、运用NPOI导出成excel文件

class="code_img_closed" src="/Upload/Images/2014100815/0015B68B3C38AA5B.gif" alt="" />logs_code_hide('5d3a440c-3e37-4971-81f4-5a51df392ddb',event)" src="/Upload/Images/2014100815/2B1B950FA3DF188F.gif" alt="" />

 1  protected void btnOutExcel_Click(object sender, EventArgs e)
 2         {
 3             HSSFWorkbook workbook = new HSSFWorkbook();//创建一个工作薄
 4             ISheet sheet1 = workbook.CreateSheet("学员信息页");//创建一个sheet页
 5             IRow rowHeader = sheet1.CreateRow(0);//创建第一行
 6             //设置表头内容
 7             rowHeader.CreateCell(0, CellType.STRING).SetCellValue("ID");//创建第一行第一列的单元格，设定里面的值为ID
 8             rowHeader.CreateCell(1, CellType.STRING).SetCellValue("姓名");
 9             rowHeader.CreateCell(2, CellType.STRING).SetCellValue("用户名");
10             rowHeader.CreateCell(3, CellType.STRING).SetCellValue("手机号");
11             rowHeader.CreateCell(4, CellType.STRING).SetCellValue("班级");
12             rowHeader.CreateCell(5, CellType.STRING).SetCellValue("创建时间");
13             //设置数据内容
14             OpenDB();
15             string strSql = "select UserId,RealName,UserName,PhoneNum,Phase,CreatedTime from UserInfor";
16             using (cmd = new SqlCommand(strSql, con))
17             {
18                 using (read = cmd.ExecuteReader())
19                 {
20                     int i = 0;
21                     while (read.Read())
22                     {
23                         i++;
24                         IRow rowData = sheet1.CreateRow(i);
25                         rowData.CreateCell(0, CellType.STRING).SetCellValue(read["UserId"].ToString());//创建第一行第一列的单元格，设定里面的值为ID
26                         rowData.CreateCell(1, CellType.STRING).SetCellValue(read["RealName"].ToString());
27                         rowData.CreateCell(2, CellType.STRING).SetCellValue(read["UserName"].ToString());
28                         rowData.CreateCell(3, CellType.STRING).SetCellValue(read["PhoneNum"].ToString());
29                         rowData.CreateCell(4, CellType.STRING).SetCellValue(read["Phase"].ToString());
30 
31                         ICellStyle styledate = workbook.CreateCellStyle();
32                         IDataFormat format = workbook.CreateDataFormat();
33                         styledate.DataFormat = format.GetFormat("yyyy\"年\"m\"月\"d\"日\"");
34                         ICell cellInDate = rowData.CreateCell(5, CellType.NUMERIC);
35                         cellInDate.CellStyle = styledate;
36                         cellInDate.SetCellValue(read["CreatedTime"].ToString());
37                     }
38                     using (Stream stream = File.OpenWrite("e:/1.xls"))
39                     {
40                         workbook.Write(stream);
41                     }
42                 }
43             }
44         }

View Code

三、从excel文件里面导出数据

 1 protected void btnFromExcel_Click(object sender, EventArgs e)
 2         {
 3             StringBuilder sb = new StringBuilder();
 4             sb.Append("<table>");
 5             sb.Append("<tr><th>ID</th><th>姓名</th><th>用户名</th><th>手机号</th><th>班级</th><th>创建时间</th></tr>");
 6             using (Stream stream = File.OpenRead("e:/1.xls"))
 7             {
 8                 HSSFWorkbook workbook = new HSSFWorkbook(stream);
 9                 int j = workbook.GetSheetAt(0).LastRowNum;
10                 for (int row = 1; row <= j; row++)
11                 {
12                     sb.Append("<tr>");
13                     sb.Append(string.Format("<td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td>", workbook.GetSheetAt(0).GetRow(row).GetCell(0).StringCellValue, workbook.GetSheetAt(0).GetRow(row).GetCell(1).StringCellValue, workbook.GetSheetAt(0).GetRow(row).GetCell(2).StringCellValue, workbook.GetSheetAt(0).GetRow(row).GetCell(3).StringCellValue, workbook.GetSheetAt(0).GetRow(row).GetCell(4).StringCellValue, workbook.GetSheetAt(0).GetRow(row).GetCell(5).StringCellValue));
14                     sb.Append("</tr>");
15                 }
16             }
17             sb.Append("<table>");
18             Response.Write(sb.ToString());
19         }

View Code

demo下载