Read Excel cell values of specific columns using open xml sdk in c#

Question

I have these data in an Excel file and I am able to read the entire file using Open XML and store it in an ArrayList.

Acme Auto Dealer                    

ProductID   Model       Type    Color   MaxSpeed    Manufacturer
10-01       Fortuner    SUV     Black       200     Toyota
10-02       Innova      MPV     Red         200     Toyota
10-03       Altis       Car     White       200     Toyota
10-04       Land Cruiser        SUV Red     200     Toyota
10-05       Vios        Car     Black       200     Toyota
10-06       Avanza      MPV     White       200     Toyota
10-07       Wigo        Car     Grey        200     Toyota
10-08       Rush        SUV     Red         200     Toyota
10-09       Alphard     Van     Black       200     Toyota
10-10       Hiace       Van     Black       200     Toyota
10-11       Hilux       Truck   Red         200     Toyota
10-12       City        Car     White       200     Honda
10-13       Brio        Car     Red         200     Honda
10-14       Civic       Car     Black       200     Honda
10-15       Jazz        Car     White       200     Honda
10-16       Accord      Car     Grey        200     Honda
10-17       Mobilio     MPV     Grey        200     Honda
10-18       CR-V        SUV     Black       200     Honda
10-19       Odyssey     Van     Red         200     Honda
10-20       Ranger      Truck   White       200     Ford
10-21       Expedition  SUV     Red         200     Ford
10-22       Explorer    SUV     Black       200     Ford
10-23       Everest     SUV     White       200     Ford
10-24       Fiesta      Car     Grey        200     Ford
10-25       Focus       Car     White       200     Ford

Here is the code:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;


namespace OpenXMLSDK
{
    public class Program
    {
        static void Main(string[] args)
        {
            var fileName = @"C:\XML\Vehicles.xlsx";
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

                ArrayList data = new ArrayList();
                foreach (Row r in sheetData.Elements<Row>())
                {
                    foreach (Cell c in r.Elements<Cell>())
                    {
                        if (c.DataType != null && c.DataType == CellValues.SharedString)
                        {
                            var stringId = Convert.ToInt32(c.InnerText);
                            data.Add(workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(stringId).InnerText);
                        }   
                        else if (c.InnerText != null || c.InnerText != string.Empty)
                        {
                            data.Add(c.InnerText);
                        }


                    }
                }
                Console.ReadKey();
            }                
        }
    }
}

Now I want to extract the values from specific columns and store these values in a dictionary.. Dictionary<string, List<KeyValuePair<string, string>>>

A sample output will be something like this:

10-01 List(Model=Fortuner, Type=SUV, MaxSpeed=200, Manufacturer=Toyota)
10-02 List(Model=Innova, Type=MPV, MaxSpeed=200, Manufacturer=Toyota)
10-03 List(Model=Altis, Type=Car, MaxSpeed=200, Manufacturer=Toyota)
10-04 List(Model=Land Cruiser, Type=SUV, MaxSpeed=200, Manufacturer=Toyota)
10-05 List(Model=Vios, Type=Car, MaxSpeed=200, Manufacturer=Toyota)
10-06 List(Model=Avanza, Type=MPV, MaxSpeed=200, Manufacturer=Toyota)
10-07 List(Model=Wigo, Type=Car, MaxSpeed=200, Manufacturer=Toyota)
10-08 List(Model=Rush, Type=SUV, MaxSpeed=200, Manufacturer=Toyota)
10-09 List(Model=Alphard, Type=Van, MaxSpeed=200, Manufacturer=Toyota)
10-10 List(Model=Hiace, Type=Van, MaxSpeed=200, Manufacturer=Toyota)
10-11 List(Model=Hilux, Type=Truck, MaxSpeed=200, Manufacturer=Toyota)
10-12 List(Model=City, Type=Car, MaxSpeed=200, Manufacturer=Honda)
10-13 List(Model=Brio, Type=Car, MaxSpeed=200, Manufacturer=Honda)
10-14 List(Model=Civic, Type=Car, MaxSpeed=200, Manufacturer=Honda)
10-15 List(Model=Jazz, Type=Car, MaxSpeed=200, Manufacturer=Honda)
10-16 List(Model=Accord, Type=Car, MaxSpeed=200, Manufacturer=Honda)
10-17 List(Model=Mobilio, Type=MPV, MaxSpeed=200, Manufacturer=Honda)
10-18 List(Model=CR-V, Type=SUV, MaxSpeed=200, Manufacturer=Honda)
10-19 List(Model=Odyssey, Type=Van, MaxSpeed=200, Manufacturer=Honda)
10-20 List(Model=Ranger, Type=Truck, MaxSpeed=200, Manufacturer=Ford)
10-21 List(Model=Expedition, Type=SUV, MaxSpeed=200, Manufacturer=Ford)
10-22 List(Model=Explorer, Type= SUV, MaxSpeed=200, Manufacturer=Ford)
10-23 List(Model=Everest, Type= SUV, MaxSpeed=200, Manufacturer=Ford)
10-24 List(Model=Fiesta, Type=Car, MaxSpeed=200, Manufacturer=Ford)
10-25 List(Model=Focus, Type=Car, MaxSpeed=200, Manufacturer=Ford)

