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
|