C# 操作NPOI导入导出

//把T_Seats中的输入导出到Excel
        private void button3_Click(object sender, EventArgs e)
        {
            //1.读取
            string sql = "select * from T_Seats";
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text))
            {
                if (reader.HasRows)
                {

                    //创建Workbook
                    IWorkbook wk = new HSSFWorkbook();

                    //创建Sheet
                    ISheet sheet = wk.CreateSheet("T_Seats");

                    int rowIndex = 0;


                    #region 读取并创建每一行

                    //读取每一条数据
                    while (reader.Read())
                    {
                        //CC_AutoId, CC_LoginId, CC_LoginPassword, CC_UserName, CC_ErrorTimes, CC_LockDateTime, CC_TestInt
                        int autoId = reader.GetInt32(0);
                        string uid = reader.GetString(1);
                        string pwd = reader.GetString(2);
                        string name = reader.GetString(3);
                        int errorTimes = reader.GetInt32(4);
                        DateTime? lockDate = reader.IsDBNull(5) ? null : (DateTime?)reader.GetDateTime(5);
                        int? testInt = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);

                        IRow row = sheet.CreateRow(rowIndex);
                        rowIndex++;

                        //像行中创建单元格
                        row.CreateCell(0).SetCellValue(autoId);
                        row.CreateCell(1).SetCellValue(uid);
                        row.CreateCell(2).SetCellValue(pwd);
                        row.CreateCell(3).SetCellValue(name);
                        row.CreateCell(4).SetCellValue(errorTimes);

                        //对于数据库中的空值，向单元格中插入空内容
                        ICell cellLockDate = row.CreateCell(5);
                        if (lockDate == null)
                        {
                            //设置单元格的数据类型为Blank，表示空单元格
                            cellLockDate.SetCellType(CellType.BLANK);
                        }
                        else
                        {
                            cellLockDate.SetCellValue((DateTime)lockDate);

                            //创建一个单元格格式对象
                            ICellStyle cellStyle = wk.CreateCellStyle();
                            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
                            //设置当前日期这个单元格的是CellStyle属性
                            cellLockDate.CellStyle = cellStyle;

                        }
                        ICell cellTestInt = row.CreateCell(6);
                        if (testInt == null)
                        {
                            cellTestInt.SetCellType(CellType.BLANK);
                        }
                        else
                        {
                            cellTestInt.SetCellValue((int)testInt);
                        }

                    }
                    #endregion


                    //将Excel写入文件
                    using (FileStream fsWrite = File.OpenWrite("tseats.xls"))
                    {
                        wk.Write(fsWrite);
                    }
                }
            }

            MessageBox.Show("操作完毕！");
            //2.写Excel
        }



        //把Excel的内容导入到数据库表T_Seats
        private void button4_Click(object sender, EventArgs e)
        {
            using (FileStream fsRead = File.OpenRead("tseats.xls"))
            {
                //1.读取Excel
                IWorkbook wk = new HSSFWorkbook(fsRead);
                ISheet sheet = wk.GetSheetAt(0);

                string sql_insert = "insert into T_Seats values(@uid,@pwd,@uname,@errorTimes,@lockDate,@testint)";


                //读取sheet中的每一行
                for (int r = 0; r <= sheet.LastRowNum; r++)
                {

                    //读取每行
                    IRow row = sheet.GetRow(r);
                    //读取除了第一列的其他几列
                    string loginId = row.GetCell(1).StringCellValue;
                    string password = row.GetCell(2).StringCellValue;
                    string username = row.GetCell(3).StringCellValue;
                    int errorTimes = (int)row.GetCell(4).NumericCellValue;
                    double? lockDate = null;
                    ICell cellLockDate = row.GetCell(5);
                    if (cellLockDate != null && cellLockDate.CellType != CellType.BLANK)
                    {
                        lockDate = row.GetCell(5).NumericCellValue;
                    }
                    else
                    {
                        //lockDate = null;
                    }

                    int? testInt = null;
                    ICell cellTestInt = row.GetCell(6);

                    if (cellTestInt != null && cellTestInt.CellType != CellType.BLANK)
                    {
                        testInt = (int)cellTestInt.NumericCellValue;
                    }
                    else
                    {
                        //testInt = null;
                    }

                    SqlParameter[] pms = new SqlParameter[] { 
                        new SqlParameter("@uid",loginId),
                        new SqlParameter("@pwd",password),
                        new SqlParameter("@uname",username),
                        new SqlParameter("@errorTimes",errorTimes),

                 
    
                        new SqlParameter("@lockDate",lockDate==null?DBNull.Value:(object)DateTime.FromOADate((double)lockDate)),
                        new SqlParameter("@testint",testInt==null?DBNull.Value:(object)testInt),
                    };
                    //执行插入操作
                    SqlHelper.ExecuteNonQuery(sql_insert, CommandType.Text, pms);
                }
            }
            MessageBox.Show("ok");

            //2.向表T_Seats执行insert语句
        }
    }