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