microsoft excel

Putting search column

Page 1 of 1

2 Replies - 858 Views - Last Post: 03 September 2009 - 08:32 AM Rate Topic: -----

#1 CCONNER9582002  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 35
  • Joined: 21-April 09

microsoft excel

Post icon  Posted 03 September 2009 - 06:36 AM

Is there any way I could use C# to program a column in microsoft excel to search for what is in the spreadsheet by item or description using C# program?
Is This A Good Question/Topic? 0
  • +

Replies To: microsoft excel

#2 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: microsoft excel

Posted 03 September 2009 - 07:23 AM

Yes.

How to automate Microsoft Excel from Microsoft Visual C# .NET
How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays
Was This Post Helpful? 0
  • +
  • -

#3 CCONNER9582002  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 35
  • Joined: 21-April 09

Re: microsoft excel

Posted 03 September 2009 - 08:32 AM

View PostJayman, on 3 Sep, 2009 - 06:23 AM, said:



I used the code below provided by microsoft but I am getting this error message C:\Documents and Settings\cconner\My Documents\Visual Studio Projects\WindowsApplication2\Form1.cs(8): Namespace '' already contains a definition for 'Excel'. According to the data I have read that this is suppose to work and not give me this error but I am still getting this.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;


namespace WindowsApplication2
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{
		private System.Windows.Forms.Button button1;
		private System.Windows.Forms.Button button2;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public Form1()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.button1 = new System.Windows.Forms.Button();
			this.button2 = new System.Windows.Forms.Button();
			this.SuspendLayout();
			// 
			// button1
			// 
			this.button1.Location = new System.Drawing.Point(56, 128);
			this.button1.Name = "button1";
			this.button1.TabIndex = 0;
			this.button1.Text = "button1";
			this.button1.Click += new System.EventHandler(this.button1_Click);
			// 
			// button2
			// 
			this.button2.Location = new System.Drawing.Point(184, 128);
			this.button2.Name = "button2";
			this.button2.TabIndex = 1;
			this.button2.Text = "button2";
			this.button2.Click += new System.EventHandler(this.button2_Click);
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(292, 273);
			this.Controls.Add(this.button2);
			this.Controls.Add(this.button1);
			this.Name = "Form1";
			this.Text = "Form1";
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}
		//Declare these two variables globally so you can access them from both
		//Button1 and Button2.
		Excel.Application objApp;
		Excel._Workbook objBook;

		private void button1_Click(object sender, System.EventArgs e)
		{
			Excel.Workbooks objBooks;
			Excel.Sheets objSheets;
			Excel._Worksheet objSheet;
			Excel.Range range;

			try
			{
				// Instantiate Excel and start a new workbook.
				objApp = new Excel.Application();
				objBooks = objApp.Workbooks;
				objBook = objBooks.Add( Missing.Value );
				objSheets = objBook.Worksheets;
				objSheet = (Excel._Worksheet)objSheets.get_Item(1);

				//Get the range where the starting cell has the address
				//m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
				range = objSheet.get_Range("A1", Missing.Value);
				range = range.get_Resize(5, 5);

				if (this.FillWithStrings.Checked == false)
				{
					//Create an array.
					double[,] saRet = new double[5, 5];

					//Fill the array.
					for (long iRow = 0; iRow < 5; iRow++)
					{
						for (long iCol = 0; iCol < 5; iCol++)
						{
							//Put a counter in the cell.
							saRet[iRow, iCol] = iRow * iCol;
						}
					}

					//Set the range value to the array.
					range.set_Value(Missing.Value, saRet );
				}

				else
				{
					//Create an array.
					string[,] saRet = new string[5, 5];

					//Fill the array.
					for (long iRow = 0; iRow < 5; iRow++)
					{
						for (long iCol = 0; iCol < 5; iCol++)
						{
							//Put the row and column address in the cell.
							saRet[iRow, iCol] = iRow.ToString() + "|" + iCol.ToString();
						}
					}

					//Set the range value to the array.
					range.set_Value(Missing.Value, saRet );
				}

				//Return control of Excel to the user.
				objApp.Visible = true;
				objApp.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 button2_Click(object sender, System.EventArgs e)
		{
			Excel.Sheets objSheets;
			Excel._Worksheet objSheet;
			Excel.Range range;

			try
			{
				try
				{
					//Get a reference to the first sheet of the workbook.
					objSheets = objBook.Worksheets;
					objSheet = (Excel._Worksheet)objSheets.get_Item(1);
				}

				catch( Exception theException ) 
				{
					String errorMessage;
					errorMessage = "Can't find the Excel workbook.  Try clicking Button1 " +
						"to create an Excel workbook with data before running Button2.";

					MessageBox.Show( errorMessage, "Missing Workbook?");

					//You can't automate Excel if you can't find the data you created, so 
					//leave the subroutine.
					return;
				}

				//Get a range of data.
				range = objSheet.get_Range("A1", "E5");

				//Retrieve the data from the range.
				Object[,] saRet;
				saRet = (System.Object[,])range.get_Value( Missing.Value );

				//Determine the dimensions of the array.
				long iRows;
				long iCols;
				iRows = saRet.GetUpperBound(0);
				iCols = saRet.GetUpperBound(1);

				//Build a string that contains the data of the array.
				String valueString;
				valueString = "Array Data\n";

				for (long rowCounter = 1; rowCounter <= iRows; rowCounter++)
				{
					for (long colCounter = 1; colCounter <= iCols; colCounter++)
					{

						//Write the next value into the string.
						valueString = String.Concat(valueString,
							saRet[rowCounter, colCounter].ToString() + ", ");
					}

					//Write in a new line.
					valueString = String.Concat(valueString, "\n");
				}

				//Report the value of the array.
				MessageBox.Show(valueString, "Array Values");
			}

			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" );
			}
		}

		
	}
}


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1