Click here to Skip to main content
15,895,557 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
Hi,I have a excel sheet.
In excel sheet there are two columns german and english.
In german and english columns suppose there are 5 rows. when i am open a excel sheet then automatically A2,B2 ranges word are came in two text boxes. It works fine But now i want when i click on button "Next word" then i want next range "A3,B3" of excel sheet on two text boxes.

how to open another range's of excel file ?

I am using this code-
C#
OpenFileDialog openexcel = new OpenFileDialog();
                openexcel.Title = "Mayank";
                openexcel.InitialDirectory = @"c:\";
                openexcel.RestoreDirectory = true;
                openexcel.DefaultExt = ".xls";
                openexcel.Filter = "xls files (*.xls)|*.xls|All files (*.*)|*.*";
                openexcel.FilterIndex = 2;
                if (openexcel.ShowDialog() == DialogResult.OK)
                {
                    Excel.Application xlApp;
                    Excel.Workbook xlWorkBook;
                    Excel.Worksheet xlWorkSheet;
                    object filename = openexcel.FileName;
                    object misValue = System.Reflection.Missing.Value;
                    xlApp = new Excel.ApplicationClass();
                    xlWorkBook = xlApp.Workbooks.Open(filename.ToString(), misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                    //xlApp.Visible = true;
                    txtsearchgerman.Text = (xlWorkSheet.get_Range("A2", "A2").Value2.ToString());
                    txtsearchenglish.Text = (xlWorkSheet.get_Range("B2", "B2").Value2.ToString());
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
                    xlApp = null;


Thanks in advance.
Posted
Updated 5-Feb-12 23:31pm
v3
Comments
Herman<T>.Instance 6-Feb-12 5:31am    
why open it in a textbox and not in the datagridview (which have cells and columns just like Excel)?
mayankshrivastava 6-Feb-12 5:33am    
i don't know how to open excel data in datagrid view on every click
mayankshrivastava 6-Feb-12 5:37am    
actually i am searching that particular word in another textbox.
BillWoodruff 6-Feb-12 7:31am    
Hi, it seems you have solved the basic tasks of opening Excel, via Automation, from C#, diplaying it in a Window in a C# WinForms Form, and being able to access cell values: congratulations !

But, when you say, in a later coment: "actually i am searching that particular word in another textbox."

What that means to me is that you have NOT described what you are doing, in your original question, accurately.

Please, go back and re-write your original question so it reflects exactly what you are trying to do.

And, if your goal is searching, from a TextBox entry on a WinForm, for a matching value (text) in a Cell: then examine what native methods the Excel App you are using offers you to search.

The task of moving to the next row in the Excel instance when a Button is clicked on the WinForm, should be extremely easy to accomplish. Look at Excel's methods for Selection, or setting the Current Cell, or whatever.
mayankshrivastava 6-Feb-12 8:26am    
suppose there are two columns in excel sheet german and english
so when i click on button i want these two column values(A2) in first text boxs and B2 in second text box.
and when i click again in button i want next two values A3 in first text box and B3 in second text box.

1 solution

The suggested solution is shown in bold with comments. I hope this may help you.

C#

//Attach click event of button to the click event handler
NextWord.Click += NextWord_Click;

//Declare fields to hold the excel rownumber and cell addresses
int rowNumber;
string germanCell;
string englishCell;


OpenFileDialog openexcel = new OpenFileDialog();


//Initialize the row number
rowNumber = 2;
                openexcel.Title = "Mayank";
                openexcel.InitialDirectory = @"c:\";
                openexcel.RestoreDirectory = true;
                openexcel.DefaultExt = ".xls";
                openexcel.Filter = "xls files (*.xls)|*.xls|All files (*.*)|*.*";
                openexcel.FilterIndex = 2;
                if (openexcel.ShowDialog() == DialogResult.OK)
                {
                    Excel.Application xlApp;
                    Excel.Workbook xlWorkBook;
                    Excel.Worksheet xlWorkSheet;
                    object filename = openexcel.FileName;
                    object misValue = System.Reflection.Missing.Value;
                    xlApp = new Excel.ApplicationClass();
                    xlWorkBook = xlApp.Workbooks.Open(filename.ToString(), misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                    //xlApp.Visible = true;

           
//Populate the text boxes with the first values
                    CreateCellAddresses();
                    txtsearchgerman.Text = GetTextFromExcel(germanCell);
                    txtsearchenglish.Text = GetTextFromExcel(englishCell);

                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
                    xlApp = null;



//Define a method to handle the click event of the NextWork button
private void NextWord_Click(object sender, EventArgs e)
{
//Increment the rownumber
    rowNumber++;
//Create cell addresses
    CreateCellAddresses();
//Populate the text boxes with the words from the next row of the excel
    txtsearchgerman.Text = GetTextFromExcel(germanCell);
    txtsearchenglish.Text = GetTextFromExcel(englishCell);
}
//Define a method to create cell addresses of the next words
private void CreateCellAddresses()
{
    germanCell = string.Format("A{0},A{0}",rowNumber);
    englishCell = string.Format("B{0},B{0}",rowNumber);
}
//Define a method to get the text from excel cell
private string GetTextFromExcel(string cellAddress)
{
    return (xlWorkSheet.get_Range(cellAddress).Value2.ToString());
}
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900