EXCEL导入数据库及数据库数据导出到EXCEL

   主要涉及内容及技术：
  javaexcel api
  jasonreport及编辑工具ireport和数据库技术。
导入及导出EXCEL解决思路：
  使用javaexcel api导入普通的EXCEL表格，就是没有合并单元格的规范数据，如果导出有规则的EXCEL也使用JAVAEXECEL API，对中文支持比较好。
  如果要导出报表可打印的EXCEL，使用ireport为工具图形化画出报表，并使用jasonreprt控制导出，展示给客户端。
  主要代码有：
  连接数据库的方法:

import java.sql.Connection;   
import java.sql.ResultSet;   
import java.sql.SQLException;   
import java.sql.Statement;   
import java.util.Hashtable;   
import java.util.Vector;   
   
import org.apache.log4j.Logger;   
   
public class DBOperate {      

       
    /**  
     * 得到sequence的nextval  
     * @param sequenceName   
     * @return  
     */   
    public static String getSequence(String sequenceName) {   
        String nextval = "";   
        if(sequenceName == null || "".equals(sequenceName)) {   
            System.out.println("sequenceName name is null!");              
            return null;   
        } else {   
            sequenceName = sequenceName.toUpperCase();             
            String sql = "select " + sequenceName + ".nextval nextid from user_sequences where sequence_name = '"    
                       + sequenceName + "'";   
               
            Statement stmt = null;   
            Connection conn = null;   
            ResultSet rs = null;   
               
            try {   
                conn = TreatDB.getConnDB("");   
                if (conn != null) {   
                    stmt = conn.createStatement();   
                    rs = stmt.executeQuery(sql);   
                       
                    if(rs != null) {   
                      while(rs.next()) {   
                        nextval = rs.getString("nextid");                          
                      }   
                    }   
                }   
            } catch (SQLException e) {   
                System.out.println("SQLException : " + e);   
            } finally {   
                try {   
                    if(rs != null) rs.close();   
                    if(stmt != null) stmt.close();   
                    if(conn != null) conn.close();   
                } catch (SQLException e) {   
                    // TODO Auto-generated catch block                     
                }   
            }   
            return nextval;   
        }   
    }   
/**  
     * 一个插入、更新数据表的通用方法，传入一个sql脚本  
     * @param sql ：要进行操作的脚本  
     * @return ：发生变化的条数  
     */   
    public static int updateSql(String sql) {   
        String dbName = "";   
        Connection conn = null;   
        Statement stmt = null;   
        int result = 0;   
        try {   
            conn = TreatDB.getConnDB(dbName);   
            if (conn != null) {   
                stmt = conn.createStatement();   
                //logger.info(sql);   
                result = stmt.executeUpdate(sql);                  
                conn.commit();   
            }   
   
        } catch (SQLException e) {   
            try {   
                conn.rollback();   
            } catch (SQLException e1) {   
                System.out.println("update sql error: "+e);   
                   
            }              
            System.out.println("update sql error: "+e);   
            System.out.println("sql: " + sql);             
        } finally {   
            try {   
                if (stmt != null)   
                    stmt.close();   
                if (conn != null)   
                    conn.close();   
            } catch (Exception ex) {   
                //do nothing;   
            }   
   
        }   
        return result;   
    }   
       
} 

--------------------------------------------------------------------
导入EXCEL的程序代码:

