/// <summary>
/// Import a text file into an existing Excel file, creates a new WorkSheet and
/// populates the WorkSheet with comma delimited data from the text file. Note that
/// the folder in this case is our database so in the method ImportFromTextFile the
/// connection string has DATABASE which is set the current application's executable
/// folder.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdImportTextFileIntoWorkSheetOleDb_Click(object sender, EventArgs e)
{
var oleOperations = new OledbCode.Operations();
var excelFileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ImportFromTextFile.xlsx");
var textFileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Sample1.txt");
var sheetName = "Customers";
if (oleOperations.ImportFromTextFile(excelFileName, textFileName, sheetName))
{
MessageBox.Show("Import done");
}
else
{
Dialogs.ExceptionDialog(oleOperations.LastException);
}
}
/// <summary>
/// Retrieve sheet names using OleDb data provider
/// which unlike automation or open xml the sheet names
/// are a-z order rather then ordinal position.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdGetSheetNamesOleDb_Click(object sender, EventArgs e)
{
var helper = new OledbCode.Operations();
var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx");
var sheetNames = helper.SheetNames(fileName);
if (helper.IsSuccessFul)
{
sheetNames.Insert(0, "Select sheet");
cboGetSheetNamesOleDb.DataSource = sheetNames;
}
else
{
Dialogs.ExceptionDialog(helper.LastException);
}
}
/// <summary>
/// Read in a work sheet using OleDb data provider. One must be careful when
/// there is mixed data in a column. This demo does not have mixed data so
/// there will be no issues unlike working with mixed data there is no one
/// solution.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdReadCustomersOleDb_Click(object sender, EventArgs e)
{
var helper = new OledbCode.Operations();
var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx");
var customers = helper.ReadCustomers(fileName);
//var test = helper.ReaDataTable(fileName);
var f = new frmCustomers(customers);
try
{
f.ShowDialog();
}
finally
{
f.Dispose();
}
}
private void cmdSimpleOleSearch_Click(object sender, EventArgs e)
{
var ops = new OledbCode.Operations();
var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx");
var(Table, Success) = ops.ReaDataTable(fileName, "Sales Representative");
if (Success)
{
if (Table.Rows.Count > 0)
{
MessageBox.Show("Done");
}
else
{
MessageBox.Show("Done, nothing for search");
}
}
else
{
MessageBox.Show($"Encountered an issue: {ops.LastExceptionMessage}");
}
}
/// <summary>
/// Exports a DataGridView to Excel.
///
/// First make sure the WorkSheet does not exists, if the WorkSheet exists abort.
///
/// If the WorkSheet does not exist, display data retrieved from a SQL-Server database table in a
/// DataGridView that is unbound meaning adding DataGridView rows rather than setting the
/// DataGridView DataSoure.
///
/// Take the DataGridView and convert to a DataTable where all DataColumn's are converted to
/// strings.
///
/// Next control is passed to a method in ExcelOperations.OleDbWork.Operations.ExportDataTableToExcel
/// See comments in ExportDataTableToExcel.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// <remarks>
/// There are literally thousands of recommendations out there for performing what this code
/// does. This is unlike other versions out there for strictly working with OleDb.
/// </remarks>
private void cmdCopyAndPopulateOleDb_Click(object sender, EventArgs e)
{
var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "PeopleOleDbImport.xlsx");
var sheetName = "People";
var oleOperations = new OledbCode.Operations();
if (oleOperations.SheetNames(fileName).Contains(sheetName))
{
MessageBox.Show($"Sheet {sheetName} already exists");
return;
}
var f = new ExportDataGridViewToExcelForm();
try
{
if (f.ShowDialog() == DialogResult.OK)
{
var dtPeople = f.DataTable;
dtPeople.TableName = sheetName;
oleOperations.ExportDataTableToExcel(fileName, dtPeople);
if (oleOperations.IsSuccessFul)
{
Dialogs.InformationDialog("Export finished");
}
else
{
Dialogs.ExceptionDialog(oleOperations.LastException);
}
}
}
finally
{
f.Dispose();
}
}
