Inserting in Excel file from C# collection using Open XML

Inserting in Excel file from C# collection using Open XML SDK 2.0

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.

view sourceprint?

01	private List<Bloggers> GetDataToInsertInExcel()

02

{

03	List<Bloggers> lstBloggers = new List<Bloggers>

04

{

05	new Bloggers

06

{

07	Name = "Pinal Dave",

08	Interest = "SQL Server",

09	NumberofPosts = 1500,

10	Speaker = true

11

},

12	new Bloggers

13

{

14	Name = "Mahesh Chand",

15	Interest = "C Sharp",

16	NumberofPosts = 1300,

17	Speaker = true

18

},

19	new Bloggers

20

{

21	Name = "Debug Mode",

22	Interest = "all",

23	NumberofPosts = 400,

24	Speaker = false

25

},

26	new Bloggers

27

{

28	Name = "Shiv Prasad Koirala",

29	Interest = "ASp.Net",

30	NumberofPosts = 500,

31	Speaker = true

32

},

33	new Bloggers

34

{

35	Name = "Anoop Madusudhan",

36	Interest = "WCF",

37	NumberofPosts = 500,

38	Speaker = false

39

},

40

};

41	return lstBloggers;

42

}

43

44

 

45

46

 

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,

view sourceprint?

001	using System;

002	using System.Collections.Generic;

003	using DocumentFormat.OpenXml.Packaging;

004	using DocumentFormat.OpenXml.Spreadsheet;

005

006	namespace ConsoleApplication28

007

{

008	class Program

009

{

010	static void Main(string[] args)

011

{

012	CreatingAndUploadingExcel();

013

}

014	Public static bool CreatingAndUploadingExcel()

015

{

016

017	using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open("d:\\LocalCollection.xlsx", true))

018

{

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

020

021	WorkbookPart workbookPart = myWorkbook.WorkbookPart;

022

023	IEnumerable<Sheet> Sheets = myWorkbook.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s=>s.Name=="items");

024	if (Sheets.Count() == 0)

025

{

026	// The specified worksheet does not exist.

027	return false;

028

}

029

030	string relationshipId = Sheets.First().Id.Value;

031	WorksheetPart worksheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(relationshipId);

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

033

034	int index = 2;

035	foreach (var entity in GetDataToInsertInExcel())

036

{

037

038	Row contentRow = CreateContentRow(index, entity);

039

index++;

040	sheetData.AppendChild(contentRow);

041

}

042

043	workbookPart.Workbook.Save();

044

045

}

046

047

}

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

049	private Row CreateContentRow(int index, Bloggers objToInsert)

050

{

051

052	Row r = new Row ();

053	r.RowIndex = (UInt32) index;

054	int i = 0;

055

056	foreach (var prop in objToInsert.GetType().GetProperties())

057

{

058	Cell c = new Cell();

059	c.CellReference = headerColumns[i].ToString() + index;

060

061	if (prop.PropertyType.ToString().Equals("System.string", StringComparison.InvariantCultureIgnoreCase))

062

{

063

064	var result = prop.GetValue(objToInsert, null);

065

066	if (result == null)

067

{

068	result = "";

069

}

070

071	c.DataType = CellValues.String;

072	InlineString inlineString = new InlineString();

073	Text t = new Text();

074	t.Text = result.ToString();

075	inlineString.AppendChild(t);

076	c.AppendChild(inlineString);

077

078

}

079

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

081

{

082

083	var result = prop.GetValue(objToInsert, null);

084	if (result == null)

085

{

086	result = 0;

087

}

088

089	CellValue v = new CellValue();

090	v.Text = result.ToString();

091	c.AppendChild(v);

092

093

}

094

095	if (prop.PropertyType.ToString().Equals("System.boolean", StringComparison.InvariantCultureIgnoreCase))

096

{

097

098	var result = prop.GetValue(objToInsert, null);

099	if (result == null)

100

{

101	result = "False";

102

}

103	c.DataType = CellValues.InlineString;

104	InlineString inlineString = new InlineString();

105	Text t = new Text();

106	t.Text = result.ToString();

107	inlineString.AppendChild(t);

108	c.AppendChild(inlineString);

109

110

}

111

112

113	r.AppendChild(c);

114	i = i + 1;

115

}

116

117

return r;

118

119

}

120	private List<Bloggers> GetDataToInsertInExcel()

121

{

122	List<Bloggers> lstBloggers = new List<Bloggers>

123

{

124	new Bloggers

125

{

126	Name = "Pinal Dave",

127	Interest = "SQL Server",

128	NumberofPosts = 1500,

129	Speaker = true

130

},

131	new Bloggers

132

{

133	Name = "Mahesh Chand",

134	Interest = "C Sharp",

135	NumberofPosts = 1300,

136	Speaker = true

137

},

138	new Bloggers

139

{

140	Name = "Debug Mode",

141	Interest = "all",

142	NumberofPosts = 400,

143	Speaker = false

144

},

145	new Bloggers

146

{

147	Name = "Shiv Prasad Koirala",

148	Interest = "ASp.Net",

149	NumberofPosts = 500,

150	Speaker = true

151

},

152	new Bloggers

153

{

154	Name = "Anoop Madusudhan",

155	Interest = "WCF",

156	NumberofPosts = 500,

157	Speaker = false

158

},

159

};

160	return lstBloggers;

161

}

162

163

}

164

165

}

166

167	public class Bloggers

168

{

169	public string Name { get; set; }

170	public string Interest { get; set; }

171	public int NumberofPosts { get; set; }

172	public bool Speaker { get; set; }

173

}

174

175

}

Now go ahead and open Excel file and you should get the row inserted. I hope this post was useful. Thanks for reading






Posted by Dhananjay Kumar ⋅ October 4, 2011