private void sheetComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
ExcelBibliographicReader read = new ExcelBibliographicReader();
try
{
// Make sure there is a filename and a sheet name
if ((fileTextBox.Text.Length > 0) && (sheetComboBox.SelectedIndex >= 0))
{
// reset the status label
labelStatus.Text = "";
read.Sheet = sheetComboBox.Text;
read.Filename = fileTextBox.Text;
// Declare constant fields
Constant_Fields constants = new Constant_Fields();
columnNamePanel.Controls.Clear();
column_map_inputs.Clear();
pnlConstants.Controls.Clear();
constant_map_inputs.Clear();
columnNamePanel.Enabled = true;
pnlConstants.Enabled = true;
// Display an hourglass cursor:
this.Cursor = Cursors.WaitCursor;
// Try reading data from the selected Excel Worksheet
bool readFlag = true;
while (readFlag)
{
try
{
if (!read.Check_Source())
{
ResetFormControls();
return;
}
else
{
readFlag = false;
}
}
catch (Exception ex)
{
DLC.Tools.Forms.ErrorMessageBox.Show(ex.Message, "Unexpected Error", ex);
}
}
// change cursor back to default
this.Cursor = Cursors.Default;
this.excelDataTbl = read.excelData;
if (read.excelData.Rows.Count > 0 || read.excelData.Columns.Count > 0)
{
int column_counter = 1;
foreach (DataColumn thisColumn in excelDataTbl.Columns)
{
// Create the column mapping custom control
Column_Assignment_Control thisColControl = new Column_Assignment_Control();
// Get the column name
string thisColumnName = thisColumn.ColumnName;
thisColControl.Column_Name = thisColumnName;
if (thisColumnName == "F" + column_counter)
{
thisColControl.Empty = true;
}
thisColControl.Location = new Point(10, 10 + ((column_counter - 1) * 30));
this.columnNamePanel.Controls.Add(thisColControl);
this.column_map_inputs.Add(thisColControl);
// Select value in list control that matches to a Column Name
thisColControl.Select_List_Item(thisColumnName);
// Increment for the next column
column_counter++;
}
// Create the constants mapping custom control
// Add eight constant user controls to panel
for (int i = 1; i < 9; i++)
{
Constant_Assignment_Control thisConstantCtrl = new Constant_Assignment_Control();
thisConstantCtrl.Location = new Point(10, 10 + ((i - 1) * 30));
this.pnlConstants.Controls.Add(thisConstantCtrl);
this.constant_map_inputs.Add(thisConstantCtrl);
}
// set some of the constant columns to required tracking fields
constant_map_inputs[0].Mapped_Name = "Material Type";
constant_map_inputs[1].Mapped_Name = "Aggregation Code";
constant_map_inputs[2].Mapped_Name = "Visibility";
constant_map_inputs[3].Mapped_Name = "Tickler";
FileInfo fileInfo = new FileInfo(fileTextBox.Text);
constant_map_inputs[3].Mapped_Constant = fileInfo.Name.Replace(fileInfo.Extension, "");
// Move to STEP 3
show_step_3();
if (column_map_inputs.Count > 0)
{
// Move to STEP 4
show_step_4();
}
}
// Close the reader
read.Close();
}
}
catch (Exception ex)
{
DLC.Tools.Forms.ErrorMessageBox.Show(ex.Message, "Unexpected Error", ex);
}
finally
{
// change cursor back to default
this.Cursor = Cursors.Default;
// Close the reader
read.Close();
}
}
private void Browse_Source()
{
// reset the status label
labelStatus.Text = "";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
ExcelBibliographicReader read = new ExcelBibliographicReader();
List <string> tables;
try
{
// Set form controls to default state
ResetFormControls();
// Write the filename to the text box first
this.fileTextBox.Text = openFileDialog1.FileName;
this.filename = openFileDialog1.FileName;
// Try getting the worksheet names from the selected workbook
bool readFlag = true;
while (readFlag)
{
try
{
// Get the sheet names
read = new ExcelBibliographicReader();
tables = read.GetExcelSheetNames(openFileDialog1.FileName);
if (tables == null)
{
ResetFormControls();
return;
}
else
{
readFlag = false;
// Populate the combo box
this.sheetComboBox.Enabled = true;
foreach (string thisSheetName in tables)
{
this.sheetComboBox.Items.Add(thisSheetName);
}
// show step 2 instructions
show_step_2();
}
}
catch (Exception ex)
{
DLC.Tools.Forms.ErrorMessageBox.Show(ex.Message, "Unexpected Error", ex);
}
}
}
catch (Exception ex)
{
DLC.Tools.Forms.ErrorMessageBox.Show(ex.Message, "Unexpected Error", ex);
}
finally
{
// Close the reader
read.Close();
}
}
else
{
// reset the form
this.ResetFormControls();
// Move to STEP 1
this.show_step_1();
}
}
private void sheetComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
ExcelBibliographicReader read = new ExcelBibliographicReader();
try
{
// Make sure there is a filename and a sheet name
if ((fileTextBox.Text.Length > 0) && (sheetComboBox.SelectedIndex >= 0))
{
read.Sheet = sheetComboBox.Text;
read.Filename = fileTextBox.Text;
// Try reading data from the selected Excel Worksheet
if (!read.Check_Source())
{
MessageBox.Show("Unable to read the source sheet ");
fileTextBox.Text = String.Empty;
filename = String.Empty;
sheetComboBox.Items.Clear();
sheetComboBox.Text = String.Empty;
sheetComboBox.Hide();
sheetLabel.Hide();
executeButton.Button_Enabled = false;
}
else
{
rawDataTbl = read.excelData;
columnname_to_input.Clear();
foreach (DataColumn thisColumn in rawDataTbl.Columns)
{
columnname_to_input[thisColumn.ColumnName.ToUpper().Trim()] = thisColumn.ColumnName.Trim();
thisColumn.ColumnName = thisColumn.ColumnName.ToUpper().Trim().Replace(" ", "");
}
if (!Transform_Data())
{
fileTextBox.Text = String.Empty;
filename = String.Empty;
sheetComboBox.Items.Clear();
sheetComboBox.Text = String.Empty;
sheetComboBox.Hide();
sheetLabel.Hide();
executeButton.Button_Enabled = false;
}
else
{
// Check to see if multiple bibids loaded
string check_bib = transformedDataTbl.Rows[0][0].ToString().ToUpper();
bool multiple_bibs_in_table = transformedDataTbl.Rows.Cast <DataRow>().Any(thisRow => thisRow[0].ToString().ToUpper() != check_bib);
btnShowData.Location = new Point(239, 66);
btnShowData.Show();
if ((bibidLabel.Visible) || (multiple_bibs_in_table))
{
executeButton.Button_Enabled = true;
}
}
}
// Close the reader
read.Close();
}
}
catch (Exception ex)
{
ErrorMessageBox.Show(ex.Message, "Unexpected Error", ex);
fileTextBox.Text = String.Empty;
filename = String.Empty;
sheetComboBox.Items.Clear();
sheetComboBox.Text = String.Empty;
sheetComboBox.Hide();
sheetLabel.Hide();
executeButton.Button_Enabled = false;
}
finally
{
// Close the reader
read.Close();
}
}
private void Browse_Source()
{
fileTextBox.Text = String.Empty;
filename = String.Empty;
sheetComboBox.Items.Clear();
sheetComboBox.Text = String.Empty;
sheetComboBox.Hide();
sheetLabel.Hide();
btnShowData.Hide();
executeButton.Button_Enabled = false;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
// Write the filename to the text box first
fileTextBox.Text = openFileDialog1.FileName;
filename = openFileDialog1.FileName;
// Determine the filetype
FileInfo fileInfo = new FileInfo(filename);
string extension = fileInfo.Extension.ToUpper();
if ((extension == ".XLS") || (extension == ".XLSX"))
{
ExcelBibliographicReader read = new ExcelBibliographicReader();
try
{
// Try getting the worksheet names from the selected workbook
// Get the sheet names
read = new ExcelBibliographicReader();
List <string> tables = read.GetExcelSheetNames(openFileDialog1.FileName);
if (tables == null)
{
MessageBox.Show("Unable to read the source workbook. ");
fileTextBox.Text = String.Empty;
filename = String.Empty;
}
else
{
// Populate the combo box
foreach (string thisSheetName in tables)
{
sheetComboBox.Items.Add(thisSheetName);
}
sheetComboBox.Show();
sheetLabel.Show();
}
}
catch (Exception ex)
{
ErrorMessageBox.Show(ex.Message, "Unexpected Error", ex);
fileTextBox.Text = String.Empty;
filename = String.Empty;
}
finally
{
// Close the reader
read.Close();
}
return;
}
// Check with CSV or TXT files
if ((extension == ".CSV") || (extension == ".TXT"))
{
// Create the splitter to use
string splitter = ",";
if (extension == ".TXT")
{
splitter = ",\t";
}
char[] splitter_chars = splitter.ToCharArray();
StreamReader text_reader = new StreamReader(filename);
try
{
string line = text_reader.ReadLine();
if (line != null)
{
string[] split = line.Split(splitter_chars);
int columns = 0;
rawDataTbl = new DataTable();
columnname_to_input.Clear();
foreach (string topRowSplit in split)
{
if (topRowSplit.Length > 0)
{
columns++;
rawDataTbl.Columns.Add(topRowSplit.ToUpper().Trim().Replace(" ", ""));
columnname_to_input[topRowSplit.ToUpper().Trim().Replace(" ", "")] =
topRowSplit.Trim();
}
else
{
break;
}
}
line = text_reader.ReadLine();
while (line != null)
{
split = line.Split(splitter_chars);
DataRow newRow = rawDataTbl.NewRow();
for (int i = 0; (i < columns) && (i < split.Length); i++)
{
newRow[i] = split[i];
}
rawDataTbl.Rows.Add(newRow);
line = text_reader.ReadLine();
}
}
btnShowData.Location = new Point(73, 67);
btnShowData.Show();
if (bibidLabel.Visible)
{
executeButton.Button_Enabled = true;
}
}
catch
{
MessageBox.Show("Input text file is in improper format");
fileTextBox.Text = String.Empty;
filename = String.Empty;
}
text_reader.Close();
return;
}
// Type was invalid so just return
fileTextBox.Text = String.Empty;
filename = String.Empty;
}
}
private void sheetComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
ExcelBibliographicReader read = new ExcelBibliographicReader();
try
{
// Make sure there is a filename and a sheet name
if ((fileTextBox.Text.Length > 0) && (sheetComboBox.SelectedIndex >= 0))
{
// reset the status label
labelStatus.Text = "";
read.Sheet = sheetComboBox.Text;
read.Filename = fileTextBox.Text;
// Declare constant fields
Constant_Fields constants = new Constant_Fields();
columnNamePanel.Controls.Clear();
column_map_inputs.Clear();
pnlConstants.Controls.Clear();
constant_map_inputs.Clear();
columnNamePanel.Enabled = true;
pnlConstants.Enabled = true;
// Display an hourglass cursor:
this.Cursor = Cursors.WaitCursor;
// Try reading data from the selected Excel Worksheet
bool readFlag = true;
while (readFlag)
{
try
{
if (!read.Check_Source())
{
ResetFormControls();
return;
}
else
{
readFlag = false;
}
}
catch (Exception ex)
{
DLC.Tools.Forms.ErrorMessageBox.Show(ex.Message, "Unexpected Error", ex);
}
}
// change cursor back to default
this.Cursor = Cursors.Default;
this.excelDataTbl = read.excelData;
if (read.excelData.Rows.Count > 0 || read.excelData.Columns.Count > 0)
{
int column_counter = 1;
foreach (DataColumn thisColumn in excelDataTbl.Columns)
{
// Create the column mapping custom control
Column_Assignment_Control thisColControl = new Column_Assignment_Control();
// Get the column name
string thisColumnName = thisColumn.ColumnName;
thisColControl.Column_Name = thisColumnName;
if (thisColumnName == "F" + column_counter)
{
thisColControl.Empty = true;
}
thisColControl.Location = new Point(10, 10 + ((column_counter - 1) * 30));
this.columnNamePanel.Controls.Add(thisColControl);
this.column_map_inputs.Add(thisColControl);
// Select value in list control that matches to a Column Name
thisColControl.Select_List_Item(thisColumnName);
// Increment for the next column
column_counter++;
}
// Create the constants mapping custom control
// Add eight constant user controls to panel
for (int i = 1; i < 9; i++)
{
Constant_Assignment_Control thisConstantCtrl = new Constant_Assignment_Control();
thisConstantCtrl.Location = new Point(10, 10 + ((i - 1) * 30));
this.pnlConstants.Controls.Add(thisConstantCtrl);
this.constant_map_inputs.Add(thisConstantCtrl);
}
// set some of the constant columns to required tracking fields
constant_map_inputs[0].Mapped_Name = "Material Type";
constant_map_inputs[1].Mapped_Name = "Aggregation Code";
constant_map_inputs[2].Mapped_Name = "Visibility";
constant_map_inputs[3].Mapped_Name = "Tickler";
FileInfo fileInfo = new FileInfo(fileTextBox.Text);
constant_map_inputs[3].Mapped_Constant = fileInfo.Name.Replace(fileInfo.Extension,"");
// Move to STEP 3
show_step_3();
if (column_map_inputs.Count > 0)
// Move to STEP 4
show_step_4();
}
// Close the reader
read.Close();
}
}
catch (Exception ex)
{
DLC.Tools.Forms.ErrorMessageBox.Show(ex.Message, "Unexpected Error", ex);
}
finally
{
// change cursor back to default
this.Cursor = Cursors.Default;
// Close the reader
read.Close();
}
}
private void Browse_Source()
{
// reset the status label
labelStatus.Text = "";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
ExcelBibliographicReader read = new ExcelBibliographicReader();
List<string> tables;
try
{
// Set form controls to default state
ResetFormControls();
// Write the filename to the text box first
this.fileTextBox.Text = openFileDialog1.FileName;
this.filename = openFileDialog1.FileName;
// Try getting the worksheet names from the selected workbook
bool readFlag = true;
while (readFlag)
{
try
{
// Get the sheet names
read = new ExcelBibliographicReader();
tables = read.GetExcelSheetNames(openFileDialog1.FileName);
if (tables == null)
{
ResetFormControls();
return;
}
else
{
readFlag = false;
// Populate the combo box
this.sheetComboBox.Enabled = true;
foreach (string thisSheetName in tables)
this.sheetComboBox.Items.Add(thisSheetName);
// show step 2 instructions
show_step_2();
}
}
catch (Exception ex)
{
DLC.Tools.Forms.ErrorMessageBox.Show(ex.Message, "Unexpected Error", ex);
}
}
}
catch (Exception ex)
{
DLC.Tools.Forms.ErrorMessageBox.Show(ex.Message, "Unexpected Error", ex);
}
finally
{
// Close the reader
read.Close();
}
}
else
{
// reset the form
this.ResetFormControls();
// Move to STEP 1
this.show_step_1();
}
}
