Currently viewing the tag: "office 2010"

At work my boss asked me to automate some document creation form data in Excel and i said OK , even tho i havent ever done something like this.

After a couple of days of going trough the MSDN documentation and few google pages i finaly did it !

Here is the source with lots of comments , i hope its some use to you 🙂

Add references to MS interop word & excell

then

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

//defines new excel and workd apps
 var ap = new Word.Application();
 var excelApp = new Excel.Application();
 // defines new excel worksheet & workbooks
 Excel.Workbook exBook;
 Excel.Worksheet xlWorkSheet;
 // should the excell/word apps be visible ?
 excelApp.Visible = false;
 ap.Visible = false;

 //defining the index numbers of our content controls that are in the word template
 // index numbers start from 1 and are numbered by order of creation
 object Price, Name, address;
 Price = 1;
 Name = 2;
 address = 3;


 // here we open the excell file
 exBook = excelApp.Workbooks.Open(@"C:\test.xls");
 // and we open the first worksheet
 xlWorkSheet = exBook.Worksheets.get_Item(1);
 Excel.Range range ;
 //here we select the first worksheet and make it active
 Excel._Worksheet workSheet = (Excel.Worksheet) excelApp.ActiveSheet;
 //we open the word document
 var doc = ap.Documents.Open(@"C:\test.dotx", ReadOnly: false, Visible: false);
 // and we assign the content controls
 var dPrice = doc.ContentControls.get_Item(ref Price);
 var dName = doc.ContentControls.get_Item(ref Name);
 var dAddress = doc.ContentControls.get_Item(ref address);
 doc.Activate();
 range = xlWorkSheet.UsedRange;
 // here we define the columns that we are going to select
 object t, P , E , K, N,M,J;

 P = "P";
 E = "E";
 K = "K";
 J  = "J";
 N = "N";
 M = "M";

 // and here we loop trought the rows of the excell worksheet
 // IMPORTANT! excell rows count starts from 1 and not from 0 !
 for (int i =1; i< Convert.ToInt16(Settings1.Default.copies) ;i++)

 {

 t = i;
 // here we get the value if cell t(1..2..3..etc), P
 var dummy = (range.Cells[t, P] as Excel.Range).Value2;
 // here we insert the content of the cell to the content control

 dPrice.Range.Text = ": " + Convert.ToString(dummy) + " лв";
 dName.Range.Text = ": " + (string)(range.Cells[t, E] as Excel.Range).Value2;

 // same thing here
 var city = (string) (range.Cells[t, J] as Excel.Range).Value2;
var address1 = (string) (range.Cells[t, K] as Excel.Range).Value2;

 try
 {
 //here we try to save the word document as a pdf file
 object name = @"C:\t\test"+i+".pdf";
 object FileFormat = WdSaveFormat.wdFormatPDF;
 doc.SaveAs(ref name, ref FileFormat);

 }
 catch (Exception ex)
 {
 MessageBox.Show("Exception Caught: " + ex.Message +" source "+ ex.Source.ToString());

 }

 }
 // here quit word without saving the changes to the template
 ap.Quit(SaveChanges: false, OriginalFormat: false, RouteDocument: false);
 excelApp.Quit();
 // and we release the objects
 System.Runtime.InteropServices.Marshal.ReleaseComObject(ap);
 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

Tagged with: