jsp和excel的資料交換—–我的項目實踐

前段時間,客戶要求我們的系統可以實現與ms excel的資料交換,可以把系統中的資料匯入匯出到excel.我們的系統是一個用java做的b/s結構的MIS系統.為瞭解決這個問題,我採用了專門操作excel的開源項目jxl
根據客戶的要求,用jxl實現了如下功能:
1:在用戶端讀取excel檔案的資料;
2:把系統資料匯出到excel模板檔案中.

於是就想到了先把excel檔案上傳到伺服器,然後用jxl來解析資料.怎麼上傳檔案我就不說了,只要做過jsp的人應該都知道吧.主要講一下如何解析.由於上傳的資料都是
有固定格式的,所以我們做了一個excel模板,客戶必須按照模板的格式填寫資料,程式才可以正常的解析.
解析其實也是很簡單的,但在做的過程中發現,excel中的時間格式資料需要特別注意:如果按照文字格式設定讀取時間格式資料,讀取的時間是錯誤的(大家可以實驗一下).
為瞭解決讀取時間錯誤的問題,我仔細研究了一下jxl的api,呵呵,居然發現了一個好東東:DateCell類,用它的getDate方法可以得到一個cell的date對象.不過由於
國際化的需要,這個date對象得到的是格林尼治時間,你需要處理一下.
至於匯出,其實和匯入差不多,無非就是先訪問一下資料庫,把得到的資訊寫入excel檔案然後下載.
下面是我寫的匯入匯出周生產計劃的java類,其中開始的幾行注釋掉的部分分別是下載和上傳的試例.
(其實模板也很關鍵,下面的程式是基於模板而寫的,不過相信大家看了下面的程式,也可以知道模板的樣子了,需要重點說明的是,在模板中,填寫時間的欄位必須設定成時
間格式-----在excel中點擊右鍵菜單輕鬆搞定)

/*
 * Create Date: 2004-11-9  10:20
 * Create By: 李春雷
 * purpose:和周計劃相關的匯入匯出操作
 */
   
    /**********************周生產下載樣本*****************
    ExcelZJHXM myZJH =  new ExcelZJHXM();
    myZJH.download("mydataZjh","20041018003");
    ****************************************************/ 
   
    /**********************周計划上傳樣本*****************         
        ExcelZJHXM test = new ExcelZJHXM();       
        test.upload("zscjhtest");
        if(test.getFlag()){
        System.out.println("begin test--------------");
        ArrayList a = test.getData();
        for(int i = 0;i<a.size();i++){
        ExcelZJHXM.dataZJHXM mydata =(ExcelZJHXM.dataZJHXM)a.get(i);
        System.out.println(mydata.XH+" "+mydata.XMMC+" "+mydata.GZNR +" "+mydata.SFTD +" "+mydata.TDFW +" "+mydata.JHLRFLBH +" "+mydata.JHLRBH +" "+mydata.ZXDWMC +" "+mydata.PHDWMC +" "+mydata.DBRMC +" "+mydata.JHKSSJ+" "+mydata.JHWCSJ +" "+mydata.BZ);
        
    } 
        }else{
            System.out.println("模板錯誤");
            }
    *****************************************************/

package com.infoearth.excel;

import com.infoearth.common.DataAccess;  //我們項目自己的訪問資料庫的東東
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.SQLException;
import java.sql.*;
import java.io.*;
import java.io.Serializable;
import java.util.*;
import javax.sql.RowSet;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.*;
import java.text.DateFormat;
import jxl.DateCell;
import java.text.*;

public class ExcelZJHXM
{
    private boolean flag=false;
   
    private ArrayList subdata = new ArrayList();
       
