java excel导入

poi导入：

//得到excel中数据
public String[][] ReadExcleUnlockAndLock(FormFile file) throws FileNotFoundException, IOException,AppException {

        List<String> lists = new ArrayList<String>();
        String fileName = file.getFileName();
        Workbook workbook = null;
        Sheet sheet = null;
        if (fileName.endsWith("xls")) {
            workbook = new HSSFWorkbook(file.getInputStream());
            sheet = workbook.getSheetAt(0);
        } else if (fileName.endsWith("xlsx")) {
            workbook = new XSSFWorkbook(file.getInputStream());
            sheet = workbook.getSheetAt(0);
        }
        if (sheet==null) {
            //return "导入文件的内容为空！";
            return null;
        }
        String message = "";

        int coloumNum=sheet.getRow(0).getPhysicalNumberOfCells();//获得总列数
        int rowNum=sheet.getLastRowNum()+1;//获得总行数
        String[][] excelValue = new String[rowNum][coloumNum];//获取excel行例
        for (int i = 0; i < rowNum; i++) {
            for (int j = 0; j < coloumNum; j++) {
                Row row=sheet.getRow(i);
                if (row.getRowNum() == 0) {
                    break;
                }
                // 跳过空行
                Cell cell = row.getCell(j);
                if (cell==null) {
                    excelValue[i][j] = "";
                    continue;
                }

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_STRING://字符串
                    cell.setCellType(cell.CELL_TYPE_STRING);
                    excelValue[i][j]=cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC://数值
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        excelValue[i][j]=sdf.format(date);
                    }else{//纯数值
                        excelValue[i][j]=String.valueOf(cell.getNumericCellValue());
                    }   
                    break;
                case Cell.CELL_TYPE_BOOLEAN://布尔
                    cell.setCellType(cell.CELL_TYPE_BOOLEAN);
                    excelValue[i][j]=String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_BLANK://空值
                    cell.setCellType(cell.CELL_TYPE_BLANK);
                    excelValue[i][j]="";
                    break;
                case Cell.CELL_TYPE_FORMULA://公式
                    cell.setCellType(cell.CELL_TYPE_FORMULA);
                    excelValue[i][j]="";
                    break;
                case Cell.CELL_TYPE_ERROR://错误
                    excelValue[i][j]="";
                    break;

                default:
                    break;
                }
            }
        }
        return excelValue;





        if (excelValue==null || excelValue.length==0 ||(excelValue.length==1&& excelValue[0].length==0)) {
            return "导入文件的内容为空！";
        }
        //以下是校验方法
        for (int i = 1 , rowlength=excelValue.length; i < rowlength; i++) {
            boolean falg=false;
            //for (int j = 0 ,colcolength=excelValue[i].length; j <colcolength; j++) {
                String orderid=excelValue[i][0]==null?"":excelValue[i][0];
                String orderno =excelValue[i][1]==null?"":excelValue[i][1];
                String isorder=";
                Query isroorderquery = this.getSession().createSQLQuery(isorder).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                isroorderquery.setParameter("orderid", orderid);
                List<Map> list1 = isroorderquery.list();
                if (list1==null||list1.size()<=0) {
                    falg=true;
                    break;
                }
                String mrno =excelValue[i][2]==null?"":excelValue[i][2];
                String sql = "";        
                Query query = this.getSession().createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                query.setParameter("ORDERID", orderid);
                query.setParameter("MRNO", mrno);
                String mrqorderid="";
                List<Map> quList = query.list();            
                if (quList==null || quList.size()<1) {
                    falg=true;
                    break;
                }else{
                    mrqorderid=String.valueOf(quList.get(0).get("ORDERID"));
                }

            }
        //  if (falg) {
        //      continue;
        //  }
        //}

        //实际导入，保存到数据库

        if ("".equals(message)) {
            return "导入成功！！！";
        } else {
            return message;
        }


    }

}

jxl导入

package org.rj.me.impl.imm;



import java.io.InputStream;
import java.math.BigDecimal;


import java.text.SimpleDateFormat;

import java.util.Date;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.struts.upload.FormFile;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.rj.me.amevo.imm.DrExcleVO;
import org.rj.me.impl.AMEBaseService;
import org.rj.me.interfaces.imm.IDrExcle;
import org.rj.me.model.imm.AttendanceModel;

import org.rj.me.model.imm.SmAofAttendence;
import org.rj.me.model.imm.SmAofFloat;

public class DrImpl extends AMEBaseService implements IDrExcle {
    DrExcleVO dr = new DrExcleVO();
    private Sheet sheet;
    private String[][] excelValue;

    private StringBuilder msg = new StringBuilder();
    private String finalMsg="";


    /**
     * 人员出勤及excel导入总方法
     */
    public String HolidayUploadStu(FormFile upload) {
        initExcel(upload);
        readExcel();
        return HolidayinsertIntoDB();

    }

    public String HolidayinsertIntoDB() {
        int successRow=0;
        int excelRows = excelValue.length;
        Long attendenceID = this.getHolidayId();
        try {
            if (excelRows > 1) {
                for (int i = 1; i < excelRows; i++) { // 从第2排开始 第一排文字说明
                    String[] DBValue = excelValue[i];// 取第一行数据      
                        successRow += 1;
                    SmAofAttendence sav = new SmAofAttendence();
                    if(sav.getMebno()==""){
                        continue;
                    }
                    // sav.setDeptname(DBValue[1]);
                    sav.setAttendenceid(new Long(attendenceID + i));
                    sav.setDeptid(Long.parseLong(DBValue[0]));
                    sav.setMebno(DBValue[2]);
                    sav.setName(DBValue[3]);
                    sav.setDays(Double.parseDouble(DBValue[4]));
                    sav.setLate(Double.parseDouble(DBValue[5]));
                    sav.setYear(Double.parseDouble(DBValue[6]));
                    sav.setSick(Double.parseDouble(DBValue[7]));
                    sav.setPs(Double.parseDouble(DBValue[8]));
                    sav.setVf(Double.parseDouble(DBValue[9]));
                    sav.setMn(Double.parseDouble(DBValue[10]));
                    sav.setFuneral(Double.parseDouble(DBValue[11]));
                    sav.setChaperone(Double.parseDouble(DBValue[12]));
                    sav.setWedding(Double.parseDouble(DBValue[13]));
                    sav.setFeeding(Double.parseDouble(DBValue[14]));
                    sav.setInjury(Double.parseDouble(DBValue[15]));
                    sav.setStudy(Double.parseDouble(DBValue[16]));
                    sav.setStationcode(DBValue[17]);
                    sav.setNote(DBValue[18]);
                    this.getSession().save(sav);

                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            finalMsg = "导入失败！";
        }
        finalMsg = "导入成功：" + "<br>" + "目标导入人员出勤及休假管理：" + (successRow-1) + "</br>"
                + "成功录入数：" + (successRow-1)
                + "</br>" + "失败录入数" + (0) + "<br>" + msg.toString();
        return finalMsg;
    }

    /**
     * 读取excel数据，保存sheet对象中
     */
    public void initExcel(FormFile upload) {
        Workbook rwb = null;
        try {
            InputStream is =upload.getInputStream();
            rwb = Workbook.getWorkbook(is);
            sheet = rwb.getSheet(0);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 读取excle数据进入excelValue数组中
     */
    public void readExcel() {
        excelValue = new String[sheet.getRows()][sheet.getColumns()];
        for (int i = 0; i < sheet.getRows(); i++) {
            for (int j = 0; j < sheet.getColumns(); j++) {
                Cell cell = sheet.getCell(j, i);
                if ("".equals(cell.getContents().toString().trim())) {
                    excelValue[i][j] = "";
                }
                if (cell.getType() == CellType.LABEL) {
                    LabelCell lablecell = (LabelCell) cell;
                    excelValue[i][j] = lablecell.getString().trim();
                } else if (cell.getType() == CellType.NUMBER) {
                    excelValue[i][j] = cell.getContents();
                } else if (cell.getType() == CellType.DATE) {
                    DateCell datcell = (DateCell) cell;
                    Date excelDate = null;
                    excelDate = datcell.getDate();
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                    excelValue[i][j] = format.format(excelDate);
                } else {
                    excelValue[i][j] = cell.getContents().toString().trim();
                }
            }
        }

    }

    /**
     * 生成出勤表的主键ID
     * 
     * @return
     */
    private Long getHolidayId() {
        String sql = "select MAX(ATTENDENCEID) as ATTENDENCEID from sm_aof_attendence";
        SQLQuery query = this.getSession().createSQLQuery(sql);
        query.addScalar("ATTENDENCEID", Hibernate.LONG);
        List<Map<String, Object>> list = query.setResultTransformer(
                Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Object> map = list.get(0);
        String Holidayid = map.get("ATTENDENCEID") == null ? "0" : map.get("ATTENDENCEID").toString();
        return new Long(Holidayid);
    }

    /**
     * 生成节假日加班主键ID
     */
    private Long getAttendanceId() {
        String sql = "select MAX(OVERTIMEID) as OVERTIMEID from sm_aof_overtime";
        SQLQuery query = this.getSession().createSQLQuery(sql);
        query.addScalar("OVERTIMEID", Hibernate.LONG);
        List<Map<String, Object>> list = query.setResultTransformer(
                Transformers.ALIAS_TO_ENTITY_MAP).list();
        if(list.size() == 0){
            return 0l;
        }
        Map<String, Object> map = list.get(0);
        String attendenceid = map.get("OVERTIMEID") == null ? "0" : map.get("OVERTIMEID").toString();
        return new Long(attendenceid);
    }

    /**
     * 生成员工津贴主键ID
     */
    private Long getAllowanceId() {
        String sql = "select MAX(FLOATID) as FLOATID from sm_aof_float";
        SQLQuery query = this.getSession().createSQLQuery(sql);
        query.addScalar("FLOATID", Hibernate.LONG);
        List<Map<String, Object>> list = query.setResultTransformer(
                Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Object> map = list.get(0);
        String attAllowanceid = map.get("FLOATID").toString();
        return new Long(attAllowanceid);
    }

    /**
     * 员工节假日加班管理 总方法
     */
    public String AttendanceUploadStu(FormFile upload) {
        initExcel(upload);
        readExcel();
        return AtteninsertIntoDB();
    }

    private String AtteninsertIntoDB() {
        int successRow=0;
        int excelRows = excelValue.length;
        Long overtimeid = this.getAttendanceId();
        if (excelRows > 1) {
            for (int i = 1; i < excelRows; i++) { // 从第2排开始 第一排文字说明
                String[] DBValue = excelValue[i];// 取第一行数据

                AttendanceModel av = new AttendanceModel();
                if(DBValue[0]==""){
                    continue;
                }
                successRow += 1;
                av.setOvertimeid(new Long(overtimeid + i));
                Date day = null;
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                try {
                    day = sdf.parse(DBValue[0]);
                    av.setMonth(day);
                    // av.setDeptname(DBValue[1]);//部门名称
                    av.setDeptid(Long.parseLong(DBValue[1]));
                    av.setSysuserid(DBValue[3]);
                    av.setMebno(DBValue[4]);
                    av.setName(DBValue[5]);
                    av.setHs(Double.parseDouble(DBValue[6]));
                    av.setOvertimehours(Double.parseDouble(DBValue[7]));
                    av.setOvertimepay(Double.parseDouble(DBValue[8]));
                    av.setNightdutytime(Double.parseDouble(DBValue[9]));
                    av.setNightdutypay(Double.parseDouble(DBValue[10]));
                    av.setSum(Double.parseDouble(DBValue[11]));
                    av.setNote(DBValue[12]);
                    av.setStatus("0");
                    this.getHibernateTemplate().save(av);
                } catch (Exception e) {
                    e.printStackTrace();
                    finalMsg = "导入失败！";
                }
            }
        }
        finalMsg = "导入成功：" + "<br>" + "目标导入人员假日加班管理：" + successRow + "</br>"
        + "成功录入数：" + (successRow)
        + "</br>" +"失败录入数"+(0)+ "<br>"+ msg.toString();
        return finalMsg;

    }

    /**
     * 员工节津贴及奖金管理
     */
    public String AllowanceUploadStu(FormFile upload) {
        initExcel(upload);
        readExcel();
        /*String a=thIntoDB();
        String b=a;*/
        try {
            String a=this.IntoDB();
            return a;   
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    private String IntoDB() {
        int successRow=0;
        int excelRows = excelValue.length;
        double num=0.0;
        double quota=0.0;
        String deptname=null;
        Long deptid=0L; 
        if(excelRows > 1){
            for(int i=1;i<excelRows;i++){
                String[] DBValue = excelValue[i];
                double d=Double.parseDouble(DBValue[8]);
                num+=d;
                if(deptname==null){
                    deptname=DBValue[3];
                    String sql1=" select id from sys_dept where name like '%"+deptname+"%'";
                    List list1 = this.getSession().createSQLQuery(sql1).list();
                    if(list1!=null && list1.size()>0){
                        BigDecimal id=(BigDecimal) list1.get(0);
                        String s=id.toString();
                        deptid=Long.parseLong(s);

                    }
                    Date date=new Date();
                    String today=new SimpleDateFormat("yyyy-MM").format(date);
                    //String today = cal.get(Calendar.YEAR)+"-" + (cal.get(Calendar.MONTH)+1) ;
                    String sql=" select quota from sm_quota where dept='"+deptid+"'and month ='"+today+"'";
                    List list = this.getSession().createSQLQuery(sql).list();
                    if(list!=null && list.size()>0){
                        BigDecimal quo=(BigDecimal) list.get(0);
                        String b=quo.toString();
                        quota=Double.parseDouble(b);

                    }
                }
            }
        }
        try {
            if (excelRows > 1) {                
                for (int i = 1; i < excelRows; i++) { // 从第2排开始 第一排文字说明
                    String[] DBValue = excelValue[i];// 取第一行数据  
                    SmAofFloat sfv = new SmAofFloat();
                    if(sfv.getSysuserid()==""){
                        continue;
                    }   
                    successRow += 1;
                    sfv.setSysuserid(DBValue[1]);
                    sfv.setMebno(DBValue[1]);
                    //sfv.setMebno(DBValue[2]);
                    sfv.setName(DBValue[2]);// 员工姓名
                    sfv.setBranch(DBValue[4]);
                    //sfv.setDeptid();
                    //sfv.setDeptid(Long.parseLong(DBValue[3]));
                    sfv.setLev(DBValue[5]);
                    sfv.setPost(DBValue[6]);
                    sfv.setPa(Double.parseDouble(DBValue[7]));
                    sfv.setFb(Double.parseDouble(DBValue[8]));
                    sfv.setRpa(DBValue[9]);
                    //sfv.setRfb(Double.parseDouble(DBValue[10]));
                    //sfv.setFbs(Double.parseDouble(DBValue[11]));
                    //sfv.setFad(Double.parseDouble(DBValue[12]));
                    sfv.setSdb(DBValue[10]);
                    sfv.setOfficeno(DBValue[11]);       
                    sfv.setDeptid(deptid);
                    sfv.setStatus("0");
                    if(quota>=num){
                        this.getHibernateTemplate().save(sfv);
                        finalMsg = "导入成功：" + "<br>" + "目标导入员工津贴及奖金管理：" + successRow + "</br>"
                        + "成功录入数：" + (successRow)
                        + "</br>" +"失败录入数"+(0)+ "<br>"; 

                    }else{
                        finalMsg = "导入失败：员工实发效益奖金大于额度,请重新分配！";      
                    }

                }

            }
        } catch (Exception e) {
            e.printStackTrace();
            finalMsg = "导入失败！";
        }

        return finalMsg;
    }

}