excelvb.netspreadsheetlight

How to copy one Excel file to another using spreadsheet light in VB.NET?


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. :(


Solution

  • 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
    

    enter image description here

    Note

    For someone who is novice like me to SpreadsheetLight and would like to experiment.

    1. 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...

    2. Reference set to SpreadsheetLight. Can be downloaded from Here

    Above code is tested on VS 2013