java实现Excel的导入导出

本文实例为大家分享了java实现excel导入导出的具体代码，供大家参考，具体内容如下

一.excel读写技术

区别:

二.jxl读写基础代码

1.从数据库将数据导出到excel表格

public class jxlexcel {
public static void main(string[] args) {
 //创建excel文件
 string[] title= {"姓名","课程名","分数"};
 file file=new file("f:/sheet1.xls");
 try {
 file.createnewfile();
 //创建工作簿
 writableworkbook workbook=workbook.createworkbook(file);
 //创建sheet
 writablesheet sheet=workbook.createsheet("表格一", 20);
 //第一行设置列名
 label label=null;
 for (int i = 0; i < title.length; i++) {
 label=new label(i, 0, title[i]);//第一个参数为列,第二个为行
 sheet.addcell(label);
 }
 data data=new data();
 resultset rs=data.getstring();
 while(rs.next()) {
 system.out.println(rs.getstring(1));
 label=new label(0,rs.getrow(),rs.getstring(1));
 sheet.addcell(label);
 label=new label(1,rs.getrow(),rs.getstring(2));
 sheet.addcell(label);
 label=new label(2,rs.getrow(),rs.getstring(3));
 sheet.addcell(label);
 }
 workbook.write();
 workbook.close();
 } catch (exception e) {
 // todo auto-generated catch block
 e.printstacktrace();
 }
}
 
}

2.从excel表格中读取数据

public class jxlread {
public static void main(string[] args) {
 //创建workbook
 try {
 workbook workbook=workbook.getworkbook(new file("f:/sheet1.xls"));
 //获取第一个表格
 sheet sheet=workbook.getsheet(0);
 //获取数据
 for (int i = 0; i < sheet.getrows(); i++) {
 for (int j = 0; j < sheet.getcolumns(); j++) {
 cell cell=sheet.getcell(j, i);
 system.out.print(cell.getcontents()+" ");
 }
 system.out.println();
 }
 } catch (exception e) {
 // todo auto-generated catch block
 e.printstacktrace();
 }
 
}
}

三.poi读写基础代码

//所需jar包:commons-io-2.2.jar;poi-3.11-20141221.jar
//通过poi进行excel导入数据
public class poiexcel {
public static void main(string[] args) throws sqlexception {
 string title[]= {"名字","课程","分数"};
 //1.创建excel工作簿
 hssfworkbook workbook=new hssfworkbook();
 //2.创建一个工作表
 hssfsheet sheet=workbook.createsheet("sheet2");
 //3.创建第一行
 hssfrow row=sheet.createrow(0);
 hssfcell cell=null;
 //4.插入第一行数据
 for (int i = 0; i < title.length; i++) {
 cell=row.createcell(i);
 cell.setcellvalue(title[i]);
 }
 //5.追加数据
 data data=new data();
 resultset rs=data.getstring();
 while(rs.next()) {
 hssfrow row2=sheet.createrow(rs.getrow());
 hssfcell cell2=row2.createcell(0);
 cell2.setcellvalue(rs.getstring(1));
 cell2=row2.createcell(1);
 cell2.setcellvalue(rs.getstring(2));
 cell2=row2.createcell(2);
 cell2.setcellvalue(rs.getstring(3));
 }
 //创建一个文件,将excel内容存盘
 file file=new file("e:/sheet2.xls");
 try {
 file.createnewfile();
 fileoutputstream stream=fileutils.openoutputstream(file);
 workbook.write(stream);
 stream.close();
 } catch (ioexception e) {
 // todo auto-generated catch block
 e.printstacktrace();
 }
 
}
}

//将excel表中内容读取
public class poiread {
public static void main(string[] args) {
 //需要解析的excel文件
 file file=new file("e:/sheet2.xls");
 try {
 //获取工作簿
 fileinputstream fs=fileutils.openinputstream(file);
 hssfworkbook workbook=new hssfworkbook(fs);
 //获取第一个工作表
 hssfsheet hs=workbook.getsheetat(0);
 //获取sheet的第一个行号和最后一个行号
 int last=hs.getlastrownum();
 int first=hs.getfirstrownum();
 //遍历获取单元格里的信息
 for (int i = first; i <last; i++) {
 hssfrow row=hs.getrow(i);
 int firstcellnum=row.getfirstcellnum();//获取所在行的第一个行号
 int lastcellnum=row.getlastcellnum();//获取所在行的最后一个行号
 for (int j = firstcellnum; j <lastcellnum; j++) {
 hssfcell cell=row.getcell(j);
 string value=cell.getstringcellvalue();
 system.out.print(value+" ");
 }
 system.out.println();
 }
 } catch (ioexception e) {
 // todo auto-generated catch block
 e.printstacktrace();
 }
}
}

