CODEWITHSUNDEEP
c#asp.netexcel-2007word-2007
Steini
Steini

Reputation: 33

Transfer data from Excel to Word using Visual Studio 2010 - C#

this is my first question here. I am using Visual Studio 2010 and I want to design an Excel Addin for a sales catalog. It has two forms and I got one of them working but the other one not. What I want to achieve is that I open up a Windows form through the Addin and through the form I get data from my Access database. I import the data into a datagrid and then I export the data to Excel.

I got this part working but I have problems with my next form. In that form I want to open up a Word template ( when I press a button) and then the data from the open Excel file is transferred into my Word document. Does anybody know what would be the best way to do this? I use OleDBConnection to get the data from the database to the datagrid. I am designing this for Office 2007 and I am programming in C#.

Here is part of my code. I use this part to get the data from the Excel file into a DataGrid but I want to be able to bring that data into a WordDocument. Does anybody know what is the best way to do that. 

DataSet da = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter();
string workbookPath = "C:myfile.xlsx";

Excel.Workbook wb = Globals.ThisAddIn.Application.Workbooks.Add(workbookPath);
string ConnectionString =
    @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + workbookPath
    + @";Extended Properties=""Excel 12.0 Macro;HDR=Yes;ImpoertMixedTypes=Text;TypeGuessRows=0""";

OleDbConnection conn = new OleDbConnection(ConnectionString);
string strCmd = " select * from [Sheet1$A1:D4]"; //Or some other range
OleDbCommand cmd = new OleDbCommand(strCmd, conn);

conn.Open();
da.Clear();
adapter.SelectCommand = cmd;
adapter.Fill(da);
dataGridView1.DataSource = da.Tables[0];

Thanks in advance.

Upvotes: 2

Views: 6668

Answers (3)

superB
superB

Reputation: 288

Follow below code to Copy Active Cells from Excel to Word( Source Formatting will remain same)

var applicationWord = new Microsoft.Office.Interop.Word.Application();
            applicationWord.Visible = true;
            Word.Document wordDoc = applicationWord.Documents.Add();
            var cells = getCells();
            var last_row = cells.Row;
            var last_col = cells.Column;
            var firstcell = activeWorksheet1.get_Range("A1", Type.Missing);
            var lastcell = (Excel.Range)activeWorksheet1.Cells[last_row, last_col];

            activeWorksheet.Range[firstcell, lastcell].Copy(); // All Active cell will get copied
            wordDoc.ActiveWindow.Selection.PasteExcelTable(false, false, false);

Below Code to get active cells in the WorkSheet:

dynamic getCells()
        {
        activeWorksheet1 = ((Excel.Worksheet)Application.ActiveSheet);
        var CellZ = activeWorksheet1.Cells.Find(
                "*",
                System.Reflection.Missing.Value, Excel.XlFindLookIn.xlValues,
                Excel.XlLookAt.xlWhole,
                Excel.XlSearchOrder.xlByRows,
                Excel.XlSearchDirection.xlPrevious,
                false,
                System.Reflection.Missing.Value,
                System.Reflection.Missing.Value);
            return CellZ;
        }

Upvotes: 0

Steini
Steini

Reputation: 33

Ok, so with the help I got from jmh_gr I managed to get this working. This is how it looks. It basically does what it is supposed to do, copies data from Excel and pastes it to Word.

object fileName = "C:\\Template_1.docx";
string workbookPath = "C:\\Book1.xlsx";
Excel.Workbook wb = Globals.ThisAddIn.Application.Workbooks.Add(workbookPath);
Excel.Worksheet ws = wb.Worksheets[1];
ws.Range["A1:D4"].Copy();

object missing = System.Reflection.Missing.Value;
Word.Application wordApp = Marshal.GetActiveObject("Word.Application") as Word.Application;
Word.Document doc;
Word.Range rng;
doc = wordApp.ActiveDocument;
rng = wordApp.Selection.Range;

object objDataTypeMetafile = Word.WdPasteDataType.wdPasteRTF;
rng.PasteSpecial(ref missing, ref missing,
ref missing, ref missing, ref objDataTypeMetafile,
ref missing, ref missing);

Upvotes: 1

Joshua Honig
Joshua Honig

Reputation: 13215

Since you're copying between two Office applications I would recommend you use the built-in PasteExcelTable Method. Just add a reference to the Microsoft Word 1x.0 Object Library:

using Word = Microsoft.Office.Interop.Word;
...

string workbookPath = @"C:\temp\example.xlsx";
Excel.Workbook wb = Globals.ThisAddIn.Application.Workbooks.Add(workbookPath);
Excel.Worksheet ws = wb.Worksheets[1];
Word.Application wdApp = new Word.Application();
Word.Document wdDoc = wdApp.Documents.Add();
ws.Range["A1:D4"].Copy();
wdDoc.ActiveWindow.Selection.PasteExcelTable(false, false, false);

Upvotes: 0

Related Questions