How to append multiple excel template with data in one printing-CodePudding

I am working on Windows form application C# and have encountered a problem in exporting data to excel. I don't have enough knowledge about this and I don't know if it is possible. I tried searching about this and I can't seem to compose my question properly. I just want to know if this is possible and if there are documents about this.

I have a template: Excel template:

and this is the print/output that I wanted in one paper: Print output:

How do I achieve this?

CodePudding user response：

Take a look at following demo. I made it simple.

Create a WinForms project (in this example it's name is WindowsFormsApp3).

Create a strongly-typed data set - ProductsDataSet.

Add a new static class Extensions to the project

namespace WindowsFormsApp3
{
    using Microsoft.Office.Interop.Excel;

    static class Extensions
    {       

    }
}

Insert into this class following 2 methods

First method

private static void Export(this ProductsDataSet dataSet, Worksheet worksheet)
{
    var products = dataSet.Product.Select();

    for (int i = 1, j = 6; i < products.Length; i  , j  = 5)
    {
        worksheet.Range["A1", "D4"].Copy(Type.Missing);
        worksheet.Range[$"A{j}"].PasteSpecial(XlPasteType.xlPasteAll,
            XlPasteSpecialOperation.xlPasteSpecialOperationNone,
            true, Type.Missing);
    }

    for (int i = 0, j = 3; i < products.Length; i  , j  = 5)
    {
        worksheet.Range[$"B{j}"].Value = products[i][0];
        worksheet.Range[$"C{j}"].Value = products[i][1];
    }
}

Second method

public static void Export(this ProductsDataSet dataSet, string fileName)
{
    Application application = new Application();
    var workbook = application.Workbooks.Open(fileName,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing
        );
    dataSet.Export((Worksheet)workbook.Worksheets[1]);
    application.Visible = true;
}

Customize Form1 as below

Subscribe following event handler to button's click event.

private void Button1_Click(object sender, EventArgs e)
{
    using (var openFileDialog = new OpenFileDialog())
    {
        switch (openFileDialog.ShowDialog())
        {
            case DialogResult.OK:

                var directoryName = System.IO.Path
                        .GetDirectoryName(openFileDialog.FileName);
                var fileName = System.IO.Path
                        .GetFileName(openFileDialog.FileName);
                var destFileName = 
                        $"{directoryName}\\{DateTime.Now.Ticks}.{fileName}";
                System.IO.File.Copy(openFileDialog.FileName, destFileName);
                productsDataSet1.Export(destFileName);
                break;
        }
    }
}

I created following excel template file, similar to yours. The program will open this file and populate it with data from productsDataSet1.

Here is running application

Here is exported data in the excel file