使用httphandler实现 NPOI从数据库导数据进Office Exce
?
HSSFWorkbook workbook =(HSSFWorkbook) new HSSFWorkbook();
HSSFSheet sheet =null;
//List<Log4j> list=new List<Log4j>();
//list.Count();
string query="select * from log4j";
using(SqlDataReader dr = SQLDBHelper.ExecuteReader(CommandType.Text, query, null))
{
int rownum = 0;
int [] list= { rownum };
while(dr.Read())
{
pageNum = (list.Count() % pageSize > 0) ? (list.Count() / pageSize + 1) : (list.Count() / pageSize);
Console.WriteLine("sss"+pageNum);
for(int i = 0 ; i < pageNum ; i++)
{
sheet = (HSSFSheet) workbook.CreateSheet("日志信息" + i);
int count = (i + 1) * pageSize > list.Count() ? list.Count() : (i + 1) * pageSize;
for(int j = i * pageSize ; j < count ; j++)
{
//list.Add(null);
int id=(int) dr ["id"];
string stamp=dr ["stamp"].ToString();
string thread=dr ["infoLevel"].ToString();
string clazz=dr ["clazz"].ToString();
string message=dr ["message"].ToString();
HSSFRow row =(HSSFRow) sheet.CreateRow(pageNum); //创建第n行
//把值填充到excle单元
row.CreateCell(0).SetCellValue(id);
row.CreateCell(1).SetCellValue(stamp);
row.CreateCell(2).SetCellValue(thread);
row.CreateCell(3).SetCellValue(clazz);
row.CreateCell(4).SetCellValue(message);
}
}
rownum++;
}
}
workbook.Write(context.Response.OutputStream); //输入excle里面
}
/// <summary>
///
/// </summary>
public bool IsReusable
{
get
{
return false;
}
}
}
}
?数据脚本
?
?
?
?
--创建用户信息表
create database test;
create table log4j
(
id int identity(1,1) not null primary key,?
stamp varchar(10)not null, ? ?
thread varchar(30)not null, ? ? ?
clazz varchar(15)not null,
infoLevel varchar(15)not null,
message varchar(250)?
)
insert ?into log4j values('dddddd','aaaaa','dsfdfsfs','fdsffas');
create table ?tb_charts
(
?team varchar(50),
?Points varchar(50)
)?
insert into tb_charts values('Java','33');
insert into tb_charts values('C++','33');
insert into tb_charts values('Android','33');
insert into tb_charts values('PHP','33');
insert into tb_charts values('C#,ASP.NET','33');
insert into tb_charts values('Oracle','33');
insert into tb_charts values('F','33');
select * from tb_charts;
?
?
?