Microsoft

Creating Excel Files from DataSet using OpenXML 2.0 – C#

September 26, 2010 .NET, All, C#.NET, Microsoft, Microsoft SDKs, Office, Open XML 3 comments

Introduction

It is quite often we programmers have to develop business applications which provides the facility for the end-user, such as “Export to Excel”, dumping report information to an Excel worksheet and mail them to the user. Since excel is a good medium to generate easy to use reports, calculation, sales forcasts etc.

Imagine that you need to retrieve a list of all the worksheets within an Excel workbook or set of workbooks. The ability to perform this operation without requiring you to load Excel 2007 and then load the workbooks, one after another, can be an incredible time saver.

I was looking for different alternatives to write genuine Excel files.  I was familiar with the two options

1. Using JET OLEDB Provider and reading/writing excel files using OLEDB Connection.  (This is a pretty good way of reading and writing excel file considering the file itself as DataSource for OLEDB Connection. The problem is deleting recording is not possible, this is a serious limitation. Some times we would be needed to clear all the contents from Excel sheet and write contents freshly)

  1. Using Microsoft Office Excel Interoperability COM assemblies. (This requires the Microsoft Office Excel Suite to be installed on the target system, this is costly some of our clients will not opt for it, because of the license cost). 

Because of the limitations of above two options, we have to look for another alternative. A set of Costly third party libraries or Microsoft’s own open library “Open XML”. With introduction of Office 2007, things are changed. The Office Open XML File Formats(XLSX, DOCX ,PPTX) make this task possible. Working with the Office Open XML File Formats requires knowledge of the way Excel stores its content, the System.IO.Packaging API, and XML programming.

Introduction to Open XML

Office Open XML (also informally known as OOXML or OpenXML) is a zipped, XML-based file format developed by Microsoft for representing spreadsheets, charts, presentations and word processing documents. The Office Open XML specification has been standardised by Ecma. A later edition was standardized by ISO and IEC as an International Standard (ISO/IEC 29500); this edition is still not implemented in any products.

Starting with Microsoft Office 2007, the Office Open XML file formats (ECMA-376) have become the default target file format of Microsoft Office, although the Strict variant of the standard is not fully supported. Microsoft Office 2010 provides read support for ECMA-376, read/write support for ISO/IEC 29500 Transitional, and read support for ISO/IEC 29500 Strict.

Reference Links: OpenXML Developer WebSite, 

Required Files for using OpenXML with C# / VB.NET

Open XML SDK 2.0 for Microsoft Office  – download

Open XML SDK 2.0 provides an open source library DocumentFormat.OpenXml.dll using this library, .net developers can leverage the power of reading/writing Microsoft Office documents (PPTX, XLSX, DOCX) – From Microsoft Office 2007 onwards Microsoft focusing on Open XML based document types (PowerPoint: PPTX, Excel: XLSX, Document: DOCX) .

Not to be confused with OpenOffice.org XML (a deprecated format used by early versions of OpenOffice.org), or Microsoft Office XML formats (deprecated formats used by earlier versions of Microsoft Office).
Microsoft Office Word 2003 XML Format — WordProcessingML or WordML (.XML)
Microsoft Office Excel 2002 and Excel 2003 XML Format — SpreadsheetML (.XML)
Microsoft Office Visio 2003 XML Format — DataDiagramingML (.VDX, .VSX, .VTX)
Microsoft Office InfoPath 2003 XML Format — XML FormTemplate (.XSN) (Compressed XML templates in a Cabinet file)

These are all deprecated now after the release of Office 2007, which came with Open XML Formats( XLSX, PPTX, DOCX), and open wide variaty of programming options for the developers.

Here I am going to discuss / demonstrate only about the Excel (XSLX) file format. With Open XML we can create Charts, Calculations, alter existing Excel sheet contents, write new worksheet. Extensibility of Excel document creation is in your hand now.

For demonstrate the Excel document format & Relationships, I am going to use a diagram lend from CodeProject article Reading & Writing Open XML Files.

From the above diagram you will get an exact idea how the Excel – Open XML relational structure. Workbook with multiple worksheets, you can customize the styles and settings for the workbook or per sheets in the Excel file. There are more to discuss about the things in details, but my time is limited and I am going to provide few helpful reference links in the end of this article. With which you will get an idea about Open XML.

My Sample Solution – Source Code

I decided to build a Utility class which will be reusable, so that other business logic classes can utilize this method for reading & writing excel document with help of Open XML 2.0 library DocumentFormat.OpenXml.dll .

My solution is a console application consist of

Created a Solution named : ConsoleApp

