alumnus system - jxl导入导出excel java - bulote

bulote

浏览: 1305606 次

最近访客更多访客>>

u012363178

songhait

chk141

sharpbai

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (1272)

社区版块

存档分类

2012-02 ( 25)
2012-01 ( 145)
2011-12 ( 22)
更多存档...

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 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);        // 创建一个工作表

//    填充数据的内容
            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">
	
  </head>
  
  <body bgcolor="#f0f0f0">
     <form name="formimport" method="post" action="ImportFromExcel.action" <span style="color: #990000;"><strong>enctype="multipart/form-data"</strong></span>>
    请选择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配置文件:

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

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

come on baby~

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

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

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

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

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

好啦,下面是代码:

Person.java

package com.xingyun.pi;

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;

public class DealWithExcel {

wwb.write();
            wwb.close();

} catch (IOException e){
        } catch (RowsExceededException e){
        } catch (WriteException e){}
    }

book.close();
        }catch(Exception e) {} 
        return v;
    }

这是人家搞的,我觉得很好用很好用,下面俺就来试着去实现一个什么功能呢,是这样的:

对于导出:

对于导入:

导入数据的时候,一般我们要让用户去选择文件,然后点击"导入",之后再读取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 com.alumnus.data.Alumnus;

public class Excel {

wwb.write();
            wwb.close();

} catch (IOException e){
        } catch (RowsExceededException e){
        } catch (WriteException e){}
    }

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">
	
  </head>
  
  <body bgcolor="#f0f0f0">
     <form name="formimport" method="post" action="ImportFromExcel.action" <span style="color: #990000;"><strong>enctype="multipart/form-data"</strong></span>>
    请选择excel文件:<input type="file" name="upload" id="upload">
    <input type="submit" name="button4" id="button4" value="导入">
    </form>
</body>