[naturephoenix]

Let's say I have five sheets in excel workbook,and each sheet contains one table.Is there any way to Import only active sheet to my C#,What should I do to recognize only active sheet.??any help,ideas??

[fixo]

naturephoenix, on 22 February 2011 - 01:46 AM, said:

Let's say I have five sheets in excel workbook,and each sheet contains one table.Is there any way to Import only active sheet to my C#,What should I do to recognize only active sheet.??any help,ideas??

Please, follow the forum rules
At the first show us your code you tried

[naturephoenix]

fixo, on 23 February 2011 - 12:44 PM, said:

naturephoenix, on 22 February 2011 - 01:46 AM, said:

Let's say I have five sheets in excel workbook,and each sheet contains one table.Is there any way to Import only active sheet to my C#,What should I do to recognize only active sheet.??any help,ideas??

Please, follow the forum rules
At the first show us your code you tried

Reason why I didnt post anything was tht I was looking just for some idea or something,no "code help". By the way I resolve this problem but not sure is it a smart way.I made in my sheet a "ID_Table" which contains just a one number.Only active sheet contains this table(i made tht with Excel VBA).So my first step in C# is to check which number is in ID_Table and then I do rest.

Checking which number is in ID_Table:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;

namespace MyTry
{
 static  class Checking
    {
       static public int Check_Method()
        { 
   string sConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Cp\My Documents\MyExample.xlsm" + @";Extended Properties=""Excel 12.0 Macro;HDR=Yes;ImpoertMixedTypes=Text;TypeGuessRows=0""";

                OleDbConnection conn = new OleDbConnection(sConn);
                string command_check = "select * from [ID]";
                OleDbCommand cmd = new OleDbCommand(command_check, conn);
                try
                {
                    conn.Open();
                    return Convert.ToInt16(cmd.ExecuteScalar());
                    //x =
                    //MessageBox.Show(x.ToString());
                }
                catch (OleDbException ex)
                {
                    MessageBox.Show(ex.ToString ());
                    return 0;
                }
                finally
                {
                    conn.Close();
        
                }

        }
    }
}

After I got a number I'm able to open right(active) sheet:
This is in Form1.cs

private void btnImport_Click(object sender, EventArgs e)
        {
            
            try
            {
               
                if (Checking.Check_Method() == 1)//Calling my Checking class function
                {
                   
                    conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Cp\My Documents\MyExample.xlsm" + @";Extended Properties=""Excel 12.0 Macro;HDR=Yes;ImpoertMixedTypes=Text;TypeGuessRows=0""";
                    
                    string comm_1 = "select * from [MyTable]";
                    OleDbCommand command = new OleDbCommand(comm_1, conn);
                    System.Data.DataTable table_1 = new System.Data.DataTable();

                    dsDvds.Clear();
                    adapter.SelectCommand = command;
                    OleDbCommandBuilder Builder_1 = new OleDbCommandBuilder(adapter);



                    adapter.Fill(dsDvds, "MyTable");
                    table_1 = dsDvds.Tables["MyTable"];
                    dtgViewTab1.DataSource = table_1;
                    tblNameBs.DataSource = table_1;
                 
                }
                else if (Checking.Check_Method() == 2)
                {
                    //...............
                }
            

            catch (Exception ex)
            {
                MessageBox.Show("Error Occurs: "+ex .ToString ());
            }
}

But there is another problem which i rly need help.In my excel file
I have some checkBoxes.If checkBoxName.value=true then some table pop up,if checkBoxName.value=false there is no tht table in my active sheet.

So the question is How can I check a value in my CheckBox(which I made in Excel VBA.)Can somebody help me with this and add some code if it's not a problem and ofc explain how he/she did tht.

btw: i was thinking to make another ID_table for checkBox(I have 3 checkboxes in one of my sheets and + 10 sheets) but I'm sure there is some much better solutions.

This post has been edited by naturephoenix: 27 February 2011 - 03:35 PM

[fixo]

naturephoenix, on 27 February 2011 - 03:32 PM, said:

btw: i was thinking to make another ID_table for checkBox(I have 3 checkboxes in one of my sheets and + 10 sheets) but I'm sure there is some much better solutions.

Not sure about it would be a better solution
but you can rich at active workbook using interop
This will work just with opened Excel document
See code, sorry there is no good explanation in there
and the bad code formatting coz I wrote it in Sharpdevelop 3.2
Tested with .NET Framework 3.0, Excel 2007 (as .xls file, not .xlsx)

Add reference to Microsoft.Office.Interop.Excel
using System.Globalization;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using Excel=Microsoft.Office.Interop.Excel;

............................................
public void test()
{
	try 
	{
       Excel.Application xlApp = new Excel.Application();
		Excel.Workbook xlWorkBook = default(Excel.Workbook);
		Excel.Worksheet xlWorkSheet = default(Excel.Worksheet);
		Excel.Range xlRange = default(Excel.Range);
		Excel.Range xlUsedRange = default(Excel.Range);

		// get the current instance of Excel application:
		xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");

		xlWorkBook = xlApp.ActiveWorkbook;
		List<string> sheets= new List<string>();
		
		foreach (Excel.Worksheet sheet in xlWorkBook.Worksheets)
		        {
		        	sheets.Add(sheet.Name);
		        }
		string msg ="";
		foreach (string sh in sheets)
			msg=msg+sh+"\n";
		MessageBox.Show("Therea are sheets in this workbook:\n" + msg);
		xlWorkSheet = (Excel.Worksheet)xlApp.ActiveSheet;
		xlRange = (Excel.Range)xlWorkSheet.Cells;
		xlUsedRange=(Excel.Range)xlWorkSheet.UsedRange;
		
		// convert used range to table, see Google
        //Test only:

		MessageBox.Show("Active Workbook: "+ xlWorkBook.FullName + "\n" + 
                        "Active Sheet Name: "+ xlWorkSheet.Name + "\n" +
                       "Used Range Rows Count: "+  xlUsedRange.Rows.Count.ToString() + "\n"+
                      "Used Range Columns Count: "+  xlUsedRange.Columns.Count.ToString() );
        //Move to Sheet3:
        ((Excel._Worksheet)xlWorkBook.Sheets[3]).Activate(); <-Underscore character is important in this case!
       // get active sheet
        xlWorkSheet = (Excel.Worksheet)xlApp.ActiveSheet;
        MessageBox.Show("Now you was moving to sheet:\n" + xlWorkSheet.Name );
		//'Close witout changes:
		xlWorkBook.Close(false,xlWorkBook.FullName,null);
		xlApp.Quit();
		releaseObject(xlWorkBook);
       releaseObject(xlWorkSheet);
		releaseObject(xlApp);
		
		
	} 
			catch (System.Exception ex)
			{
				MessageBox.Show(ex.ToString());
	}

}

private void releaseObject(object obj)
{
	try
	{
		System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj);
		obj = null;
	} 
	catch (System.Exception ex)
	{

		System.Diagnostics.Debug.Print(ex.ToString());
		obj = null;
	}
	finally 
	{
		GC.Collect();
	}
}

This post has been edited by fixo: 28 February 2011 - 03:32 PM

[naturephoenix]

Thx fixo,but I will stay to "Reading and Writing my excel using OLEDB".
"Opened Excel document" is not a problem.
Problem is reading data(value) from Excel VBA Control(CheckBox in my case-placed in all sheets).

This post has been edited by naturephoenix: 01 March 2011 - 12:29 PM

[fixo]

I've showed you how to get the active workbook and sheet names frome there,
without this you could not read the data from separate sheet
Just IMO

[naturephoenix]

fixo, on 01 March 2011 - 03:18 PM, said:

I've showed you how to get the active workbook and sheet names frome there,
without this you could not read the data from separate sheet
Just IMO

I just try this to the one part of my application.It works great.Thx

[naturephoenix]

I have some strange problem with textboxes.In my Excel file I have 3 textboxes called(txt_1.txt_2 and txt_3). I put them all in group called "Group 99".
Let's say txt_1.value=1,txt_2.value=2, and txt_3.value=3.Then I save excel file and reopen it,everything works good.
In my textboxes I see values (1,2 and 3).If I change tht values to let's say 5,6 and 7,(txt_1.value=5......) and then I open my c# application,read the data from my textboxes which are in Group 99

int i = 0;
                foreach (Excel.Shape shp in xlWorkSheet.Shapes.Item("Group 99").GroupItems)
                {
                    
                    Excel.OLEObject excelOleObject1 = (Excel.OLEObject)shp.OLEFormat.Object;
                    if (i == 0)
                    {
                        csharp_txt_1.Text = excelOleObject1.Object.GetType().InvokeMember("Text", System.Reflection.BindingFlags.GetProperty, null, excelOleObject1.Object, null).ToString();
                     
                    
                    }
                    else if (i == 1)
                        csharp_txt_2.Text = excelOleObject1.Object.GetType().InvokeMember("Text", System.Reflection.BindingFlags.GetProperty, null, excelOleObject1.Object, null).ToString();
                    else if (i == 2)
                        csharp_txt_3.Text = excelOleObject1.Object.GetType().InvokeMember("Text", System.Reflection.BindingFlags.GetProperty, null, excelOleObject1.Object, null).ToString();
                    i++;
                }

So far everything is great.Next what I do is just releasing my objects

releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
releaseObject(xlApp);

I didnt save and close my woorkbook cuz I need to do tht manually in Excel.Now the weird part:
So I switch to my Excel file, and what I see in my textboxes are old values,txt_1.text=1,txt_2.text=2,txt_3.text=3(instead txt_1.text=5....(6,7)).
After I select one of my three textboxes, a real value is come back,but when i click somewhere else again fake value pop up again.This fake values are only displayed,they dont exist,cuz if i do something with this textboxes like Msg(txt_1.text) i will get a real value(5 in this case).Also If i call my c# application again my c# textboxes contains a real values(5,6,7).So fake values in excel are displayed but textboes contains a real value.

After I use debug i find out tht this happens after this :

releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
releaseObject(xlApp);

So i comment tht and it worked fine,until I havent stopped c# program by clicking "X" in right up corner.
Any help or ideas pls?

This post has been edited by naturephoenix: 24 March 2011 - 03:25 AM

[Sergio Tapia]

Here's a simple example on how to do this:

http://www.dreaminco...-than-it-seems/

[naturephoenix]

Sergio Tapia, on 24 March 2011 - 04:08 AM, said:

Here's a simple example on how to do this:

http://www.dreaminco...-than-it-seems/

I dont think this gonna help me.

[Sergio Tapia]

Why? Did you read the entire article? The relevant bit is here:

[HttpPost]
public ActionResult Index(HttpPostedFileBase excelFile)
{
    if (excelFile != null)
    {
        string pathToExcelFile = "PATH HERE";

        //Create a connection string to access the Excel file using the ACE provider.
        //This is for Excel 2007. 2003 uses an older driver.
        var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", pathToExcelFile);

        //Fill the dataset with information from the Hoja1 worksheet.
        var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
        var ds = new DataSet();
        adapter.Fill(ds, "results");
        DataTable data = ds.Tables["results"];

        //Create a new list of People.
        var people = new List<Person>();

        for (int i = 0; i < data.Rows.Count - 1; i++)
        {
            Person newPerson = new Person();
            newPerson.Id = data.Rows[i].Field<double?>("Id");
            newPerson.Name = data.Rows[i].Field<string>("Name");
            newPerson.LastName = data.Rows[i].Field<string>("LastName");
            newPerson.DateOfBirth = data.Rows[i].Field<DateTime?>("DateOfBirth");                    

            people.Add(newPerson);
        }
    }                   
}

While the blog didn't hold your hand on how to the everything, the relevant bit was there. You need to practice reading an article/example and applying what you learn to your context. Hope this helps.

[naturephoenix]

Sergio Tapia, on 24 March 2011 - 04:40 AM, said:

Why? Did you read the entire article? The relevant bit is here:

[HttpPost]
public ActionResult Index(HttpPostedFileBase excelFile)
{
    if (excelFile != null)
    {
        string pathToExcelFile = "PATH HERE";

        //Create a connection string to access the Excel file using the ACE provider.
        //This is for Excel 2007. 2003 uses an older driver.
        var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", pathToExcelFile);

        //Fill the dataset with information from the Hoja1 worksheet.
        var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
        var ds = new DataSet();
        adapter.Fill(ds, "results");
        DataTable data = ds.Tables["results"];

        //Create a new list of People.
        var people = new List<Person>();

        for (int i = 0; i < data.Rows.Count - 1; i++)
        {
            Person newPerson = new Person();
            newPerson.Id = data.Rows[i].Field<double?>("Id");
            newPerson.Name = data.Rows[i].Field<string>("Name");
            newPerson.LastName = data.Rows[i].Field<string>("LastName");
            newPerson.DateOfBirth = data.Rows[i].Field<DateTime?>("DateOfBirth");                    

            people.Add(newPerson);
        }
    }                   
}

While the blog didn't hold your hand on how to the everything, the relevant bit was there. You need to practice reading an article/example and applying what you learn to your context. Hope this helps.

It's not problem to fill c#'s textboxes from excel worksheet,it's problem in excel textbox after using COM objects.
btw I'm not sure u can get values from excel textbox to c# textbox using OLEDB.

[Sergio Tapia]

Disregard my previous post.

[raziel_]

Yes you can. Other way of doing this without using OLEDB is Excel reference. Check this link out:
http://csharp.net-in...el-tutorial.htm