public void GetCellsByFirstSheet(string fileName, StringBuilder sb)
{
SyncFile.RegisterLicense();
ModifyFile(fileName, sb);
Workbook v = new Workbook(fileName);
using (OracleConnection conn = new OracleConnection(Connectionstr))
{
conn.Open();
foreach (Worksheet st in v.Worksheets)
{
Cells cells = st.Cells;
try
{
ImportDataTable(cells, st.Name, conn);
sb.AppendFormat("[{0} import success,totla:{1}]", st.Name, cells.Rows.Count - 1);
}
catch (Exception e)
{
throw new Exception(sb.AppendFormat("[{0} read failed,because:{1}]", st.Name, e.Message).ToString());
}
}
conn.Close();
}
}
public void GetCellsByFirstSheet(Uri serverUri, string user, string password, string fileName, StringBuilder sb, string reDate)
{
SyncFile.RegisterLicense();
// The serverUri parameter should start with the ftp:// scheme.
if (serverUri.Scheme != Uri.UriSchemeFtp)
{
return;
}
// Get the object used to communicate with the server.
var request = new WebClient {
Credentials = new NetworkCredential(user, password)
};
//var request = new WebClient();
request.DownloadFile(serverUri.ToString(), fileName);
var v = new Workbook(fileName);
try
{
var productTable = ExportWorkSheetToProduct(v.Worksheets[0].Cells, reDate);
ExecuteWrite(productTable, "vav.sp_UpdateLufaxProduct");
sb.AppendFormat("[{0} Product Export success,totla:{1}]", fileName, v.Worksheets[0].Cells.Rows.Count - 1);
var detailTable = ExportWorkSheetToDetail(v.Worksheets[1].Cells);
ExecuteWrite(detailTable, "vav.sp_UpdateLufaxDetail");
sb.AppendFormat("[ Detail Export success,totla:{0}]", v.Worksheets[1].Cells.Rows.Count - 1);
}
catch (Exception e)
{
sb.AppendFormat("[{0} Export failed,because:{1}]", fileName, e.Message);
throw;
}
}
public void GetCellsByFirstSheet(string fileName, StringBuilder sb)
{
SyncFile.RegisterLicense();
ModifyFile(fileName, sb);
Workbook v = new Workbook(fileName);
foreach (Worksheet st in v.Worksheets)
{
if (st.Name.IndexOf("week", StringComparison.Ordinal) == -1)
{
continue;
}
Cells cells = st.Cells;
try
{
int total;
ImportDataTable(cells, out total);
sb.AppendFormat("[Table CofeedWeek import success,Rows:{1},No:{0}]\n" + "\r\n", st.Name, total);
}
catch (Exception e)
{
sb.AppendFormat("[Table CofeedWeek import failed,because {0}:{1}]\n" + "\r\n", st.Name, e.Message);
throw e;
}
}
}
public void ImportTheWholeExcel()
{
SyncFile.RegisterLicense();
if (FileName.EndsWith("toThomsonReuters.xlsx"))
{
metals_smm_shanghai(1);
}
else if (FileName.EndsWith("路透.xlsx"))
{
metals_smm_shanghai(0);
}
}
public void ImportTheWholeExcel(StringBuilder str)
{
SyncFile.RegisterLicense();
TotalOperatingRate();
DayProduction();
MonthProduction();
Profit();
TotalStock();
GasolineIndex();
DieselIndex();
GasolineValuation();
DieselValuation();
//DeviceStatistics();
str.Append(_log);
}
public void ProcessData(string file, StringBuilder log)
{
SyncFile.RegisterLicense();
ModifyFile(file, log);
var v = new Workbook(file);
foreach (Worksheet st in v.Worksheets)
{
try
{
BuildUpdateTROilInventorySql(st, "cne.TROilInventory");
}
catch (Exception e)
{
log.AppendFormat("Table cne.TROilInventory Sync Data failed, because:{0}", e.Message);
}
}
}
public void GetCellsByFirstSheet(string fileName, StringBuilder sb)
{
SyncFile.RegisterLicense();
ModifyFile(fileName, sb);
Workbook v = new Workbook(fileName);
foreach (Worksheet st in v.Worksheets)
{
Cells cells = st.Cells;
try
{
sb.Append("Sheet:" + st.Name + "\n");
ImportDataTable(cells, sb);
}
catch (Exception e)
{
sb.AppendFormat("[LongZhong_Yield import failed,because:{0}]", e.Message);
throw;
}
}
}
public void GetCellsBy(string fileName, StringBuilder sb)
{
SyncFile.RegisterLicense();
ModifyFile(fileName, sb);
Workbook v = new Workbook(fileName);
foreach (Worksheet st in v.Worksheets)
{
try
{
if (st.Name == "Sheet1")
{
continue;
}
var count = Import2Dt(st);
sb.AppendFormat("[{0} read complete,totle:{1}]", st.Name, count);
}
catch (Exception e)
{
throw new Exception(sb.AppendFormat("[{0} read failed,because:{1}]", st.Name, e.Message).ToString());
}
}
}
public void GetCellsByFirstSheet(string fileName, StringBuilder sb)
{
SyncFile.RegisterLicense();
ModifyFile(fileName, sb);
Workbook v = new Workbook(fileName);
//v.Open(fileName ,FileFormatType .Excel97To2003);
// v.FileName = fileName;
// Workbook sheet = new Workbook(fileName);
foreach (Worksheet st in v.Worksheets)
{
Cells cells = st.Cells;
try
{
ImportDataTable(cells);
sb.AppendFormat("[{0} import success,Rows:{1}]\n" + "\r\n", DateTime.ParseExact(st.Name, "yyyyMMdd", null).ToString("yyyy-MM-dd"), cells.Rows.Count - 1);
}
catch (Exception e)
{
sb.AppendFormat("[{0} import failed,because:{1}]\n" + "\r\n", DateTime.ParseExact(st.Name, "yyyyMMdd", null).ToString("yyyy-MM-dd"), e.Message);
throw;
}
}
}
public void GetCellsByFirstSheet(string fileName, StringBuilder sb)
{
SyncFile.RegisterLicense();
ModifyFile(fileName, sb);
Workbook v = new Workbook(fileName);
//v.Open(fileName ,FileFormatType .Excel97To2003);
// v.FileName = fileName;
// Workbook sheet = new Workbook(fileName);
foreach (Worksheet st in v.Worksheets)
{
Cells cells = st.Cells;
try
{
int total;
ImportDataTable(cells, out total);
sb.AppendFormat("[Table CofeedProduct import success,Rows:{1},No:{0}]\n" + "\r\n", st.Name, total);
}
catch (Exception e)
{
sb.AppendFormat("[Table CofeedProduct import failed,because {0}:{1}]\n" + "\r\n", st.Name, e.Message);
}
}
}
public void ProcessData(string file, StringBuilder log)
{
SyncFile.RegisterLicense();
ModifyFile(file, log);
var v = new Workbook(file);
foreach (Worksheet st in v.Worksheets)
{
try
{
int insertRows;
switch (st.Name)
{
case "废钢供应和需求量":
BuildScrapSteelSupplyDemand(st, "ScrapSteelSupplyDemand", out insertRows);
log.Append("Table ScrapSteelSupplyDemand insertRows:" + insertRows + " \r\n");
break;
case "废钢基地库存":
BuildFormalSheetData(st, "ScrapSteelBaseInventory", 5, 2, out insertRows);
log.Append("Table ScrapSteelBaseInventory insertRows:" + insertRows + " \r\n");
break;
case "矿石保税区库存":
BuildFormalSheetData(st, "OreBondedAreaInventory", 5, 2, out insertRows);
log.Append("Table OreBondedAreaInventory insertRows:" + insertRows + " \r\n");
break;
case "旬度产量":
BuildTenDaysPeriodOutputData(st, "TenDaysPeriodOutput", out insertRows);
log.Append("Table TenDaysPeriodOutput insertRows:" + insertRows + " \r\n");
break;
case "重点月产量":
BuildFormalSheetData(st, "KeyMonthOutput", 4, 1, out insertRows);
log.Append("Table KeyMonthOutput insertRows:" + insertRows + " \r\n");
break;
case "冷热轧及中板排产":
BuildFormalSheetData(st, "ColdHotRolledSheet", 4, 1, out insertRows);
log.Append("Table ColdHotRolledSheet insertRows:" + insertRows + " \r\n");
break;
case "钢厂库存":
BuildFormalSheetData(st, "SteelMillInventory", 4, 1, out insertRows);
log.Append("Table SteelMillInventory insertRows:" + insertRows + " \r\n");
break;
case "行业财务":
BuildIndustryFinanceData(st, "IndustryFinance", out insertRows);
log.Append("Table IndustryFinance insertRows:" + insertRows + " \r\n");
break;
case "中厚板产销存":
BuildFormalSheetData(st, "HeavyAndMediumPlate", 5, 2, out insertRows);
log.Append("Table HeavyAndMediumPlate insertRows:" + insertRows + " \r\n");
break;
case "内矿开工率":
BuildFormalSheetData(st, "RateOfOperation", 4, 1, out insertRows);
log.Append("Table RateOfOperation insertRows:" + insertRows + " \r\n");
break;
}
}
catch (Exception e)
{
log.AppendFormat("Custeel Reuters Unnormalized Data[{0} read failed,because:{1}]", st.Name, e.Message);
throw;
}
}
}
public void ImportTheWholeExcel(string filename, StringBuilder sb)
{
SyncFile.RegisterLicense();
var workbook = new Workbook(filename); //工作簿
var ws = workbook.Worksheets[0];
var arr = new ArrayList();
var isContains = new ArrayList();
if (ws != null)
{
int maxId;
int insertRows = 0;
using (var con = new OracleConnection(Connectionstr))
{
var cmd = new OracleCommand("SELECT distinct ReportDate FROM NationalBureau ", con);
var da = new OracleDataAdapter(cmd);
var ds = new DataSet();
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
isContains.Add(ds.Tables[0].Rows[i][0]);
}
}
using (var con = new OracleConnection(Connectionstr))
{
var cmd = new OracleCommand("SELECT max(ID) FROM NationalBureau ", con);
var da = new OracleDataAdapter(cmd);
var ds = new DataSet();
da.Fill(ds);
maxId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
var dtColumn = ws.Cells.ExportDataTable(0, 0, 1,
ws.Cells.MaxColumn + 1);
var productName = dtColumn.Rows[0][0].ToString().Trim();
for (int i = 2; i < ws.Cells.MaxColumn; i++)
{
if (dtColumn.Rows[0][i].ToString() != "")
{
arr.Add(dtColumn.Rows[0][i]);
}
}
var dt = ws.Cells.ExportDataTable(1, 0, ws.Cells.MaxRow + 1,
ws.Cells.MaxColumn + 1);
using (var con = new OracleConnection(Connectionstr))
{
con.Open();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][0].ToString() != "" && dt.Rows[i][1].ToString() != "")
{
for (int j = 0; j < arr.Count; j++)
{
if (isContains.Contains(arr[j]))
{
continue;
}
var reportDate = Convert.ToDateTime(arr[j]).ToString("dd-MMM-yyyy");
var proAreas = dt.Rows[i][0].ToString().Split(':');
var area = proAreas[0];
var collectName = proAreas[1];
var unit = dt.Rows[i][1];
decimal productPrice;
if (dt.Rows[i][2 + j] != null)
{
decimal d;
if (Decimal.TryParse(dt.Rows[i][2 + j].ToString(), out d))
{
if (d == 0)
{
continue;
}
productPrice = d;
}
else
{
continue;
}
}
else
{
continue;
}
try
{
maxId++;
insertRows++;
string operationSql = "INSERT INTO NATIONALBUREAU values(" + maxId + ",'" + productName + "','" + area + "','" + collectName + "','" + unit + "'," + productPrice + ",'" + reportDate + "',sysdate) ";
var cmd = new OracleCommand(operationSql, con);
cmd.ExecuteNonQuery();
}
catch (OracleException e)
{
con.Close();
throw new Exception(e.Message);
}
}
}
else if (dt.Rows[i][0].ToString() != "" && dt.Rows[i][1].ToString() == "")
{
productName = dt.Rows[i][0].ToString().Trim();
}
}
con.Close();
}
sb.Append("Table NATIONALBUREAU insert rows: " + insertRows + ";\r\n");
}
}
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");
}
