Issues with ExcelML export of RadGrid

[Kfir]

Hi,
In my app I export a RadGrid in ExcelML format in order to set font styles, sheet name etc., but unfotunately not all columns are exported.
I must say that those columns that are not exported for some reason are similar (or even identical) to the columns
that are exported.

I've set the UseAllDataFields, ExportOnlyData to "true" but it still did not solve the problem.

In order to make sure that the export format is the real cause I changed it to Biff export format and all columns were exported properly.
I really don't know what I'm doing wrong and how can I fix it.

Hereby I attach the RadGrid with 2 columns - the 1st (Group_Order) is properly exported and the 2nd (Group_Demand) is not exported at all.

Thanks for your help, Kfir.

<

 

 

telerik:RadGrid ID="grdObsoleteFullForm" runat="server" AllowPaging="true" AllowCustomPaging="true" OnItemCommand="grdItemCommand" OnPageIndexChanged="PageIndexChanged" PageSize="100"

 

AutoGenerateColumns="false" OnNeedDataSource="NeedDataSource" GridLines="Both" OnItemCreated="grdObsoleteFullForm_ItemCreated" OnExcelMLExportStylesCreated="grdObsoleteFullForm_ExcelMLExportStylesCreated" OnExcelMLExportRowCreated="grdObsoleteFullForm_ExcelMLExportRowCreated" >

 

<ExportSettings FileName="ObsoleteReport" IgnorePaging="true" ExportOnlyData="true" Excel-Format="ExcelML" ></ExportSettings>

 

<ClientSettings AllowColumnHide="true" AllowColumnsReorder="true" ReorderColumnsOnClient="true" >                                       

 

</ClientSettings>

 

 

 

<MasterTableView GridLines="Both" UseAllDataFields="true" AllowFilteringByColumn="true" AutoGenerateColumns="false" AllowPaging="true" CommandItemDisplay="Top" Width="100%" TableLayout="Fixed">

 

<CommandItemSettings ShowExportToExcelButton="true" ShowRefreshButton="false" ShowAddNewRecordButton="false" />

 

<CommandItemTemplate> <asp:ImageButton ID="excelBtn" Text="export To Excel" OnClick="btnExcel_Click" runat="server" ToolTip="Export To Excel" ImageUrl="~/Images/excel2.jpg" />

<

 

Columns>

 

 

<telerik:GridBoundColumn HeaderText="Group Order" UniqueName="Group_Order" DataField="GroupOrder" HeaderStyle-BackColor="Snow"

 

HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Top" FilterControlWidth="50px" ItemStyle-HorizontalAlign="Center"

 

 

HeaderStyle-Font-Bold="true" HeaderStyle-Width="50px">

 

</telerik:GridBoundColumn>

 

<telerik:GridBoundColumn HeaderText="Group Demand" UniqueName="Group_Demand" DataField="ProjectDemand" HeaderStyle-BackColor="Snow"

 

 

HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Top" FilterControlWidth="40px" ItemStyle-HorizontalAlign="Center"

HeaderStyle-Font-Bold="true" HeaderStyle-Width="55px">

 

 

</telerik:GridBoundColumn>

</

 

 

MasterTableView>

 

<PagerStyle Mode="NextPrev" AlwaysVisible="true" ></PagerStyle>

 

</telerik:RadGrid>

[Shinu]

Hi ,

I tried your code and it was working fine at my end. Can you make sure in your code behind you are not hiding the column during export. Here is a sample code snippet that i tried which is similar to yours. Try and see if it works for you. Please provide your code behind to look more into the issue.

ASPX:

<telerik:RadGrid ID="grdObsoleteFullForm" runat="server" AllowPaging="true" AutoGenerateColumns="false"
    GridLines="Both" OnNeedDataSource="grdObsoleteFullForm_NeedDataSource">
    <ExportSettings FileName="ObsoleteReport" IgnorePaging="true" ExportOnlyData="true"
        Excel-Format="ExcelML">
    </ExportSettings>
    <MasterTableView UseAllDataFields="true" AutoGenerateColumns="false" AllowPaging="true"
        CommandItemDisplay="Top">
        <CommandItemSettings ShowExportToExcelButton="true" ShowRefreshButton="false" ShowAddNewRecordButton="false" />
        <CommandItemTemplate>
            <asp:ImageButton ID="excelBtn" Text="export To Excel" OnClick="btnExcel_Click" runat="server"
                ToolTip="Export To Excel" ImageUrl="~/Images/excel2.jpg" />
        </CommandItemTemplate>
        <Columns>
            <telerik:GridBoundColumn HeaderText="ID" UniqueName="ID" DataField="ID">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn HeaderText="Name" UniqueName="Name" DataField="Name">
            </telerik:GridBoundColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

C#:

protected void grdObsoleteFullForm_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
    dynamic data = new[] {
new { ID = 1, Name = "Name1", Number=123},
new { ID = 2, Name = "Name2", Number=234},
new { ID = 3, Name = "Name3", Number=234},
new { ID = 4, Name = "Name4", Number=456},
new { ID = 5, Name = "Name5", Number=567},
new { ID = 6, Name = "Name6", Number=567},
new { ID = 7, Name = "Name7", Number=789},
new { ID = 8, Name = "Name8", Number=896},
new { ID = 9, Name = "Name9", Number=741}
};
    grdObsoleteFullForm.DataSource = data;
}
protected void btnExcel_Click(object sender, ImageClickEventArgs e)
{
    grdObsoleteFullForm.MasterTableView.ExportToExcel();
}

Thanks,
Shinu

[Kfir]

               

 

Thanks Shinu,
I double checked the code and the column visibility is never set to hidden state, and like I said before - when I export the same (!) grid on Biff format (by changing the parameter on the .aspx) the result is just fine.
I use the ExcelML format to enable text wrap and custom fonts but for some reason not all the columns are exported.

code behind (I get the data named "listObsolete" from a web service):

void

btnExcel_Click(

object

sender,

EventArgs

e) {

 

grdObsoleteFullForm.DataSource = listObsolete;

grdObsoleteFullForm.Rebind();

grdObsoleteFullForm.MasterTableView.ExportToExcel();

 

}

       

 

protected void NeedDataSource(object sender, GridNeedDataSourceEventArgs e)

{

grdObsoleteFullForm.DataSource = listObsolete;

}

 

protected void grdObsoleteFullForm_ExcelMLExportRowCreated(object sender, GridExportExcelMLRowCreatedArgs e)

{

e.Worksheet.Name =

 

"Sheet1";

}

 

[Kfir]

Got it!

the problem was that 2 different GridBoundColumns (with a totally different UniqueName) had the same DataField (="projectDemand") - a web service returned the data to the grid and then it was binded.

Sometimes the data of ProjectDemand was relevant to column 1 and sometimes it was relevant to column 2 (according to the app business logic) and therefore in case 1 I needed to display and export column 1 and in case 2 I needed to display and export case 2.

When the grid was ExcelML exported only column 1 was exported, while column 2 was always (!) dropped.
Note that this issue occurs when exporting in ExcelML format while Biff format exports the columns properly.

I'd really like to know what's the best way to resolve it.
Thanks, kfir.

[Kostadin]

Hi Kfir,

This is a limitation of the ExcelML export format and there isn't a workaround for it. Since this export format builds the output directly from the datasource and not from the page, only one column from all columns with identical DataFields will be exported. Nevertheless the Html based and Biff export could achieve the requested feature.

Regards,
Kostadin
Telerik