public void FillPrefabDataTable() { using (var conn = new SqlConnection(App.ConnectionString)) { conn.Open(); if (string.IsNullOrEmpty(_prefabName)) { _prefabName = App.RandomString(12); _prefabTable = new DataTable(_prefabName); _prefabTable.Columns.Add("COLUMNS"); _prefabTable.Columns.Add("TYPES"); _prefabTable.Columns.Add("SORTBYS"); _prefabTable.Columns.Add("GROUPS"); return; } using (var cmd = new SqlCommand($"SELECT * FROM [{Settings.Default.Schema}_PREFABS].[{_prefabName}]", conn)) { using (var sda = new SqlDataAdapter(cmd)) { _prefabTable.TableName = _prefabName; sda.Fill(_prefabTable); } } conn.Close(); } }
private void ExportCurrentSheetToExcel(object sender, ExecutedRoutedEventArgs e) { var options = new ExcelExportingOptions { ExcelVersion = ExcelVersion.Excel2013 }; var excelEngine = new ExcelEngine(); DataTable itemsSource = ((DataTable)MasterDataGrids[MasterTabControl.SelectedIndex].ItemsSource); string tempSheetName = App.RandomString(12); var workBook = excelEngine.Excel.Workbooks.Create(new[] { tempSheetName }); var tempExcelEngine = MasterDataGrids[MasterTabControl.SelectedIndex] .ExportToExcel(MasterDataGrids[MasterTabControl.SelectedIndex].View, options); var workSheet = tempExcelEngine.Excel.Workbooks[0].Worksheets[0]; workSheet.Name = itemsSource.TableName; workBook.Worksheets.AddCopy(workSheet); //workBook.Worksheets.Remove(1); SaveFileDialog sfd = new SaveFileDialog { FilterIndex = 3, Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx" }; if (sfd.ShowDialog() == true) { using (Stream stream = sfd.OpenFile()) { if (sfd.FilterIndex == 1) { workBook.Version = ExcelVersion.Excel97to2003; } else if (sfd.FilterIndex == 2) { workBook.Version = ExcelVersion.Excel2010; } else { workBook.Version = ExcelVersion.Excel2013; } workBook.Worksheets.Remove(tempSheetName); workBook.SaveAs(stream); } //Message box confirmation to view the created workbook. if (MessageBox.Show("Do you want to view the spreadsheet?", "Spreadsheet has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) { new SpreadsheetEditor(sfd.FileName).Show(); } } }