java项目在开发过程中，当遇到需要批量添加表单数据时，如果在页面一个表单一个表单地添加，效率很低，用户体验很差，比如说要添加学生信息，一个班有很多个学生，而学生信息无非就是姓名、性别、年龄等信息，这时，如果把学生信息的Excel文档批量添加，效率很高，省去了很多重复工作。那么java项目怎样读取、解析Excel文档呢？
java项目操作Excel文档，在业界一般就会用到POI或者是JXL，两个工具都比较好用，各有优缺点，本篇文章就使用JXL工具，以导入学生信息，导出湖北省行政区划为例，详细介绍JXL导入导出Excel文档的功能。
使用效果如下：
导入功能：
导出功能：
实现思路：导入时，先读取用户指定的Excel文档，将Excel文档中的每一行数据赋值给实体类中的对应的属性，逐个添加实体对象到数据库，多条添加记得开启事务；导出时，先查询出所有的数据，得到一个包含若干个对象的集合，然后在指定路径创建一个Excel文档，遍历数据集合，将对象逐条写到Excel文档中。
导入导出前端代码：
<body>
<h2 style="margin-left: 50px;margin-top: 50px">学生信息导入</h2>
<button class="layui-btn test" style="margin-left: 50px;margin-top: 10px" lay-data="{url: formTool.getAjaxBaseUrl()+'excel/readAndSaveExcel', accept: 'file'}">一键导入学生信息</button>
<h2 style="margin-left: 50px;margin-top: 50px">湖北省行政区划导出</h2>
<button class="layui-btn" style="margin-left: 50px;margin-top: 10px" id="outPrefecture">一键导出湖北省行政区划信息</button>
<script src="../../../layuiadmin/layui/layui.js" type="text/javascript" charset="utf-8"></script>
<script src="../../content/js/jquery.min.js" type="text/javascript" charset="utf-8"></script>
<script src="../../../layuiadmin/lib/formTools.js" type="text/javascript" charset="UTF-8"></script>
<script type="text/javascript" charset="UTF-8">
var layer;
layui.use(['layer','upload'], function () {
layer=layui.layer;
var upload=layui.upload;
upload.render({
elem: '.test',
exts: 'xls|xlsx',
done: function(res, index, upload){
if(res>0){
layer.msg("成功导入"+res+"条学生信息！",{icon:1});
}else{
layer.msg("导入失败！",{icon:5});
}
}
})
});
$("#outPrefecture").bind('click',function () {
formTool.ajaxCallBack(null,"excel/outPrefectureToExcel",function (result) {
layer.msg(result.msg,{icon:1});
});
});
</script>
</body>导入实体类：
public class EduStudent implements Serializable {
private Integer id;
private Integer gcId;
private Integer studyId;
private String studentName;
private Integer sex;
private String studentNum;
private Date birthDate;
private Date enterTime;
private String memberOneName;
private String memberOneRelation;
private String memberOneTelephone;
private String memberOneWechat;
private String memberOneQq;
private String memberTwoName;
private String memberTwoRelation;
private String memberTwoTelephone;
private String memberTwoWechat;
private String memberTwoQq;
private String keyContact;
private String keyContactTelephone;
private Byte isAvaliable;
private BigDecimal studentMoney;
private Byte paymentSource;
private String openId;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getGcId() {
return gcId;
}
public void setGcId(Integer gcId) {
this.gcId = gcId;
}
public Integer getStudyId() {
return studyId;
}
public void setStudyId(Integer studyId) {
this.studyId = studyId;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName == null ? null : studentName.trim();
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public String getStudentNum() {
return studentNum;
}
public void setStudentNum(String studentNum) {
this.studentNum = studentNum == null ? null : studentNum.trim();
}
@JsonFormat(pattern = "yyyy-MM-dd",timezone="GMT+8")
public Date getBirthDate() {
return birthDate;
}
public void setBirthDate(Date birthDate) {
this.birthDate = birthDate;
}
@JsonFormat(pattern = "yyyy-MM-dd",timezone="GMT+8")
public Date getEnterTime() {
return enterTime;
}
public void setEnterTime(Date enterTime) {
this.enterTime = enterTime;
}
public String getMemberOneName() {
return memberOneName;
}
public void setMemberOneName(String memberOneName) {
this.memberOneName = memberOneName == null ? null : memberOneName.trim();
}
public String getMemberOneRelation() {
return memberOneRelation;
}
public void setMemberOneRelation(String memberOneRelation) {
this.memberOneRelation = memberOneRelation == null ? null : memberOneRelation.trim();
}
public String getMemberOneTelephone() {
return memberOneTelephone;
}
public void setMemberOneTelephone(String memberOneTelephone) {
this.memberOneTelephone = memberOneTelephone == null ? null : memberOneTelephone.trim();
}
public String getMemberOneWechat() {
return memberOneWechat;
}
public void setMemberOneWechat(String memberOneWechat) {
this.memberOneWechat = memberOneWechat == null ? null : memberOneWechat.trim();
}
public String getMemberOneQq() {
return memberOneQq;
}
public void setMemberOneQq(String memberOneQq) {
this.memberOneQq = memberOneQq == null ? null : memberOneQq.trim();
}
public String getMemberTwoName() {
return memberTwoName;
}
public void setMemberTwoName(String memberTwoName) {
this.memberTwoName = memberTwoName == null ? null : memberTwoName.trim();
}
public String getMemberTwoRelation() {
return memberTwoRelation;
}
public void setMemberTwoRelation(String memberTwoRelation) {
this.memberTwoRelation = memberTwoRelation == null ? null : memberTwoRelation.trim();
}
public String getMemberTwoTelephone() {
return memberTwoTelephone;
}
public void setMemberTwoTelephone(String memberTwoTelephone) {
this.memberTwoTelephone = memberTwoTelephone == null ? null : memberTwoTelephone.trim();
}
public String getMemberTwoWechat() {
return memberTwoWechat;
}
public void setMemberTwoWechat(String memberTwoWechat) {
this.memberTwoWechat = memberTwoWechat == null ? null : memberTwoWechat.trim();
}
public String getMemberTwoQq() {
return memberTwoQq;
}
public void setMemberTwoQq(String memberTwoQq) {
this.memberTwoQq = memberTwoQq == null ? null : memberTwoQq.trim();
}
public String getKeyContact() {
return keyContact;
}
public void setKeyContact(String keyContact) {
this.keyContact = keyContact == null ? null : keyContact.trim();
}
public String getKeyContactTelephone() {
return keyContactTelephone;
}
public void setKeyContactTelephone(String keyContactTelephone) {
this.keyContactTelephone = keyContactTelephone == null ? null : keyContactTelephone.trim();
}
public Byte getIsAvaliable() {
return isAvaliable;
}
public void setIsAvaliable(Byte isAvaliable) {
this.isAvaliable = isAvaliable;
}
public BigDecimal getStudentMoney() {
return studentMoney;
}
public void setStudentMoney(BigDecimal studentMoney) {
this.studentMoney = studentMoney;
}
public Byte getPaymentSource() {
return paymentSource;
}
public void setPaymentSource(Byte paymentSource) {
this.paymentSource = paymentSource;
}
public String getOpenId() {
return openId;
}
public void setOpenId(String openId) {
this.openId = openId == null ? null : openId.trim();
}
}导入Controller代码：
/**
* 导入学生信息
*/
@Transactional
@RequestMapping(value = "readAndSaveExcel",method = RequestMethod.POST)
public Integer readAndSaveExcel(MultipartFile file){
Integer responseCode=0;
SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
Workbook book = null;
try {
book = Workbook.getWorkbook(file.getInputStream());
// 获得第一个工作表对象
Sheet sheet = book.getSheet(0);
int rows=sheet.getRows();
int columns=sheet.getColumns();
// 遍历每行每列的单元格
for(int i=3;i<rows;i++){
EduStudent eduStudent=new EduStudent();
for(int j=0;j<columns;j++){
eduStudent.setGcId(100);
eduStudent.setStudentNum(sheet.getCell(0,i).getContents());
eduStudent.setStudentName(sheet.getCell(1,i).getContents());
if(sheet.getCell(2,i).getContents().contains("男")) eduStudent.setSex(1);
else if(sheet.getCell(2,i).getContents().contains("女")) eduStudent.setSex(2);
eduStudent.setBirthDate(simpleDateFormat.parse(sheet.getCell(3,i).getContents()));
eduStudent.setEnterTime(simpleDateFormat.parse(sheet.getCell(4,i).getContents()));
eduStudent.setMemberOneName(sheet.getCell(5,i).getContents());
eduStudent.setMemberOneRelation(sheet.getCell(6,i).getContents());
eduStudent.setMemberOneTelephone(sheet.getCell(7,i).getContents());
eduStudent.setMemberOneWechat(sheet.getCell(8,i).getContents());
eduStudent.setMemberOneQq(sheet.getCell(9,i).getContents());
eduStudent.setMemberTwoName(sheet.getCell(10,i).getContents());
eduStudent.setMemberTwoRelation(sheet.getCell(11,i).getContents());
eduStudent.setMemberTwoTelephone(sheet.getCell(12,i).getContents());
eduStudent.setMemberTwoWechat(sheet.getCell(13,i).getContents());
eduStudent.setMemberTwoQq(sheet.getCell(14,i).getContents());
eduStudent.setKeyContact(sheet.getCell(15,i).getContents());
eduStudent.setKeyContactTelephone(sheet.getCell(16,i).getContents());
}
Integer insert = excelService.insertStudent(eduStudent);
responseCode+=insert;
}
} catch (Exception e) {
e.printStackTrace();
}finally{
if(book!=null){
book.close();
}
}
return responseCode;
}导出Controller代码：
/**
* 导出湖北省行政区划信息
*/
@RequestMapping(value = "outPrefectureToExcel",method = RequestMethod.POST)
public ResponseData<JSON> outPrefectureToExcel(HttpServletRequest request) throws Exception{
ResponseData<JSON> responseData=new ResponseData<>();
String basePath = request.getServletContext().getRealPath("/");
String savePath = basePath+"views"+File.separator+"backend"+File.separator+"test";
WritableWorkbook book = null;
String msg="导出失败！人品问题！";
try {
// 打开文件
book = Workbook.createWorkbook(new File(savePath+File.separator+"prefecture.xls"));
// 生成名为"学生"的工作表，参数0表示这是第一页
WritableSheet sheet = book.createSheet("湖北省行政区划", 0);
List<Prefecture> prefectureList = nodesService.queryAll();
if(prefectureList!=null && !prefectureList.isEmpty()){
for(int i=0; i<prefectureList.size(); i++){
sheet.addCell(new Label(0, i, prefectureList.get(i).getPrefectureId()));
sheet.addCell(new Label(1, i, prefectureList.get(i).getPrefecture()));
}
msg="成功导出了"+prefectureList.size()+"个行政区域！请到【"+savePath+"】路径下查看！";
}
// 写入数据并关闭文件
book.write();
} catch (Exception e) {
e.printStackTrace();
}finally{
if(book!=null){
try {
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return responseData.setMsg(msg);
}
1830
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
