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