Merging excel documents

Question

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.

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.

XAML

    <DockPanel Margin="10">
        <WrapPanel HorizontalAlignment="Center" DockPanel.Dock="Top" Margin="0,0,0,10">
            <Button x:Name="btnSelectFile" Width="75" Height="30" Margin="5" Click="btnSelectFile_Click">Select Files</Button>
            <Button x:Name="btnMergeFile" Width="75" Height="30" Margin="5" Click="btnMergeFile_Click">Merge Files</Button>
            <Button x:Name="btnClearFile" Width="75" Height="30" Margin="5" Click="btnClearFile_Click">Clear Files</Button>
            <TextBox x:Name="newFileName" TextAlignment="Left" HorizontalAlignment="Center" Width="150" Text="New File Name"/>
        </WrapPanel>
        <ListBox x:Name="listBox1" />
    </DockPanel>

</Grid>

.CS

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();
    }
}
}

score 0 · Answer 1 · answered Sep 18 '17 at 00:43

I guess that you only need to get the items that you previously added in the listbox, so you can use the same collection you used when adding items:

foreach(var fileName in listBox1.Items)
{
     Workbook workbook = new Workbook();
     workbook.LoadFromFile(fileName);
}

But as a starter I would recommend you read about the MVVM architecture which will not only help you in this scenario but keep your code logically structured. You can see this other question: MVVM: Tutorial from start to finish?

score 0 · Answer 2 · answered Sep 19 '17 at 10:20

You mentioned that the user can select multiple files, so I believe you need this:

<ListBox x:Name="listBox1" SelectionMode="Multiple" />

Also you'll iterate through those selected files by using ListBox.SelectedItems collection.
Also here is a complete btnMergeFile_Click event that you can try:

private void btnMergeFile_Click(object sender, RoutedEventArgs e)
{
    ExcelFile workbook = new ExcelFile();

    // Copy all sheets into a resulting ExcelFile.
    foreach (string file in this.listBox1.SelectedItems)
    {
        ExcelFile temp = ExcelFile.Load(file);
        foreach (ExcelWorksheet sheet in temp.Worksheets)
            workbook.Worksheets.AddCopy(
                // Unique sheet name.
                string.Format("{0} - {1}", System.IO.Path.GetFileNameWithoutExtension(file), sheet.Name),
                // Sheet object.
                sheet);
    }

    // Save ExcelFile.
    workbook.Save(this.newFileName.Text);
}

Note, I've used GemBox.Spreadsheet to merge the selected excel files.

score 0 · Accepted Answer · answered Sep 20 '17 at 06:57

First, you need to set the selection mode of the listbox as multiple like NixonUposseen suggested.

Second, merge the selected excel files and csv files using code as below. I've already tried it and it worked well on my side.

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

namespace MergeExcel
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }        

        private void btnSelectFile_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Multiselect = true;
            openFileDialog1.Title = "Select Files";
            openFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            openFileDialog1.Filter = "Files(*.xls;*.xlsx;*.csv)|*.xls;*.xlsx;*.csv";            
            openFileDialog1.FilterIndex = 1;
            openFileDialog1.RestoreDirectory = true;
            //openFileDialog1.CheckFileExists = true;
            //openFileDialog1.CheckPathExists = true;

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

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

        private void btnMergeFile_Click(object sender, RoutedEventArgs e)
        {
            Workbook tempbook = new Workbook();

            Workbook workbook = new Workbook();
            workbook.Version = ExcelVersion.Version2013;
            workbook.Worksheets.Clear();


            foreach (string file in listBox1.SelectedItems)
            {
                string extension = Path.GetExtension(file);
                if (extension == ".xlsx" | extension == ".xls")
                {
                    tempbook.LoadFromFile(file);//Load Excel files
                }
                else
                {
                    tempbook.LoadFromFile(file, ",", 1, 1);//Load CSV files                      
                }
                foreach (Worksheet sheet in tempbook.Worksheets)
                {
                    workbook.Worksheets.AddCopy(sheet);//Merge files
                }
            }
            string newFileName = textBox1.Text.Trim();
            workbook.SaveToFile(newFileName);
        }       

    }
}

Merging excel documents

3 Answers3