Enabling Excel autofilter in export excel file using
I have written a C# method that will export to Excel 2007, a html file that contain开发者_如何转开发s 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?
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>
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.
Well, I was able to achieve this just using below points:
- namespace
xmlns:x="urn:schemas-microsoft-com:office:excel". - 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>
加载中,请稍侯......
精彩评论