Add reference to DocumentFormat.OpenXml.dll (Open XML 2.0 Library)

ExcelUtils.cs -> The implementation utility class consisting the necessary methods for writing excel file taking input in the form of a DataSet(consisting of multiple tables, transformed in to multiple worksheets while writing to EXCEL). This class also has the methods to clear existing contents from a worksheet, delete a worksheet, create a new worksheet if not exists and if worksheet exists append the records.

Create(string fileName, DataSet inputDataSet) -> this method creates a new Excel file in specified path from the inputDataSet. This is limited to write/overwrite only, this method doesn’t check for sheet already exist or not. This method is developed in LinQ based expression for converting the DataSet to a hirachical List.

SaveWorkbook(string fileName, DataSet inputData, bool overwriteContents, bool clearHeader ) This method has the logic to create / modify an excel sheet.
Parameters : overwriteContents – This will clear all contents from a worksheet except the header.
clearHeader – Clear the header /column defenition also from worksheet when overwriteContents = true.
DeleteWorkSheet(string fileName, string sheetToDelete) Deletes a worksheet from an existing excel file.


using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace ConsoleApp
{
    /// <summary>
    ///
    /// </summary>
    public class ExcelUtils
    {
        /// <summary>
        /// Builds the workbook.
        /// </summary>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="inputData">The input data.</param>
        /// <param name="overwriteContents"></param>
        /// <param name="clearHeader"></param>
        public void SaveWorkbook(string fileName, DataSet inputData, bool overwriteContents, bool clearHeader )
        {
            SpreadsheetDocument spreadsheetDocument;
            bool isNewFile = false;
            try
            {

                if (!File.Exists(fileName))
                {
                    spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook); //Create
                    isNewFile = true;
                }
                else
                {
                    spreadsheetDocument = SpreadsheetDocument.Open(fileName, true); //Opening exisiting
                    isNewFile = false;
                }

                if(true)//using (spreadsheetDocument)
                {

                    WorkbookPart workbookPart;

                    //Instantiates workbookpart
                    if (isNewFile)
                        workbookPart = spreadsheetDocument.AddWorkbookPart(); //If file is new file
                    else
                        workbookPart = spreadsheetDocument.WorkbookPart;

                    //Creates Workbook if workbook not existed(For New Excel File), Existing excel file this is not necessary
                    if (workbookPart.Workbook == null)
                         workbookPart.Workbook  = new Workbook();

                    Sheets sheets ;

                    //
                    if(isNewFile)
                        sheets = workbookPart.Workbook.AppendChild(new Sheets()); //Adding new sheets to the file, if new file
                    else
                        sheets = workbookPart.Workbook.Sheets; //Retrieving existing sheets from the file

                    FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
                   //  workbook.Append(fileVersion);

                    uint tableIndex = 0;

                    //converting the sheets collection to a list of <Sheet>
                    List<Sheet> sheetsList = workbookPart.Workbook.Descendants<Sheet>().ToList();

                    // If the InputDataSet having 1 or more tables, looping through and
                    // creates new sheet for each table and dumps the data to the sheet
                    // and saves the excel workbook.
                    foreach (DataTable inputDataTable in inputData.Tables)
                    {
                        bool hasSheetExists = false;
                        int sheetIndex = 0;
                        string relId = "";
                        Sheet sheet;

                        //Checking sheet exists in the excel file.
                        sheetIndex   =  sheetsList.FindIndex(c => c.Name == inputDataTable.TableName);

                        if( sheetIndex >= 0)
                        {
                            hasSheetExists = true;
                        }

                        WorksheetPart worksheetPart;

                        if(!hasSheetExists) //If a new sheet
                        {

                            worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                            relId = workbookPart.GetIdOfPart(worksheetPart);
                            sheet = new Sheet { Name = inputDataTable.TableName, SheetId = tableIndex + 1, Id = relId };
                        }
                        else // if sheet already exists
                        {
                             sheet = sheetsList[sheetIndex];

                            List<WorksheetPart> partList = workbookPart.WorksheetParts.ToList();

                             // Take the existing the sheet reference from the workbook.
                            WorksheetPart worksheetPart1 = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));

                            worksheetPart = worksheetPart1;

                            //Retriving RelationID form the workbookPart
                            relId = workbookPart.GetIdOfPart(worksheetPart);
                            // partList.FindAll( c=> c.)
                           // worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                        }

                        SheetData sheetData = new SheetData();

                        Worksheet workSheet = worksheetPart.Worksheet;

                        int lastRowIndex = 0;

                        if (workSheet != null)
                        {
                            //Retrieving existing sheet data from the worksheet
                            sheetData = workSheet.GetFirstChild<SheetData>();

                            if(overwriteContents) //Clearing the contents of the Sheet, except the header
                            {
                                int endIndex = 1;

                                //if value true clear the existing header
                                if (clearHeader)
                                    endIndex = 0;

                                //Deleting content row by row, starting from the bottom.
                                for (int childIndex = sheetData.ChildElements.Count - 1; childIndex >= endIndex; childIndex--)
                                {
                                    sheetData.RemoveChild(sheetData.ChildElements[childIndex]);
                                }
                            }

                            //Getting all existing record rows.
                            IEnumerable<Row> rows = sheetData.Descendants<Row>();

                            //Considering the last row index as total row count. append the records to the last index onwards.
                            lastRowIndex = rows.Count();
                        }
                        else //Creating new worksheet
                        {
                            workSheet = new Worksheet(sheetData);
                            worksheetPart.Worksheet = workSheet;
                        }

                        //If Data Table is not empty
                        if (inputDataTable != null && inputDataTable.Rows.Count > 0)
                        {
                            //If Not Sheet already exists(Based on Table Name) -- creating column headers for the excel sheet
                            if (!hasSheetExists || lastRowIndex < 1)
                            {
                                //Creating columns..(INDX = 1 , Header)
                                Row headerRow = CreateContentHeader(1, inputDataTable.Columns);
                                sheetData.Append(headerRow);
                                lastRowIndex = 1;
                            }

                            //Last Row index
                            lastRowIndex++;

                            //Worksheet Data Row Number.. (INDX = 2 onwards data)
                            uint currDataRowIndex = (uint) lastRowIndex; //From this index on data will get appended.

                            //Creating Row Data
                            for (int iterRowIndex = 0; iterRowIndex < inputDataTable.Rows.Count; iterRowIndex++)
                            {
                                //Retrieving current DataRow from DataTable
                                DataRow currentInputRow = inputDataTable.Rows[iterRowIndex];

                                //Creating insertble row for the openxml.
                                Row contentRow = CreateContentRow(currDataRowIndex, currentInputRow,
                                                                  inputDataTable.Columns);
                                currDataRowIndex++;

                                //Appending to sheet data
                                sheetData.AppendChild(contentRow);
                            }
                        }

                        //new Worksheet(sheetData);

                        //Saving worksheet contents
                        worksheetPart.Worksheet.Save();

                        //If sheet new, then appending to sheets collection
                        if(!hasSheetExists)
                         sheets.AppendChild(sheet);

                        tableIndex++;

                    }

                    //Saving the complete workbook to disk
                    spreadsheetDocument.WorkbookPart.Workbook.Save();

                    spreadsheetDocument.Close();

                }

            }

            catch (Exception)
            {
               // spreadsheetDocument.Close();
                throw;

            }

        }

        /// <summary>
        /// Creates the content header.
        /// </summary>
        /// <param name="rowDataIndex">Index of the row data.</param>
        /// <param name="dataColumns">The data columns.</param>
        /// <returns></returns>
        private Row CreateContentHeader(UInt32 rowDataIndex, DataColumnCollection dataColumns)
        {
            Row resultRow = new Row { RowIndex = rowDataIndex };

            for (int iterColIndex = 0; iterColIndex < dataColumns.Count; iterColIndex++)
            {
                Cell cell1 = CreateHeaderCell(dataColumns[iterColIndex].ColumnName, rowDataIndex, dataColumns[iterColIndex].ColumnName);  //CreateTextCell("A", rowDataIndex, Convert.ToString(dataRow[iterColIndex]));
                resultRow.Append(cell1);

            }

            return resultRow;
        }

        /// <summary>
        /// Creates the content row.
        /// </summary>
        /// <param name="rowDataIndex">The rowDataIndex.</param>
        /// <param name="dataRow">DataRow</param>
        /// <param name="dataColumns">DataColumnCollection</param>
        /// <returns></returns>
        private  Row CreateContentRow(UInt32 rowDataIndex, DataRow dataRow,DataColumnCollection dataColumns)
        {

            Row resultRow = new Row { RowIndex = rowDataIndex };

            for(int iterColIndex =0; iterColIndex < dataColumns.Count; iterColIndex ++)
            {
                Cell cell1 = CreateContentCell(dataColumns[iterColIndex].ColumnName, rowDataIndex, dataRow[iterColIndex]);  //CreateTextCell("A", rowDataIndex, Convert.ToString(dataRow[iterColIndex]));
                resultRow.Append(cell1);

            }

            return resultRow;

        }

        /// <summary>
        /// Creates the content cell.
        /// </summary>
        /// <param name="header">The header.</param>
        /// <param name="index">The rowDataIndex.</param>
        /// <param name="inputValue">The input value.</param>
        /// <returns></returns>
        private Cell CreateContentCell(string header, UInt32 index,  object  inputValue)
        {
            Cell resultCell = null;

            Type objectType = inputValue.GetType();

            TypeCode objectTypeCode;

            bool parseSuccess = Enum.TryParse(objectType.Name, true, out objectTypeCode);

            if (parseSuccess)
            {
                switch (objectTypeCode)
                {
                    // Number Fields
                    case TypeCode.UInt64:
                    case TypeCode.UInt32:
                    case TypeCode.UInt16:
                    case TypeCode.Int64:
                    case TypeCode.Int32:
                    case TypeCode.Int16:
                    case TypeCode.Double :
                    case TypeCode.Decimal :
                        resultCell = CreateNumberCell(header, index, inputValue);
                        break;
                    // Date Time Field
                    case TypeCode.DateTime :
                        resultCell = CreateDateCell(header, index, inputValue);
                        break;
                    // Boolean Field
                    case TypeCode.Boolean:
                        resultCell = CreateBooleanCell(header, index, inputValue);
                        break;

                    default :
                       resultCell  =  CreateTextCell(header, index, inputValue);
                        break;
                        //case TypeCode.
                }
            }
            else
               resultCell =  CreateTextCell(header, index, inputValue);
            return resultCell;
        }

        /// <summary>
        /// Creates the header cell.
        /// </summary>
        /// <param name="header">The header.</param>
        /// <param name="index">The index.</param>
        /// <param name="text">The text.</param>
        /// <returns></returns>
        private Cell CreateHeaderCell(string header, UInt32 index, object text)
        {
            Cell c = new Cell { DataType = CellValues.String, CellReference = header + index };

            CellValue cellValue = new CellValue
                              {
                                  Text = Convert.ToString(text),
                              };

            c.Append(cellValue);

            return c;

        }

        /// <summary>
        /// Creates the text cell.
        /// </summary>
        /// <param name="header">The header.</param>
        /// <param name="index">The rowDataIndex.</param>
        /// <param name="text">The text.</param>
        /// <returns></returns>
        private  Cell CreateTextCell(string header, UInt32 index, object text)
        {

            Cell c = new Cell { DataType = CellValues.InlineString, CellReference = header + index };

            InlineString istring = new InlineString();

            Text t = new Text { Text = Convert.ToString(text) };

            istring.Append(t);

            c.Append(istring);

            return c;

        }

        /// <summary>
        /// Creates the number cell.
        /// </summary>
        /// <param name="header">The header.</param>
        /// <param name="index">The rowDataIndex.</param>
        /// <param name="number">The number.</param>
        /// <returns></returns>
        private Cell CreateNumberCell(string header, UInt32 index, object number)
        {

            Cell c = new Cell
            {
                CellReference = header + index,
                DataType = CellValues.Number
            };

            CellValue v = new CellValue
            {
                Text = Convert.ToString(number),
               // DataType = CellValues.Number,
            };

            c.Append(v);

            return c;

        }

        /// <summary>
        /// Creates the date cell.
        /// </summary>
        /// <param name="header">The header.</param>
        /// <param name="index">The rowDataIndex.</param>
        /// <param name="number">The date.</param>
        /// <returns></returns>
        private Cell CreateDateCell(string header, UInt32 index, object date)
        {

            Cell c = new Cell
            {
                CellReference = header + index,
                DataType = CellValues.Date
            };

            CellValue v = new CellValue
            {
                Text = Convert.ToString(date),
                // DataType = CellValues.Number,
            };

            c.Append(v);

            return c;

        }

        /// <summary>
        /// Creates the date cell.
        /// </summary>
        /// <param name="header">The header.</param>
        /// <param name="index">The rowDataIndex.</param>
        /// <param name="number">The date.</param>
        /// <returns></returns>
        private Cell CreateBooleanCell(string header, UInt32 index, object boolVal)
        {

            Cell c = new Cell
            {
                CellReference = header + index,
                DataType = CellValues.Boolean
            };

            CellValue v = new CellValue
            {
                Text = Convert.ToString(boolVal),
                // DataType = CellValues.Number,
            };

            c.Append(v);

            return c;

        }

        #region DeleteWorksheet()

        /// <summary>
        /// Deletes the A work sheet.
        /// </summary>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="sheetToDelete">The sheet to delete.</param>
        public void DeleteWorkSheet(string fileName, string sheetToDelete)
        {
            string Sheetid = "";
            //Open the workbook
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
            {
                WorkbookPart wbPart = document.WorkbookPart;

                // Get the pivot Table Parts
                IEnumerable<PivotTableCacheDefinitionPart> pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts;
                Dictionary<PivotTableCacheDefinitionPart, string> pvtTableCacheDefinationPart = new Dictionary<PivotTableCacheDefinitionPart, string>();
                foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
                {
                    PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition;
                    //Check if this CacheSource is linked to SheetToDelete
                    var pvtCahce = pvtCacheDef.Descendants<CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToDelete);
                    if (pvtCahce.Count() > 0)
                    {

                        pvtTableCacheDefinationPart.Add(Item, Item.ToString());
                    }
                }
                foreach (var Item in pvtTableCacheDefinationPart)
                {
                    wbPart.DeletePart(Item.Key);
                }
                //Get the SheetToDelete from workbook.xml
                Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetToDelete).FirstOrDefault();
                if (theSheet == null)
                {
                    // The specified sheet doesn't exist.
                }
                //Store the SheetID for the reference
                Sheetid = theSheet.SheetId;

                // Remove the sheet reference from the workbook.
                WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
                theSheet.Remove();

                // Delete the worksheet part.
                wbPart.DeletePart(worksheetPart);

                //Get the DefinedNames
                var definedNames = wbPart.Workbook.Descendants<DefinedNames>().FirstOrDefault();
                if (definedNames != null)
                {
                    foreach (DefinedName Item in definedNames)
                    {
                        // This condition checks to delete only those names which are part of Sheet in question
                        if (Item.Text.Contains(sheetToDelete + "!"))
                            Item.Remove();
                    }
                }
                // Get the CalculationChainPart
                //Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the
                //workbook whose value is calculated from any formula

                CalculationChainPart calChainPart;
                calChainPart = wbPart.CalculationChainPart;
                if (calChainPart != null)
                {
                    var calChainEntries = calChainPart.CalculationChain.Descendants<CalculationCell>().Where(c => c.SheetId == Sheetid);
                    foreach (CalculationCell Item in calChainEntries)
                    {
                        Item.Remove();
                    }
                    if (calChainPart.CalculationChain.Count() == 0)
                    {
                        wbPart.DeletePart(calChainPart);
                    }
                }

                // Save the workbook.
                wbPart.Workbook.Save();
            }
        }

        #endregion

        #region  ClearWorkSheetData

        /// <summary>
        /// Deletes the A work sheet.
        /// </summary>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="sheetToDelete">The sheet to delete.</param>
        public void ClearWorkSheetData(string fileName, string sheetToClear)
        {
            string Sheetid = "";
            //Open the workbook
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
            {
                WorkbookPart wbPart = document.WorkbookPart;

                // Get the pivot Table Parts
                IEnumerable<PivotTableCacheDefinitionPart> pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts;
                Dictionary<PivotTableCacheDefinitionPart, string> pvtTableCacheDefinationPart = new Dictionary<PivotTableCacheDefinitionPart, string>();
                foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
                {
                    PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition;
                    //Check if this CacheSource is linked to SheetToDelete
                    var pvtCahce = pvtCacheDef.Descendants<CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToClear);
                    if (pvtCahce.Count() > 0)
                    {

                        pvtTableCacheDefinationPart.Add(Item, Item.ToString());
                    }
                }
                foreach (var Item in pvtTableCacheDefinationPart)
                {
                    wbPart.DeletePart(Item.Key);
                }
                //Get the SheetToDelete from workbook.xml
                Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetToClear).FirstOrDefault();
                if (theSheet == null)
                {
                    // The specified sheet doesn't exist.
                }
                //Store the SheetID for the reference
                Sheetid = theSheet.SheetId;

                // Remove the sheet reference from the workbook.
                WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));

                Worksheet workSheet = worksheetPart.Worksheet;
                SheetData sheetData = workSheet.GetFirstChild<SheetData>();

                for(int childIndex = 1; childIndex < sheetData.ChildElements.Count; childIndex ++)
                {
                    sheetData.RemoveChild(sheetData.ChildElements[childIndex]);
                }

                IEnumerable<Row> rows = sheetData.Descendants<Row>();

                List<Row> rowsList = rows.ToList();

                //rowsList.RemoveRange(1, rowsList.Count - 1);

                // Save the workbook.
                wbPart.Workbook.Save();
            }
        }

        #endregion

        #region Create(string fileSavePath, DataSet dataSet)

        /// <summary>
        /// Saves the specified file save path.
        /// </summary>
        /// <param name="fileSavePath">The file save path.</param>
        /// <param name="dataSet">The data set.</param>
        public void Create(string fileSavePath, DataSet dataSet)
        {

            Dictionary<string, List<OpenXmlElement>> inputDictionary = ToSheets(dataSet);

            Create(fileSavePath, inputDictionary);
           // inputDictionary
        }

        /// <summary>
        /// Creates the specified path.
        /// </summary>
        /// <param name="path">The path.</param>
        /// <param name="sets">The sets.</param>
        private void Create(string path, Dictionary<String, List<OpenXmlElement>> sets)
        {
            using (SpreadsheetDocument package = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookpart = package.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();

                Sheets sheets = workbookpart.Workbook.AppendChild(new Sheets());

                foreach (KeyValuePair<String, List<OpenXmlElement>> set in sets)
                {
                    WorksheetPart worksheetpart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetpart.Worksheet = new Worksheet(new SheetData(set.Value));
                    worksheetpart.Worksheet.Save();

                    Sheet sheet = new Sheet()
                    {
                        Id = workbookpart.GetIdOfPart(worksheetpart),
                        SheetId = (uint)(sheets.Count() + 1),
                        Name = set.Key
                    };
                    sheets.AppendChild(sheet);
                }
                workbookpart.Workbook.Save();
            }
        }

        /// <summary>
        /// Toes the sheets.
        /// </summary>
        /// <param name="ds">The ds.</param>
        /// <returns></returns>
        private Dictionary<string, List<OpenXmlElement>> ToSheets(DataSet ds)
        {
            return
                (from dt in ds.Tables.OfType<DataTable>()
                 select new
                 {
                     // Sheet Name
                     Key = dt.TableName,
                     Value = (
                         // Sheet Columns
                     new List<OpenXmlElement>(
                        new OpenXmlElement[]
                {
                    new Row(
                        from d in dt.Columns.OfType<DataColumn>()
                        select (OpenXmlElement)new Cell()
                        {
                            CellValue = new CellValue(d.ColumnName),
                            DataType = CellValues.String
                        })
                })).Union
                         // Sheet Rows
                     ((from dr in dt.Rows.OfType<DataRow>()
                       select ((OpenXmlElement)new Row(from dc in dr.ItemArray
                                                       select (OpenXmlElement)new Cell()
                                                       {
                                                           CellValue = new CellValue(dc.ToString()),
                                                           DataType = CellValues.String
                                                       })))).ToList()
                 }).ToDictionary(p => p.Key, p => p.Value);
        }

        #endregion

        #region Read

        /// <summary>
        /// Reads the specified file save path.
        /// </summary>
        /// <param name="fileSavePath">The file save path.</param>
        /// <returns></returns>
        public DataSet Read(string fileSavePath)
        {

            DataSet resultSet = new DataSet();

            using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileSavePath, false))
            {

                WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
                IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();

                foreach (Sheet sheet in sheets)
                {
                    DataTable dt = new DataTable();

                    string relationshipId = sheet.Id.Value;
                    string sheetName      = sheet.SheetId;
                    dt.TableName            = sheet.SheetId;

                    WorksheetPart worksheetPart =
                        (WorksheetPart) spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
                    Worksheet workSheet = worksheetPart.Worksheet;
                    SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                    IEnumerable<Row> rows = sheetData.Descendants<Row>();

                    foreach (Cell cell in rows.ElementAt(0))
                    {
                        dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
                    }

                    List<Row> rowsList = new List<Row>();

                    rowsList = rows.ToList();

                    //Start from 1, first row is header.
                    for ( int iterRowIndex = 1 ; iterRowIndex  < rowsList.Count ; iterRowIndex ++) //this will also include your header row...
                    {
                        Row row = rowsList[iterRowIndex];

                        DataRow tempRow = dt.NewRow();

                        for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                        {
                            tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                        }

                        dt.Rows.Add(tempRow);
                    }

                    resultSet.Tables.Add(dt);
                }

            }

            return resultSet;
        }

        /// <summary>
        /// Gets the cell value.
        /// </summary>
        /// <param name="document">The document.</param>
        /// <param name="cell">The cell.</param>
        /// <returns></returns>
        public static string GetCellValue(SpreadsheetDocument document, Cell cell)
        {
            SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
            string value = cell.CellValue != null ? cell.CellValue.InnerXml : string.Empty;

            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            }
            else
            {
                return value;
            }
        }

        #endregion
    }
}

