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