private void mergeAndSave_Click(object sender, EventArgs e)
{
if (_keyColumns.Count == 0)
{
var errorDialog =
MessageBox.Show("There are no key columns selected. Please select at least one column.", "Error");
if (errorDialog == DialogResult.OK)
return;
}
try
{
var mergedTable = new DataTable();
var table1 = _listTables1[tableSelection1.SelectedIndex];
var table2 = _listTables2[tableSelection2.SelectedIndex];
var table1Copy = _listTables1[tableSelection1.SelectedIndex]; //used for removal of nonselected columns
var table2Copy = _listTables2[tableSelection2.SelectedIndex]; //to ensure correct indices for removal
if (table1.Equals(table2))
{
var errorDialog = MessageBox.Show("You cannot merge the same sheet.", "Error");
if (errorDialog == DialogResult.OK)
return;
}
if (_duplicateColumns.Count() > 0)
{
var confirmationDialog =
MessageBox.Show(
"Key columns will be merged into one. When merging two rows with common keys, cells within the same column that are not key columns will only display data overriden by the second file. Rows with key column cells that do not have absolute matches will be created as independent rows. Would you like to continue?",
"Confirmation", MessageBoxButtons.YesNo);
if (confirmationDialog == DialogResult.No)
return;
else
{
for (var index = 0; index < table1Copy.Columns.Count; index++)
if (checkedListBox1.CheckedIndices.IndexOf(index) == -1)
table1.Columns.Remove(table1Copy.Columns[index].ColumnName);
for (var index = 0; index < table2Copy.Columns.Count; index++)
if (checkedListBox2.CheckedIndices.IndexOf(index) == -1)
table2.Columns.Remove(table2Copy.Columns[index].ColumnName);
mergedTable = MergeTables(table1, table2);
var dialog = new SaveFileDialog();
dialog.InitialDirectory = "C:\\Users";
dialog.Filter = "Excel|*.xlsx"; //"Excel Files|(*.xlsx)|*.xlsx"; //changed from xlsx to xls
if (dialog.ShowDialog() == DialogResult.OK)
{
try
{
FileStream checkOpen = File.Open(dialog.FileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None);
checkOpen.Close();
}
catch(IOException)
{
var dialog1 = MessageBox.Show("A file with the given name is currently running. Please close the open file and try again to override.", "Error");
if (dialog1 == DialogResult.OK)
return;
}
CreateExcelFile.CreateExcelDocument(mergedTable, dialog.FileName);
var confirmation = new Confirmation();
if (confirmation.ShowDialog(this) == DialogResult.OK)
{
Close();
}
}
}
}
}
catch (ArgumentNullException)
{
var dialog = MessageBox.Show("The column you are merging must be a column in both sheets.",
"Error");
if (dialog == DialogResult.OK)
return;
}
catch (DataException)
{
var dialog = MessageBox.Show("There are some cells that are empty in the key columns.", "Error");
if (dialog == DialogResult.OK)
return;
}
catch (ArgumentException)
{
var dialog = MessageBox.Show("There is duplicate data in the key columns selected. Check to make sure there are no empty rows in the datasheet.", "Error");
if (dialog == DialogResult.OK)
return;
}
}
/// <summary>
/// This checks all conditions for insertion and does the actual insertion process. Look at comments for step-by-step information.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
if (listBox1.Items.Count == 0) //if there are no columns to insert (from text file or selected additional columns)
{
MessageBox.Show("There are no columns to insert. Please import a text file with column mappings to insert.", "Error");
return;
}
if (String.IsNullOrWhiteSpace(textBox1.Text)) //check if template has been selected
{
if (listBox2.Items.Count == 0) //check if template sheet has columns to insert into
{
MessageBox.Show("Please select a template sheet that has columns to insert data into.", "Error");
return;
}
else
{
MessageBox.Show("Please select a valid template file.", "Error");
return;
}
}
List <string> uniqueColNames = new List <string>(); //check if source file has duplicate column names
for (int x = 0; x < selectedTableNoFormat.Columns.Count; x++)
{
string temp = selectedTableNoFormat.Rows[0][x].ToString().Trim();
if (!string.IsNullOrWhiteSpace(temp))
{
if (uniqueColNames.Contains(temp))
{
MessageBox.Show("Duplicate column names were found in source file sheet. Please make sure there are no duplicates.", "Error");
return;
}
else
{
uniqueColNames.Add(temp);
}
}
}
uniqueColNames = new List <string>();
for (int x = 0; x < selectedTemplateNoFormat.Columns.Count; x++)
{
string temp = selectedTemplateNoFormat.Rows[0][x].ToString().Trim();
if (!string.IsNullOrWhiteSpace(temp))
{
if (uniqueColNames.Contains(temp))
{
MessageBox.Show("Duplicate column names were found in template sheet. Please make sure there are no duplicates.", "Error");
return;
}
else
{
uniqueColNames.Add(temp);
}
}
}
DataTable selectedTableCopy = selectedTable.Copy(); //use copies so original does not get modified when deleting rows
DataTable selectedTemplateCopy = selectedTemplate.Copy();
List <string> tempColNamesNoFormat = new List <string>();
foreach (DataColumn datum in selectedTableCopy.Columns) //trim excess off of column names of source
{
selectedTableCopy.Columns[datum.ColumnName].ColumnName = datum.ColumnName.Trim();
}
foreach (DataColumn datum in selectedTemplateCopy.Columns) //trim excess off of column names of template
{
selectedTemplateCopy.Columns[datum.ColumnName].ColumnName = datum.ColumnName.Trim();
}
for (int x = 0; x < selectedTemplateNoFormat.Columns.Count; x++) //stores all the column names without formatting (gets rid of default)
{
if (!string.IsNullOrWhiteSpace(selectedTemplateNoFormat.Rows[0][x].ToString().Trim()))
{
tempColNamesNoFormat.Add(selectedTemplateNoFormat.Rows[0][x].ToString().Trim());
}
}
for (int x = selectedTemplateCopy.Columns.Count - 1; x >= 0; x--) //remove all default template columns
{
selectedTemplateCopy.Columns[x].ColumnName = selectedTemplateCopy.Columns[x].ColumnName.Trim();
if (!tempColNamesNoFormat.Contains(selectedTemplateCopy.Columns[x].ColumnName))
{
selectedTemplateCopy.Columns.Remove(selectedTemplateCopy.Columns[x]);
}
}
bool colExistSource = true; //make sure all inserted columns exist in source file (bc text file)
foreach (string colName in columnKeys)
{
if (!selectedTableCopy.Columns.Contains(colName))
{
colExistSource = false;
}
}
if (!colExistSource)
{
MessageBox.Show("Please make sure all key columns from text file to insert exist in the source file.", "Error");
return;
}
List <string> templateColNames = new List <string>();
foreach (DataColumn tempCol in templateColumns)
{
templateColNames.Add(tempCol.ColumnName.Trim());
}
bool match = true; //true if all columns found in template, false otherwise
foreach (string colName in columnValues)
{
if (!templateColNames.Contains(colName))
{
match = false;
}
}
if (!match)
{
MessageBox.Show("Please make sure all columns to be inserted are columns that exist in template.", "Error");
return;
}
//now, all conditions have been checked, start process of actually inserting
//move stuff from "selectedTable" based on column names from "columnInsert" into template table called "selectedTemplate" with matching column
//names as in "columnInsert"
bool foundContent = false;
for (int i = 0; i < selectedTableCopy.Rows.Count; i++) //delete all rows with any cell containing "NotDelete" as value
{
DataRow row = selectedTableCopy.Rows[i];
if (!row.ItemArray.Any(o => o.ToString().Trim().Equals("NotDelete"))) //if row has whitespace or content
{
foundContent = true;
}
else //if row contains NotDelete
{
if (foundContent)
{
MessageBox.Show("Please make sure the source file has no extra rows before the last row with any cell designated \"NotDelete\".");
return;
}
else
{
selectedTableCopy.Rows.Remove(selectedTableCopy.Rows[i]); //remove all leading rows with "NotDelete"
i--; //account for removed row
}
}
}
for (int i = selectedTableCopy.Rows.Count - 1; i >= 0; i--) //delete all empty rows from end of source file
{
DataRow row = selectedTableCopy.Rows[i];
if (row.ItemArray.All(o => string.IsNullOrWhiteSpace(o.ToString())))
{
selectedTableCopy.Rows.Remove(selectedTableCopy.Rows[i]);
}
}
for (int i = selectedTemplateCopy.Rows.Count - 1; i >= 0; i--) //delete all empty rows from end of template file
{
DataRow row = selectedTemplateCopy.Rows[i];
if (row.ItemArray.All(o => string.IsNullOrWhiteSpace(o.ToString())))
{
selectedTemplateCopy.Rows.Remove(selectedTemplateCopy.Rows[i]);
}
else
{
break; //break out of loop when encountering first row with content
}
}
bool emptyTemplate = true; //true if template is "empty" and ready for insert, false otherwise
for (int i = 0; i < selectedTemplateCopy.Rows.Count; i++)
{
DataRow row = selectedTemplateCopy.Rows[i];
if (!row.ItemArray.Any(o => o.ToString().Trim().Equals("NotDelete")))
{
emptyTemplate = false;
}
}
if (!emptyTemplate)
{
MessageBox.Show("Please make sure the template you are inserting into is empty (except for rows containing cells designated \"NotDelete\") and does not have empty rows between designated \"NotDelete\" rows.");
return;
}
//At this point, our sourcefile only contains data under each column name we actually want to add.
//At this point, our templatefile is guaranteed to be empty and ready for things to be inserted.
int rowTemplateInsert = selectedTemplateCopy.Rows.Count; //first row to insert into from the template
for (int x = 0; x < selectedTableCopy.Rows.Count; x++)
{
selectedTemplateCopy.Rows.Add(selectedTemplateCopy.NewRow()); //add empty rows to allow for data insert
}
for (int z = 0; z < columnKeys.Length; z++) //col is already trimmed
{
string colKey = columnKeys[z];
string colVal = columnValues[z];
List <string> dataInsert = new List <string>();
if (selectedTableCopy.Columns[colKey] != null)
{
int columnIndex = selectedTableCopy.Columns[colKey].Ordinal;
for (int x = 0; x < selectedTableCopy.Rows.Count; x++)
{
dataInsert.Add(selectedTableCopy.Rows[x][columnIndex].ToString().Trim()); //copy all data from a given column into a stringlist
}
}
if (selectedTemplateCopy.Columns[colVal] != null)
{
int templateColIndex = selectedTemplateCopy.Columns[colVal].Ordinal;
for (int y = 0; y < dataInsert.Count; y++)
{
selectedTemplateCopy.Rows[y + rowTemplateInsert][templateColIndex] = dataInsert[y]; //copy over cell data into template
}
}
}
var dialog = new SaveFileDialog();
dialog.InitialDirectory = "C:\\Users";
dialog.Filter = "Excel|*.xlsx"; //"Excel Files|(*.xlsx)|*.xlsx"; //changed from xlsx to xls
if (dialog.ShowDialog() == DialogResult.OK)
{
try
{
FileStream checkOpen = File.Open(dialog.FileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None);
checkOpen.Close();
}
catch (IOException)
{
MessageBox.Show("The file to override is currently running. Please close the open file and try again to save.", "Error");
return;
}
File.Copy(templatePath, dialog.FileName, true); //creates original template copy with all formatting into FileName
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workBooks = excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workBook = workBooks.Open(dialog.FileName);
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[comboBox2.SelectedItem.ToString()];
for (int x = 0; x < selectedTemplateCopy.Rows.Count; x++)
{
for (int y = 0; y < selectedTemplateCopy.Columns.Count; y++)
{
if (!string.IsNullOrWhiteSpace(selectedTemplateCopy.Rows[x][y].ToString()))
{
workSheet.Cells[x + 2, y + 1] = selectedTemplateCopy.Rows[x][y].ToString();
}
}
}
excelApp.DisplayAlerts = false;
workBook.SaveAs(dialog.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges,
Type.Missing, Type.Missing);
workBook.Close();
workBooks.Close();
var confirmation = new Confirmation();
if (confirmation.ShowDialog(this) == DialogResult.OK)
{
Close();
}
}
}
