Merging excel documents WPF C#

Question

0.00/5 (No votes)

See more:

I am trying to make a simple program to merge excel files and having trouble loading the files in the ListBox individually.

The user can select multiple file which appear into a ListBox, then when merge is clicked a new file is generated with the name given in the TextBox at the side.

My problem is when i come to load the files to merge from the ListBox.

C#

btnMergeFile_Click(object sender, RoutedEventArgs e)
{
    Workbook workbook = new Workbook();
    workbook.LoadFromFile(@"filename.xlsx");
    Workbook workbook2 = new Workbook();
    workbook2.LoadFromFile(@"filename.xlsx");
}

Is it possible to call the list names individually?

Sorry I'm new to c# and wpf.

Here is literally all the code!

XAML

XML

<DockPanel Margin="10">
    <WrapPanel HorizontalAlignment="Center" DockPanel.Dock="Top" Margin="0,0,0,10">
        Select Files
        Merge Files
        Clear Files
        <TextBox x:Name="newFileName" TextAlignment="Left" HorizontalAlignment="Center" Width="150" Text="New File Name"/>
    
    <ListBox x:Name="listBox1" />
</DockPanel>

.CS

C#

using System;
using System.Windows;
using Microsoft.Win32;
using System.Data;
using Spire.Xls;

namespace ExcelMerge_1._1
{
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        public void btnSelectFile_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Multiselect = true;
            openFileDialog.Filter = "csv files (*.csv)|*.csv|Excel files (*.XLSX)|*.XLSX";
            openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);

            if (openFileDialog.ShowDialog() == true)
            {
                foreach (string filename in openFileDialog.FileNames)

                    listBox1.Items.Add(System.IO.Path.GetFullPath(filename));
            }

        }

        public void btnMergeFile_Click(object sender, RoutedEventArgs e)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"filename.xlsx");
            Workbook workbook2 = new Workbook();
            workbook2.LoadFromFile(@"filename.xlsx");


            Worksheet sheet2 = workbook2.Worksheets[0];
            DataTable dataTable = sheet2.ExportDataTable();
            Worksheet sheet1 = workbook.Worksheets[0];
            sheet1.InsertDataTable(dataTable, false, sheet1.LastRow + 1, 1);

            workbook.SaveAsXml(newFileName.Text);
        }

        private void btnClearFile_Click(object sender, RoutedEventArgs e)
        {
            listBox1.Items.Clear();
        }
    }
}

What I have tried:

First tried interlop and couldn't get that working so decided to try Spire, looks promising however this simple hurdle is getting me!

Posted 17-Sep-17 11:12am

Member 13414500

Updated 17-Sep-17 12:53pm

Graeme_Grant

v2

Add a Solution

Comments

Graeme_Grant 17-Sep-17 18:33pm

What exactly is your question?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Graeme_Grant · Answer 1 · 2017-09-17T12:53:00

Is it possible to call the list names individually? Yes. Here is an easier way of doing it:

C#

public partial class MainWindow : Window
{
    public MainWindow()
    {
        InitializeComponent();
        DataContext = this;
    }

    public ObservableCollection<XlFile> XlFiles { get; }
        = new ObservableCollection<XlFile>();

	public void btnSelectFile_Click(object sender, RoutedEventArgs e)
	{
		OpenFileDialog openFileDialog = new OpenFileDialog();
		openFileDialog.Multiselect = true;
		openFileDialog.Filter = 
                    "csv files (*.csv)|*.csv|Excel files (*.XLSX)|*.XLSX";
		openFileDialog.InitialDirectory =
                    Environment.GetFolderPath(
                        Environment.SpecialFolder.MyDocuments);

		if (openFileDialog.ShowDialog() == true)
		{
			foreach (string filename in openFileDialog.FileNames)

            XlFiles.Add(new XlFile
            {
                Name = System.IO.Path.GetFileName(filename),
                FullPath = filename
            });
		}

	}

    public void btnMergeFile_Click(object sender, RoutedEventArgs e)
    {
        foreach (var xlFile in XlFiles)
        {
            // do work here...
        }
    }
}

public class XlFile
{
    public string Name { get; set; }
    public string FullPath { get; set; }
}

And the Xaml for the ListBox:

XML

<ListBox ItemsSource="{Binding XlFiles}" DisplayMemberPath="Name"/>