//获取数据
using (SqlDataReader reader = Alian_SQL_Helper.SQL_Helper .ExecuteReader(
"select CC_AutoId, CC_CustomerName, CC_CellPhone, CC_Landline, CC_Postcode, CC_Email, CC_Address, CC_BranchId, CC_BuyDate, CC_CarNum, CC_BracketNum, CC_Brand, CC_TypeNum, CC_Suggestion, CC_Remarks from T_Customers",
CommandType.Text))
{
if (reader.HasRows)
{
//创建工作薄
IWorkbook wk = new HSSFWorkbook ();
//创建工作表
ISheet sheet = wk.CreateSheet ("24KB" );
int num = 0 ;
while (reader.Read())
{
//创建行
IRow row = sheet.CreateRow (num);
//字段的长度
// reader.VisibleFieldCount;
//获取当前行中的列数
// reader.FieldCount
//获取一个表示指定列中的数据类型的字符串
//reader.GetDataTypeName("")
for (int i = 0; i < reader.FieldCount; i++)
{
//创建单元格
ICell cell = row.CreateCell (i);
if (reader.IsDBNull(i))
{
cell.SetCellType(CellType .BLANK);
}
else
{
判断数据类型
switch (reader.GetDataTypeName(i))
{
case "nvarchar" :
case "varchar" :
cell.SetCellType(CellType .STRING);
cell.SetCellValue(reader.GetString(i));
break;
case "int" :
cell.SetCellType(CellType .NUMERIC);
cell.SetCellValue(reader.GetInt32(i));
break;
case "datetime" :
cell.SetCellType(CellType .STRING);
ICellStyle cellStyle = wk.CreateCellStyle();
IDataFormat dataFormat = wk.CreateDataFormat();
cellStyle.DataFormat = dataFormat.GetFormat("yyyy-MM-dd hh:mm:ss" );
cell.CellStyle = cellStyle;
cell.SetCellValue(reader.GetDateTime(i));
break;
}
}
}
num++;
}
using (FileStream fileStreamWrite = File.OpenWrite( "24KK.xls"))
{
wk.Write(fileStreamWrite );
}
}
}