Gridview databounded dropdown export in Excel sheet : The Official Microsoft ASP.NET Forums

Reply

Rajneesh Ver...

All-Star

37156 Points

6818 Posts

Gridview databounded dropdown export in Excel sheet

Jun 09, 2011 01:35 PM|LINK

Hi,

Here is my scenario (as i know how to export gridview data to excel) but if i have a dropdownlist (assume a list of all countries) in gridview at the time of export i want same list in excel also i.e. in excel sheet user can choose any country from the list.

Hope you understand...

Rajneesh Verma's Blog
www.rajneeshverma.com
Keep Forums Clean || Use Alert Moderators.

Reply

shashankgwl

All-Star

18926 Points

3662 Posts

Re: Gridview databounded dropdown export in Excel sheet

Jun 09, 2011 02:10 PM|LINK

i've not tried this , c if it clicks , create an excel at your end the way you want , i mean with all the dropdowns and rest of the things that you want. now save in .xml format from save as and then use this generated xml as the template in your code so you can replace the xml content with the content that you get from your web app etc...

SHASHANK BHIDE
All is well if it runs well.
blog

Reply

Rajneesh Ver...

All-Star

37156 Points

6818 Posts

Re: Gridview databounded dropdown export in Excel sheet

Jun 09, 2011 04:58 PM|LINK

Hi,

Shashank Thanks for reply

but how can i create Excel from XML?

Rajneesh Verma's Blog
www.rajneeshverma.com
Keep Forums Clean || Use Alert Moderators.

Reply

shashankgwl

All-Star

18926 Points

3662 Posts

Re: Gridview databounded dropdown export in Excel sheet

Jun 10, 2011 03:45 AM|LINK

No No , what i'm asking is go to MS Office , and create an excel that looks like your gridview with dropdowns and all etc.. , office 2003 onwards you can save any office file in xml , so you save it and then open that xml in notepad and use it like a template.

SHASHANK BHIDE
All is well if it runs well.
blog

Reply

Rajneesh Ver...

All-Star

37156 Points

6818 Posts

Re: Gridview databounded dropdown export in Excel sheet

Jun 10, 2011 04:43 AM|LINK

shashankgwl
so you save it and then open that xml in notepad and use it like a template.

But user want all data in excel sheet he want to change data in excel.

Rajneesh Verma's Blog
www.rajneeshverma.com
Keep Forums Clean || Use Alert Moderators.

Reply

shashankgwl

All-Star

18926 Points

3662 Posts

Re: Gridview databounded dropdown export in Excel sheet

Jun 10, 2011 07:53 AM|LINK

Rajneesh Verma
But user want all data in excel sheet he want to change data in excel.

you are not getting me. consider this sample gridview export code.

