java导出包含多个sheet的Excel代码示例
程序员文章站
2024-02-27 14:37:15
本文实例为大家分享了java导出包含多个sheet的excel的具体代码,供大家参考,具体内容如下
要导出多个sheet,关键就是excel导出的时间设定,在执行导出文件...
本文实例为大家分享了java导出包含多个sheet的excel的具体代码,供大家参考,具体内容如下
要导出多个sheet,关键就是excel导出的时间设定,在执行导出文件之前,创建多个工作表
hssfsheet sheet = workbook.createsheet(sheettitle);
这样每创建一个工作表,便会生成一个新的sheet表,在最后导出excel的时候一次性导出。
示例:
java类:
try { hssfworkbook workbook = new hssfworkbook(); outputstream out = response.getoutputstream(); for(int j=0;j<n;j++){ baseresult<list<t>> teasallist = service.select(teasal); //接下来循环list放到excel表中 if(teasallist.issuccess()&&teasallist.getresult().size()>0){ //文件标题 simpledateformat formatter1 = new simpledateformat("yyyy-mm-dd"); string nowdate = formatter1.format(new date()); string title = null; title = "excel表格标题-" + nowdate + ".xls"; string sheettitle = "sheet表名"; //设置表格标题行 string oneheaders = "首行标题" ; string dateheaders = nowdate ; string[] headers = new string[] {"列1","列2","列3","列4"}; list<object[]> datalist = new arraylist<object[]>(); object[] objs = null; for(int i =0; i<3 ; i++){ //循环每一条数据 objs = new object[headers.length]; objs[1] = "张三"; //姓名 objs[2] = "3"; //序号 //数据添加到excel表格 datalist.add(objs); } //使用流将数据导出 //防止中文乱码 string headstr = "attachment; filename=\"" + new string( title.getbytes("gb2312"), "iso8859-1" ) + "\""; response.setcontenttype("octets/stream"); response.setcontenttype("application/octet-stream"); response.setheader("content-disposition", headstr); exportexceldownfee ex ; ex = new exportexceldownfee(sheettitle, oneheaders, dateheaders,headers, datalist);//没有标题 ex.export(workbook,out); } } workbook.write(out); //循环生成多个sheet之后在导出excel out.close(); //关闭流 } catch (exception e) { e.printstacktrace(); }
工具类:
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; } }
以上所述是小编给大家介绍的java导出包含多个sheet的excel代码示例详解整合,希望对大家有所帮助
下一篇: python读写json文件的简单实现