Program.cs  -> The entry point method in which I will be creating the DUMMY Dataset, to pass as input to SaveWorksheet(string fileSavePath, DataSet ds) method.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;

namespace ConsoleApp
{
    class Program
    {
        /// <summary>
        /// Mains the specified args.
        /// </summary>
        /// <param name="args">The args.</param>
        static void Main(string[] args)
        {

            ExcelUtils excelUtils = new ExcelUtils();

            DataSet dummySet = CreateDummyDataSet();

            excelUtils.SaveWorkbook("c:Test9.xlsx", dummySet,true,true);

           // excelUtils.Save("c:Test7.xlsx", dummySet);
            DataSet dsNewSet =  excelUtils.Read("c:Test9.xlsx");

            Console.Read();
        }

        /// <summary>
        /// Creates the dummy data set.
        /// </summary>
        /// <returns></returns>
        public static DataSet CreateDummyDataSet()
        {
            DataSet resultSet = new DataSet();

            for (int tableIndex = 0; tableIndex < 5; tableIndex ++ )
            {
                DataTable dataTable = new DataTable("Table" + tableIndex.ToString());

                for (int colIndex = 0; colIndex < 10; colIndex++)
                {
                    if(colIndex % 2 == 0)
                    {
                        dataTable.Columns.Add("Column" + colIndex.ToString(), Type.GetType("System.Decimal"));
                    }
                    else
                    {
                        dataTable.Columns.Add("Column" + colIndex.ToString(), Type.GetType("System.String"));
                    }
                }

                for(int rowIndex = 0; rowIndex < 20000; rowIndex++)
                {
                    DataRow dataRow = dataTable.NewRow();

                    for (int colIndex = 0; colIndex < dataTable.Columns.Count; colIndex++)
                    {
                        if (colIndex % 2 == 0)
                        {
                            dataRow[dataTable.Columns[colIndex].ColumnName] = (rowIndex* (colIndex+1)) + colIndex;
                        }
                        else
                        {
                            dataRow[dataTable.Columns[colIndex].ColumnName] = "STR : " + (rowIndex * (colIndex + 1)) + colIndex;
                        }

                    }
                    dataTable.Rows.Add(dataRow);
                }
                resultSet.Tables.Add(dataTable);
            }

            return resultSet;
        }

    }
}

