Empty extra column in RadGrid Export to Excel

[Prajakta]

Hi,

I am using Rad grid with following definition.

<telerik:RadGrid ID="radGridProject" runat="server"
           AutoGenerateColumns="False" CommandItemDisplay="Top" GridLines="None" AllowFilteringByColumn="True"
           AllowSorting="true" Width="100%" AllowPaging="true" PageSize="15"
           OnNeedDataSource="RadGridProject_NeedDataSource" OnItemDataBound="RadGridProject_OnItemDataBound"
           OnItemCommand="RadGridProject_OnItemCommand" AllowMultiRowSelection="false"
           ClientSettings-Resizing-AllowColumnResize="true" Skin="Default">
           <MasterTableView AutoGenerateColumns="false" GridLines="None" CommandItemDisplay="Top" EditMode="EditForms" DataKeyNames="ProjectID" AllowFilteringByColumn="True" TableLayout="Auto" Width="100%" >
               <RowIndicatorColumn>
                   <HeaderStyle Width="20px" />
               </RowIndicatorColumn>
               <ExpandCollapseColumn >
                   <HeaderStyle Width="20px" />
               </ExpandCollapseColumn>
                <Columns>
                   <telerik:GridEditCommandColumn ButtonType="ImageButton" HeaderStyle-Width="20px" UniqueName="ProjectsEditButton">
                    <ItemStyle Width="5%" />
                   </telerik:GridEditCommandColumn>
                   <telerik:GridTemplateColumn AllowFiltering="false" HeaderStyle-Width="25px" UniqueName="ProjectsDeleteButton">
                   <ItemStyle Width="5%" />
                       <ItemTemplate>
                           <asp:ImageButton ID="btnDelete" runat="server" CommandName="Delete" ImageUrl="~/Images/delete.gif" OnClientClick="javascript:return confirm('Are you sure you want to delete this record?')" />
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                    <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="ProjectName" HeaderText="   Project Name" SortExpression="Status" UniqueName="ProjectNameColumn" AllowFiltering="true">
                       <ItemTemplate>
                           <asp:Label ID="labelProjectName" runat="server" Text='<%# Bind("ProjectName") %>'></asp:Label>
                       </ItemTemplate>
                       <EditItemTemplate>
                           <asp:TextBox ID="textboxProjectName" runat="server" Text='<%# Bind("ProjectName") %>'></asp:TextBox>
                       </EditItemTemplate>
                   </telerik:GridTemplateColumn>

                   <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="Status" HeaderText="   Status" SortExpression="Status" UniqueName="StatusColumn" AllowFiltering="true">
                       <ItemTemplate>
                           <asp:Label ID="labelStatus" runat="server" Text='<%# Bind("Status") %>'></asp:Label>
                       </ItemTemplate>
                       <EditItemTemplate>
                           <asp:TextBox ID="textboxStatus" runat="server" Text='<%# Bind("Status") %>'></asp:TextBox>
                       </EditItemTemplate>
                   </telerik:GridTemplateColumn>

                    <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="ProjectType" HeaderText="   Project Type" SortExpression="ProjectType" UniqueName="ProjectTypeColumn" AllowFiltering="true" Visible="false">
                       <ItemTemplate>
                           <asp:Label ID="labelProjectType" runat="server" Text='<%# Bind("ProjectType") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>


                   <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="City" HeaderText="   City" SortExpression="City" UniqueName="CityColumn" AllowFiltering="true">
                       <ItemTemplate>
                           <asp:Label ID="labelCity" runat="server" Text='<%# Bind("City") %>'></asp:Label>
                       </ItemTemplate>
                       <EditItemTemplate>
                           <asp:TextBox ID="textboxCity" runat="server" Text='<%# Bind("City") %>'></asp:TextBox>
                       </EditItemTemplate>
                   </telerik:GridTemplateColumn>
                      <telerik:GridTemplateColumn FilterControlWidth="80%" DataField="ProjectDescription" HeaderText="   Project Description" SortExpression="ProjectDescription" UniqueName="ProjectDescriptionColumn" AllowFiltering="true">
                       <ItemTemplate>
                           <asp:Label ID="labelProjectDescription" runat="server" Text='<%# Bind("ProjectDescription") %>'></asp:Label>
                       </ItemTemplate>
                       <EditItemTemplate>
                           <asp:TextBox ID="textboxProjectDescription" runat="server" Text='<%# Bind("ProjectDescription") %>'></asp:TextBox>
                       </EditItemTemplate>
                   </telerik:GridTemplateColumn>

                    <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="StNumber" HeaderText="   St. Number" SortExpression="StNumber" UniqueName="StNumberColumn" AllowFiltering="true" Visible="false">
                       <ItemTemplate>
                           <asp:Label ID="labelStNumber" runat="server" Text='<%# Bind("StNumber") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                    <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="StPre" HeaderText="   StPre" SortExpression="StPre" UniqueName="StPreColumn" AllowFiltering="true" Visible="false">
                       <ItemTemplate>
                           <asp:Label ID="labelStPre" runat="server" Text='<%# Bind("StPre") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                    <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="StName" HeaderText="   StName" SortExpression="StName" UniqueName="StNameColumn" AllowFiltering="true" Visible="false">
                       <ItemTemplate>
                           <asp:Label ID="labelStName" runat="server" Text='<%# Bind("StName") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                    <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="StType" HeaderText="   StType" SortExpression="StType" UniqueName="StTypeColumn" AllowFiltering="true" Visible="false">
                       <ItemTemplate>
                           <asp:Label ID="labelStType" runat="server" Text='<%# Bind("StType") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                   <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="StSuffix" HeaderText="   StSuffix" SortExpression="StSuffix" UniqueName="StSuffixColumn" AllowFiltering="true" Visible="false">
                       <ItemTemplate>
                           <asp:Label ID="labelStSuffix" runat="server" Text='<%# Bind("StSuffix") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                   <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="Zip" HeaderText="   Zip" SortExpression="Zip" UniqueName="ZipColumn" AllowFiltering="true">
                       <ItemTemplate>
                           <asp:Label ID="labelZip" runat="server" Text='<%# Bind("Zip") %>'></asp:Label>
                       </ItemTemplate>
                       <EditItemTemplate>
                           <asp:TextBox ID="textboxZip" runat="server" Text='<%# Bind("Zip") %>'></asp:TextBox>
                       </EditItemTemplate>
                   </telerik:GridTemplateColumn>

                    <telerik:GridTemplateColumn Visible="false"  FilterControlWidth="80px" DataField="ClientID" HeaderText="   ClientID" SortExpression="ClientID" UniqueName="ClientIDColumn" AllowFiltering="true">
                       <ItemTemplate>
                           <asp:Label ID="labelClientID" runat="server" Text='<%# Bind("ClientID") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                   <telerik:GridTemplateColumn Visible="false"  FilterControlWidth="80px" DataField="ClientName" HeaderText="   Client Name" SortExpression="ClientName" UniqueName="ClientNameColumn" AllowFiltering="true">
                       <ItemTemplate>
                           <asp:Label ID="labelClientName" runat="server" Text='<%# Bind("ClientName") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                   <telerik:GridTemplateColumn Visible="false"  FilterControlWidth="80px" DataField="MSTProjectID" HeaderText="   MSTProjectID" SortExpression="MSTProjectID" UniqueName="MSTProjectIDColumn" AllowFiltering="true">
                       <ItemTemplate>
                           <asp:Label ID="labelMSTProjectID" runat="server" Text='<%# Bind("MSTProjectID") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                   <telerik:GridTemplateColumn Visible="false"  FilterControlWidth="80px" DataField="ProjectID" HeaderText="   Project ID" SortExpression="ProjectID" UniqueName="ProjectIDColumn" AllowFiltering="true">
                       <ItemTemplate>
                           <asp:Label ID="labelProjectID" runat="server" Text='<%# Bind("ProjectID") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>


                   <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="LatMin" HeaderText="   LatMin" SortExpression="LatMin" UniqueName="LatMinColumn" AllowFiltering="true" Visible="false">
                       <ItemTemplate>
                           <asp:Label ID="labelLatMin" runat="server" Text='<%# Bind("LatMin") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                    <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="LatMax" HeaderText="   LatMax" SortExpression="LatMax" UniqueName="LatMaxColumn" AllowFiltering="true" Visible="false">
                       <ItemTemplate>
                           <asp:Label ID="labelLatMax" runat="server" Text='<%# Bind("LatMax") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                    <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="LongMin" HeaderText="   LongMin" SortExpression="LongMin" UniqueName="LongMinColumn" AllowFiltering="true" Visible="false">
                       <ItemTemplate>
                           <asp:Label ID="labelLongMin" runat="server" Text='<%# Bind("LongMin") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                    <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="LongMax" HeaderText="   LongMax" SortExpression="LongMax" UniqueName="LongMaxColumn" AllowFiltering="true" Visible="false">
                       <ItemTemplate>
                           <asp:Label ID="labelLongMax" runat="server" Text='<%# Bind("LongMax") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

                   <telerik:GridTemplateColumn FilterControlWidth="80px" DataField="ProjectNotes" HeaderText="   Project Notes" SortExpression="ProjectNotes" UniqueName="ProjectNotesColumn" AllowFiltering="true" Visible="false">
                       <ItemTemplate>
                           <asp:Label ID="labelProjectNotes" runat="server" Text='<%# Bind("ProjectNotes") %>'></asp:Label>
                       </ItemTemplate>
                   </telerik:GridTemplateColumn>

               </Columns>
               <EditFormSettings editformtype="Template">
                   <FormTemplate>
                       <table>
                           <tr>
                               <td align="left">
                                   <mst:ProjectInfo ID="uctrlProjectInfo" runat="server" />
                               </td>
                           </tr>
                           <tr>
                               <td>
                                   <asp:Button ID="btnUpdate" runat="server" CommandName="Update" Text="Submit" />

                                   <asp:Button ID="btnCancel" runat="server" CommandName="Cancel" Text="Cancel" CausesValidation="false" />
                               </td>
                           </tr>
                       </table>
                   </FormTemplate>
               </EditFormSettings>
               <NestedViewTemplate>
                   <table>
                       <tr>
                           <td align="left">
                               <mst:ProjectInfo ID="uctrlProjectInfo" runat="server" />
                           </td>
                       </tr>
                   </table>
               </NestedViewTemplate>


           </MasterTableView>
           <ClientSettings AllowDragToGroup="True">
               <Scrolling AllowScroll="false" UseStaticHeaders="True" />
               <Selecting AllowRowSelect="true" />
               <Resizing AllowColumnResize="True" />
           </ClientSettings>
           <FilterMenu EnableTheming="True" Skin="Default2006">
               <CollapseAnimation Duration="200" Type="OutQuint" />
           </FilterMenu>
           <GroupingSettings CaseSensitive="false" />
           <PagerStyle Mode="NextPrevAndNumeric"></PagerStyle>
           <HeaderStyle HorizontalAlign="Left" />
           <ValidationSettings CommandsToValidate="PerformInsert,Update" EnableValidation="true" />

       </telerik:RadGrid>