如果excel版本过高则需要改写用xssf

public class poiexpexcel2 {
 
 /**
 * poi生成excel文件
 * @author david
 * @param args
 */
 public static void main(string[] args) {
 
 string[] title = {"id","name","sex"};
 
 //创建excel工作簿
 xssfworkbook workbook = new xssfworkbook();
 //创建一个工作表sheet
 sheet sheet = workbook.createsheet();
 //创建第一行
 row row = sheet.createrow(0);
 cell cell = null;
 //插入第一行数据 id,name,sex
 for (int i = 0; i < title.length; i++) {
 cell = row.createcell(i);
 cell.setcellvalue(title[i]);
 }
 //追加数据
 for (int i = 1; i <= 10; i++) {
 row nextrow = sheet.createrow(i);
 cell cell2 = nextrow.createcell(0);
 cell2.setcellvalue("a" + i);
 cell2 = nextrow.createcell(1);
 cell2.setcellvalue("user" + i);
 cell2 = nextrow.createcell(2);
 cell2.setcellvalue("男");
 }
 //创建一个文件
 file file = new file("e:/poi_test.xlsx");
 try {
 file.createnewfile();
 //将excel内容存盘
 fileoutputstream stream = fileutils.openoutputstream(file);
 workbook.write(stream);
 stream.close();
 } catch (ioexception e) {
 e.printstacktrace();
 }
 
 }
 
}

四.定制导入模板

1.首先准备好模板的.xml文件,然后导入所需的jar包

例子:student.xml文件

<?xml version="1.0" encoding="utf-8"?>
<excel id="student" code="student" name="学生信息导入">
 <colgroup>
 <col index="a" width='17em'></col>
 <col index="b" width='17em'></col>
 <col index="c" width='17em'></col>
 <col index="d" width='17em'></col>
 <col index="e" width='17em'></col>
 <col index="f" width='17em'></col> 
 </colgroup>
 <title>
 <tr height="16px">
 <td rowspan="1" colspan="6" value="学生信息导入" />
 </tr>
 </title>
 <thead>
 <tr height="16px">
 <th value="编号" />
 <th value="姓名" />
 <th value="年龄" />
 <th value="性别" />
 <th value="出生日期" />
 <th value=" 爱好" /> 
 </tr>
 </thead>
 <tbody>
 <tr height="16px" firstrow="2" firstcol="0" repeat="5">
 <td type="string" isnullable="false" maxlength="30" /><!--用户编号 -->
 <td type="string" isnullable="false" maxlength="50" /><!--姓名 -->
 <td type="numeric" format="##0" isnullable="false" /><!--年龄 -->
 <td type="enum" format="男,女" isnullable="true" /><!--性别 -->
 <td type="date" isnullable="false" maxlength="30" /><!--出生日期 -->
 <td type="enum" format="足球,篮球,乒乓球" isnullable="true" /><!--爱好 -->
 </tr>
 </tbody>
</excel>

所需jar包:
commons-lang3-3.1.jar
jdom.jar
poi-3.11-20141221.jar
commons-io-2.2.jar

java代码:

