c# winforms: Unable to export data from multiple rows in DTG to last available row in excel

Question

I am unable to export data from multiple rows in DTG to specific columns in excel. I have tried multiple methods from forums. So far, the results are excel opened but no data are exported (empty cells) or only the last row of DTG are copied to the specific columns in excel.

I want this excel sheet to compile the DTG data. Hence, example user open form 1st time, enter DTG data, saves to excel and closes form. User then open form 2nd time and enter another DTG data, the 2nd DTG data will go into the same excel columns but on the next empty row (underneath the row of the 1st DTG data).

Most codes that I've tried comes from this link [Programmatically getting the last filled excel row using C# ]. I did not put the column part as I only want last row. Do note that the skeleton of all the codes are the same but the way lastUsedRow was initialized are different.

Codes below gives empty cells

Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
try
        {
            oXL = new Microsoft.Office.Interop.Excel.Application();
            oWB = oXL.Workbooks.Open("C:\\Users\\User\\Test.xlsx");
            oSheet = oXL.Worksheets["Vehicles"];

            Excel.Range last = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
            Excel.Range range = sheet.get_Range("A1", last);

            int lastUsedRow = last.Row;


            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                for (int j = 1; j < dataGridView1.Columns.Count; j++)
                {
                    oSheet.Cells[lastUsedRow, j+1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }

OR

 int lasUsedRow = oSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row;

Codes below shows only last row of DTG

int lastUsedRow = oSheet.Cells.Find("*",System.Reflection.Missing.Value, 
System.Reflection.Missing.Value, System.Reflection.Missing.Value,    Excel.XlSearchOrder.xlByRows,Excel.XlSearchDirection.xlPrevious, false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Row;

OR

int lastUsedRow = oSheet.Range["B" + oSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row+1;

Hope to get some help. Thank you so much!

Answer 1

As per Yoshi's comment, below is the updated code where it allows multiple datagridview rows to be added and compiled into excel.

        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;

        try
        {
            //Start Excel and get Application object.
            oXL = new Microsoft.Office.Interop.Excel.Application();

            //Get a new workbook.
            oWB = oXL.Workbooks.Open("C:\\Users\\User\\Test.xlsx");

            //Specify different sheet names
            oSheet = oXL.Worksheets["Vehicles"];

            //Define last row
            int _lastRow = oSheet.Range["B" + oSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row+1;

            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                for (int j = 1; j < dataGridView1.Columns.Count; j++)
                {
                    oSheet.Cells[_lastRow, j+1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }

                _lastRow++;
            }

            //Make sure Excel open and give the user control of Microsoft Excel's lifetime.
            oXL.Visible = true;
            oXL.UserControl = true;

            //Autosave excel file
            oWB.Save();

            Marshal.ReleaseComObject(oXL);
        }
        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");
        }
    }