Sign in Join
Wiki copytemporary

Office Automation: what changed

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

Title: Office Automation

OldNewLine
1+*(Windows only)*
2+
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+
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+
7+## Using the OpenOffice UNO Bridge
8+
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+
11+[api.openoffice.org](http://api.openoffice.org/)
12+
13+[About Bridges](http://wiki.services.openoffice.org/wiki/Uno/Article/About_Bridges)
14+
15+## Using COM Automation to interact with OpenOffice and Microsoft Office
16+
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+
19+### OpenOffice/LibreOffice on Windows
20+
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+
23+``` pascal
24+program TestOO;
25+
26+{$IFDEF FPC}
27+ {$MODE Delphi}
28+{$ELSE}
29+ {$APPTYPE CONSOLE}
30+{$ENDIF}
31+
32+uses
33+ SysUtils, Variants, ComObj;
34+
35+const
36+ ServerName = 'com.sun.star.ServiceManager';
37+var
38+ Server : Variant;
39+ Desktop : Variant;
40+ LoadParams : Variant;
41+ Document : Variant;
42+ TextCursor : Variant;
43+begin
44+ if Assigned(InitProc) then
45+ TProcedure(InitProc);
46+
47+ try
48+ Server := CreateOleObject(ServerName);
49+ except
50+ WriteLn('Unable to start OO.');
51+ Exit;
52+ end;
53+
54+ Desktop := Server.CreateInstance('com.sun.star.frame.Desktop');
55+
56+ LoadParams := VarArrayCreate([0, -1], varVariant);
57+
58+ {Create new document}
59+ Document := Desktop.LoadComponentFromURL('private:factory/swriter', '_blank', 0, LoadParams);
60+
61+ {or Open existing} //you must use forward slashes, not backward!
62+ //Document := Desktop.LoadComponentFromURL('file:///C:/my/path/mydoc.doc', '_blank', 0, LoadParams);
63+
64+ TextCursor := Document.Text.CreateTextCursor;
65+
66+ {Insert existing document} //Substitute your path and doc
67+ TextCursor.InsertDocumentFromURL('file:///C:/my/path/mydoc.doc', LoadParams);
68+end.
69+```
70+
71+More comprehensive examples can found [here](https://bitbucket.org/blikblum/pascal-demos/src/04a4d10bf30b59215ff3ad4cd41dba71405ae5fc/fpc/LibreOfficeOLE/?at=master):
72+
73+- OLECreateTextDoc: Create and populate a text document with paragraph, table and frame
74+- OLEEditTextDoc: Load an existing text document, search with regular expression, replace text and add rows to a table
75+
76+### Office on Windows
77+
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+
80+``` pascal
81+program TestMsOffice;
82+
83+{$IFDEF FPC}
84+ {$MODE Delphi}
85+{$ELSE}
86+ {$APPTYPE CONSOLE}
87+{$ENDIF}
88+
89+uses
90+ SysUtils, Variants, ComObj;
91+
92+const
93+ ServerName = 'Word.Application';
94+var
95+ Server : Variant;
96+ w:widestring;
97+begin
98+ if Assigned(InitProc) then
99+ TProcedure(InitProc);
100+
101+ try
102+ Server := CreateOleObject(ServerName);
103+ except
104+ WriteLn('Unable to start Word.');
105+ Exit;
106+ end;
107+
108+ {Open existing document} //Substitute your path and doc
109+ w:= UTF8Decode('c:\my\path\mydoc.doc');
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+ Server.Visible := True; {Make Word visible}
112+
113+end.
114+```
115+
116+Here is a sample code how to work in an open Word document, using the Word Automation server.
117+
118+``` pascal
119+var
120+ Server: Variant;
121+begin
122+ try
123+ Server := GetActiveOleObject('Word.Application');
124+ except
125+ try
126+ ShowMessage('Word not already open create a Word Object');
127+ // If no instance of Word is running, try to Create a new Word Object
128+ Server := CreateOleObject('Word.Application');
129+ except
130+ ShowMessage('Cannot start Word/Word not installed ?');
131+ Exit;
132+ end;
133+ end;
134+end;
135+```
136+
137+Limitations: Since **End** is a reserved word in FPC it shall be used as a parameter after the *&* sign.
138+
139+``` pascal
140+Server.ActiveDocument.Application.Selection.start:=Server.ActiveDocument.Application.Selection.&end+1;
141+```
142+
143+A lot of examples for Excel are available on the German wiki page [[ExcelAutomation/de]].
144+
145+## Using the fpXMLXSDExport unit
146+
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+
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+
151+In the Excel format, multiline text fields are not supported at the moment: the line ends are removed during the export.
152+
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+
155+See [[fpXMLXSDExport]] for details.
156+
157+## Using the FPSpreadsheet Library
158+
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+
161+The advantages are that fpspreadsheet is 100% [[Object Pascal]] code, and it requires no external libraries or programs.
162+
163+## Writing an Excel file using ADO
164+
165+please write me.
166+
167+## Reading/Writing an Excel file using OLE
168+
169+This method needs Excel to be installed on the user's machine because it uses OLE to access it.
170+
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+
173+- Create a new form with button, stringgrid and edit.
174+- Create a new Excel file and fill a few cells.
175+
176+<figure>
177+<img src="excel123.png" title="excel123.png" />
178+<figcaption>excel123.png</figcaption>
179+</figure>
180+
181+Example - Open/Read Excel file:
182+
183+``` pascal
184+uses ..... comobj;
185+
186+procedure TForm1.Button1Click(Sender: TObject);
187+
188+Var XLApp: OLEVariant;
189+ x,y: byte;
190+ path: variant;
191+
192+begin
193+ XLApp := CreateOleObject('Excel.Application'); // requires comobj in uses
194+ try
195+ XLApp.Visible := False; // Hide Excel
196+ XLApp.DisplayAlerts := False;
197+ path := edit1.Text;
198+ XLApp.Workbooks.Open(Path); // Open the Workbook
199+ for x := 1 to 4 do
200+ begin
201+ for y := 1 to 6 do
202+ begin
203+ SG.Cells[x,y] := XLApp.Cells[y,x].Value; // fill stringgrid with values
204+ end;
205+ end;
206+ finally
207+ XLApp.Quit;
208+ XLAPP := Unassigned;
209+ end;
210+```
211+
212+If you want to make some changes and you want them to write back into the Excel, file you can use:
213+
214+``` pascal
215+XLApp.Cells[x,y].Value := SG.Cells[y,x];
216+```
217+
218+If you want to save:
219+
220+``` pascal
221+XLApp.ActiveWorkBook.Save;
222+```
223+
224+## Read/Writing an Excel file using the SpreadSheet Interface Component
225+
226+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+
228+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+
230+**Functionality:**
231+
232+- creating and loading excel workbooks,
233+- saving workbooks,
234+- creating and accessing sheets,
235+- getting values and setting values (and formulas) in cells,
236+- getting and changing color of cells,
237+- getting and changing column height and row width,
238+- creating comments,
239+- creating shapes,
240+- creating charts.
241+
242+**Inits first.**
243+
244+``` pascal
245+ IMPLEMENTATION
246+ USES
247+ ExcelUtilities,
248+ SpreadSheetInterfaces ;
249+
250+ VAR
251+ aCell : IRange ;
252+ aValue : OleVariant ; // Not sure about this, but it works. ie( Edit.Text := STRING(aValue); )
253+ ExcelApp : TExcelApplication ;
254+ ExcelWbs : IWorkBooks ;
255+
256+ ExcelBook : IWorkBook ;
257+ ExcelSheet : ISheet ;
258+ ExcelSheets : ISheets ;
259+```
260+
261+Getting a sheet is simple:
262+
263+``` pascal
264+ // Initializing the common excel workbook:
265+ ExcelApp := TExcelApplication.Create(nil) ;
266+ ExcelApp.Active := True ;
267+ ExcelApp.Visible := True ;
268+
269+ ExcelWbs := ExcelApp.WorkBooks ;
270+ ExcelBook := ExcelWbs.Add ;
271+ ExcelSheets := ExcelBook.Sheets ;
272+ ExcelSheet := ExcelSheets.Sheet(1) ;
273+```
274+
275+Playing around with cells is simple too:
276+
277+``` pascal
278+ // adding a value
279+ aCell := ExcelSheet.Cells(1, 1) ;
280+ aCell.Value := 10;
281+
282+ // adding a formula
283+ aCell := ExcelSheet.Cells(2,1) ;
284+ aCell.Formula := '=A1+10' ;
285+
286+ // getting the value computed in Excel
287+ aValue := aCell.Value ;
288+```
289+
290+The test case provided has many more examples.
291+
292+## Copy HTML to the clipboard
293+
294+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+
296+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+
298+``` pascal
299+uses
300+ ClipBrd;
301+...
302+ // register the mime type for text/html. You can do this once at program start:
303+ ClipbrdFmtHTML:=RegisterClipboardFormat('text/html');
304+...
305+ // Clear any previous formats off the clipboard before starting
306+ Clipboard.Clear;
307+
308+ // put text and html on the clipboard. Other applications will choose the best format automatically.
309+ ClipBoard.SetAsHtml('<b>Formatted</b> text', 'Simple Text');
310+```
311+
312+## See also
313+
314+- [[Clipboard]]
315+- [[PowerpointAutomation/de|Powerpoint Automation]]
316+
317+## External links
318+
319+- [Excel file format](http://sc.openoffice.org/excelfileformat.pdf) - description on OpenOffice website
320+
321+---
322+*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