Code of clearing redundant rows in Excel file

[candice]

Hi guys,
What I'm trying to do now is to export the data in datagridview control into an Excel File, save and close it. The code is runing well.
The problem I'm facing is that, if the number of second time's rows of data in datagridview is less then the first time's. The redundant rows in Excel file will not be cleared but leave there. Say, if first time I have 15 rows of data, and second time the number of rows is 10. The program will open the Excel file, change the data in the first 10 rows, but leave the following 5 rows unchanged.
I tried to write a loop to clear the redundant rows which I don't think is the best way because it will only clear the 20 rows and 10 columns redundant data( of course I can change the number but it's not what I expected).
So, my question is: is there a way to detect the redundant data and clear it? My sample code is like following:
Export data into Excel file:

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] r [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] c [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] r = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dataGridView1.Rows.Count - 1
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] c = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dataGridView1.Rows(r).Cells.Count - 1
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] s [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dataGridView1.Rows(r).Cells(c).Value
oXLSheet.Cells.Item(r + 1, c + 1).Value = s
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE]

Clear the redundant data:

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] a [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] b [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] a = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dataGridView1.Rows.Count + 1 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dataGridView1.Rows.Count + 20
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] b = 1 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] 10
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Not[/COLOR][/SIZE][SIZE=2] oXLSheet.Cells.Item(a, b).Value = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]oXLSheet.Cells.Item(a, b).Value = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE]

And help will be very appreciated! Thank you!

 

[cjard]

I have a much faster, cleaner way of generating Excel files, and the necessary code doesnt even require excel be installed on the machine.. Would you like to see it?