/**  
     * 将Excel文件中的数据添加到数据库中  
     * 新的监理信息　modify by heweiya  
     *   
     * @param xlsFile，Excel文件  
     * @return  
     */   
    public String addJLXlsForm(InputStream is) {   
           
        String errStr = "";   
        String result = "";        
   
        try {              
            Workbook rwb = Workbook.getWorkbook(is);   
            // Workbook rwb = Workbook.getWorkbook(new File(filePath+fileName));   
            // 获取第一张Sheet表   
            Sheet sheet = rwb.getSheet(0);   
            // 获取总列数   
            int rsColumns = sheet.getColumns();   
            System.out.println("rsColumns = " + rsColumns);   
               
            // 获取总行数   
            int rsRows = sheet.getRows();   
            System.out.println("rsRows = " + rsRows);   
               
            if (rsColumns < 19) {   
                errStr = "错误原因：字段不全。";   
                vErr.addElement(errStr);   
            }   
               
            int i = 0;   
            int startRows = 1;   
            int startColumn = 0;   
            while (startRows < rsRows) {   
                Hashtable ht = new Hashtable();   
                   
                String zj_id = DBOperate.getSequence("S_JIANLI");          
   
                   
                Cell tmp = sheet.getCell(0,startRows);                     
                String sfzh = tmp.getContents();       
                   
                tmp = sheet.getCell(1,startRows);                      
                String issueDate = tmp.getContents();                  
                String issueDate_new = "";   
                if(issueDate != null && issueDate.length()>=6) issueDate_new = issueDate.substring(6) + issueDate.substring(3,5) + issueDate.substring(0,2);   
                   
                tmp = sheet.getCell(2,startRows);                      
                String approveDate = tmp.getContents();                
                String approve_date_new = "";   
                if(approveDate != null && approveDate.length()>=6) approve_date_new = approveDate.substring(6) + approveDate.substring(3,5) + approveDate.substring(0,2);   
               
                tmp = sheet.getCell(3,startRows);                      
                String cerNo = tmp.getContents();   
                   
                tmp = sheet.getCell(4,startRows);                      
                String jianliMajor = tmp.getContents();                
   
                tmp = sheet.getCell(5,startRows);                      
                String department = tmp.getContents();   
                   
                tmp = sheet.getCell(6,startRows);                      
                String jianliName = tmp.getContents();   
               
                tmp = sheet.getCell(7,startRows);                      
                String jianliSex = tmp.getContents();                          
                   
                //毕业院校   
                tmp = sheet.getCell(8,startRows);                      
                String degree = tmp.getContents();   
               
                //毕业时间   
                tmp = sheet.getCell(9,startRows);                      
                String cooleage = tmp.getContents();   
               
                //所学专业   
                tmp = sheet.getCell(10,startRows);                     
                String company = tmp.getContents();   
               
                //职务   
                tmp = sheet.getCell(11,startRows);                     
                String tech_post = tmp.getContents();                  
                   
//              工作单位   
                tmp = sheet.getCell(12,startRows);                     
                String address = tmp.getContents();                
                   
//              职称   
                tmp = sheet.getCell(13,startRows);                     
                String postcode = tmp.getContents();                   
                   
                tmp = sheet.getCell(14,startRows);                     
                String jianliTel = tmp.getContents();   
                   
                tmp = sheet.getCell(15,startRows);                     
                String mobile = tmp.getContents();   
                   
                tmp = sheet.getCell(16,startRows);                     
                String email = tmp.getContents();   
                   
                tmp = sheet.getCell(17,startRows);                     
                String birthday = tmp.getContents();                   
                String birthday_new = "";   
                if(birthday != null && birthday.length()>=6) birthday_new = birthday.substring(6) + birthday.substring(3,5) + birthday.substring(0,2);   
                   
                   
                tmp = sheet.getCell(18,startRows);                     
                String jianli_type = tmp.getContents();    
                //加入密码　modify by heweiya 2007/05/17   
                String sql = "insert into jianli values('" + zj_id + "', '" + sfzh + "', to_date('" + issueDate_new + "','yyyymmdd'),to_date('"   
                   + approve_date_new + "','yyyymmdd'),'" + cerNo + "','" + jianliMajor + "','" + department + "','"   
                   + jianliName + "','" + jianliSex + "','" + degree + "','" + cooleage + "','"    
                   + company + "','" + tech_post + "','" + address + "','" + postcode + "','" + jianliTel    
                   + "','" + mobile + "','" + email + "',to_date('" + birthday_new + "','yyyymmdd'),'" + jianli_type  + "',null,null,null,null,"+sfzh.substring(0, 10)+",null,null,null,null,null)";   
                   
                int a = DBOperate.updateSql(sql);   
                   
                System.out.println("成功导入第"+startRows+"条");         
                if (a == 0) {   
                    errStr = errStr + "添加失败:库中已有此记录,或有值不符合库中设定的字段属性!";   
                } else if (a == 1) {   
                    result = "添加成功";   
                } else {   
                    errStr = errStr + "添加失败:连接数据库失败";   
                }   
                       
                   
   
                if (!"".equals(errStr)) {   
                    vErr.addElement("导入：" + "【" + startRows + "】" + "<br>(错误原因：" + errStr);   
                    errStr += "导入：" + "【" + startRows + "】" + "<br>(关键字段为：姓名："+jianliName+",身份证号为："+sfzh+",监理证书号为："+cerNo+")<br>";   
                } else {   
                    vOk.add(ht);   
                }   
   
                startRows++;   
            }   
               
            rwb.close();   
            result = "成功导入：" + vOk.size()+"条记录。<br> 错误导入： " + vErr.size()+"条记录,错误记录及原因如下：<br>"+errStr;   
            System.out.println("vOK.size() = " + vOk.size());   
            System.out.println("VErr.size() = " + vErr.size());   
            return result;   
        } catch (Exception e) {   
            result = "在导入的过程当中发生了错误，其中错误的原因是：" + e.toString();             
            return result;   
        }   
    }   

