Dhananjay Kumar

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

You can download it from 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 below function returning list of bloggers. We are going to insert all the items from this list in 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;
}

&nbsp;

&nbsp;

You are very much free to change data source to

• Azure table
• SQL Server table
• SQL Azure table

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

Since now we have data source, let us insert the items of list in the excel file using open xml SDK.

Add Namespaces

You need to add below namespaces,

Have a Template

If you notice we have four properties in entity class. So there would 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 the

There are three points worth noticing about the template

1. All the columns [properties of entity class] is in 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 save template Excel file with different name in different location then you will have to change the location in 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 code in comment to fetch the first sheet.

 

Inserting the rows

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

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

Creating the rows

 

In you notice above that in 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 above snippet I am iterating through all the properties of the entity object and creating cell reference by appending index with column headers.

Next I need to find 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 all together all 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 Excel file and you should get the row inserted. 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.

 

Follow @debug_mode