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