------------------------------------------------------------------------------
导出报表的代码:

/**  
 *   
 * 导出报表  
 *   
 * @param  
 * @return  
 * @throws  
 */   
public ActionForward ExportXLS(ActionMapping mapping, ActionForm form,   
        HttpServletRequest request, HttpServletResponse response)   
        throws Exception {   
    HttpSession session = request.getSession();   
    String CerNo = (String) session.getAttribute("CerNo");// 登录名   
    String JianliName = (String) session.getAttribute("JianliName");// 角色ID   
    if (CerNo == null || JianliName == null) {   
        return mapping.findForward("Fail");   
    }   
    File business_rpt = new File(getServlet().getServletConfig()   
            .getServletContext().getRealPath("/report/water_jianli.jasper"));   
    JasperPrint jasperPrint = null;   
   
    Map parameters = new HashMap();   
    Jianli jianli = personalDAO.GetJianli(CerNo);   
   
    ReportModel report_user = new ReportModel();   
    report_user.setCol1(jianli.getJianliName());// 姓名   
    report_user.setCol2(jianli.getJianliSex());// 性别   
    report_user.setCol3(jianli.getDegree());// 学历   
    report_user.setCol4("'" + jianli.getSfzh());// 身份证   
    report_user.setCol5(jianli.getTechPost());// 职称   
    report_user.setCol6(jianli.getCerNo());// 证书编号   
    report_user.setCol7(jianli.getJianliMajor());// 监理专业   
    if (jianli.getOrgs() != null) {   
        report_user.setCol8(jianli.getOrgs().getOrgName());// 隶属单位   
    }   
    report_user.setCol10(jianli.getAddress());   
    report_user.setCol11(jianli.getJianliTel());   
    /**  
     * 加入简历的东东  
     */   
    List Ljianli = assistDAO.GetEngineerAssist(CerNo);   
    if (Ljianli != null) {   
        for (int i = 0; i < Ljianli.size(); i++) {   
            Assistant assist = (Assistant) Ljianli.get(i);   
            String begindate = "";   
            String enddate = "";   
            SimpleDateFormat sfDate = new SimpleDateFormat("yyyy/MM/dd");   
            if (assist.getBegindate() != null) {   
                begindate = sfDate.format(assist.getBegindate());   
            }   
            if (assist.getEnddate() != null) {   
                enddate = sfDate.format(assist.getEnddate());   
            }      
            String deptname = (assist.getAtdeptname() == null)?"":assist.getAtdeptname();   
            String zewu = (assist.getAssistname() == null)?"":assist.getAssistname();   
            String huihua = "开始日期为：" + begindate + ",结束日期为：" + enddate   
                    + "，所在工作单位"+deptname+",担任职务为：" + zewu+"。";   
            if (assist.getOther() != null){   
         &nbs

 

评论

1 楼 weiwei566716 2011-08-26  

你好：你的代码不完整啊