I wanted to know if there is a way to retain grouping, sorting, and filteringg set in a WinForms RadGrid when exporting to Excel.
I have a WinForms application that uses the RadGridview to allow users to sort and group items, however currently when they export the data to Excel the data only exports when they do not have any items grouped.
I wanted to know how I could allow them to use the RadGridview to group, sort, and filter the items then keep those settings when the data is exported to Excel. Are there some settings I need to enable in the Gridview itself or modifications that need to be made to the Export sub-routine to allow this.
| Private Sub btnExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click |
| If SaveFileDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then |
| If (Not SaveFileDialog.FileName.Equals(String.Empty)) Then |
| Dim file As New FileInfo(SaveFileDialog.FileName) |
| If file.Extension.Equals(".xls") Then |
| Export(SaveFileDialog.FileName) |
| Else |
| MessageBox.Show("Invalid file type") |
| End If |
| Else |
| MessageBox.Show("Please enter a file name.") |
| End If |
| Me.btnExportToExcel.Enabled = True |
| End If |
| End Sub |
| Private Sub Export(ByVal filepath As String) |
| Me.btnExportToExcel.Enabled = False |
| Dim exporter As New ExportToExcelML() |
| Dim exportThemeColors As Boolean = True |
| exporter.RunExport(Me.grdData, filepath, ExportToExcelML.ExcelMaxRows._65536, exportThemeColors) |
| Me.btnExportToExcel.Enabled = True |
| MessageBox.Show("Exporting Finished.") |
| End Sub |
namespace
WindowsFormsApplication3
{
public partial class Grid : Form
{
public Grid()
{
InitializeComponent();
}
private DataTable dt = new DataTable();
private void Grid_Load(object sender, EventArgs e)
{
dt.Columns.Add("Value");
DataRow dr = dt.NewRow();
dr[0] = "Test";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = "Test1";
dt.Rows.Add(dr);
DataTable table = new DataTable();
table.Columns.Add(
"Item");
table.Columns.Add(
"Type");
table.Columns.Add(
"Buy/Sell");
Random r = new Random();
for (int i = 0; i < 5; i++)
{
if(i< 2)
table.Rows.Add(
"Row " + i, 1);
else
table.Rows.Add(
"Row " + i, 2);
}
this.radGridView1.DataSource = table;
radGridView1.Columns[1].IsVisible =
false;
}
private void radGridView1_CreateCell(object sender, GridViewCreateCellEventArgs e)
{
if (e.Row is GridDataRowElement && e.Column.HeaderText == "Buy/Sell")
{
string s = Convert.ToString(e.Row.RowInfo.Cells[1].Value);
if (s == "1")
{
e.CellElement =
new ComboboxCell(e.Column, e.Row,dt,"ComboBox");
}
else if (s == "2")
e.CellElement =
new TextboxCell(e.Column, e.Row, "Hello", "TextBox");
}
}
}
public class ComboboxCell : GridDataCellElement
{
RadComboBoxElement comboBox;
DataTable table;
string controlType = string.Empty;
string text;
public ComboboxCell(GridViewColumn column, GridRowElement rowElement, DataTable dataTable, string type) :
base(column, rowElement)
{
table = dataTable;
controlType = type;
}
protected override void SetContentCore(object value)
{
comboBox.SelectedItem = comboBox.FindItem(
Convert.ToString(value));
}
private void comboBox_SelectedIndexChanged(object sender, EventArgs e)
{
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
comboBox.SelectedIndexChanged +=
new EventHandler(comboBox_SelectedIndexChanged);
}
base.Dispose(disposing);
}
protected override void CreateChildElements()
{
if (controlType == "ComboBox")
{
comboBox =
new RadComboBoxElement();
comboBox.DropDownStyle = Telerik.WinControls.
RadDropDownStyle.DropDownList;
comboBox.DataSource = table;
comboBox.DisplayMember =
"Value";
this.Children.Add(comboBox);
}
}
}
public class TextboxCell : GridDataCellElement
{
RadTextBoxElement textBox;
string controlType = string.Empty;
string text;
public TextboxCell(GridViewColumn column, GridRowElement rowElement, string value, string type) :
base(column, rowElement)
{
text = value;
controlType = type;
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
}
base.Dispose(disposing);
}
protected override void CreateChildElements()
{
textBox =
new RadTextBoxElement();
textBox.Text = text;
this.Children.Add(textBox);
}
protected override void SetContentCore(object value)
{
textBox.Text =
Convert.ToString(value);
}
}
}
this
.radGridView1.MasterGridViewTemplate.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
But the problem with that is it automatically sizes the columns to be all equal which is not suitable for this application. And in this case giving each column a fixed length is also not an option as this application needs to run on several resolutions. When i tried using
this.radGridView1.MasterGridViewTemplate.BestFitColumns();
The columns only appear on part of the child form(ie if the user resizes the child form, the columns do not resize with it). So if you could please let me know if there is a workaround. Thanks in advance.
| // |
| // RadMaskedEditBox_Test |
| // |
| this.RadMaskedEditBox_Test.Culture = null; |
| this.RadMaskedEditBox_Test.EnableKeyMap = true; |
| this.RadMaskedEditBox_Test.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(0)))), ((int)(((byte)(20)))), ((int)(((byte)(107))))); |
| this.RadMaskedEditBox_Test.Location = new System.Drawing.Point(205, 10); |
| this.RadMaskedEditBox_Test.Mask = "N1"; |
| this.RadMaskedEditBox_Test.MaskType = Telerik.WinControls.UI.MaskType.Numeric; |
| this.RadMaskedEditBox_Test.Name = "RadMaskedEditBox_Test"; |
| this.RadMaskedEditBox_Test.PlaceHolder = ' '; |
| this.RadMaskedEditBox_Test.RightToLeft = System.Windows.Forms.RightToLeft.Yes; |
| // |
| // |
| // |
| this.RadMaskedEditBox_Test.RootElement.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(0)))), ((int)(((byte)(20)))), ((int)(((byte)(107))))); |
| this.RadMaskedEditBox_Test.Size = new System.Drawing.Size(40, 20); |
| this.RadMaskedEditBox_Test.TabIndex = 23; |
| this.RadMaskedEditBox_Test.Text = "0.0"; |
| this.RadMaskedEditBox_Test.Value = "0"; |
| #region Patches for Telerik |
| /// <summary> |
| /// Max length control |
| /// </summary> |
| private void RadMaskedEditBox_Test_KeyPress(object sender, KeyPressEventArgs e) |
| { |
| if (this.RadMaskedEditBox_Test.Text.Length == 5) |
| e.Handled = true; |
| } |
| #endregion |