Reading EXCEL FILE in a Collection sing Open XML SDK 2.0

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 that post.

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

Create Entity class



Add namespace



Setting initial code as of Excel template

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



In the above code snippet, the 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? "  The answer is that, if you notice our excel file has four columns. So in our logic we need a string array of four. If you are working with an excel file with 10 columns then your string array should have a size of 10.



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

Open Excel file



If you have saved the template Excel file with a different name in a 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,



If you have not renamed the sheet and want to insert into the first sheet, you can do like below. Make note of the code in comments to fetch the first sheet.



Read Row by Row



In above code snippet

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

If all the cells contains numeric value then the above code is sufficient, 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 the string array as below,

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

Consolidating all together we can create a function to read from the 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;

                    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 a 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 that is required to read an excel file using the Open XML SDK. I hope this post was useful to you. Thanks for reading.

[twitter-follow screen_name='debug_mode' show_count='yes']

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