RadGrid export to excel with warning message

[zorro]

Hi team,

I found many thread about this issue. Like below

http://www.telerik.com/community/forums/wpf/gridview/export-to-excel-2007.aspx
http://www.telerik.com/community/forums/aspnet-ajax/grid/excel-export-the-file-you-are-trying-to-open-filename-is-in-a-different-format-than-specified-by-the-file-extension.aspx

And the link how to fix (use registry)
http://www.itexperience.net/2008/03/17/excel-2007-error-different-format-than-specified-by-the-file-extension/

But seem, it was before 2012. Our client has concern this warning message. So I would like to receive the ways to solve but not use Registry.

Thanks.

[Telugu]

RadGrid export to excel with warning message

"The file you are trying to open, '[filename]', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

  (Yes | No | Help)

Any Solution for this

Thanks,
Praveen

[Kostadin]

Hi Telugu,

If you would like to avoid this message you have to use our latest Excel export format which is based on a binary XLS (BIFF) format. Additional information about this warning could be found at the following help topics.
MSDN blogs: Excel 2007 Extension Warning On Opening Excel Workbook from a Web Site
Grinn blog: "The file you are trying to open, '[filename]', is in a different format"

Greetings,
Kostadin
the Telerik team

[Kostadin]

Hello Kim,

The warning message you received is seen only with the HTML-based Excel export. Now we have introduced a new Excel export format (in Q2 2012) which is based on a binary XLS (BIFF) format and is supported in all versions of Microsoft Office, starting from 2003. With it, there is no longer a warning message when you open the exported file. To see the new Excel export in action, you can check this demo: http://demos.telerik.com/aspnet-ajax/grid/examples/export/biffexport/defaultcs.aspx

All the best,
Kostadin
the Telerik team

[Radha]

Hi Kostadin,

     With the new format "BIFF" can we use old events like below or do we need to rewrite the code accordingly.

 

protected void grdReport_ExcelMLExportRowCreated(object sender, GridExportExcelMLRowCreatedArgs e)

 

{

}

protected

 

void grdReport_ExcelMLExportStylesCreated(object source, GridExportExcelMLStyleCreatedArgs e)

 

{

}

Please suggest.

Regards,
Radha

[Kostadin]

Hello Radha,

I am afraid you could not use those events when using BIFF Export. There is a separate event called BiffExporting. You could find more information at the following help article. Additionally you could check out the following live example.

All the best,
Kostadin
the Telerik team

[Priyalakshmi]

Hi Kostadin,

I am also facing the same problem while exporting data to excel. But I am using WPF. Is the new component(BIFF Export).
available for wpf? Please help.


Regards,
Priyalakshmi

[Kostadin]

Hi Priyalakshmi,

I am afraid that Biff export is not supported in WPF. More information could be found at the following help article. If you have any further question I would suggest you to open a new forum thread in WPF forums as in this case you could receive more to the point answer.

Kind regards,
Kostadin
the Telerik team

[May]

I am using telerik 2013 version to export excel from the radgrid. However the window authentication keep reprompting. I typed in username and password and it the window authentication still reprompting. I also tried to use the OpenINNewWindow in the setting but that does not take care of the problem. Anything I can do?

It seems it only happens in IE not Chrome.

Thanks!

[Kostadin]

Hi May,

Could you please elaborate a little bit more on your scenario? Are you experience this behavior only when opening a file? Could you please try saving it instead opening and let me know about the result?

Regards,
Kostadin
Telerik

[SC]

Hello

I have a different issue. 
click on export, i receive open/save dialog click on "Open",  receive a warning which is fine i click on "No", again warning window is displayed click on "No" again , my page goes blank.
What might be going wrong?

Thanks
Sc

[Kostadin]

Hi Sc,

In order to open the file you have to click Yes button. More information about this warning message could be found at the beginning of the following help article.

Regards,
Kostadin
Telerik

 

 

[Sumila]

Hello Team,

 

I face an issue while using 'Xlsx' while Export to Excel. I do development on a Virtual desktop which has VS 2015. Also this desktop does not have Microsoft Office installed(Hope this is not a limitation but just want to add). I am copying the error below,

[NullReferenceException: Object reference not set to an instance of an object.]
   Telerik.Web.UI.GridExpandColumn.PrepareCell(TableCell cell, GridItem item) +2865
   Telerik.Web.UI.GridItemDecorator.DecorateItem(GridTableView owner, GridColumn[] columnArray) +510
   Telerik.Web.UI.GridInfrastructureExporter.CreateRow(GridItem currentItem, Int32& invisibleRows, Int32 row, Boolean isMultiRow) +455
   Telerik.Web.UI.GridInfrastructureExporter.GenerateStructure() +654
   Telerik.Web.UI.Grid.Export.TableViewExporter.ExcelExportRenderForm(HtmlTextWriter nullWriter, Control form) +1749
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +116
   System.Web.UI.HtmlControls.HtmlForm.RenderChildren(HtmlTextWriter writer) +130
   System.Web.UI.HtmlControls.HtmlContainerControl.Render(HtmlTextWriter writer) +46
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +78
   System.Web.UI.HtmlControls.HtmlForm.RenderControl(HtmlTextWriter writer) +49
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +116
   System.Web.UI.Page.Render(HtmlTextWriter writer) +38
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +78
   Telerik.Web.UI.RadAjaxControl.RenderPageInAjaxMode(HtmlTextWriter writer, Control page) +952
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +116
   System.Web.UI.Page.Render(HtmlTextWriter writer) +38
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +78
   Telerik.Web.UI.RadAjaxControl.RenderPageInAjaxMode(HtmlTextWriter writer, Control page) +952
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +116
   System.Web.UI.Page.Render(HtmlTextWriter writer) +38
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +78
   System.Web.UI.<ProcessRequestMainAsync>d__523.MoveNext() +13204

Kindly help.

[Attila Antal]

Hi Sumila,

Could you share some more details? For instance, I would like to see the markup of the grid as well if there is logic in the code behind that fires when exporting is clicked. RadGrid includes the necessary assemblies for exporting, and can export without the Office application installed.

I look forward to hearing from you. 

Kind regards,
Attila Antal
Progress Telerik

[Sumila]

Hi,

There is a custom Radgrid defined for the project. The code excerpts are copied here.

<telerik:RadGrid RenderMode="Lightweight" ID="gvDynamicGrid" runat="server" Skin="Office2007" ShowStatusBar="true" AutoGenerateColumns="False"
        PageSize="30" AllowSorting="True" AllowPaging="True" ClientIDMode="AutoID"
        ClientSettings-AllowGroupExpandCollapse="true" ClientSettings-AllowExpandCollapse="true" EnableViewState="true" MasterTableView-EnableViewState="true"
        EnableHeaderContextFilterMenu="true" EnableHeaderContextMenu="false" EnableLinqExpressions="false" RetainExpandStateOnRebind="true"
        OnDetailTableDataBind="gvDynamicGrid_DetailTableDataBind"
        OnNeedDataSource="gvDynamicGrid_NeedDataSource"
        OnItemCreated="gvDynamicGrid_ItemCreated"
        OnPreRender="gvDynamicGrid_PreRender"
        OnSortCommand="gvDynamicGrid_SortCommand"
        onmousedown="onGv_gvDynamicGrid_MouseDown(event);"
        onmousemove="onGv_gvDynamicGrid_MouseMove(event);"
        onmouseup="onGv_gvDynamicGrid_MouseUp(event);">
        <PagerStyle Mode="NumericPages" Visible="false"></PagerStyle>
        <MasterTableView EnableViewState="true" Name="MasterTable">
            <DetailItemTemplate>
                <asp:Table ID="tblOrderItemdescription" runat="server" CellPadding="0" CellSpacing="0" CssClass="fontGrid">
                    <asp:TableRow>
                        <asp:TableCell Width="35px" />
                        <asp:TableCell Width="100%">
                                <%# string.Format("{0}",Eval("Description")) %>
                        </asp:TableCell>
                    </asp:TableRow>
                </asp:Table>
            </DetailItemTemplate>
            <NoRecordsTemplate>
                <telerik:RadLabel runat="server" ClientIDMode="Static" ID="lblNoRecord" Text="No records are found."
                    CssClass="norecord">
                </telerik:RadLabel>
            </NoRecordsTemplate>
        </MasterTableView>
        <ClientSettings AllowRowsDragDrop="false" ColumnsReorderMethod="Reorder" AllowColumnsReorder="true" ReorderColumnsOnClient="true">

            <Resizing AllowColumnResize="True" AllowRowResize="false" ResizeGridOnColumnResize="true"
                ClipCellContentOnResize="true" EnableRealTimeResize="true" AllowResizeToFit="true" />
            <ClientEvents OnColumnCreated="onDynamicGridColumnCreated" OnScroll="HandleScrolling"
                OnColumnSwapped="onDynamicGridColumnSwapped"
                OnCellSelected="DynamicGridcellSelected"
                OnRowDblClick="DynGridRowDblClick"
                OnRowContextMenu="RowContextMenuDynGrid"
                OnRowClick="DynGridOnRowClick"
                OnColumnResized="DynGridColumnResized" />
            <Selecting AllowRowSelect="True" CellSelectionMode="SingleCell" EnableDragToSelectRows="false"></Selecting>
            <Scrolling AllowScroll="true" UseStaticHeaders="True" ScrollHeight="250px" />
        </ClientSettings>
    </telerik:RadGrid>                                                                                      

