Comments by sonny3g (Top 4 by date)

And the solution is so simple that I am embarrassed. I needed to use "result = thecurrentcell.CellValue.InnerText;" instead of "result = thecurrentcell.InnerText;". I now get the value instead of the formula.

That code is the basis of what I am using now. Unfortunately, what it returns is the formula =B4+C4+Q4, not the actual value.

For now, I have moved my formula column out of the way and manually copy the column and paste the values into the first column that I am searching. Fortunately, the data in the spreadsheet is static so I do not have to redo this multiple times.

Thanks for your suggestion.

Here is more of the code.

public void ReadMyData()//double idx) // idx = xmHt + bf + heavy duty
{

string fileName = @"c:\cax\resource\bpsys\programs\dose_data.xlsx";
string sheetName = "RFML_DATA";
string addressName = "A1";
double idx = 77.50;
int count = 1;
string key = "";
string myData = idx.ToString();
try
{
do
{
addressName = "A" + count.ToString();
key = GetCellValue(fileName, sheetName, addressName);
count++;
}
while (key != myData);
}
catch (ArgumentException e)
{
MessageBox.Show(e.Message);
}
}

// GetCellValue code:
// Adapted from "How to: retrieve the values of cells in a spreadsheet document (open xml sdk)"
//
// Retrieve the value of a cell, given a file name, sheet name, and address name.
public static string GetCellValue(string fileName, string sheetName, string addressName)
{
string value = null;

// Open the spreadsheet document for read-only access.
using (DOpkg.SpreadsheetDocument document = DOpkg.SpreadsheetDocument.Open(fileName, false))
{
// Retrieve a reference to the workbook part.
DOpkg.WorkbookPart wbPart = document.WorkbookPart;

// Find the sheet with the supplied name, and then use that
// Sheet object to retrieve a reference to the first worksheet.
DOxls.Sheet theSheet = wbPart.Workbook.Descendants<doxls.sheet>().Where(s => s.Name == sheetName).FirstOrDefault();

// Throw an exception if there is no sheet.
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}

// Retrieve a reference to the worksheet part.
DOpkg.WorksheetPart wsPart = (DOpkg.WorksheetPart)(wbPart.GetPartById(theSheet.Id));

// Use its Worksheet property to get a reference to the cell
// whose address matches the address you supplied.
DOxls.Cell theCell = wsPart.Worksheet.Descendants<doxls.cell>().Where(c => c.CellReference == addressName).FirstOrDefault();

// If the cell does not exist, return an empty string.
if (theCell != null)
{
value = theCell.InnerText;

// If the cell represents an integer number, you are done. For dates, this code returns the serialized value that
// represents the date. The code handles strings and Booleans individually. For shared strings, the code
// looks up the corresponding value in the shared string table. For Booleans, the code converts the value into
// the words TRUE or FALSE.
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case DOxls.CellValues.SharedString:

// For shared strings, look up the value in the shared strings table.
var stringTable = wbPart.GetPartsOfType<dopkg.sharedstringtablepart>().FirstOrDefault();

// If the shared string table is missing, something is wrong. Return the index that is in
// the cell. Otherwise, look up the correct text in the table.
if (stringTable != null)
{
value = stringTable.Share