RadGrid Excel.Format Export

[Tineke]

Hi all,

I'm having problems exporting my radGrid to Excel.
I want to format my excel file by using the GridExportExcelMLRowCreated and GridExportExcelMLStyleCreated events.
Therefore I'm following the instructions of the demo on the telerik site:
http://www.telerik.com/DEMOS/ASPNET/Prometheus/Grid/Examples/GeneralFeatures/Exporting/DefaultCS.aspx

The first thing I don't understand is that the sample code sets the exportonlydata = true;
But when you read the description, it says that you need to set the exportonlydate = false when you want to use Excel.Format.

But if I do the following, I get an error: Object not set to an instance of an object. NullReferenceException. This is not an error in de code, but this error appears when it wants to load the excel file --> after calling the ExportToExcel() Method.

rgd.ExportSettings.Excel.Format =

GridExcelExportFormat.ExcelML;
rgd.ExportSettings.ExportOnlyData = true;
rgd.ExportSettings.OpenInNewWindow = true;
rgd.MasterTableView.ExportToExcel();

When I set the ExportOnlyData = false, my excel file opens, but nothing appears on the worksheet. If I set OpenInNewWindow = false --> my .aspx page hangs. Nothing happens.

The second issue I have is that I've set the GridExportExcelMLRowCreated and GridExportExcelMLStyleCreated events on my radGrid, but this code is not executed. I've placed a breakpoint, but nothing happens with these events.


Can someone please help me further? A simple export succeeds or when I place the GridExcelExportFormat.HTML  as Excel.Format, everything works fine, but I'm not able to place any style on my excel export like in the example of telerik.

Thanks in advance!

Regards,
Tineke

[Iana Tsolova]

Hello Tineke,

Please excuse us for the mistake in the description of the example and thank you that you pointed it out. To export in ExcelML format the ExportOnlyData property should be set to true.

Unfortunately I couldn't reproduce the problems you described. Find attached a sample and tell us how it behaves on your end and what differs in your case. This can lead us to the source of the problem to address it accordingly.

Kind regards,
Iana
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Tineke]

Hi,

Thanks for the response, but it didn't help me any further.

This is my code of the radgrid, where you can see I've set the OnExcelMLExportRowCreated and OnExcelMLExportStylesCreated events:

<

telerik:RadGrid ID="rgdContracts1" runat="server" BorderWidth="1px" BorderStyle="solid" Skin="WebBlue" AllowFilteringByColumn="False" PageSize="1000" AutoGenerateColumns="False"
OnExcelMLExportRowCreated="Contracts1ExcelRowCreated" OnExcelMLExportStylesCreated="Contracts1ExcelStylesCreated">
<MasterTableView CommandItemDisplay="None" CurrentResetPageIndexAction="SetPageIndexToFirst" Dir="LTR" Frame="Border" TableLayout="Auto">
<Columns>
<telerik:GridBoundColumn ........ all my columns</Columns>

This is de code on my export button:

rgdContracts1.ExportSettings.FileName =

"Contracts_" + DateTime.Now;
rgdContracts1.ExportSettings.ExportOnlyData = true;
rgdContracts1.ExportSettings.OpenInNewWindow = true;
rgdContracts1.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
rgdContracts1.MasterTableView.ExportToExcel();