The above code snippets writes / appends the input DataSet content to Excel File.

This just a sample code and I have tested the code on appending records to existing sheet, clearing the existing contents and write records to the file or delete a worksheet from the existing excel file.

Helpful Links / References:

Reading & Writing Open XML Files – Code Project

OpenXML Developer WebSite

Wikipedia.ORG

Office Open XML Formats: Retrieving Lists of Excel 2007 Worksheets

An introduction to Open XML SDK 2.0

You can modify the code according to your need and can refer to it for understanding about Open XML for EXCEL. If you have any doubts or queris regarding the code snippet, please mail me to nithinmohantk (at) nitrix-reloaded.com . I will help you the way i could, also if interested follow me on Twitter(@nithinmohantk), FaceBook(nithinmohantk)

Released: BizTalk Server 2010 – Developer Edition

September 24, 2010 All, BizTalk No comments

BizTalk Server 2010 is an integration server that provides connectivity, Business Process Management (BPM), and SOA/ESB capabilities. It also includes Business to Business (B2B) integration capabilities, Adapters, Accelerators, and an RFID platform.

BizTalk Server 2010 offers significant enhancements to help integrate heterogeneous Line-of-business systems with Windows .NET and SharePoint based applications to optimize user productivity, gain business efficiency and increase agility. BizTalk Server 2010 allows .Net developers to take advantage of BizTalk services right out of the box to rapidly build solutions that need to integrate transactions and data from applications like SAP, Mainframes, MS Dynamics, and Oracle. Similarly SharePoint developers can seamlessly use BizTalk services directly through the new Business Connectivity Services in SharePoint 2010.

