excelread.java

oa 源码

package com.redmoon.oa.address;


import java.io.*;
import jxl.*;
import jxl.read.biff.BiffException;
import cn.js.fan.util.ErrMsgException;
import com.redmoon.oa.pvg.Privilege;
import javax.servlet.http.*;
import cn.js.fan.util.ParamUtil;
import cn.js.fan.util.*;

public class ExcelRead {
    Workbook book = null;
    public ExcelRead(){
    }
    public ExcelRead(String xlspath, String username, int type, int group) throws ErrMsgException,
            IndexOutOfBoundsException {
        boolean re = true;
        String person = "", job = "", tel = "", mobile = "", email = "",
                address = "", postalcode = "", introduction = "";
        String beepPager = "", city = "", company = "", companyCity = "",
                companyCountry = "", companyPostcode = "";
        String companyProvice = "", companyStreet = "", country = "",
                familyname = "", fax = "", firstname = "", middlename = "",
                nickname = "";
        String operationFax = "", operationPhone = "", operationweb = "",
                province = "", street = "", web = "", QQ = "", MSN = "";
        String errmsg = "";

        try {
            book = Workbook.getWorkbook(new java.io.File(xlspath));
            
            jxl.Sheet rs = book.getSheet(0);
            int rsRows = rs.getRows();
            int rsColumns = rs.getColumns();
            Cell cc;
            String strc[] = new String[rsColumns];
            for (int i = 1; i < rsRows; i++) {
                for (int j = 0; j < rsColumns; j++) {
                    cc = rs.getCell(j, i);
                    strc[j] = cc.getContents();
                }
                person = strc[3];
                nickname = strc[4];
                email = strc[5];
                street = strc[6];
                tel = strc[11];
                fax = strc[12];
                mobile = strc[13];
                web = strc[14];
                company = strc[15];
                operationweb = strc[20];
                operationPhone = strc[21];
                operationFax = strc[22];
                job = strc[25];
                address = strc[27];
                AddressDb addr = new AddressDb();
                addr.setPerson(person);
                addr.setJob(job);
                addr.setTel(tel);
                addr.setMobile(mobile);
                addr.setEmail(email);
                addr.setAddress(address);
                addr.setPostalcode(postalcode);
                addr.setIntroduction(introduction);
                addr.setBeepPager(beepPager);
                addr.setCity(city);
                addr.setCompany(company);
                addr.setCompanyCity(companyCity);
                addr.setCompanyCountry(companyCountry);
                addr.setCompanyPostcode(companyPostcode);
                addr.setCompanyProvice(companyProvice);
                addr.setCompanyStreet(companyStreet);
                addr.setCountry(country);
                addr.setFamilyname(familyname);
                addr.setFax(fax);
                addr.setFirstname(firstname);
                addr.setMiddleName(middlename);
                addr.setNickname(nickname);
                addr.setOperationFax(operationFax);
                addr.setOperationPhone(operationPhone);
                addr.setOperationweb(operationweb);
                addr.setProvince(province);
                addr.setStreet(street);
                addr.setWeb(web);
                addr.setUserName(username);
                addr.setType(type);
                addr.setTypeId(group);
                addr.create();
            }
        } catch (BiffException ex) {
            System.out.println("Read Excel file failed!");
            System.out.println("File name: " + xlspath);
            System.exit(0);
        } catch (IOException ex) {
            System.out.println("Read Excel file failed!");
            System.out.println("File name: " + xlspath);
            System.exit(0);
        }
    }
    public void Excelhad(String xlspath, String username, int type, int group) throws ErrMsgException,
            IndexOutOfBoundsException {
        boolean re = true;
       String person = "", job = "", tel = "", mobile = "", email = "",
               address = "", postalcode = "", introduction = "";
       String beepPager = "", city = "", company = "", companyCity = "",
               companyCountry = "", companyPostcode = "";
       String companyProvice = "", companyStreet = "", country = "",
               familyname = "", fax = "", firstname = "", middlename = "",
               nickname = "";
       String operationFax = "", operationPhone = "", operationweb = "",
               province = "", street = "", web = "", QQ = "", MSN = "";
       String errmsg = "",department="";

       try {
           book = Workbook.getWorkbook(new java.io.File(xlspath));
           
           jxl.Sheet rs = book.getSheet(0);
           int rsRows = rs.getRows();
           int rsColumns = rs.getColumns();
           Cell cc;
           int columns;
           String fieldsValue;
           int fields[] = new int[rsColumns];
           String strc[] = new String[rsColumns];
           int k = 0;
           for (int m=0; m<rsColumns; m++) {
               cc = rs.getCell(m,0);
               fieldsValue = cc.getContents();
               if(fieldsValue.equals("名")) {
                   fields[0] = m;
                   k++;
               }
               if(fieldsValue.equals("姓")) {
                   fields[1] = m;
                   k++;
               }
               if(fieldsValue.equals("单位")||fieldsValue.equals("公司")) fields[2] = m;
               if(fieldsValue.equals("部门")) fields[3] = m;
               if(fieldsValue.equals("职务")) fields[4] = m;
               if(fieldsValue.equals("住宅电话")) fields[5] = m;
               if(fieldsValue.equals("移动电话")) fields[6] = m;
               if(fieldsValue.equals("电子邮件地址")) fields[7] = m;
               if(fieldsValue.equals("类别")) fields[8] = m;
               if(fieldsValue.indexOf("网页")!=-1) fields[9] = m;
               if(fieldsValue.equals("车载电话")) fields[10] = m;
               if(fieldsValue.equals("商务电话")) fields[11] = m;
               if(fieldsValue.equals("商务传真")) fields[12] = m;
            }
            if (k <2) throw new ErrMsgException("导入Excel格式不正确，请检查！");
            for (int i=1 ;i< rsRows ; i++) {
              for (int j=0; j<=12 ;j++){
                 columns = fields[j];
                 cc = rs.getCell(columns,i);
                 strc[j] = cc.getContents();
              }
              
              person = strc[1] + strc[0];
              company = strc[2];
              department = strc[3];
              job = strc[4];
              tel = strc[5];
              mobile = strc[6];
              email = strc[7];
              web = strc[9];
              beepPager = strc[10];
              operationPhone = strc[11];
              operationFax = strc[12];

              AddressDb addr = new AddressDb();
              addr.setPerson(person);
              addr.setEmail(email);
              addr.setTel(tel);
              addr.setMobile(mobile);
              addr.setWeb(web);
              addr.setCompany(company);
              addr.setJob(job);
              addr.setDepartment(department);
              addr.setBeepPager(beepPager);
              addr.setUserName(username); 
              addr.setType(type);
              addr.setTypeId(group);
              addr.setOperationPhone(operationPhone);
              addr.setOperationFax(operationFax);
              addr.create();
          }
       } catch (BiffException ex) {
           System.out.println("Read Excel file failed!");
           System.out.println("File name: " + xlspath);
           System.exit(0);
       } catch (IOException ex) {
           System.out.println("Read Excel file failed!");
           System.out.println("File name: " + xlspath);
           System.exit(0);
       }

    }
}