Good Day,
My apologies for the subject, sometimes it is difficult to nail down the issue in the subject line. In any event, my problem is pretty direct. I have a hierarchical grid that I am exporting to excel with no issues. I can call the export function and it does so correctly and loads into excel as expected. However, my issue is this: I have a list of employee ids stored in a session value. On the user UI they click "next" or "previoius" and the page posts back, grabs the next/previous employee id, grabs the data from the database and populates the hierarchical grid. Now, my issue is that my client needs to be able to export Employees (from some ID to another ID) to excel, with each employee being on a seperate sheet. I attempted to use the parameter that allows for an array of Webcontrols, but I keep getting an "object is equal to null" error on the export function. Below is my code, starting with my .aspx grid code, then my export function. I should also note that my grid is loaded completely dynamically from an IEnumerable object.
---SECTION 1 WHDG .ASPX CODE--------
<td> <ig:WebHierarchicalDataGrid ID="whdgWeeklyPayroll" runat="server" Height="700px"
Width="100%" InitialDataBindDepth="-1" EnableDataViewState="True" AutoGenerateBands="true" AutoGenerateColumns="true" OnInitializeRow="whdgWeeklyPayroll_InitializeRow" />
<ig:WebExcelExporter ID="weeWeeklyPayroll" runat="server"></ig:WebExcelExporter>
</td>
---SECTION 2 EXCEL EXPORT FUNCTION----
protected void btn_wdwEE_ExportSubmit_Click(object sender, EventArgs e)
{
//this.wdwExcelExport.WindowState = Infragistics.Web.UI.LayoutControls.DialogWindowState.Hidden;
WebControl[] _gridExport;
int _fromIndex = Get_IndexByEmployeeNumber(this.tbx_wdwEE_ExportEmployeesFrom.Text);
int _toIndex = Get_IndexByEmployeeNumber(this.tbx_wdwEE_ExportEmployeesTo.Text);
int _totalSheets = (_toIndex - _fromIndex) + 1;
_gridExport = new WebControl[_totalSheets];
List<string> _items = new List<string>();
foreach (ListItem l in this.cbxl_wdwEE_ExportViewTypes.Items)
if (l.Selected)
_items.Add(l.Text);
}
for (int i = 0; i < _totalSheets; i++)
_gridExport[i] = new WebHierarchicalDataGrid();
GetSet_Index(_fromIndex + i);
//LoadGrid(this.cbxl_wdwEE_ExportViewTypes);
((WebHierarchicalDataGrid)_gridExport[i]).ID = i.ToString();
((WebHierarchicalDataGrid)_gridExport[i]).InitialDataBindDepth = -1;
((WebHierarchicalDataGrid)_gridExport[i]).AutoGenerateBands = true;
((WebHierarchicalDataGrid)_gridExport[i]).AutoGenerateColumns = true;
((WebHierarchicalDataGrid)_gridExport[i]).EnableDataViewState = true;
((WebHierarchicalDataGrid)_gridExport[i]).Rows.Clear();
((WebHierarchicalDataGrid)_gridExport[i]).DataSource = RetrieveResults(GetSet_Index(null), _items);
((WebHierarchicalDataGrid)_gridExport[i]).DataBind();
//List<string> _hidden = cePayrollReport_Utilities.HiddenColumns_ByRecordType();
//foreach (string s in _hidden)
//{
// if (e.Row.Items.FindItemByKey(s) != null)
// {
// e.Row.Items.FindItemByKey(s).Column.Hidden = true;
// }
//}
//foreach (ContainerGridRecord row in ((WebHierarchicalDataGrid)_gridExport[i]).GridView.Rows)
// row.ExpandChildren();
this.weeWeeklyPayroll.DownloadName = string.Format("HPDSchedule_{0}", DateTime.Now.ToString("MM-dd-yy(HHmm)"));
this.weeWeeklyPayroll.ExportMode = ExportMode.Download;
this.weeWeeklyPayroll.WorkbookFormat = Infragistics.Documents.Excel.WorkbookFormat.Excel97To2003;
this.weeWeeklyPayroll.Export(true, _gridExport);
Hi Jason,
You need to add each grid in your array to the form as the WebExcelExporter is looking there for the actual grids. This should be achievable using:
form1.Controls.Add(_gridExport[i]));
Please let me know if this helps.
Best Regards,
Petar IvanovDeveloper Support EngineerInfragistics, Inc.http://es.infragistics.com/support
The suggestion you gave did work correctly. In addition to this issue, how can I get access to the workbook after the data is exported? I tried the "exported" event but it says the worksheets are null. I would like to name the worksheets and add some header data to the worksheet.
Thank you for this piece of code. It is working as expected.
Thank you for your reply.
I am glad that you were able to resolve your issues. The delay in exporting may be caused by the amount of data and number of grids your are exporting at once.
Please let me know if you have any questions.
Thank you for that part. It was the changing of the export mode to custom that I didn't know needed to be done for this type of situation. Besides the fact that it is extremely slow (upwards to 5mins), it does work as expected.
You can use a Workbook object in order to manipulate the export sheet's name and contents. Here is some sample code for such a scenario:
protected void Button1_Click(object sender, EventArgs e) { WebControl[] array = new WebControl[2]; array[0] = WebDataGrid1; array[1] = WebDataGrid2; //define the workbook and some worksheets. Workbook book = new Workbook(); book.Worksheets.Add("grid1"); //set a custom value in one of the cells in this worksheet book.Worksheets["grid1"].Rows[0].Cells[5].Value = "This is a custom value"; book.Worksheets.Add("grid2"); //first export the grids to each worksheet. Custom export mode is required for that. WebExcelExporter1.ExportMode = Infragistics.Web.UI.GridControls.ExportMode.Custom; WebExcelExporter1.Export(WebDataGrid1, book.Worksheets[0]); WebExcelExporter1.Export(WebDataGrid1, book.Worksheets[1]); //change the export mode back to Download WebExcelExporter1.ExportMode = Infragistics.Web.UI.GridControls.ExportMode.Download; //export the workbook and pass it an empty array (as the grids are already in the worksheets). WebExcelExporter1.Export(book, 0, 0, new WebControl[0]); }
protected void Button1_Click(object sender, EventArgs e) { WebControl[] array = new WebControl[2]; array[0] = WebDataGrid1; array[1] = WebDataGrid2;
//define the workbook and some worksheets. Workbook book = new Workbook(); book.Worksheets.Add("grid1"); //set a custom value in one of the cells in this worksheet book.Worksheets["grid1"].Rows[0].Cells[5].Value = "This is a custom value"; book.Worksheets.Add("grid2");
//first export the grids to each worksheet. Custom export mode is required for that. WebExcelExporter1.ExportMode = Infragistics.Web.UI.GridControls.ExportMode.Custom;
WebExcelExporter1.Export(WebDataGrid1, book.Worksheets[0]); WebExcelExporter1.Export(WebDataGrid1, book.Worksheets[1]);
//change the export mode back to Download WebExcelExporter1.ExportMode = Infragistics.Web.UI.GridControls.ExportMode.Download; //export the workbook and pass it an empty array (as the grids are already in the worksheets). WebExcelExporter1.Export(book, 0, 0, new WebControl[0]);
A detailed sample for using custom worksheets can be accessed at:
http://blogs.infragistics.com/forums/p/55900/286799.aspx