java实现excel数据导出：（推荐：java视频教程）
目前，比较常用的实现java导入、导出excel的技术有两种jakarta poi和java excel
jakarta poi 是一套用于访问微软格式文档的java api。jakarta poi有很多组件组成，其中有用于操作excel格式文件的hssf和用于操作word的hwpf，在各种组件中目前只有用于操作excel的hssf相对成熟。
jakarta poi hssf api组件
hssf（用于操作excel的组件）提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中,主要部分包括excel对象，样式和格式，还有辅助操作。有以下几种对象：
基本操作步骤
首先，理解一下一个excel的文件的组织形式，一个excel文件对应于一个workbook(hssfworkbook)，一个workbook可以有多个sheet（hssfsheet）组成，一个sheet是由多个row（hssfrow）组成，一个row是由多个cell（hssfcell）组成。
基本操作步骤：
下面来看一个动态生成excel文件的例子：
//创建hssfworkbook对象hssfworkbook wb = new hssfworkbook();//创建hssfsheet对象hssfsheet sheet = wb.createsheet("sheet0");//创建hssfrow对象hssfrow row = sheet.createrow(0);//创建hssfcell对象hssfcell cell=row.createcell(0);//设置单元格的值cell.setcellvalue("单元格中的中文");//输出excel文件fileoutputstream output=new fileoutputstream("d:\\workbook.xls");wkb.write(output);output.flush();
hssf读取文件同样还是使用这几个对象，只是把相应的createxxx方法变成了getxxx方法即可。可见只要理解了其中原理，不管是读还是写亦或是特定格式都可以轻松实现，正所谓知其然更要知其所以然。
导出表格的工具类：
excelutil:
package com.zhl.push.utils;import com.google.common.base.strings;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.hssf.util.hssfcolor;import org.apache.poi.ss.usermodel.verticalalignment;import org.apache.poi.ss.util.cellrangeaddress;import javax.servlet.http.httpservletrequest;import javax.servlet.http.httpservletresponse;import java.io.ioexception;import java.io.outputstream;import java.math.bigdecimal;import java.util.list;import java.util.map;public class excelexportutil { //表头 private string title; //各个列的表头 private string[] heardlist; //各个列的元素key值 private string[] heardkey; //需要填充的数据信息 private list<map> data; //字体大小 private int fontsize = 14; //行高 private int rowheight = 30; //列宽 private int columwidth = 200; //工作表 private string sheetname = "sheet1"; public string gettitle() { return title; } public void settitle(string title) { this.title = title; } public string[] getheardlist() { return heardlist; } public void setheardlist(string[] heardlist) { this.heardlist = heardlist; } public string[] getheardkey() { return heardkey; } public void setheardkey(string[] heardkey) { this.heardkey = heardkey; } public list<map> getdata() { return data; } public void setdata(list<map> data) { this.data = data; } public int getfontsize() { return fontsize; } public void setfontsize(int fontsize) { this.fontsize = fontsize; } public int getrowheight() { return rowheight; } public void setrowheight(int rowheight) { this.rowheight = rowheight; } public int getcolumwidth() { return columwidth; } public void setcolumwidth(int columwidth) { this.columwidth = columwidth; } public string getsheetname() { return sheetname; } public void setsheetname(string sheetname) { this.sheetname = sheetname; } /** * 开始导出数据信息 * */ public byte[] exportexport(httpservletrequest request, httpservletresponse response) throws ioexception { //检查参数配置信息 checkconfig(); //创建工作簿 hssfworkbook wb = new hssfworkbook(); //创建工作表 hssfsheet wbsheet = wb.createsheet(this.sheetname); //设置默认行宽 wbsheet.setdefaultcolumnwidth(20); // 标题样式（加粗，垂直居中） hssfcellstyle cellstyle = wb.createcellstyle(); cellstyle.setalignment(hssfcellstyle.align_center);//水平居中 cellstyle.setverticalalignment(verticalalignment.center);//垂直居中 hssffont fontstyle = wb.createfont(); fontstyle.setboldweight(hssffont.boldweight_bold); fontstyle.setbold(true); //加粗 fontstyle.setfontheightinpoints((short)16); //设置标题字体大小 cellstyle.setfont(fontstyle); //在第0行创建rows (表标题) hssfrow title = wbsheet.createrow((int) 0); title.setheightinpoints(30);//行高 hssfcell cellvalue = title.createcell(0); cellvalue.setcellvalue(this.title); cellvalue.setcellstyle(cellstyle); wbsheet.addmergedregion(new cellrangeaddress(0,0,0,(this.heardlist.length-1))); //设置表头样式，表头居中 hssfcellstyle style = wb.createcellstyle(); //设置单元格样式 style.setalignment(hssfcellstyle.align_center); style.setverticalalignment(hssfcellstyle.vertical_center); //设置字体 hssffont font = wb.createfont(); font.setfontheightinpoints((short) this.fontsize); style.setfont(font); //在第1行创建rows hssfrow row = wbsheet.createrow((int) 1); //设置列头元素 hssfcell cellhead = null; for (int i = 0; i < heardlist.length; i++) { cellhead = row.createcell(i); cellhead.setcellvalue(heardlist[i]); cellhead.setcellstyle(style); } //设置每格数据的样式 （字体红色） hssfcellstyle cellparamstyle = wb.createcellstyle(); hssffont paramfontstyle = wb.createfont(); cellparamstyle.setalignment(hssfcellstyle.align_center); cellparamstyle.setverticalalignment(hssfcellstyle.vertical_center); paramfontstyle.setcolor(hssfcolor.dark_red.index); //设置字体颜色 (红色) paramfontstyle.setfontheightinpoints((short) this.fontsize); cellparamstyle.setfont(paramfontstyle); //设置每格数据的样式2（字体蓝色） hssfcellstyle cellparamstyle2 = wb.createcellstyle(); cellparamstyle2.setalignment(hssfcellstyle.align_center); cellparamstyle2.setverticalalignment(hssfcellstyle.vertical_center); hssffont paramfontstyle2 = wb.createfont(); paramfontstyle2.setcolor(hssfcolor.blue.index); //设置字体颜色 (蓝色) paramfontstyle2.setfontheightinpoints((short) this.fontsize); cellparamstyle2.setfont(paramfontstyle2); //开始写入实体数据信息 int a = 2; for (int i = 0; i < data.size(); i++) { hssfrow roww = wbsheet.createrow((int) a); map map = data.get(i); hssfcell cell = null; for (int j = 0; j < heardkey.length; j++) { cell = roww.createcell(j); cell.setcellstyle(style); object valueobject = map.get(heardkey[j]); string value = null; if (valueobject == null) { valueobject = ""; } if (valueobject instanceof string) { //取出的数据是字符串直接赋值 value = (string) map.get(heardkey[j]); } else if (valueobject instanceof integer) { //取出的数据是integer value = string.valueof(((integer) (valueobject)).floatvalue()); } else if (valueobject instanceof bigdecimal) { //取出的数据是bigdecimal value = string.valueof(((bigdecimal) (valueobject)).floatvalue()); } else { value = valueobject.tostring(); } //设置单个单元格的字体颜色 if(heardkey[j].equals("ddnum") || heardkey[j].equals("sjnum")){ if((long)map.get("ddnum")!=null){ if((long)map.get("sjnum")==null){ cell.setcellstyle(cellparamstyle); } else if((long) map.get("ddnum") != (long) map.get("sjnum")){ if ((long) map.get("ddnum") > (long) map.get("sjnum")) { cell.setcellstyle(cellparamstyle); } if ((long) map.get("ddnum") < (long) map.get("sjnum")) { cell.setcellstyle(cellparamstyle2); } }else { cell.setcellstyle(style); } } } cell.setcellvalue(strings.isnullorempty(value) ? "" : value); } a++; } //导出数据 try { //设置http响应头告诉浏览器下载这个附件 response.setheader("content-disposition", "attachment;filename=" + system.currenttimemillis() + ".xls"); outputstream outputstream = response.getoutputstream(); wb.write(outputstream); outputstream.close(); return wb.getbytes(); } catch (exception ex) { ex.printstacktrace(); throw new ioexception("导出excel出现严重异常，异常信息：" + ex.getmessage()); } } /** * 检查数据配置问题 * * @throws ioexception 抛出数据异常类 */ protected void checkconfig() throws ioexception { if (heardkey == null || heardlist.length == 0) { throw new ioexception("列名数组不能为空或者为null"); } if (fontsize < 0 || rowheight < 0 || columwidth < 0) { throw new ioexception("字体、宽度或者高度不能为负值"); } if (strings.isnullorempty(sheetname)) { throw new ioexception("工作表表名不能为null"); } }}
service :
@override public void queryprojectinfobyschemeid(httpservletresponse response, httpservletrequest request, string schemeid, string pushdate) throws ioexception { list<map> maps = pushmonitordao.queryprojectinfobyschemeid(schemeid, pushdate); if(maps!=null && maps.size()>0){ string companyname = pushmonitordao.querycompanynamebyschemeid(schemeid); string sheettitle = companyname; string [] title = new string[]{"城市","项目名字","合同","实际"}; //设置表格表头字段 string [] properties = new string[]{"city","projectname","ddnum","sjnum"}; // 查询对应的字段 excelexportutil excelexport2 = new excelexportutil(); excelexport2.setdata(maps); excelexport2.setheardkey(properties); excelexport2.setfontsize(14); excelexport2.setsheetname(sheettitle); excelexport2.settitle(sheettitle); excelexport2.setheardlist(title); excelexport2.exportexport(request, response); } }
excel格式
import java.io.bytearrayoutputstream;import java.io.ioexception;import java.lang.reflect.field;import java.util.list;import java.util.logging.level;import java.util.logging.logger;import org.apache.poi.hssf.usermodel.hssfcell;import org.apache.poi.hssf.usermodel.hssfcellstyle;import org.apache.poi.hssf.usermodel.hssffont;import org.apache.poi.hssf.usermodel.hssfrow;import org.apache.poi.hssf.usermodel.hssfsheet;import org.apache.poi.hssf.usermodel.hssfworkbook;import org.apache.poi.ss.usermodel.verticalalignment;import org.apache.poi.ss.util.cellrangeaddress;/** * @作者 yan * @创建日期 * @版本 v1.0 * @描述 excel 导出通用工具类 */public class excelutil { public static byte[] export(string sheettitle, string[] title, list<object> list) { hssfworkbook wb = new hssfworkbook();//创建excel表 hssfsheet sheet = wb.createsheet(sheettitle); sheet.setdefaultcolumnwidth(20);//设置默认行宽 //表头样式（加粗，水平居中，垂直居中） hssfcellstyle cellstyle = wb.createcellstyle(); cellstyle.setalignment(hssfcellstyle.align_center);//水平居中 cellstyle.setverticalalignment(verticalalignment.center);//垂直居中 //设置边框样式 cellstyle.setborderbottom(hssfcellstyle.border_thin); //下边框 cellstyle.setborderleft(hssfcellstyle.border_thin);//左边框 cellstyle.setbordertop(hssfcellstyle.border_thin);//上边框 cellstyle.setborderright(hssfcellstyle.border_thin);//右边框 hssffont fontstyle = wb.createfont(); fontstyle.setboldweight(hssffont.boldweight_bold); cellstyle.setfont(fontstyle); //标题样式（加粗，垂直居中） hssfcellstyle cellstyle2 = wb.createcellstyle(); cellstyle2.setverticalalignment(verticalalignment.center);//垂直居中 cellstyle2.setfont(fontstyle); //设置边框样式 cellstyle2.setborderbottom(hssfcellstyle.border_thin); //下边框 cellstyle2.setborderleft(hssfcellstyle.border_thin);//左边框 cellstyle2.setbordertop(hssfcellstyle.border_thin);//上边框 cellstyle2.setborderright(hssfcellstyle.border_thin);//右边框 //字段样式（垂直居中） hssfcellstyle cellstyle3 = wb.createcellstyle(); cellstyle3.setverticalalignment(verticalalignment.center);//垂直居中 //设置边框样式 cellstyle3.setborderbottom(hssfcellstyle.border_thin); //下边框 cellstyle3.setborderleft(hssfcellstyle.border_thin);//左边框 cellstyle3.setbordertop(hssfcellstyle.border_thin);//上边框 cellstyle3.setborderright(hssfcellstyle.border_thin);//右边框 //创建表头 hssfrow row = sheet.createrow(0); row.setheightinpoints(20);//行高 hssfcell cell = row.createcell(0); cell.setcellvalue(sheettitle); cell.setcellstyle(cellstyle); sheet.addmergedregion(new cellrangeaddress(0,0,0,(title.length-1))); //创建标题 hssfrow rowtitle = sheet.createrow(1); rowtitle.setheightinpoints(20); hssfcell hc; for (int i = 0; i < title.length; i++) { hc = rowtitle.createcell(i); hc.setcellvalue(title[i]); hc.setcellstyle(cellstyle2); } byte result[] = null; bytearrayoutputstream out = null; try { //创建表格数据 field[] fields; int i = 2; for (object obj : list) { fields = obj.getclass().getdeclaredfields(); hssfrow rowbody = sheet.createrow(i); rowbody.setheightinpoints(20); int j = 0; for (field f : fields) { f.setaccessible(true); object va = f.get(obj); if (null == va) { va = ""; } hc = rowbody.createcell(j); hc.setcellvalue(va.tostring()); hc.setcellstyle(cellstyle3); j++; } i++; } out = new bytearrayoutputstream(); wb.write(out); result = out.tobytearray(); } catch (exception ex) { logger.getlogger(excelutil.class.getname()).log(level.severe, null, ex); } finally{ try { if(null != out){ out.close(); } } catch (ioexception ex) { logger.getlogger(excelutil.class.getname()).log(level.severe, null, ex); } finally{ try { wb.close(); } catch (ioexception ex) { logger.getlogger(excelutil.class.getname()).log(level.severe, null, ex); } } } return result; }}
更多java知识请关注java基础教程栏目。
以上就是java怎么导出excel的详细内容。