MSDN Magazine - December 2008 - (Page 65)

the developer’s code, this also moves the type-checking from runtime to compile time, which improves the robustness of the application and reduces the cost of the test. With the extensions, you can index into the collection using either an integer or the name of the sheet: //Excel.Worksheet sheet = // book.Sheets.Item ("Sheet1"); Excel.Worksheet sheet = book.Sheets.Item (1); select c).ToArray(); foreach (Excel.Range cell in cells) { cell.Value2 = 0; } Next, we create a simple line chart from the mSec and Bytes columns and copy it to the Clipboard. With the PIA approach, we have to use loose typing, explicit optional parameters, and parameterized properties: Excel.Shape chart = sheet.Shapes.AddChart( Excel.XlChartType.xlLine, missing, missing, missing, missing); chart.Select(missing); excel.ActiveChart.SetSourceData( sheet.get_Range("A1", lastCell), missing); excel.ActiveChart.ChartArea.Copy(); In the example, we want to get specific cell ranges and insert some data. We will have two columns of data: one for the elapsed time in milliseconds between sending and receiving an e-mail message and one for the size of the e-mail in bytes. After inserting the column headings, we then insert the e-mail data into a grid of cells. Using the PIA approach, we again have to use parameterized properties for Range and Offset. Note that the emailData variable is an array of strings, which we will populate in the complete solution from e-mail data in Outlook: sheet.get_Range("A1", missing).Value2 = "mSec"; sheet.get_Range("B1", missing).Value2 = "Bytes"; Excel.Range firstCell = sheet.get_Range("A2", missing); Excel.Range lastCell; for (int r = 0; r < this.emailData.Length; r++) { String[] cellValues = this.emailData[r].Split(','); for (int c = 0; c = '" + (DateTime.Now.ToUniversalTime() - new TimeSpan(30, 0, 0, 0)).ToString("g") + @"'))"; } else { filter = @"@SQL=(((""http://schemas.microsoft.com/mapi/proptag/0x001a001e""" + "LIKE 'IPM.Note%')" + @" AND (""urn:schemas:httpmail:subject"" LIKE 'RE:%'))" + @" AND (""urn:schemas:httpmail:datereceived"" >= '" + (DateTime.Now.ToUniversalTime() - new TimeSpan(30, 0, 0, 0)).ToString("g") + @"'))"; } Outlook.Items folderItems = folder.Items; Outlook.Items filteredItems = folderItems.Restrict(filter); StringBuilder builder = new StringBuilder(); foreach (Outlook.MailItem item in filteredItems) { builder.AppendLine(String.Format("{0},{1},{2}", item.SentOn, item.ReceivedTime, item.Size)); } CalculateElapsedTime(builder.ToString()); Next, we want to find any negative values in the sheet and set them to zero. We can do this in a simple way, using the PIA: Excel.Range chartData = sheet.get_Range("A2", lastCell); foreach (Excel.Range cell in chartData) { if (((double)cell.Value2) < 0) { cell.Value2 = 0; } } Alternatively, we can do this using a SQL-like query, using the extensions. This uses the LINQ-to-Objects functionality in the extensions library. Note that LINQ is optimized for performance and uses a delayed execution model where the query is not actually executed until you begin to enumerate the resultset. In some scenarios, this can dramatically improve the performance of your application. That said, it is sometimes more efficient to force execution of the query by calling ToList or ToArray and then operate on the result—for instance if you intend to iterate over the resultset several times. In this example, the search condition is very simple (cell value less than zero), but you can see how a SQL-like query would become more useful as the complexity of the search conditions increases: Excel.Range chartData = sheet.Range("A2", lastCell); var cells = (from c in chartData.Items() where ((double)c.Value2) < 0 msdnmagazine.com December 2008 65 http://www.msdnmagazine.com

Table of Contents Feed for the Digital Edition of MSDN Magazine - December 2008

MSDN Magazine - December 2008 Contents Toolbox CLR Inside Out Advanced Basics Cutting Edge Patterns In Practice Team System Real-World WF Visual Studio OBA Tools SOA Data Access Geneva Framework Test Run Foundations Windows With C++ Going Places End Bracket

MSDN Magazine - December 2008

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.