欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

java实现Excel的导入导出

程序员文章站 2024-02-03 12:06:58
本文实例为大家分享了java实现excel导入导出的具体代码,供大家参考,具体内容如下一.excel读写技术区别:二.jxl读写基础代码1.从数据库将数据导出到excel表格public class...

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

一.excel读写技术

java实现Excel的导入导出

区别:

java实现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);
 }
 
}
}

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