BizTalk Server 2010 includes new data mapping & transformation tool to dramatically reduce the development time to mediate data exchange between disparate systems. It also provide a new single dashboard to manage performance parameters and streamline deployments from development to test to production.

BizTalk Server 2010 includes new, scalable Trading Partner Management (TPM) model with a graphical interface for flexible management of business partner relationships and efficient on-boarding process. BizTalk Server 2010 also provides extended connectivity to IBM systems with Host Integration Server.

Microsoft is providing developer edition download for developers.

Download BizTalk Server 2010 – Developer Edition

Released: Windows Phone Developer Tools RTW

September 18, 2010 .NET, All, Microsoft, Microsoft SDKs, VisualStudio, VS2010, Windows, Windows 7, Windows Phone SDK No comments

The powerful and easy to use Visual Studio integrated developer environment (IDE) handles developing Windows Phone 7 phone applications.

The Windows Phone Developer Tools includes the following

  • Visual Studio 2010 Express for Windows Phone
  • Windows Phone Emulator Resources
  • Silverlight 4 Tools For Visual Studio
  • XNA Game Studio 4.0
  • Microsoft Expression Blend for Windows Phone

Download Windows Phone Developer Tools RTW (Microsoft Download Center)

Released: Microsoft XNA Game Studio 4.0

