Import/Export to Excel

[Mark Jayvee]

I am exporting my sfdatagrid using the below code, and this will save automatically to the path where I hard coded it.

What I want is to make it flexible and let me choose my desired file location. How can I do that?

Another scenario is that, I notice when I export the excel file it doesn't include my checkbox?

string filename = projectnumber + "-" + projectname + "-" + DateTime.Now.ToString("dMMMyyyy_HHmmss") + ".xlsx";

var options = new ExcelExportingOptions();

            var excelEngine = dataGridView1.ExportToExcel(dataGridView1.View, options);

            var workBook = excelEngine.Excel.Workbooks[0];

            workBook.SaveAs(@"C:\Users\Public\Documents\" + filename);

thank you so much.

[Vijayarasan Sivanandham]

Hi Mark Jayvee,

Thank you for contacting Syncfusion support.

Please find answer for your queries below 

Queries	Solutions
What I want is to make it flexible and let me choose my desired file location. How can I do that?	Your requirement can be achievd by using SaveFileDialog while export to excel. For more information, please refer the below UG link, UG Link: https://help.syncfusion.com/windowsforms/datagrid/exporttoexcel#save-using-file-dialog
Another scenario is that, I notice when I export the excel file it doesn't include my checkbox?	Currently, we are analyzing your requirement of “Export the excel file l with checkbox in SfDataGid” We will validate and update you the details on or before April 23, 2021.

Please let us know if you have any concerns in this. 

Regards,
Vijayarasan S 

[Vijayarasan Sivanandham]

Hi Mark Jayvee,

Thank you for your patience.

Your requirement can be achieved by add the checkbox while exporting to excel by using the CellExporting event of the ExcelExportingOption. Please refer the below code snippet, 

GridExcelExportingOptions.CellExporting += Options_CellExporting1; 

 

private void Options_CellExporting1(object sender, Syncfusion.WinForms.DataGridConverter.Events.DataGridCellExcelExportingEventArgs e) 

{ 

            //check the checkbox column  

            if (e.CellType == ExportCellType.RecordCell && e.ColumnName == "IsShipped") 

            {    

                //add the checkbox into excel shhet 

                var checkbox = e.Range.Worksheet.CheckBoxes.AddCheckBox(e.Range.Row, e.Range.Column, 20, 20); 

                 

                //set the checked or unchecked state based on cell value 

                if (e.CellValue.ToString().ToLower() == "true")                    

                    checkbox.CheckState = ExcelCheckState.Checked;                  

                else if (e.CellValue.ToString().ToLower() == "false")                    

                    checkbox.CheckState = ExcelCheckState.Unchecked; 

 

                checkbox.LinkedCell = e.Range.Worksheet[e.Range.AddressLocal]; 

 

                e.Handled = true; 

            } 

} 

Sample Link: https://www.syncfusion.com/downloads/support/forum/164675/ze/Sample-1636133857 

For more information related to Printing, please refer the below UG link,

UG Link: https://help.syncfusion.com/windowsforms/datagrid/exporttoexcel#customize-cell-value-while-exporting 

https://help.syncfusion.com/file-formats/xlsio/working-with-drawing-objects#check-box 

Please let us know, if you require further assistance on this. 

Regards,
Vijayarasan S 

[Mark Jayvee]

Thank you, Exporting works.

Is it possible to import the excel file back to sfdatagrid with selected column range?

Example, I have 6 Columns from excel file as shown. And I want to import is the 3 Columns only from Order ID to Customer Name.

Order ID	Customer ID	Customer Name	Country	Ship City	Is Shipped
1001.00	ALFKI	Thomas Hardy	Germany	Berlin	TRUE
1002.00	ANATR	Laurence Lebihan	Mexico	Mexico	FALSE
1003.00	ANTON	Antonio Moreno	Mexico	Mexico	TRUE
1004.00	AROUT	Thomas Hardy	UK	London	TRUE
1005.00	BERGS	Christina Berglund	Sweden	Lula	FALSE

Cheers,

Mark

[Vijayarasan Sivanandham]

Hi Mark Jayvee,

Thanks for the update.

Your requirement can be achieved by using ExportDataTable method and manually column generation in SfDataGrid while import the excel. Please refer the below code snippet, 

private void button1_Click(object sender, EventArgs e) 

{ 

            using (ExcelEngine excelEngine = new ExcelEngine()) 

            { 

                IApplication application = excelEngine.Excel; 

                 

                //get the saved excel file 

                IWorkbook workbook = application.Workbooks.Open("s.xlsx"); 

                IWorksheet sheet = workbook.Worksheets[0]; 

                application.DefaultVersion = ExcelVersion.Excel2013; 

 

                //Export data from worksheet used range to a DataTable 

                DataTable customersTable = sheet.ExportDataTable(1, 1, 15, 3, ExcelExportDataTableOptions.ColumnNames); 

 

                //clear the column collection 

                this.sfDataGrid.Columns.Clear(); 

                //set the exported datatable datasource into SfDataGrid 

                this.sfDataGrid.DataSource = customersTable; 

 

                //here mention import the excel file back to sfdatagrid with selected column range 

                this.sfDataGrid.Columns.Add(new GridTextColumn() { MappingName = "Order ID", HeaderText = "Order ID" }); 

                this.sfDataGrid.Columns.Add(new GridTextColumn() { MappingName = "Customer ID", HeaderText = "Customer ID" }); 

                this.sfDataGrid.Columns.Add(new GridTextColumn() { MappingName = "Customer Name", HeaderText = "Customer Name" }); 

                

            } 

} 

Sample Link: https://www.syncfusion.com/downloads/support/forum/164675/ze/ModifiedSample190861860

UG Link: https://help.syncfusion.com/windowsforms/datagrid/exporttoexcel#import-form-excel-to-sfdatagrid 

https://help.syncfusion.com/file-formats/xlsio/getting-started-create-excel-file-csharp-vbnet#export-data-from-excel-worksheets

Please let us know, if you require further assistance on this.

Regards,
Vijayarasan S