Office Automation: what changed

Revision 1 to 2. Removed lines have a minus sign; added lines have a plus sign.

Old	New		Line
1	1		(Windows only)
2	2
3	3		The ability to interact with Office software and generate spreadsheets, text documents and presentations from code can be invaluable in the office, and save a lot of time otherwise spent on repetitive tasks.
4	4
5	5		One example of this is the creation of applications that can read files in an arbitrary format and output an Excel file, a task that can be done much more efficiently with code than manually.
6	6
7	7		## Using the OpenOffice UNO Bridge
8	8
9	9		OpenOffice has language bindings for C++, Java, JavaScript and Python. On Windows, OpenOffice can also be manipulated in Pascal via COM Automation (see below), but there is currently no easy way of using OpenOffice's UNO (Universal Network Objects) from Pascal on OS X and Linux. If you're interested in developing an OO "bridge" for Pascal, please refer to these links for more information (caution: these links are quite techie in true Sun fashion):
10	10
11	11		[api.openoffice.org](http://api.openoffice.org/)
12	12
13	13		[About Bridges](http://wiki.services.openoffice.org/wiki/Uno/Article/About_Bridges)
14	14
15	15		## Using COM Automation to interact with OpenOffice and Microsoft Office
16	16
17	17		Automation is unique to Windows so the following two examples won't work on OS X or Linux. For those platforms, please refer to [Making do without Windows COM Automation](http://wiki.lazarus.freepascal.org/Multiplatform_Programming_Guide#Making_do_without_Windows_COM_Automation). If you only need to create and/or view a word processing document from your program, take a look at the [[XDev Toolkit]].
18	18
19	19		### OpenOffice/LibreOffice on Windows
20	20
21	21		Here's a simple example of how to open a document with your program using the OpenOffice Automation server. Note that this works only on Windows.
22	22
23	23		``` pascal
24	24		program TestOO;
25	25
26	26		{$IFDEF FPC}
27	27		{$MODE Delphi}
28	28		{$ELSE}
29	29		{$APPTYPE CONSOLE}
30	30		{$ENDIF}
31	31
32	32		uses
33	33		SysUtils, Variants, ComObj;
34	34
35	35		const
36	36		ServerName = 'com.sun.star.ServiceManager';
37	37		var
38	38		Server : Variant;
39	39		Desktop : Variant;
40	40		LoadParams : Variant;
41	41		Document : Variant;
42	42		TextCursor : Variant;
43	43		begin
44	44		if Assigned(InitProc) then
45	45		TProcedure(InitProc);
46	46
47	47		try
48	48		Server := CreateOleObject(ServerName);
49	49		except
50	50		WriteLn('Unable to start OO.');
51	51		Exit;
52	52		end;
53	53
54	54		Desktop := Server.CreateInstance('com.sun.star.frame.Desktop');
55	55
56	56		LoadParams := VarArrayCreate([0, -1], varVariant);
57	57
58	58		{Create new document}
59	59		Document := Desktop.LoadComponentFromURL('private:factory/swriter', '_blank', 0, LoadParams);
60	60
61	61		{or Open existing} //you must use forward slashes, not backward!
62	62		//Document := Desktop.LoadComponentFromURL('file:///C:/my/path/mydoc.doc', '_blank', 0, LoadParams);
63	63
64	64		TextCursor := Document.Text.CreateTextCursor;
65	65
66	66		{Insert existing document} //Substitute your path and doc
67	67		TextCursor.InsertDocumentFromURL('file:///C:/my/path/mydoc.doc', LoadParams);
68	68		end.
69	69		```
70	70
71	71		More comprehensive examples can found [here](https://bitbucket.org/blikblum/pascal-demos/src/04a4d10bf30b59215ff3ad4cd41dba71405ae5fc/fpc/LibreOfficeOLE/?at=master):
72	72
73	73		- OLECreateTextDoc: Create and populate a text document with paragraph, table and frame
74	74		- OLEEditTextDoc: Load an existing text document, search with regular expression, replace text and add rows to a table
75	75
76	76		### Office on Windows
77	77
78	78		Here's a simple example of how to open a document with your program using the Word Automation server. Note that this example works only on Windows. This will work with both delphi and fpc.
79	79
80	80		``` pascal
81	81		program TestMsOffice;
82	82
83	83		{$IFDEF FPC}
84	84		{$MODE Delphi}
85	85		{$ELSE}
86	86		{$APPTYPE CONSOLE}
87	87		{$ENDIF}
88	88
89	89		uses
90	90		SysUtils, Variants, ComObj;
91	91
92	92		const
93	93		ServerName = 'Word.Application';
94	94		var
95	95		Server : Variant;
96	96		w:widestring;
97	97		begin
98	98		if Assigned(InitProc) then
99	99		TProcedure(InitProc);
100	100
101	101		try
102	102		Server := CreateOleObject(ServerName);
103	103		except
104	104		WriteLn('Unable to start Word.');
105	105		Exit;
106	106		end;
107	107
108	108		{Open existing document} //Substitute your path and doc
109	109		w:= UTF8Decode('c:\my\path\mydoc.doc');
110	110		Server.Documents.Open(w); //OLE uses BSTR (http://msdn.microsoft.com/en-us/library/windows/desktop/ms221069(v=vs.85).aspx). Only widestring is compatible with BSTR in FPC, so conversion is needed for nonlatin chars.
111	111		Server.Visible := True; {Make Word visible}
112	112
113	113		end.
114	114		```
115	115
116	116		Here is a sample code how to work in an open Word document, using the Word Automation server.
117	117
118	118		``` pascal
119	119		var
120	120		Server: Variant;
121	121		begin
122	122		try
123	123		Server := GetActiveOleObject('Word.Application');
124	124		except
125	125		try
126	126		ShowMessage('Word not already open create a Word Object');
127	127		// If no instance of Word is running, try to Create a new Word Object
128	128		Server := CreateOleObject('Word.Application');
129	129		except
130	130		ShowMessage('Cannot start Word/Word not installed ?');
131	131		Exit;
132	132		end;
133	133		end;
134	134		end;
135	135		```
136	136
137	137		Limitations: Since End is a reserved word in FPC it shall be used as a parameter after the & sign.
138	138
139	139		``` pascal
140	140		Server.ActiveDocument.Application.Selection.start:=Server.ActiveDocument.Application.Selection.&end+1;
141	141		```
142	142
143	143		A lot of examples for Excel are available on the German wiki page [[ExcelAutomation/de]].
144	144
145	145		## Using the fpXMLXSDExport unit
146	146
147	147		FPC 2.6 and newer contain the fpXMLXSDExport unit, part of the FCL-DB export components. With that, you can export datasets to various XML formats, including a Microsoft Access-compatible format and a Microsoft Excel-compatible format.
148	148
149	149		The Access format can output XML with or without an embedded XSD data/table definition. Note that exporting binary/BLOB type data needs additional action at the Access import end, as Access does not support proper binary fields, only OLE fields.
150	150
151	151		In the Excel format, multiline text fields are not supported at the moment: the line ends are removed during the export.
152	152
153	153		Lazarus provides a visual component for this: after installing the lazdbexport package, you will see the TXMLXSDExporter component on the Data Export tab
154	154
155	155		See [[fpXMLXSDExport]] for details.
156	156
157	157		## Using the FPSpreadsheet Library
158	158
159	159		Another way to automate repetitive work with spreadsheets is to use the [[FPSpreadsheet]] library. It can read and write spreadsheets in several formats and it doesn't require having any external application installed on the machine.
160	160
161	161		The advantages are that fpspreadsheet is 100% [[Object Pascal]] code, and it requires no external libraries or programs.
162	162
163	163		## Writing an Excel file using ADO
164	164
165	165		please write me.
166	166
167	167		## Reading/Writing an Excel file using OLE
168	168
169	169		This method needs Excel to be installed on the user's machine because it uses OLE to access it.
170	170
171	171		*Keep in mind that this method starts Excel in the background, which opens the file and works with it like a real user.*
172	172
173	173		- Create a new form with button, stringgrid and edit.
174	174		- Create a new Excel file and fill a few cells.
175	175
176		-	~~<figure>~~
177		-	~~<img src="excel123.png" title="excel123.png" />~~
178		-	~~<figcaption>excel123.png</figcaption>~~
179		-	~~</figure>~~
	176	+	![](/wiki-images/7/74/Excel123.png)
180	177
181	178		Example - Open/Read Excel file:
182	179
183	180		``` pascal
184	181		uses ..... comobj;
185	182
186	183		procedure TForm1.Button1Click(Sender: TObject);
187	184
188	185		Var XLApp: OLEVariant;
189	186		x,y: byte;
190	187		path: variant;
191	188
192	189		begin
193	190		XLApp := CreateOleObject('Excel.Application'); // requires comobj in uses
194	191		try
195	192		XLApp.Visible := False; // Hide Excel
196	193		XLApp.DisplayAlerts := False;
197	194		path := edit1.Text;
198	195		XLApp.Workbooks.Open(Path); // Open the Workbook
199	196		for x := 1 to 4 do
200	197		begin
201	198		for y := 1 to 6 do
202	199		begin
203	200		SG.Cells[x,y] := XLApp.Cells[y,x].Value; // fill stringgrid with values
204	201		end;
205	202		end;
206	203		finally
207	204		XLApp.Quit;
208	205		XLAPP := Unassigned;
209	206		end;
210	207		```
211	208
212	209		If you want to make some changes and you want them to write back into the Excel, file you can use:
213	210
214	211		``` pascal
215	212		XLApp.Cells[x,y].Value := SG.Cells[y,x];
216	213		```
217	214
218	215		If you want to save:
219	216
220	217		``` pascal
221	218		XLApp.ActiveWorkBook.Save;
222	219		```
223	220
224	221		## Read/Writing an Excel file using the SpreadSheet Interface Component
225	222
226	223		The component provides a library interface, abstracting the Excel COM and the Calc Open Office UNO interfaces. The component is available here: <http://tcoq.free.fr/composants.html> (Link verified in May 2016)
227	224
228	225		Since Automation is not yet available, but COM is available, the Excel interface component provides a set of Lazarus classes encapsulating calls to the Excel COM interface (the one below the Automation). It hides most of the drudgery of working with low-level code. Be careful, this is a work-in-progress. Use it at your own risk.
229	226
230	227		Functionality:
231	228
232	229		- creating and loading excel workbooks,
233	230		- saving workbooks,
234	231		- creating and accessing sheets,
235	232		- getting values and setting values (and formulas) in cells,
236	233		- getting and changing color of cells,
237	234		- getting and changing column height and row width,
238	235		- creating comments,
239	236		- creating shapes,
240	237		- creating charts.
241	238
242	239		Inits first.
243	240
244	241		``` pascal
245	242		IMPLEMENTATION
246	243		USES
247	244		ExcelUtilities,
248	245		SpreadSheetInterfaces ;
249	246
250	247		VAR
251	248		aCell : IRange ;
252	249		aValue : OleVariant ; // Not sure about this, but it works. ie( Edit.Text := STRING(aValue); )
253	250		ExcelApp : TExcelApplication ;
254	251		ExcelWbs : IWorkBooks ;
255	252
256	253		ExcelBook : IWorkBook ;
257	254		ExcelSheet : ISheet ;
258	255		ExcelSheets : ISheets ;
259	256		```
260	257
261	258		Getting a sheet is simple:
262	259
263	260		``` pascal
264	261		// Initializing the common excel workbook:
265	262		ExcelApp := TExcelApplication.Create(nil) ;
266	263		ExcelApp.Active := True ;
267	264		ExcelApp.Visible := True ;
268	265
269	266		ExcelWbs := ExcelApp.WorkBooks ;
270	267		ExcelBook := ExcelWbs.Add ;
271	268		ExcelSheets := ExcelBook.Sheets ;
272	269		ExcelSheet := ExcelSheets.Sheet(1) ;
273	270		```
274	271
275	272		Playing around with cells is simple too:
276	273
277	274		``` pascal
278	275		// adding a value
279	276		aCell := ExcelSheet.Cells(1, 1) ;
280	277		aCell.Value := 10;
281	278
282	279		// adding a formula
283	280		aCell := ExcelSheet.Cells(2,1) ;
284	281		aCell.Formula := '=A1+10' ;
285	282
286	283		// getting the value computed in Excel
287	284		aValue := aCell.Value ;
288	285		```
289	286
290	287		The test case provided has many more examples.
291	288
292	289		## Copy HTML to the clipboard
293	290
294	291		You can copy HTML to the clipboard which is understood by many applications. This way you can copy formatted text. For those applications that only understand text put plain text too.
295	292
296	293		Microsoft Office applications require HTML to be pasted onto the clipboard in a more complex format than described here. [[Clipboard\|See here for an example that works with Microsoft Office.]]
297	294
298	295		``` pascal
299	296		uses
300	297		ClipBrd;
301	298		...
302	299		// register the mime type for text/html. You can do this once at program start:
303	300		ClipbrdFmtHTML:=RegisterClipboardFormat('text/html');
304	301		...
305	302		// Clear any previous formats off the clipboard before starting
306	303		Clipboard.Clear;
307	304
308	305		// put text and html on the clipboard. Other applications will choose the best format automatically.
309	306		ClipBoard.SetAsHtml('<b>Formatted</b> text', 'Simple Text');
310	307		```
311	308
312	309		## See also
313	310
314	311		- [[Clipboard]]
315	312		- [[PowerpointAutomation/de\|Powerpoint Automation]]
316	313
317	314		## External links
318	315
319	316		- [Excel file format](http://sc.openoffice.org/excelfileformat.pdf) - description on OpenOffice website
320	317
321	318		---
322	319		From the Free Pascal wiki, page "Office Automation", as it stood on 2023-11-03 after a change by Bart, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).

Back to history