locked
How to read a particular cell from Excell Sheet RRS feed

  • Question

  • User-1727942399 posted

    Hi,

    How to get a particular cell value from excell sheet. Can some one help with this please..  using c#.net

     

    Thursday, September 27, 2012 5:29 AM

Answers

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 27, 2012 7:00 AM
  • User1778637605 posted
    Hi, 
    Please try below code.

    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(documentPath, true)) { int rowcount = 0; var sheet = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().First(); var worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id.Value); IEnumerable<Row> rows = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>(); string textval = string.Empty; xdocNameGrid.Add(new XElement(nameGrid)); var columnHeadersCellReference = rows.First().Descendants<Cell>().Select(c => c.CellReference.InnerText.Replace("1", string.Empty)).ToArray(); foreach (var row in rows) { IEnumerable<Cell> cells = row.Elements<Cell>(); List<Cell> Customizedcells = new List<Cell>(); for (int i = 0; i < columnHeadersCellReference.Count(); i++) { Cell cell = row.Descendants<Cell>().Where(c => c.CellReference == columnHeadersCellReference[i] + row.RowIndex).FirstOrDefault(); Cell curCell = new Cell(); if (cell == null) { curCell.CellReference = columnHeadersCellReference[i] + row.RowIndex; var cellValue = new CellValue(); cellValue.Text = row.Descendants<Cell>().ElementAt(i).InnerText; curCell.CellValue = cellValue; } else { curCell = cell; } Customizedcells.Add(curCell); } //var cellCount = cells.Count(); var cellCount = Customizedcells.Count(); rowcount++; XElement nameGridDetails = new XElement(nameGridDetail); for (var i = 0; i < cellCount; i++) { //if (row.Hidden != null && !row.Hidden) //{ //var currentCell = cells.ToArray()[i]; var currentCell = Customizedcells.ToArray()[i]; textval = GetCellValue(spreadsheetDocument, currentCell).Trim(); //currentCell.CellValue.Text; } } } public string GetCellValue(SpreadsheetDocument document, Cell cell) { SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart; string value = string.Empty; if (cell.CellValue != null) value = cell.CellValue.InnerXml; if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString) { return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText; } else { return value; } }

    Mark as answer if you feel its right.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 27, 2012 7:35 AM
  • User-1727942399 posted

     

    Thank You both..

    I tried this it working fine.

    Microsoft.Office.Interop.Excel.Range firstCell = objWorksheet.get_Range("A1", Type.Missing);
                    Microsoft.Office.Interop.Excel.Range lastCell = objWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);

                    object[,] cellValues;
                    //object[,] cellFormulas;

                    Microsoft.Office.Interop.Excel.Range worksheetCells = objWorksheet.get_Range(firstCell, lastCell);
                    cellValues = worksheetCells.Value2 as object[,];
                    //cellFormulas = worksheetCells.Formula as object[,];

     objWorksheet.Cells[49, 4] = objCurrency.TranslateCurrency(Convert.ToDecimal(cellValues[49, 9])).ToString();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 27, 2012 9:03 AM

All replies

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 27, 2012 7:00 AM
  • User1778637605 posted
    Hi, 
    Please try below code.

    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(documentPath, true)) { int rowcount = 0; var sheet = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().First(); var worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id.Value); IEnumerable<Row> rows = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>(); string textval = string.Empty; xdocNameGrid.Add(new XElement(nameGrid)); var columnHeadersCellReference = rows.First().Descendants<Cell>().Select(c => c.CellReference.InnerText.Replace("1", string.Empty)).ToArray(); foreach (var row in rows) { IEnumerable<Cell> cells = row.Elements<Cell>(); List<Cell> Customizedcells = new List<Cell>(); for (int i = 0; i < columnHeadersCellReference.Count(); i++) { Cell cell = row.Descendants<Cell>().Where(c => c.CellReference == columnHeadersCellReference[i] + row.RowIndex).FirstOrDefault(); Cell curCell = new Cell(); if (cell == null) { curCell.CellReference = columnHeadersCellReference[i] + row.RowIndex; var cellValue = new CellValue(); cellValue.Text = row.Descendants<Cell>().ElementAt(i).InnerText; curCell.CellValue = cellValue; } else { curCell = cell; } Customizedcells.Add(curCell); } //var cellCount = cells.Count(); var cellCount = Customizedcells.Count(); rowcount++; XElement nameGridDetails = new XElement(nameGridDetail); for (var i = 0; i < cellCount; i++) { //if (row.Hidden != null && !row.Hidden) //{ //var currentCell = cells.ToArray()[i]; var currentCell = Customizedcells.ToArray()[i]; textval = GetCellValue(spreadsheetDocument, currentCell).Trim(); //currentCell.CellValue.Text; } } } public string GetCellValue(SpreadsheetDocument document, Cell cell) { SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart; string value = string.Empty; if (cell.CellValue != null) value = cell.CellValue.InnerXml; if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString) { return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText; } else { return value; } }

    Mark as answer if you feel its right.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 27, 2012 7:35 AM
  • User-1727942399 posted

     

    Thank You both..

    I tried this it working fine.

    Microsoft.Office.Interop.Excel.Range firstCell = objWorksheet.get_Range("A1", Type.Missing);
                    Microsoft.Office.Interop.Excel.Range lastCell = objWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);

                    object[,] cellValues;
                    //object[,] cellFormulas;

                    Microsoft.Office.Interop.Excel.Range worksheetCells = objWorksheet.get_Range(firstCell, lastCell);
                    cellValues = worksheetCells.Value2 as object[,];
                    //cellFormulas = worksheetCells.Formula as object[,];

     objWorksheet.Cells[49, 4] = objCurrency.TranslateCurrency(Convert.ToDecimal(cellValues[49, 9])).ToString();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 27, 2012 9:03 AM