使用jar包jcifs
import java.io.BufferedOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import jcifs.smb.NtlmPasswordAuthentication;
import jcifs.smb.SmbFile;
import jcifs.smb.SmbFileInputStream;
import jcifs.smb.SmbFileOutputStream;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.XSSFWorkbook;
public class ReadExceUtil {
static Logger logger = Logger.getLogger(ReadExceUtil.class.getName());
//读取excel
private static List<List> readExcel(SmbFile remoteFile,int at){
String path = remoteFile.getPath();
Workbook wb = null;
try {
if(path==null || remoteFile==null || !remoteFile.isFile()){
return null;
}
if(path.endsWith(".xls")){
wb = new HSSFWorkbook(new SmbFileInputStream(remoteFile));
}else if(path.endsWith(".xlsx")){
wb = new XSSFWorkbook(new SmbFileInputStream(remoteFile));
}else{
wb = null;
}
return readExcel(wb,at);
} catch (Exception e) {
logger.info(remoteFile.getName()+" analysis error:"+path+"\n "+e.getMessage());
e.printStackTrace();
}
return null;
}
@SuppressWarnings("unchecked")
public static List<List> readExcel(Workbook wb,int at){
if(wb==null){
return null;
}
List<List> obj = new ArrayList();
Row row;
int firstCellIndex,lastCellIndex;
List objv1;
Cell cell;
Sheet sheet = wb.getSheetAt(at);
int firstRowIndex = sheet.getFirstRowNum()+1; //第一行是列名,所以不读
int lastRowIndex = sheet.getLastRowNum();
for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { //遍历行
row = sheet.getRow(rIndex);
if (row != null) {
firstCellIndex = row.getFirstCellNum();
lastCellIndex = row.getLastCellNum();
objv1 = new ArrayList();
for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) { //遍历列
cell = row.getCell(cIndex);
objv1.add(getCellFormatValue(cell));
}
obj.add(objv1);
}
}
return obj;
}
private static NtlmPasswordAuthentication init(){
String domain = PropUtil.getKeyValue(Constants.share,Constants.domain);
String name = PropUtil.getKeyValue(Constants.share,Constants.name);
String pass = PropUtil.getKeyValue(Constants.share,Constants.pass);
logger.info("connect shart path");
NtlmPasswordAuthentication auth = new NtlmPasswordAuthentication(domain, name,pass);
return auth;
}
private static Object getCellFormatValue(Cell cell){
Object cellValue = null;
if(cell!=null){
//判断cell类型
switch(cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:{
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA:{
//判断cell是否为日期格式
if(DateUtil.isCellDateFormatted(cell)){
//转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
}else{
//数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING:{
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
}else{
cellValue = "";
}
return cellValue;
}
public static List<List> connectSmb(String shareDirectory){
return connectSmb(shareDirectory,0);
}
public static List<List> connectSmb(String shareDirectory,int at){
if(shareDirectory==null){
logger.error("file path is null");
return null;
}
try {
logger.info("shart file path:"+shareDirectory);
NtlmPasswordAuthentication auth = init();
SmbFile remoteFile = new SmbFile(shareDirectory, auth);
remoteFile.connect();//尝试连接
logger.info("connect shart path success");
return readExcel(remoteFile,at);
} catch (Exception e) {
logger.error("shart file error:"+e.getMessage());
throw new RuntimeException(e);
}
}
public static <T> List<T> set(Class<T> classz,List<List> obj,String...field){
try {
List<T> arr=new ArrayList();
for(List o: obj){
T t = classz.newInstance();
for(int i=0;i<o.size();i++){
Field declaredField = classz.getDeclaredField(field[i]);
declaredField.setAccessible(true);
declaredField.set(classz, o.get(i));
}
arr.add(t);
}
return arr;
} catch (InstantiationException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
//向共享目录上传文件
public static void smbPut(String remoteUrl,String fileName,String val) {
OutputStream out = null;
try {
logger.info("Upload path<>"+remoteUrl+fileName);
NtlmPasswordAuthentication auth = init();
SmbFile remoteFile = new SmbFile(remoteUrl, auth);
boolean flag = false;
if(!remoteFile.exists()){
remoteFile.mkdirs();
}else {
SmbFile[] files = remoteFile.listFiles();
for (SmbFile sf : files) {
if(sf.getName().equals(fileName)){
remoteFile=sf;
flag=true;
}
}
}
if(!flag){
remoteFile = new SmbFile(remoteUrl+fileName, auth);
if(!remoteFile.exists()){
remoteFile.createNewFile();
}
}
out = new BufferedOutputStream(new SmbFileOutputStream(remoteFile));
out.write(val.getBytes());
out.flush();
logger.info("upload success");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(out!=null){
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}