java实现Excel的导入导出
程序员文章站
2024-02-03 12:06:58
本文实例为大家分享了java实现excel导入导出的具体代码,供大家参考,具体内容如下一.excel读写技术区别:二.jxl读写基础代码1.从数据库将数据导出到excel表格public class...
本文实例为大家分享了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); } } }
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。
上一篇: 通过php添加xml文档内容的方法