Compare RTF file with Excel file and generate separate RTF file based on group assigned in excel using C# and VB.Net in ASP.Net

Hi mod7609,

I have created a sample that full fill your requirement. After a lot of work around i am able to do it. Refer the below sample.

C#

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using ClosedXML.Excel;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using Word = Microsoft.Office.Interop.Word;

public partial class CS : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        List<DataTable> result = MergeWordExcelWithGroup().AsEnumerable()
            .GroupBy(row => row.Field<string>("Group"))
            .Select(g => g.CopyToDataTable()).ToList();
        for (int i = 0; i < result.Count; i++)
        {
            DataTable dt = result[i];
            string groupName = dt.Rows[0]["Group"].ToString();
            dt.Columns.Remove("Group");
            CreateWordtable(dt, @"C:\Users\dharmendra\Desktop\" + groupName + ".rtf");
        }
    }

    public void CreateWordtable(DataTable dt, string filePath)
    {
        if (dt.Rows.Count > 0)
        {
            // Adding header.
            DataRow headerRow = dt.NewRow();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                headerRow[i] = dt.Columns[i].ColumnName;
            }
            dt.Rows.InsertAt(headerRow, 0);

            // Creating WordDocument.
            WordprocessingDocument document = WordprocessingDocument.Create(filePath, WordprocessingDocumentType.Document);
            MainDocumentPart mainDocumentPart = document.AddMainDocumentPart();
            mainDocumentPart.Document = new Document();
            Body body = new Body();
            mainDocumentPart.Document.Append(body);

            // Creating Table.
            Table table = new Table();
            TableProperties props = new TableProperties(new TableBorders(
                        new TopBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 1 },
                        new BottomBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 1 },
                        new LeftBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 1 },
                        new RightBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 1 },
                        new InsideHorizontalBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 1 },
                        new InsideVerticalBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 1 }));
            table.AppendChild<TableProperties>(props);
            for (int i = 0; i < dt.Rows.Count; ++i)
            {
                // Creating Rows.
                TableRow row = new TableRow();
                for (int j = 0; j < dt.Columns.Count; j++)
                {

                    TableCell cell = new TableCell();
                    cell.Append(new TableCellProperties(new TableCellWidth { Type = TableWidthUnitValues.Dxa, Width = "1200" }));
                    cell.Append(new Paragraph(new Run(new Text(dt.Rows[i][j].ToString()))));
                    row.Append(cell);
                }
                table.Append(row);
            }

            body.Append(table);
            // Saving Document.
            document.MainDocumentPart.Document.Save();
            document.Dispose();
        }
    }

    private DataTable MergeWordExcelWithGroup()
    {
        DataSet ds = new DataSet();
        ds.Tables.Add(ExcelToDataTable());
        ds.Tables.Add(WordTableToDataTable());

        DataTable dtWithGroup = ds.Tables["Word"].Clone();
        dtWithGroup.Columns.Add("Group");
        for (int i = 0; i < ds.Tables["Excel"].Rows.Count; i++)
        {
            string excelRefrence = ds.Tables["Excel"].Rows[i]["Refrences"].ToString();
            string nameOfGroup = ds.Tables["Excel"].Rows[i]["Name of Group"].ToString();
            for (int j = 0; j < ds.Tables["Word"].Rows.Count; j++)
            {
                string nrCurrent = ds.Tables["Word"].Rows[j]["Nr"].ToString();
                string caseNrCurrent = ds.Tables["Word"].Rows[j]["Case Nr"].ToString();
                string wordRefrenceCurrent = ds.Tables["Word"].Rows[j]["References"].ToString();
                string goodsDesignationCurrent = ds.Tables["Word"].Rows[j]["Goods designation"].ToString();
                string quantityCurrent = ds.Tables["Word"].Rows[j]["Quantity"].ToString();
                string totalNetWeightCurrent = ds.Tables["Word"].Rows[j]["Total net weight, kg"].ToString();
                string totalGrossWeightCurrent = ds.Tables["Word"].Rows[j]["Total gross weight, kg"].ToString();
                string countryCurrent = ds.Tables["Word"].Rows[j]["Country of origin"].ToString();
                if (!string.IsNullOrEmpty(excelRefrence))
                {
                    if (excelRefrence.ToLower() == wordRefrenceCurrent.ToLower())
                    {
                        string nrPrevious = ds.Tables["Word"].Rows[j - 1]["Nr"].ToString();
                        string caseNrPrevious = ds.Tables["Word"].Rows[j - 1]["Case Nr"].ToString();
                        string wordRefrencePrevious = ds.Tables["Word"].Rows[j - 1]["References"].ToString();
                        string goodsDesignationPrevious = ds.Tables["Word"].Rows[j - 1]["Goods designation"].ToString();
                        string quantityPrevious = ds.Tables["Word"].Rows[j - 1]["Quantity"].ToString();
                        string totalNetWeightPrevious = ds.Tables["Word"].Rows[j - 1]["Total net weight, kg"].ToString();
                        string totalGrossWeightPrevious = ds.Tables["Word"].Rows[j - 1]["Total gross weight, kg"].ToString();
                        string countryPrevious = ds.Tables["Word"].Rows[j - 1]["Country of origin"].ToString();
                        dtWithGroup.Rows.Add(nrPrevious, caseNrPrevious, wordRefrencePrevious, goodsDesignationPrevious, quantityPrevious, totalNetWeightPrevious, totalGrossWeightPrevious, countryPrevious, nameOfGroup);
                        dtWithGroup.Rows.Add(nrCurrent, caseNrCurrent, wordRefrenceCurrent, goodsDesignationCurrent, quantityCurrent, totalNetWeightCurrent, totalGrossWeightCurrent, countryCurrent, nameOfGroup);
                    }
                }
            }
        }
        return dtWithGroup;
    }

    private DataTable WordTableToDataTable()
    {
        DataTable dt = new DataTable("Word");
        Word.Application word = new Word.Application();
        Word.Document doc = new Word.Document();
        object filePath = Server.MapPath("~/Files/Test.rtf");
        object missing = System.Type.Missing;
        try
        {
            doc = word.Documents.Open(ref filePath, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
                ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);

            // Columns            
            for (int i = 1; i <= word.Application.ActiveDocument.Tables[1].Rows[1].Cells.Count; i++)
            {
                dt.Columns.Add(word.Application.ActiveDocument.Tables[1].Rows[1].Cells[i].Range.Text.Replace("\r\a", "").Trim());
            }

            // Rows
            for (int i = 2; i <= word.Application.ActiveDocument.Tables[1].Rows.Count; i++)
            {
                Word.Row tableRow = word.Application.ActiveDocument.Tables[1].Rows[i];
                dt.Rows.Add(dt.NewRow());
                for (int j = 1; j <= tableRow.Cells.Count; j++)
                {
                    dt.Rows[i - 2][j - 1] = tableRow.Cells[j].Range.Text.Replace("\r\a", "").Trim();
                }
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            ((Word._Application)word).Quit();
        }

        return dt;
    }

    private DataTable ExcelToDataTable()
    {
        DataTable dt = new DataTable("Excel");
        string filePath = Server.MapPath("~/Files/Test.xlsx");
        string extension = Path.GetExtension(filePath);
        using (XLWorkbook workBook = new XLWorkbook(filePath))
        {
            //Read the first Sheet from Excel file.
            IXLWorksheet workSheet = workBook.Worksheet(1);
            //Loop through the Worksheet rows.
            bool firstRow = true;
            foreach (IXLRow row in workSheet.Rows())
            {
                //Use the first row to add columns to DataTable.
                if (firstRow)
                {
                    foreach (IXLCell cell in row.Cells())
                    {
                        dt.Columns.Add(cell.Value.ToString());
                    }
                    firstRow = false;
                }
                else
                {
                    //Add rows to DataTable.
                    dt.Rows.Add();
                    int i = 0;
                    foreach (IXLCell cell in row.Cells())
                    {
                        dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                        i++;
                    }
                }
            }

            return dt;
        }
    }
}

VB.Net

Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Wordprocessing
Imports Microsoft.Office.Interop
Imports System.IO
Imports ClosedXML.Excel

Partial Class VB
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim result As List(Of DataTable) = MergeWordExcelWithGroup().AsEnumerable() _
                                            .GroupBy(Function(row) row.Field(Of String)("Group")) _
                                            .[Select](Function(g) g.CopyToDataTable()) _
                                            .ToList()
        For i As Integer = 0 To result.Count - 1
            Dim dt As DataTable = result(i)
            Dim groupName As String = dt.Rows(0)("Group").ToString()
            dt.Columns.Remove("Group")
            CreateWordtable(dt, (Convert.ToString("C:\Users\dharmendra\Desktop\") & groupName) + ".rtf")
        Next
    End Sub

    Public Sub CreateWordtable(dt As DataTable, filePath As String)
        If dt.Rows.Count > 0 Then
            ' Adding header.
            Dim headerRow As DataRow = dt.NewRow()
            For i As Integer = 0 To dt.Columns.Count - 1
                headerRow(i) = dt.Columns(i).ColumnName
            Next
            dt.Rows.InsertAt(headerRow, 0)

            ' Creating WordDocument.
            Dim document As WordprocessingDocument = WordprocessingDocument.Create(filePath, WordprocessingDocumentType.Document)
            Dim mainDocumentPart As MainDocumentPart = document.AddMainDocumentPart()
            mainDocumentPart.Document = New Wordprocessing.Document()
            Dim body As New Wordprocessing.Body()
            mainDocumentPart.Document.Append(body)

            ' Creating Table.
            Dim table As New Table()
            Dim props As New TableProperties(New TableBorders(
                                            New TopBorder() With {.Val = New EnumValue(Of BorderValues)(BorderValues.[Single]), .Size = 1},
                                            New BottomBorder() With {.Val = New EnumValue(Of BorderValues)(BorderValues.[Single]), .Size = 1},
                                            New LeftBorder() With {.Val = New EnumValue(Of BorderValues)(BorderValues.[Single]), .Size = 1},
                                            New RightBorder() With {.Val = New EnumValue(Of BorderValues)(BorderValues.[Single]), .Size = 1},
                                            New InsideHorizontalBorder() With {.Val = New EnumValue(Of BorderValues)(BorderValues.[Single]), .Size = 1},
                                            New InsideVerticalBorder() With {.Val = New EnumValue(Of BorderValues)(BorderValues.[Single]), .Size = 1}))
            table.AppendChild(Of TableProperties)(props)
            For i As Integer = 0 To dt.Rows.Count - 1
                ' Creating Rows.
                Dim row As New TableRow()
                For j As Integer = 0 To dt.Columns.Count - 1

                    Dim cell As New TableCell()
                    cell.Append(New TableCellProperties(New TableCellWidth() With {.Type = TableWidthUnitValues.Dxa, .Width = "1200"}))
                    cell.Append(New Paragraph(New Run(New Text(dt.Rows(i)(j).ToString()))))
                    row.Append(cell)
                Next
                table.Append(row)
            Next

            body.Append(table)
            ' Saving Document.
            document.MainDocumentPart.Document.Save()
            document.Dispose()
        End If
    End Sub

    Private Function MergeWordExcelWithGroup() As DataTable
        Dim ds As New DataSet()
        ds.Tables.Add(ExcelToDataTable())
        ds.Tables.Add(WordTableToDataTable())

        Dim dtWithGroup As DataTable = ds.Tables("Word").Clone()
        dtWithGroup.Columns.Add("Group")
        For i As Integer = 0 To ds.Tables("Excel").Rows.Count - 1
            Dim excelRefrence As String = ds.Tables("Excel").Rows(i)("Refrences").ToString()
            Dim nameOfGroup As String = ds.Tables("Excel").Rows(i)("Name of Group").ToString()
            For j As Integer = 0 To ds.Tables("Word").Rows.Count - 1
                Dim nrCurrent As String = ds.Tables("Word").Rows(j)("Nr").ToString()
                Dim caseNrCurrent As String = ds.Tables("Word").Rows(j)("Case Nr").ToString()
                Dim wordRefrenceCurrent As String = ds.Tables("Word").Rows(j)("References").ToString()
                Dim goodsDesignationCurrent As String = ds.Tables("Word").Rows(j)("Goods designation").ToString()
                Dim quantityCurrent As String = ds.Tables("Word").Rows(j)("Quantity").ToString()
                Dim totalNetWeightCurrent As String = ds.Tables("Word").Rows(j)("Total net weight, kg").ToString()
                Dim totalGrossWeightCurrent As String = ds.Tables("Word").Rows(j)("Total gross weight, kg").ToString()
                Dim countryCurrent As String = ds.Tables("Word").Rows(j)("Country of origin").ToString()
                If Not String.IsNullOrEmpty(excelRefrence) Then
                    If excelRefrence.ToLower() = wordRefrenceCurrent.ToLower() Then
                        Dim nrPrevious As String = ds.Tables("Word").Rows(j - 1)("Nr").ToString()
                        Dim caseNrPrevious As String = ds.Tables("Word").Rows(j - 1)("Case Nr").ToString()
                        Dim wordRefrencePrevious As String = ds.Tables("Word").Rows(j - 1)("References").ToString()
                        Dim goodsDesignationPrevious As String = ds.Tables("Word").Rows(j - 1)("Goods designation").ToString()
                        Dim quantityPrevious As String = ds.Tables("Word").Rows(j - 1)("Quantity").ToString()
                        Dim totalNetWeightPrevious As String = ds.Tables("Word").Rows(j - 1)("Total net weight, kg").ToString()
                        Dim totalGrossWeightPrevious As String = ds.Tables("Word").Rows(j - 1)("Total gross weight, kg").ToString()
                        Dim countryPrevious As String = ds.Tables("Word").Rows(j - 1)("Country of origin").ToString()
                        dtWithGroup.Rows.Add(nrPrevious, caseNrPrevious, wordRefrencePrevious, goodsDesignationPrevious, quantityPrevious, totalNetWeightPrevious, _
                         totalGrossWeightPrevious, countryPrevious, nameOfGroup)
                        dtWithGroup.Rows.Add(nrCurrent, caseNrCurrent, wordRefrenceCurrent, goodsDesignationCurrent, quantityCurrent, totalNetWeightCurrent, _
                         totalGrossWeightCurrent, countryCurrent, nameOfGroup)
                    End If
                End If
            Next
        Next
        Return dtWithGroup
    End Function

    Private Function WordTableToDataTable() As DataTable
        Dim dt As New DataTable("Word")
        Dim word As New Word.Application()
        Dim doc As New Word.Document()
        Dim filePath As Object = Server.MapPath("~/Files/Test.rtf")
        Dim missing As Object = System.Type.Missing
        Try
            doc = word.Documents.Open(filePath, missing, missing, missing, missing, missing, _
             missing, missing, missing, missing, missing, missing, missing, missing, missing, missing)

            ' Columns            
            For i As Integer = 1 To word.Application.ActiveDocument.Tables(1).Rows(1).Cells.Count
                dt.Columns.Add(word.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.Text.Replace(vbCr & ChrW(7), "").Trim())
            Next

            ' Rows
            For i As Integer = 2 To word.Application.ActiveDocument.Tables(1).Rows.Count
                Dim tableRow As Word.Row = word.Application.ActiveDocument.Tables(1).Rows(i)
                dt.Rows.Add(dt.NewRow())
                For j As Integer = 1 To tableRow.Cells.Count
                    dt.Rows(i - 2)(j - 1) = tableRow.Cells(j).Range.Text.Replace(vbCr & ChrW(7), "").Trim()
                Next
            Next
        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            DirectCast(word, Word._Application).Quit()
        End Try

        Return dt
    End Function

    Private Function ExcelToDataTable() As DataTable
        Dim dt As New DataTable("Excel")
        Dim filePath As String = Server.MapPath("~/Files/Test.xlsx")
        Dim extension As String = Path.GetExtension(filePath)
        Using workBook As New XLWorkbook(filePath)
            'Read the first Sheet from Excel file.
            Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
            'Loop through the Worksheet rows.
            Dim firstRow As Boolean = True
            For Each row As IXLRow In workSheet.Rows()
                'Use the first row to add columns to DataTable.
                If firstRow Then
                    For Each cell As IXLCell In row.Cells()
                        dt.Columns.Add(cell.Value.ToString())
                    Next
                    firstRow = False
                Else
                    'Add rows to DataTable.
                    dt.Rows.Add()
                    Dim i As Integer = 0
                    For Each cell As IXLCell In row.Cells()
                        dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                        i += 1
                    Next
                End If
            Next

            Return dt
        End Using
    End Function
End Class

Input file

Test.rtf

Test.xlsx

Output

Group 1.rtf

Group 2.rtf

Group 3.rtf