Loop

[muzikhera]

Hi guys

Can anyone help me with this one?

I want to insert a loop in my code to read all the data in excel file here's a portion of my code.

private void btnUpdate_Click(object sender, EventArgs e)
        {
            string directory = "D:/myFile.xls";

            if (File.Exists(directory))
            {
                Excel._Application myPartFile = new Excel.ApplicationClass();
                string workbookPath = directory;
                try
                {
                    Excel.Workbook excelWorkbook = myPartFile.Workbooks.Open(workbookPath, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows,
                        "\t", false, false, 0, false, true, false);
                    Excel.Sheets excelSheets = excelWorkbook.Worksheets;
                    Excel.Worksheet excelWorkSheet = (Excel.Worksheet)excelSheets.get_Item(1);
                                        
                    Excel.Range excelPartNumber = (Excel.Range)excelWorkSheet.get_Range("A1", "A1");
                    textBox1.Text = String.Format("{0:N0}", excelPartNumber.Value2);

                    Excel.Range excelPartName = (Excel.Range)excelWorkSheet.get_Range("B1", "B1");
                    textBox2.Text = String.Format("{0:N0}", excelPartName.Value2);

                    Excel.Range excelLocCode = (Excel.Range)excelWorkSheet.get_Range("C1", "C1");
                    textBox3.Text = String.Format("{0:N0}", excelLocCode.Value2);

                    Marshal.ReleaseComObject(excelSheets);
                    excelWorkbook.Close(false, "", false);
                    Marshal.ReleaseComObject(excelWorkbook);
                    myPartFile.Quit();
                    Marshal.ReleaseComObject(myPartFile);
                    GC.Collect();
                        
                }
                catch (COMException)
                {
                    MessageBox.Show("Cannot open the file exclusively.Other program maybe using it.\r\nClose the file/program and try again.", "File Opening Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }            
        }

I need to increment the A1, B1, C1 until i reach the last rows with value.

Hope to received any reply with this.

Thanks in advance ;)

[lolafuertes]

You can use the Cell(int row, int column) range instead. This is an 'easy' way to navigate in the Excel sheet.

To do that, you need to set the excel application ReferenceStyle to the xlReferenceStyle.xlR1C1 in order to enumerate the rows and columns by numeric integer value starting at 1.

Then you can substitute the .get_Range("A1", "A1") by .Cells( 1, 1).

You can find the last row to precess using the UsedRange.Rows.Count and UsedRange.Columns.Count properties of the worksheet.

Hope this helps.

[muzikhera]

Hi lolafuertes,

My File is fixed in .xls extension. I want my .get_Range("A1", "A1") to have loop.

Do you think it is possible?

Thanks again.

[lolafuertes]

Yes. It is.

In order to have a loop, you need a loop structure like for or while.

Inside the loop, you need to modify the row to read from. When you request the range from cell "A1" to cell "A1", you are requesting the cell at column 1 (A) and the row 1, with "B1" you are requesting the column 2(B) and row 1 and with "C1" you are requesting column 3 (C) and row 1.

If you implement a row (integer) counter, you can cicle over the rows in the excel sheet, changing the row suffix of the cell name, in the get_range call, using string cellName="A"+row.ToString(); in order to get the cell name for column 1 (A).

This way, you will call the getr_range funcion like get_range(cellName, cellName) .

In order exit timely from the loop you need to know the number of filled rows before starting the loop, or you can break the loop when the cell in colum A has an empty text.

Here we have another problem to solve: the output. If you want to show all the excel sheet content, having text boxes, maybe, is not the best aproach, even it can be valid. If you only wants to show the last row with data, then this is not a bad aproach.

If you want to show all the rows in the tex boxes, before starting the loop you'll need to empty the Text property of the text boxes like textbox1.Text=""; ad, inside the loop you can add the contents using textBox1.Text += String.Format("{0:N0}\n", excelPartNumber.Value2); .

Where to put the loop? I wil suggest to put it in the line 15 of your example, enclosing your lines 16 to 23.

Hope this helps

[muzikhera]

I tried to insert your suggestion in my code. See below code:

try
                {
                    int x = 0;
                    string cellName = "A" + x;
                    for (x = 0; x < 5; x++)
                    {
                    //    cell = "A" + A;
                        //Get Part Number
                        Excel.Range excelPartNumber = (Excel.Range)excelWorkSheet.get_Range(cellName, cellName);
                        textBox1.Text = "";
                        textBox1.Text += String.Format("{0:N0}", excelPartNumber.Value2);
                        
                    }                    
                    Marshal.ReleaseComObject(excelSheets);
                    excelWorkbook.Close(false, "", false);
                    Marshal.ReleaseComObject(excelWorkbook);
                    myPartFile.Quit();
                    Marshal.ReleaseComObject(myPartFile);
                    GC.Collect();                    
                }
                catch (COMException)
                {
                    MessageBox.Show("Cannot open the file exclusively.Other program maybe using it.\r\nClose the file/program and try again.", "File Opening Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }

But it always give me the COMException Message!

[lolafuertes]

Can you be so kind to indicate in wich line the COMException appears and wich is the associated error value?

Which Excel version are you using?

Which Interop assemblies have you added to your project?

Yours