|
public class exportexceldownfee {
//导出表的列名
private string[] rowname ;
//导出表的小标题
private string oneheaders;
//导出表的日期
private string dateheaders;
//sheet表表名
private string sheettitle;
private list<object[]> datalist = new arraylist<object[]>();
httpservletresponse response;
//构造方法2，传入要导出的数据
public exportexceldownfee( string sheettitle, string oneheaders, string dateheaders, string[] rowname,list<object[]> datalist){
this .datalist = datalist;
this .oneheaders = oneheaders;
this .dateheaders = dateheaders;
this .rowname = rowname;
this .sheettitle = sheettitle;
}
/*
* 导出数据
* */
public void export(hssfworkbook workbook,outputstream out) throws exception{
try{
hssfsheet sheet = workbook.createsheet(sheettitle); // 创建工作表
hssfcellstyle columntopstyle = this.getcolumntopstyle(workbook);//获取列头样式对象
hssfcellstyle style = this.getstyle(workbook); //单元格样式对象
//第一行
hssfrow rowfirstname = sheet.createrow(0);
hssfcell onecellrowname = rowfirstname.createcell(0); //创建列头对应个数的单元格
onecellrowname.setcelltype(hssfcell.cell_type_string); //设置列头单元格的数据类型
hssfrichtextstring onetext = new hssfrichtextstring(oneheaders);
onecellrowname.setcellvalue(onetext); //设置列头单元格的值
//合并单元格cellrangeaddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addmergedregion(new cellrangeaddress(0,0,0,3));
onecellrowname.setcellstyle(columntopstyle); //设置列头单元格样式
//第二行
hssfrow rowdatename = sheet.createrow(1);
hssfcell datecellrowname = rowdatename.createcell(3);
datecellrowname.setcellvalue(dateheaders);
datecellrowname.setcellstyle(columntopstyle);
// 定义所需列数
int columnnum = rowname.length;
hssfrow rowrowname = sheet.createrow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)
// 将列头设置到sheet的单元格中
for(int n=0;n<columnnum;n++){
hssfcell cellrowname = rowrowname.createcell(n); //创建列头对应个数的单元格
cellrowname.setcelltype(hssfcell.cell_type_string); //设置列头单元格的数据类型
hssfrichtextstring text = new hssfrichtextstring(rowname[n]);
cellrowname.setcellvalue(text); //设置列头单元格的值
cellrowname.setcellstyle(style); //设置列头单元格样式
}
//将查询出的数据设置到sheet对应的单元格中
for(int i=0;i<datalist.size();i++){
object[] obj = datalist.get(i);//遍历每个对象
hssfrow row = sheet.createrow(i+3);//创建所需的行数（从第二行开始写数据）
for(int j=0; j<obj.length; j++){
hssfcell cell = null; //设置单元格的数据类型
if(j == 0){
cell = row.createcell(j,hssfcell.cell_type_numeric);
cell.setcellvalue(i+1);
}else{
cell = row.createcell(j,hssfcell.cell_type_string);
if(!"".equals(obj[j]) && obj[j] != null){
cell.setcellvalue(obj[j].tostring()); //设置单元格的值
}
}
cell.setcellstyle(style); //设置单元格样式
}
}
//让列宽随着导出的列长自动适应
for (int colnum = 0; colnum < columnnum; colnum++) {
int columnwidth = sheet.getcolumnwidth(colnum) / 256;
for (int rownum = 0; rownum < sheet.getlastrownum(); rownum++) {
hssfrow currentrow;
//当前行未被使用过
if (sheet.getrow(rownum) == null) {
currentrow = sheet.createrow(rownum);
} else {
currentrow = sheet.getrow(rownum);
}
if (currentrow.getcell(colnum) != null) {
hssfcell currentcell = currentrow.getcell(colnum);
if (currentcell.getcelltype() == hssfcell.cell_type_string) {
int length = 0;
try {
length = currentcell.getstringcellvalue().getbytes().length;
} catch (exception e) {
e.printstacktrace();
}
if (columnwidth < length) {
columnwidth = length;
}
}
}
}
if(colnum == 0){
sheet.setcolumnwidth(colnum, (columnwidth-2) * 256);
}else{
sheet.setcolumnwidth(colnum, (columnwidth+4) * 256);
}
}
}catch(exception e){
e.printstacktrace();
}
}
/*
* 列头单元格样式
*/
public hssfcellstyle getcolumntopstyle(hssfworkbook workbook) {
// 设置字体
hssffont font = workbook.createfont();
//设置字体大小
font.setfontheightinpoints((short)11);
//字体加粗
//font.setboldweight(hssffont.boldweight_bold);
//设置字体名字
font.setfontname("宋体");
//设置样式;
hssfcellstyle style = workbook.createcellstyle();
//在样式用应用设置的字体;
style.setfont(font);
//设置自动换行;
style.setwraptext(false);
//设置水平对齐的样式为居中对齐;
style.setalignment(hssfcellstyle.align_center);
//设置垂直对齐的样式为居中对齐;
style.setverticalalignment(hssfcellstyle.vertical_center);
return style;
}
/*
* 列数据信息单元格样式
*/
public hssfcellstyle getstyle(hssfworkbook workbook) {
// 设置字体
hssffont font = workbook.createfont();
//设置字体名字
font.setfontname( "宋体" );
//设置样式;
hssfcellstyle style = workbook.createcellstyle();
//设置底边框;
style.setborderbottom(hssfcellstyle.border_thin);
//设置底边框颜色;
style.setbottombordercolor(hssfcolor.black.index);
//设置左边框;
style.setborderleft(hssfcellstyle.border_thin);
//设置左边框颜色;
style.setleftbordercolor(hssfcolor.black.index);
//设置右边框;
style.setborderright(hssfcellstyle.border_thin);
//设置右边框颜色;
style.setrightbordercolor(hssfcolor.black.index);
//设置顶边框;
style.setbordertop(hssfcellstyle.border_thin);
//设置顶边框颜色;
style.settopbordercolor(hssfcolor.black.index);
//在样式用应用设置的字体;
style.setfont(font);
//设置自动换行;
style.setwraptext( false );
//设置水平对齐的样式为居中对齐;
style.setalignment(hssfcellstyle.align_center);
//设置垂直对齐的样式为居中对齐;
style.setverticalalignment(hssfcellstyle.vertical_center);
return style;
}
}
|