解析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:muser!17@192.168.21.220/BatchesPro/" + proposalNo + "_" + customerName + "/";//合同所在目录String uploadPath = "smb://muser:muser!17@192.168.21.16/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);//解析exceltraverseLoanTypeAndLoanNo(mapLists);//从文件服务器读取文件,上传到远程服务器指定文件夹} catch (FileNotFoundException e) {e.printStackTrace();}}}