    public boolean download(String filename,String jhbh){
        Connection conn = null;
        PreparedStatement pStatement = null;
        ResultSet rs = null;
        DataAccess dBean = new DataAccess();
        String mysql;
        mysql = "select XH,XMMC,GZNR,SFTD,TDFW,JHLRFLBH,JHLRBH,ZXDWMC,PHDWMC,DBRMC,JHKSSJ,JHWCSJ,BZ from JH_ZJHXM where JHBH='"+jhbh+"' order by jhxmbh asc";
        try {
            conn = dBean.getConnection();
            pStatement = conn.prepareStatement(mysql);
            rs = pStatement.executeQuery();
            Workbook wb=Workbook.getWorkbook(new File("pengyue//webmis//template//zscjh.xls"));
            WritableWorkbook book= Workbook.createWorkbook(new File("pengyue//webmis//download//"+filename+".xls"),wb);
            WritableSheet sheet = book.getSheet("zscjh");
            jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat();
         wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);           
            int i=4;
                jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-mm-dd hh:mm");
                jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
                wcfDF.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);                           
            while(rs.next()) {
                sheet.addCell(new Label(2,i,rs.getString("XH"),wcfFC));     //序號
                sheet.addCell(new Label(3,i,rs.getString("XMMC"),wcfFC));   //項目名稱
                sheet.addCell(new Label(4,i,rs.getString("GZNR"),wcfFC));   //工作內容
                sheet.addCell(new Label(5,i,rs.getString("SFTD"),wcfFC));   //是否停電
                sheet.addCell(new Label(6,i,rs.getString("TDFW"),wcfFC));   //停電範圍
                sheet.addCell(new Label(7,i,rs.getString("JHLRFLBH"),wcfFC));  //任務來源
                sheet.addCell(new Label(8,i,rs.getString("JHLRBH"),wcfFC));  //來源編號
                sheet.addCell(new Label(9,i,rs.getString("ZXDWMC"),wcfFC));  //執行單位
                sheet.addCell(new Label(10,i,rs.getString("PHDWMC"),wcfFC));  //配合單位
                sheet.addCell(new Label(11,i,rs.getString("DBRMC"),wcfFC));  //督辦人
//              sheet.addCell(new Label(12,i,rs.getString("JHKSSJ"),wcfFC));  //開始時間
//              sheet.addCell(new Label(13,i,rs.getString("JHWCSJ"),wcfFC));  //結束時間
                if(rs.getString("JHKSSJ")!=null)sheet.addCell(new jxl.write.DateTime(12, i,DateFormat.getDateTimeInstance().parse(rs.getString("JHKSSJ")), wcfDF));
                if(rs.getString("JHWCSJ")!=null)sheet.addCell(new jxl.write.DateTime(13, i,DateFormat.getDateTimeInstance().parse(rs.getString("JHWCSJ")), wcfDF));
                sheet.addCell(new Label(14,i,rs.getString("BZ"),wcfFC));      //備忘
                i++;
                }
            book.write();
            book.close();           
            rs.close();
            pStatement.close();
            return true;
        }
     
        catch(Exception e)
        {
            e.printStackTrace();
            return false;
        }
        finally{
            try{
               
                if (rs!=null){
                    rs.close();
                }
               
                if (pStatement!=null){
                    pStatement.close();
                }
                if (conn!=null){
                    conn.close();
                }
            }
            catch(SQLException sqle){
                conn = null;
            }
        }
    }

    public void upload(String filename){
        String flagstr;
        try{            
            jxl.Workbook rwb = Workbook.getWorkbook(new File("pengyue//webmis//upload//"+filename+".xls"));
            Sheet st = rwb.getSheet("zscjh");
            int i = 4;           //其始列-1
            flagstr = st.getCell(0,0).getContents();
            if(flagstr.equals("zscjhbegin"))flag=true;
              while(st.getCell(2,i).getContents().length()!=0&&flag==true){
                dataZJHXM mydata = new dataZJHXM();
                for(int j=2;j<st.getColumns();j++){              
                    if(st.getCell(j,0).getContents().equals("XH"))mydata.XH = st.getCell(j,i).getContents();
                    if(st.getCell(j,0).getContents().equals("XMMC"))mydata.XMMC = st.getCell(j,i).getContents();
                    if(st.getCell(j,0).getContents().equals("GZNR"))mydata.GZNR = st.getCell(j,i).getContents();
                    if(st.getCell(j,0).getContents().equals("SFTD"))mydata.SFTD = st.getCell(j,i).getContents();
                    if(st.getCell(j,0).getContents().equals("TDFW"))mydata.TDFW = st.getCell(j,i).getContents();
                    if(st.getCell(j,0).getContents().equals("JHLRFLBH"))mydata.JHLRFLBH = st.getCell(j,i).getContents();
                    if(st.getCell(j,0).getContents().equals("JHLRBH"))mydata.JHLRBH = st.getCell(j,i).getContents();
                    if(st.getCell(j,0).getContents().equals("ZXDWMC"))mydata.ZXDWMC = st.getCell(j,i).getContents();
                    if(st.getCell(j,0).getContents().equals("PHDWMC"))mydata.PHDWMC = st.getCell(j,i).getContents();
                    if(st.getCell(j,0).getContents().equals("DBRMC"))mydata.DBRMC = st.getCell(j,i).getContents();
                    //時間處理
                    if(st.getCell(j,0).getContents().equals("JHKSSJ"))mydata.JHKSSJ = FormateData(st.getCell(j,i));
                    if(st.getCell(j,0).getContents().equals("JHWCSJ"))mydata.JHWCSJ = FormateData(st.getCell(j,i));
                    if(st.getCell(j,0).getContents().equals("BZ"))mydata.BZ = st.getCell(j,i).getContents();
                    }
                    subdata.add(mydata);              
                    i++;
                }
            rwb.close();          
        }catch(Exception e){
            e.printStackTrace();
            flag = false;           
            }
       }

    //處理日期格式資料  
    public String FormateData(Cell formatecell){
        try{
            java.util.Date mydate=null;
            DateCell datecll = (DateCell)formatecell;
            mydate =datecll.getDate();
            long time=(mydate.getTime()/1000)-60*60*8;
            mydate.setTime(time*1000);
            Calendar cal = Calendar.getInstance();
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm");
            return  formatter.format(mydate);          
            }
        catch(Exception e){
            e.printStackTrace();
            return null;
            }      
        }
  
    public boolean getFlag(){
        return flag;
        }
       
    public ArrayList getData(){
        return subdata;
        } 
         
    public static class dataZJHXM implements Serializable
    {
        public String XH;
        public String XMMC;
        public String GZNR;
        public String SFTD;
        public String TDFW;
        public String JHLRFLBH;
        public String JHLRBH;
        public String ZXDWMC;
        public String PHDWMC;
        public String DBRMC;
        public String JHKSSJ;
        public String JHWCSJ;
        public String BZ;
   
        public dataZJHXM(){
            XH = "";
            XMMC = "";
            GZNR = "";
            SFTD = "";
            TDFW = "";
            JHLRFLBH = "";
            JHLRBH = "";
            ZXDWMC = "";
            PHDWMC = "";
            DBRMC = "";
            JHKSSJ = "";
            JHWCSJ = "";
            BZ = "";                       
            }     

    }
}

附:

參考:http://www-900.cn.ibm.com/developerWorks/cn/java/l-javaExcel/#2