实现将数据导入到excel中或者从excel中将数据导入到数据库

/** 
* 将数据写入Excel中 
*/ 
   public String exportExcel(String filepath,String sheetName,long clientID)throws Exception{ 
String returnPath = ""; 
long thistime = new Date().getTime(); 
OutputStream os; 
Connection con = null; 
  ResultSet rs = null; 
  PreparedStatement ps = null; 
  try{ 
// 生成文件 
os = new FileOutputStream(filepath + sheetName + "-" + thistime + ".xls"); 
            WritableWorkbook book = Workbook.createWorkbook(os); 
// sheet名称 
WritableSheet sheet = book.createSheet(sheetName, 0); 
    //设置格式 
WritableFont wf = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE); 
WritableCellFormat cellFormat=new WritableCellFormat(wf); 

//设置为文本格式 
NumberFormat nf = new NumberFormat("#00"); 
WritableCellFormat  cf = new WritableCellFormat(nf); 
//设置为金额 
NumberFormat nf2 = new NumberFormat("#,##0.00"); 
WritableCellFormat wcf = new WritableCellFormat(nf2); 

cellFormat.setAlignment(Alignment.CENTRE); 
cellFormat.setBackground(jxl.format.Colour.SKY_BLUE);//设置单元格的颜色 
    
    sheet.setColumnView(0, 20); 
sheet.setColumnView(1, 30); 
sheet.setColumnView(2, 30); 
sheet.setColumnView(3, 20); 
sheet.setColumnView(4, 20); 
WritableCellFormat cellFormat1=new WritableCellFormat(); 
    cellFormat1.setAlignment(Alignment.CENTRE); 
    
   
    //设置列 
    Label four_1 = new Label(0, 0, "合同号"); 
four_1.setCellFormat(cellFormat); 
//four_1.setCellFormat(cf); 
sheet.addCell(four_1); 

Label four_2 = new Label(1, 0, "收款方账户"); 
four_2.setCellFormat(cellFormat); 
//four_2.setCellFormat(cf); 
sheet.addCell(four_2); 

Label four_3 = new Label(2, 0, "付款方账户"); 
four_3.setCellFormat(cellFormat); 
//four_3.setCellFormat(cf); 
sheet.addCell(four_3); 




Label four_4 = new Label(3, 0, "金额"); 
four_4.setCellFormat(cellFormat); 
//four_4.setCellFormat(wcf); 
sheet.addCell(four_4); 

Label four_5 = new Label(4, 0, "摘要"); 
four_5.setCellFormat(cellFormat); 
//four_5.setCellFormat(cf); 
sheet.addCell(four_5); 
            int x=0; 
                String sql="select * from  SETT_CONSIGNRECEIVESET c where c.npayeeclientid="+clientID; 
        con = Database.getConnection(); 
    ps=con.prepareStatement(sql); 
    rs = ps.executeQuery(); 
    while(rs.next()){ 
x++; 
Label label1=new Label(0,x,rs.getString("SCONTRACTCODE"),cf); 
sheet.addCell(label1); 

Label label2=new Label(1,x,NameRef.getAccountNum(rs.getLong("NPAYEEACCTID")),cf); 
sheet.addCell(label2);

Label label3=new Label(2,x,NameRef.getAccountNum(rs.getLong("NPAYERACCTID")),cf); 
sheet.addCell(label3); 

Label label4=new Label(3,x,"",wcf); 
sheet.addCell(label4); 

Label label5=new Label(4,x,"",cf); 
sheet.addCell(label5); 

} 
book.write(); 
book.close(); 
returnPath=filepath + sheetName + "-" + thistime + ".xls"; 
} catch (Exception e) { 
// TODO Auto-generated catch block 
e.printStackTrace(); 
}finally 
{ 
    try 
{ 
if (rs != null) 
{ 
rs.close(); 
rs = null; 
} 
if (ps != null) 
{ 
ps.close(); 
ps = null; 
} 
if (con != null) 
{ 
con.close(); 
con = null; 
} 
} 
catch (Exception e) 
{ 
; 
}   

} 
return returnPath; 
} 

