//CC_AutoId, CC_LoginId, CC_LoginPassword, CC_UserName, CC_ErrorTimes, CC_LockDateTime, CC_TestInt
string sqlStr = "select * from T_Seats";
using (SqlDataReader reader = Alian_SQL_Helper.SqlHelper .ExecuteReader(sqlStr, CommandType .Text))
{
if (reader.HasRows)
{
//获得列的索引
int autoIdindex = reader.GetOrdinal("CC_AutoId" );
int loginIdIndex = reader.GetOrdinal("CC_LoginId" );
int loginPasswordIndex = reader.GetOrdinal("CC_LoginPassword" );
int userNameIndex = reader.GetOrdinal("CC_UserName" );
int errorTimesIndex = reader.GetOrdinal("CC_ErrorTimes" );
int lockDateTimeIndex = reader.GetOrdinal("CC_LockDateTime" );
int testIntIndex = reader.GetOrdinal("CC_TestInt" );
using (Workbook wk = new HSSFWorkbook ())
{
//创建工作表
using (Sheet sheet = wk.CreateSheet("24K" ))
{
int rowIndex = 0 ;
while (reader.Read())
{
//读取数据
Seats seats = new Seats();
seats.CC_AutoId = reader.GetInt32(autoIdindex);
seats.CC_LoginId = reader.GetString(loginIdIndex);
seats.CC_LoginPassword = reader.GetString(loginPasswordIndex);
seats.CC_UserName = reader.GetString(userNameIndex);
seats.CC_ErrorTimes = reader.GetInt32(errorTimesIndex);
seats.CC_LockDateTime = reader.IsDBNull(lockDateTimeIndex) ? null : (DateTime?)reader.GetDateTime (lockDateTimeIndex );
seats.CC_TestInt = reader.IsDBNull(testIntIndex) ? null : (int?)reader.GetInt32 (testIntIndex );
Row row = sheet.CreateRow(rowIndex);
row.CreateCell(0) .SetCellValue( seats.CC_AutoId);
row.CreateCell(1) .SetCellValue( seats.CC_LoginId);
row.CreateCell(2) .SetCellValue( seats.CC_LoginPassword);
row.CreateCell(3) .SetCellValue( seats.CC_UserName);
row.CreateCell(4) .SetCellValue( seats.CC_ErrorTimes);
Cell cell1 = row.CreateCell (5);
if (seats.CC_LockDateTime == null)
{
cell1.SetCellType(CellType .BLANK);
}
else
{
//添加相应的时间样式
cell1.SetCellValue((DateTime )seats.CC_LockDateTime );
CellStyle cellStyle = wk.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat .GetBuiltinFormat("m/d/yy h:mm" );
cell1.CellStyle = cellStyle ;
}
Cell cellTestInt = row.CreateCell(6);
//对数字类型进行判断 让他可以在表格中参与计算
if (seats.CC_TestInt == null)
{
cellTestInt.SetCellType(CellType .BLANK);
}
else
{
cellTestInt.SetCellValue((int)seats.CC_TestInt );
}
rowIndex++;
}
//保存
using (FileStream fileStreamWrite = File.OpenWrite( "24K.xls"))
{
wk.Write(fileStreamWrite );
}
}
}
}
}
手机扫一扫
移动阅读更方便
你可能感兴趣的文章