c# excel

[surakesh]

using Excel = Microsoft.Office.Interop.Excel;
namespace program
{

	private void cmdOpen_Click(object sender, System.EventArgs e)
	{

	FileSystem.FileOpen(1, inputfile, OpenMode.Input, (OpenAccess)(-1), (OpenShare)(-1), -1); // 1 : excel file
	Excel.Application MyXl;
	MyXl = new Excel.ApplicationClass();
	MyXl.Visible = true;
	Excel.Workbook xlWorkbook = MyXl.Workbooks.Open(inputfile, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
        Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets;
	Variables();	

	}

	public void Variables()
	{
	int i; 
        object ws; 
        int j;
	ws = MyXl.xlWorksheet("sheet1");
	do
		{
		
		inputs[i] = ws.Cells[5, input_startcol+i];
	
		}while(inputs[i]!="");

	}

Error:
1.The name 'MyXl' does not exist in the current context
2.'object' does not contain definition for 'Cells'

[thines01]

Application MyExcel = new Application();

1) It looks also like you're opening the file twice. Is that intentional?
2) Is the life of your file only to exist inside the button-click?
3) If you are just harvesting data from the file, does it need to be visible?

[surakesh]

Application MyExcel = new Application();

1) It looks also like you're opening the file twice. Is that intentional?
2) Is the life of your file only to exist inside the button-click?
3) If you are just harvesting data from the file, does it need to be visible?

1. I like open the file only once.
2. Not necessary that the life of file should have life within button-click
3. No need of visibility.

I would like to access data from excel through different button clicks.

[thines01]

What I'm talking about is the FileSystem.FileOpen and also the MyXl.Workbooks.Open calls.

Based on what you have said, the most important thing is to load the data from the sheet. This means you can treat the sheet like a database or other repository.
When I load data, I create a custom Object for the data, a Master which is a collection of those objects and a Loader.

I remain consistent with this, so that I can concentrate on the data rather than the repository.

Let's say I have a spreadsheet with the phonetic alphabet in it. If I have a loader for that data that is in a separate class, I don't have to worry about the the display or the user input. That class will do nothing but load that data into a collection of the objects.

[Imagine the spreadsheet contains:]
[A][ B ]
---------------
A Alpha
B Bravo
C Charlie
etc...

If I have a console app or a windows form or a web page, I could use my pattern to load that data without having to care what methodology is using it.

Here is the way it would look in a console app:

using System;
//
namespace DW_400446
{
   class CDW_400446
   {
      private static void Display(string strData)
      {
         Console.WriteLine(strData);
      }

      static void Main(string[] args)
      {
         string strError = ""; //error messages go here

         string strXlsFile = "c:/science/DaniWeb/DW_400446/Data/Alphabet.xls";
         CPhoneticAlphabetMaster masterPA = new CPhoneticAlphabetMaster(strXlsFile);

         ///////////////////////////////////////////////////////////////////////
         // if the master cannot be loaded, show the error message and quit.
         if (!masterPA.Load(ref strError))
         {
            Display("Could not load XLS file: " + strError);
            return;
         }

         Display(string.Format("Loaded {0} records", masterPA.Count));
      }
   }
}

If I want to load this in a button-push, it would have mostly the same code.
{more in next message}

[thines01]

The object would look like this:

using System.Reflection;
using Microsoft.Office.Interop.Excel;

namespace DW_400446
{
   public class CPhoneticAlphabet
   {
      public char chrKey { get; set; }
      public string strValue { get; set; }

      public CPhoneticAlphabet()
      {
         chrKey = '\0';
         strValue = "";
      }

      public CPhoneticAlphabet(char c, string s)
      {
         chrKey = c;
         strValue = s;
      }

      public CPhoneticAlphabet(CPhoneticAlphabet copy)
      {
         this.chrKey = copy.chrKey;
         this.strValue = copy.strValue;
      }

      public CPhoneticAlphabet(Range row) // EXCEL
      {
         chrKey = SafeStringValue(row, 1)[0];
         strValue = SafeStringValue(row, 2);
      }

      public static string SafeStringValue(Range row, int intCol)
      {
         object obj = row.Cells[Missing.Value, intCol];
         if (null == obj)
         {
            return "";
         }

         return ((Range)obj).Value2.ToString();
      }

      public override string ToString()
      {
         return chrKey + '-' + strValue;
      }

      public override bool Equals(object obj)
      {
         return this.ToString().Equals(((CPhoneticAlphabet)obj).ToString());
      }

      public override int GetHashCode()
      {
         return this.ToString().GetHashCode();
      }
   }
}

{more in the next message}

[thines01]

I also use an Interface to govern method names in this pattern, but it's optional for this test.

The master would look like this:

using System.Collections.Generic;

namespace DW_400446
{
   using IMaster;
   public class CPhoneticAlphabetMaster : List<CPhoneticAlphabet>, IMaster
   {
      private string _strFileName { get; set; }

      public CPhoneticAlphabetMaster()
      {
         _strFileName = "";
      }

      public CPhoneticAlphabetMaster(string strFileName)
      {
         _strFileName = strFileName;
      }

      public bool Load(ref string strError)
      {
         return (new CPhoneticAlphabetLoader(_strFileName)).Load(this, ref strError);
      }
   }
}

{more in the next message}

[thines01]

And, of course, the loader will do nothing but load the data into the master by way of the object:

using System;
using System.Reflection;
using Microsoft.Office.Interop.Excel;

namespace DW_400446
{
   using IMaster;
   public class CPhoneticAlphabetLoader : IMasterLoader<CPhoneticAlphabetMaster>
   {
      private string _strFileName { get; set; }
      public CPhoneticAlphabetLoader()
      {
         _strFileName = "";
      }

      public CPhoneticAlphabetLoader(string strFileName)
      {
         _strFileName = strFileName;
      }

      public bool Load(CPhoneticAlphabetMaster master, ref string strError)
      {
         bool blnRetVal = true;
         long lngRowCount = 1; // adjust to skip headers, if necessary
         //
         try
         {
            Application excel = new Application();
            excel.DisplayAlerts = false;

            Workbook wb = excel.Workbooks.Open(
               _strFileName,
               Missing.Value, Missing.Value, Missing.Value,
               Missing.Value, Missing.Value, Missing.Value,
               Missing.Value, Missing.Value, Missing.Value,
               Missing.Value, Missing.Value, Missing.Value,
               Missing.Value, Missing.Value
               );

            Worksheet ws = (Worksheet)wb.Worksheets[1]; // First Worksheet
            //
            Range rowCurrent = ((Range)ws.Rows[lngRowCount, Missing.Value]);
            //
            ////////////////////////////////////////////////////////////////////
            // Loops until it encounters an empty cell.
            while (null != ((Range)rowCurrent.Cells[Missing.Value, 1]).Value2)
            {
               master.Add(new CPhoneticAlphabet(rowCurrent));
               rowCurrent = ((Range)ws.Rows[++lngRowCount, Missing.Value]);
            }
            //
            wb.Close(XlSaveAction.xlDoNotSaveChanges, Missing.Value, Missing.Value);
            excel.Workbooks.Close();
            excel.Quit();
         }
         catch (Exception exc)
         {
            blnRetVal = false;
            strError = exc.Message;
         }

         return blnRetVal;
      }
   }
}