I've been searching around for a while now but can't find out how to do this. Any help is appreciated. Thanks in advance.

it seems you List contains multiple values but keyvaluepair contains only key and value so you need a class object to store your Model, Type, MaxSpeed, Manufacturer — er-sho
– er-sho, Commented Oct 4, 2018 at 13:13
Does your code really work or is my test .xlsx file incorrect? It always returns 0 rows. Do you have to use openxml-sdk or is a wrapper, like EPPlus, allowed? — FrankM
– FrankM, Commented Oct 4, 2018 at 13:22
oh sorry.. the dictionary key should be ProductIDs and they are unique... — klee
– klee, Commented Oct 4, 2018 at 13:50
yes, it works.. i used openxml-sdk.. i was also looking at EPPlus but haven't tried it yet. — klee
– klee, Commented Oct 4, 2018 at 13:53

er-sho · Accepted Answer · 2018-10-05 06:43:48Z

From Below method you can map your excel sheet data to Dictionary<string, List<KeyValuePair<string, string>>>.

public void MapExcelToDictionary()
        {
            var fileName = @"C:\XML\Vehicles.xlsx";
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;

                //Get sheet from excel
                var sheets = workbookPart.Workbook.Descendants<Sheet>();

                //First sheet from excel
                Sheet sheet = sheets.FirstOrDefault();

                var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
                var rows = worksheetPart.Worksheet.Descendants<Row>().ToList();

                //Get all data rows from sheet
                Row headerRow = rows.First();
                var headerCells = headerRow.Elements<Cell>();
                int totalColumns = headerCells.Count();


                List<string> lstHeaders = new List<string>();
                foreach (var value in headerCells)
                {
                    var stringId = Convert.ToInt32(value.InnerText);
                    lstHeaders.Add(workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(stringId).InnerText);
                }

                // Remove the header row
                rows.RemoveAt(0);

               //Dictionary to map row data into key value pair
                Dictionary<string, List<KeyValuePair<string, string>>> dict = new Dictionary<string, List<KeyValuePair<string, string>>>();

                var productID = string.Empty;

                //Iterate to all rows
                foreach (Row r in rows)
                {
                    List<KeyValuePair<string, string>> keyValuePairs = new List<KeyValuePair<string, string>>();

                    //Iterate to all cell in current row
                    foreach (Cell c in r.Elements<Cell>())
                    {
                        if (c.DataType != null && c.DataType == CellValues.SharedString)
                        {
                            var stringId = Convert.ToInt32(c.InnerText);
                            string val = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(stringId).InnerText;

                            //Find cell index and map each cell and add in key value pair
                            switch (GetColumnIndex(c.CellReference))
                            {
                                case 1:
                                    productID = val;
                                    break;

                                case 2:
                                    keyValuePairs.Add(new KeyValuePair<string, string>("Model", val));
                                    break;

                                case 3:
                                    keyValuePairs.Add(new KeyValuePair<string, string>("Type", val));
                                    break;

                                case 4:
                                    keyValuePairs.Add(new KeyValuePair<string, string>("Color", val));
                                    break;

                                case 5:
                                    keyValuePairs.Add(new KeyValuePair<string, string>("MaSpeed", val));
                                    break;

                                case 6:
                                    keyValuePairs.Add(new KeyValuePair<string, string>("Manufacturer", val));
                                    break;
                            }

                        }
                        else if (c.InnerText != null || c.InnerText != string.Empty)
                        {
                            //Do code here
                        }
                    }

                    //Add productId and its repsective data to dictionary
                    dict.Add(productID, keyValuePairs);
                }

                Console.ReadKey();
            }
        }

And below method can find column index from cell reference in excel sheet.

private static int? GetColumnIndex(string cellReference)
        {
            if (string.IsNullOrEmpty(cellReference))
            {
                return null;
            }

            string columnReference = Regex.Replace(cellReference.ToUpper(), @"[\d]", string.Empty);

            int columnNumber = -1;
            int mulitplier = 1;

            foreach (char c in columnReference.ToCharArray().Reverse())
            {
                columnNumber += mulitplier * ((int)c - 64);

                mulitplier = mulitplier * 26;
            }

            return columnNumber + 1;
        }

@klee, If answer help you then mark tick on left side of answer to make it green :)

Collectives™ on Stack Overflow

Read Excel cell values of specific columns using open xml sdk in c#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related