这两天在做一个项目,需要从ftp上拉取文件到本地进行数据读取,这里的文件主要是.xls、.xlsx、.csv文件(其他文件也可拉取,没有限制),下面分享一下我的思路及代码:
思路:主要分两步,第一步把ftp文件拉取到本地,未登陆时是无法拉取的,所以这里需要ftp地址、用户名、密码进行登陆操作,我写了个FtpHelper工具类用于拉取,第二步把本地文件进行读取这个就比较简单了。
FtpHelper工具类
public class FtpHelper
{
//基本设置
private string ftpServer = @"ftp:XXXXXX"; //目标路径
private string ftpDefaultUrl = "/XX/"; //ftp文件夹
private string ftpUserName = "XX"; //ftp用户名
private string ftpUserPwd = "XXX"; //ftp密码
private string downloadUrl = @"XXXXX"; //下载后的文件存放路径
/// <summary>
/// ftp拉取文件到本地
/// </summary>
public void FtpDownLoad()
{
//需要下载的文件名
string fileName = "2021-01-11detail.csv";
//需要现在的文件在ftp上的完整路径
string fileUploadPath = ftpServer + ftpDefaultUrl;
Uri uri = new Uri(fileUploadPath + "/" + fileName);
//下载后存放的路径
string FileName = Path.GetFullPath(downloadUrl) + Path.DirectorySeparatorChar.ToString() + Path.GetFileName(uri.LocalPath);
//创建文件流
FileStream fs = null;
Stream responseStream = null;
try
{
//判断ftp上是否存在此文件
if (DirectoryIsExist(uri, ftpUserName, ftpUserPwd))
{
//创建一个与FTP服务器联系的FtpWebRequest对象
FtpWebRequest request = (FtpWebRequest)WebRequest.Create(uri);
//设置请求的方法是FTP文件下载
request.Method = WebRequestMethods.Ftp.DownloadFile;
//连接登录FTP服务器
request.Credentials = new NetworkCredential(ftpUserName, ftpUserPwd);
//获取一个请求响应对象
FtpWebResponse response = (FtpWebResponse)request.GetResponse();
//获取请求的响应流
responseStream = response.GetResponseStream();
//判断本地文件是否存在,如果存在,则打开和重写本地文件
if (File.Exists(FileName))
{
fs = File.Open(FileName, FileMode.Open, FileAccess.ReadWrite);
}
else
{
fs = File.Create(FileName);
}
if (fs != null)
{
int buffer_count = 65536;
byte[] buffer = new byte[buffer_count];
int size = 0;
while ((size = responseStream.Read(buffer, 0, buffer_count)) > 0)
{
fs.Write(buffer, 0, size);
}
fs.Flush();
fs.Close();
responseStream.Close();
}
}
}
finally
{
if (fs != null)
fs.Close();
if (responseStream != null)
responseStream.Close();
}
}
/// <summary>
/// 检测目录或文件是否存在
/// </summary>
/// <param name=”pFtpServerIP”></param>
/// <param name=”pFtpUserID”></param>
/// <param name=”pFtpPW”></param>
/// <returns>false不存在,true存在</returns>
public static bool DirectoryIsExist(Uri pFtpServerIP, string pFtpUserID, string pFtpPW)
{
string[] value = GetFileList(pFtpServerIP, pFtpUserID, pFtpPW);
if (value == null)
{
return false;
}
else
{
return true;
}
}
public static string[] GetFileList(Uri pFtpServerIP, string pFtpUserID, string pFtpPW)
{
StringBuilder result = new StringBuilder();
try
{
FtpWebRequest reqFTP = (FtpWebRequest)FtpWebRequest.Create(pFtpServerIP);
reqFTP.UseBinary = true;
reqFTP.Credentials = new NetworkCredential(pFtpUserID, pFtpPW);
reqFTP.Method = WebRequestMethods.Ftp.ListDirectoryDetails;
WebResponse response = reqFTP.GetResponse();
StreamReader reader = new StreamReader(response.GetResponseStream());
string line = reader.ReadLine();
while (line != null)
{
result.Append(line);
result.Append('\n');
line = reader.ReadLine();
}
reader.Close();
response.Close();
return result.ToString().Split('\n');
}
catch
{
return null;
}
}
}
文件读取:
string filePath = (@"XXXXXXXXXXX"); //文件完整物理路径
//判断文件是否存在
if (!System.IO.File.Exists(filePath))
{
return SuccessResult("未轮询到今日文件");
}
DataTable dt = new DataTable();
FileStream fs = new FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
StreamReader sr = new StreamReader(fs, Encoding.Default);
//记录每次读取的一行记录
string strLine = "";
//记录每行记录中的各字段内容
string[] aryLine = null;
string[] tableHead = null;
//标示列数
int columnCount = 0;
//标示是否是读取的第一行
bool IsFirst = true;
//逐行读取CSV中的数据
while ((strLine = sr.ReadLine()) != null)
{
if (IsFirst == true)
{
tableHead = strLine.Split(',');
IsFirst = false;
columnCount = tableHead.Length;
//创建列
for (int i = 0; i < columnCount; i++)
{
DataColumn dc = new DataColumn(tableHead[i]);
dt.Columns.Add(dc);
}
}
else
{
aryLine = strLine.Split(',');
DataRow dr = dt.NewRow();
for (int j = 0; j < columnCount; j++)
{
dr[j] = aryLine[j];
}
dt.Rows.Add(dr);
}
}
if (aryLine != null && aryLine.Length > 0)
{
dt.DefaultView.Sort = tableHead[0] + " " + "asc";
}
sr.Close();
fs.Close();
获取DataTable数据:
dt.Rows[行号]["列名"].ToString();