//准备工作:导入相关jar包commons-lang3-3.1.jar,jdom.jar,poi-3.11-20141221.jar
public class createtemp {
public static void main(string[] args) {
 //获取解析xml路径
 string path=system.getproperty("user.dir")+"/student.xml";
 file file=new file(path);
 saxbuilder builder=new saxbuilder();
 //解析xml文件
 try {
 document document=builder.build(file);
 //创建excel
 hssfworkbook workbook=new hssfworkbook();
 //创建表格
 hssfsheet sheet=workbook.createsheet("sheet0");
 //获取xml文件的根节点
 element root=document.getrootelement();
 //获取模板名称
 string tempname=root.getattributevalue("name");
 //设置列宽
 element colgroup=root.getchild("colgroup");
 setcolumnwidth(sheet,colgroup);
 //设置标题
 int rownum = 0;
 int column = 0;
 element title=root.getchild("title");
 list<element> trs=title.getchildren("tr");
 for (int i = 0; i <trs.size(); i++) {
 element tr=trs.get(i);
 list<element> tds=tr.getchildren("td");
 hssfrow row=sheet.createrow(rownum);
 hssfcellstyle cellstyle=workbook.createcellstyle();//创建单元格格式
 cellstyle.setalignment(hssfcellstyle.align_center);//标题居中
 for (int j = 0; j < tds.size(); j++) {
 element td=tds.get(j);
 hssfcell cell=row.createcell(j);
 attribute rowspan=td.getattribute("rowspan");
 attribute colspan=td.getattribute("colspan");
 attribute value=td.getattribute("value");
 if (value!=null) {
 string content=value.getvalue();
 
 cell.setcellvalue(content);
 int rspan=rowspan.getintvalue()-1;
 int cspan=colspan.getintvalue()-1;
 //设置字体
 hssffont font=workbook.createfont();
 font.setfontname("仿宋_gb2312");
 font.setboldweight(hssffont.boldweight_bold);//字体加粗
// font.setfontheight((short)12);
 font.setfontheightinpoints((short)12);
 cellstyle.setfont(font);
 cell.setcellstyle(cellstyle);
 //合并单元格居中
 sheet.addmergedregion(new cellrangeaddress(rspan, rspan, 0, cspan));
 }
 
 }
 rownum++;
 
 }
 //设置表头
 element thead=root.getchild("thead");
 trs=thead.getchildren("tr");
 for (int i = 0; i < trs.size(); i++) {
 element tr=trs.get(i);
 hssfrow row=sheet.createrow(rownum);
 list<element> ths=tr.getchildren("th");
 for (int j = 0; j <ths.size(); j++) {
 element th=ths.get(j);
 hssfcell cell=row.createcell(j);
 attribute value=th.getattribute("value");
 if (value!=null) {
 string content=value.getvalue();
 cell.setcellvalue(content); 
 
 }
 }
 rownum++;
 }
 
 //设置数据区域样式
 element tbody = root.getchild("tbody");
 element tr=tbody.getchild("tr");
 int repeat=tr.getattribute("repeat").getintvalue();
 list<element> tds=tr.getchildren("td");
 for (int i = 0; i < repeat; i++) {
 hssfrow row=sheet.createrow(rownum);
 for (int j = 0; j < tds.size(); j++) {
 element td=tds.get(j);
 hssfcell cell=row.createcell(j);
 settype(workbook,cell,td);
 }
 }
 rownum++;
 //生成excel导入模板
 file tempfile=new file("e:/"+tempname+".xls");
 tempfile.delete();
 tempfile.createnewfile();
 fileoutputstream fos=fileutils.openoutputstream(tempfile);
 workbook.write(fos);
 fos.close();
 
 } catch (exception e) {
 // todo auto-generated catch block
 e.printstacktrace();
 }
}
 
private static void settype(hssfworkbook workbook, hssfcell cell, element td) {
 attribute typeattr = td.getattribute("type");
 string type = typeattr.getvalue();
 hssfdataformat format = workbook.createdataformat();
 hssfcellstyle cellstyle = workbook.createcellstyle();
 if("numeric".equalsignorecase(type)){
 cell.setcelltype(hssfcell.cell_type_numeric);
 attribute formatattr = td.getattribute("format");
 string formatvalue = formatattr.getvalue();
 formatvalue = stringutils.isnotblank(formatvalue)? formatvalue : "#,##0.00";
 cellstyle.setdataformat(format.getformat(formatvalue));
 }else if("string".equalsignorecase(type)){
 cell.setcellvalue("");
 cell.setcelltype(hssfcell.cell_type_string);
 cellstyle.setdataformat(format.getformat("@"));
 }else if("date".equalsignorecase(type)){
 cell.setcelltype(hssfcell.cell_type_numeric);
 cellstyle.setdataformat(format.getformat("yyyy-m-d"));
 }else if("enum".equalsignorecase(type)){
 cellrangeaddresslist regions = 
 new cellrangeaddresslist(cell.getrowindex(), cell.getrowindex(), 
 cell.getcolumnindex(), cell.getcolumnindex());
 
 attribute enumattr = td.getattribute("format");
 string enumvalue = enumattr.getvalue();
 //加载下拉列表内容
 dvconstraint constraint = 
 dvconstraint.createexplicitlistconstraint(enumvalue.split(","));
 //数据有效性对象
 hssfdatavalidation datavalidation = new hssfdatavalidation(regions, constraint);
 workbook.getsheetat(0).addvalidationdata(datavalidation);
 }
 cell.setcellstyle(cellstyle);
 
}
 
private static void setcolumnwidth(hssfsheet sheet, element colgroup) {
 list<element> cols=colgroup.getchildren("col");//获取col的节点
 for (int i = 0; i < cols.size(); i++) {
 element col=cols.get(i);
 attribute width=col.getattribute("width");//获取每列中的width属性
 string unit = width.getvalue().replaceall("[0-9,\\.]", "");//单位
 string value = width.getvalue().replaceall(unit, "");//数值
 int v=0;
 if(stringutils.isblank(unit) || "px".endswith(unit)){
 v = math.round(float.parsefloat(value) * 37f);
 }else if ("em".endswith(unit)){
 v = math.round(float.parsefloat(value) * 267.5f);
 }//对单位进行判断
 sheet.setcolumnwidth(i, v);
 }
 
}
}

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。