public void OpenXMLReadValues_PortefeuilleModele() { DataSet ds = new DataSet("PTF_MODELE"); SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_RW"); /* using (var reader = new OpenXMLDataReader(new StreamReader(@"Portefeuille modèle.xlsm"), null, true, "modele_€", "J7", "R94",null)) * { * * OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(reader); */ string model_path1 = @"G:\,FGA Front Office\02_Gestion_Actions\01_MODELES\MODELE GARP\Portefeuille modèle.xlsm"; string model_path2 = @"C:\DATA\Portefeuille modèle_20131125.xlsm"; string model_path3 = @"C:\DATA\Portefeuille modèle.xlsm"; OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(model_path1, "modele_€", "J7", "R94", true); //ITableMapping mapping = adapter.TableMappings.Add("Table", "PTF_FGA"); //mapping.ColumnMappings.Add("CLOSE", "close"); //mapping.ColumnMappings.Add("INDEX", "ponderationIndice"); adapter.AddColumnMapping("PTF_FGA", "COMPANY", "Libelle_Titre"); adapter.AddColumnMapping("PTF_FGA", "QUANTITY", "quantite", System.Type.GetType("System.Double")); adapter.AddColumnMapping("PTF_FGA", "€CLOSE", "coursclose", System.Type.GetType("System.Double")); adapter.AddColumnMapping("PTF_FGA", "CRNCY", "Devise_Titre"); adapter.FillColumnWithCell("Dateinventaire", "Q5", System.Type.GetType("System.DateTime")); adapter.FillColumnWithCell("Libelle_PtfSource", "G3"); adapter.FillColumnWithValue("Groupe", "MODEL_ACT"); adapter.FillColumnWithValue("Compte", "ACT001"); adapter.FillColumnWithValue("ISIN_Ptf", "MODEL_ACT001"); adapter.FillColumnWithValue("Coupon_Couru", 0d); adapter.FillColumnWithValue("Dateintegration", DateTime.Now); adapter.Fill(ds); DataTable dt = ds.Tables["PTF_FGA"]; // conversion de colonne pour mettre dans la BDD // DataColumn.Expression pour remplir des colonnes //dt.Columns.Add("quantite", System.Type.GetType("System.Double"), "CONVERT( quantiteSource, System.Double )"); //dt.Columns.Add("coursclose", System.Type.GetType("System.Double"), "CONVERT( courscloseSource, System.Double )"); dt.Columns.Add("Libelle_Ptf", System.Type.GetType("System.String"), "TRIM(Libelle_PtfSource)"); dt.Columns.Add("Valeur_Boursiere", System.Type.GetType("System.Double"), "quantite*coursclose"); dt.Columns.Add("code_Titre", System.Type.GetType("System.String"), "TICKER + ' EQUITY'"); //dt.Columns.Add("Dateinventaire", System.Type.GetType("System.DateTime"), "CONVERT(DateinventaireSource,System.DateTime)"); s.bulkcopyData(dt); }
public static void ACTION_PROCESS_BulkCopy_FACTSET_MODELE_CLASSIFICATION_2(string date, string filepath) { DataSet ds = new DataSet("DATA_FACTSET"); MSDBIntegration s = new MSDBIntegration(destinationConnection: ENV); OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(filepath, "Modele Classification", "B1", "V926", true); adapter.AddColumnMapping("DATA_FACTSET", "MXEU", "MXEU", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "MXUSLC", "MXUSLC", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "MXEM", "MXEM", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "MXEUM", "MXEUM", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "MXFR", "MXFR", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100001", "6100001", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100004", "6100004", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100030", "6100030", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100033", "6100033", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100063", "6100063", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "AVEURO", "AVEURO", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "AVEUROPE", "AVEUROPE", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100026", "6100026", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100062", "6100062", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100002", "6100002", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100024", "6100024", System.Type.GetType("System.Double")); adapter.FillColumnWithValue("DATE", date); adapter.Fill(ds); DataTable dt = ds.Tables["DATA_FACTSET"]; s.bulkcopyData(dt); }
public void ACTION_PROCESS_BulkCopy_FACTSET_MODELE_CLASSIFICATION_2() { string date = "10/02/2014"; //string filepath1 = @"C:\FGA_SOFT\DEVELOPPEMENT\PROJET\FGA_Soft_Front\Front\INPUT\ACTION\FACTSET\Modele_Classification.xlsx"; string filepath2 = @"G:\,FGA Front Office\02_Gestion_Actions\00_BASE\Base 2.0\Modele_Classification.xlsx"; DataSet ds = new DataSet("DATA_FACTSET"); SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_RW"); OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(filepath2, "Modele Classification", "B1", "V926", true); adapter.AddColumnMapping("DATA_FACTSET", "MXEU", "MXEU", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "MXUSLC", "MXUSLC", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "MXEM", "MXEM", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "MXEUM", "MXEUM", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "MXFR", "MXFR", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100001", "6100001", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100004", "6100004", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100030", "6100030", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100033", "6100033", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100063", "6100063", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "AVEURO", "AVEURO", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "AVEUROPE", "AVEUROPE", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100026", "6100026", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100062", "6100062", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100002", "6100002", System.Type.GetType("System.Double")); adapter.AddColumnMapping("DATA_FACTSET", "6100024", "6100024", System.Type.GetType("System.Double")); adapter.FillColumnWithValue("DATE", date); adapter.Fill(ds); DataTable dt = ds.Tables["DATA_FACTSET"]; s.bulkcopyData(dt); }