My Events in the code behind (They contain no code yet, I've only set a breakpoint, but I never get there:

protected void Contracts1ExcelStylesCreated(object source, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLStyleCreatedArgs e)
{ }

protected void Contracts1ExcelRowCreated(object source, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowCreatedArgs e)
{}



When I debug, I pass the code o my export button succesfully. But afther "ExportToExcel()" I get the following error below.
I hope someone can help me further with this.
(I know it is in dutch but it is the same as "Object is not set to an instance of an object....")

De objectverwijzing is niet op een exemplaar van een object ingesteld.

Beschrijving: Er is een onverwerkte uitzondering opgetreden tijdens het uitvoeren van de huidige webaanvraag. Raadpleeg de stacktracering voor meer informatie over deze fout en de oorsprong ervan in de code.

Details van uitzondering: System.NullReferenceException: De objectverwijzing is niet op een exemplaar van een object ingesteld.

Fout in bron:

Er is een onverwerkte uitzondering gegenereerd tijdens het uitvoeren van de huidige webaanvraag. Aan de hand van de onderstaande tracering van de uitzonderingsstack kunt u meer informatie verkrijgen over de oorsprong en de locatie van de uitzondering.

Stacktracering:

[NullReferenceException: De objectverwijzing is niet op een exemplaar van een object ingesteld.] Telerik.Web.UI.Grid.Export.TableViewExporter.ExcelExportRenderForm(HtmlTextWriter nullWriter, Control form) +407 System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +98 System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +20 System.Web.UI.HtmlControls.HtmlForm.RenderChildren(HtmlTextWriter writer) +59 System.Web.UI.HtmlControls.HtmlForm.Render(HtmlTextWriter output) +68 System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25 System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121 System.Web.UI.HtmlControls.HtmlForm.RenderControl(HtmlTextWriter writer) +37 Telerik.Web.UI.Grid.Export.TableViewExporter.ExcelExportRenderPage(HtmlTextWriter nullWriter, Control page) +102 System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +98 System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +20 System.Web.UI.Page.Render(HtmlTextWriter writer) +26 System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25 System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121 System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22 Telerik.Web.UI.RadAjaxControl.RenderPageInAjaxMode(HtmlTextWriter writer, Control page) +45 System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +98 System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +20 System.Web.UI.Page.Render(HtmlTextWriter writer) +26 System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25 System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121 System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22 Telerik.Web.UI.RadAjaxControl.RenderPageInAjaxMode(HtmlTextWriter writer, Control page) +45 System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +98 System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +20 System.Web.UI.Page.Render(HtmlTextWriter writer) +26 System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25 System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121 System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2558

---

Versiegegevens: Microsoft .NET Framework Versie:2.0.50727.1433; ASP.NET Versie:2.0.50727.1433



Many Thanks already!!!
Regards,
Tineke

[Iana Tsolova]

Hello Tineke,

The only way for ExcelStylesCreated and ExcelRowCreated events not to fire is if your export button is updating the grid via AjaxManager. So if you've added an AjaxSetting where the button as an initiator and the grid is the updated control, remove it and try if the problem persists.

Please read more about grid exporting and ajax in this online help topic.

Regards,
Iana
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Tineke]

Hi,

Yeah I know. But I'm not using AjaxManager for these buttons. Although, I know also that buttons can behave strange because of ajaxmanger, even when you don't manage  ajax settings for these buttons.
--> For example when you want to upload a file or use the asp fileupload.
---> I had the same thing for this export.

Therefore I'm already using the following code (which works successfull by the way as I can export to word and excel without setting Format to ExcelML or by using Format = Html. Just when I want to use the Format = ExcelML, it doesn't work and I get the error as you can see in one of my previous posts):

<

telerik:RadCodeBlock ID="RadCodeBlock1" runat="server">
<script type="text/javascript">

//on export button click temporarily disables ajax to perform export actions
function conditionalPostback(sender, args)
{
             if(args.EventTarget == "<%= btnExportExcel.UniqueID %>")  { 
                    args.EnableAjax = false; } 
            if(args.EventTarget == "<%= btnExportWord.UniqueID %>") { 
                    args.EnableAjax = false; } 
}
</script>
</telerik:RadCodeBlock>

This is the code of my AjaxManager. As you can see no settings are set  for btnExportExcel and btnExportWord.

<

telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
<AjaxSettings>
<telerik:AjaxSetting AjaxControlID="btnSearch">
<UpdatedControls>
<telerik:AjaxUpdatedControl ControlID="pnlRadGrids" LoadingPanelID="RadAjaxLoadingPanel1" />
</UpdatedControls>
</telerik:AjaxSetting>
<telerik:AjaxSetting AjaxControlID="rbtnBasic">
<UpdatedControls>
<telerik:AjaxUpdatedControl ControlID="pnlContracts" />
<telerik:AjaxUpdatedControl ControlID="pnlBasic" />
<telerik:AjaxUpdatedControl ControlID="pnlExtended" />
<telerik:AjaxUpdatedControl ControlID="pnlRadGrids" LoadingPanelID="RadAjaxLoadingPanel1" />
</UpdatedControls>
</telerik:AjaxSetting>
<telerik:AjaxSetting AjaxControlID="rbtnExtended">
<UpdatedControls>
<telerik:AjaxUpdatedControl ControlID="pnlContracts" />
<telerik:AjaxUpdatedControl ControlID="pnlBasic" />
<telerik:AjaxUpdatedControl ControlID="pnlExtended" />
<telerik:AjaxUpdatedControl ControlID="pnlRadGrids" LoadingPanelID="RadAjaxLoadingPanel1" />
</UpdatedControls>
</telerik:AjaxSetting>
</AjaxSettings>
</telerik:RadAjaxManager>

This is the code sample of my buttons:

<

telerik:RadAjaxPanel ID="radAjaxExportPanel" Width="100%" Height="35px" runat="server" ClientEvents-OnRequestStart="conditionalPostback">

