/// <summary> /// Returns all available operations with their sql templates /// </summary> /// <param name="confiFilePath">path to the excel configuration file for operations and templates</param> /// <returns></returns> private List <Operations> GetOperationFromConfig(string confiFilePath) { List <Operations> curentOpers = new List <Operations>(); if (!File.Exists(configurationFolderPath + templateConfigFileName)) { throw new FileNotFoundException("File not found in App Configuration directory" + configurationFolderPath, templateConfigFileName); } DataTable currentData = ReadExcelFile.getExcellToDtbl(configurationFolderPath + templateConfigFileName); for (int i = 0; i < currentData.Rows.Count; i++) { if (!File.Exists(configurationSQLPath + currentData.Rows[i][0].ToString())) { throw new FileNotFoundException("File not found in App SQL directory" + configurationSQLPath, currentData.Rows[i][0].ToString()); } Operations currentOper = new Operations( currentData.Rows[i][0].ToString() , (TableData.Operation)Enum.Parse(typeof(TableData.Operation), currentData.Rows[i][1].ToString()) , currentData.Rows[i][2].ToString() , currentData.Rows[i][3].ToString() ); curentOpers.Add(currentOper); } return(curentOpers); }
static void Main(string[] args) { ExportAccessTabletoExcel.ExportQuery(@"C:\Projects\CHIA\Cerner\Cerner_Multum_en-US_249_180601.accdb", "drc_gestational_age_non_continuous_infusion", @"C:\Projects\CHIA\drc_gestational_age_non_continuous_infusion.xlsx"); ReadExcelFileReturnList GetGetExcel = new ReadExcelFileReturnList(); GetGetExcel.getExcelFile(@"C:\Projects\CHIA\drc_gestational_age_non_continuous_infusion.xlsx", 1); //List<string> ExcelList = new List<string>(); //ReadExcelFile GetExcel = new ReadExcelFile(@"C:\Projects\CHIA\Copyof20180513_drc_age2_updates_US_CA_v249.xlsx"); ReadExcelFile GetExcel = new ReadExcelFile(); //ReadExcelFile GetExcel = new ReadExcelFile(); //GetExcel.ExcelList = ReadExcelFile.getExcelFile(); List <string> ExcelList = GetExcel.getExcelFile(@"C:\Projects\CHIA\Copyof20180513_drc_age2_updates_US_CA_v251.xlsx", 3); //Pass in Excel path and string //List<string> ExcelList2 = GetExcel2.getExcelFile(@"C:\Projects\CHIA\Copyof20180513_drc_age2_updates_US_CA_v251.xlsx", 3); List <string> ExcelList2 = GetExcel.getExcelFile(@"C:\Projects\CHIA\drc_gestational_age_non_continuous_infusion.xlsx", 1); CompareList CompareTwoExcelLists = new CompareList(); //List<string> ReturnLists = CompareTwoLists.Contains(ExcelList, DataList); IEnumerable <string> ReturnDiffLists = CompareTwoExcelLists.Contains(ExcelList, ExcelList2); PrintToFile.Print(ExcelList); PrintToFile.Print(ExcelList2); PrintToFile.Print(ReturnDiffLists); //ConsoleWriteLineList.DumpExcelSet(ExcelList); // Load an Access ACCDB file DataSet ds1 = AccessDbLoader.LoadFromFile(@"C:\Projects\CHIA\Cerner\Cerner_Multum_en-US_249_180601.accdb"); //DataSet ds1 = AccessDbLoader.LoadFromFile(@"C:\Projects\CHIA\Cerner\Cerner_Multum_en-CA_249_180601.accdb"); //DataSet ds1 = AccessDbLoader.LoadFromFile(@"C:\Projects\CHIA\Lexi\Cerner_LexiComp_en-US_249_180601.accdb"); List <string> DataList = DataSetToList.DumpDataSet(ds1); //DumpDataSetToConsole.DumpDataSet(ds1); //var firstNotSecond = ExcelList.Except(DataList).ToList(); //var secondNotFirst = DataList.Except(ExcelList).ToList(); CompareList CompareTwoLists = new CompareList(); //List<string> ReturnLists = CompareTwoLists.Contains(ExcelList, DataList); IEnumerable <string> ReturnLists = CompareTwoLists.Contains(ExcelList, DataList); PrintToFile.Print(ExcelList); PrintToFile.Print(DataList); PrintToFile.Print(ReturnLists); //ConsoleWriteLineList.DumpExcelSet(ReturnLists); Console.ReadLine(); }
static void Main(string[] args) { List <SimpleExcelModel> simpleExcelModelList = new List <SimpleExcelModel>(); // 엑셀파일 읽기 ReadExcelFile readExcelFile = new ReadExcelFile(); simpleExcelModelList = readExcelFile.ReadExcelFileValues(); // 위에서 읽은 엑셀파일의 텍스트 값을 가지고 그대로 새 엑셀파일을 만들어서 뿌려주기 MakeExcelFiles makeExcelFiles = new MakeExcelFiles(); makeExcelFiles.MakeDetailsAsExcel(simpleExcelModelList); }
protected void btnUploadExcel_Click(object sender, EventArgs e) { try { if (excelUpload.HasFile) { extension = String.Empty; extension = excelUpload.FileName.Substring(excelUpload.FileName.LastIndexOf(".")); string FileName = excelUpload.FileName; excelUpload.SaveAs(HttpContext.Current.Server.MapPath("excelfile/" + FileName)); StateData sdata = new StateData(); dt = ReadExcelFile.ReadAsDataTable(FileName); for (int i = 0; i < dt.Rows.Count; i++) { sdata.Name = dt.Rows[i][0].ToString(); sdata.Save(); } } } catch (Exception ex) { } }
protected void btnUploadCity_Click(object sender, EventArgs e) { try { if (FileUploadCity.HasFile) { extension = String.Empty; extension = FileUploadCity.FileName.Substring(FileUploadCity.FileName.LastIndexOf(".")); string FileName = FileUploadCity.FileName; FileUploadCity.SaveAs(HttpContext.Current.Server.MapPath("excelfile/" + FileName)); CityData cdata = new CityData(); dt = ReadExcelFile.ReadAsDataTable(FileName); for (int i = 0; i < dt.Rows.Count; i++) { cdata.StateId = int.Parse(dt.Rows[i][0].ToString()); cdata.Name = dt.Rows[i][1].ToString(); cdata.Save(); } } } catch (Exception ex) { } }
//changeit to work with Wpf public bool DrawGroupBoxElements(string confFilePath) { DataTable formatConfiguration = ReadExcelFile.GetExcellToDtbl(confFilePath); Point previousPoint = new Point(6, 0); Point firstRow = new Point(6, 0); int maxY = 407; int controlHeight = 25; int currentCol = 1; int columnMaxLen = 0; foreach (DataRow dr in formatConfiguration.Rows) { if (dr[3].ToString().ToUpper() == "TRUE OR FALSE") { CheckBox myCb = new CheckBox(); myCb.Name = dr[1].ToString(); myCb.Content = dr[1].ToString(); myCb.BackColor = Color.Black; myCb.ForeColor = Color.MediumTurquoise; if (((previousPoint.Y + controlHeight - 5) < maxY) && currentCol == 1) { previousPoint.X = previousPoint.X; previousPoint.Y = previousPoint.Y + controlHeight; myCb.Location = previousPoint; } else { previousPoint.X = firstRow.X + currentCol * 10 + 260; previousPoint.Y = firstRow.Y + controlHeight; myCb.Location = previousPoint; } if (int.Parse(dr[0].ToString()) == 1) { myCb.IsChecked = true; } else { myCb.IsChecked = false; } this.gbFormatSettings.Controls.Add(myCb); } else if (dr[3].ToString().ToUpper() == "SqlVersion".ToUpper() || dr[3].ToString().ToUpper() == "KeywordCasing".ToUpper()) { Label myComboLable = new Label(); myComboLable.Text = dr[1].ToString(); ComboBox myCombo = new ComboBox(); myCombo.Name = dr[1].ToString(); myCombo.AutoSize = true; myCombo.DropDownStyle = ComboBoxStyle.DropDownList; myCombo.BackColor = Color.Black; myCombo.ForeColor = Color.MediumTurquoise; if (dr[3].ToString().ToUpper() == "SqlVersion".ToUpper()) { myCombo.Items.Add("SQL 2005"); myCombo.Items.Add("SQL 2000"); myCombo.Items.Add("SQL 2008"); myCombo.Items.Add("SQL 2012"); myCombo.Items.Add("SQL 2014"); myCombo.Items.Add("SQL 2016"); } else { myCombo.Items.Add("Lowercase"); myCombo.Items.Add("Uppercase"); myCombo.Items.Add("PascalCase"); } if (drawFromFile) { myCombo.SelectedIndex = int.Parse(dr[0].ToString()); } if (((previousPoint.Y + controlHeight - 5) < maxY) && currentCol == 1) { previousPoint.X = previousPoint.X; previousPoint.Y = previousPoint.Y + controlHeight; myComboLable.Location = previousPoint; myCombo.Location = new Point(previousPoint.X, previousPoint.Y + 25); previousPoint.Y = previousPoint.Y + 25; } else { previousPoint.X = firstRow.X + currentCol * 10 + 260; previousPoint.Y = firstRow.Y + controlHeight; myComboLable.Location = previousPoint; myCombo.Location = new Point(previousPoint.X, previousPoint.Y + 25); previousPoint.Y = firstRow.Y + 25; } this.gbFormatSettings.Controls.Add(myComboLable); this.gbFormatSettings.Controls.Add(myCombo); } else if (dr[3].ToString().ToUpper() == "Indent".ToUpper()) { Label myTextLable = new Label(); myTextLable.Text = dr[1].ToString(); TextBox mytext = new TextBox(); mytext.Name = dr[1].ToString(); mytext.BackColor = Color.Black; mytext.ForeColor = Color.MediumTurquoise; if (drawFromFile) { mytext.Text = dr[0].ToString(); } mytext.AutoSize = true; if (((previousPoint.Y + controlHeight - 5) < maxY) && currentCol == 1) { previousPoint.X = previousPoint.X; previousPoint.Y = previousPoint.Y + controlHeight; myTextLable.Location = previousPoint; mytext.Location = new Point(previousPoint.X, previousPoint.Y + 25); previousPoint.Y = previousPoint.Y + 25; } else { previousPoint.X = firstRow.X + currentCol * 10 + 260; previousPoint.Y = firstRow.Y + controlHeight; myTextLable.Location = previousPoint; mytext.Location = new Point(previousPoint.X, previousPoint.Y + 25); previousPoint.Y = firstRow.Y + 25; } this.gbFormatSettings.Controls.Add(myTextLable); this.gbFormatSettings.Controls.Add(mytext); } } System.Diagnostics.Debug.WriteLine(columnMaxLen); return(true); }
/// <summary> /// Fill the Database Data based on Information that`s extracted of SQL Server Selected Database /// in the Interface of the app /// </summary> /// <param name="customColumnsFileName"> Full path file for CustomMappings.xlsx file </param> private void GetDBData(string customColumnsFileName) { dbData.DatabaseName = currentDB; List <ManagedColumns> customInsertColumns = new List <ManagedColumns>(); List <ManagedColumns> customUpdateColumns = new List <ManagedColumns>(); List <ManagedColumns> customSelectColumns = new List <ManagedColumns>(); if (File.Exists(customColumnsFileName) && customColumns) { DataTable configTbl = ReadExcelFile.getExcellToDtbl(customColumnsFileName); for (int i = 0; i < configTbl.Rows.Count; i++) { if (configTbl.Rows[i][0].ToString() != string.Empty) { ManagedColumns managedInsertColumns = new ManagedColumns(); managedInsertColumns.ColumnName = configTbl.Rows[i][0].ToString(); managedInsertColumns.ColumnValue = configTbl.Rows[i][1].ToString(); managedInsertColumns.UsedForParam = Convert.ToBoolean(int.Parse(configTbl.Rows[i][2].ToString())); managedInsertColumns.UsedForColumn = Convert.ToBoolean(int.Parse(configTbl.Rows[i][3].ToString())); customInsertColumns.Add(managedInsertColumns); ManagedColumns managedUpdateColumns = new ManagedColumns(); managedUpdateColumns.ColumnName = configTbl.Rows[i][0].ToString(); managedUpdateColumns.ColumnValue = configTbl.Rows[i][1].ToString(); managedUpdateColumns.UsedForParam = Convert.ToBoolean(int.Parse(configTbl.Rows[i][4].ToString())); managedUpdateColumns.UsedForColumn = Convert.ToBoolean(int.Parse(configTbl.Rows[i][5].ToString())); customUpdateColumns.Add(managedUpdateColumns); ManagedColumns managedSelectColumns = new ManagedColumns(); managedSelectColumns.ColumnName = configTbl.Rows[i][0].ToString(); managedSelectColumns.ColumnValue = configTbl.Rows[i][1].ToString(); managedSelectColumns.UsedForParam = Convert.ToBoolean(int.Parse(configTbl.Rows[i][6].ToString())); managedSelectColumns.UsedForColumn = Convert.ToBoolean(int.Parse(configTbl.Rows[i][7].ToString())); customSelectColumns.Add(managedSelectColumns); } } dbData.ManagedInsertColumns = customInsertColumns; dbData.ManagedUpdateColumns = customUpdateColumns; dbData.ManagedSelectColumns = customSelectColumns; } else if (customColumns) { MessageBox.Show("File CustomColumsMappings.xlsx does not exist!\nNot found in the Application Configuration dir!"); } int startvalue = 1; //1 RANK 2 tableName, 3 columnname, 4 primarikey chek, 5 columndatatype , 6 null , 7 IsFK procGenData = sqlConn.GetProcTablesData(serverAddress, serverUser, serverUserPassword, currentDB, configurationFolderPath, columnsDataSql); string currentPrimaryKey = string.Empty; string currentPrimaryKeyDataType = string.Empty; List <ColumnData> currentColumns = new List <ColumnData>(); for (int i = 0; i < procGenData.Rows.Count; i++) { if ((startvalue == int.Parse(procGenData.Rows[i][0].ToString()) - 1) || ((i + 1) == procGenData.Rows.Count)) { TableData currentTbl = new TableData(); currentTbl.Columns = currentColumns; currentTbl.TableName = procGenData.Rows[i - 1][1].ToString(); currentTbl.PrimaryKey = currentPrimaryKey; currentTbl.PrimaryKeyDataType = currentPrimaryKeyDataType; dbData.Tables.Add(currentTbl); currentColumns = new List <ColumnData>(); startvalue = int.Parse(procGenData.Rows[i][0].ToString()); } if (bool.Parse(procGenData.Rows[i][3].ToString())) { currentPrimaryKey = procGenData.Rows[i][2].ToString(); currentPrimaryKeyDataType = procGenData.Rows[i][4].ToString(); } if (bool.Parse(procGenData.Rows[i][3].ToString()) == false) { ColumnData currentColumn = new ColumnData(); currentColumn.ColumnName = procGenData.Rows[i][2].ToString(); currentColumn.ColumnDataType = procGenData.Rows[i][4].ToString(); currentColumn.isNullable = bool.Parse(procGenData.Rows[i][5].ToString()); currentColumn.isFK = bool.Parse(procGenData.Rows[i][6].ToString()); currentColumns.Add(currentColumn); } } }