I create a workbook file via Infragistics.Documents.Excel.
I export an UltraWinGrid to a sheet in workbook via UltraGridExcelExporter. This works, however I have a multi-column picklist in the grid that I want to maintain. So after getting the sheet, I can edit the sheet - I think the export won't do the multi-column picklist.
I know there is some validation features in Excel to do this but I was hoping we can optimize or have an example of doing this already.
So, I have two columns A and B for instance
Column A = Well Pad
Column B = Unit
So for instance, Well Pads are 61, 62, 63, 64
Well Pad 61 has Units 1, 2, 3
Well Pad 62 has Units 4, 5, 6
Well Pad 63 has Units 1, 4, 7
Well Pad 64 has Units 5, 6
Therefore, the UltraWinGrid has a picklist on the Well Pad that is a multi-column ValueList with Well Pad, Well Pad Unit, and Description.
When a value is chosen from the picklist, it fills in the Well Pad + the Unit. So for this example, the picklist would have 11 entries (61 1 desc1, 61 2 desc2, 61 3 desc 3, 62 1 desc 4, ....) and after picklist selection, the 2 fields (Well Pad + Unit will be filled in). I can live without the descriptions and just have a way of selecting always a valid Well Pad + Unit combination on the grid. I think it is doable by creating a picklist sheet and adding validation but it seems a little convoluted (if that is how we have to do this, hopefully you have a simple example I can follow - aka a prototype).
Is there an example of doing this in Infragistics probably via the Infragistics.Documents.Excel library of functions? Alternatively a step by step way of doing this.
Hello,
Exporting dropdown lists to excel was introduced in version 2013 Volume 1 for Windows Forms. With the ExportValueList property set to true for your UltraWinGridExcelExporter any column that contains a value list can be exported to Excel.
For more details with an included sample, please visit Export DropDown lists to Excel.
Let me know if you have any quesitons regarding this matter.
Cool example, but it doesn't do exactly what I want.
I have 2 columns in my excel SS:
Area + Well Pad Number
Well Pad Number is read only.
UltraDataSource _AreaWellPadUDS= new UltraDataSource();
_AreaWellPadUDS.Band.Columns.Add("Area");
_AreaWellPadUDS.Band.Columns.Add("Well Pad");
_AreaWellPadUDS.Band.Columns.Add("Description");
{
object[] values = { "64", "1", "My Description" };
_AreaWellPadUDS.Band.DataSource.Add(values);
}
object[] values = { "64", "2", "My Description2" };
object[] values = { "65", "1", "My Description3" };
UltraDropDown udd = new UltraDropDown();
udd.DisplayLayout.Override.HeaderClickAction = HeaderClickAction.SortMulit;
udd.AllowRowFiltering = Infragistics.Win.DefaultableBoolean.True;
udd.DisplayMember = "Area";
udd.ValueMember = "Area";
MyGrid.DisplayLayout.Bands[0].Columns["Area"].ValueList = udd;
After Cell Update -> set the MyGrid fill in Well Pad based on the selection from Area udd...
So ideally, would like a multi-column picklist if possible that selects two separate columns (Area + Well Pad). I would then like to create a formula that takes Area + Column + Well Pad and puts into a new column. Thus, the exported excel allows users to enter valid data and generate a new combined (concatenated) field based on the values from the picklist.
I expect the formulat might have to be added manually - aka $A2 & $A5 & $A3 for instance.
If not possible, would like to have an area picklist and a validation / picklist on the well pad based on the selection of the area.For instance if Area = "64", then Well Pad = "1" or "2"if Area = "65", then Well Pad = "2".Anyway of achieving this???
See http://www.contextures.com/xlDataVal02.html as an example of kind of what I want -> dependent picklists would work. For instance Area would determine which Well Pads are Valid.I can do easily in code but would like to create an export that does something similar without having to create an additional tab ... like the example does => seems too convoluted.
After further research with our team, we've concluded that, yes, in theory generating an Excel file based on your Grid's specifications is supported however our components do not extend or offer any additional built-in functionality to automatically generate dependent dropdowns in Excel.
We do not have a sample of this demonstrated. This would be need implemented yourself based on own your requirements by creating dynamic data validation lists in Excel using various formulas like INDIRECT.
Our Excel library will allow you to create these forumulas, as long as you hook the right events from our UltraGridExcelExporter component and modifying the data validation of a cell during/after the export has taken place. eg. API overview, events included.
http://help.infragistics.com/doc/WinForms/2014.2/CLR4.0/?page=Infragistics4.Win.UltraWinGrid.ExcelExport.v14.2~Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter_members.html
I recommend reaching out to Microsoft via their forums for more assistance with this here:
https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev
Let me know if you have any questions regarding this matter.
PS as a side note> in your original example posted, does Excel have a display value vs. an internal value. For instance, after exporting to Excel, can you know the internal value for SONY = 3 => aka access the internal value or display value (might want to use both for whatever bizarre reason - I expect would only want to use the internal value but who knows) in a formula for another cell.
Obviously can distinguish in Infragistics UltraWinGrid, not so certain about Excel (not an Excel expert).