<div style="padding:5px; vertical-align:bottom; height:35px; background-color:#BBC1C9; border:solid 1pt gray;">
<asp:Button ID="btnExportExcel" runat="server" OnClick="btnExportExcel_Click" Width="100px" Text="Export to Excel" BackColor="transparent" BorderWidth="0px" Font-Underline="true" />
<asp:Button ID="btnExportWord" runat="server" OnClick="btnExportWord_Click" Width="100px" Text="Export to Word" BackColor="transparent" BorderWidth="0px" Font-Underline="true" />
</div>

</telerik:RadAjaxPanel>

I don't know which information/code/samples of my application can help you further in helping me, otherwise let me know.

Thanks again for the help!

Regards,
Tineke

[Iana Tsolova]

Hi Tineke,

I followed your scenario and used the code you've provided to replicate the unexpected behavior but unfortunately with no result. Could you please have a look at this code library on how to export RadGrid with Ajax enabled and check if something differs in your case. Also, you can open a formal support ticket, and send us the problematic code, in the form of a working sample, for further investigation.

Regards,
Iana
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Ramesh]

I get the same problem trying to export with ExportSettings.ExportOnlyData set to false and ExportSettings.Excel.Format = ExcelML

This is an issue with the telerick RAD Grid export process. Please advise

thanks

[Tineke]

Hi Ramesh,

did someone help you further on this?
I still haven't got a solution for this.

Thx!

[Ramesh]

Hi Tineke,
No. Noone has helped me with this. I see that you have been in contact with Telerik and have sent them sample projects but they haven't been able to reproduce it. This is surprising. I am able to reproduce it with my application which use the same code you are for exporting to XL. Their sample on the web seems to work but that is hosted on their server. I did not want to go thru the pain of creating a sample only to be told that Telerik cannot reporduce it,

Just the fact that you and I both have run into this issue convinces me that this is an issue with Telerik control. Your setup and mine are totally disconnected and yet we ran into this.

I hope someone from Telrik reading this will go the extra mile in addressing this issue.

thanks
ramesh

[Ramesh]

I was able to find the cause of the problem. If you set EnableViewState="false" on the grid then you will encounter the

RegisterControlState should be called before or during PreRender

I am not sure why disabling ViewState should cause this issue because I thought ASP.Net 2.0 later introduced the concept of control state to handle the disable the view state issue.

Setting EnableViewState="true" I was able to get Export to work as expected and control the formatting of the exported XLS via the triggered events.

Hope This Helps someone

ramesh

[Iana Tsolova]

Hello Tineke,

Have you tried if the problem persists if you disable the Ajax on the page? Please let me know if it makes any difference.

Greetings,
Iana
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[sudheer]

Hi all,

I have the same problem. when i export to excel as HTML format every thing is perfect. but when i try in ExcelML format nothing is working. the  OnExcelMLExportStylesCreated and OnExcelMLExportRowCreated events are also not fired. i have also set EnableViewState as true.

[Iana Tsolova]

Hi sudheer,

Could you please elaborate on your scenario and the issues you are facing? For instance:

• How is your grid bound and to what datasource?
• Is the ExportOnlyData property of the export settings set to true?
• Do you receive any error during export, or an empty excel sheet, or some other unexpected behavior?

Any additional information could be of help in isolating the problems with the ExcelML export.

Regards,
Iana
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[N Santhosh]

Hi,

I am new to RadControls in ASP.I have to export the RadGrid data to excel.While doing so I find that the records are exported correctly but in the same excel sheet the source code also is displayed.Looks so strange.Any one encountered simila rproblems pls suggest possible reasons.

Thanks,
SNK

[Jared Lewis]

Has there been a resolution to this issue? I hate when these threads stop with no resolution but the problem still persists.

I have the same issue where OnExcelMLExportStylesCreated  & OnExcelMLExportRowCreated are not firing when exporting to Excel.

<telerik:RadAjaxManager ID="RadAjaxManager1" runat="server" EnableAJAX="true">
            <ClientEvents OnRequestStart="onRequestStart" />...

<telerik:RadGrid ID="grdNegotiationList" runat="server" Width="100%" AutoGenerateColumns="false" Skin="Windows7"
                                AllowPaging="true" AllowSorting="true" AllowFilteringByColumn="true" ShowStatusBar="false" BorderStyle="none"
                                OnItemDataBound="grdNegotiationList_OnItemDataBound" OnSortCommand="grdNegotiationList_OnSortCommand"
                                OnItemCommand="grdNegotiationList_OnItemCommand" OnPageIndexChanged="grdNegotiationList_OnPageIndexChanged"
                                OnItemCreated="grdNegotiationList_OnItemCreated" OnNeedDataSource="grdNegotiationList_OnNeedDataSource"
                                OnExcelMLExportStylesCreated="grdNegotiationList_OnExcelMLExportStylesCreated" OnExcelMLExportRowCreated="grdNegotiationList_OnExcelMLExportRowCreated"
                                PageSize="100">
                                <MasterTableView CommandItemDisplay="top" DataKeyNames="debt_id" ClientDataKeyNames="debt_id" NoMasterRecordsText="No Data Available"
                                    TableLayout="Fixed">
                                    <CommandItemSettings ShowAddNewRecordButton="false" ShowRefreshButton="false" ShowExportToWordButton="true"
                                        ShowExportToExcelButton="true" ShowExportToCsvButton="true" ShowExportToPdfButton="true" />
                                    <Columns>

