NPOI导入与导出数据

 1 private void Import_Click(object sender, EventArgs e)
 2         {
 3             using (Stream stream=File.OpenRead(@"F:\2.xls"))
 4             {
 5                 HSSFWorkbook wb = new HSSFWorkbook(stream);
 6                 HSSFSheet sheet = wb.GetSheetAt(0);
 7                 for (int i = 1; i <= sheet.LastRowNum; i++)
 8                 {
 9                     HSSFRow row = sheet.GetRow(i);
10                     HSSFCell cellUserName = row.GetCell(0);
11                     HSSFCell cellPwd = row.GetCell(1);
12                     string password = "";
13                     //因为第0行是标题，所以从1开始
14                     //CellType代表单元格的类型，可以是string，numeric
15                     if (cellPwd.CellType == HSSFCellType.STRING)
16                     {
17                         password = cellPwd.StringCellValue;
18                     }
19                     else if (cellPwd.CellType==HSSFCellType.NUMERIC)
20                     {
21                         //如果是数字类型，则读取NumericCellValue属性
22                         password = cellPwd.NumericCellValue.ToString();
23                     }
　　　　　　　　　　　　　　　　//这里我没有进行数据唯一值的判断
24                     SqlHelper.ExecuteNonQuery("insert into T_User(UserName,Password) values(@UserName,@Password)",
25                         new SqlParameter("@UserName",cellUserName.StringCellValue),
26                         new SqlParameter("@Password",CommonHelper.GetMD5(password)));
27                     MessageBox.Show(cellUserName.StringCellValue);
28                 }
29                 
30             }   
31         }

 1  private void Export_Click(object sender, EventArgs e)
 2         {
 3             HSSFWorkbook wb = new HSSFWorkbook();
 4             HSSFSheet sheet = wb.CreateSheet();
 5 
 6             DataTable dt = SqlHelper.ExecuteDataTable("select * from T_User");
 7 
 8             for (int i = 0; i < dt.Rows.Count; i++)
 9             {
10                 DataRow dataRow = dt.Rows[i];//读取数据库的第i行
11                 HSSFRow row = sheet.CreateRow(i);//Excel创建第i行
12                 HSSFCell cellUserName = row.CreateCell(0);
13                 HSSFCell cellPwd = row.CreateCell(1);
14                 string username=(string)dataRow["UserName"];
15                 string password = (string)dataRow["Password"];
16                 cellUserName.SetCellValue(username);
17                 cellPwd.SetCellValue(password);
18             }
19             using (Stream stream=File.OpenWrite(@"F:\3.xls"))
20             {
21                 wb.Write(stream);
22             }
23         }

posted on 2013-04-21 09:40 洇伱『迗晴ㄋ』阅读(210) 评论(0) 编辑收藏举报