Sunday, 22 January 2012

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,
image
And below function returning list of bloggers. We are going to insert all the items from this list in Excel file.
01private List<Bloggers> GetDataToInsertInExcel()
02{
03List<Bloggers> lstBloggers = new List<Bloggers>
04{
05new Bloggers
06{
07Name = "Pinal Dave",
08Interest = "SQL Server",
09NumberofPosts = 1500,
10Speaker = true
11},
12new Bloggers
13{
14Name = "Mahesh Chand",
15Interest = "C Sharp",
16NumberofPosts = 1300,
17Speaker = true
18},
19new Bloggers
20{
21Name = "Debug Mode",
22Interest = "all",
23NumberofPosts = 400,
24Speaker = false
25},
26new Bloggers
27{
28Name = "Shiv Prasad Koirala",
29Interest = "ASp.Net",
30NumberofPosts = 500,
31Speaker = true
32},
33new Bloggers
34{
35Name = "Anoop Madusudhan",
36Interest = "WCF",
37NumberofPosts = 500,
38Speaker = false
39},
40};
41return lstBloggers;
42}
43 
44&nbsp;
45 
46&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,
image
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
image
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

image
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,

image

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.

image
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.
image
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

image
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
image
Checking for Integer

image
Checking for Boolean
image
Putting all together all pieces of codes we discussed above, for your reference whole source code is as below,
001using System;
002using System.Collections.Generic;
003using DocumentFormat.OpenXml.Packaging;
004using DocumentFormat.OpenXml.Spreadsheet;
005 
006namespace ConsoleApplication28
007{
008class Program
009{
010static void Main(string[] args)
011{
012CreatingAndUploadingExcel();
013}
014Public static bool CreatingAndUploadingExcel()
015{
016 
017using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open("d:\\LocalCollection.xlsx", true))
018{
019//WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
020 
021WorkbookPart workbookPart = myWorkbook.WorkbookPart;
022 
023IEnumerable<Sheet> Sheets = myWorkbook.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s=>s.Name=="items");
024if (Sheets.Count() == 0)
025{
026// The specified worksheet does not exist.
027return false;
028}
029 
030string relationshipId = Sheets.First().Id.Value;
031WorksheetPart worksheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(relationshipId);
032SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
033 
034int index = 2;
035foreach (var entity in GetDataToInsertInExcel())
036{
037 
038Row contentRow = CreateContentRow(index, entity);
039index++;
040sheetData.AppendChild(contentRow);
041}
042 
043workbookPart.Workbook.Save();
044 
045}
046 
047}
048string[] headerColumns = new string[] { "A", "B","C","D"};
049private Row CreateContentRow(int index, Bloggers objToInsert)
050{
051 
052Row r = new Row ();
053r.RowIndex = (UInt32) index;
054int i = 0;
055 
056foreach (var prop in objToInsert.GetType().GetProperties())
057{
058Cell c = new Cell();
059c.CellReference = headerColumns[i].ToString() + index;
060 
061if (prop.PropertyType.ToString().Equals("System.string", StringComparison.InvariantCultureIgnoreCase))
062{
063 
064var result = prop.GetValue(objToInsert, null);
065 
066if (result == null)
067{
068result = "";
069}
070 
071c.DataType = CellValues.String;
072InlineString inlineString = new InlineString();
073Text t = new Text();
074t.Text = result.ToString();
075inlineString.AppendChild(t);
076c.AppendChild(inlineString);
077 
078}
079 
080if (prop.PropertyType.ToString().Equals("System.int32", StringComparison.InvariantCultureIgnoreCase))
081{
082 
083var result = prop.GetValue(objToInsert, null);
084if (result == null)
085{
086result = 0;
087}
088 
089CellValue v = new CellValue();
090v.Text = result.ToString();
091c.AppendChild(v);
092 
093}
094 
095if (prop.PropertyType.ToString().Equals("System.boolean", StringComparison.InvariantCultureIgnoreCase))
096{
097 
098var result = prop.GetValue(objToInsert, null);
099if (result == null)
100{
101result = "False";
102}
103c.DataType = CellValues.InlineString;
104InlineString inlineString = new InlineString();
105Text t = new Text();
106t.Text = result.ToString();
107inlineString.AppendChild(t);
108c.AppendChild(inlineString);
109 
110}
111 
112 
113r.AppendChild(c);
114i = i + 1;
115}
116 
117return r;
118 
119}
120private List<Bloggers> GetDataToInsertInExcel()
121{
122List<Bloggers> lstBloggers = new List<Bloggers>
123{
124new Bloggers
125{
126Name = "Pinal Dave",
127Interest = "SQL Server",
128NumberofPosts = 1500,
129Speaker = true
130},
131new Bloggers
132{
133Name = "Mahesh Chand",
134Interest = "C Sharp",
135NumberofPosts = 1300,
136Speaker = true
137},
138new Bloggers
139{
140Name = "Debug Mode",
141Interest = "all",
142NumberofPosts = 400,
143Speaker = false
144},
145new Bloggers
146{
147Name = "Shiv Prasad Koirala",
148Interest = "ASp.Net",
149NumberofPosts = 500,
150Speaker = true
151},
152new Bloggers
153{
154Name = "Anoop Madusudhan",
155Interest = "WCF",
156NumberofPosts = 500,
157Speaker = false
158},
159};
160return lstBloggers;
161}
162 
163}
164 
165}
166 
167public class Bloggers
168{
169public string Name { get; set; }
170public string Interest { get; set; }
171public int NumberofPosts { get; set; }
172public 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 October 4, 2011  Smile
Post a Comment