运用NPOI操作EXCEL
一、引入NPOI程序集
下载地址：http://pan.baidu.com/s/1qWI3Vgo
二、运用NPOI导出成excel文件
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 }
三、从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 }