September 18, 2010 .NET, All, Microsoft, Microsoft SDKs, XBOX, XBOX Development, XNA No comments

The XNA Framework Redistributable download provides game developers with the XNA Framework run-time libraries they can include with their product for redistribution on the Windows platform.
Microsoft XNA Game Studio 4.0

Microsoft XNA Game Studio 4.0 makes it easier than ever to create great video games for Windows-based PCs, Xbox 360 consoles, and Windows Phone.
XNA Game Studio 4.0 is a programming environment that allows you to use Visual Studio 2010 to create games for Windows Phone, the Xbox 360 console, and Windows-based computers. XNA Game Studio 4.0 includes the XNA Framework 4.0, which is a set of managed libraries designed for game development based on Microsoft .NET Framework 4.

New Version: Microsoft Silverlight 4 Tools for Visual Studio 2010 – Build 30319.352

September 3, 2010 .NET, All, Microsoft, Silverlight 1 comment

Microsoft Silverlight 4 Tools for Visual Studio 2010 is an Add-on and pre-requisite files for Visual Studio 2010 to develop Silverlight 4 and RIA Services applications.

Latest version of Microsoft Silverlight 4 Tools for Visual Studio 2010 has been released on 09/02/2010.

The quick file details are

File Name:  Silverlight4_Tools.exe

Version:      30319.352

Date Published: 9/2/2010

Language:   English

Download Size:  35.1 MB

This package is an add-on for Visual Studio 2010 to provide tooling for Microsoft Silverlight 4 and RIA Services. It can be installed on top of either Visual Studio 2010 or Visual Web Developer 2010 Express. It extends existing Silverlight 3 features and multitargeting capabilities in Visual Studio 2010 to also create applications for Silverlight 4 using C# or Visual Basic.

Here are the highlights of release:

  • SDK feature to enable Add New Row capabilities in DataGrid control
  • Improving startup performance of Silverlight applications
  • Adding mouse wheel support for out-of-browser applications on the Mac platform
  • Various media-related fixes around DRM content
  • Fixed memory leak when MouseCapture is used
  • Fixed memory leak for DataTemplate usage

Taken from Tim Heuer’s Blog  (Visit here for More information)

Download  Microsoft Silverlight 4 Tools for Visual Studio 2010 – Build 30319.352 ( Microsoft Download Center)

Released: Windows Phone 7 RTM

September 3, 2010 All, General, Microsoft, Windows, Windows Mobile, Windows Phone 1 comment

On 1st September 2010 Microsoft has announced the Windows Phone 7 RTM(Released-To-Manufacture), the most awaited Mobile OS from Microsoft. Just like Windows 7 it has already won the hearts of millions. Hope to win more..

