using (FileStream fileStreamRead = new FileStream("new.xls" , FileMode.Open ))
{
//创建工作簿
IWorkbook wk = new HSSFWorkbook (fileStreamRead );
//获得工作表的个数
int sheetCount = wk.NumberOfSheets;
for (int i = 0; i < sheetCount; i++)
{
//获得当前工作表
ISheet sheet = wk.GetSheetAt (i);
string strSql =
"insert into T_Customers(CC_CustomerName, CC_CellPhone, CC_Landline,CC_CarNum ,CC_BracketNum,CC_BuyDate) values(@CC_CustomerName, @CC_CellPhone, @CC_Landline,@CC_CarNum ,@CC_BracketNum,@CC_BuyDate)";
//获取当前工作表的总行数
int rowCount = sheet.LastRowNum ;
for (int j = 1; j <= rowCount; j++)
{
SqlParameter[] parameters = new SqlParameter []
{
new SqlParameter ("@CC_CustomerName", SqlDbType .NVarChar,50),
new SqlParameter ("@CC_CellPhone",SqlDbType .VarChar,50),
new SqlParameter ("@CC_Landline",SqlDbType .VarChar,50),
new SqlParameter ("@CC_CarNum",SqlDbType .VarChar,50),
new SqlParameter ("@CC_BracketNum",SqlDbType .VarChar,50),
new SqlParameter ("@CC_BuyDate",SqlDbType .DateTime),
};
//获得当前行
IRow row = sheet.GetRow (j);
//获得当前行的单元格数
int cellCount = row.LastCellNum;
for (int k = 0; k < cellCount; k++)
{
//获得当前单元格
ICell cell = row.GetCell (k);
if (cell == null)
{
//当前单元格的数据为空 则给数据库传入空
parameters[ k].Value = DBNull.Value;
}
else
{
//判断读到的数据类型
switch (cell.CellType)
{
//数字类型
case CellType .NUMERIC:
if (k == 5 )
{
parameters[ k].Value = cell.DateCellValue;
}
else
{
parameters[ k].Value = cell.NumericCellValue;
}
break;
//空值
case CellType .BLANK:
parameters[ k].Value = DBNull.Value;
break;
default:
parameters[ k].Value = cell.ToString();
break;
}
}
}
int r = Alian_SQL_Helper.SQL_Helper .ExecuteNonquery(strSql, CommandType .Text, parameters);
}
}
}