解释下流程，第一步：将数据库表中的数据导出到excel表

                          第二步：将excel表中的数据再插入到数据库表中（当然没有做重复性校验，测试而已）

注：表结构很简单：

　　Id (int) 自增

　　Name (nvarchar(20))

　　Age (int)

　　Phone(nvarchar(13))

 

1  class Program  2     {  3         /* 首先用ado.net简单封装下 Insert和Select操作，导入导出要用到*/  4   5         //封装ado.net  Insert操作  6         static void Insert(string sql, params SqlParameter[] parameters)  7         {  8             string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;  9             using (SqlConnection conn = new SqlConnection(connstr)) 10             { 11                 conn.Open(); 12                 using (SqlCommand cmd = conn.CreateCommand()) 13                 { 14                     cmd.CommandText = sql; 15                     cmd.Parameters.AddRange(parameters); 16                     cmd.ExecuteNonQuery(); 17                 } 18             } 19         } 20  21         //封装ado.net Select操作 22         static DataTable Select(string sql, params SqlParameter[] parameters) 23         { 24             DataTable table = new DataTable(); 25             string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString; 26             using (SqlConnection conn = new SqlConnection(connstr)) 27             { 28                 conn.Open(); 29                 using (SqlCommand cmd = conn.CreateCommand()) 30                 { 31                     cmd.CommandText = sql; 32                     cmd.Parameters.AddRange(parameters); 33                     using (SqlDataReader read = cmd.ExecuteReader()) 34                     { 35                         table.Load(read); 36                     } 37                 } 38             } 39             return table; 40         } 41      42         /*封装导入导出方法*/ 43  44         //从数据库表中导出数据到excel表中 45         static void Export_toxlsx() 46         { 47             IWorkbook wb = new XSSFWorkbook(); 48             ISheet sheet =  wb.CreateSheet("sheet1");  //创建工作簿“sheet1” 49            /*表头*/ 50            IRow title =  sheet.CreateRow(0); 51             ICell cell_0 = title.CreateCell(0); 52             cell_0.SetCellValue("姓名"); 53             ICell cell_1 = title.CreateCell(1); 54             cell_1.SetCellValue("年龄"); 55             ICell cell_2 = title.CreateCell(2); 56             cell_2.SetCellValue("电话"); 57             /*表内容*/ 58             DataTable tb = Select("select * from t_test"); 59             int i = 1; 60             foreach(DataRow item in tb.Rows) 61             { 62                 IRow row = sheet.CreateRow(i); 63                 ICell cell0 = row.CreateCell(0); 64                 cell0.SetCellValue((string)item["Name"]); 65                 ICell cell1 = row.CreateCell(1); 66                 cell1.SetCellValue((int)item["Age"]); 67                 ICell cell2 = row.CreateCell(2); 68                 cell2.SetCellValue((string)item["Phone"]); 69                 i++; 70             } 71  72             using (Stream stream = File.OpenWrite(@"e:/test/test.xlsx")) 73             { 74                 wb.Write(stream); //将使用npoi创建的xlsx文件写入到流中 75             } 76             Console.WriteLine("Export Done!"); 77         } 78         //从excel表中将数据导入到数据库中 79         static void Import_toDb() 80         { 81             IWorkbook wb = WorkbookFactory.Create(@"e:/test/test.xlsx"); 82             ISheet sheet = wb.GetSheetAt(0); //获取test.xlsx的第一个工作簿 83  84             for(int i=1; i<=sheet.LastRowNum;i++) //从第一行开始读，第0行为表头 85             { 86                 IRow row = sheet.GetRow(i); 87                 ICell cell_0 = row.GetCell(0); 88                 ICell cell_1 = row.GetCell(1); 89                 ICell cell_2 = row.GetCell(2); 90  91                 Insert("insert into t_test (Name,Age,Phone) values(@Name,@Age,@Phone)", 92                     new SqlParameter("@Name",cell_0.StringCellValue), 93                     new SqlParameter("@Age",(int)cell_1.NumericCellValue), 94                     new SqlParameter("@Phone",cell_2.StringCellValue)); 95             } 96             Console.WriteLine("Inset Done!"); 97         } 98         static void Main(string[] args) 99         {100            101             Export_toxlsx();102             Import_toDb();103             Console.ReadKey();104         }105 106     }