Erika Ehrli - Adventures with Office Products & Technologies
MSDN & TechNet: Releasing Office, SharePoint, Exchange & Lync Centers and content for developers and IT professionals.
 

October, 2005

  • Erika Ehrli - Adventures with Office Products & Technologies

    How to programmatically generate Microsoft Excel AutoFiltered Lists with C#

    • 41 Comments

    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!

  • Erika Ehrli - Adventures with Office Products & Technologies

    Visit the new InfoPath Developer Portal

    • 1 Comments

    The Office Developer Center has a new version of the InfoPath Developer Portal. Visit this portal to find InfoPath articles, blogs, newsgroup threads, code samples, getting started resources, labs, webcasts, and more.

     

  • Erika Ehrli - Adventures with Office Products & Technologies

    My two cents... (as my team says)

    • 0 Comments

    I remember many days (and nights too) when I was desperate trying to find some source code, conceptual or procedural documentation related to a specific technology and I just could not find the exact line of code that I was looking for. I remember also how grateful I felt when I found an article in a developer community Web site and I just can’t describe how thankful I was to the different authors that invested their time thinking in someone else and writing down their experiences and knowledge. By now, I owe two many beers to people that write for MSDN, Code Project, and C# Corner; however, more than buying a beer to every author who has helped me through my technical struggles, I knew that there was a better way to pay back the help and advice to the developer community: making yourself a contributor.

    I started writing technical articles and published them at my Web Site, later, some articles were published in C# Corner, and two months ago I joined Microsoft again. This time having the great opportunity to do developer evangelism through the Office Developer Center. I am currently writing my first "MSDN column" and in the middle of the writing I stopped to think about how much we can share with other people. Technology evolves so fast that a single person could never know everything there is to know. However, we still can learn from other people who share their knowledge with the community. So I am doing my best to help other people in the same way because I appreciate the effort and time that other people invest in sharing their knowledge.

    I don't have a big list of published articles because most of the technical documentation that I created in MCS belongs to the customers. However, I published this year the following technical articles:

  • Active Directory and .NET
  • Information Design
  • ASP.NET 2.0 Colorful Web Site Starter Kit
  • Insert Master-Detail Data by using the Transact-SQL OPENXML method with .NET
  • Object-Oriented Programming Concepts and .NET Part 1
  • Object-Oriented Programming Concepts and .NET Part 2
  • Building Stacks with C#
  • Insert programmatically a bitmap to Microsoft Word documents

    I hope the list keeps growing, and as I promised today to Guy Stat, I will get to "Object-Oriented Programming Concepts and .NET Part 3." I have been so excited with Office development that I completely forgot about this and I got to the very bad practice of writing an incomplete series of articles.

    If there is some topic that you think is a MUST at the Office Developer Center, please let me know. We are constantly looking for ideas of great content that developers would die to have.

    My two cents....

  • Erika Ehrli - Adventures with Office Products & Technologies

    Host an Interactive Visio Drawing Surface in .NET Custom Clients

    • 1 Comments

    I was making some research on Visio development for managed applications and I found this interesting article that explains in detail how to integrate an interactive Visio drawing board to your managed applications.

    Host an Interactive Visio Drawing Surface in .NET Custom Clients

    I have some friends from MCS that used the Visio Drawing Component for a project, and at the project's time, they found few documentation that explored this topic. However, now you can see this great article and ramp up quickly to Visio development.

  • Erika Ehrli - Adventures with Office Products & Technologies

    Getting the best out of bookmarks in Microsoft Word

    • 3 Comments

    Suppose you have a .NET application that needs to open a Word document and insert data from a database or from an XML file. To provide this functionality, Microsoft Word documents contain bookmarks, a set of "placeholders" that allow you to insert text at design time and runtime. I want to share with you some guidelines that I use to work with bookmarks:

    To go to a particular location and insert text, you can create your own bookmarks using the Boomark object. You can also take advantage of the set of prefedined bookmarks to go to a particular location in a document. For example, to insert text at the end of a document, you can do something like:

    private object _endOfDoc "\\endofdoc";
    Word.Range _rng doc.Bookmarks.get_Item(ref _endOfDoc).Range;
    _rng.Text "Bookmark demo";

    There are times when you have a huge document and you get lost with all the bookmarks that you might have, or like me, you create some bookmarks and forget or mispell the names you defined for the bookmarks (doh!) while coding. You can use the following snippet of code to get a list of all the bookmarks that you have in your document:

    public void ListBookmarks()
    {
        StringWriter sw 
    = new StringWriter();

        foreach 
    (Word.Bookmark bookmark in Word.Application.ActiveDocument.Bookmarks)
        {
            sw.WriteLine(
    "Name: {0}, Contents: {1}", bookmark.Name, bookmark.Range.Text);
        
    }
        MessageBox.Show(sw.ToString(), 
    "Bookmarks and Contents");
    }

    You should also explore the "Bookmark View Controls" provided by Visual Studio Tools for Office v2.0. Take a look at this cool video created by Paul Stub and learn more about bookmarks.

    Happy bookmark programming!

  • Erika Ehrli - Adventures with Office Products & Technologies

    Office "12" supports "Save As PDF"

    • 5 Comments

    I didn't know about that, but Office Online receives over 120,000 queries per month requesting the ability to "save as PDF" in Office! Microsoft has LISTENED to customers and has an answer for a common need. Steven Sinofsky announced this weekend at the MVP Summit another great feature provided by Office "12:"

    "Office Word, Excel, PowerPoint, Access, InfoPath, Publisher, Visio, Project, and OneNote – will provide the capability to save documents in the PDF format, which will include a fixed layout representation of the document. PDF documents created by Office “12” may contain live hyperlinks, cross-document links and bookmarks and will support accessibility. PDF documents produced from Microsoft Office Publisher “12” will also include additional support for pre-press specific functionality such as CMYK color models and printing page marks. The PDF format has been developed by Adobe and the specifications are available for developers like Microsoft to use."

    I know that lots of customers and developers worldwide will be more than excited about this. Lots of Microsoft Consulting Services customers required to integrate "save as PDF" functionality to their solutions; I would love to be back with them and tell them: You can!

    Today, a big set of managed applications generate Word documents, and Excel spreadsheets, and now, without using third-party PDF libraries, you will be able to change some lines of code to save the files using PDF format. Even outside managed applications, you can now save your legal contracts, requests for proposals, work orders, finance budgets and more!

    The best part is that Office "12" still has more surprises to announce, so keep close to Office Developer Center and be the first to know the future of Microsoft Office.

  • Page 1 of 1 (6 items)