selecting Values from autofilter

Dec 21, 2010 at 12:40 PM

I would like to select values from autofilter. can you please give me an example of code how i can do it.

 

Dec 21, 2010 at 1:27 PM
Hi, could you please explain more of what you mean by autofilter?
Just so I don't misunderstand you.

On 12/21/10, natla <notifications@codeplex.com> wrote:
> From: natla
>
> I would like to select values from autofilter. can you please give me an
> example of code how i can do it.
>
>

--
Sent from my mobile device
Dec 21, 2010 at 1:43 PM

Hello akronite,

  First of all thanks alot for your quick reply. The Autofilter keeps a drop down arrow on the top of every column in worksheet,to filter the data accordingly. Below code will explain how to keep auotfilter

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using OfficeOpenXml;
using System.Xml;
using System.Drawing;
using OfficeOpenXml.Style;
using System.Windows.Forms;

namespace try1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            FileInfo shilpaFile = new FileInfo(@"C:\Book1.xlsx");

            using (ExcelPackage package = new ExcelPackage(shilpaFile))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
               
                //Create an autofilter for the range
                worksheet.Cells["A1:ZZ1"].AutoFilter = true;
            
            
                package.Save();
            }
        }
    }

Now i want to select specific values from those autofilter. Dont know how to do it. please help with this.
Thanks,
Natla
Dec 21, 2010 at 2:24 PM
Hrmmm... I see what you are trying to do. I'm not at my computer at
the moment...
Do you need this urgently? If so email Jan, he might be able to help
you out straight away.
Or if you can wait, then I'll take a look at it first thing in the morning.

On 12/21/10, natla <notifications@codeplex.com> wrote:
> From: natla
>
> Hello akronite, First of all thanks alot for your quick reply. The
> Autofilter keeps a drop down arrow on the top of every
> column in worksheet,to filter the data accordingly. Below code will explain
> how to keep auotfilterusing System;
> using System.Collections.Generic;
> using System.Text;
> using System.IO;
> using OfficeOpenXml;
> using System.Xml;
> using System.Drawing;
> using OfficeOpenXml.Style;
> using System.Windows.Forms;
>
> namespace try1
> {
> public partial class Form1 : Form
> {
> public Form1()
> {
> InitializeComponent();
> }
>
> private void Form1_Load(object sender, EventArgs e)
> {
>
> FileInfo shilpaFile = new FileInfo(@"C:\Book1.xlsx");
>
> using (ExcelPackage package = new ExcelPackage(shilpaFile))
> {
> ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
>
> //Create an autofilter for the range
> worksheet.Cells["A1:ZZ1"].AutoFilter = true;
>
>
> package.Save();
> }
> }
> }Now i want to select specific values from those autofilter. Dont know
> how to do it. please help with this.Thanks,Natla
>
>

--
Sent from my mobile device
Dec 21, 2010 at 3:11 PM

Hello Akronite, i mailed Jan. If he could respond today,can you please look at it tomorrow morning.

Thanks,

Natla

Dec 21, 2010 at 3:20 PM
Yes, I'll certainly look into this first thing in the morning for you.

Regards,
-Michael T.

On 12/21/10, natla <notifications@codeplex.com> wrote:
> From: natla
>
> Hello Akronite, i mailed Jan. If he could respond today,can you please look
> at it tomorrow morning.Thanks,Natla
>
>

--
Sent from my mobile device
Coordinator
Dec 21, 2010 at 7:01 PM

Hi, there is not support in the library to do that at the moment. If you must do it, you have to work directly with the WorksheetXml or add it to the component yourself.

Since I have not done it I have no samples how to do it.

Jan

Jul 5, 2011 at 8:43 PM
Edited Jul 5, 2011 at 8:44 PM

In the interests of lending a hand, here's a quick workaround I used to add a straight value filter and a simple numeric filter on a sheet. You can always experiment with Excel to test what the output should be for other filter types and then apply accordingly. Here's a VB.Net snippet. The namespace stuff at the start is so that you can use the XPath request (a default namespace needs to be given an alias so you can query it).

 

'
'... Assumes you've already defined an auto-filter range
'

'Create a namespace manager and add a default namespace so
'that we can actually use xpath to query nodes in the ns
Dim nsMgr = New System.Xml.XmlNamespaceManager(ws.WorksheetXml.NameTable)
nsMgr.AddNamespace("default", "http://schemas.openxmlformats.org/spreadsheetml/2006/main")

Dim autoFilterNode As System.Xml.XmlNode = _
ws.WorksheetXml.SelectSingleNode("//default:autoFilter", nsMgr) ' Create a simple value filter on the first column (0) Dim filterDef = New XElement("filterColumn") filterDef.Add(New XAttribute("colId", "0")) filterDef.Add(New XElement("filters", _
New XElement("filter", New XAttribute("val", "Some Text Label")))) autoFilterNode.InnerXml = filterDef.ToString() ' Create a numeric comparison filter on the third column filterDef = New XElement("filterColumn") filterDef.Add(New XAttribute("colId", "2")) filterDef.Add(New XElement("filters", _
 New XElement("filter", New XAttribute("operator", "greaterThanOrEqual"), _
 New XAttribute("val", "30")))) autoFilterNode.InnerXml += filterDef.ToString()
Feb 5, 2013 at 4:44 AM
For folks who stumble across this thread now and in the future, I cannot make this statement strongly enough...

If you want / need to do something with this package which isn't already supported, try doing it in an Excel file using Excel, then unpack the source (it's just a zip archive with components inside) and see what Excel did - then copy!

Some of the things you may wish to do will require you to do some work to achieve - e.g. calculating resultant values or adding extra nodes to the XML tree to make sure the file opens consistently in Excel and other packages (like Open Office). Do what the pro's do; try it in Excel and copy :-) (Disclaimer: I'm not a pro, but I have been fortunate to know a few to learn from.)

With filters in the raw XML, there are some tricky little gotcha's where you may have to set additional attributes on the rows which need to be filtered out; if you read the MS packaging format documentation it describes the behavior in detail, even if it is a little tough to digest.