運(yùn)用NPOI操作Excel

一、引入NPOI程序集

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

二、運(yùn)用NPOI導(dǎo)出成excel文件

 1  PRotected void btnOutExcel_Click(object sender, EventArgs e) 2         { 3             HSSFWorkbook workbook = new HSSFWorkbook();//創(chuàng)建一個工作薄 4             ISheet sheet1 = workbook.CreateSheet("學(xué)員信息頁");//創(chuàng)建一個sheet頁 5             IRow rowHeader = sheet1.CreateRow(0);//創(chuàng)建第一行 6             //設(shè)置表頭內(nèi)容 7             rowHeader.CreateCell(0, CellType.STRING).SetCellValue("ID");//創(chuàng)建第一行第一列的單元格，設(shè)定里面的值為ID 8             rowHeader.CreateCell(1, CellType.STRING).SetCellValue("姓名"); 9             rowHeader.CreateCell(2, CellType.STRING).SetCellValue("用戶名");10             rowHeader.CreateCell(3, CellType.STRING).SetCellValue("手機(jī)號");11             rowHeader.CreateCell(4, CellType.STRING).SetCellValue("班級");12             rowHeader.CreateCell(5, CellType.STRING).SetCellValue("創(chuàng)建時間");13             //設(shè)置數(shù)據(jù)內(nèi)容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());//創(chuàng)建第一行第一列的單元格，設(shè)定里面的值為ID26                         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         }

三、從excel文件里面導(dǎo)出數(shù)據(jù)

 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>手機(jī)號</th><th>班級</th><th>創(chuàng)建時間</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         }

demo下載