alumnus system - jxl导入导出excel java

虽然没有留下什么,但是我确确实实来过这里

--- xingyunpi

用到的包:jxl.jar

下载地址: http://www.java2s.com/Code/Jar/JKL/Downloadjxljar.htm

查了一些资料,发现对数据进行导入导出操作的也就是jxl.jar用的比较多了,然后整理了一下实现过程,下面记录一下:

首先,新建一个javaproject,导入要用的jar包,即jxl.jar

其实功能很简单,实现过程很是很简单,直接把找到的并修改的代码记录下来吧:

哦,在粘出来代码前,要做一下简单的介绍啦.

首先,其实我们用到的数据的导入导出,都是和数据库中的数据联系,所以,我选择一个Person作为存入数据的内容.这样,就对应了数据表映射成持久化类的样子,也就是在后面与数据库连接打下基础了.

DealWithExcel.java这个类,主要定义了两个方法,一个是导入,一个是导出,参数看一下就明白了.

好啦,下面是代码:

Person.java

package com.xingyun.pi; public class Person { private String name; private String nickname; private String power; private String wit; private String polity; private String charm; private String story; public Person(){ } public Person(String name,String nickname,String power,String wit,String polity,String charm,String story){ this.name = name; this.nickname = nickname; this.power = power; this.wit = wit; this.polity = polity; this.charm = charm; this.story = story; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getNickname() { return nickname; } public void setNickname(String nickname) { this.nickname = nickname; } public String getPower() { return power; } public void setPower(String power) { this.power = power; } public String getWit() { return wit; } public void setWit(String wit) { this.wit = wit; } public String getPolity() { return polity; } public void setPolity(String polity) { this.polity = polity; } public String getCharm() { return charm; } public void setCharm(String charm) { this.charm = charm; } public String getStory() { return story; } public void setStory(String story) { this.story = story; } }
DealWithExcel.java

package com.xingyun.pi; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.Vector; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.Colour; import jxl.format.UnderlineStyle; import jxl.format.VerticalAlignment; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException; public class DealWithExcel { /** *//** * 导出数据为XLS格式 * @param fileName 文件的名称，可以设为绝对路径，也可以设为相对路径 * @param content 数据的内容 */ public static void exportExcel(String fileName, Vector<Person> content) { WritableWorkbook wwb; FileOutputStream fos; try { fos = new FileOutputStream(fileName); wwb = Workbook.createWorkbook(fos); WritableSheet ws = wwb.createSheet("三国志武将列表", 10); // 创建一个工作表 // 设置单元格的文字格式 WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLUE); WritableCellFormat wcf = new WritableCellFormat(wf); wcf.setVerticalAlignment(VerticalAlignment.CENTRE); wcf.setAlignment(Alignment.CENTRE); ws.setRowView(1, 500); // 填充数据的内容 Person[] p = new Person[content.size()]; for (int i = 0; i < content.size(); i++){ p[i] = (Person)content.get(i); ws.addCell(new Label(0, i, p[i].getName(), wcf)); ws.addCell(new Label(1, i, p[i].getNickname(), wcf)); ws.addCell(new Label(2, i, p[i].getPower(), wcf)); ws.addCell(new Label(3, i, p[i].getWit(), wcf)); ws.addCell(new Label(4, i, p[i].getPolity(), wcf)); ws.addCell(new Label(5, i, p[i].getCharm(), wcf)); ws.addCell(new Label(6, i, p[i].getStory(), wcf)); if(i == 0) wcf = new WritableCellFormat(); } wwb.write(); wwb.close(); } catch (IOException e){ } catch (RowsExceededException e){ } catch (WriteException e){} } /** *//** * 从Excel文件里读取数据保存到Vector里 * @param fileName Excel文件的名称 * @return Vector对象,里面包含从Excel文件里获取到的数据 */ public static Vector<Person> importExcel(String fileName){ Vector<Person> v = new Vector<Person>(); try { Workbook book = Workbook.getWorkbook(new File(fileName)); Sheet sheet = book.getSheet(0); // 获得第一个工作表对象 int rows = sheet.getRows(); for(int i = 0; i < rows; i++) { Cell [] cell = sheet.getRow(i); if(cell.length == 0) continue; Person p = new Person(); p.setName(sheet.getCell(0, i).getContents()); p.setNickname(sheet.getCell(1, i).getContents()); p.setPower(sheet.getCell(2, i).getContents()); p.setWit(sheet.getCell(3, i).getContents()); p.setPolity(sheet.getCell(4, i).getContents()); p.setCharm(sheet.getCell(5, i).getContents()); p.setStory(sheet.getCell(6, i).getContents()); v.add(p); } book.close(); }catch(Exception e) {} return v; } public static void main(String [] args){ String fileName = "C:\\test.xls"; String fileNameNew = "C:\\testNew.xls"; Person p0 = new Person("姓名","字","武力","智力","政治","魅力","英雄事迹"); Person p1 = new Person("赵云","子龙","98","84","83","87","单骑救主!!!"); Person p2 = new Person("马超","孟起","98","62","40","88","杀得曹操割须弃袍!!!"); Person p3 = new Person("诸葛亮","孔明","55","100","92","93","死后木偶退兵，锦囊杀魏延!!!"); Vector<Person> v = new Vector<Person>(); v.add(p0); v.add(p1); v.add(p2); v.add(p3); exportExcel(fileName, v); System.out.println("成功导出数据到Excel文件(" + fileName + ")了!!!"); Vector<Person> vector = importExcel(fileName); System.out.println("成功从Excel文件(" + fileName + ")导入数据!!!"); for(int i=1;i<vector.size();i++){ System.out.println(vector.get(i).getName()); } // // exportExcel(fileNameNew, vector); // System.out.println("成功将" + fileName + "里的数据手复制到" + fileNameNew + "中!!!"); } } 这是人家搞的,我觉得很好用很好用,下面俺就来试着去实现一个什么功能呢,是这样的:

对于导出:

导出数据其实是没什么可以变化的,唯一一点就是不会再用Main方法去自己定义几个Person,而是把Person从数据库中读取出来放到vector中,然后调用exporExcel方法就行了.

对于导入:

导入数据的时候,一般我们要让用户去选择文件,然后点击"导入",之后再读取excel中的内容,最后才导入数据,这样的话,我们应该再加点功能

嗯嗯,我也就主要做了这点工作,下面也记录一下:

首先,将DealWithExcel.java的main方法去掉,作为一个工具类:

(介个介个,我又用拼音了,嘿嘿,Alumnus.java的属性也多了些,见谅见谅~)

package com.alumnus.util; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.util.Vector; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.Colour; import jxl.format.UnderlineStyle; import jxl.format.VerticalAlignment; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException; import com.alumnus.data.Alumnus; public class Excel { /** *//** * 导出数据为XLS格式 * @param fileName 文件的名称，可以设为绝对路径，也可以设为相对路径 * @param content 数据的内容 */ public static void exportExcel(String fileName, Vector<Alumnus> content) { WritableWorkbook wwb; FileOutputStream fos; try { fos = new FileOutputStream(fileName); wwb = Workbook.createWorkbook(fos); WritableSheet ws = wwb.createSheet("列表", 10); // 创建一个工作表 // 设置单元格的文字格式 WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLUE); WritableCellFormat wcf = new WritableCellFormat(wf); wcf.setVerticalAlignment(VerticalAlignment.CENTRE); wcf.setAlignment(Alignment.CENTRE); ws.setRowView(1, 500); // 填充数据的内容 Alumnus[] p = new Alumnus[content.size()]; for (int i = 0; i < content.size(); i++){ p[i] = (Alumnus)content.get(i); ws.addCell(new Label(0, i, p[i].getName(), wcf)); ws.addCell(new Label(1, i, p[i].getGender(), wcf)); ws.addCell(new Label(2, i, p[i].getBirth(), wcf)); ws.addCell(new Label(3, i, p[i].getJiguan(), wcf)); ws.addCell(new Label(4, i, p[i].getShouji(), wcf)); ws.addCell(new Label(5, i, p[i].getEmail(), wcf)); ws.addCell(new Label(6, i, p[i].getDianhua(), wcf)); ws.addCell(new Label(7, i, p[i].getXiantongxun(), wcf)); ws.addCell(new Label(8, i, p[i].getYoubian(), wcf)); ws.addCell(new Label(9, i, p[i].getXueli(), wcf)); ws.addCell(new Label(10, i, p[i].getXueyuan(), wcf)); ws.addCell(new Label(11, i, p[i].getXibie(), wcf)); ws.addCell(new Label(12, i, p[i].getMajor(), wcf)); ws.addCell(new Label(13, i, p[i].getRuxuetime(), wcf)); ws.addCell(new Label(14, i, p[i].getGotime(), wcf)); ws.addCell(new Label(15, i, p[i].getGongzuochengshi(), wcf)); ws.addCell(new Label(16, i, p[i].getDaiwei(), wcf)); ws.addCell(new Label(17, i, p[i].getDanweidizhi(), wcf)); ws.addCell(new Label(18, i, p[i].getDanweiyoubian(), wcf)); ws.addCell(new Label(19, i, p[i].getGongzuodianhua(), wcf)); ws.addCell(new Label(20, i, p[i].getGongzuochuanzhen(), wcf)); ws.addCell(new Label(21, i, p[i].getDanweixingzhi(), wcf)); ws.addCell(new Label(22, i, p[i].getZhiwu(), wcf)); ws.addCell(new Label(23, i, p[i].getZhicheng(), wcf)); ws.addCell(new Label(24, i, p[i].getZhiji(), wcf)); ws.addCell(new Label(25, i, p[i].getBeizhu1(), wcf)); ws.addCell(new Label(26, i, p[i].getBeizhu2(), wcf)); ws.addCell(new Label(27, i, p[i].getQita(), wcf)); ws.addCell(new Label(28, i, p[i].getUrl(), wcf)); ws.addCell(new Label(29, i, p[i].getLastName(), wcf)); ws.addCell(new Label(30, i, p[i].getFirstName(), wcf)); if(i == 0) wcf = new WritableCellFormat(); } wwb.write(); wwb.close(); } catch (IOException e){ } catch (RowsExceededException e){ } catch (WriteException e){} } /** *//** * 从Excel文件里读取数据保存到Vector里 * @param fileName Excel文件的名称 * @return Vector对象,里面包含从Excel文件里获取到的数据 */ public static Vector<Alumnus> importExcel(String fileName){ Vector<Alumnus> v = new Vector<Alumnus>(); try { Workbook book = Workbook.getWorkbook(new File(fileName)); Sheet sheet = book.getSheet(0); // 获得第一个工作表对象 int rows = sheet.getRows(); for(int i = 0; i < rows; i++) { Cell [] cell = sheet.getRow(i); if(cell.length == 0) continue; Alumnus p = new Alumnus(); p.setName(sheet.getCell(0, i).getContents()); p.setGender(sheet.getCell(1, i).getContents()); p.setBirth(sheet.getCell(2, i).getContents()); p.setJiguan(sheet.getCell(3, i).getContents()); p.setShouji(sheet.getCell(4, i).getContents()); p.setEmail(sheet.getCell(5, i).getContents()); p.setDianhua(sheet.getCell(6, i).getContents()); p.setXiantongxun(sheet.getCell(7, i).getContents()); p.setYoubian(sheet.getCell(8, i).getContents()); p.setXueli(sheet.getCell(9, i).getContents()); p.setXueyuan(sheet.getCell(10, i).getContents()); p.setXibie(sheet.getCell(11, i).getContents()); p.setMajor(sheet.getCell(12, i).getContents()); p.setRuxuetime(sheet.getCell(13, i).getContents()); p.setGotime(sheet.getCell(14, i).getContents()); p.setGongzuochengshi(sheet.getCell(15, i).getContents()); p.setDaiwei(sheet.getCell(16, i).getContents()); p.setDanweidizhi(sheet.getCell(17, i).getContents()); p.setDanweiyoubian(sheet.getCell(18, i).getContents()); p.setGongzuodianhua(sheet.getCell(19, i).getContents()); p.setGongzuochuanzhen(sheet.getCell(20, i).getContents()); p.setDanweixingzhi(sheet.getCell(21, i).getContents()); p.setZhiwu(sheet.getCell(22, i).getContents()); p.setZhicheng(sheet.getCell(23, i).getContents()); p.setZhiji(sheet.getCell(24, i).getContents()); p.setBeizhu1(sheet.getCell(25, i).getContents()); p.setBeizhu2(sheet.getCell(26, i).getContents()); p.setQita(sheet.getCell(27, i).getContents()); p.setUrl(sheet.getCell(28, i).getContents()); p.setLastName(sheet.getCell(29, i).getContents()); p.setFirstName(sheet.getCell(30, i).getContents()); v.add(p); } book.close(); }catch(Exception e) {} return v; } } 然后,在前台(jsp)页面中设置一下,搞一个文件的上传:

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@ taglib uri="/struts-tags" prefix="s"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <% if(session.getAttribute("username")==null){ response.sendRedirect("index.jsp"); } %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>欢迎</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <!-- <link rel="stylesheet" type="text/css" href="styles.css"> --> </head> <body bgcolor="#f0f0f0"> <form name="formimport" method="post" action="ImportFromExcel.action" enctype="multipart/form-data"> 请选择excel文件:<input type="file" name="upload" id="upload"> <input type="submit" name="button4" id="button4" value="导入"> </form> </body>
接下来是ImportFromExcel.java:

代码之前要简单说明一下:为了将上传的文档备份,所以,在导入数据库之前要先将文件上传到服务器,然后再导入数据库,上传用到了struts2的上传:

(介个介个,还是拼音,还是字段比较多,嘿嘿)

package com.alumnus.action; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Vector; import org.apache.struts2.ServletActionContext; import com.alumnus.data.Alumnus; import com.alumnus.data.AlumnusDAO; import com.alumnus.util.Excel; import com.opensymphony.xwork2.ActionSupport; public class ImportFromExcel extends ActionSupport { File upload; String uploadContentType; String uploadFileName; private String savePath; private String name; private String gender; private String birth; private String jiguan; private String shouji; private String email; private String dianhua; private String xiantongxun; private String youbian; private String xueli; private String xueyuan; private String xibie; private String major; private String ruxuetime; private String gotime; private String gongzuochengshi; private String daiwei; private String danweidizhi; private String danweiyoubian; private String gongzuodianhua; private String gongzuochuanzhen; private String danweixingzhi; private String zhiwu; private String zhicheng; private String zhiji; private String beizhu1; private String beizhu2; private String qita; private String url; private String lastname; private String firstname; public String execute() throws Exception{ /** * 文件上传工作 */ String doc = getFilename(getUploadFileName()); String filename =getTime() + "." + doc; FileOutputStream fos = new FileOutputStream(getSavePath() + "\\" + filename); FileInputStream fis = new FileInputStream(getUpload()); byte[] buffer = new byte[1024]; int len = 0; while ((len = fis.read(buffer)) > 0) { fos.write(buffer , 0 , len); } String path = ServletActionContext.getServletContext().getRealPath("/upload"); System.out.println(path); Vector<Alumnus> v = Excel.importExcel("C:\\Tomcat 6.0\\webapps\\alumnus\\upload\\" + filename); for(int i=1;i<v.size();i++){ Alumnus alu = v.get(i); name = alu.getName(); gender = alu.getGender(); birth = alu.getBirth(); jiguan = alu.getJiguan(); shouji = alu.getShouji(); email = alu.getEmail(); dianhua = alu.getDianhua(); xiantongxun = alu.getXiantongxun(); youbian = alu.getYoubian(); xueli = alu.getXueli(); xueyuan = alu.getXueyuan(); xibie = alu.getXibie(); major = alu.getMajor(); ruxuetime = alu.getRuxuetime(); gotime = alu.getGotime(); gongzuochengshi = alu.getGongzuochengshi(); daiwei = alu.getDaiwei(); danweidizhi = alu.getDanweidizhi(); danweiyoubian = alu.getDanweiyoubian(); gongzuodianhua = alu.getGongzuodianhua(); gongzuochuanzhen = alu.getGongzuochuanzhen(); danweixingzhi = alu.getDanweixingzhi(); zhiwu = alu.getZhiwu(); zhicheng = alu.getZhicheng(); zhiji = alu.getZhiji(); beizhu1 = alu.getBeizhu1(); beizhu2 = alu.getBeizhu2(); qita = alu.getQita(); url = alu.getUrl(); lastname = alu.getLastName(); firstname = alu.getFirstName(); Alumnus a = new Alumnus(name, gender,birth,jiguan ,shouji,email,dianhua,xiantongxun,youbian,xueli,xueyuan,xibie, major, ruxuetime,gotime,gongzuochengshi,daiwei,danweidizhi,danweiyoubian,gongzuodianhua, gongzuochuanzhen,danweixingzhi,zhiwu,zhicheng,zhiji, beizhu1,beizhu2,qita,url,lastname,firstname); AlumnusDAO.save(a); } return SUCCESS; } String getTime(){ /** * 获取时间来定义文件名称 * @return String as the name of the file */ SimpleDateFormat formatter_f = new SimpleDateFormat("yyyyMMddHHmmss"); Date now = new Date(); String time = formatter_f.format(now); return time; } String getTrueTime(){ /** * 获取当前时间 * @return String time */ SimpleDateFormat formatter_f = new SimpleDateFormat("yyyy-MM-dd"); Date now = new Date(); String time = formatter_f.format(now); return time; } String getFilename(String name){ /** * 获取文件名的后缀 * @return String */ int i = name.lastIndexOf(".")+1; return name.substring(i,name.length()); } //接受依赖注入的方法 public void setSavePath(String value) { this.savePath = value; } @SuppressWarnings("deprecation") private String getSavePath() throws Exception { return ServletActionContext.getRequest().getRealPath(savePath); } public void setUpload(File upload) { this.upload = upload; } public void setUploadContentType(String uploadContentType) { this.uploadContentType = uploadContentType; } public void setUploadFileName(String uploadFileName) { this.uploadFileName = uploadFileName; } public File getUpload() { return (this.upload); } public String getUploadContentType() { return (this.uploadContentType); } public String getUploadFileName() { return (this.uploadFileName); } }
struts2.xml配置文件:

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN" "http://struts.apache.org/dtds/struts-2.1.dtd"> <struts> <package namespace="/" name="default" extends="struts-default"> <!-- 从excel导入 --> <action name="ImportFromExcel" class="com.alumnus.action.ImportFromExcel" > <param name="savePath">/upload</param> <result name="success">/test.jsp</result> </action> </package> </struts>

当然,还有Alumnus.java,这个我就不写了,反正就是那一群用汉语命名的属性,还有get/set方法.

其实这样看来,不是很麻烦的,但是,这个思维过程还是要有那么一段时间一段时间的...

come on baby~

查了一些资料,发现对数据进行导入导出操作的也就是jxl.jar用的比较多了,然后整理了一下实现过程,下面记录一下:

首先,新建一个javaproject,导入要用的jar包,即jxl.jar

其实功能很简单,实现过程很是很简单,直接把找到的并修改的代码记录下来吧:

哦,在粘出来代码前,要做一下简单的介绍啦.

首先,其实我们用到的数据的导入导出,都是和数据库中的数据联系,所以,我选择一个Person作为存入数据的内容.这样,就对应了数据表映射成持久化类的样子,也就是在后面与数据库连接打下基础了.

DealWithExcel.java这个类,主要定义了两个方法,一个是导入,一个是导出,参数看一下就明白了.

好啦,下面是代码:

Person.java

package com.xingyun.pi; public class Person { private String name; private String nickname; private String power; private String wit; private String polity; private String charm; private String story; public Person(){ } public Person(String name,String nickname,String power,String wit,String polity,String charm,String story){ this.name = name; this.nickname = nickname; this.power = power; this.wit = wit; this.polity = polity; this.charm = charm; this.story = story; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getNickname() { return nickname; } public void setNickname(String nickname) { this.nickname = nickname; } public String getPower() { return power; } public void setPower(String power) { this.power = power; } public String getWit() { return wit; } public void setWit(String wit) { this.wit = wit; } public String getPolity() { return polity; } public void setPolity(String polity) { this.polity = polity; } public String getCharm() { return charm; } public void setCharm(String charm) { this.charm = charm; } public String getStory() { return story; } public void setStory(String story) { this.story = story; } }
DealWithExcel.java

package com.xingyun.pi; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.Vector; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.Colour; import jxl.format.UnderlineStyle; import jxl.format.VerticalAlignment; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException; public class DealWithExcel { /** *//** * 导出数据为XLS格式 * @param fileName 文件的名称，可以设为绝对路径，也可以设为相对路径 * @param content 数据的内容 */ public static void exportExcel(String fileName, Vector<Person> content) { WritableWorkbook wwb; FileOutputStream fos; try { fos = new FileOutputStream(fileName); wwb = Workbook.createWorkbook(fos); WritableSheet ws = wwb.createSheet("三国志武将列表", 10); // 创建一个工作表 // 设置单元格的文字格式 WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLUE); WritableCellFormat wcf = new WritableCellFormat(wf); wcf.setVerticalAlignment(VerticalAlignment.CENTRE); wcf.setAlignment(Alignment.CENTRE); ws.setRowView(1, 500); // 填充数据的内容 Person[] p = new Person[content.size()]; for (int i = 0; i < content.size(); i++){ p[i] = (Person)content.get(i); ws.addCell(new Label(0, i, p[i].getName(), wcf)); ws.addCell(new Label(1, i, p[i].getNickname(), wcf)); ws.addCell(new Label(2, i, p[i].getPower(), wcf)); ws.addCell(new Label(3, i, p[i].getWit(), wcf)); ws.addCell(new Label(4, i, p[i].getPolity(), wcf)); ws.addCell(new Label(5, i, p[i].getCharm(), wcf)); ws.addCell(new Label(6, i, p[i].getStory(), wcf)); if(i == 0) wcf = new WritableCellFormat(); } wwb.write(); wwb.close(); } catch (IOException e){ } catch (RowsExceededException e){ } catch (WriteException e){} } /** *//** * 从Excel文件里读取数据保存到Vector里 * @param fileName Excel文件的名称 * @return Vector对象,里面包含从Excel文件里获取到的数据 */ public static Vector<Person> importExcel(String fileName){ Vector<Person> v = new Vector<Person>(); try { Workbook book = Workbook.getWorkbook(new File(fileName)); Sheet sheet = book.getSheet(0); // 获得第一个工作表对象 int rows = sheet.getRows(); for(int i = 0; i < rows; i++) { Cell [] cell = sheet.getRow(i); if(cell.length == 0) continue; Person p = new Person(); p.setName(sheet.getCell(0, i).getContents()); p.setNickname(sheet.getCell(1, i).getContents()); p.setPower(sheet.getCell(2, i).getContents()); p.setWit(sheet.getCell(3, i).getContents()); p.setPolity(sheet.getCell(4, i).getContents()); p.setCharm(sheet.getCell(5, i).getContents()); p.setStory(sheet.getCell(6, i).getContents()); v.add(p); } book.close(); }catch(Exception e) {} return v; } public static void main(String [] args){ String fileName = "C:\\test.xls"; String fileNameNew = "C:\\testNew.xls"; Person p0 = new Person("姓名","字","武力","智力","政治","魅力","英雄事迹"); Person p1 = new Person("赵云","子龙","98","84","83","87","单骑救主!!!"); Person p2 = new Person("马超","孟起","98","62","40","88","杀得曹操割须弃袍!!!"); Person p3 = new Person("诸葛亮","孔明","55","100","92","93","死后木偶退兵，锦囊杀魏延!!!"); Vector<Person> v = new Vector<Person>(); v.add(p0); v.add(p1); v.add(p2); v.add(p3); exportExcel(fileName, v); System.out.println("成功导出数据到Excel文件(" + fileName + ")了!!!"); Vector<Person> vector = importExcel(fileName); System.out.println("成功从Excel文件(" + fileName + ")导入数据!!!"); for(int i=1;i<vector.size();i++){ System.out.println(vector.get(i).getName()); } // // exportExcel(fileNameNew, vector); // System.out.println("成功将" + fileName + "里的数据手复制到" + fileNameNew + "中!!!"); } } 这是人家搞的,我觉得很好用很好用,下面俺就来试着去实现一个什么功能呢,是这样的:

对于导出:

导出数据其实是没什么可以变化的,唯一一点就是不会再用Main方法去自己定义几个Person,而是把Person从数据库中读取出来放到vector中,然后调用exporExcel方法就行了.

对于导入:

导入数据的时候,一般我们要让用户去选择文件,然后点击"导入",之后再读取excel中的内容,最后才导入数据,这样的话,我们应该再加点功能,嗯嗯,我也就主要做了这点工作,下面也记录一下:

首先,将DealWithExcel.java的main方法去掉,作为一个工具类:

(介个介个,我又用拼音了,嘿嘿,Alumnus.java的属性也多了些,见谅见谅~)

package com.alumnus.util; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.util.Vector; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.Colour; import jxl.format.UnderlineStyle; import jxl.format.VerticalAlignment; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException; import com.alumnus.data.Alumnus; public class Excel { /** *//** * 导出数据为XLS格式 * @param fileName 文件的名称，可以设为绝对路径，也可以设为相对路径 * @param content 数据的内容 */ public static void exportExcel(String fileName, Vector<Alumnus> content) { WritableWorkbook wwb; FileOutputStream fos; try { fos = new FileOutputStream(fileName); wwb = Workbook.createWorkbook(fos); WritableSheet ws = wwb.createSheet("列表", 10); // 创建一个工作表 // 设置单元格的文字格式 WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLUE); WritableCellFormat wcf = new WritableCellFormat(wf); wcf.setVerticalAlignment(VerticalAlignment.CENTRE); wcf.setAlignment(Alignment.CENTRE); ws.setRowView(1, 500); // 填充数据的内容 Alumnus[] p = new Alumnus[content.size()]; for (int i = 0; i < content.size(); i++){ p[i] = (Alumnus)content.get(i); ws.addCell(new Label(0, i, p[i].getName(), wcf)); ws.addCell(new Label(1, i, p[i].getGender(), wcf)); ws.addCell(new Label(2, i, p[i].getBirth(), wcf)); ws.addCell(new Label(3, i, p[i].getJiguan(), wcf)); ws.addCell(new Label(4, i, p[i].getShouji(), wcf)); ws.addCell(new Label(5, i, p[i].getEmail(), wcf)); ws.addCell(new Label(6, i, p[i].getDianhua(), wcf)); ws.addCell(new Label(7, i, p[i].getXiantongxun(), wcf)); ws.addCell(new Label(8, i, p[i].getYoubian(), wcf)); ws.addCell(new Label(9, i, p[i].getXueli(), wcf)); ws.addCell(new Label(10, i, p[i].getXueyuan(), wcf)); ws.addCell(new Label(11, i, p[i].getXibie(), wcf)); ws.addCell(new Label(12, i, p[i].getMajor(), wcf)); ws.addCell(new Label(13, i, p[i].getRuxuetime(), wcf)); ws.addCell(new Label(14, i, p[i].getGotime(), wcf)); ws.addCell(new Label(15, i, p[i].getGongzuochengshi(), wcf)); ws.addCell(new Label(16, i, p[i].getDaiwei(), wcf)); ws.addCell(new Label(17, i, p[i].getDanweidizhi(), wcf)); ws.addCell(new Label(18, i, p[i].getDanweiyoubian(), wcf)); ws.addCell(new Label(19, i, p[i].getGongzuodianhua(), wcf)); ws.addCell(new Label(20, i, p[i].getGongzuochuanzhen(), wcf)); ws.addCell(new Label(21, i, p[i].getDanweixingzhi(), wcf)); ws.addCell(new Label(22, i, p[i].getZhiwu(), wcf)); ws.addCell(new Label(23, i, p[i].getZhicheng(), wcf)); ws.addCell(new Label(24, i, p[i].getZhiji(), wcf)); ws.addCell(new Label(25, i, p[i].getBeizhu1(), wcf)); ws.addCell(new Label(26, i, p[i].getBeizhu2(), wcf)); ws.addCell(new Label(27, i, p[i].getQita(), wcf)); ws.addCell(new Label(28, i, p[i].getUrl(), wcf)); ws.addCell(new Label(29, i, p[i].getLastName(), wcf)); ws.addCell(new Label(30, i, p[i].getFirstName(), wcf)); if(i == 0) wcf = new WritableCellFormat(); } wwb.write(); wwb.close(); } catch (IOException e){ } catch (RowsExceededException e){ } catch (WriteException e){} } /** *//** * 从Excel文件里读取数据保存到Vector里 * @param fileName Excel文件的名称 * @return Vector对象,里面包含从Excel文件里获取到的数据 */ public static Vector<Alumnus> importExcel(String fileName){ Vector<Alumnus> v = new Vector<Alumnus>(); try { Workbook book = Workbook.getWorkbook(new File(fileName)); Sheet sheet = book.getSheet(0); // 获得第一个工作表对象 int rows = sheet.getRows(); for(int i = 0; i < rows; i++) { Cell [] cell = sheet.getRow(i); if(cell.length == 0) continue; Alumnus p = new Alumnus(); p.setName(sheet.getCell(0, i).getContents()); p.setGender(sheet.getCell(1, i).getContents()); p.setBirth(sheet.getCell(2, i).getContents()); p.setJiguan(sheet.getCell(3, i).getContents()); p.setShouji(sheet.getCell(4, i).getContents()); p.setEmail(sheet.getCell(5, i).getContents()); p.setDianhua(sheet.getCell(6, i).getContents()); p.setXiantongxun(sheet.getCell(7, i).getContents()); p.setYoubian(sheet.getCell(8, i).getContents()); p.setXueli(sheet.getCell(9, i).getContents()); p.setXueyuan(sheet.getCell(10, i).getContents()); p.setXibie(sheet.getCell(11, i).getContents()); p.setMajor(sheet.getCell(12, i).getContents()); p.setRuxuetime(sheet.getCell(13, i).getContents()); p.setGotime(sheet.getCell(14, i).getContents()); p.setGongzuochengshi(sheet.getCell(15, i).getContents()); p.setDaiwei(sheet.getCell(16, i).getContents()); p.setDanweidizhi(sheet.getCell(17, i).getContents()); p.setDanweiyoubian(sheet.getCell(18, i).getContents()); p.setGongzuodianhua(sheet.getCell(19, i).getContents()); p.setGongzuochuanzhen(sheet.getCell(20, i).getContents()); p.setDanweixingzhi(sheet.getCell(21, i).getContents()); p.setZhiwu(sheet.getCell(22, i).getContents()); p.setZhicheng(sheet.getCell(23, i).getContents()); p.setZhiji(sheet.getCell(24, i).getContents()); p.setBeizhu1(sheet.getCell(25, i).getContents()); p.setBeizhu2(sheet.getCell(26, i).getContents()); p.setQita(sheet.getCell(27, i).getContents()); p.setUrl(sheet.getCell(28, i).getContents()); p.setLastName(sheet.getCell(29, i).getContents()); p.setFirstName(sheet.getCell(30, i).getContents()); v.add(p); } book.close(); }catch(Exception e) {} return v; } } 然后,在前台(jsp)页面中设置一下,搞一个文件的上传:

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@ taglib uri="/struts-tags" prefix="s"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>欢迎</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <!-- <link rel="stylesheet" type="text/css" href="styles.css"> --> </head> <body bgcolor="#f0f0f0"> <form name="formimport" method="post" action="ImportFromExcel.action" enctype="multipart/form-data"> 请选择excel文件:<input type="file" name="upload" id="upload"> <input type="submit" name="button4" id="button4" value="导入"> </form> </body>
接下来是ImportFromExcel.java:

代码之前要简单说明一下:为了将上传的文档备份,所以,在导入数据库之前要先将文件上传到服务器,然后再导入数据库,上传用到了struts2的上传:

(介个介个,还是拼音,还是字段比较多,嘿嘿)

package com.alumnus.action; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Vector; import org.apache.struts2.ServletActionContext; import com.alumnus.data.Alumnus; import com.alumnus.data.AlumnusDAO; import com.alumnus.util.Excel; import com.opensymphony.xwork2.ActionSupport; public class ImportFromExcel extends ActionSupport { File upload; String uploadContentType; String uploadFileName; private String savePath; private String name; private String gender; private String birth; private String jiguan; private String shouji; private String email; private String dianhua; private String xiantongxun; private String youbian; private String xueli; private String xueyuan; private String xibie; private String major; private String ruxuetime; private String gotime; private String gongzuochengshi; private String daiwei; private String danweidizhi; private String danweiyoubian; private String gongzuodianhua; private String gongzuochuanzhen; private String danweixingzhi; private String zhiwu; private String zhicheng; private String zhiji; private String beizhu1; private String beizhu2; private String qita; private String url; private String lastname; private String firstname; public String execute() throws Exception{ /** * 文件上传工作 */ String doc = getFilename(getUploadFileName()); String filename =getTime() + "." + doc; FileOutputStream fos = new FileOutputStream(getSavePath() + "\\" + filename); FileInputStream fis = new FileInputStream(getUpload()); byte[] buffer = new byte[1024]; int len = 0; while ((len = fis.read(buffer)) > 0) { fos.write(buffer , 0 , len); } String path = ServletActionContext.getServletContext().getRealPath("/upload"); System.out.println(path); Vector<Alumnus> v = Excel.importExcel("C:\\Tomcat 6.0\\webapps\\alumnus\\upload\\" + filename); for(int i=1;i<v.size();i++){ Alumnus alu = v.get(i); name = alu.getName(); gender = alu.getGender(); birth = alu.getBirth(); jiguan = alu.getJiguan(); shouji = alu.getShouji(); email = alu.getEmail(); dianhua = alu.getDianhua(); xiantongxun = alu.getXiantongxun(); youbian = alu.getYoubian(); xueli = alu.getXueli(); xueyuan = alu.getXueyuan(); xibie = alu.getXibie(); major = alu.getMajor(); ruxuetime = alu.getRuxuetime(); gotime = alu.getGotime(); gongzuochengshi = alu.getGongzuochengshi(); daiwei = alu.getDaiwei(); danweidizhi = alu.getDanweidizhi(); danweiyoubian = alu.getDanweiyoubian(); gongzuodianhua = alu.getGongzuodianhua(); gongzuochuanzhen = alu.getGongzuochuanzhen(); danweixingzhi = alu.getDanweixingzhi(); zhiwu = alu.getZhiwu(); zhicheng = alu.getZhicheng(); zhiji = alu.getZhiji(); beizhu1 = alu.getBeizhu1(); beizhu2 = alu.getBeizhu2(); qita = alu.getQita(); url = alu.getUrl(); lastname = alu.getLastName(); firstname = alu.getFirstName(); Alumnus a = new Alumnus(name, gender,birth,jiguan ,shouji,email,dianhua,xiantongxun,youbian,xueli,xueyuan,xibie, major, ruxuetime,gotime,gongzuochengshi,daiwei,danweidizhi,danweiyoubian,gongzuodianhua, gongzuochuanzhen,danweixingzhi,zhiwu,zhicheng,zhiji, beizhu1,beizhu2,qita,url,lastname,firstname); AlumnusDAO.save(a); } return SUCCESS; } String getTime(){ /** * 获取时间来定义文件名称 * @return String as the name of the file */ SimpleDateFormat formatter_f = new SimpleDateFormat("yyyyMMddHHmmss"); Date now = new Date(); String time = formatter_f.format(now); return time; } String getTrueTime(){ /** * 获取当前时间 * @return String time */ SimpleDateFormat formatter_f = new SimpleDateFormat("yyyy-MM-dd"); Date now = new Date(); String time = formatter_f.format(now); return time; } String getFilename(String name){ /** * 获取文件名的后缀 * @return String */ int i = name.lastIndexOf(".")+1; return name.substring(i,name.length()); } //接受依赖注入的方法 public void setSavePath(String value) { this.savePath = value; } @SuppressWarnings("deprecation") private String getSavePath() throws Exception { return ServletActionContext.getRequest().getRealPath(savePath); } public void setUpload(File upload) { this.upload = upload; } public void setUploadContentType(String uploadContentType) { this.uploadContentType = uploadContentType; } public void setUploadFileName(String uploadFileName) { this.uploadFileName = uploadFileName; } public File getUpload() { return (this.upload); } public String getUploadContentType() { return (this.uploadContentType); } public String getUploadFileName() { return (this.uploadFileName); } }
struts2.xml配置文件:

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN" "http://struts.apache.org/dtds/struts-2.1.dtd"> <struts> <package namespace="/" name="default" extends="struts-default"> <!-- 从excel导入 --> <action name="ImportFromExcel" class="com.alumnus.action.ImportFromExcel" > <param name="savePath">/upload</param> <result name="success">/test.jsp</result> </action> </package> </struts>

当然,还有Alumnus.java,这个我就不写了,反正就是那一群用汉语命名的属性,还有get/set方法.

其实这样看来,不是很麻烦的,但是,这个思维过程还是要有那么一段时间一段时间的...

come on baby~