How to remove all rows from ExcelWorksheet

GemBox.Spreadsheet
1

Hi Team,

How to delete all rows from ExcelWorksheet?

i have copied the existing worksheet and will try to delete all row in new sheet.

Dim newSheet As ExcelWorksheet = ef.Worksheets.AddCopy(“Sheet2”, sheet1)

Version:37.3.30.1000

Regards,
Girish

2

Hi Girish,

Try this:

For i As Integer = newSheet.Rows.Count - 1 To 0
    newSheet.Rows(i).Delete()
Next

Or with the current latest version, you could use this:

newSheet.Rows.Remove(0, newSheet.Rows.Count)

I hope this helps.

Regards,
Mario

3

Thanks for replying quickly.

Dim newSheet As ExcelWorksheet = ef.Worksheets.AddCopy(“Sheet2”, sheet1)

i am getting Object refence error, but ef, sheet1 has the instance

4

Please create a small Visual Studio project that reproduces your issue so we can investigate it.

5

Public Sub DownloadDataAsExcelFile()
Try

    'load data from excel into DataTable
    Dim filePath As String = "SampleData.xlsx"
    SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
    Dim ef As ExcelFile = ExcelFile.Load(filePath)
    Dim sheet1 As ExcelWorksheet = ef.Worksheets.FirstOrDefault()
    Dim dto As CreateDataTableOptions = New CreateDataTableOptions()
    Dim dt As DataTable = sheet1.CreateDataTable(dto)

    ' Save the Excel file
    Dim saveFileDialog As New SaveFileDialog With {
            .Filter = "Excel files (*.xlsx)|*.xlsx",
            .FilterIndex = 1,
            .RestoreDirectory = True
            }

    If saveFileDialog.ShowDialog() = DialogResult.OK Then
        ef.Save(saveFileDialog.FileName)
    Else
        Exit Sub
    End If

	'Writing the data to sheet1
     WriteDataToSheet1()
     
	 'Add new sheet2 by copying the sheet1
	 Dim newSheet As ExcelWorksheet=ef.Worksheets.AddCopy("Sheet2",sheet1)
            For i As Integer = newSheet.Rows.Count - 1 To 0
                newSheet.Rows(i).Delete()
            Next
	
   Catch ex As Exception
       
   Finally
   End Try

End Sub

6

Please create a small Visual Studio project that reproduces your issue so we can investigate it.
You can send us the project via email or support ticket, see the Contact page.