![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(288, 40%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERG%3C/text%3E%3C/svg%3E)
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,821 questions
Hello,
If you are open to an alternative using a free library which does not require Excel to be installed. In short you have a fresh copy of the Excel file which gets copied to (in this case) to the application folder which of course you can change.
Otherwise back to your current code, it appears the last row is off and needs to be set as per this Microsoft page.
- Install from NuGet package manager SpreadSheetLight and DocumentFormat.OpenXml (may not need this one).
- Create a class named ExcelOperations1 as per below.
- Add form code as per below.
- Place a copy of the Excel file say in a folder beneath the application folder then in code below that file is copied to the application folder and data is placed into the copied Excel file.
Full source
Full source is located in the following GitHub repository project. To run the code sample, perform a NuGet Package restore, run the following script to create the database. Or simply view the source.
ExcelOperations1.vb
Notes
- There is actually more code here than needed. This is because you might benefit from the additional methods as all SpreadSheetLight documentation is in C#.
- An event is available for when a runtime exception is thrown e.g. if the Excel file does not exists, the sheet does not exists or if you run the code, open the file, keep it open while running the code again which would cause an exception because you can't write to an open file.
.
Imports System.IO
Imports SpreadsheetLight
Public Class ExcelOperations1
Public Delegate Sub OnErrorDelegate(exception As Exception)
Public Shared Event OnErrorEvent As OnErrorDelegate
''' <summary>
''' Insert DataTable into an existing Excel WorkSheet
''' </summary>
''' <param name="pFileName">Existing Excel file</param>
''' <param name="pSheetName">Existing WorkSheet in pFileName</param>
''' <param name="pDataTable">DataTable</param>
''' <param name="pColumnHeaders">Use column name from DataTable as headers</param>
''' <param name="pStartRow">Row to start the import</param>
Public Shared Function Import(
pFileName As String, pSheetName As String,
pDataTable As DataTable, pColumnHeaders As Boolean,
Optional pStartRow As Integer = 3) As Boolean
Try
CopyFile(pFileName)
Using doc As New SLDocument(pFileName)
doc.SelectWorksheet(pSheetName)
doc.ImportDataTable(pStartRow, SLConvert.ToColumnIndex("A"), pDataTable, pColumnHeaders)
doc.Save()
Return True
End Using
Catch ex As Exception
RaiseEvent OnErrorEvent(ex)
Return False
End Try
End Function
Private Shared Sub CopyFile(pFileName As String)
Dim originalFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ExcelFiles", pFileName)
Dim targetFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, pFileName)
File.Copy(originalFile, targetFile, True)
End Sub
Public Shared Function SheetExists(doc As SLDocument, pSheetName As String) As Boolean
Return doc.GetSheetNames(False).Any(Function(sheetName) sheetName.ToLower() = pSheetName.ToLower())
End Function
Public Function SheetNames(pFileName As String) As List(Of String)
Using doc = New SLDocument(pFileName)
Return doc.GetSheetNames(False)
End Using
End Function
''' <summary>
''' Remove a sheet if it exists.
''' </summary>
''' <param name="pFileName">Existing Excel file</param>
''' <param name="pSheetName"></param>
''' <returns></returns>
Public Function RemoveWorkSheet(pFileName As String, pSheetName As String) As Boolean
Using doc As New SLDocument(pFileName)
Dim workSheets = doc.GetSheetNames(False)
If workSheets.Any(Function(sheetName) sheetName.ToLower() = pSheetName.ToLower()) Then
If workSheets.Count > 1 Then
doc.SelectWorksheet(doc.GetSheetNames().FirstOrDefault(Function(sName) sName.ToLower() <> pSheetName.ToLower()))
ElseIf workSheets.Count = 1 Then
RaiseEvent OnErrorEvent(New Exception("Can not delete the sole worksheet"))
End If
doc.DeleteWorksheet(pSheetName)
doc.Save()
Return True
Else
Return False
End If
End Using
End Function
''' <summary>
''' Add a new sheet if it does not currently exists.
''' </summary>
''' <param name="pFileName"></param>
''' <param name="pSheetName"></param>
''' <returns></returns>
Public Function AddNewSheet(pFileName As String, pSheetName As String) As Boolean
Using doc = New SLDocument(pFileName)
If Not (SheetExists(doc, pSheetName)) Then
doc.AddWorksheet(pSheetName)
doc.Save()
Return True
Else
Return False
End If
End Using
End Function
End Class
Form code
In this case I populate a DataTable from a database, assigned to a DataGridView. Where the data comes from is irrelevant and if using a DataSet rather than a DataTable simple pass the Table e.g. SomeDataSet.Tables("SomeTableName").
Public Class Form1
Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
DataGridView1.DataSource = DataOperationsSqlServer.LoadCustomerRecordsUsingDataTable()
AddHandler ExcelOperations1.OnErrorEvent, AddressOf OnExcelExportError
End Sub
Private Sub ExportSimpleButton_Click(sender As Object, e As EventArgs) Handles ExportSimpleButton.Click
Dim dt = CType(DataGridView1.DataSource, DataTable)
If ExcelOperations1.Import("DemoExport.xlsx", "Info_Table", dt, False) Then
MessageBox.Show("Done")
End If
End Sub
Private Sub OnExcelExportError(exception As Exception)
MessageBox.Show(exception.Message)
End Sub
End Class