Inserting in Excel FILE from C# Collection Using Open XML SDK 2.0

In this post I will show inserting rows in to an excel file from a c Sharp list using the Open XML SDK. The Open XML SDK is very useful when you don't want to or cannot use the Microsoft Office InterOP DLL. 

You can download it from the below URL

http://www.microsoft.com/download/en/details.aspx?id=5124

Once you download and run the MSI follow the below steps.

Creating Data Source to be inserted in excel

Let us say you have class as below,



And the below function returning a list of bloggers. We are going to insert all the items from this list into the Excel file.

private List<Bloggers> GetDataToInsertInExcel()
        {
            List<Bloggers> lstBloggers = new List<Bloggers>
                                         {
                                             new Bloggers
                                             {
                                                 Name = "Pinal Dave",
                                                 Interest = "SQL Server",
                                                 NumberofPosts = 1500,
                                                 Speaker = true
                                             },
                                             new Bloggers
                                             {
                                                 Name = "Mahesh Chand",
                                                 Interest = "C Sharp",
                                                 NumberofPosts = 1300,
                                                 Speaker = true
                                             },
                                             new Bloggers
                                             {
                                                 Name = "Debug Mode",
                                                 Interest = "all",
                                                 NumberofPosts = 400,
                                                 Speaker = false
                                             },
                                              new Bloggers
                                             {
                                                 Name = "Shiv Prasad Koirala",
                                                 Interest = "ASp.Net",
                                                 NumberofPosts = 500,
                                                 Speaker = true
                                             },
                                               new Bloggers
                                             {
                                                 Name = "Anoop Madusudhan",
                                                 Interest = "WCF",
                                                 NumberofPosts = 500,
                                                 Speaker = false
                                             },
                                         };
            return lstBloggers;
        }


You are very much free to change data source to

1. Azure table
2. SQL Server table
3. SQL Azure table

Theoretically you can use any data source provided you are converting the result into a List. If you are using SQL Server or SQL Azure, you can use LINQ to SQL to create a data source.

Since now we have a data source, let us insert the items of the list into the excel file using the Open XML SDK.

Add Namespaces

You need to add the below namespaces,

Have a Template

If you notice we have four properties in the entity class. So there will be four columns in the excel sheet. Save an excel file with any name of your preference at any location of your preference. For purpose of this article I am saving it to "d:\"



There are three points worth noticing about the template

1. All the columns [properties of entity class] are in the first row in columns A, B,C,D
2. Sheet is renamed to items. If you want you can have default name.
3. Template excel file with name testupload is in d drive.

Opening the template file to insert rows



If you have saved the template Excel file with different name in different location then you will have to change the location in the above code.

If you have changed the sheet name to item then you will fetch it as below,



If you have not renamed the sheet and want to insert in the first sheet, you can do like below. Make note of the code in the comments to fetch the first sheet.



Inserting the rows

Now the document is open, so we need to insert rows one by one. So we will loop through all the items in the list and call a function to create a row. On successful return of the row from the function we will append it to the open sheet.



If you notice in the above code snippet I have initialized the index value to 2 because in the  first row of the excel sheet, we are putting the header. From the second row onward items in each row would get inserted. I am making call to CreateContentRow function.

Creating the rows



If you notice, in the header columns string array, we are starting from A to D. It is because we have only four columns to insert. If you have 6 columns to insert then string array would be from A to F.

In the above snippet I am iterating through all the properties of the entity object and creating cell references by appending index with column headers.

Next I need to find the type of property .There may be three types

1. String
2. Integer
3. Boolean

We need to check for the type of property and then create the cell to insert the value.

Checking for String



Checking for Integer

Checking for Boolean



Putting together all the pieces of codes we discussed above, for your reference whole source code is as below,

using System;
using System.Collections.Generic;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace ConsoleApplication28
{
    class Program
    {
        static void Main(string[] args)
        {
             CreatingAndUploadingExcel();
        }
        Public static bool CreatingAndUploadingExcel()
        {                       
 
            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open("d:\\LocalCollection.xlsx", true))
            {
 

//WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

               WorkbookPart workbookPart = myWorkbook.WorkbookPart;

                                IEnumerable<Sheet> Sheets = myWorkbook.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s=>s.Name=="items");
                  if (Sheets.Count() == 0)
                  {               
                      // The specified worksheet does not exist.
                      return false;
                  }

                string relationshipId = Sheets.First().Id.Value; 
                WorksheetPart worksheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(relationshipId);
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                int index = 2;
                foreach (var entity in GetDataToInsertInExcel())
                {
 
                    Row contentRow = CreateContentRow(index, entity);
                    index++;
                    sheetData.AppendChild(contentRow);

                }

                workbookPart.Workbook.Save();

            }

        }
        string[] headerColumns = new string[] { "A", "B","C","D"};
        private Row CreateContentRow(int index, Bloggers objToInsert)
        {

            Row r = new Row ();
            r.RowIndex = (UInt32) index;
            int i = 0;

            foreach (var prop in objToInsert.GetType().GetProperties())
            {
                Cell c = new Cell();
                c.CellReference = headerColumns[i].ToString() + index;

                if (prop.PropertyType.ToString().Equals("System.string", StringComparison.InvariantCultureIgnoreCase))
                {
 
                    var result = prop.GetValue(objToInsert, null);

                    if (result == null)
                    {
                        result = "";
                    }

                    c.DataType = CellValues.String;
                    InlineString inlineString = new InlineString();
                    Text t = new Text();
                    t.Text = result.ToString();
                    inlineString.AppendChild(t);
                    c.AppendChild(inlineString);                                                         

                } 

                if (prop.PropertyType.ToString().Equals("System.int32", StringComparison.InvariantCultureIgnoreCase))
                {

                    var result = prop.GetValue(objToInsert, null);
                    if (result == null)
                    {
                        result = 0;
                    }

                    CellValue v = new CellValue();
                    v.Text = result.ToString();
                    c.AppendChild(v);

                }

                if (prop.PropertyType.ToString().Equals("System.boolean", StringComparison.InvariantCultureIgnoreCase))
                { 
                    var result = prop.GetValue(objToInsert, null);
                    if (result == null)
                    {
                        result = "False";
                    }
                        c.DataType = CellValues.InlineString;
                        InlineString inlineString = new InlineString();
                        Text t = new Text();
                        t.Text = result.ToString();
                        inlineString.AppendChild(t);
                        c.AppendChild(inlineString);  
                 }                  
               
                r.AppendChild(c);
                i = i + 1;
            }

            return r;

        }
        private List<Bloggers> GetDataToInsertInExcel()
        {
            List<Bloggers> lstBloggers = new List<Bloggers>
                                         {
                                             new Bloggers
                                             {
                                                 Name = "Pinal Dave",
                                                 Interest = "SQL Server",
                                                 NumberofPosts = 1500,
                                                 Speaker = true
                                             },
                                             new Bloggers
                                             {
                                                 Name = "Mahesh Chand",
                                                 Interest = "C Sharp",
                                                 NumberofPosts = 1300,
                                                 Speaker = true
                                             },
                                             new Bloggers
                                             {
                                                 Name = "Debug Mode",
                                                 Interest = "all",
                                                 NumberofPosts = 400,
                                                 Speaker = false
                                             },
                                              new Bloggers
                                             {
                                                 Name = "Shiv Prasad Koirala",
                                                 Interest = "ASp.Net",
                                                 NumberofPosts = 500,
                                                 Speaker = true
                                             },
                                               new Bloggers
                                             {
                                                 Name = "Anoop Madusudhan",
                                                 Interest = "WCF",
                                                 NumberofPosts = 500,
                                                 Speaker = false
                                             },
                                         };
            return lstBloggers;
        }
 
        }         

    }

  public class Bloggers
    {
        public string Name { get; set; }
        public string Interest { get; set; }
        public int NumberofPosts { get; set; }
        public bool Speaker { get; set; }
    }

}

Now go ahead and open the Excel file and you should see the inserted rows(s). I hope this post was useful. Thanks for reading.

If you find my posts useful you may like to follow me on twitter http://twitter.com/debug_mode or may like Facebook page of my blog http://www.facebook.com/DebugMode.Net If you want to see post on a particular topic please do write on FB page or tweet me about that, I would love to help you.