How to maintain formatting of cells when copying cells from Excel to Word

Question

Currently I'm using OleDB to connect with Excel Spreadsheet and getting data in a DataTable, doing some modifications on data and then copying the data to Word.

In doing so I'm losing the formatting of the cell, like if any part of text was colored or if the background color was grayed or if it's bold.

I'm using Interop library to communicate with word and OLEDB with Excel.

If this solution is not good enough for what I need to achieve , can you suggest alternative solutions? (Macros?) I tried using Interop.Excel.Styles but I can't figure out how to relate it with the cell being currently used.

Answer 1

We copy the range of the table in the spreadsheet and the directly paste in the word document which preserves formatting.

wordDoc.Tables.Add(b.Range,newsheet.UsedRange.Rows.Count,newsheet.UsedRange.Columns.Count);
Microsoft.Office.Interop.Word.Table table = b.Range.Tables[1];
newsheet.UsedRange.Copy();
table.Range.Select(); 
wordApp.Selection.Paste();

wordDoc is Word.Document and wordApp is word.Application. Hope this helps