//导入,把Excel中的输入导入到表中
private void button4_Click(object sender, EventArgs e)
{
//1.Excel读取
//1.1创建文件流
using (FileStream fsRead = File.OpenRead(“TblPerson.xls”))
{
//1.2根据文件流创建一个“工作薄”Workbook
IWorkbook wk = new HSSFWorkbook(fsRead);
//1.3获取第一个Sheet
ISheet sheet = wk.GetSheetAt(0);
//一、构建sql语句
string sql = "insert into TblPerson values(@name,@age,@height,@gender)";
//1.4读取“工作表”中的每一行
for (int i = 0; i <= sheet.LastRowNum; i++)
{
//二、构建参数
SqlParameter[] pms = new SqlParameter[] {
new SqlParameter("@name",SqlDbType.NVarChar,50),
new SqlParameter("@age",SqlDbType.Int),
new SqlParameter("@height",SqlDbType.Int),
new SqlParameter("@gender",SqlDbType.Bit)
};
//获取每一行
IRow row = sheet.GetRow(i);
//获取当前行的每一列
for (int c = 1; c < row.LastCellNum; c++)
{
//三、为参数赋值
ICell cell = row.GetCell(c);
pms[c - 1].Value = cell.ToString();
}
//处理性别问题
//if (pms[3].Value != null && pms[3].Value.ToString().Length == 0)
if (pms[3].Value == null || pms[3].Value.ToString().Length == 0)
{
//DBNull.Value表示向数据库中插入一个null值。
pms[3].Value = DBNull.Value;
}
// if (pms[2].Value != null && Convert.ToInt32(pms[2].Value) == -1)
if (pms[2].Value == null || Convert.ToInt32(pms[2].Value) == -1)
{
pms[2].Value = DBNull.Value;
}
//四、执行SQL语句
SqlHelper.ExecuteNonQuery(sql, pms);
}
}
MessageBox.Show("导入操作成功!");
//2.ado.net操作(insert插入语句)
}