虽然没有留下什么,但是我确确实实来过这里
--- 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;
}
}<%@ 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~
首先,新建一个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;
}
}
<%@ 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~
289
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
