Dhananjay Kumar

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

Add namespace

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.

 

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.

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

 

Open Excel file

 

 

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.

 

Read Row by Row

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

 

Checking for Boolean cell values

Set the value in string array as below,

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

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

Follow @debug_mode

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