You can read the official Windows Phone 7 team blog  Windows Phone 7 – Released To Manufacturing.

Quoting from Paul’s blog at WinSuperSite

Windows Phone 7 RTM

Well, the day has finally arrived. I was told very early on that Windows Phone would most likely be released to manufacturing (RTM) in August, and they missed that mark by just a day, which isn’t so horrible for a completely new platform. Anyway, the Windows Phone 7 OS has been finalized and sent to Microsoft’s hardware and carrier partners so that they can integrate their own software and services solutions and ship new devices to customers later in the year. No word yet on the launch, but I don’t believe the October/November plans have changed.

There are, however, some changes to the RTM version of the Windows Phone 7 OS, which are fortunately not too bad considering I pretty much finished the Windows Phone 7 Secrets book recently:

  • Facebook contacts filtering in the People hub, which isn’t actually what people have been asking for (i.e. the ability to decide which Facebook contacts appear and which do not). Instead, it’s that those Facebook contacts who don’t have phone information will be automatically filtered out of the list for you.
  • Facebook “Like” capability from the People hub. You can now “Like” a Facebook post from within the People hub’s What’s New list and post messages directly to someone’s Facebook wall.
  • Various user interface updates, including a new Search button in the contacts list.

Note that those with Tech Preview prototype phones will not be getting upgraded to the RTM build.