export data from excel to C# datagrid view

[judithSampathwa]

hi there,
i have a excel file which have purchase order information. the file contains of the information of the user and the the items that he ordered with the order item quantity and the price but there may be any amount of items in the excel sheet. the thing is how can i get all the items details from the excel sheet while there is no item details. something like a for loop(how can i get the nuber of rows in the excel sheet for the order items)

the code i need to edit is below:

Microsoft.Office.Interop.Excel.ApplicationClass tt = new Microsoft.Office.Interop.Excel.ApplicationClass();

            tt.Workbooks.Open("C:\\Documents and Settings\\awaduge\\Desktop\\Purchase _Order1.xls",
                0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
                true, false, 0, true, false, false);

            String name = tt.get_Range("b8", "b8").Text.ToString();
            String OrderDate = tt.get_Range("h8", "h8").Text.ToString();
            
            txtName.Text = name;
            txtDate.Text = OrderDate;

           [B] for (int r = 0; r < 5; r++) 
            {[/B]
                int quantity = Convert.ToInt32(tt.get_Range("g12", "g12").Text);
                double pricePerUnit = Convert.ToDouble(tt.get_Range("h12", "h12").Text);
                
                dgvOrderDetails.Rows[r].Cells[0].Value = tt.get_Range("a12", "a12").Text.ToString();
                dgvOrderDetails.Rows[r].Cells[1].Value = tt.get_Range("b12", "b12").Text.ToString();
                dgvOrderDetails.Rows[r].Cells[2].Value = tt.get_Range("g12", "g12").Text.ToString();
                dgvOrderDetails.Rows[r].Cells[3].Value = tt.get_Range("h12", "h12").Text.ToString();
                dgvOrderDetails.Rows[r].Cells[4].Value = (quantity * pricePerUnit).ToString();
 
          [B]  }
            [/B]
            tt.Workbooks.Close();

i have attached the sample excel file

it is in txt format delete the txt format and use the excel file
thanxxxxxxx

[nick.crane]

Change the condition part of your for loop to test the item cell in the excel sheet for empty.

[judithSampathwa]

Change the condition part of your for loop to test the item cell in the excel sheet for empty.

yeah , i know that i should do that, but do u know how to do that,

thanxxxx
appriciate if u could help me
thanxxxxxxxx

[nick.crane]

Firstly the parameters in get_Range should be made a string variable e.g. cellStr.
Set this using something like cellStr = String.Format("b{0}", r+12); Now you can check if the cell value is empty with something like string.IsNullOrEmpty(tt.get_Range(cellStr, cellStr).Text) Not fully sure if this will work because I have not used Microsoft.Office.Interop before.
But give it a try.

[judithSampathwa]

Firstly the parameters in get_Range should be made a string variable e.g. cellStr.
Set this using something like cellStr = String.Format("b{0}", r+12); Now you can check if the cell value is empty with something like string.IsNullOrEmpty(tt.get_Range(cellStr, cellStr).Text) Not fully sure if this will work because I have not used Microsoft.Office.Interop before.
But give it a try.

ok,
hey can u explain to me the below code u have written with it's parameters

cellStr = String.Format("b{0}", r+12);

[nick.crane]

According to MSDN .Net Class Library the String.Format function:

"Replaces each format item in a specified string with the text equivalent of a corresponding object's value."

In the code cellStr = String.Format("b{0}", r+12); the object is r+12 and is an interger value, the "format item" is the {0} part of "b{0}" , the b in this is the cell column for you excel sheet.

Perhaps this example will help:

int X = 20;
double Y = 25.7
MyString = String.Format("The value of X is {0} and Y is {1}", X, Y);
// MyString now has a value of "The value of X is 20 and Y is 25.7"

The "format item" can contain additional parts that define how the object is to be converted in to a string. This is most useful when formating date or time values. However, it is too much for me to go in to here. Please read the extensive help on String.Format online in MSDN.