/** 
* 从Excel中导入数据 
* @param mySmartUpload 
* @param uploadPath 
* @param sessionMng 
* @return map 
* @throws Exception 
*/ 
public Map importExcel(SmartUpload mySmartUpload, String uploadPath,SessionOB sessionMng) throws Exception { 
Vector vector = new Vector(); 
Map map = new HashMap(); 
        Vector returnVector = new Vector(); 
String strAdd = "";// 每次从上传文件中读到的一个单元。 
short index = 0; 
boolean bIsValid = false; 
File myfile = mySmartUpload.getFiles().getFile(0); 
myfile.saveAs(uploadPath + "special/consignreceive/"+myfile.getFileName()); 
Connection conn = null; 
FileInputStream fis = new FileInputStream(Env.UPLOAD_PATH+"special/consignreceive/"+myfile.getFileName()); 
ConsignReceiveInfo consignReceiveInfo = null; 


if(myfile.getFileName().indexOf(".xls") < 0) { 
throw new Exception("选择文档与模板文件不一致，请检查"); 
} 

try { 
            HSSFWorkbook workbook = new HSSFWorkbook(fis); 
HSSFSheet sheet = null; 
HSSFRow row = null; 
HSSFCell cell = null; 
            if (workbook != null) { 
sheet = workbook.getSheetAt(0); 
} 

if (sheet == null) { 
throw new Exception("不能导入空的Excel文件"); 
} 

if (sheet != null) { 
row = sheet.getRow(1); 
} 

if(row == null){ 
throw new Exception("不能导入空的Excel文件"); 
} 

for (int j=1; row!=null; j++, row=sheet.getRow(j)) { 
bIsValid = true; 

consignReceiveInfo = new ConsignReceiveInfo(); 
            //1.合同号   2.收款方账号  3.付款方账号  4.金额  5.摘要 
long subTypeId = -1; 
for(index = 1; index <= 5; index++){ 
strAdd = ""; 
cell = row.getCell((short) (index - 1)); 
if (cell != null) { 
// 在Excel中的类型是文本、常规 
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { 
strAdd = cell.getStringCellValue(); 
} 
// 在Excel中的类型是数值 
else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { 
strAdd = String.valueOf(cell.getNumericCellValue()); 
} 
System.out.println(strAdd); 
} 
if (strAdd == null) { 
strAdd = ""; 
} 

if(index==1){ 
consignReceiveInfo.setSContractCode(strAdd); 
                     } 



if (index == 2) { 
consignReceiveInfo.setNPayeeAcctID((Long)NameRef.getByNoFromSett(strAdd).get("id")); 
consignReceiveInfo.setNPayeeClientID((Long)NameRef.getByNoFromSett(strAdd).get("nclientid")); 
                     } 

if(index==3){ 
consignReceiveInfo.setNPayerAcctID((Long)NameRef.getByNoFromSett(strAdd).get("id")); 
consignReceiveInfo.setNPayerClientID((Long)NameRef.getByNoFromSett(strAdd).get("nclientid")); 
                     } 

if (index == 4) { 
if (strAdd.matches("^\\s*([1-9]\\d{0,12}|0)(\\.(\\d){1,2})?\\s*$")) { 
consignReceiveInfo.setMAmount(Double.valueOf(strAdd.replaceAll(",", "")).doubleValue()); 

} 
else { 
UpLoanReturnInfo upLoanReturnInfo = new UpLoanReturnInfo(); 
upLoanReturnInfo.setIsOk(true); 
upLoanReturnInfo.setPositionCol(index); 
upLoanReturnInfo.setPositionRow(j+1); 
upLoanReturnInfo.setReason("金额格式不正确"); 
returnVector.addElement(upLoanReturnInfo); 
} 
} 

if(index==5){ 
UpLoanReturnInfo upLoanReturnInfo = new UpLoanReturnInfo(); 
if(strAdd != null && !strAdd.equals("") && strAdd.length() > 0){ 
if(NameRef.getNAbstractIDByName(strAdd)>0){ 
consignReceiveInfo.setNAbstractID(NameRef.getNAbstractIDByName(strAdd)); 

}else{ 
upLoanReturnInfo.setIsOk(true); 
upLoanReturnInfo.setPositionCol(index); 
upLoanReturnInfo.setPositionRow(j+1); 
upLoanReturnInfo.setReason("摘要不存在,请检查!"); 
returnVector.addElement(upLoanReturnInfo); 
} 

                        }else{ 
                            upLoanReturnInfo.setIsOk(true); 
upLoanReturnInfo.setPositionCol(index); 
upLoanReturnInfo.setPositionRow(j+1); 
upLoanReturnInfo.setReason("摘要填写不正确,请检查!"); 
returnVector.addElement(upLoanReturnInfo); 
                         } 
                    } 
} 

consignReceiveInfo.setNOfficeID(sessionMng.m_lOfficeID); 
consignReceiveInfo.setNCurrencyID(sessionMng.m_lCurrencyID); 
consignReceiveInfo.setDTInput(Env.getSystemDate()); 
consignReceiveInfo.setNInputUserID(sessionMng.m_lUserID); 
//consignReceiveInfo.setNPayeeClientID(sessionMng.m_lClientID); 
consignReceiveInfo.setNStatus(OBConstant.SettInstrStatus.SAVE); 
consignReceiveInfo.setNTransType(31); 
vector.add(consignReceiveInfo); 

               } 


//判断格式是否正确  如果正确则插入数据库 
if(returnVector.size()==0){ 
int length=vector.size(); 
ConsignReceiveInfo[] infos=new ConsignReceiveInfo[length]; 
if (length > 0) { 
for (int i = 0; i < length; i++) { 
ConsignReceiveInfo info = (ConsignReceiveInfo) vector.get(i); 
infos[i] = info; 
} 
//插入操作 
} 
删除已经插入的操作 
conn = Database.getConnection(); 
conn.setAutoCommit(false); 
ConsignReceiveDao consignReceiveDao=new ConsignReceiveDao(conn); 
for(int i=0;i<infos.length;i++){ 
ConsignReceiveInfo dataEntity = infos[i]; 
consignReceiveDao.add(dataEntity); 
} 
conn.commit(); 
conn.close(); 
//consignReceiveDao.importFromExcel(infos); 
} else { 
map.put("returnVector", returnVector); 
} 
      }catch(Exception e){ 
e.printStackTrace(); 
conn.rollback(); 
} 
           return map; 
    }
}