实现将数据导入到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;
    }
}