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

Question

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

Have you seen [THIS](https://stackoverflow.com/questions/31325827/row-count-with-spreadsheetlight) — Siddharth Rout, Nov 30 '17 at 07:51
I have never worked with `SSLight`. However I am ready to experiment with you :) Does `.GetWorksheetStatistics()` work in vb.net for you? — Siddharth Rout, Nov 30 '17 at 08:03
Some thing like `Private stats = shtlight1.GetWorksheetStatistics()` and then `Private rowcount = stats.NumberOfRows`? — Siddharth Rout, Nov 30 '17 at 08:05
what is stats? It does work but I need to declare stats, what is the variabe type? — Divya Roopam, Nov 30 '17 at 08:40

Siddharth Rout · Accepted Answer · 2017-11-30T10:24:46.140

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

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

1 Answers1