I am using C# 2008, reading excel worksheets into my program. I have the following:

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application excelApp = new Excel.Application();
excelApp.Workbooks.Open(excelFile, 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

// Get the first worksheet in the workbook
Excel.Worksheet workSheet = (Excel.Worksheet)estimates.Worksheets.get_Item(1);


I can get cells easily with
((Excel.Range)workSheet.Cells[row, col]).Text.ToString();      // For greyed out, non-editable cells
or
((Excel.Range)workSheet.Cells[row, col]).Value2.ToString();   // For white, editable cells


Now, I need to get the values of checkboxes and the value/text of comboboxes on the worksheet. Since they aren't in any cell,
they are floating above the sheet, there is no way to get them by cell number. I have tried the following:

Excel.OLEObject cb1 = (Excel.OLEObject)workSheet.OLEObjects("ComboBox1");

but the only thing I can access is the index using  cb1.Index

I have also tried:

Excel.Dropdown dd1 = ((Excel.Dropdown)workSheet.DropDowns("ComboBox1")).Text.ToString();
and
Excel.Dropdown dd1 = ((Excel.Dropdown)workSheet.DropDowns("ComboBox1")).Value.ToString();

but the ((Excel.Dropdown)workSheet.DropDowns("ComboBox1"))  part gives me a COMException

How would I go about getting the text of the selected item in a dropdown and also find out if a checkbox is checked or not?

Thanks

That works, thanks very much!

I found that you can also do this:

 
Excel.OLEObject cb1 = (Excel.OLEObject)workSheet.OLEObjects("ComboBox1");

MSForm.ComboBox combo1 = (MsForm.ComboBox)cb1.Object; 

and then do   combo1.Text   to get the text value 

Wow, thanks so much LawrenceSin! I have been looking for this info for days and was just about to give up on it.  I just have one more question...in the code below, everything works except that even though the BackStyle gets changed to transparent in code, in Excel it never appears to be transparent--even if I toggle it back and forth in Design mode.  Any ideas on why this isn't working?

Excel.OLEObject ole = ((Excel.OLEObjects)wsTemplate.OLEObjects(Missing.Value)).Add("Forms.Textbox.1", Missing.Value, Missing.Value, 
	Missing.Value, Missing.Value, Missing.Value, Missing.Value, 15, 15, 100, 20);
MSForm.TextBox tb = (MSForm.TextBox)(ole.Object);
tb.Text = "ActiveX Test";
tb.Font.Name = "Arial";
tb.Font.Size = 16;
tb.SpecialEffect = Microsoft.Vbe.Interop.Forms.fmSpecialEffect.fmSpecialEffectFlat;
tb.BackStyle = Microsoft.Vbe.Interop.Forms.fmBackStyle.fmBackStyleTransparent;

Hi,

using Interop is not best way to work with Excel in .NET. You can try using GemBox .NET Excel component which is free for commercial use (limit is 150 rows).

Here is a list of reasons why GemBox.Spreadsheet is better then Excel Interop and Excel Automation

Sweet!  150 rows!

I wouldn't drive a car that could only go 5 mph.  The "free" GemBox is equally as useless.

im1dermike:

Sweet!  150 rows!

I wouldn't drive a car that could only go 5 mph.  The "free" GemBox is equally as useless.

Limited functionality is common practice in software distribution, you can fully test if the component meets your requirements. If you're satisfied, buy a license so the limits are gone..... Another option is full functionality for limited time only... The nice thing of the first option that the component is free for those who don't want full functionality (home users)

 I do agree that Excel Automation is not a very good idea, I don't know GemBox, but if the needs arrives I'll certainly take a look. Another option is Aspose:

http://www.aspose.com/categories/file-format-components/aspose.cells-for-.net-and-java/default.aspx

private function()

{

int i = 0;

      Excel.Application xl = null;

      Excel._Workbook wb = null;

      Excel._Worksheet sheet = null;

      object Missing = System.Reflection.Missing.Value;

      //VBIDE.VBComponent module = null;

      bool SaveChanges = false;

      try

      {

          if (File.Exists(FileName)) { File.Delete(FileName); }

          GC.Collect();

          // Create a new instance of Excel from scratch

          xl = new Excel.Application();

          xl.Visible = false;

          // Add one workbook to the instance of Excel

          wb = (Excel._Workbook)(xl.Workbooks.Add(Missing));

          wb.Sheets.Add(Missing, Missing, Missing, Missing);

          wb.Sheets.Add(Missing, Missing, Missing, Missing);

          wb.Sheets.Add(Missing, Missing, Missing, Missing);

          wb.Sheets.Add(Missing, Missing, Missing, Missing);

          wb.Sheets.Add(Missing, Missing, Missing, Missing);

          // Get a reference to the one and only worksheet in our workbook

          //sheet = (Excel._Worksheet)wb.ActiveSheet;

          sheet = (Excel._Worksheet)(wb.Sheets[1]);

          // Fill spreadsheet with sample data

          sheet.Name = "Test";

          SqlDataReader iDr = getFieldName("tbl_student_profile");

          Excel.DropDowns xlDropDowns = null;

          Excel.DropDown xlDropDown = null;

          Excel.Range xlsRange = null;

          xlsRange = sheet.get_Range("A1", "Z1");

          xlsRange.Font.Bold = true;

          xlsRange.ColumnWidth = 25;

          xlsRange.RowHeight = 20;

          xlsRange.EntireColumn.AutoFit();

          int k = 0;

          object objVal = null;

          for (int n = 0; n <= iDr.FieldCount - 1; n++)

          {

           sheet.Cells[1, n + 1] = iDr.GetName(n);

          }

          for (k = 2; k <= 10; k++)

          {

              for (int n = 0; n <= iDr.FieldCount - 1; n++)

              {

                  sheet.Cells[k, n + 1] = null;

                  if (iDr.GetName(n).ToString() == "city_ID")

                  {

                      xlsRange = sheet.get_Range("M" + k, Missing);

                      xlDropDowns = ((Excel.DropDowns)(sheet.DropDowns(Missing)));

                      xlDropDown = xlDropDowns.Add((double)xlsRange.Left, (double)xlsRange.Top, (double)xlsRange.Width, (double)xlsRange.Height, true);

                      DataSet dsCity = getValue("tbl_city");

                      for (i = 0; i < dsCity.Tables[0].Rows.Count; i++)

                      {

                          objVal = dsCity.Tables[0].Rows[i][0];

                          xlDropDown.AddItem(dsCity.Tables[0].Rows[i][1].ToString(), objVal);

                      }

                  }

                  if (iDr.GetName(n).ToString() == "state_ID")

                  {

                      xlsRange = sheet.get_Range("N" + k, Missing);

                      xlDropDowns = ((Excel.DropDowns)(sheet.DropDowns(Missing)));

                      xlDropDown = xlDropDowns.Add((double)xlsRange.Left, (double)xlsRange.Top, (double)xlsRange.Width, (double)xlsRange.Height, true);

                      DataSet dsState = getValue("tbl_state");

                      for (i = 0; i < dsState.Tables[0].Rows.Count; i++)

                      {

                          objVal = dsState.Tables[0].Rows[i][0];

                          xlDropDown.AddItem(dsState.Tables[0].Rows[i][1].ToString(), objVal);

                      }

                  }

                  if (iDr.GetName(n).ToString() == "country_ID")

                  {

                      xlsRange = sheet.get_Range("L" + k, Missing);

                      xlDropDowns = ((Excel.DropDowns)(sheet.DropDowns(Missing)));

                      xlDropDown = xlDropDowns.Add((double)xlsRange.Left, (double)xlsRange.Top, (double)xlsRange.Width, (double)xlsRange.Height, true);

                      DataSet dsCountry = getValue("tbl_country");

                      for (i = 0; i < dsCountry.Tables[0].Rows.Count; i++)

                      {

                          objVal = dsCountry.Tables[0].Rows[i][0];

                          xlDropDown.AddItem(dsCountry.Tables[0].Rows[i][1].ToString(), objVal);

                      }

                  }

              }

          }

          con.Close();

          // Let loose control of the Excel instance

          xl.Visible = false;

          xl.UserControl = false;

          // Set a flag saying that all is well and it is ok to save our changes to a file.

          SaveChanges = true;

          //  Save the file to disk

          wb.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal,

                    null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,

                    false, false, null, null, null);

      }

      catch (Exception err)

      {

          msg = "Error: ";

          msg = String.Concat(msg, err.Message);

          msg = String.Concat(msg, " Line: ");

          msg = String.Concat(msg, err.Source);

      }

      finally

      {

          try

          {

              // Repeat xl.Visible and xl.UserControl releases just to be sure

              // we didn't error out ahead of time.

              xl.Visible = false;

              xl.UserControl = false;

              // Close the document and avoid user prompts to save if our method failed.

              wb.Close(SaveChanges, null, null);

              xl.Workbooks.Close();

          }

          catch { }

          // Gracefully exit out and destroy all COM objects to avoid hanging instances

          // of Excel.exe whether our method failed or not.

          xl.Quit();

          if (File.Exists(FileName))

          {

              chkSave = "true";

          }

          //if (module != null) { Marshal.ReleaseComObject(module); }

          if (sheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); }

          if (wb != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(wb); }

          if (xl != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xl); }

          //module = null;

          sheet = null;

          wb = null;

          xl = null;

          GC.Collect();

          //con.Close();

      }

iyyappan,

As already said earlier, it isn't good practice to use Office automation in an ASP.NET environment. You can find the reason here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2 

Thanks for this post, it allmost does what I need it to do. However I can't figure out the type conversion using visual basic 2008.

I have:

If TypeName(MyShape.OLEFormat.Object) = "CheckBox" Then

Dim MyCB As Microsoft.Vbe.Interop.Forms.CheckBox = MyShape.OLEFormat.Object

End If

However this results in an error. Any idea how I can do this conversion?

I receive the error:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Vbe.Interop.Forms.CheckBox'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BD21D43-EC42-11CE-9E0D-00AA006002F3}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

The same error occurs when I write the conversion in C#

namespace PConversion

{

public class CConversion

{

public MSForm.ComboBox PCombobox(Microsoft.Office.Interop.Excel.Shape MyShape)

{

MSForm.ComboBox newBox = (MSForm.ComboBox)(((Excel.OLEObject)MyShape).Object);

return newBox;

}

}

}

Hope someone will be able to tell me why I receive this error and even better, how to get rid of it.

Kind regards,

Reinier