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"); }