protected void Button1_Click(object sender, EventArgs e)
    {
        Response.Clear();

        Response.AddHeader("content-disposition", "attachment;
        filename=FileName.xls");

        Response.Charset = "";

        // If you want the option to open the Excel file without saving than

        // comment out the line below

        // Response.Cache.SetCacheability(HttpCacheability.NoCache);

        Response.ContentType = "application/vnd.xls";

        System.IO.StringWriter stringWrite = new System.IO.StringWriter();

        System.Web.UI.HtmlTextWriter htmlWrite =
        new HtmlTextWriter(stringWrite);

        <b>GridView1.RenderControl(htmlWrite);</B>

        Response.Write(stringWrite.ToString());

        Response.End();

    }

now , the highlighted line gives you the HTML markup of the gridview, which when is response.written on the output stream comes to the browser as an excel attachment, so you've to modify this string (stringWrite variable) markup generated from the gridview and make it include the xml data from any sample ms-excel xml file which looks like your gridview , or whatever way you want it to be exported.

hope it makes some sense.

FOR E.G. THIS IS SAMPLE XML WHICH CAN BE OPENED IN EXCEL AND HAS DROPDOWNS, VALIDATIONS , FORMATTING ETC..

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>shabhide</Author>
  <LastAuthor>shabhide</LastAuthor>
  <Created>2011-06-10T08:33:01Z</Created>
  <Company>Capgemini India Private Limited</Company>
  <Version>12.00</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8190</WindowHeight>
  <WindowWidth>15195</WindowWidth>
  <WindowTopX>120</WindowTopX>
  <WindowTopY>15</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s67">
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#FF0000"
    ss:Bold="1"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Names>
   <NamedRange ss:Name="_FilterDatabase" ss:RefersTo="=Sheet1!R1C1:R1C5"
    ss:Hidden="1"/>
  </Names>
  <Table ss:ExpandedColumnCount="21" ss:ExpandedRowCount="7" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:Width="61.5"/>
   <Column ss:Index="4" ss:Width="69"/>
   <Row ss:StyleID="s67">
    <Cell><Data ss:Type="String">EmployeeID</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell><Data ss:Type="String">Name</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
    <Cell><Data ss:Type="String">Age</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
    <Cell><Data ss:Type="String">Working Days</Data><NamedCell
      ss:Name="_FilterDatabase"/></Cell>
    <Cell><Data ss:Type="String">Salary</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>
    <Cell ss:Index="21"><Data ss:Type="String">Sunday</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="String">Sachin</Data></Cell>
    <Cell><Data ss:Type="Number">38</Data></Cell>
    <Cell><Data ss:Type="String">Monday</Data></Cell>
    <Cell><Data ss:Type="Number">854214</Data></Cell>
    <Cell ss:Index="21"><Data ss:Type="String">Monday</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">2</Data></Cell>
    <Cell><Data ss:Type="String">Saurav</Data></Cell>
    <Cell><Data ss:Type="Number">40</Data></Cell>
    <Cell><Data ss:Type="String">Friday</Data></Cell>
    <Cell><Data ss:Type="Number">534534</Data></Cell>
    <Cell ss:Index="21" ss:StyleID="s67"><Data ss:Type="String">Tuesday</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">3</Data></Cell>
    <Cell><Data ss:Type="String">Zahir</Data></Cell>
    <Cell><Data ss:Type="Number">35</Data></Cell>
    <Cell><Data ss:Type="String">Thursday</Data></Cell>
    <Cell><Data ss:Type="Number">42343</Data></Cell>
    <Cell ss:Index="21"><Data ss:Type="String">Wednesday</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">4</Data></Cell>
    <Cell><Data ss:Type="String">Shahid</Data></Cell>
    <Cell><Data ss:Type="Number">18</Data></Cell>
    <Cell><Data ss:Type="String">Sunday</Data></Cell>
    <Cell><Data ss:Type="Number">5434</Data></Cell>
    <Cell ss:Index="21" ss:StyleID="s67"><Data ss:Type="String">Thursday</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">5</Data></Cell>
    <Cell><Data ss:Type="String">Ajit</Data></Cell>
    <Cell><Data ss:Type="Number">25</Data></Cell>
    <Cell><Data ss:Type="String">Thursday</Data></Cell>
    <Cell><Data ss:Type="Number">535353</Data></Cell>
    <Cell ss:Index="21"><Data ss:Type="String">Friday</Data></Cell>
   </Row>
   <Row>
    <Cell ss:Index="21" ss:StyleID="s67"/>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>6</ActiveRow>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
  <DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
   <Range>R2C4,R3C4:R103C4</Range>
   <Type>List</Type>
   <Value>R1C21:R7C21</Value>
  </DataValidation>
  <DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
   <Range>R2C5</Range>
   <Type>Decimal</Type>
   <Min>1</Min>
   <Max>99999</Max>
  </DataValidation>
  <AutoFilter x:Range="R1C1:R1C5" xmlns="urn:schemas-microsoft-com:office:excel">
  </AutoFilter>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

SHASHANK BHIDE
All is well if it runs well.
blog

Edited by shashankgwl on Jun 10, 2011 07:53 AM
Edited by shashankgwl on Jun 10, 2011 07:55 AM
Edited by shashankgwl on Jun 10, 2011 08:39 AM

Reply

Rajneesh Ver...

All-Star

37156 Points

6818 Posts

Re: Gridview databounded dropdown export in Excel sheet

Jun 10, 2011 08:40 AM|LINK

Thanks Shashank,

ok if i am able to export to excel then again problem because .net Dropdownlist supports 2 fiels Text and Value but in Excel i can bid only Text.

For example:

ID(Value) Name(Text)

001 Shanshank

002 Rajneesh

003 Vishal

but in Excel i can show only Names as

Shashank

Rajneesh

Vishal

## Is there any way to handle id with Text in Excel List?

@Don't forget that i am using Globlization so its Difficult to match Name to get ID

Any help Appriciated.................

Rajneesh Verma's Blog
www.rajneeshverma.com
Keep Forums Clean || Use Alert Moderators.

Edited by Rajneesh Verma on Jun 10, 2011 08:42 AM

Reply

shashankgwl

All-Star

18926 Points

3662 Posts

Re: Gridview databounded dropdown export in Excel sheet

Jun 10, 2011 09:00 AM|LINK

but what the user will do with ID??

SHASHANK BHIDE
All is well if it runs well.
blog

Reply

Rajneesh Ver...

All-Star

37156 Points

6818 Posts

Re: Gridview databounded dropdown export in Excel sheet

Jun 10, 2011 01:46 PM|LINK

shashankgwl
but what the user will do with ID??

User can change data in excelsheet then import to grid again.

Rajneesh Verma's Blog
www.rajneeshverma.com
Keep Forums Clean || Use Alert Moderators.

Reply

shashankgwl

All-Star

18926 Points

3662 Posts

Re: Gridview databounded dropdown export in Excel sheet

Jun 10, 2011 02:24 PM|LINK

U know, this can be tricky may be somewhere in the excel iteself you've to store the IDs etc.. this might not be simple to do but i guess can be worked upon.

SHASHANK BHIDE
All is well if it runs well.
blog

Gridview databounded dropdown export in Excel sheet RSS

11 replies

Rajneesh Ver...

Gridview databounded dropdown export in Excel sheet

shashankgwl

Re: Gridview databounded dropdown export in Excel sheet

Rajneesh Ver...

Re: Gridview databounded dropdown export in Excel sheet

shashankgwl

Re: Gridview databounded dropdown export in Excel sheet

Rajneesh Ver...

Re: Gridview databounded dropdown export in Excel sheet

shashankgwl

shashankgwl

Re: Gridview databounded dropdown export in Excel sheet

Rajneesh Verma

Rajneesh Ver...

Re: Gridview databounded dropdown export in Excel sheet

shashankgwl

Re: Gridview databounded dropdown export in Excel sheet

Rajneesh Ver...

Re: Gridview databounded dropdown export in Excel sheet

shashankgwl

shashankgwl

Re: Gridview databounded dropdown export in Excel sheet

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft