Why should move to Visual Studio 2010

• Built-in tools for Windows 7, including multitouch and "ribbon" UI components
• Rich, new editor with built-in Windows Presentation Foundation (WPF)
• Multimonitor support
• New Quick Search, which helps to find relevant results with intellisense support
• Great support for developing and deploying Microsoft Office 2010, SharePoint 2010, and Windows Azure applications
• Multicore development support that allow to parallelize applications and a new specialized debugger to help to track the tasks and threads
• Improvements to the ASP.NET AJAX framework, core JavaScript IntelliSense support, and the inclusion in Visual Studio 2010 of JQuery, the open-source library for DOM interactions
• Multitargeting/multiframework support
• Support for developing WPF and Silverlight applications with enhanced drag-and-drop support and data binding
• Featured support for Team Foundation Server (TFS) 2010 (and previous versions) using Team Explorer
• Integrated support for test-driven development

For more information :

http://msdn.microsoft.com/en-us/library/dd547188(VS.100).aspx

http://msdn.microsoft.com/en-us/library/bb386063(VS.100).aspx

LINQ - Language Integrated Query

LINQ is a programming model that introduces queries as a first-class concept into any Microsoft .NET language. It allows you to write structured type-safe queries over local object collections and remote data sources. However, complete support for LINQ requires some extensions in the language used. These extensions boost productivity, thereby providing a shorter, meaningful, and expressive syntax to manipulate data.

The LINQ language extensions use the new Standard Query Operators API, which is the query language for any collection that implements IEnumerable<t>. It means that all collections and arrays can be queried using LINQ. The collections classes simply needs to implement IEnumerable<t>, to enable it for LINQ to query the collections. LINQ is a technology that covers many data domains. Some of these domains are included in those “LINQ Flavors” that Microsoft provides as part of the .NET 3.5 Framework, as shown in figure below:

LINQ to Objects

LINQ to Objects has the goal of manipulating collections of objects, which can be related to each other to form a hierarchy or a graph. From a certain point of view, LINQ to Objects is the default implementation used by a LINQ query.

LINQ to ADO.NET

LINQ to ADO.NET includes different LINQ implementations that share the need to manipulate relational data. It includes other technologies that are specific to each particular persistence layer:

• LINQ to SQL Handles the mapping between custom types in C# and the physical table schema.
• LINQ to Entities Is in many ways similar to LINQ to SQL. However, instead of using the physical database as a persistence layer, it uses a conceptual Entity Data Model (EDM). The result is an abstraction layer that is independent from the physical data layer.
• LINQ to DataSet Makes it possible to query a DataSet using LINQ.

LINQ to XML

LINQ to XML offers a slightly different syntax that operates on XML data, allowing query and data manipulation. It provides the in-memory document modification capabilities of the Document Object Model and supports LINQ queries.

SQL Server : Covering Index

Covering Index term is used to describe a certain technique that is used to improve performance. It does not mean a separate kind of index having a different internal structure.

      A covering index is a form of a composite index, includes all of the columns referenced in the SELECT, JOIN and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn’t have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance.

On the other hand, if the covering index gets too big (has too many columns), this could actually increase I/O and degrade performance. Generally, when creating covering indexes, follow these guidelines:

• If the query or queries you run using the covering index are seldom run, then the overhead of the covering index may outweigh the benefits it provides.
• The covering index should not add significantly to the size of the key. If it does, then it its use may outweigh the benefits it provides.
• The covering index must include all columns found in the SELECT list, the JOIN clause, and the WHERE clause.

To determine if a covering index could help a query’s performance is to create a graphical query execution plan in Query Analyzer or Management Studio and check for any Bookmark Lookups (RID or Key) being performed. Essentially, a Bookmark Lookup is indicate that the Query Processor had to look up the row columns it needs from a table or a clustered index, instead of being able to read it directly from a non-clustered index. Bookmark Lookups can reduce query performance because they produce extra disk I/O to retrieve the column data.One way to avoid a Bookmark Lookup is to create a covering index. This way, all the columns from the query are available directly from the non-clustered index, which means that Bookmark Lookups are unnecessary, which reduces disk I/O and helps to boost performance.

Example: (specifying the index explicitly in the query, forced SQL Server to use the non-clustered AK_Employee_NationalIDNumber index)

Query 2 is a covered query, a query where all the columns in the query's result set are pulled from non-clustered indexes.

SQL Collation

I came across very interesting question related to SQL Collation on SQLServerCentral.com.

Question: You administer two SQL Server 2005 SP3 installations at Niagara Falls, one on the US side and one on the Canadian side. Each server instance was installed with default settings on a Windows machine set to the proper regional settings for that country. You execute the following script on both servers:

DECLARE @var VARCHAR(MAX);
SET @var = 'Hello World';
SELECT REPLACE(@var, CHAR(0), '')

What is the result on each server?

Correct answer: US: 'Hello World'; Canada: [server hangs]

Explanation: The key to this question is the default collation each server will be set to based on the regional settings of the machine:

    English (United States): SQL_Latin1_General_CP1_CI_AS

    English (Canada): Latin1_General_CI_AS

This only happens with a varchar(max) value in the first argument of the REPLACE() function. If you alter the script to use an nvarchar, or a varchar(x), or eliminate the variable entirely and simply run the REPLACE() on the string itself (without casting the string as a varchar(max)), both servers return 'Hello World'. The same behavior can be reproduced on a single server by specifying the collation when casting 'Hello World' to varchar(max):

SELECT REPLACE(CAST('Hello World' COLLATE SQL_Latin1_General_CP1_CI_AS AS VARCHAR(MAX)), CHAR(0), ''); --returns 'Hello World'
SELECT REPLACE(CAST('Hello World' COLLATE Latin1_General_CI_AS AS VARCHAR(MAX)), CHAR(0), ''); --hangs

Click here for more information on SQLServerCentral.com

Working with SQL Server System Databases

This post will brief about the SQL Server system databases.

SQL Server System Databses:

•  master

The master database records all of the system level information for a SQL Server system. It records all login accounts and all system configuration settings. master is the database that records the existence of all other databases, including the location of the database files. master records the initialization information for SQL Server.

• tempdb

The tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. It is used to hold the temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors and internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database.

• model

The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.

• msdb

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.

Restrictions on these databases:

	master	tempdb	model	msdb
Adding filegroups	√	√	√
Backing up or restoring the database		√
Changing collation. The default collation is the server collation	√	√	√	√
Changing the database owner (owned by dbo)	√	√	√
Creating a full-text catalog or full-text index	√
Creating triggers on system tables in the database	√
Creating a database snapshot		√
Dropping the database	√	√	√	√
Dropping the guest user from the database	√	√	√	√
Enabling change data capture	√	√	√	√
Participating in database mirroring	√	√	√	√
Removing the primary filegroup, primary data file, or log file	√	√	√	√
Renaming the database or primary filegroup	√	√	√	√
Running DBCC CHECKALLOC		√
Running DBCC CHECKCATALOG		√
Setting the database to OFFLINE	√	√	√	√
Setting the database or primary filegroup to READ_ONLY	√	√	√	√
Creating procedures, views, or triggers using the WITH ENCRYPTION option			√

NOTE : The encryption key is tied to the database in which the object is created. Encrypted objects created in the model database can only be used in model.

SharePoint 2010

SharePoint is a rich framework for developing business collaboration solutions. In fact, from developer’s perspective, SharePoint is simply a rich set of tools, classes, libraries, controls, and so on that are useful for building custom solutions that are focused on making business collaboration possible. SharePoint is a platform for building websites, and of course, it can target intranet and extranet sites. But it is more than just that; you can use it to build any kind of web solution, including Internet publishing sites, by taking advantage of a well-defined and ready-to-use set of tools, based on a secure, scalable, and maintainable architecture. You can think of SharePoint as a superset of ASP.NET, with a broad set of services that can speed up the development of collaboration solutions.

Main Capabilities

Microsoft grouped the features and services in SharePoint 2010 into six main categories: Sites, Communities, Content, Search, Insights, and Composites. Figure shows these capabilities along with their main characteristics.

Organise your Gmail Inbox

One of the most important feature, sorting messages is missing in GMail. By default, Gmail sorts mails by date, which doesn’t do a great job in organizing your inbox. There is no facility to sort mails by name, subject, size and sender.

One thing to note that Gmail is based on search and not sort, therefore things have to be done here in different ways.

Find All Unread Messages in Gmail

To view all (and only) unread messages in your Gmail account:

• Type "is:unread" (not including the quotation marks) in the Gmail search field.
• Click Search Mail.

If you do this search repeatedly or generally appreciate brevity, you can use "l:unread" or even "l:^u" instead of "is:unread" (or "label:unread").

Of course, you can combine the quest for unread messages with others:

• "l:^u from:tim" finds all unread messages from "tim",
• "l:^u l:^t" finds all unread starred mail, and
• "l:^u l:^k subject:hi" finds all unread messages with "hi" in the subject that are in the Trash.

Gmail is different from the regular mail clients like outlook or yahoo. It’s a very powerful application, if only you can learn to search and make proper filters. Here are some of the search operators that you will find useful in daily life.

Operator	Definition	Example(s)
from:	Used to specify the sender	Example: from:amy Meaning: Messages from Amy
to:	Used to specify a recipient	Example: to:david Meaning: All messages that were sent to David (by you or someone else)
subject:	Search for words in the subject line	Example: subject:dinner Meaning: Messages that have the word "dinner" in the subject
OR	Search for messages matching term A or term B* *OR must be in all caps	Example: from:amy OR from:david Meaning: Messages from Amy or from David
- (hyphen)	Used to exclude messages from your search	Example: dinner -movie Meaning: Messages that contain the word "dinner" but do not contain the word "movie"
label:	Search for messages by label* *There isn't a search operator for unlabeled messages	Example: from:amy label:friends Meaning: Messages from Amy that have the label "friends" Example: from:david label:my-family Meaning: Messages from David that have the label "My Family"
has:attachment	Search for messages with an attachment	Example: from:david has:attachment Meaning: Messages from David that have an attachment
list:	Search for messages on mailing lists	Example: list:info@example.com Meaning: Messages with the words info@example.com in the headers, sent to or from this list
filename:	Search for an attachment by name or type	Example: filename:physicshomework.txt Meaning: Messages with an attachment named "physicshomework.txt" Example: label:work filename:pdf Meaning: Messages labeled "work" that also have a PDF file as an attachment
" " (quotes)	Used to search for an exact phrase* *Capitalization isn't taken into consideration	Example: "i'm feeling lucky" Meaning: Messages containing the phrase "i'm feeling lucky" or "I'm feeling lucky" Example: subject:"dinner and a movie" Meaning: Messages containing the phrase "dinner and a movie" in the subject
( )	Used to group words Used to specify terms that shouldn't be excluded	Example: from:amy (dinner OR movie) Meaning: Messages from Amy that contain either the word "dinner" or the word "movie" Example: subject:(dinner movie) Meaning: Messages in which the subject contains both the word "dinner" and the word "movie"
in:anywhere	Search for messages anywhere in Gmail* *Messages in Spam and Trash are excluded from searches by default	Example: in:anywhere movie Meaning: Messages in All Mail, Spam, and Trash that contain the word "movie"
in:inbox in:trash in:spam	Search for messages in Inbox, Trash, or Spam	Example: in:trash from:amy Meaning: Messages from Amy that are in Trash
is:important label:important	Search within messages that Priority Inbox considers important.	Example: is:important from:janet Meaning: Messages from Janet that were marked as important by Priority Inbox
is:starred is:unread is:read	Search for messages that are starred, unread or read	Example: is:read is:starred from:David Meaning: Messages from David that have been read and are marked with a star
has:yellow-star has:red-star has:orange-star has:green-star has:blue-star has:purple-star has:red-bang has:orange-guillemet has:yellow-bang has:green-check has:blue-info has:purple-question	Search for messages with a particular star	Example: has:purple-star from:David Meaning: Messages from David that are marked with a purple star
cc: bcc:	Used to specify recipients in the cc: or bcc: fields* *Search on bcc: cannot retrieve messages on which you were blind carbon copied	Example: cc:david Meaning: Messages that were cc-ed to David
after: before:	Search for messages sent during a certain period of time* *Dates must be in yyyy/mm/dd format.	Example: after:2004/04/16 before:2004/04/18 Meaning: Messages sent between April 16, 2004 and April 18, 2004.* *More precisely: Messages sent after 12:00 AM (or 00:00) April 16, 2004 and before April 18, 2004.
is:chat	Search for chat messages	Example: is:chat monkey Meaning: Any chat message including the word "monkey".
deliveredto:	Search for messages within a particular email address in the Delivered-To line of the message header	Example: deliveredto:username@gmail.com Meaning: Any message with username@gmail.com in the Delivered-To: field of the message header (which can help you find messages forwarded from another account or ones sent to an alias).

C#.NET - Export to Excel using Open Document Format

The Open Document Format for Office Applications (also known as OpenDocument or ODF) is an XML-based file format for representing electronic documents such as spreadsheets, charts, presentations and word processing documents.

This is really a very nice way to export the data to Excel.

Reference DLL : DocumentFormat.OpenXml.dll

public class ExcelXml
{
    public void BuildWorkbook(string fileName, DataSet ds)
    {
        try
        {
            using (SpreadsheetDocument s = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = s.AddWorkbookPart();
                WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();

                // Create Styles and Insert into Workbook
               WorkbookStylesPart stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
                Stylesheet styles = CreateStylesheet();
                styles.Save(stylesPart);

                string relId = workbookPart.GetIdOfPart(worksheetPart);

                Workbook workbook = new Workbook();
                FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
                Worksheet worksheet = new Worksheet();

                int numCols = ds.Tables[0].Columns.Count;

                Columns columns = new Columns();
                for (int col = 0; col < numCols; col++)
                {
                    Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, 25);

                    columns.Append(c);
                }
                worksheet.Append(columns);


                SheetData sheetData = new SheetData();

                Row headerRow = CreateHeaderRow(1, ds.Tables[0]);
                   sheetData.AppendChild(headerRow);

                UInt32 index = 2;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Row contentRow = CreateContentRow(index++, dr);
                       sheetData.AppendChild(contentRow);
                }

                   worksheet.Append(sheetData);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();
                Sheets sheets = new Sheets();
                Sheet sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
                sheets.Append(sheet);
                   workbook.Append(fileVersion);
                workbook.Append(sheets);
                s.WorkbookPart.Workbook = workbook;
                   s.WorkbookPart.Workbook.Save();
                s.Close();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            Console.ReadLine();
        }
    }

    private Column CreateColumnData(UInt32 startColumnIndex, UInt32 endColumnIndex, double columnWidth)
    {
        Column column;
        column = new Column();
        column.Min = startColumnIndex;
        column.Max = endColumnIndex;
        column.Width = columnWidth;
        column.CustomWidth = true;
        return column;
    }

  private Row CreateHeaderRow(UInt32 index, DataTable dt)
    {
        Row r = new Row { RowIndex = index };

        foreach (DataColumn col in dt.Columns)
        {
            Cell cell = CreateTextCell(col.ColumnName);
           cell.StyleIndex = 10;
            r.Append(cell);
        }
        return r;
    }

    private Row CreateContentRow(UInt32 index, DataRow dr)
    {
        Row r = new Row { RowIndex = index };

        foreach (Object itm in dr.ItemArray)
        {
            Cell cell;

            if (itm.GetType() == Type.GetType("System.Int32"))
                cell = CreateNumberCell(Convert.ToInt32(itm));
            else if (itm.GetType() == Type.GetType("System.Decimal"))
                cell = CreateDecimalCell(Convert.ToDecimal(itm));
            else
                cell = CreateTextCell(itm.ToString());

            r.Append(cell);
        }
        return r;
    }

    private Cell CreateTextCell(string text)
    {
        Cell c = new Cell { DataType = CellValues.InlineString };
        InlineString istring = new InlineString();
        Text t = new Text { Text = text };
        istring.Append(t);
        c.StyleIndex = 8;
        c.Append(istring);
        return c;
    }

    private Cell CreateTextCell(string header, UInt32 index, string text)
    {
        Cell c = new Cell { DataType = CellValues.InlineString, CellReference = header + index };
        InlineString istring = new InlineString();
        Text t = new Text { Text = text };
        istring.Append(t);
        c.StyleIndex = 8;
        c.Append(istring);
        return c;
    }

    private Cell CreateNumberCell(int number)
    {
        Cell c = new Cell();
        CellValue v = new CellValue { Text = number.ToString() };
        c.StyleIndex = 11;
        c.Append(v);
       return c;
    }

    private Cell CreateNumberCell(string header, UInt32 index, int number)
    {
        Cell c = new Cell { CellReference = header + index };
        CellValue v = new CellValue { Text = number.ToString() };
        c.StyleIndex = 8;
        c.Append(v);
        return c;
    }

    private Cell CreateDecimalCell(decimal number)
    {
        Cell c = new Cell();
        CellValue v = new CellValue { Text = number.ToString() };
        c.StyleIndex = 9;
        c.Append(v);
        return c;
    }

    private Cell CreateDecimalCell(string header, UInt32 index, decimal number)
    {
        Cell c = new Cell { CellReference = header + index };
        CellValue v = new CellValue { Text = number.ToString() };
        c.StyleIndex = 9;
        c.Append(v);
        return c;
    }

    private static Stylesheet CreateStylesheet()
    {
        Stylesheet ss = new Stylesheet();

        Fonts fts = new Fonts();
        DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
        FontName ftn = new FontName();
        ftn.Val = StringValue.FromString("Calibri");
        FontSize ftsz = new FontSize();
        ftsz.Val = DoubleValue.FromDouble(11);
        ft.FontName = ftn;
        ft.FontSize = ftsz;
        fts.Append(ft);

        ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
        ftn = new FontName();
        ftn.Val = StringValue.FromString("Calibri");
        ftsz = new FontSize();
        ftsz.Val = DoubleValue.FromDouble(11);
        Bold ftb = new Bold();
        ftb.Val = BooleanValue.FromBoolean(true);
        ft.FontName = ftn;
        ft.FontSize = ftsz;
        ft.Bold = ftb;
        fts.Append(ft);

        fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);

        Fills fills = new Fills();
        Fill fill;
        PatternFill patternFill;
        fill = new Fill();
        patternFill = new PatternFill();
        patternFill.PatternType = PatternValues.None;
        fill.PatternFill = patternFill;
        fills.Append(fill);

        fill = new Fill();
        patternFill = new PatternFill();
        patternFill.PatternType = PatternValues.Gray125;
        fill.PatternFill = patternFill;
        fills.Append(fill);

        fill = new Fill();
        patternFill = new PatternFill();
        patternFill.PatternType = PatternValues.Solid;
        patternFill.ForegroundColor = new ForegroundColor();
        patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ddd9c4");
        patternFill.BackgroundColor = new BackgroundColor();
        patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
        fill.PatternFill = patternFill;
        fills.Append(fill);

        fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);

        Borders borders = new Borders();
        Border border = new Border();
        border.LeftBorder = new LeftBorder();
        border.RightBorder = new RightBorder();
        border.TopBorder = new TopBorder();
        border.BottomBorder = new BottomBorder();
        border.DiagonalBorder = new DiagonalBorder();
        borders.Append(border);

        //Boarder Index 1
        border = new Border();
        border.LeftBorder = new LeftBorder();
        border.LeftBorder.Style = BorderStyleValues.Thin;
        border.RightBorder = new RightBorder();
        border.RightBorder.Style = BorderStyleValues.Thin;
        border.TopBorder = new TopBorder();
        border.TopBorder.Style = BorderStyleValues.Thin;
        border.BottomBorder = new BottomBorder();
        border.BottomBorder.Style = BorderStyleValues.Thin;
        border.DiagonalBorder = new DiagonalBorder();
        borders.Append(border);


        //Boarder Index 2
        border = new Border();
        border.LeftBorder = new LeftBorder();
        border.RightBorder = new RightBorder();
        border.TopBorder = new TopBorder();
        border.TopBorder.Style = BorderStyleValues.Thin;
        border.BottomBorder = new BottomBorder();
        border.BottomBorder.Style = BorderStyleValues.Thin;
        border.DiagonalBorder = new DiagonalBorder();
        borders.Append(border);


        borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);

        CellStyleFormats csfs = new CellStyleFormats();
        CellFormat cf = new CellFormat();
        cf.NumberFormatId = 0;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        csfs.Append(cf);
        csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);

        uint iExcelIndex = 164;
        NumberingFormats nfs = new NumberingFormats();
        CellFormats cfs = new CellFormats();

        cf = new CellFormat();
        cf.NumberFormatId = 0;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cfs.Append(cf);

        NumberingFormat nfDateTime = new NumberingFormat();
        nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
        nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
        nfs.Append(nfDateTime);

        NumberingFormat nf4decimal = new NumberingFormat();
        nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
        nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
        nfs.Append(nf4decimal);

        // #,##0.00 is also Excel style index 4
        NumberingFormat nf2decimal = new NumberingFormat();
        nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
        nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
        nfs.Append(nf2decimal);

        // @ is also Excel style index 49
        NumberingFormat nfForcedText = new NumberingFormat();
       nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
        nfForcedText.FormatCode = StringValue.FromString("@");
        nfs.Append(nfForcedText);

        NumberingFormat nfInteger = new NumberingFormat();
        nfInteger.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
        nfInteger.FormatCode = StringValue.FromString("#");
        nfs.Append(nfInteger);

        // index 1
        // Format dd/mm/yyyy
        cf = new CellFormat();
        cf.NumberFormatId = 14;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
        cfs.Append(cf);

        // index 2
        // Format #,##0.00
        cf = new CellFormat();
        cf.NumberFormatId = 4;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
        cfs.Append(cf);

        // index 3
        cf = new CellFormat();
        cf.NumberFormatId = nfDateTime.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
        cfs.Append(cf);

        // index 4
        cf = new CellFormat();
        cf.NumberFormatId = nf4decimal.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
        cfs.Append(cf);

        // index 5
        cf = new CellFormat();
        cf.NumberFormatId = nf2decimal.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
        cfs.Append(cf);

        // index 6
        cf = new CellFormat();
        cf.NumberFormatId = nfForcedText.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
        cfs.Append(cf);

        // index 7
        // Header text
        cf = new CellFormat();
        cf.NumberFormatId = nfForcedText.NumberFormatId;
        cf.FontId = 1;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
        cfs.Append(cf);

       // index 8
        // column text
        cf = new CellFormat();
        cf.NumberFormatId = nfForcedText.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 1;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
        cfs.Append(cf);

        // index 9
        // coloured 2 decimal text
        cf = new CellFormat();
        cf.NumberFormatId = nf2decimal.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 1;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
        cfs.Append(cf);

        // index 10
        // coloured column text
        cf = new CellFormat();
        cf.NumberFormatId = nfForcedText.NumberFormatId;
        cf.FontId = 1;
        cf.FillId = 2;
        cf.BorderId = 1;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
        cfs.Append(cf);

        // index 11
        // coloured 2 decimal text
        cf = new CellFormat();
        cf.NumberFormatId = nfInteger.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 1;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
        cfs.Append(cf);

        nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
        cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);

        ss.Append(nfs);
        ss.Append(fts);
        ss.Append(fills);
        ss.Append(borders);
        ss.Append(csfs);
        ss.Append(cfs);

        CellStyles css = new CellStyles();
        CellStyle cs = new CellStyle();
        cs.Name = "Normal";
        cs.FormatId = 0;
        cs.BuiltinId = 0;
        css.Append(cs);
        css.Count = (uint)css.ChildElements.Count;
        ss.Append(css);

        DifferentialFormats dfs = new DifferentialFormats();
        dfs.Count = 0;
        ss.Append(dfs);

        TableStyles tss = new TableStyles();
        tss.Count = 0;
        tss.DefaultTableStyle = "TableStyleMedium9";
        tss.DefaultPivotStyle = "PivotStyleLight16";
        ss.Append(tss);

        return ss;
    }
}