In my previous article I talked about Inserting in Excel file from C# collection using Open XML SDK 2.0 before you go ahead, I strongly recommend you to read this post.

In this post, let us try to do the reverse process. We will read all the rows of Excel file List of Bloggers. Again start with creating the custom class

Create Entity class

image

Add namespace

image

Setting initial code as of Excel template

I will walkthrough line by line of codes to make you easier for you to get a sense of the code.

 

clip_image001

In above code snippet last three lines should appear easier to you. However in first line of code you might be thinking, “Hey why string array of length 4? “ Answer of this that, if you notice our excel file it is having four columns. So in our logic we need a string array of four. If you are working with excel file with columns 10 then your string array should of size 10.

clip_image002

Then I have list of bloggers and each blogger object will represent a row of the excel file.

 

Open Excel file

 

 

clip_image003

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,

clip_image005

 

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.

 

clip_image007

Read Row by Row

clip_image008

In above code snippet

  1. We are iterating through all the rows in open sheet.
  2. Iterating through all the cells on selected rows.
  3. There is a check because we don’t want to read first row. Since first row of the excel will contain headers not the real data.

If all the cells contains numeric value then above code is suffice but there may be string or Boolean values as well. So we need to check that also as below,

Checking for string cell values

 

clip_image010

Checking for Boolean cell values

clip_image011

Set the value in string array as below,

clip_image012

Now we need to create object of Bloggers class by values from string array.

clip_image013

Consolidating all together we can create a function to read excel file


static List<Bloggers> ReadExcelFileDOM(string filename)
{

string[] strProperties = new string[4];
List<Bloggers> lstBloggers = new List<Bloggers>();
Bloggers facet = null;
int j = 0;

using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
IEnumerable<Sheet> Sheets = myDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == "items");
if (Sheets.Count() == 0)
{
}
string relationshipId = Sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)myDoc.WorkbookPart.GetPartById(relationshipId);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

List<string> lstRow = new List<string>();

int i = 1;
string value;
foreach (Row r in sheetData.Elements<Row>())
{
if (i != 1)
{
foreach (Cell c in r.Elements<Cell>())
{
if (c != null)
{
value = c.InnerText;

if (c.DataType != null)
{
switch (c.DataType.Value)
{
case CellValues.SharedString:
var stringTable = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (stringTable != null)
{
value = stringTable.SharedStringTable.
ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}

strProperties[j] = value;
j = j + 1;
}

}
}
j = 0;
&nbsp;

i = i + 1;
facet = new Bloggers();
facet.Name = strProperties[0];
facet.Intrest = strProperties[1];
facet.NumberofPosts = strProperties[2];
facet.Speaker = strProperties[3];
lstFacetToInsert.Add(facet);
}

return lstFacetToInsert;
}

}

And you can make function call as below,


List<SCDataFacets> result =  ReadExcelFileDOM("D:\\MyExcel.xlsx");

foreach (var a in result)
{

Console.WriteLine(a.Name);

}
Console.ReadKey(true);

This was all required to read a excel file using Open XML SDK. I hope this post was useful to you. Thanks for reading Smile

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

Advertisements

2 thoughts on “Reading EXCEL FILE in a collection using Open XML SDK 2.0

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s