Exemple #1
0
        private void CombineSheets_Btn_Click(object sender, RibbonControlEventArgs e)
        {
            int totalSheets = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets.Count;

            string[] mySheet = new string[totalSheets];

            foreach (Excel.Worksheet sheet in Globals.ThisAddIn.Application.ActiveWorkbook.Sheets)
            {
                mySheet[sheet.Index - 1] = sheet.Name;
            }

            DataTable dtTable = global::DataPrepTools.CreateDataSet.ExcelToDataSet(mySheet);

            Globals.ThisAddIn.Application.Worksheets.Add();
            Globals.ThisAddIn.Application.ActiveSheet.Name = "All Sheets";
            Excel.Worksheet newSheet = Globals.ThisAddIn.Application.ActiveSheet;

            ExportData.ExportDataTableToExcel(dtTable, newSheet);
        }
Exemple #2
0
        public static string DupCheck(DataTable excelData)
        {
            //values to get back from form
            string storeNum    = null;
            string uniqueField = null;

            //create array of excel column headers
            string[] columnsArray = new string[excelData.Columns.Count];
            for (int i = 0; i < excelData.Columns.Count; i++)
            {
                columnsArray[i] = excelData.Columns[i].ToString();
            }

            //get user input for store number and unique field
            using (var form = new DupsFieldSelectionForm(columnsArray))
            {
                var result = form.ShowDialog();
                if (result != DialogResult.OK) /* I don't think this is the "right" way to do this */
                {
                    storeNum    = form.StoreNumber;
                    uniqueField = form.UniqueColumn;
                }
            }

            if (storeNum != null && uniqueField != null)
            {
                DataProcessing dataProcessing = new DataProcessing();
                dataProcessing.Show();
                dataProcessing.Update();

                //change fields in checkfordups to match original string names
                bool ContainsDups = false;

                if (columnsArray.Contains("Dups"))
                {
                    excelData.Columns.Remove("Dups");
                }
                excelData.Columns.Add("Dups");

                //double sort excelData by the store number and unique field
                DataView sortDT = new DataView(excelData);
                sortDT.Sort = storeNum + "," + uniqueField;

                string previousValue = null;

                for (int i = 0; i < excelData.Rows.Count; i++)
                {
                    //check if selectedColumn value == previousValue.  If yes, add "dup" to Dup column
                    if (sortDT[i][uniqueField].ToString() == previousValue)
                    {
                        sortDT[i]["Dups"]     = "DUP";
                        sortDT[i - 1]["Dups"] = "DUP";
                        ContainsDups          = true;
                    }

                    //set previous value to
                    previousValue = sortDT[i][uniqueField].ToString();
                }

                if (ContainsDups == true)
                {
                    MessageBox.Show("Duplicates have been found.  Please remove duplicates.");
                    sortDT.Sort = "Dups DESC," + storeNum + "," + uniqueField;
                    excelData   = sortDT.ToTable();

                    Excel.Workbook  excelWorkBook  = Globals.ThisAddIn.GetActiveWorkbook();
                    Excel.Worksheet excelWorkSheet = excelWorkBook.ActiveSheet;
                    ExportData.ExportDataTableToExcel(excelData, excelWorkSheet);

                    dataProcessing.Close();
                    return(null);
                }

                else
                {
                    excelData.Columns.Remove("Dups");
                    dataProcessing.Close();
                    return(uniqueField);
                }
            }

            else
            {
                return(null);
            }
        }
