解析excel表擷取資料,并從遠端伺服器下載下傳和上傳檔案

package com.primecredit.dayend;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.MalformedURLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jcifs.smb.SmbException;
import jcifs.smb.SmbFile;
import jcifs.smb.SmbFileInputStream;
import jcifs.smb.SmbFileOutputStream;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.primecredit.common.LoggerServiceFactory;
import com.primecredit.common.Parameter;
import com.primecredit.connection.ConnectionManager;
import com.primecredit.exception.LoanWebException;
import com.primecredit.los.DAOManager.ProposalManager;

/**
 * @author hongk
 * @date 2017-7-31
 * @description **********************
 */
public class FinOverdueCustomerImageDataController extends BaseController{
	
	protected static Logger logger=LoggerServiceFactory.getInstance().getLogger("dayend");
	
	private ConnectionManager connMgr = null;
	
	public FinOverdueCustomerImageDataController(ConnectionManager connMgr){
		this.connMgr = connMgr;
	}

	/**
	 * 解析excel
	 * @param inputStream
	 * @return
	 */
	public static List<Map<String, String>> readExcel(InputStream inputStream){
		List<Map<String, String>> listMaps = new ArrayList<Map<String,String>>();
		Workbook workbook = null;
		try {
			logger.debug("read Excel begin");
			workbook = new XSSFWorkbook(inputStream);
			Sheet xssfsheet = workbook.getSheetAt(0);
			int rowstart = xssfsheet.getFirstRowNum();  
			int rowEnd = xssfsheet.getLastRowNum();  
			for(int i = rowstart + 1; i <= rowEnd; i++){//行  
				Row row = xssfsheet.getRow(i);  
				if(null == row) continue;  
				int cellStart = row.getFirstCellNum();  
				int cellEnd = row.getLastCellNum();  
				Map<String, String> map = new HashMap<String, String>();
				for(int j = cellStart; j <= cellEnd; j++){//列  
					Cell cell = row.getCell(j);  
					if(null == cell) continue;
					String cellValue = "";
					switch (cell.getCellType()){  
					case XSSFCell.CELL_TYPE_NUMERIC: // 數字  
						cellValue = new DecimalFormat("#").format(cell.getNumericCellValue());//電話号或是長數字類型轉化成純數字
						break;  
					case XSSFCell.CELL_TYPE_STRING: // 字元串  
						cellValue = cell.getStringCellValue();
						break;  
					case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean  
						break;  
					case XSSFCell.CELL_TYPE_FORMULA: // 公式  
						break;  
					case XSSFCell.CELL_TYPE_BLANK: // 空值  
						break;  
					case XSSFCell.CELL_TYPE_ERROR: // 故障  
						break;  
					default:  
						break;
					}  
					if(j == 0){
						map.put("customerName", cellValue);
					}else if(j == 1){
						map.put("loanType", cellValue);
					}else if(j == 2){
						map.put("loanNo", cellValue);
					}else{
						map.put("bankNo", cellValue);
					}
				}
				listMaps.add(map);
			}
			logger.debug("read Excel end");
		} catch (IOException e) {
			e.printStackTrace();
			logger.error("read Excel error:" + e.getMessage(), e);
		}
		return listMaps;
	}
	
	/**
	 * @description **********************
	 * @param mapLists
	 */
	public static void traverseLoanTypeAndLoanNo(List<Map<String, String>> mapLists){
		ConnectionManager connMgr = new ConnectionManager();
		FinOverdueCustomerImageDataController controller = new FinOverdueCustomerImageDataController(connMgr);
		for (Map<String, String> map : mapLists) {
			String customerName = map.get("customerName");
			String loanType = map.get("loanType");
			String loanNo = map.get("loanNo");
			String proposalNo = controller.getProposalNobyLoanTypeAndLoanNo(loanType, loanNo);
			String bankNo = map.get("bankNo");
			String IMAGEDATADir = "smb://muser:[email protected]/BatchesPro/" + proposalNo + "_" + customerName + "/";//合同所在目錄
			String uploadPath = "smb://muser:[email protected]/recover/M7/M7/" + bankNo;//上傳目錄
			Collection<? extends SmbFile> fileList;
			try {
				fileList = list(new SmbFile(IMAGEDATADir), true);//擷取對應目錄下的檔案
				if(fileList != null && fileList.size() > 0){
					for(SmbFile file:fileList){
						if(file.getName().contains(Parameter.IMAGEDATA)){
							String imageDataPath = file.getPath();
							UploadIMAGEDATA(imageDataPath , file.getName(), uploadPath);//從檔案伺服器中讀取小額合同,上傳到财務公共盤
						}
					}
				}
			} catch (SmbException e) {
				e.printStackTrace();
			} catch (MalformedURLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * @description **********************
	 * @param loanType
	 * @param loanNo
	 * @return
	 */
	public String getProposalNobyLoanTypeAndLoanNo(String loanType, String loanNo){
		String proposalNo = "";
		ProposalManager pManager = new ProposalManager(connMgr);
		try {
			logger.debug("get ProposalNo by LoanType And LoanNo begin");
			proposalNo = pManager.getProposalNobyLoanTypeAndLoanNo(loanType, loanNo);
			logger.debug("get ProposalNo by LoanType And LoanNo end");
		} catch (LoanWebException e) {
			logger.error("get ProposalNo by LoanType And LoanNo:" + e.getMessage(), e);
			e.printStackTrace();
		}
		return proposalNo;
	}
	
	/**
	 * @description **********************
	 * 通過SMB協定上傳檔案到遠端伺服器
	 * @param imageDataFile
	 * @param fullName
	 * @param uploadPath
	 * @return
	 */
	public static boolean UploadIMAGEDATA(String imageDataPath,String fileName,String uploadPath){
		boolean flag = false;
		InputStream is = null;
		OutputStream os = null;
		try {
			logger.debug(fileName + "貸款合同開始上傳.");
			SmbFile imageDataFile = new SmbFile(imageDataPath);
			imageDataFile.connect();
			is = new BufferedInputStream(new SmbFileInputStream(imageDataFile));
			//判斷檔案夾是否存在
			SmbFile remoteFile = new SmbFile(uploadPath);
			if(!remoteFile.exists()){
				remoteFile.mkdirs();
			}
			remoteFile.connect();
			SmbFile uploadFile = new SmbFile(remoteFile + File.separator + fileName);
			os = new BufferedOutputStream(new SmbFileOutputStream(uploadFile));
			byte[] buffer = new byte[1024];
			int len = 0;
			while((len = is.read(buffer)) != -1){
				os.write(buffer, 0, len);
			}
			os.flush();
			flag = true;
			logger.debug(fileName + "貸款合同上傳成功!");
		} catch (FileNotFoundException e) {
			flag = false;
			logger.error("File Not Found Exception" + e.getMessage(), e);
		} catch (IOException e) {
			flag = false;
			e.printStackTrace();
			logger.error("IO Exception" + e.getMessage(), e);
		}finally{
			try {
				if(os != null){
					os.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				if(is != null){
					is.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	/**
	 * 擷取對應目錄下所有的檔案
	 * @param dir
	 * @param recursive
	 * @return
	 * @throws SmbException 
	 */
	public static Collection<? extends SmbFile> list(SmbFile dir, boolean recursive) throws SmbException{
		List<SmbFile> fileList = new ArrayList<SmbFile>();
		if(dir.isFile()){
			fileList.add(dir);
		}else if(dir.isDirectory()){
			SmbFile[] tempList = dir.listFiles();
			for(SmbFile temp: tempList){
				if(recursive){
					if(temp.isFile()){
						fileList.add(temp);
					}else if(temp.isDirectory()){
						fileList.addAll(list(temp, recursive));
					}
				}else{
					fileList.add(temp);
				}
			}
		}
		return fileList;
	}
	
	public static void main(String[] args) {
		try {
			InputStream is = new FileInputStream(new File("D:/M7_IMAGEDATA/QH_M7_INFO_20179-201710.xlsx"));
			List<Map<String, String>> mapLists = readExcel(is);//解析excel
			traverseLoanTypeAndLoanNo(mapLists);//從檔案伺服器讀取檔案,上傳到遠端伺服器指定檔案夾
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
	}
}