0

I have an Excel,

 A      B        C      D
 --    ---      ---    ---
                 3      4
 7      8        9      10

In Interop.Excel I use.

xlSheet.Cells(1, 3).Formula.ToString

To get the value of row 1 column 3 which the result would be 3 OR

xlSheet.Cells(1, 2).Formula.ToString

To get the value of row 1 column 2 which the result would be an Empty string

How do I go about this using Open XML?

chikor.net
  • 357
  • 9
  • 20

2 Answers2

0

With openxml you have .RowId and .ColumnId to select a specific cell, using LINQ should then allow you to manipulate your data as will. See this http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2011/07/18/query-open-xml-spreadsheets-in-vb-net-using-linq.aspx for detailled exemples

After looking again, this solution should work

Using doc As SpreadsheetDocument = SpreadsheetDocument.Open("Data.xlsx", False)
        Dim worksheet As WorksheetPart = doc.WorkbookPart.GetPartById(0)
        Dim cell as Cell = worksheet.rows(yourRow).collumns(yourCollumn)
        return cell.value
End using

If it does not, maybe you will have to make a foreach loop, but that would not be very advisable if you have a lot of data

Mazette
  • 27
  • 4
  • consider including your findings in post itself to avoid dead link for future readers. – Bond - Java Bond Jun 25 '15 at 09:25
  • @Mazette thanks for your reply, but how would i implement a function to get the cellvalue when the row and column index are passed? – chikor.net Jun 25 '15 at 10:13
  • @mazette thanks once again but i didnt find the rows method in worksheet (i.e worksheet.rows(yourRow) – chikor.net Jun 25 '15 at 13:06
  • @chikor.net apparently, worksheet also have a Descendants method where you can find the rows (something like worksheet.Descendants(Of Row) ). I can't look at it more closely right now, but it seems like this thread http://stackoverflow.com/questions/2624333/how-do-i-read-data-from-a-spreadsheet-using-the-openxml-format-sdk could help you if you're still having issues – Mazette Jun 25 '15 at 13:19
0

var value = AllCells.Where(t => t.CellReference != "AC1" && string.Join("", t.CellReference.Value.ToString().Where(e => char.IsLetter(e)).ToList()) == "AC" && t.CellValue != null).GroupBy(g => g.CellValue.Text).Select(x => x.FirstOrDefault()).Select(s => new CellModel() { CellReference = s.CellReference, DataType = s.DataType != null ? s.DataType.Value : CellValues.Error, CellValue = s.CellValue }).ToList();

maazasif1995
  • 39
  • 6
  • 1
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-ask). – Community Sep 21 '21 at 11:40