Exemple #3
0
        public static void AccessToDataSet(DataTable excelData, string uniqueField)
        {
            try
            {
                DataSet DtSet = new DataSet("DPT");
                System.Data.OleDb.OleDbConnection  MyConnection;
                System.Data.OleDb.OleDbDataAdapter MyCommand;

                //Create connection to access database and add to table in dataset
                MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= 'C:\Space\Database\MillerCoors.mdb' ; Persist Security Info=False;");
                MyCommand    = new System.Data.OleDb.OleDbDataAdapter("select * from [MillerCoors Beer Database]", MyConnection);
                MyCommand.TableMappings.Add("Table", "AccessDataTable");
                DataTable accessData1 = DtSet.Tables.Add("AccessData");

                MyCommand.Fill(accessData1);
                MyConnection.Close();

                //create array of excel column headers
                string[] columnsArray = new string[excelData.Columns.Count];
                for (int i = 0; i < excelData.Columns.Count; i++)
                {
                    columnsArray[i] = excelData.Columns[i].ToString();
                }

                //create array of access column headers
                string[] accessFieldsArray = new string[accessData1.Columns.Count];
                for (int i = 0; i < accessData1.Columns.Count; i++)
                {
                    accessFieldsArray[i] = accessData1.Columns[i].ToString();
                }

                //make clone that converts all datatypes to string (for dynamic LINQ)
                DataTable accessData = accessData1.Clone();
                for (int i = 0; i < accessFieldsArray.Length - 1; i++)
                {
                    accessData.Columns[i].DataType = typeof(string);
                }
                foreach (DataRow row in accessData1.Rows)
                {
                    accessData.ImportRow(row);
                }

                //checking to see if there are duplicates between excel column headers and access column headers
                var    duplicates   = columnsArray.Intersect(accessFieldsArray, StringComparer.OrdinalIgnoreCase);
                string Intersection = String.Join(", ", duplicates);

                if (Intersection.Length > 1)
                {
                    DialogResult result = MessageBox.Show(
                        "By selecting OK, the UPC Wizard will remove the matching columns from the returnable fields.  " +
                        "\n \n If you would like to change the column headers, hit Cancel. " +
                        "\n \n Matching columns: " + Intersection, "Excel sheet contains column headers that match the Database.", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);

                    if (result == DialogResult.Cancel)
                    {
                        return;
                    }
                    foreach (var item in duplicates)
                    {
                        accessFieldsArray = Array.FindAll(accessFieldsArray, x => x != item);
                    }
                }

                //values to get back from form
                string selectedColumn = uniqueField;
                string columnType     = null;
                string returnedFields = null;

                //get user input for excel field and fields to return
                using (var form = new WizardFieldSelectionForm(uniqueField, accessFieldsArray))
                {
                    var result = form.ShowDialog();

                    if (result == DialogResult.OK)
                    {
                        columnType     = form.ColumnType;
                        returnedFields = form.ReturnedFields;
                    }
                    else
                    {
                        return;
                    }
                }

                //DataProcessing dataProcessing = new DataProcessing();
                //dataProcessing.Show();
                //dataProcessing.Update();

                DataProcessing dataProcessing = new DataProcessing();
                dataProcessing.Show();
                dataProcessing.Update();
                Commands.UpdatingMessage("UPC WIZARD");

                //add returnFields as columns in excelData
                string[] newColumns = returnedFields.Split(',');
                for (int i = 0; i < newColumns.Length; i++)
                {
                    string newColumn = newColumns[i];
                    excelData.Columns.Add(newColumn, typeof(String));
                }

                var caseSwitch = columnType;
                switch (caseSwitch)
                {
                case "UPC":
                    GivenUPC(excelData, accessData, selectedColumn, newColumns);
                    break;

                case "ID":
                    GivenID(excelData, accessData, selectedColumn, newColumns);
                    break;

                case "Name":
                    GivenName(excelData, accessData, selectedColumn, newColumns);
                    break;
                }
                //bubble blanks to top
                DataView sortDT = new DataView(excelData)
                {
                    Sort = (newColumns[0]) + "," + uniqueField
                };
                excelData = sortDT.ToTable();

                //Exports dataset back into UPC Wizard tab
                Excel.Workbook  excelWorkBook  = Globals.ThisAddIn.GetActiveWorkbook();
                Excel.Worksheet excelWorkSheet = excelWorkBook.ActiveSheet;
                ExportData.ExportDataTableToExcel(excelData, excelWorkSheet);

                Commands.ClearMessage();
                dataProcessing.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }