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,
![IExlXML1.gif]()
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
- Azure table
- SQL Server table
- 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,
![IExlXML2.gif]()
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:\"
![IExlXML3.gif]()
There are three points worth noticing about the template
- All the columns [properties of entity class] are in the first row in columns A, B,C,D
- Sheet is renamed to items. If you want you can have default name.
- Template excel file with name testupload is in d drive.
Opening the template file to insert rows
![IExlXML4.gif]()
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,
![IExlXML5.gif]()
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.
![IExlXML6.gif]()
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.
![IExlXML7.gif]()
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
![IExlXML8.gif]()
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
- String
- Integer
- Boolean
We need to check for the type of property and
then create the cell to insert the value.
Checking for String
![IExlXML9.gif]()
Checking for Integer
![IExlXML10.gif]()
Checking for Boolean
![IExlXML11.gif]()
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.