access string in worksheet cell with Excel.Worksheet?

Question

How do I access the string value in a cell in an Excel worksheet?

string cellInfo = (string)w.Cells[3, 3];

I want the string value in the 3rd row, 3rd column, C3

        Excel.Application excel = new Excel.Application();
        Excel.Workbook workBook = excel.Workbooks.Open(string.Format(@"{0}\{1}", Directory.GetCurrentDirectory(), excelFilename),
               Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        List<Excel.Worksheet> result = excel.Worksheets.Cast<Excel.Worksheet>().Where(w => !exclude.Contains(w.Name)).ToList();

     result.ForEach(w => {

                    Excel.Range objRange = w.get_Range("C3", "C3");
                    string colLengthStr = objRange.Value2;
     }

Please don't prefix your titles with "C#:" and such. That's what the tags are for. — John Saunders, Mar 13 '12 at 16:47

score 1 · Accepted Answer · edited May 23 '17 at 12:28

There are many answers on SO about how to get data from Excel using c#. But you will need to do something similar to this:

Excel.Range objRange = (Excel.Range)objSheet.Cells[1,1];
strData = objRange.get_Value(Missing.Value).ToString();

How to read data of an Excel file using C#?

Resources for learning c# Excel interop

Or

C# Excel Interop

Or Even

How to automate Excel from C#.NET

Code from the automate Excel from c#.net, which includes reading/writing to cells:

private void button1_Click(object sender, System.EventArgs e)
{
    Excel.Application oXL;
    Excel._Workbook oWB;
    Excel._Worksheet oSheet;
    Excel.Range oRng;

    try
    {
        //Start Excel and get Application object.
        oXL = new Excel.Application();
        oXL.Visible = true;

        //Get a new workbook.
        oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
        oSheet = (Excel._Worksheet)oWB.ActiveSheet;

        //Add table headers going cell by cell.
        oSheet.Cells[1, 1] = "First Name";
        oSheet.Cells[1, 2] = "Last Name";
        oSheet.Cells[1, 3] = "Full Name";
        oSheet.Cells[1, 4] = "Salary";

        //Format A1:D1 as bold, vertical alignment = center.
        oSheet.get_Range("A1", "D1").Font.Bold = true;
        oSheet.get_Range("A1", "D1").VerticalAlignment = 
            Excel.XlVAlign.xlVAlignCenter;

        // Create an array to multiple values at once.
        string[,] saNames = new string[5,2];

        saNames[ 0, 0] = "John";
        saNames[ 0, 1] = "Smith";
        saNames[ 1, 0] = "Tom";
        saNames[ 1, 1] = "Brown";
        saNames[ 2, 0] = "Sue";
        saNames[ 2, 1] = "Thomas";
        saNames[ 3, 0] = "Jane";
        saNames[ 3, 1] = "Jones";
        saNames[ 4, 0] = "Adam";
        saNames[ 4, 1] = "Johnson";

            //Fill A2:B6 with an array of values (First and Last Names).
            oSheet.get_Range("A2", "B6").Value2 = saNames;

        //Fill C2:C6 with a relative formula (=A2 & " " & B2).
        oRng = oSheet.get_Range("C2", "C6");
        oRng.Formula = "=A2 & \" \" & B2";

        //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
        oRng = oSheet.get_Range("D2", "D6");
        oRng.Formula = "=RAND()*100000";
        oRng.NumberFormat = "$0.00";

        //AutoFit columns A:D.
        oRng = oSheet.get_Range("A1", "D1");
        oRng.EntireColumn.AutoFit();

        //Manipulate a variable number of columns for Quarterly Sales Data.
        DisplayQuarterlySales(oSheet);

        //Make sure Excel is visible and give the user control
        //of Microsoft Excel's lifetime.
        oXL.Visible = true;
        oXL.UserControl = true;
    }
    catch( Exception theException ) 
    {
        String errorMessage;
        errorMessage = "Error: ";
        errorMessage = String.Concat( errorMessage, theException.Message );
        errorMessage = String.Concat( errorMessage, " Line: " );
        errorMessage = String.Concat( errorMessage, theException.Source );

        MessageBox.Show( errorMessage, "Error" );
    }
}

private void DisplayQuarterlySales(Excel._Worksheet oWS)
{
    Excel._Workbook oWB;
    Excel.Series oSeries;
    Excel.Range oResizeRange;
    Excel._Chart oChart;
    String sMsg;
    int iNumQtrs;

    //Determine how many quarters to display data for.
    for( iNumQtrs = 4; iNumQtrs >= 2; iNumQtrs--)
    {
        sMsg = "Enter sales data for ";
        sMsg = String.Concat( sMsg, iNumQtrs );
        sMsg = String.Concat( sMsg, " quarter(s)?");

        DialogResult iRet = MessageBox.Show( sMsg, "Quarterly Sales?", 
            MessageBoxButtons.YesNo );
        if (iRet == DialogResult.Yes)
            break;
    }

    sMsg = "Displaying data for ";
    sMsg = String.Concat( sMsg, iNumQtrs );
    sMsg = String.Concat( sMsg, " quarter(s)." );

    MessageBox.Show( sMsg, "Quarterly Sales" );

    //Starting at E1, fill headers for the number of columns selected.
    oResizeRange = oWS.get_Range("E1", "E1").get_Resize( Missing.Value, iNumQtrs);
    oResizeRange.Formula = "=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\"";

    //Change the Orientation and WrapText properties for the headers.
    oResizeRange.Orientation = 38;
    oResizeRange.WrapText = true;

    //Fill the interior color of the headers.
    oResizeRange.Interior.ColorIndex = 36;

    //Fill the columns with a formula and apply a number format.
    oResizeRange = oWS.get_Range("E2", "E6").get_Resize( Missing.Value, iNumQtrs);
    oResizeRange.Formula = "=RAND()*100";
    oResizeRange.NumberFormat = "$0.00";

    //Apply borders to the Sales data and headers.
    oResizeRange = oWS.get_Range("E1", "E6").get_Resize( Missing.Value, iNumQtrs);
    oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin;

    //Add a Totals formula for the sales data and apply a border.
    oResizeRange = oWS.get_Range("E8", "E8").get_Resize( Missing.Value, iNumQtrs);
    oResizeRange.Formula = "=SUM(E2:E6)";
    oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).LineStyle 
        = Excel.XlLineStyle.xlDouble;
    oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).Weight 
        = Excel.XlBorderWeight.xlThick;

    //Add a Chart for the selected data.
    oWB = (Excel._Workbook)oWS.Parent;
    oChart = (Excel._Chart)oWB.Charts.Add( Missing.Value, Missing.Value, 
        Missing.Value, Missing.Value );

    //Use the ChartWizard to create a new chart from the selected data.
    oResizeRange = oWS.get_Range("E2:E6", Missing.Value ).get_Resize( 
        Missing.Value, iNumQtrs);
    oChart.ChartWizard( oResizeRange, Excel.XlChartType.xl3DColumn, Missing.Value,
        Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value, 
        Missing.Value, Missing.Value, Missing.Value, Missing.Value );
    oSeries = (Excel.Series)oChart.SeriesCollection(1);
    oSeries.XValues = oWS.get_Range("A2", "A6");
    for( int iRet = 1; iRet <= iNumQtrs; iRet++)
    {
        oSeries = (Excel.Series)oChart.SeriesCollection(iRet);
        String seriesName;
        seriesName = "=\"Q";
        seriesName = String.Concat( seriesName, iRet );
        seriesName = String.Concat( seriesName, "\"" );
        oSeries.Name = seriesName;
    }                                                         

    oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oWS.Name );

    //Move the chart so as not to cover your data.
    oResizeRange = (Excel.Range)oWS.Rows.get_Item(10, Missing.Value );
    oWS.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top;
    oResizeRange = (Excel.Range)oWS.Columns.get_Item(2, Missing.Value );
    oWS.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;
}

