CODEWITHSUNDEEP
asp.netexport-to-excel
vandana sharma
vandana sharma

Reputation: 191

Enabling Excel autofilter in export excel file using

I have written a C# method that will export to Excel 2007, a html file that contains an html table. The data renders perfectly when the exported file is opened in Excel.

I would like to add an extra feature into the above export, so that when I open the exported data in Excel, the autofilter is turned on automatically.

What can I do in C# during export, to enable autofilter for all columns?

Upvotes: 1

Views: 4537

Answers (3)

Paritosh
Paritosh

Reputation: 11570

Well, I was able to achieve this just using below points:

  1. namespace xmlns:x="urn:schemas-microsoft-com:office:excel".
  2. Autofilter attribute for th - x:autofilter='all'

Below is the sample html for that.

<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
    <!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>Sheet1</x:Name><x:WorksheetOptions><x:Print><x:ValidPrinterInfo/></x:Print></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
    <table>
      <thead>
        <tr>
          <th x:autofilter='all'>Age</th>
          <th x:autofilter='all'>Salary</th>
        </tr>
     </thead>
     <tbody>
       <tr>
        <td>34</td>
        <td>2333</td>
       </tr>
     </tbody>
   </table>
</head>
</html>

Upvotes: 0

vandana sharma
vandana sharma

Reputation: 191

If you want to automatically make the filters appear for header row in an Excel file that is created through programatically exporting html to an .xls file, then remember to add the following 2 features to this file.

  • Add schemas for Excel to html tag as in bold in listing below. Add exactly these schemas.
  • Add to each header column 'td' tag the following attribute x:autofilter='all' which is also in bold in listing below.

That's it. Then you are done. Its as simple as this. A sample listing that I saved as 'SampleInHTML.xls' from notepad and tested in Excel 2007 AND Excel 2010 to work perfectly with auto-filtering appearing automatically on opening the saved file in Excel, is given below.

<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head></head>

<body >

<table >

 <tr >
  <td  width=130 x:autofilter='all'   
  >Age</td>
  <td  width=130 x:autofilter='all'
  >Salary</td>
 </tr>
 <tr height=18 style='height:13.2pt'>
  <td  >34</td>
  <td >2333</td>
 </tr>
 <tr >
  <td>55</td>
  <td>1222</td>
 </tr>

</table>

</body>

</html>

Upvotes: 7

Pleun
Pleun

Reputation: 8920

To do these kind of things, you have to operate directly against a spreadsheet obect. As far as I know this cannot be done by exporting a HTML table.

I have used Gembox.Spreadsheet and it will be able to do what you want. However, it is not free.

Upvotes: 0

Related Questions