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

Last Reply one year ago By dharmendr

Posted one year ago

Dears,

I have a RTF file including several different references and three groups, and also I have an excel database including some references in three groups too.

I would like to compare the references which are in RFT file with the all the references in excel file and separate references based on their group and export three separated RFT file.

I attached these two file in following link:

https://www.file-upload.com/ydxpfmbi9eym

https://www.file-upload.com/dwcxnxlihr8f

Can somebody help me in this regards ? how I can handle it ?

 

BR

MA

You are viewing reply posted by: dharmendr one year ago.
Posted one year ago

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

Nr

Case Nr

References

Goods designation

Quantity

Total net weight, kg

Total gross weight, kg

Country of origin

1

126178212

 

 

64

426,880

451,380

 

 

 

8200537190

COMPR

 

64

426,880

451,380

TH

2

126179212

 

 

64

426,880

451,380

 

 

 

8200537190

COMPR

 

64

426,880

451,380

TH

3

130701211

 

 

480

547,200

571,700

 

 

 

765872424R

REINF- SIDE IMPACT RE

 

480

547,200

571,700

EU

4

130718211

 

 

480

547,200

571,700

 

 

 

8200536832

REINF- SIDE IMPACT RE

 

480

547,200

571,700

EU

5

155932212

 

 

336

493,920

518,420

 

 

 

476604621R

UNIT-ABS HYD

 

336

493,920

518,420

EU

Test.xlsx

Refrences Name of Group
8200537190 Group 1
765872424R Group 2
8200536832 Group 1
476604621R Group 3

Output

Group 1.rtf

Nr

Case Nr

References

Goods designation

Quantity

Total net weight, kg

Total gross weight, kg

Country of origin

1

126178212

 

 

64

426,880

451,380

 

 

 

8200537190

COMPR

64

426,880

451,380

TH

2

126179212

 

 

64

426,880

451,380

 

 

 

8200537190

COMPR

64

426,880

451,380

TH

4

130718211

 

 

480

547,200

571,700

 

 

 

8200536832

REINF- SIDE IMPACT RE

480

547,200

571,700

EU

Group 2.rtf

Nr

Case Nr

References

Goods designation

Quantity

Total net weight, kg

Total gross weight, kg

Country of origin

3

130701211

 

 

480

547,200

571,700

 

 

 

765872424R

REINF- SIDE IMPACT RE

480

547,200

571,700

EU

Group 3.rtf

Nr

Case Nr

References

Goods designation

Quantity

Total net weight, kg

Total gross weight, kg

Country of origin

5

155932212

 

 

336

493,920

518,420

 

 

 

476604621R

UNIT-ABS HYD

336

493,920

518,420

EU