public void UpLoadFileToFtp(DataTable dt, string sheetName, string[] headerData, string[] rowPointers, string filename, string ftpFilePath)
{
var uri = ftpFilePath + filename;
var fs = new FileStream(uri, FileMode.Create);
RegisterLicense();
var workbook = new Workbook();
var worksheet = workbook.Worksheets[0];
worksheet.Name = sheetName;
ExcelUtil.CreateWorksheet("", worksheet, worksheet.Name, dt.AsEnumerable().AsQueryable(), headerData, rowPointers);
workbook.Save(fs, SaveFormat.Xlsx);
fs.Close();
}
public void Excute()
{
var ds = SettingManager.Execute();
#region 判断是否下载(不下载的条件：1、只有一张表，且是空表，2、有多张表，都为空)
if (SettingManager.TableMappings.Count() == 1 && ds.Tables[0].Rows.Count == 0)
{
return;
}
bool isAllNull = true;
foreach (var mapping in SettingManager.TableMappings)
{
if (ds.Tables[mapping.Destination].Rows.Count > 0)
{
isAllNull = false;
}
}
if (isAllNull)
{
return;
}
#endregion
SyncFile.RegisterLicense();
string filepath = SettingManager.FileSavePath + SettingManager.FileName.Replace("{fileDateFormat}", DateTime.Now.ToString("yyyyMMdd"));
var fs = new FileStream(filepath, FileMode.Create);
var workbook = new Workbook();
var i = 0;
foreach (var mapping in SettingManager.TableMappings)
{
var arrHeard = new string[mapping.ColumnMappings.Count()];
var arrRow = new string[mapping.ColumnMappings.Count()];
var j = 0;
foreach (var column in mapping.ColumnMappings)
{
arrHeard[j] = column.Destination;
arrRow[j] = column.Source;
j++;
}
if (i == 0)
{
workbook.Worksheets.Clear();
}
var worksheet = workbook.Worksheets.Add(mapping.Destination);
ExcelUtil.CreateWorksheet("", worksheet, worksheet.Name, ds.Tables[mapping.Destination].AsEnumerable().AsQueryable(), arrHeard, arrRow);
_log.AppendFormat
("{0} rows have been synchronized from {1} table in DB to {2} sheet in Excel.\r\n",
ds.Tables[mapping.Destination].Rows.Count, mapping.Source, mapping.Destination);
i++;
}
SaveFormat saveFormat;
//Check file format is xls
if (SettingManager.FileName.Split('.').Last().ToUpper() == "XLS")
{
//Set save format optoin to xls
saveFormat = SaveFormat.Excel97To2003;
}
//Check file format is xlsx
else
{
//Set save format optoin to xlsx
saveFormat = SaveFormat.Xlsx;
}
workbook.Save(fs, new XlsSaveOptions(saveFormat));
#region UploadFile
string uri = "ftp://" + SettingManager.HostName + "/" + SettingManager.TargetDir + "/" +
SettingManager.FileName.Replace("{fileDateFormat}", DateTime.Now.ToString("yyyyMMdd"));
FtpWebRequest reqFTP = GetRequest(uri, SettingManager.UserName, SettingManager.Password);
reqFTP.UsePassive = true;
reqFTP.UseBinary = true;
reqFTP.Method = WebRequestMethods.Ftp.UploadFile;
reqFTP.ContentLength = fs.Length;
int buffLength = 2048;
byte[] buff = new byte[buffLength];
int contentLen;
fs.Close();
FileInfo localFile = new FileInfo(filepath);
fs = localFile.OpenRead();
try
{
Stream strm = reqFTP.GetRequestStream();
contentLen = fs.Read(buff, 0, buffLength);
while (contentLen != 0)
{
strm.Write(buff, 0, contentLen);
contentLen = fs.Read(buff, 0, buffLength);
}
strm.Close();
fs.Close();
_log.AppendFormat
("UploadFile:" + SettingManager.FileName.Replace("{fileDateFormat}", DateTime.Now.ToString("yyyyMMdd")) + " . \r\n");
}
catch (Exception ex)
{
throw new Exception(_log.Append("FTP UploadFile failed,because:" + ex.Message).ToString());
}
#endregion
//delete old file ,if custeel retain 20 files
var leftCount = 5;
if (SettingManager.FileSavePath.ToLower().Contains("custeel"))
{
leftCount = 20;
}
localFile.Delete();
_log.Append("Deleted files : " + DeleteFtpFile(leftCount) + ".\n");
}