Code behind is below,

 private void BuildGridTableDetails(Telerik.Web.UI.GridTableView parent)
        {
            Telerik.Web.UI.GridTableView child = null;
            if (gvDynamicGrid.MasterTableView.DetailTables.Count > 0)
                child = gvDynamicGrid.MasterTableView.DetailTables[0];
            GridTableDetailsCollection tableCollection = ((GridBasePage)this.Page).GridTableDetailsList;

            GridTableDetails gridTableDetails = new GridTableDetails();
            if (tableCollection == null)
            {
                tableCollection = new GridTableDetailsCollection();
                tableCollection.GridTableDetailsList = new List<GridTableDetails>();

                GridTableDetails grid = new GridTableDetails();
                grid.ColumnDetailsList = new List<GridColumnDetails>();
                grid.GridIdentifier = Convert.ToString(Session["SearchListName"]);

                //parent grid column details
                GridColumnDetails gridColumn = new GridColumnDetails();
                gridColumn.GridTableColumnSettingsList = new List<Telerik.Web.UI.GridTableView.PersistableColumnSetting>();
                gridColumn.GridTableDetailsList = new GridColumnCollection(gvDynamicGrid.MasterTableView);
                gridColumn.GridTableDetailsList = parent.Columns;
                gridColumn.GridTableColumnSettingsList = parent.ColumnSettings;
                grid.ColumnDetailsList.Add(gridColumn);

                //child column details
                gridColumn = new GridColumnDetails();
                gridColumn.GridTableColumnSettingsList = new List<Telerik.Web.UI.GridTableView.PersistableColumnSetting>();
                gridColumn.GridTableDetailsList = new GridColumnCollection(gvDynamicGrid.MasterTableView.DetailTables[0]);
                if (child != null)
                {
                    gridColumn.GridTableDetailsList = child.Columns;
                    gridColumn.GridTableColumnSettingsList = child.ColumnSettings;

                }
                grid.ColumnDetailsList.Add(gridColumn);

                tableCollection.GridTableDetailsList.Add(grid);

            }
            else
            {
                gridTableDetails = tableCollection.GridTableDetailsList.FirstOrDefault(x => x.GridIdentifier == Convert.ToString(Session["SearchListName"]));


                if (gridTableDetails != null)
                {
                    gridTableDetails.ColumnDetailsList[0].GridTableDetailsList = parent.Columns;
                    gridTableDetails.ColumnDetailsList[0].GridTableColumnSettingsList = parent.ColumnSettings;
                    if (child != null)
                    {

                        gridTableDetails.ColumnDetailsList[1].GridTableDetailsList = child.Columns;
                        gridTableDetails.ColumnDetailsList[1].GridTableColumnSettingsList = child.ColumnSettings;
                    }
                }
                else
                {
                    GridTableDetails grid = new GridTableDetails();
                    grid.ColumnDetailsList = new List<GridColumnDetails>();
                    grid.GridIdentifier = Convert.ToString(Session["SearchListName"]);

                    //parent grid column details
                    GridColumnDetails gridColumn = new GridColumnDetails();
                    gridColumn.GridTableColumnSettingsList = new List<Telerik.Web.UI.GridTableView.PersistableColumnSetting>();
                    gridColumn.GridTableDetailsList = new GridColumnCollection(gvDynamicGrid.MasterTableView);
                    gridColumn.GridTableDetailsList = parent.Columns;
                    gridColumn.GridTableColumnSettingsList = parent.ColumnSettings;
                    grid.ColumnDetailsList.Add(gridColumn);
                    if (child != null)
                    {
                        //child column details
                        gridColumn = new GridColumnDetails();
                        gridColumn.GridTableColumnSettingsList = new List<Telerik.Web.UI.GridTableView.PersistableColumnSetting>();
                        gridColumn.GridTableDetailsList = new GridColumnCollection(gvDynamicGrid.MasterTableView.DetailTables[0]);
                        gridColumn.GridTableDetailsList = child.Columns;
                        gridColumn.GridTableColumnSettingsList = child.ColumnSettings;
                        grid.ColumnDetailsList.Add(gridColumn);
                    }
                    tableCollection.GridTableDetailsList.Add(grid);
                }
            }

            ((GridBasePage)this.Page).GridTableDetailsList = tableCollection;

        }

[Attila Antal]

Hi Sumila, 

Thank you for details.

The markup seems to be okay, but I guess the application breaks somewhere in the code behind.  I took the code snippet you have provided, I've bound some dummy data to the grid, added a RadButton that will call the ExportToExcel() method of RadGrid when clicked and the export is successful. Attached you can find the sample project I've used to test this scenario.

I can't tell what is going on with the method you've shared as it is calling some custom classes that are not available.  If you could modify this sample to produce the error and send it back, I'd be happy to take a look.

Kind regards,
Attila Antal
Progress Telerik

[Sumila]

Hello Attila,

This solution does not help me. I have mentioned that I am facing issue with Export format 'Xlsx'. Your sample is in HTML format. Could you please share a sample with XLsx format? Will try if this works on my virtual desktop.

 

Thanks,

Sumila.

[Attila Antal]

Hi Sumila,

My sample was using the markup you have shared with me and that did not specify the export type of "XLSX".

To export to XLSX, set the ExportSettings - Excel Format to the desired name (see XLSX and DOCX Export documentation for more).

Once the format is configured, you will need to include the rest of the assemblies that are located in the AdditionalLibraries folder in the Telerik® UI for ASP.NET AJAX installation (see Telerik document processing libraries documentation for more).

Once you have included those, the exporting to XLSX will work as expected.

I hope this will help resolve the issue.

Kind regards,
Attila Antal
Progress Telerik

[Shaik]

Hello 

                                

protected void RadGrid1_Exporting(object sender, GridExportingArgs e)
    {   
        else if (e.ExportType == ExportType.Excel )
        {
            string css = "<style> body { border:solid 0.1pt #CCCCCC; }</style>";
            e.ExportOutput = e.ExportOutput.Replace("</head>", css + "</head>");
            String subString1 = String.Empty;
            String subString2 = String.Empty;
            String subString3 = String.Empty;
            String subString4 = String.Empty;
            string symbol = "$";
            subString1 = e.ExportOutput.Substring(0, e.ExportOutput.IndexOf(Environment.NewLine));
            subString2 = e.ExportOutput.Substring(e.ExportOutput.IndexOf(Environment.NewLine), e.ExportOutput.Length - e.ExportOutput.IndexOf(Environment.NewLine));
            subString2 = subString2.Replace(symbol, "");
            subString3 = Resources.Strings.ToTal_Records1.Trim() + " : " + DS.Tables[0].Rows.Count.ToString();
            e.ExportOutput = "<b>" + subString1 + "Ad Hoc Location Report" + subString1 + subString3 + subString1 + Environment.NewLine.ToString() + subString2 + "</b>";
            subString1 = String.Empty;
            subString2 = String.Empty;
            subString3 = String.Empty;
            subString4 = String.Empty;
            String re = @"<a [^>]+>(.*?)<\/a>";
            e.ExportOutput = Regex.Replace(e.ExportOutput, re, "$1");
        }
    }

 

If I change the export format to XLSX Then how can I add the above XTML content to the sheet 

[Attila Antal]

Hi Shaik.

By changing the Excel format to "XLSX" the Grid will be utilizing the Telerik Document Processing Libraries (.NET libraries for building and editing Excel, Word, Pdf, and CSV files), see Excel-Xlsx (OOXML) Export.

This does not allow appending XTML content to the Document.

To see what supported methods/properties the DPL can provide, refer to the SpreadProcessing documentation

You can find more information on this topic in the following resources:

• Integration with Telerik Document Processing Libraries (DPL)
• DPL Overview
• Understanding Telerik AJAX and Document Processing Library Spreadsheet Workbook and Worksheets
• Export large amount of data to PDF, XLSX and CSV using the Telerik Document Processing libraries
• Import and Export between Excel files and DataTable
• Export to Excel XLSX with Hyperlinks
• Export Groups to separate Excel Worksheets