Based on your edit try the following:

result.ForEach(w => {

                    Excel.Range objRange = w.get_Range("C3", Missing.Value);
                    string colLengthStr = objRange.Value2;
     }

Missing.Value is used for the optional parameters - see http://blogs.msdn.com/b/gabhan_berry/archive/2008/02/06/excel-s-optional-parameters-and-missing-value-a-c-workaround.aspx — Taryn, Mar 13 '12 at 16:59
I looked through this documentation before. It's not very clear. There isnt an example of how to get a string value from a cell or at least I don't see it. — user840930, Mar 13 '12 at 17:13
I tried: Excel.Range objRange = w.get_Range("C3", "C3"); string colLengthStr = objRange.Value2; but Value2 is null not the value in cell C3 how do I get the string value from an Excel worksheet? — user840930, Mar 13 '12 at 17:14
can you edit your original post and provide more code, showing exactly what you are doing? — Taryn, Mar 13 '12 at 17:16
ok I edited my original post. I'm just getting a standard workbook then worksheet from excel — user840930, Mar 13 '12 at 17:27
@Taryn I can't seem to find any documentation for this `Excel.Range.get_Value()` method, yet I've seen multiple posts with it. Could you please share documentation for it? — Matthew Kligerman, Jul 03 '20 at 00:21

access string in worksheet cell with Excel.Worksheet?

1 Answers1