I have a code wherein I am taking two files. Both are Excel files and I need to copy one file to another. The number of rows and columns in the files are dynamic.
sFile
is the source file and sFile1
is the destination file. I need to know the number of rows in source file to run a for loop when am copying it to destination file.
The code is below:
Dim shtlight As New SLDocument(sFile1)
Dim lrow As Integer
Dim lcol As Integer
Dim sflie = Environment.Directory & "\Data.xls"
lrow = 2
Dim shtlight1 As New SLDocument(sFile1)
For each row in sfile1
for column in sfile1 'code here to set value next next
I want help in running this for loop, don't know how to set the upper bound as no of rows and columns in file is dynamic.
Can someone help please? I am using phone so format might not be correct. :(
As I mentioned in the comments above, I have never worked with SpreadsheetLight
. I just downloaded SpreadsheetLight
and experimented with it.
To get the last row you need to use this
Imports DocumentFormat.OpenXML
Imports DocumentFormat.OpenXML.Spreadsheet
Imports SpreadsheetLight
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim sl As New SLDocument("C:\Users\Siddharth\Desktop\Tester.xlsx", "Sheet1")
MessageBox.Show (sl.GetWorksheetStatistics.EndRowIndex)
End Sub
End Class
Note
For someone who is novice like me to SpreadsheetLight and would like to experiment.
Reference set to DocumentFormat.OpenXml.dll (Open XML SDK 2.0). Can be downloaded from Here. It didn't work with Open XML SDK 2.5 for me and I didn't put too much into it to find out to why...
Reference set to SpreadsheetLight. Can be downloaded from Here
Above code is tested on VS 2013