Office Automation/zh CN: what changed

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

Title: Office Automation/zh CN

New		Line
1	+	(Windows only)
2	+
3	+	与 Office 办公软件交互，从代码中生成电子表格，文本文档及幻灯片的能力对办公来说是非常有价值的，可以节省大量时间避免做重复任务。
4	+
5	+	这样的一种例子是建立一可以读取任意格式文件的程序，并输出为一 Excel 文档，这项任务使用代码完成远比手工完成高效地多。
6	+
7	+	## 使用 OpenOffice UNO 桥
8	+
9	+	OpenOffice 有针对 C++, Java, JavaScript 和 Python 的语言绑定件。在 Windows 平台上，OpenOffice 也可通过 COM 自动化使用 Pascal 操纵（见下文），但目前在 OS X 和 Linux 上使用 Pascal 操纵 OpenOffice 的 UNO （通用网络对象）并不那么容易。如果您对使用 Pascal 开发面向对象的”桥“感兴趣，请参看如下联接以获取更多信息。（注意：以下连接特别具有 Sun 专业范儿)
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	+	## 使用 COM 自动化与 OpenOffice 和 Microsoft Office 交互
16	+
17	+	自动化是 Windows 特有的所以以下两个事例不能在 OS X 或 Linux 上运行. 对于后两者请参见 [Making do without Windows COM Automation](http://wiki.lazarus.freepascal.org/Multiplatform_Programming_Guide#Making_do_without_Windows_COM_Automation). 如果您只是想在您的程序中创建或查看文字处理文档，参看以下联接 [XDev Toolkit](http://web.fastermac.net/~MacPgmr/XDev/XDevStatus.html#RTF).
18	+
19	+	### Windows 上的 OpenOffice
20	+
21	+	以下是简单示例，展示了如何使用 OpenOffice 自动化服务器打开一文档。注意该示例仅可在 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	+	### Windows 上的 Office
72	+
73	+	以下是简单示例，展示了如何在您的程序中使用 Word 自动化服务器打开一文档。注意该示例仅可在 Windows 下运行。可在 Delphi 和 FPC 下编译通过。
74	+
75	+	``` pascal
76	+	program TestMsOffice;
77	+
78	+	{$IFDEF FPC}
79	+	{$MODE Delphi}
80	+	{$ELSE}
81	+	{$APPTYPE CONSOLE}
82	+	{$ENDIF}
83	+
84	+	uses
85	+	SysUtils, Variants, ComObj;
86	+
87	+	const
88	+	ServerName = 'Word.Application';
89	+	var
90	+	Server : Variant;
91	+	w:widestring;
92	+	begin
93	+	if Assigned(InitProc) then
94	+	TProcedure(InitProc);
95	+
96	+	try
97	+	Server := CreateOleObject(ServerName);
98	+	except
99	+	WriteLn('Unable to start Word.');
100	+	Exit;
101	+	end;
102	+
103	+	{Open existing document} //Substitute your path and doc
104	+	w:= UTF8Decode('c:\my\path\mydoc.doc');
105	+	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.
106	+	Server.Visible := True; {Make Word visible}
107	+
108	+	end.
109	+	```
110	+
111	+	以下是展示如何使用Word自动化服务器创建一Word文档对象
112	+
113	+	``` pascal
114	+	var
115	+	Server: Variant;
116	+	begin
117	+	try
118	+	Server := GetActiveOleObject('Word.Application');
119	+	except
120	+	try
121	+	ShowMessage('Word not already open create a Word Object');
122	+	// If no instance of Word is running, try to Create a new Word Object
123	+	Server := CreateOleObject('Word.Application');
124	+	except
125	+	ShowMessage('Cannot start Word/Word not installed ?');
126	+	Exit;
127	+	end;
128	+	end;
129	+	end;
130	+	```
131	+
132	+	限制: 因为 End 是FPC中的保留字，在&号后应被用做参数。
133	+
134	+	``` pascal
135	+	Server.ActiveDocument.Application.Selection.start:=Server.ActiveDocument.Application.Selection.&end+1;
136	+	```
137	+
138	+	许多Excel的示例可参见该主题的德语Wiki页面 [[ExcelAutomation/de]].
139	+
140	+	## 使用 fpXMLXSDExport 单元
141	+
142	+	FPC 2.6及更新的版本包含fpXMLXSDExport单元，它是FCL-DB导出组件的一部分。有了它您就可以导出数据集合到各种XML格式，包括Microsoft Access兼容格式和Excel兼容格式。
143	+
144	+	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.
145	+
146	+	In the Excel format, multiline text fields are not supported at the moment: the line ends are removed during the export.
147	+
148	+	Lazarus provides a visual component for this: after installing the lazdbexport package, you will see the TXMLXSDExporter component on the Data Export tab
149	+
150	+	See [[fpXMLXSDExport]] for details.
151	+
152	+	## Using the FPSpreadsheet Library
153	+
154	+	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.
155	+
156	+	The advantages are that fpspreadsheet is 100% [[Object Pascal]] code, and it requires no external libraries or programs.
157	+
158	+	## Writing an Excel file using ADO
159	+
160	+	please write me.
161	+
162	+	## Reading/Writing an Excel file using OLE
163	+
164	+	This method needs Excel to be installed on the user's machine because it uses OLE to access it.
165	+
166	+	*Keep in mind that this method starts Excel in the background, which opens the file and works with it like a real user.*
167	+
168	+	- Create a new form with button, stringgrid and edit.
169	+	- Create a new Excel file and fill a few cells.
170	+
171	+	<figure>
172	+	<img src="excel123.png" title="excel123.png" />
173	+	<figcaption>excel123.png</figcaption>
174	+	</figure>
175	+
176	+	Example - Open/Read Excel file:
177	+
178	+	``` pascal
179	+	uses ..... comobj;
180	+
181	+	procedure TForm1.Button1Click(Sender: TObject);
182	+
183	+	Var XLApp: OLEVariant;
184	+	x,y: byte;
185	+	path: variant;
186	+
187	+	begin
188	+	XLApp := CreateOleObject('Excel.Application'); // requires comobj in uses
189	+	try
190	+	XLApp.Visible := False; // Hide Excel
191	+	XLApp.DisplayAlerts := False;
192	+	path := edit1.Text;
193	+	XLApp.Workbooks.Open(Path); // Open the Workbook
194	+	for x := 1 to 4 do
195	+	begin
196	+	for y := 1 to 6 do
197	+	begin
198	+	SG.Cells[x,y] := XLApp.Cells[y,x].Value; // fill stringgrid with values
199	+	end;
200	+	end;
201	+	finally
202	+	XLApp.Quit;
203	+	XLAPP := Unassigned;
204	+	end;
205	+	```
206	+
207	+	If you want to make some changes and you want them to write back into the Excel, file you can use:
208	+
209	+	``` pascal
210	+	XLApp.Cells[x,y].Value := SG.Cells[y,x];
211	+	```
212	+
213	+	If you want to save:
214	+
215	+	``` pascal
216	+	XLApp.ActiveWorkBook.Save;
217	+	```
218	+
219	+	## Read/Writing an Excel file using the SpreadSheet Interface Component
220	+
221	+	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)
222	+
223	+	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.
224	+
225	+	Functionality:
226	+
227	+	- creating and loading excel workbooks,
228	+	- saving workbooks,
229	+	- creating and accessing sheets,
230	+	- getting values and setting values (and formulas) in cells,
231	+	- getting and changing color of cells,
232	+	- getting and changing column height and row width,
233	+	- creating comments,
234	+	- creating shapes,
235	+	- creating charts.
236	+
237	+	Inits first.
238	+
239	+	``` pascal
240	+	IMPLEMENTATION
241	+	USES
242	+	ExcelUtilities,
243	+	SpreadSheetInterfaces ;
244	+
245	+	VAR
246	+	aCell : IRange ;
247	+	aValue : OleVariant ; // Not sure about this, but it works. ie( Edit.Text := STRING(aValue); )
248	+	ExcelApp : TExcelApplication ;
249	+	ExcelWbs : IWorkBooks ;
250	+
251	+	ExcelBook : IWorkBook ;
252	+	ExcelSheet : ISheet ;
253	+	ExcelSheets : ISheets ;
254	+	```
255	+
256	+	Getting a sheet is simple:
257	+
258	+	``` pascal
259	+	// Initializing the common excel workbook:
260	+	ExcelApp := TExcelApplication.Create(nil) ;
261	+	ExcelApp.Active := True ;
262	+	ExcelApp.Visible := True ;
263	+
264	+	ExcelWbs := ExcelApp.WorkBooks ;
265	+	ExcelBook := ExcelWbs.Add ;
266	+	ExcelSheets := ExcelBook.Sheets ;
267	+	ExcelSheet := ExcelSheets.Sheet(1) ;
268	+	```
269	+
270	+	Playing around with cells is simple too:
271	+
272	+	``` pascal
273	+	// adding a value
274	+	aCell := ExcelSheet.Cells(1, 1) ;
275	+	aCell.Value := 10;
276	+
277	+	// adding a formula
278	+	aCell := ExcelSheet.Cells(2,1) ;
279	+	aCell.Formula := '=A1+10' ;
280	+
281	+	// getting the value computed in Excel
282	+	aValue := aCell.Value ;
283	+	```
284	+
285	+	The test case provided has many more examples.
286	+
287	+	## Copy HTML to the clipboard
288	+
289	+	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.
290	+
291	+	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.]]
292	+
293	+	``` pascal
294	+	uses
295	+	ClipBrd;
296	+	...
297	+	// register the mime type for text/html. You can do this once at program start:
298	+	ClipbrdFmtHTML:=RegisterClipboardFormat('text/html');
299	+	...
300	+	// Clear any previous formats off the clipboard before starting
301	+	Clipboard.Clear;
302	+
303	+	// put text and html on the clipboard. Other applications will choose the best format automatically.
304	+	ThePlainUTF8Text:='Simple text';
305	+	Clipboard.AsText:=ThePlainUTF8Text;
306	+
307	+	AsHTML:='<b>Formatted</b> text'; // text with formattings
308	+	Clipboard.AddFormat(ClipbrdFmtHTML,AsHTML[1],length(AsHTML));
309	+	```
310	+
311	+	## See also
312	+
313	+	- [[Clipboard]]
314	+
315	+	## External links
316	+
317	+	- [Excel file format](http://sc.openoffice.org/excelfileformat.pdf) - description on OpenOffice website
318	+
319	+	---
320	+	From the Free Pascal wiki, page "Office Automation/zh CN", as it stood on 2020-02-21 after a change by Trev, 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