How to programmatically generate Microsoft Excel AutoFiltered Lists with C#
One of my favorite features in Microsoft Excel is AutoFilter. I love to manage lists of data in Excel where I can organize, sort, and filter my information in different columns.
Filtering is a quick and easy way to find and work with a subset of data in a range. A filtered range displays only the rows that meet custom filter criteria defined in a search query or fitler specified for a column. Microsoft Excel provides two commands for filtering ranges: AutoFilter and Advanced Filter. Unlike sorting, filtering does not rearrange a range. Filtering temporarily hides rows you do not want displayed. When Excel filters rows, you can edit, format, chart, and print your range subset without rearranging or moving it. You can learn more about autofiltering here: All About AutoFilter.
The best part is that you can programatically generate autofiltered lists from your managed applications thanks to the extensibility offered by the Microsoft Excel Primary Interop Assembly. You can use the Range.AutoFilter method to filter a list using AutoFilter. The following code sample (C# Console Application) generates an autofiltered list of all the directories and files that belong to given directory path. You can modify the code and send any path that you need.
namespace ErikaEc.OfficeTools.Excel
{
using System;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
class DemoExcelAutoFiltering {
[STAThread]
static void Main(string[] args) {
DirectoryInfo di = new DirectoryInfo(@"C:\Program Files\Microsoft Office\OFFICE11\1033");
ExcelFileReport efr = new ExcelFileReport(di);
efr.Generate();
}
}
public class ExcelFileReport {
private object _missing;
private Excel.Workbook _book;
Excel.Worksheet _sheet;
Excel.Range _rng;
int _row;
private DirectoryInfo _di;
ExcelHelper _eh = new ExcelHelper();
public ExcelFileReport(DirectoryInfo di) {
_di = di;
_missing = System.Reflection.Missing.Value;
_row = 4;
}
public void DocumentDirectory(DirectoryInfo di) {
foreach (DirectoryInfo d in di.GetDirectories()) {
DocumentDirectory(d);
}
foreach (FileInfo f in di.GetFiles()) {
_row++;
_rng = (Excel.Range)_sheet.Cells[_row, "A"];
_rng.Value2 = di.Name;
_rng = (Excel.Range)_sheet.Cells[_row, "B"];
_rng.Value2 = f.FullName;
_rng = (Excel.Range)_sheet.Cells[_row, "C"];
_rng.Value2 = f.Name;
_rng = (Excel.Range)_sheet.Cells[_row, "D"];
_rng.Value2 = f.Length;
_rng = (Excel.Range)_sheet.Cells[_row, "E"];
_rng.Value2 = f.Extension;
_rng = (Excel.Range)_sheet.Cells[_row, "F"];
_rng.Value2 = f.LastWriteTime.ToLongDateString();
}
}
public void Generate(){
string caption = "File Analysis Results";
string heading1 = "File Analysis Report for Folder " + _di.FullName;
_book = _eh.Create(caption, heading1);
_sheet = ((Excel.Worksheet)_book.ActiveSheet);
WriteTableHeader();
DocumentDirectory(_di);
SetAutoFilter();
_eh.Close();
}
private void SetAutoFilter(){
string lastrow = "F" + _row.ToString();
_rng = ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4", lastrow);
_rng.AutoFilter(1, _missing, Excel.XlAutoFilterOperator.xlAnd, _missing, true);
_rng.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
}
public void WriteTableHeader(){
_rng = ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4", "H3");
_rng.Font.Bold = true;
_rng.EntireRow.Font.Bold = true;
_rng = (Excel.Range)_sheet.Cells[_row, "A"];
_rng.Value2 = "Parent Directory";
_rng = (Excel.Range)_sheet.Cells[_row, "B"];
_rng.Value2 = "Full Path";
_rng = (Excel.Range)_sheet.Cells[_row, "C"];
_rng.Value2 = "File Name";
_rng = (Excel.Range)_sheet.Cells[_row, "D"];
_rng.Value2 = "Size";
_rng = (Excel.Range)_sheet.Cells[_row, "E"];
_rng.Value2 = "Type";
_rng = (Excel.Range)_sheet.Cells[_row, "F"];
_rng.Value2 = "Last Modified";
_sheet.Columns.ColumnWidth = 30;
}
}
class ExcelHelper {
private Excel.Application _excelApplication;
public ExcelHelper() {
_excelApplication = new Excel.Application();
}
public Excel.Workbook Create(string caption, string heading1) {
try {
_excelApplication.Caption = caption;
_excelApplication.ScreenUpdating = false;
_excelApplication.Visible = false;
Excel.Workbook book = _excelApplication.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;
Excel.Range r = (Excel.Range)sheet.Cells[1, "A"];
r.Value2 = heading1;
r.EntireRow.Font.Bold = true;
return book;
}
catch (Exception ex) {
throw (ex);
}
}
public void Close() {
_excelApplication.ScreenUpdating = true;
_excelApplication.Visible = true;
_excelApplication.DisplayAlerts = true;
if (_excelApplication != null) {
_excelApplication.Quit();
_excelApplication = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
}
Run this application and give it a try!
Comments
Anonymous
October 30, 2005
Hi erika,
I've a problem that's still related with your topics and it makes me so stressfull ... i developed infopath form and C# as codebehind. While I send a value to the excel that was created before on your programming way is still OK. But, if I try to read from Excel cell from infopath code always display error:
Exception from HRESULT: 0x800A03EC.
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.get_Item(Object RowIndex, Object ColumnIndex)
Do you know about this error??
thank you very much ...
Rahman Hadi(rahman_hadi@fmi.com)Anonymous
November 01, 2005
Hi Rahman,
I have not seen that error, but I made some research and I learned that when you are working with Excel 2003 and other Visual Studio solutions you might need to consider globalization and localization aspects. I found some articles that might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrconGlobalizingLocalizingOfficeSolutions.asp
http://www.devx.com/OfficeProDev/Article/29507?trk=DXRSS_LATEST
http://blogs.msdn.com/eric_carter/archive/2005/06/15/429515.aspxAnonymous
November 17, 2005
One way to workaround the problem of using an English version of Excel with a culture ID other than English, is to add these lines in your code:
System.Globalization.CultureInfo enUS = new System.Globalization.CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture = enUS;
Fernando Ferreira (fferre)
Microsoft Consulting ServicesAnonymous
January 20, 2006
Hello,
I have an Excel template with inserted database query. When I open a template programically from my application, I want to set AutoFilter according to values that user enters in my application. The code is as follows:
// open template, then:
Excel.Range _range = (Excel.Range)sheet.get_Range("A3", "Q3");
_range.AutoFilter(3, "=Petras", Excel.XlAutoFilterOperator.xlAnd, vk_missing, true);
_range.AutoFilter(5, "<>Jonas", Excel.XlAutoFilterOperator.xlAnd, vk_missing, true);
// show worksheet
the funny thing is that AutoFiltering works 4 times out of 5 - sometimes autofiltering is not set though no errors are displayed. Is that some kind if bug or my code isn't correct?
Thank you very much in advance.Anonymous
January 31, 2006
I am generating an XLS file via the PIAs using JSCRIPT .NET. I have carefully called Marshall.Release on each object and have even included a call to GC.Collect. EXCEL.EXE refuses to quit ... I don't know what to do. Can you help?Anonymous
February 21, 2006
You have to release ALL objects that you created using excel. Then call garbage collection.
Sample code below i am releasing each object using ReleaseComObject function.
object delete = oSheet; // assign worksheet as object
ReleaseComObject(ref delete); //delete worksheet
delete = oWB; // assign workbook as object
ReleaseComObject(ref delete); // delete workbook
delete = oXL; // assign excel application as obejct
ReleaseComObject(ref delete); //delete excel
GC.Collect();
function
----------------
private void ReleaseComObject(ref Object o)
{
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0);
}Anonymous
May 09, 2006
I got the 0x800A03EC error too.
I tried the Globalization fix, but that didn't work (I didn't really expect it to - as far as I'm aware, there's no difference in the way the Brits and the Americans reference Excel cells and columns!
I found I got the error when selecting a range using oWB.Columns[1,2]. I used Columns because I wanted to set the ColumnWidth.
I change it to oWB.Cells[1,2] and it worked fine. I guess .ColumnWidth (and no doubt .AutoFilter) don't really care HOW the Range was created, just that it is a range!Anonymous
July 25, 2006
i wanted to know if there is any online resource available where i can learn more abt the excel 11.0 object library. something that starts from basics and moves on to a more advanced levelAnonymous
August 24, 2006
Hi Sina,
You can find the VBA reference for Excel 11.0 here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xltocOMMap_HV01049651.asp
You can find more VBA reference for other products and versions here:
http://msdn.microsoft.com/office/reference/vba/default.aspxAnonymous
November 02, 2006
I have an xls file created and i want toopen it add new sheet in existing file and save the xls file is there any one who can help me in this?Anonymous
November 22, 2006
Hi Erika, I am developing an Excel Add-In in Visual Studio 2005 (C#) for Excel XP and higher. I read somewhere that I need to explicitly release all objects even the Range object that I get from get_Range() method. What else need to be released in an Add-In ? regards Abhimanyu SirohiAnonymous
December 14, 2006
The comment has been removedAnonymous
December 14, 2006
Siya, This article may help http://support.microsoft.com/kb/823981 Also Office XP PIA's are available at http://www.microsoft.com/downloads/details.aspx?FamilyId=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en regardsAnonymous
December 28, 2006
Hi Erika, I am developing an Excel Add-In in Visual Studio 2005 (C#) for Excel 2007. I would like to know how to dynamically remove the Excel Cells in Memory. Thannks, RamAnonymous
January 03, 2007
Hi, I m facing one problem How to Catch Any Excel File Which user opens.. I want to catch that file from my program and if any changes made in that file then i want to save those changes as well as old changes also i want ... I want to make program in C#.NET or ASP.NET can you help me about this? Contact me on: nileshupadhyay10582@gmail.com Thanks in advanceAnonymous
January 13, 2007
How i can set the background color for a range of cells? ThanksAnonymous
January 31, 2007
hai friends, I need help from u. i.e.,How to programmatically generate Microsoft Excel sheets with having dorpdown Lists in some columns through C#.net 2005. can any one know please tell me the solutionAnonymous
May 07, 2007
I got to this post by searching for "0x800A03EC". In my case, I had something like: xlSheet.Cells[0,0] = "a value"; The error went away when I switched this to: xlSheet.Cells[1,"A"] = "a value";Anonymous
May 08, 2007
@Ahmed: it's simple ;) Excel.Range range = worksheet.get_Range("A1", "I9"); range.Interior.Color = System.Drawing.Color.Green.ToArgb(); Note that the Color property must be set to an RGB integer value, or you will get an exception. @sfuqua: you got that error because Excel indices start at 1, not 0 as you might expect. Thus, to get the first (top-left) cell in a worksheet, you would use: xlSheet.Cells[1, 1] = "a value";Anonymous
June 08, 2007
The comment has been removedAnonymous
June 27, 2007
For everyone who is getting the 0x800A03EC exception: Excel cell indexing starts from 1 (NOT 0), if you try to access a cell like [0,x] or [x,0], the exception will be raised.Anonymous
July 17, 2007
Hi, I am creating some UDFs (in C# automation) and these are working fine. But i can't put descriptions of the functions and arguments. Please help me. Thanks MousumAnonymous
August 03, 2007
Hi Erika, I have this situation, I need to name, at the moment of the creation, every Sheet in my Worksheet, is that possible? i.e: ... Microsoft.Office.Interop.Excel.Application excel; ... excel.ActiveWorkbook.Worksheets.Add( missing ,excel.ActiveWorkbook.Worksheets[ excel.ActiveWorkbook.Worksheets.Count ] , missing , missing ); ... ///below I'm creating the necessary Sheets in my Worksheet; if the aplication needs 3 sheets, the code below, automatically will generate Sheet1, Sheet2, Sheet3. It is possible to generate it with other names?Anonymous
August 03, 2007
I am struggling to obtain argb colors from an excel sheet to store in a database, to be used later. below is the closest i got but it gives me wrong colors back (vb6 stored a value and it worked, same code in vs2005 errors) Dim Col As Color Col = Color.FromArgb(worksheet.Cells(ExcelRow, 4).interior.color.GetHashCode) Dim a As Byte = Col.A Dim r As Byte = Col.R Dim g As Byte = Col.G Dim b As Byte = Col.B fld = Format(a, "000") & "," & Format(r, "000") & "," & Format(g, "000") & "," & Format(b, "000") '''''''''' 2nd program acell = Excel.ActiveSheet.Cells(row, col) Fld = data1("rowcolor").Value acell.BackColor = Color.FromArgb(Mid(Fld, 1, 3), Mid(Fld, 5, 3), Mid(Fld, 9, 3), Mid(Fld, 13, 3)) thanks any help appreciatedAnonymous
August 13, 2007
Erica you are so awesome! I just realized that I was setting my columns at 0. Thanks, Carlos.Anonymous
September 26, 2007
Can any 1 tell me how to delete a row in excel through .Net(VB/c#) i've tried with excl(Excel object).Rows(i).Delete() but it is giving errorAnonymous
December 27, 2007
You need to get a range object and then delete the range as below. (I don't have experience delete rows, but this is how I delete cells. r1.Delete(XlDeleteShiftDirection.xlShiftUp);Anonymous
January 22, 2008
Thnks a lot it is very use full for me.....!Anonymous
February 11, 2008
Can someone tell me how to format the data in Excel File. For example i have data in the format "0527" in the dataset.But in the Excel file it is being displayed as "527" only. It is skipping the prefixed zeros. Please helpAnonymous
March 03, 2008
Hi friends, i want to display the data in asp.net along with cell colors as it is present in excel sheet, plz can any one help me. i'm using c# as code behind.Anonymous
April 15, 2008
I hate excel co-ords, so I made this. Helps with looping... <code> private static string ConvertToExcelCoord(int Col, int Row) { int c1 = -1; while (((int)'A') + Col > ((int)'Z')) { Col -= 26; c1++; } return (c1 >= 0 ? ((char)(((int)'A') + c1)).ToString() : "") + ((char)(((int)'A') + Col)).ToString() + ((int)(Row + 1)).ToString(); } </code> There should probably be a check and an exception if the coords are too big...Anonymous
October 20, 2008
I need to insert alt+enter in excel programmatically to show data in separate lines within a cell. Efrain Juarez asked this as well, but nobody replied so far.Anonymous
November 12, 2008
As my friends wrote before me, i need to insert Alt+Enter in Excel to show data in separate lines within a cell. Anyone knows a solution? Thanks a lot everybody.Anonymous
November 12, 2008
Well, I have de solution to represent Alt + Enter programmatically With VB.NET. WorkSheet(Row, Column).Value = "TextForFirstLine" & Chr(10) & "TextForSecondLine" So, Chr(10) represents Alt+Enter. I hope this solution helps you. Salutations everybody.Anonymous
December 07, 2008
Hi Erika, Can you suggest how to create borders around the cells(like a table format) in the Excel.
- Shruthi
Anonymous
January 10, 2009
Is there a way to filter a field with more than two criteria. In VBA you can specify an array list of criteria and I have been trying to implement something similar in C# with no luck. Below is the VBA example: ActiveSheet.ListObjects("tableOpenedData").Range.AutoFilter Field:=8, _ Criteria1:=Array("BLT / Desktop Tool", "Delivery", "Editorial"), Operator:= _ xlFilterValues Any suggestions on doing this in C# would be appreciated. Thanks!Anonymous
January 20, 2009
How to programatically pass ALT+ENTER from c#.netAnonymous
April 08, 2009
Can you suggest how to create borders around the cells(like a table format) in the Excel ? chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); ttp://csharp.net-informations.com/excel/csharp-format-excel.htm tks.Anonymous
November 11, 2009
please cab anyone help me this i tried various combinations but none seem to work outAnonymous
September 08, 2010
Erika, This was such a huge help - cleared up some of my confusion and is wonderful code. THANK YOU!!!Anonymous
October 16, 2011
The comment has been removed