Closed XML - Excel - VB,Net

Question

I'm pretty new to the field, I have been trying to use ClosedXML to create and modify Excel files automatically.

But this doesn't seem to be working. I'm basically trying to copy data from 2 files into a third one (a new file) with this code:

Imports System
Imports System.Collections.Specialized
Imports System.Runtime.InteropServices
Imports System.Security.Cryptography
Imports System.Windows.Forms.VisualStyles.VisualStyleElement
Imports ClosedXML
Imports ClosedXML.Excel
Imports DocumentFormat.OpenXml.Spreadsheet

Public Class Form1
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click

        Dim wb1 As IXLWorkbook = New XLWorkbook("C:\Users\oxy\Downloads\Test\Test.xlsx")
        Dim ws As IXLWorksheet = wb1.AddWorksheet
        wb1.SaveAs("C:\Users\oxy\Downloads\Test\Test2.xlsx")

        Dim wb2 As IXLWorkbook = New XLWorkbook(TextBox1.Text)
        Dim ws2 As IXLWorksheet = wb2.AddWorksheet
        ws.Cell(1, 1).InsertData(ws2.Columns("A:G"))

        wb1.SaveAs("C:\Users\oxy\Downloads\Test\Test2.xlsx")

        Dim wb3 As IXLWorkbook = New XLWorkbook(TextBox2.Text)
        Dim ws3 As IXLWorksheet = wb3.AddWorksheet
        ws.Cell(1, 6).InsertData(ws3.Columns("A:N"))
        wb1.SaveAs("C:\Users\oxy\Downloads\Test\Test2.xlsx")

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        OpenFileDialog1.Filter = "Excel | *.xls; *.xlsx"
        OpenFileDialog1.ShowDialog()
        TextBox1.Text = OpenFileDialog1.FileName

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        OpenFileDialog2.Filter = "Excel | *.xls; *.xlsx"
        OpenFileDialog2.ShowDialog()
        TextBox2.Text = OpenFileDialog2.FileName

    End Sub
End Class

I thought of the possibility of having mistakes in either the way I tried to copy columns from a file to another OR the way of selecting a range of columns might not be correct.

I tried some different ways but it doesn't seem to be the problem this is my first ever post in this community, so sorry for any mistakes, thank you in advance.

Answer 1

As you've tagged this question C# that's what I'll answer in, you'll have to convert it to VB yourself.

You have two remaining problems at the time of writing:

1. You are trying to copy from new (empty) worksheets
2. InsertData() cannot take a Columns object. It appears to because it takes an Enumerable, and Columns is Enumerable, but Enumerating it does not return the data in the cells. So once you've sorted problem 1, you'll just get nonsense in your created file.

I think this does what you're trying to do, though I'll caveat that I'm guessing as you've not actually told us more than "I want to copy data"

private void Button4_Click( /* args */)
{
    var wb1 = new XLWorkbook(@"C:\Users\oxy\Downloads\Test\Test.xlsx");
    var ws = wb1.AddWorksheet();
    
    var wb2 = new XLWorkbook(TextBox1.Text);
    var ws2 = wb2.Worksheet(1);// assume you want the first
    ws2.Range("A:G").CopyTo(ws.Cell("A1"));

    // repeat for file 2
    
    wb1.SaveAs(@"C:\Users\oxy\Downloads\Test\Test2.xlsx");
}