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