Creating Documents by Using the Open XML Format SDK 2.0 in VS2012

Step 1: Create one console application and name it ‘OpenXmlUseExcel’ as shown in image.

Step 2: Download Open xml SDK 2.0  from here

Step 3: Add Reference of ‘DocumentFormat.OpenXml’ as shown in following screenshot. If .dll is not visible in reference manger please close and open the application and try again.

Step 4: If the following error occurs while reading the excel file, Please add ‘WindowBase Reference             as shown in the screen shot.

Step 5: Following is the details code to read and write the existing excel file.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using DocumentFormat.OpenXml;

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

namespace OpenXmlUseExcel

{

    class Program

    {

        static public WorksheetPart GetWorksheetPart(WorkbookPart workbookPart, string sheetName)

        {

            string relId = workbookPart.Workbook.Descendants<Sheet>().First(s => sheetName.Equals(s.Name)).Id;

            return (WorksheetPart)workbookPart.GetPartById(relId);

        }

        static void Main(string[] args)

        {

            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(@"E:\OpenXmlUseExcel\Book1.xlsx", true))

            {

                //Access the main Workbook part, which contains all references.

                WorkbookPart workbookPart = myWorkbook.WorkbookPart;

                //Get the first worksheet.

                //WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                
                //Get the worksheet by Name.
                WorksheetPart worksheetPart = GetWorksheetPart(workbookPart, "Sheet2");

                // The SheetData object will contain all the data.

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

                //Connect to database.

                NEWDBEntities db = new NEWDBEntities();

                //The data starts at row 2.

                int index = 2;

                //Select all rows from SalesTerritory table.

                var territoryQuery = from t in db.CITies select t;

                //For each row in the database, add a row to they spreadsheet.

                foreach (var item in territoryQuery)

                {

                    string NAME = item.NAME;

                    int ID = item.ID;

                    int? CONTRYID = item.CONTRYID;

                    //Add a new row.

                    Row contentRow = CreateContentRow(index, NAME, ID, CONTRYID);

                    index++;

                    //Append new row to sheet data.

                    sheetData.AppendChild(contentRow);

                }

            }

        }

        static string[] headerColumns = new string[] { "A", "B", "C" };

       static Row CreateContentRow(int index, string territory, int salesLastYear, int? salesThisYear)

        {

            //Create the new row.

            Row r = new Row();

            r.RowIndex = (UInt32)index;

            //First cell is a text cell, so create it and append it.

            Cell firstCell = CreateTextCell(headerColumns[0], territory, index);

            r.AppendChild(firstCell);

            //Create the cells that contain the data.

            for (int i = 1; i < headerColumns.Length; i++)

            {

                Cell c = new Cell();

                c.CellReference = headerColumns[i] + index;

                CellValue v = new CellValue();

                if (i == 1)

                    v.Text = salesLastYear.ToString();

                else

                    v.Text = salesThisYear.ToString();

                c.AppendChild(v);

                r.AppendChild(c);

            }

            return r;

        }

       static Cell CreateTextCell(string header, string text, int index)

        {

            //Create a new inline string cell.

            Cell c = new Cell();

            c.DataType = CellValues.InlineString;

            c.CellReference = header + index;

            //Add text to the text cell.

            InlineString inlineString = new InlineString();

            Text t = new Text();

            t.Text = text;

            inlineString.AppendChild(t);

            c.AppendChild(inlineString);

            return c;

        }

    }

}