java批量导入导出文件的实例分享(兼容xls,xlsx)
一、介绍
利用java实现文件的导入导出数据库,目前在大部分系统中是比较常见的功能了,今天写个小demo来理解其原理,没接触过的同学也可以看看参考下。
目前我所接触过的导入导出技术主要有poi和ireport,poi主要作为一些数据批量导入数据库,ireport做报表导出。另外还有jxl类似poi的方式,不过貌似很久没跟新了,2007之后的office好像也不支持,这里就不说了。
二、poi使用详解
2.1 什么是apache poi?
apache poi是apache软件基金会的开放源码函式库,poi提供api给java程序对microsoft office格式档案读和写的功能。
2.2 poi的jar包导入
本次讲解使用maven工程,jar包版本使用poi-3.14和poi-ooxml-3.14。目前最新的版本是3.16。因为3.15以后相关api有更新,部分操作可能不一样,大家注意下。
<!-- poi的包 3.15版本后单元格类型获取方式有调整 --> <dependency> <groupid>org.apache.poi</groupid> <artifactid>poi</artifactid> <version>3.14</version> </dependency> <dependency> <groupid>org.apache.poi</groupid> <artifactid>poi-ooxml</artifactid> <version>3.14</version> </dependency>
2.3 poi的api讲解
2.3.1 结构
- hssf - 提供读写microsoft excel格式档案的功能。
- xssf - 提供读写microsoft excel ooxml格式档案的功能。
- hwpf - 提供读写microsoft word格式档案的功能。
- hslf - 提供读写microsoft powerpoint格式档案的功能。
- hdgf - 提供读写microsoft visio格式档案的功能。
2.3.2 对象
本文主要介绍hssf和xssf两种组件,简单的讲hssf用来操作office 2007版本前excel.xls文件,xssf用来操作office 2007版本后的excel.xlsx文件,注意二者的后缀是不一样的。
hssf在org.apache.poi.hssf.usermodel
包中。它实现了workbook 接口,用于excel文件中的.xls格式
常用组件:
hssfworkbook excel的文档对象
hssfsheet excel的表单
hssfrow excel的行
hssfcell excel的格子单元
hssffont excel字体
hssfdataformat 日期格式
hssfheader sheet头
hssffooter sheet尾(只有打印的时候才能看到效果)
样式:
hssfcellstyle cell样式
辅助操作包括:
hssfdateutil 日期
hssfprintsetup 打印
hssferrorconstants 错误信息表
xssf在org.apache.xssf.usemodel
包,并实现workbook接口,用于excel文件中的.xlsx格式
常用组件:
xssfworkbook excel的文档对象
xssfsheet excel的表单
xssfrow excel的行
xssfcell excel的格子单元
xssffont excel字体
xssfdataformat 日期格式
和hssf类似;
2.3.3 两个组件共同的字段类型描述
其实两个组件就是针对excel的两种格式,大部分的操作都是相同的。
单元格类型 描述 cell_type_blank 代表空白单元格 cell_type_boolean 代表布尔单元(true或false) cell_type_error 表示在单元的误差值 cell_type_formula 表示一个单元格公式的结果 cell_type_numeric 表示对一个单元的数字数据 cell_type_string 表示对一个单元串(文本)
2.3.4 操作步骤
以hssf为例,xssf操作相同。
首先,理解一下一个excel的文件的组织形式,一个excel文件对应于一个workbook(hssfworkbook),一个workbook可以有多个sheet(hssfsheet)组成,一个sheet是由多个row(hssfrow)组成,一个row是由多个cell(hssfcell)组成。
1、用hssfworkbook打开或者创建“excel文件对象”
2、用hssfworkbook对象返回或者创建sheet对象
3、用sheet对象返回行对象,用行对象得到cell对象
4、对cell对象读写。
三、代码操作
3.1 效果图
惯例,贴代码前先看效果图
excel文件两种格式各一个:
代码结构:
导入后:(我导入了两遍,没做校验)
导出效果:
3.2 代码详解
这里我以spring+springmvc+mybatis为基础
controller:
package com.allan.controller; import java.util.list; import javax.servlet.http.httpservletresponse; import org.apache.poi.ss.formula.functions.mode; import org.springframework.beans.factory.annotation.autowired; import org.springframework.stereotype.controller; import org.springframework.ui.model; import org.springframework.web.bind.annotation.requestmapping; import org.springframework.web.bind.annotation.requestmethod; import org.springframework.web.bind.annotation.requestparam; import org.springframework.web.bind.annotation.responsebody; import org.springframework.web.multipart.multipartfile; import org.springframework.web.servlet.modelandview; import com.allan.pojo.student; import com.allan.service.studentservice; /** * * @author 小卖铺的老爷爷 * */ @controller public class studentcontroller { @autowired private studentservice studentservice; /** * 批量导入表单数据 * * @param request * @param myfile * @return */ @requestmapping(value="/importexcel",method=requestmethod.post) public string importexcel(@requestparam("myfile") multipartfile myfile) { modelandview modelandview = new modelandview(); try { integer num = studentservice.importexcel(myfile); } catch (exception e) { modelandview.addobject("msg", e.getmessage()); return "index"; } modelandview.addobject("msg", "数据导入成功"); return "index"; } @requestmapping(value="/exportexcel",method=requestmethod.get) public void exportexcel(httpservletresponse response) { try { studentservice.exportexcel(response); } catch (exception e) { e.printstacktrace(); } } }
service
package com.allan.service.impl; import java.io.outputstream; import java.text.decimalformat; import java.text.simpledateformat; import java.util.date; import java.util.list; import javax.servlet.http.httpservletresponse; import org.apache.commons.lang.stringutils; import org.apache.poi.hssf.usermodel.hssfcell; import org.apache.poi.hssf.usermodel.hssfcellstyle; import org.apache.poi.hssf.usermodel.hssfdateutil; 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.cell; import org.apache.poi.ss.usermodel.row; import org.apache.poi.ss.usermodel.sheet; import org.apache.poi.ss.usermodel.workbook; import org.apache.poi.xssf.usermodel.xssfworkbook; import org.springframework.beans.factory.annotation.autowired; import org.springframework.stereotype.service; import org.springframework.web.multipart.multipartfile; import com.allan.mapper.studentmapper; import com.allan.pojo.student; import com.allan.service.studentservice; /** * * @author 小卖铺的老爷爷 * */ @service public class studentserviceimpl implements studentservice{ private final static string xls = "xls"; private final static string xlsx = "xlsx"; @autowired private studentmapper studentmapper; /** * 导入excel,兼容xls和xlsx */ @suppresswarnings("resource") public integer importexcel(multipartfile myfile) throws exception { // 1、用hssfworkbook打开或者创建“excel文件对象” // // 2、用hssfworkbook对象返回或者创建sheet对象 // // 3、用sheet对象返回行对象,用行对象得到cell对象 // // 4、对cell对象读写。 //获得文件名 workbook workbook = null ; string filename = myfile.getoriginalfilename(); if(filename.endswith(xls)){ //2003 workbook = new hssfworkbook(myfile.getinputstream()); }else if(filename.endswith(xlsx)){ //2007 workbook = new xssfworkbook(myfile.getinputstream()); }else{ throw new exception("文件不是excel文件"); } sheet sheet = workbook.getsheet("sheet1"); int rows = sheet.getlastrownum();// 指的行数,一共有多少行+ if(rows==0){ throw new exception("请填写数据"); } for (int i = 1; i <= rows+1; i++) { // 读取左上端单元格 row row = sheet.getrow(i); // 行不为空 if (row != null) { // **读取cell** student student = new student(); //姓名 string name = getcellvalue(row.getcell(0)); student.setname(name); //班级 string classes = getcellvalue(row.getcell(1)); student.setclasses(classes); //分数 string scorestring = getcellvalue(row.getcell(2)); if (!stringutils.isempty(scorestring)) { integer score = integer.parseint(scorestring); student.setscore(score); } //考试时间 simpledateformat sdf = new simpledateformat("yyyy-mm-dd");//小写的mm表示的是分钟 string datestring = getcellvalue(row.getcell(3)); if (!stringutils.isempty(datestring)) { date date=sdf.parse(datestring); student.settime(date); } studentmapper.insert(student); } } return rows-1; } /** * 获得cell内容 * * @param cell * @return */ public string getcellvalue(cell cell) { string value = ""; if (cell != null) { // 以下是判断数据的类型 switch (cell.getcelltype()) { case hssfcell.cell_type_numeric: // 数字 value = cell.getnumericcellvalue() + ""; if (hssfdateutil.iscelldateformatted(cell)) { date date = cell.getdatecellvalue(); if (date != null) { value = new simpledateformat("yyyy-mm-dd").format(date); } else { value = ""; } } else { value = new decimalformat("0").format(cell.getnumericcellvalue()); } break; case hssfcell.cell_type_string: // 字符串 value = cell.getstringcellvalue(); break; case hssfcell.cell_type_boolean: // boolean value = cell.getbooleancellvalue() + ""; break; case hssfcell.cell_type_formula: // 公式 value = cell.getcellformula() + ""; break; case hssfcell.cell_type_blank: // 空值 value = ""; break; case hssfcell.cell_type_error: // 故障 value = "非法字符"; break; default: value = "未知类型"; break; } } return value.trim(); } /** * 导出excel文件 */ public void exportexcel(httpservletresponse response) throws exception { // 第一步,创建一个webbook,对应一个excel文件 hssfworkbook wb = new hssfworkbook(); // 第二步,在webbook中添加一个sheet,对应excel文件中的sheet hssfsheet sheet = wb.createsheet("sheet1"); // 第三步,在sheet中添加表头第0行,注意老版本poi对excel的行数列数有限制short hssfrow row = sheet.createrow(0); // 第四步,创建单元格,并设置值表头 设置表头居中 hssfcellstyle style = wb.createcellstyle(); style.setalignment(hssfcellstyle.align_center); // 创建一个居中格式 hssfcell cell = row.createcell(0); cell.setcellvalue("姓名"); cell.setcellstyle(style); cell = row.createcell(1); cell.setcellvalue("班级"); cell.setcellstyle(style); cell = row.createcell(2); cell.setcellvalue("分数"); cell.setcellstyle(style); cell = row.createcell(3); cell.setcellvalue("时间"); cell.setcellstyle(style); // 第五步,写入实体数据 实际应用中这些数据从数据库得到, list<student> list = studentmapper.selectall(); for (int i = 0; i < list.size(); i++){ row = sheet.createrow(i + 1); student stu = list.get(i); // 第四步,创建单元格,并设置值 row.createcell(0).setcellvalue(stu.getname()); row.createcell(1).setcellvalue(stu.getclasses()); row.createcell(2).setcellvalue(stu.getscore()); cell = row.createcell(3); cell.setcellvalue(new simpledateformat("yyyy-mm-dd").format(stu.gettime())); } //第六步,输出excel文件 outputstream output=response.getoutputstream(); response.reset(); long filename = system.currenttimemillis(); simpledateformat df = new simpledateformat("yyyymmddhhmmss");//设置日期格式 string filename = df.format(new date());// new date()为获取当前系统时间 response.setheader("content-disposition", "attachment; filename="+filename+".xls"); response.setcontenttype("application/msexcel"); wb.write(output); output.close(); } }
3.3 导出文件api补充
大家可以看到上面service的代码只是最基本的导出。
在实际应用中导出的excel文件往往需要阅读和打印的,这就需要对输出的excel文档进行排版和样式的设置,主要操作有合并单元格、设置单元格样式、设置字体样式等。
3.3.1 单元格合并
使用hssfsheet的addmergedregion()
方法
public int addmergedregion(cellrangeaddress region)
参数cellrangeaddress 表示合并的区域,构造方法如下:依次表示起始行,截至行,起始列, 截至列
cellrangeaddress(int firstrow, int lastrow, int firstcol, int lastcol)
3.3.2 设置单元格的行高和列宽
hssfsheet sheet=wb.createsheet(); sheet.setdefaultrowheightinpoints(10);//设置缺省列高sheet.setdefaultcolumnwidth(20);//设置缺省列宽 //设置指定列的列宽,256 * 50这种写法是因为width参数单位是单个字符的256分之一 sheet.setcolumnwidth(cell.getcolumnindex(), 256 * 50);
3.3.3 设置单元格样式
1、创建hssfcellstyle
hssfcellstyle cellstyle=wkb.createcellstyle()
2、设置样式
// 设置单元格的横向和纵向对齐方式,具体参数就不列了,参考hssfcellstyle cellstyle.setalignment(hssfcellstyle.align_justify); cellstyle.setverticalalignment(hssfcellstyle.vertical_center); /* 设置单元格的填充方式,以及前景颜色和背景颜色 三点注意: 1.如果需要前景颜色或背景颜色,一定要指定填充方式,两者顺序无所谓; 2.如果同时存在前景颜色和背景颜色,前景颜色的设置要写在前面; 3.前景颜色不是字体颜色。 */ //设置填充方式(填充图案) cellstyle.setfillpattern(hssfcellstyle.diamonds); //设置前景色 cellstyle.setfillforegroundcolor(hssfcolor.red.index); //设置背景颜色 cellstyle.setfillbackgroundcolor(hssfcolor.light_yellow.index); // 设置单元格底部的边框及其样式和颜色 // 这里仅设置了底边边框,左边框、右边框和顶边框同理可设 cellstyle.setborderbottom(hssfcellstyle.border_slanted_dash_dot); cellstyle.setbottombordercolor(hssfcolor.dark_red.index); //设置日期型数据的显示样式 cellstyle.setdataformat(hssfdataformat.getbuiltinformat("m/d/yy h:mm"));
3、将样式应用于单元格
cell.setcellstyle(cellstyle); //将样式应用到行,但有些样式只对单元格起作用 row.setrowstyle(cellstyle);
3.3.4设置字体样式
1、创建hssffont对象(调用hssfworkbook 的createfont方法)
hssfworkbook wb=new hssfworkbook(); hssffont fontstyle=wb.createfont(); hssfworkbook wb=new hssfworkbook ();
2、设置字体各种样式
//设置字体样式 fontstyle.setfontname("宋体"); //设置字体高度 fontstyle.setfontheightinpoints((short)20); //设置字体颜色 font.setcolor(hssfcolor.blue.index); //设置粗体 fontstyle.setboldweight(hssffont.boldweight_bold); //设置斜体 font.setitalic(true); //设置下划线 font.setunderline(hssffont.u_single);
3、将字体设置到单元格样式
//字体也是单元格格式的一部分,所以从属于hssfcellstyle // 将字体对象赋值给单元格样式对象 cellstyle.setfont(font); // 将单元格样式应用于单元格 cell.setcellstyle(cellstyle);
大家可以看出用poi导出文件还是比较麻烦的,等下次在为大家介绍下irport的方法。
导出的api基本上就是这些,最后也希望上文对大家能有所帮助。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对的支持。