JS:

function onRequestStart(sender, args) {
            debugger;
            if (args._eventArgument.indexOf("ExportToExcel") >= 0 ||
                    args._eventArgument.indexOf("ExportToWord") >= 0 ||
                    args._eventArgument.indexOf("ExportToPdf") >= 0 ||
                    args._eventArgument.indexOf("ExportToCsv") >= 0) {

                args.set_enableAjax(false);
            }
        }

 protected void grdNegotiationList_OnItemCommand( object sender, GridCommandEventArgs e )
        {
            RadGrid grid = (RadGrid) sender;

            if ( (e.CommandName == RadGrid.FilterCommandName) )
                _storeGridState = true;
            else if ( e.CommandName == RadGrid.ExportToExcelCommandName ||
                e.CommandName == RadGrid.ExportToWordCommandName ||
                e.CommandName == RadGrid.ExportToCsvCommandName ||
                e.CommandName == RadGrid.ExportToPdfCommandName )
            {
                grid.ExportSettings.ExportOnlyData = true;
                grid.ExportSettings.IgnorePaging = true;
                grid.ExportSettings.OpenInNewWindow = true;

            }

        }

        protected void grdNegotiationList_OnExcelMLExportRowCreated( object source, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowCreatedArgs e )
        {
            if ( e.RowType == Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowType.DataRow )
            {
                if ( e.Row.Cells[0] != null && ((string) e.Row.Cells[0].Data.DataItem).Contains( "U" ) )
                {
                    e.Row.Cells[0].StyleValue = "MyCustomStyle";
                }
            }
        }

        protected void grdNegotiationList_OnExcelMLExportStylesCreated( object source, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLStyleCreatedArgs e )
        {
            foreach ( Telerik.Web.UI.GridExcelBuilder.StyleElement style in e.Styles )
            {
                if ( style.Id == "headerStyle" )
                {
                    style.FontStyle.Bold = true;
                    style.FontStyle.Color = System.Drawing.Color.Gainsboro;
                    style.InteriorStyle.Color = System.Drawing.Color.Wheat;
                    style.InteriorStyle.Pattern = Telerik.Web.UI.GridExcelBuilder.InteriorPatternType.Solid;
                }
                else if ( style.Id == "itemStyle" )
                {
                    style.InteriorStyle.Color = System.Drawing.Color.WhiteSmoke;
                    style.InteriorStyle.Pattern = Telerik.Web.UI.GridExcelBuilder.InteriorPatternType.Solid;
                }
                else if ( style.Id == "alternatingItemStyle" )
                {
                    style.InteriorStyle.Color = System.Drawing.Color.LightGray;
                    style.InteriorStyle.Pattern = Telerik.Web.UI.GridExcelBuilder.InteriorPatternType.Solid;
                }
            }

            Telerik.Web.UI.GridExcelBuilder.StyleElement myStyle = new Telerik.Web.UI.GridExcelBuilder.StyleElement( "MyCustomStyle" );
            myStyle.FontStyle.Bold = true;
            myStyle.FontStyle.Italic = true;
            myStyle.InteriorStyle.Color = System.Drawing.Color.Gray;
            myStyle.InteriorStyle.Pattern = Telerik.Web.UI.GridExcelBuilder.InteriorPatternType.Solid;
            e.Styles.Add( myStyle );
        }

Result... No formatting of Excel worksheet and not firing off OnExcelMLExportStylesCreated  or OnExcelMLExportRowCreated events.

Please advise. This should be enough code to recreate.

Note: I am opening the Excel document in Excel 2007.

I also need instruction on how to remove a column while rendering the Excel row... that is mainly why I need this code to work...

Jared

[Daniel]

Hello Jared,

I'm not sure that I understand what problem you are referring to? Can you please provide some more information?

Note that the OnExcelMLExportStylesCreated and OnExcelMLExportRowCreated events fire only when exporting to ExcelML format. I recommend that you add the following code:

...
grid.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
...

As to the last question: The easiest way to hide a given column is to set its Visible property to false.

RadGrid1.MasterTableView.GetColumn("MyColumn").Visible = false;

ExcelML export
Word/Excel export (HTML-based)

Regards,
Daniel
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.