using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Columns;
namespace de.Fesslersoft.ExportXtraGridToExcel
{
public partial class ExportForm : Form
{
private DataTable _dataTable;
public ExportForm()
{
InitializeComponent();
}
private void ExportForm_Load(object sender, EventArgs e)
{
FillDataTable();
gridViewExport.OptionsBehavior.AutoPopulateColumns = true;
gridViewExport.OptionsBehavior.ReadOnly = true;
gridViewExport.OptionsBehavior.Editable = false;
gridControlExport.DataSource = _dataTable;
}
private void FillDataTable()
{
//https://documentation.devexpress.com/#WindowsForms/CustomDocument2240
SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder
{
DataSource = "localhost",
IntegratedSecurity = true,
InitialCatalog = "Northwind"
};
using (SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString()))
{
connection.Open();
string queryCommand = "SELECT TOP 100 [ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] FROM [Northwind].[dbo].[Products]";
using (SqlDataAdapter adapter = new SqlDataAdapter(queryCommand, connection))
{
_dataTable = new DataTable();
adapter.Fill(_dataTable);
}
}
}
private void ExportToExcel()
{
//https://www.devexpress.com/Support/Center/Question/Details/Q430217
//https://documentation.devexpress.com/#WindowsForms/CustomDocument1874
//storing current layout
gridViewExport.SaveLayoutToXml(@"C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml");
foreach (GridColumn column in gridViewExport.Columns)
{
//make all export columns visible
column.Visible = true;
}
gridViewExport.ExportToXls(@"C:\Users\mf\Desktop\Neuer Ordner\export.xls");
//restoring the layout, the layout file needs to be deleted manually
gridViewExport.RestoreLayoutFromXml(@"C:\Users\mf\Desktop\Neuer Ordner\tempLayout.xml");
}
private void buttonExportToExcel_Click(object sender, EventArgs e)
{
ExportToExcel();
XtraMessageBox.Show("Export successfull!", this.Text, MessageBoxButtons.OK,MessageBoxIcon.Information,MessageBoxDefaultButton.Button1);
}
}
}