How to Get the Specific Column in Excel Worksheet Using Documentformat.Openxml C#

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

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;
        }

Get the Column Index of a Cell in Excel using OpenXML C#

This is slightly trickier than you might imagine because the schema allows for empty cells to be omitted.

To get the index you can use the Cell object wihch has a CellReference property that gives the reference in the format A1, B1 etc. You can use that reference to extract the column number.

As you probably know, in Excel A = 1, B = 2 etc up to Z = 26 at which point the cells are prefixed with A to give AA = 27, AB = 28 etc. Note that in the case of AA the first A has a value of 26 times the second; i.e. it is "worth" 26 whilst the second A is "worth" 1 giving a total of 27.

To work out the column index you can reverse the letters then take the value of the first letter and add it to a running total. Then take the value of the second letter and multiply it by 26, adding the total to the first number. For the third you multiply it by 26 twice and add it, for the fourth multiply it by 26 3 times and so on.

So for column ABC you would do:

C = 3
B = 2 * 26 = 52
A = 1 * 26 *26 = 676
3 + 52 + 676 = 731

In C# the following will work:

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

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

    int columnNumber = -1;
    int mulitplier = 1;

    //working from the end of the letters take the ASCII code less 64 (so A = 1, B =2...etc)
    //then multiply that number by our multiplier (which starts at 1)
    //multiply our multiplier by 26 as there are 26 letters
    foreach (char c in columnReference.ToCharArray().Reverse())
    {
        columnNumber += mulitplier * ((int)c - 64);

        mulitplier = mulitplier * 26;
    }

    //the result is zero based so return columnnumber + 1 for a 1 based answer
    //this will match Excel's COLUMN function
    return columnNumber + 1;
}

Note that the CellReference is not guaranteed to be in the XML either (although I've never seen it not there). In the case where the CellReference is null the cell is placed in the leftmost available cell. The RowIndex is also not mandatory in the spec so it too can be omitted in which case the cell is placed in the highest row available. More information can be seen in this question. The answer from @BCdotWEB is correct approach in cases where the CellReference is null.

Writing to Excel with Open Xml gives issue after certain columns

Your ordering is broken in CreateCell for cell references after Z. You are currently using string.Compare but that will do an alpha comparison meaning AA1 is before Z1 rather than after it.

There are various ways you could fix this - one way would be to convert the cellReference to a column index and then compare those instead of comparing the cell references directly. For example:

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

    cellRef = cellRef.ToUpper();

    int columnIndex = -1;
    int mulitplier = 1;

    foreach (char c in cellRef.ToCharArray().Reverse())
    {
        if (char.IsLetter(c))
        {
            columnIndex += mulitplier * ((int)c - 64);
            mulitplier = mulitplier * 26;
        }
    }

    return columnIndex;
}

Then instead of

if (string.Compare(cell.CellReference.Value, address, true) > 0)
{
    refCell = cell;
    break;
}

You can do

if (GetColumnIndex(cell.CellReference.Value) > GetColumnIndex(address))
{
    refCell = cell;
    break;
}

How to read file excel row and sheet specific using openXML C# into data table

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

        private void readExcel(Stream file)
        {
            String sheetName = "Sheet2";
            String delimiter = ";";
            int startColumn = 2;// 2 convert to B 
            int endColumn = 6; // read until column 6
            int startRow = 31; // start read from row 31

            String columnRequest = "Request";
            DataTable dt = new DataTable();
            dt.Columns.Add(columnRequest);
            DataRow dr;
            String stringRequest = "";
            String stringNopek = "Init";
            String value = "";
            int indexRow = 0;
            using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(file, false))
            {
                WorkbookPart wbPart = myDoc.WorkbookPart;

                indexRow = startRow;
                while (!stringNopek.Equals(""))
                {
                    stringNopek = getCellValue(GetExcelColumnName(startColumn) + indexRow.ToString(), sheetName, wbPart).Trim();
                    stringRequest = stringNopek;
                    if (!stringNopek.Equals(""))
                    {
                        dr = dt.NewRow();
                        for (int i = startColumn + 1; i <= endColumn; i++)
                        {
                            value = getCellValue(GetExcelColumnName(i) + indexRow.ToString(), sheetName, wbPart).Trim();
                            stringRequest += delimiter + value;
                        }
                        dr[columnRequest] = stringRequest;
                        dt.Rows.Add(dr);
                    }
                    indexRow++;
                }
            }

            Session["DataTableRequest"] = dt;

            string output = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                output = output + dt.Rows[i][columnRequest].ToString();
                output += (i < dt.Rows.Count) ? Environment.NewLine : string.Empty;
            }

        }

private string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

 private int ColumnIndex(string reference)
        {
            int ci = 0;
            reference = reference.ToUpper();
            for (int ix = 0; ix < reference.Length && reference[ix] >= 'A'; ix++)
                ci = (ci * 26) + ((int)reference[ix] - 64);
            return ci;
        }

private String getCellValue(String cellReference, String sheetName, WorkbookPart wbPart)
        {
            Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
              Where(s => s.Name == sheetName).FirstOrDefault();
            if (theSheet == null)
            {
                throw new ArgumentException(sheetName);
            }
            WorksheetPart wsPart =
                (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
            Cell theCell = wsPart.Worksheet.Descendants<Cell>().
              Where(c => c.CellReference == cellReference).FirstOrDefault();

            String value = "";

            if (theCell != null)
            {
                if (theCell.CellValue != null)
                {
                    value = theCell.CellValue.Text;
                }
                else
                {
                    value = value = theCell.InnerText;
                }
                if (theCell.DataType != null)
                {
                    switch (theCell.DataType.Value)
                    {
                        case CellValues.SharedString:

                            var stringTable =
                                wbPart.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;
                    }
                }
            }

            return value;
        }

 protected void UploadControl_FileUploadComplete(object sender, FileUploadCompleteEventArgs e){
 Stream strm = e.UploadedFile.FileContent;
readExcel(strm);
}

---

Related Topics