I am exporting the grid to excel using an image button click event as follows:

protected void ImageButton1_Click(object sender, System.Web.UI.ImageClickEventArgs e)
       {


           string fileName = "";
           List<ProjectBO> listProjectBO = ProjectDAP.GetAllProjects();
           if (listProjectBO.Count > 0)
           {
               // Exported excel file name contains of ClientName,ProjectName,ArrivalDate
               fileName = "ProjectData";
           }
           radGridProject.DataSource = ProjectDAP.GetAllProjects();
           radGridProject.Rebind();
           //radGridProject.AutoGenerateColumns = true;
           radGridProject.ExportSettings.FileName = fileName;


           radGridProject.MasterTableView.Columns.FindByUniqueName("ProjectIDColumn").Visible = true;
           radGridProject.MasterTableView.ExpandCollapseColumn.Visible = false;
           radGridProject.MasterTableView.RowIndicatorColumn.Visible = false;
           radGridProject.MasterTableView.Columns.FindByUniqueName("ProjectsEditButton").Visible = false;
           radGridProject.MasterTableView.Columns.FindByUniqueName("ProjectsDeleteButton").Visible = false;
           radGridProject.MasterTableView.Columns.FindByUniqueName("MSTProjectIDColumn").Visible = true;
           radGridProject.MasterTableView.Columns.FindByUniqueName("ProjectTypeColumn").Visible = true;
           radGridProject.MasterTableView.Columns.FindByUniqueName("ProjectDescriptionColumn").Visible = true;
           radGridProject.MasterTableView.Columns.FindByUniqueName("ClientIDColumn").Visible = true;

           radGridProject.MasterTableView.Columns.FindByUniqueName("ClientNameColumn").Visible = false;

           radGridProject.MasterTableView.Columns.FindByUniqueName("StNumberColumn").Visible = true;
           radGridProject.MasterTableView.Columns.FindByUniqueName("StPreColumn").Visible = true;
           radGridProject.MasterTableView.Columns.FindByUniqueName("StNameColumn").Visible = true;
           radGridProject.MasterTableView.Columns.FindByUniqueName("StTypeColumn").Visible = true;
           radGridProject.MasterTableView.Columns.FindByUniqueName("StSuffixColumn").Visible = true;
           radGridProject.MasterTableView.Columns.FindByUniqueName("LatMinColumn").Visible = true;
           radGridProject.MasterTableView.Columns.FindByUniqueName("LatMaxColumn").Visible = true;
           radGridProject.MasterTableView.Columns.FindByUniqueName("LongMinColumn").Visible = true;
           radGridProject.MasterTableView.Columns.FindByUniqueName("LongMaxColumn").Visible = true;
           radGridProject.MasterTableView.Columns.FindByUniqueName("ProjectNotesColumn").Visible = true;
           this.radGridProject.GridLines = GridLines.Both;
           radGridProject.ExportSettings.Excel.Format = GridExcelExportFormat.Html;
           this.radGridProject.ExportSettings.ExportOnlyData = true;
           this.radGridProject.ExportSettings.IgnorePaging = true;
           foreach (GridItem commandItem in this.radGridProject.MasterTableView.GetItems(GridItemType.CommandItem))
           {
               commandItem.Visible = false;
           }


           radGridProject.MasterTableView.ExportToExcel();

Problem is that when ever I export and open the excel sheet an empty column "A" is shown in the excel sheet and then the columns specified as shown in further column in sheet. I need to remove this column "A". Is it so that while exporting to excel the columns starts from "B" in excel sheet and not "A"..?

Please help me I need to remove this column "A " from excel sheet or rather it should not get displayed at all.

Thanks.

[Pavlina]

Hi Prajakta,

I suppose you want to hide the Expand/Collapse column in the exported file. If so try to add the following code before exporting and see if it helps:

protected void Export_Click(object sender, EventArgs e)
{
   RadGrid1.ExportSettings.OpenInNewWindow = true;
   foreach (GridDataItem item in RadGrid1.Items)
{
   item["ExpandColumn"].Visible = false;
}
   RadGrid1.MasterTableView.ExportToExcel();
}

Regards,

Pavlina
the Telerik team

[Kris]

I tried this code and it didn't help.

item["ExpandColumn"].Visible = false; 

Any other suggestion to remove this empty column. Thanks.

[Vasssek]

Hello,

please check this link.
http://www.telerik.com/community/forums/aspnet-ajax/grid/hiding-expandcolumn-on-export.aspx

It looks similar to your issue.

Vasssek

[Kris]

Hello,
         Thanks for the link. Setting ExportSettings.HideStructureColumns = True fixed the issue. 

Cheers