private void Export_Click(object sender, RoutedEventArgs e)
{
var options = new ExcelExportingOptions();
options.ExcelVersion = ExcelVersion.Excel2013;
var excelEngine = datagrid.ExportToExcel(datagrid.View, options);
var workbook = excelEngine.Excel.Workbooks[0];
System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog
{
FilterIndex = 2,
Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx"
};
sfd.FileName = "Annuaire";
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
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.SaveAs(stream);
}
}
}
private void button_ExportExcel_Click(object sender, EventArgs e)
{
(string title, string _) = GenerateTitle($"Bel Report", "x");
saveFileDialog1.Filter = "Excel files|*.xls;*.xlsx|All files|*.*";
string filePath = ShowSaveDialog("Export Excel");
if (string.IsNullOrEmpty(filePath))
{
return;
}
var options = new ExcelExportingOptions();
options.AllowOutlining = true;
var excelEngine = sfDataGrid1.ExportToExcel(sfDataGrid1.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
workBook.SaveAs(filePath);
//GenerateDataForExport();
//var exporter = m_ExporterProvider.Provide("Excel");
//string exportData = exporter.Export(title, ExportColNames, ExportData);
//File.WriteAllText(filePath, exportData);
}
private static void OnExecuteExportToExcel(object sender, ExecutedRoutedEventArgs args)
{
var dataGrid = args.Source as SfDataGrid;
var optionsettings = args.Parameter as ExcelExportingOptionsWrapper;
if (dataGrid == null || optionsettings == null)
{
return;
}
try
{
var options = new ExcelExportingOptions();
options.ExcelVersion = ExcelVersion.Excel2016;
options.AllowOutlining = optionsettings.AllowOutlining;
options.ExportingEventHandler = ExportingHandler;
if (!optionsettings.CanCustomizeStyle)
{
options.CellsExportingEventHandler = CellExportingHandler;
}
else
{
options.CellsExportingEventHandler = CustomizeCellExportingHandler;
}
var excelEngine = dataGrid.ExportToExcel(dataGrid.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
SaveFile(workBook);
}
catch (Exception)
{
}
}
private void _view_OnButtonExcelClick(object sender, ToolStripItemClickedEventArgs e)
{
var options = new ExcelExportingOptions();
var saveFileDialog = new SaveFileDialog();
saveFileDialog.Title = "Export to Excel";
saveFileDialog.Filter = "Excel 2007 to 2010 Files|*.xlsx|Excel 2013 File|*.xlsx|Excel 97 to 2003 Files|*.xls";
saveFileDialog.FileName = $"export_barang_{DateTime.Now.ToString("ddMMyyyy")}_{DateTime.Now.ToString("HHmmss")}";
saveFileDialog.DefaultExt = ".xlsx";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
var excelEngine = _view.ListDataGrid.ExportToExcel(_view.ListDataGrid.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
workBook.SaveAs(saveFileDialog.FileName);
if (Messages.Confirm("Apakah anda ingin membuka filenya sekarang?", "Export to Excel"))
{
var proc = new Process();
proc.StartInfo.FileName = saveFileDialog.FileName;
proc.Start();
}
}
}
public object Convert(object[] values, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
var options = new ExcelExportingOptions();
if (!(bool)values[0])
{
options.AllowOutlining = false;
}
if ((bool)values[1])
{
isCustomized = true;
}
else
{
isCustomized = false;
}
if ((bool)values[2])
{
IsCustomizeRow = true;
}
else
{
IsCustomizeRow = false;
}
return(options);
}
private void Excel(object sender, RoutedEventArgs e)
{
if (proguctsGrid1.IsVisible == true)
{
var options = new ExcelExportingOptions();
options.ExportMode = ExportMode.Value;
var excelEngine = proguctsGrid1.ExportToExcel(proguctsGrid1.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
workBook.SaveAs("adminDG1.xlsx");
MessageBox.Show("Программа успешно экспортировала данные в файл. \nЗайдите в папку с программой и найдите файл adminDG1.xlsx");
}
else if (proguctsGrid2.IsVisible == true)
{
var options = new ExcelExportingOptions();
options.ExportMode = ExportMode.Value;
var excelEngine = proguctsGrid2.ExportToExcel(proguctsGrid2.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
workBook.SaveAs("adminDG2.xlsx");
MessageBox.Show("Программа успешно экспортировала данные в файл. \nЗайдите в папку с программой и найдите файл adminDG2.xlsx");
}
else if (proguctsGrid3.IsVisible == true)
{
var options = new ExcelExportingOptions();
options.ExportMode = ExportMode.Value;
var excelEngine = proguctsGrid3.ExportToExcel(proguctsGrid3.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
workBook.SaveAs("adminDG3.xlsx");
MessageBox.Show("Программа успешно экспортировала данные в файл. \nЗайдите в папку с программой и найдите файл adminDG3.xlsx");
}
else
{
MessageBox.Show("Выберите таблицу");
}
}
// ****************************************************************************************************
// exporting all or filtered data grid records to an Excel spreadsheet in the Users Downloads folder
// the Downloads folder does not have a -- CreationCollisionOption.ReplaceExisting -- therefore it will give an error
// if the file already exists, we have overcome this by giving each file a unique name
// the UWP app automatically creates a folder with the app name within the destination path
private async void btnExportToXLS_Click(object sender, RoutedEventArgs e)
{
try
{
var options = new ExcelExportingOptions();
string date = DateTime.Now.Year.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Hour.ToString() + "h" + DateTime.Now.Minute.ToString() + "m" + DateTime.Now.Second.ToString() + "s";
options.ExcelVersion = ExcelVersion.Excel2010; // <----- this can be changed for Vision to -----> options.ExcelVersion = ExcelVersion.Excel2016;
var excelEngine = dataGrid.ExportToExcel(dataGrid.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
StorageFile storageFile = await DownloadsFolder.CreateFileAsync("Velog_" + date + ".xlsx");
if (storageFile != null)
{
await workBook.SaveAsAsync(storageFile);
txtFolderName.Text = "Downloads/VeLog <--- Copy & Paste in File Explorer";
txtFileName.Text = "Velog_" + date + ".xlsx <--- File Name";
var messageDialog = new MessageDialog("File successfully exported.");
await messageDialog.ShowAsync();
}
}
catch (Exception ex)
{
var messageDialog = new MessageDialog("File NOT exported!" + "\n" + "Error: " + ex.Message);
await messageDialog.ShowAsync();
}
}
private void GetValue(SfDataGrid grid)
{
ExcelExportingOptions exportingOptions = new ExcelExportingOptions(ExcelVersion.Excel2013, true, false, null, null);
exportingOptions.ChildExportingEventHandler = handler;
exportingOptions.ExportMode = ExportMode.Text;
exportingOptions.ExcludeColumns = new List <string>()
{
"Id", "Password", "Image", "Mechanic.Image"
};
ExcelEngine excelEngine = grid.ExportToExcel(grid.View, exportingOptions);
IWorkbook workBook = excelEngine.Excel.Workbooks[0];
SaveFileDialog sfd = new SaveFileDialog
{
FilterIndex = 2,
Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx"
};
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
using (Stream stream = sfd.OpenFile())
{
if (sfd.FilterIndex == 1)
{
workBook.Version = ExcelVersion.Excel97to2003;
}
else
{
workBook.Version = ExcelVersion.Excel2010;
}
workBook.SaveAs(stream);
}
}
}
private static void OnExecuteExportToExcel(object sender, ExecutedRoutedEventArgs args)
{
var dataGrid = args.Source as SfDataGrid;
var optionsettings = args.Parameter as ExcelExportingOptionsWrapper;
if (dataGrid == null || optionsettings == null)
{
return;
}
var options = new ExcelExportingOptions();
options.ExcelVersion = ExcelVersion.Excel2010;
if (!optionsettings.CanCustomizeStyle)
{
options.ExportingEventHandler = ExportingHandler;
}
else
{
options.ExportingEventHandler = CustomizeExportingHandler;
}
options.CellsExportingEventHandler = CellExportingHandler;
ExcelEngine excelEngine = new ExcelEngine();
IWorkbook workBook = excelEngine.Excel.Workbooks.Create();
workBook.Worksheets.Create();
dataGrid.ExportToExcel(dataGrid.SelectedItems, options, workBook.Worksheets[0]);
ExcelExportCommand.SaveFile(workBook);
}
/// <summary>
/// Method is export SfDataGrid to Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void OnExcelExportDataGrid(object sender, RoutedEventArgs e)
{
ExcelExportingOptions exportingOptions = new ExcelExportingOptions();
exportingOptions.ExportingEventHandler = exportingHandler;
if ((bool)this.customizeColumns.IsChecked)
{
exportingOptions.CellsExportingEventHandler = CustomizedCellExportingHandler;
}
else
{
exportingOptions.CellsExportingEventHandler = CellExportingHandler;
}
var excelEngine = this.dataGrid.ExportToExcel(this.dataGrid.View, exportingOptions);
var workBook = excelEngine.Excel.Workbooks[0];
var savePicker = new FileSavePicker
{
SuggestedStartLocation = PickerLocationId.Desktop,
SuggestedFileName = "Sample"
};
if (workBook.Version == ExcelVersion.Excel97to2003)
{
savePicker.FileTypeChoices.Add("Excel File (.xls)", new List <string>()
{
".xls"
});
}
else
{
savePicker.FileTypeChoices.Add("Excel File (.xlsx)", new List <string>()
{
".xlsx"
});
}
var storageFile = await savePicker.PickSaveFileAsync();
if (storageFile != null)
{
await workBook.SaveAsAsync(storageFile);
var msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
var yesCmd = new UICommand("Yes");
var noCmd = new UICommand("No");
msgDialog.Commands.Add(yesCmd);
msgDialog.Commands.Add(noCmd);
var cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
excelEngine.Dispose();
}
public static async System.Threading.Tasks.Task ExportToExcelAsync(int state)
{
var options = new ExcelExportingOptions();
options.ExcelVersion = ExcelVersion.Excel2013;
var excelEngine = Item1.dataGrid.ExportToExcel(Item1.dataGrid.View, options);
IWorkbook workBook = excelEngine.Excel.Workbooks[0];
if (state == 1)
{
var savePicker = new FileSavePicker
{
SuggestedStartLocation = PickerLocationId.Desktop,
SuggestedFileName = "Sample"
};
if (workBook.Version == ExcelVersion.Excel97to2003)
{
savePicker.FileTypeChoices.Add("Excel File (.xls)", new List <string>()
{
".xls"
});
}
else
{
savePicker.FileTypeChoices.Add("Excel File (.xlsx)", new List <string>()
{
".xlsx"
});
}
var storageFile = await savePicker.PickSaveFileAsync();
if (storageFile != null)
{
await workBook.SaveAsAsync(storageFile);
}
workBook.Close();
excelEngine.Dispose();
}
else
{
StorageFolder folder = ApplicationData.Current.LocalFolder;
StorageFile file = await folder.CreateFileAsync("data.xlsx", CreationCollisionOption.ReplaceExisting);
if (file != null)
{
await workBook.SaveAsAsync(file);
}
workBook.Close();
excelEngine.Dispose();
}
}
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();
}
}
}
void OnExporttoExcelButtonClick(object sender, RoutedEventArgs e)
{
var dataGrid = Grid;
if (dataGrid == null)
{
return;
}
try
{
//Setting the Exporting Options by craeting a instance for ExcelExportingOptions.
var exportingOptions = new ExcelExportingOptions();
exportingOptions.ExportAllPages = true;
exportingOptions.ExportStackedHeaders = true;
//Below code exports datagrid to excel and returns Excel Engine.
var excelEngine = dataGrid.ExportToExcel(dataGrid.View, exportingOptions);
var workBook = excelEngine.Excel.Workbooks[0];
//saving the workbook using savefiledialog.
SaveFileDialog sfd = new SaveFileDialog
{
FilterIndex = 2,
Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx"
};
if (sfd.ShowDialog() == true)
{
using (Stream stream = sfd.OpenFile())
{
if (sfd.FilterIndex == 1)
{
workBook.Version = ExcelVersion.Excel97to2003;
}
else
{
workBook.Version = ExcelVersion.Excel2010;
}
workBook.SaveAs(stream);
}
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(sfd.FileName);
}
}
}
catch (Exception)
{
}
}
private async void btnExport_Click(object sender, RoutedEventArgs e)
{
VisualStateManager.GoToState(this.sfDataGrid, "Busy", true);
await this.sfDataGrid.Dispatcher.BeginInvoke(new Action(() =>
{
ExcelEngine excelEngine = new ExcelEngine();
var options = new ExcelExportingOptions();
options.ExcelVersion = ExcelVersion.Excel2013;
excelEngine = sfDataGrid.ExportToExcel(sfDataGrid.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
VisualStateManager.GoToState(this.sfDataGrid, "Normal", true);
SaveFileDialog sfd = new SaveFileDialog
{
FilterIndex = 2,
FileName = "Sample",
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.SaveAs(stream);
}
//Message box confirmation to view the created workbook.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(sfd.FileName);
}
}
}), System.Windows.Threading.DispatcherPriority.Background);
}
private void OnExportButton_Click(object sender, EventArgs e)
{
var options = new ExcelExportingOptions();
var excelEngine = sfDataGrid1.ExportToExcel(sfDataGrid1.View, options);
var workBook1 = excelEngine.Excel.Workbooks[0];
var worksheet1 = workBook1.Worksheets[0];
excelEngine = sfDataGrid2.ExportToExcel(sfDataGrid2.View, options);
var workBook2 = excelEngine.Excel.Workbooks[0];
var worksheet2 = workBook2.Worksheets[0];
var columnCount = sfDataGrid2.Columns.Count;
//Merge the One SfDataGrid WorkSheet into the other SfDataGrid WorkSheet
worksheet2.UsedRange.CopyTo(worksheet1[1, columnCount + 1]);
workBook1.SaveAs("sample.xlsx");
SaveFileDialog saveFilterDialog = new SaveFileDialog
{
FilterIndex = 2,
Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx"
};
if (saveFilterDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
using (Stream stream = saveFilterDialog.OpenFile())
{
if (saveFilterDialog.FilterIndex == 1)
{
workBook1.Version = ExcelVersion.Excel97to2003;
}
else if (saveFilterDialog.FilterIndex == 2)
{
workBook1.Version = ExcelVersion.Excel2010;
}
else
{
workBook1.Version = ExcelVersion.Excel2013;
}
workBook1.SaveAs(stream);
}
//Message box confirmation to view the created workbook.
if (MessageBox.Show(this, "Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(saveFilterDialog.FileName);
}
}
}
private void Excel_Exporting(object sender, EventArgs e)
{
var options = new ExcelExportingOptions();
var excelEngine = sfDataGrid1.ExportToExcel(sfDataGrid1.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
workBook.Worksheets[0].Range["A1:A1"].CellStyle.Color = Color.DarkCyan;
workBook.Worksheets[0].Range["A1:A1"].CellStyle.Font.Color = ExcelKnownColors.White;
workBook.Worksheets[0].Range["B1:C1"].CellStyle.Color = Color.LightCyan;
workBook.Worksheets[0].Range["B1:C1"].CellStyle.Font.Color = ExcelKnownColors.Black;
workBook.Worksheets[0].Range["D1:E1"].CellStyle.Color = Color.DarkGray;
workBook.Worksheets[0].Range["D1:E1"].CellStyle.Font.Color = ExcelKnownColors.White;
workBook.SaveAs("SampleRange.xlsx");
}
/// <summary>
/// Exports a datagrid to excel
/// </summary>
/// <param name="dataGrid"></param>
private void ExportToExcel(string analysisName, SfDataGrid dataGrid, DirectoryInfo exportLocation)
{
var options = new ExcelExportingOptions();
options.ExcelVersion = Syncfusion.XlsIO.ExcelVersion.Excel2016;
var view = dataGrid.View;
using (var excelEngine = dataGrid.ExportToExcel(view, options))
{
var workBook = excelEngine.Excel.Workbooks[0];
workBook.SaveAs($@"{exportLocation.FullName}\{analysisName}.xlsx");
}
}
private void Button_Click(object sender, RoutedEventArgs e)
{
foreach (var item in new[] { dataGrid, dataGrid2 })
{
var cmd = Commands.ExportToExcel;
var param = new ExcelExportingOptions
{
AllowOutlining = allowOutlining.IsChecked.Value
};
new EccelOptionsConverter().isCustomized = customizeColumns.IsChecked.Value;
new EccelOptionsConverter().IsCustomizeRow = customizeSelectedRow.IsChecked.Value;
cmd.Execute(param, item);
}
}
public static void DownloadXLs(SfDataGrid sfDataGrid)
{
var options = new ExcelExportingOptions();
options.ExcelVersion = ExcelVersion.Excel2013;
foreach (var columns in sfDataGrid.Columns)
{
if (!columns.Visible)
{
options.ExcludeColumns.Add(columns.MappingName);
}
}
// options.CellExporting += CellExportingHandler;
var excelEngine = sfDataGrid.ExportToExcel(sfDataGrid.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
SaveFileDialog saveFilterDialog = new SaveFileDialog
{
FilterIndex = 2,
Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx"
};
if (saveFilterDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
using (Stream stream = saveFilterDialog.OpenFile())
{
if (saveFilterDialog.FilterIndex == 1)
{
workBook.Version = ExcelVersion.Excel97to2003;
}
else if (saveFilterDialog.FilterIndex == 2)
{
workBook.Version = ExcelVersion.Excel2010;
}
else
{
workBook.Version = ExcelVersion.Excel2013;
}
workBook.SaveAs(stream);
}
if (MessageBox.Show(sfDataGrid, "Apakah anda ingin membuka file excel hasil download ?", "Download Sukses!!",
MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
System.Diagnostics.Process.Start(saveFilterDialog.FileName);
}
}
}
/// <summary>
/// Exports the Selected rows of SfDataGrid to Excel.
/// </summary>
private void ExportSelectedRecordsToExcel(object sender, EventArgs e)
{
ExcelExportingOptions exportingOptions = new ExcelExportingOptions();
exportingOptions.CellExporting += OnSelectedItemsCellExporting;
if (shouldCustomizeSelectedItems.Checked)
{
exportingOptions.Exporting += CustomizeSelectedItemsExporting;
}
else
{
exportingOptions.Exporting += OnExporting;
}
var excelEngine = sfDataGrid1.ExportToExcel(sfDataGrid1.SelectedItems, exportingOptions);
var workBook = excelEngine.Excel.Workbooks[0];
SaveFileDialog sfd = new SaveFileDialog
{
FilterIndex = 2,
Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx",
FileName = "Book1"
};
if (sfd.ShowDialog() == DialogResult.OK)
{
using (Stream stream = sfd.OpenFile())
{
if (sfd.FilterIndex == 1)
{
workBook.Version = ExcelVersion.Excel97to2003;
}
else
{
workBook.Version = ExcelVersion.Excel2010;
}
workBook.SaveAs(stream);
}
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(sfd.FileName);
}
}
}
private void OnExportButton_Click(object sender, EventArgs e)
{
var options = new ExcelExportingOptions();
ExcelEngine excelEngine = new ExcelEngine();
IWorkbook workBook = excelEngine.Excel.Workbooks.Create();
sfDataGrid1.ExportToExcel(sfDataGrid1.View, options, workBook.Worksheets[0]);
sfDataGrid2.ExportToExcel(sfDataGrid2.View, options, workBook.Worksheets[1]);
workBook.Version = ExcelVersion.Excel2013;
SaveFileDialog saveFilterDialog = new SaveFileDialog
{
FilterIndex = 2,
Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx"
};
if (saveFilterDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
using (Stream stream = saveFilterDialog.OpenFile())
{
if (saveFilterDialog.FilterIndex == 1)
{
workBook.Version = ExcelVersion.Excel97to2003;
}
else if (saveFilterDialog.FilterIndex == 2)
{
workBook.Version = ExcelVersion.Excel2010;
}
else
{
workBook.Version = ExcelVersion.Excel2013;
}
workBook.SaveAs(stream);
}
//Message box confirmation to view the created workbook.
if (MessageBox.Show(this, "Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(saveFilterDialog.FileName);
}
}
}
private void cmdToolExportXLS_Click(object sender, EventArgs e)
{
var options = new ExcelExportingOptions();
var excelEngine = SelectedGrid.ExportToExcel(SelectedGrid.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
SaveFileDialog sfd = new SaveFileDialog
{
Filter = "xlsx File|*.xlsx|Ficheiro Excel|*.xlsx",
Title = "Guardar ficheiro Excel"
};
if (sfd.ShowDialog() == DialogResult.OK)
{
workBook.SaveAs(sfd.FileName);
Process.Start(sfd.FileName);
}
Cursor.Current = Cursors.Default;
}
private void OnExportClicked(object obj)
{
var grid = obj as SfDataGrid;
var options = new ExcelExportingOptions();
options.ExcelVersion = ExcelVersion.Excel2013;
var excelEngine = grid.ExportToExcel(grid.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
//Apply conditional format to worksheet
IWorksheet worksheet = workBook.Worksheets[0];
IConditionalFormats formats = worksheet["F2:F11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
format.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = format.DataBar;
dataBar.BarColor = Color.Blue;
workBook.SaveAs("Sample.xlsx");
}
private void button2_Click(object sender, System.EventArgs e)
{
ExcelExportingOptions options = new ExcelExportingOptions();
var excelEngine = this.sfDataGrid.ExportToExcel(sfDataGrid.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
//Row height will be set based on the content.
workBook.Worksheets[0].UsedRange.AutofitRows();
SaveFileDialog sfd = new SaveFileDialog
{
FilterIndex = 2,
Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx",
FileName = "Book1"
};
if (sfd.ShowDialog() == DialogResult.OK)
{
using (Stream stream = sfd.OpenFile())
{
if (sfd.FilterIndex == 1)
{
workBook.Version = ExcelVersion.Excel97to2003;
}
else
{
workBook.Version = ExcelVersion.Excel2010;
}
workBook.SaveAs(stream);
}
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(sfd.FileName);
}
}
}
// 导出成绩
private async void exportScore(object sender, RoutedEventArgs e)
{
var options = new ExcelExportingOptions();
options.ExcelVersion = ExcelVersion.Excel97to2003;
var excelEngine = scoreGrid.ExportToExcel(scoreGrid.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
// 选择文件保存的位置和名称
string _xnd = (xnd.SelectedItem as ComboBoxItem).Content.ToString();
string _xq = (xq.SelectedItem as ComboBoxItem).Content.ToString();
var savePicker = new Windows.Storage.Pickers.FileSavePicker();
savePicker.SuggestedStartLocation = Windows.Storage.Pickers.PickerLocationId.Desktop;
savePicker.FileTypeChoices.Add("Excel", new List <string>()
{
".xls"
});
savePicker.SuggestedFileName = _xnd + "-" + _xq + ".xls";
StorageFile file = await savePicker.PickSaveFileAsync();
if (file != null)
{
CachedFileManager.DeferUpdates(file);
await workBook.SaveAsAsync(file);
Windows.Storage.Provider.FileUpdateStatus status = await CachedFileManager.CompleteUpdatesAsync(file);
if (status == Windows.Storage.Provider.FileUpdateStatus.Complete)
{
await new MessageDialog("成绩导出成功!").ShowAsync();
}
else
{
await new MessageDialog("成绩导出失败!").ShowAsync();
}
}
else
{
return;
}
}
private static void OnExecuteExportToExcel(object sender, ExecutedRoutedEventArgs args)
{
var dataGrid1 = args.Source as SfDataGrid;
var dataGrid2 = args.Parameter as SfDataGrid;
if (dataGrid1 == null)
{
return;
}
if (dataGrid2 == null)
{
return;
}
try
{
var options = new ExcelExportingOptions();
options.ExcelVersion = ExcelVersion.Excel2010;
options.ExportingEventHandler = ExportingHandler;
ExcelEngine excelEngine = new ExcelEngine();
IWorkbook workBook = excelEngine.Excel.Workbooks.Create();
dataGrid1.ExportToExcel(dataGrid1.View, options, workBook.Worksheets[0]);
dataGrid2.ExportToExcel(dataGrid2.View, options, workBook.Worksheets[1]);
// Saving the workbook.
workBook.SaveAs("sample.xlsx");
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start("sample.xlsx");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
} // end method InitialiseDataGrid
/// <summary>
/// Initialise component properties.
/// </summary>
private void InitialiseTab()
{
_strikePriceVolumeDataProcessor = new StrikePriceVolumeDataProcessor();
_strikePriceTotalVolumeViewModel = new StrikePriceTotalVolumeViewModel();
_volumeUnitViewModel = new VolumeUnitViewModel();
TextBoxSymbol.AutoCompleteSource = _stockSymbolNameViewModel.StockSymbolNameRecords; // Set the auto-complete data source of the text box for the symbol.
// Set the specified property values of the data pickers.
DatePickerStartDate.CultureInfo = new CultureInfo(Properties.Settings.Default.CultureInfo);
DatePickerEndDate.CultureInfo = new CultureInfo(Properties.Settings.Default.CultureInfo);
// Set the specified property values of the data grid.
DataGridStrikePriceVolumeTable.FilterItemsPopulating += DataGridStrikePriceVolumeTable_FilterItemsPopulating;
DataGridStrikePriceVolumeTable.PrintSettings.AllowColumnWidthFitToPrintPage = false;
DataGridStrikePriceVolumeTable.PrintSettings.AllowPrintByDrawing = false;
DataGridStrikePriceVolumeTable.PrintSettings.AllowPrintStyles = false;
DataGridStrikePriceVolumeTable.PrintSettings.AllowRepeatHeaders = true;
DataGridStrikePriceVolumeTable.PrintSettings.CanPrintStackedHeaders = true;
DataGridStrikePriceVolumeTable.PrintSettings.PrintHeaderRowHeight = 40; // Customise header rows' height to show day volume headers completely.
DataGridStrikePriceVolumeTable.PrintSettings.PrintPageFooterHeight = Properties.Settings.Default.ContentTextFontSize;
DataGridStrikePriceVolumeTable.PrintSettings.PrintPageFooterTemplate = Application.Current.Resources["PrintedPageFooterTemplate"] as DataTemplate;
DataGridStrikePriceVolumeTable.PrintSettings.PrintPreviewWindowStyle = Application.Current.Resources["PrintPreviewWindowStyle"] as Style;
DataGridStrikePriceVolumeTable.QueryUnboundColumnValue += DataGridStrikePriceVolumeTable_QueryUnboundColumnValue;
ColumnStrikePrice.HeaderText += "\n" + Properties.Resources.LeftBracket + Properties.Resources.PriceUnit + Properties.Resources.RightBracket;
ColumnStrikePrice.MaximumWidth = Properties.Settings.Default.MaxCellWidth;
ColumnStrikePrice.MinimumWidth = Properties.Settings.Default.MinCellWidth;
ColumnTotalVolume.MaximumWidth = Properties.Settings.Default.MaxCellWidth;
ColumnTotalVolume.MinimumWidth = Properties.Settings.Default.MinCellWidth;
_excelExportingOptions = new ExcelExportingOptions
{
ExportStackedHeaders = true,
ExportingEventHandler = ExcelExportingHandler
}; // Set options for exporting to Excel.
} // end method InitialiseTab
private static void OnExecuteExportToExcel(object sender, ExecutedRoutedEventArgs args)
{
var dataGrid = args.Source as SfDataGrid;
if (dataGrid == null)
{
return;
}
try
{
GetDataGridStyles(dataGrid);
// Creating an instance for ExcelExportingOptions which is passed as a parameter to the ExportToExcel method.
ExcelExportingOptions options = new ExcelExportingOptions();
options.AllowOutlining = true;
options.ExportingEventHandler = ExportingHandler;
// Exports Datagrid to Excel and returns ExcelEngine.
var excelEngine = dataGrid.ExportToExcel(dataGrid.View, options);
// Gets the exported workbook from the ExcelEngine
var workBook = excelEngine.Excel.Workbooks[0];
var workSheet = workBook.Worksheets[0];
var gridColumns = dataGrid.Columns.Where(col => col.AllowEditing).ToList();
foreach (var column in workSheet.Columns)
{
if (gridColumns.Any(gridCol => gridCol.HeaderText == column.DisplayText))
{
workSheet.Range[column.AddressLocal].CellStyle.Locked = false;
}
}
workSheet.Protect("syncfusion");
// Saving the workbook.
SaveFileDialog sfd = new SaveFileDialog
{
FilterIndex = 2,
Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx"
};
if (sfd.ShowDialog() == true)
{
using (Stream stream = sfd.OpenFile())
{
if (sfd.FilterIndex == 1)
{
workBook.Version = ExcelVersion.Excel97to2003;
}
else
{
workBook.Version = ExcelVersion.Excel2010;
}
workBook.SaveAs(stream);
}
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(sfd.FileName);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public IWorkbook GetExcelWorkBook()
{
if (dataGrid == null)
{
return(null);
}
try
{
var options = new ExcelExportingOptions();
options.ExportStackedHeaders = true;
options.ExportingEventHandler = ExportingHandler;
options.CellsExportingEventHandler = CellExportingHandler;
options.ExcelVersion = ExcelVersion.Excel2007;
var document = dataGrid.ExportToExcel(dataGrid.View, options);
var workbook = document.Excel.Workbooks[0];
var workSheet = workbook.Worksheets[0];
workSheet.InsertRow(1, 5, ExcelInsertOptions.FormatDefault);
for (int i = 0; i < 5; i++)
{
workSheet.Rows[i].Merge();
var cell = workSheet.Range["A" + (i + 1).ToString()];
IStyle style = cell.CellStyle;
style.HorizontalAlignment = ExcelHAlign.HAlignCenter;
IFont font = style.Font;
font.FontName = "Segoe UI";
switch (i)
{
case 0:
cell.Value = GlobalClass.company.NAME;
font.Size = 16;
font.Bold = true;
break;
case 1:
cell.Value = GlobalClass.company.ADDRESS;
font.Size = 12;
break;
case 2:
cell.Value = GlobalClass.company.VAT;
font.Size = 12;
break;
case 3:
cell.Value = GlobalClass.ReportName;
font.Size = 14;
font.Bold = true;
break;
case 4:
cell.Value = GlobalClass.ReportParams;
font.Size = 12;
break;
}
}
return(workbook);
}
catch (Exception)
{
return(null);
}
}
public static void Write(List <SfDataGrid> grids)
{
Cursor.Current = Cursors.AppStarting;
var 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)
{
ExcelEngine excelEngine = new ExcelEngine();
string tempSheetName = App.RandomString(12);
var workBook = excelEngine.Excel.Workbooks.Create(new[] { tempSheetName });
using (Stream stream = sfd.OpenFile())
{
switch (sfd.FilterIndex)
{
case 1:
workBook.Version = ExcelVersion.Excel97to2003;
break;
case 2:
workBook.Version = ExcelVersion.Excel2010;
break;
default:
workBook.Version = ExcelVersion.Excel2013;
break;
}
foreach (var grid in grids)
{
DataTable itemsSource = (DataTable)grid.ItemsSource;
var options = new ExcelExportingOptions
{
ExcelVersion = workBook.Version,
AllowOutlining = true
};
switch (sfd.FilterIndex)
{
case 1:
options.ExcelVersion = ExcelVersion.Excel97to2003;
break;
case 2:
options.ExcelVersion = ExcelVersion.Excel2010;
break;
default:
options.ExcelVersion = ExcelVersion.Excel2013;
break;
}
if (grid.View != null && !grid.View.IsEmpty)
{
var tempExcelEngine = grid.ExportToExcel(grid.View, options);
var workSheet = tempExcelEngine.Excel.Workbooks[0].Worksheets[0];
workSheet.Name = itemsSource.TableName;
workBook.Worksheets.AddCopy(workSheet);
}
else
{
var workSheet = workBook.Worksheets.Create(itemsSource.TableName);
workSheet.ImportDataTable(itemsSource, true, 1, 1);
}
}
workBook.Worksheets.Remove(tempSheetName);
workBook.SaveAs(stream);
}
if (MessageBox.Show("Do you want to edit the spreadsheet?", "Spreadsheet has been created",
MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
new SpreadsheetEditor(sfd.FileName).Show();
}
}
/*
*
* IWorkbook workbook = isXssf ? (IWorkbook) new XSSFWorkbook() : new HSSFWorkbook();
*
* DataTable recycledTable = Recycled.RecycledDataTable.Copy();
* recycledTable.TableName = "RECYCLED";
* List<DataTable> absoluteDataTables =
* new List<DataTable>(dataTables.Tables.ToList<DataTable>()) {recycledTable};
*
* foreach (DataTable table in absoluteDataTables)
* {
* string sheetName = table.TableName;
*
* ISheet sheet = workbook.GetSheet(sheetName) ?? workbook.CreateSheet(sheetName);
*
* IRow row0 = sheet.CreateRow(0);
* WritePrefabRow(table, row0);
* foreach (ICell cell in row0)
* if (cell != null)
* {
* ICellStyle style = workbook.CreateCellStyle();
* IFont font = workbook.CreateFont();
* font.Boldweight = (short) FontBoldWeight.Bold;
* style.SetFont(font);
* cell.CellStyle = style;
* }
*
* foreach (DataRow dataRow in table.Rows)
* {
* IRow row = sheet.CreateRow(table.Rows.IndexOf(dataRow) + 2);
* WriteRow(dataRow, row);
* }
*
* //Debug.WriteLine(row0.GetCell(0).ToString());
*
* if (row0.GetCell(0).ToString().Equals("Inventoried") &&
* MainWindow.MasterDataGrids[dataTables.Tables.IndexOf(table)].Columns[0] is GridCheckBoxColumn)
* for (var i = 2; i <= sheet.LastRowNum; i++)
* {
* IRow row = sheet.GetRow(i);
* ICell cell = row.GetCell(0);
*
* if (cell == null) continue;
* ICellStyle style = workbook.CreateCellStyle();
*
* style.FillForegroundColor = table.Rows[i - 2][0].ToString().ToLower().Equals("true")
* ? IndexedColors.LightGreen.Index
* : IndexedColors.Rose.Index;
*
* style.FillPattern = FillPattern.SolidForeground;
* //System.out.println(itemSchools.get(i - counter).isInventoried());
* cell.CellStyle = style;
* }
* }
*
* workbook.Write(new FileStream(excelFilePath, FileMode.Create));
* workbook.Close();*/
Cursor.Current = Cursors.Default;
}
private async void OnExcelExportDataGrid(object sender, RoutedEventArgs e) {
ExcelEngine excelEngine = null;
ExcelExportingOptions options = new ExcelExportingOptions() {
AllowOutlining = (bool)this.AllowOutlining.IsChecked,
ExcelVersion = ExcelVersion.Excel2007,
ExportingEventHandler = exportingHandler,
};
if ((bool)this.customizeColumns.IsChecked) {
options.CellsExportingEventHandler = CustomizedCellExportingHandler;
excelEngine = this.sfDataGrid.ExportToExcel(this.sfDataGrid.View, options);
}
else {
options.CellsExportingEventHandler = CellExportingHandler;
excelEngine = this.sfDataGrid.ExportToExcel(this.sfDataGrid.View, options);
}
var workBook = excelEngine.Excel.Workbooks[0];
var savePicker = new FileSavePicker {
SuggestedStartLocation = PickerLocationId.Desktop,
SuggestedFileName = "Sample"
};
if (workBook.Version == ExcelVersion.Excel97to2003) {
savePicker.FileTypeChoices.Add("Excel File (.xls)", new List<string>() { ".xls" });
}
else {
savePicker.FileTypeChoices.Add("Excel File (.xlsx)", new List<string>() { ".xlsx" });
}
var storageFile = await savePicker.PickSaveFileAsync();
if (storageFile != null) {
await workBook.SaveAsAsync(storageFile);
var msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
var yesCmd = new UICommand("Yes");
var noCmd = new UICommand("No");
msgDialog.Commands.Add(yesCmd);
msgDialog.Commands.Add(noCmd);
var cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd) {
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
excelEngine.Dispose();
}
