合并excel文档

Question

我试图制作一个简单的程序来合并excel文件，并且在单独加载ListBox中的文件时遇到了问题。

用户可以选择出现在ListBox中的多个文件，然后单击合并，生成一个新文件，并在TextBox中指定名称。

我的问题是当我从ListBox加载要合并的文件时。

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

可以单独调用列表名吗？

对不起，我是c#和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();
    }
}
}

Answer 1

首先，您需要像NixonUposseen建议的那样设置列表框的选择模式。

其次，使用下面的代码合并选定的excel文件和csv文件。我已经试过了，对我来说效果很好。

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

    }
}

Answer 2

我猜您只需要获得以前在列表框中添加的项，这样就可以使用添加项时使用的集合：

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

但是，首先，我建议您阅读有关MVVM体系结构的文章，它不仅可以帮助您在这种情况下实现代码的结构化，还可以保持代码的逻辑结构。您可以看到另一个问题：MVVM:教程从头到尾？

Answer 3

您提到用户可以选择多个文件，所以我相信您需要这样做：

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

此外，您还将使用ListBox.SelectedItems集合迭代这些选定的文件。

这里还有一个完整的btnMergeFile_Click事件，您可以尝试：

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

注意，我已经使用GemBox.Spreadsheet合并了选定的excel文件。