Export data into EXCEL using ADO

Probably many people know that using the ADO technology it is possible to read quickly the large amounts of information from the excel books. But using this technology it is also possibly to create and fill easily the excel files.
Do not believe? Check this…

So, a problem definition: Create excel file using ADO mechanism and dump in it from the nomenclature catalog the code and description.

ExcelFileName = "D:\Export.xls"; // Name of created file 

// Prepare a connection string 
ConnectionString = "
|Provider=Microsoft.Jet.OLEDB.4.0;
|Data Source=" + ExcelFileName + ";
|Extended Properties=""Excel 8.0;HDR=No;"";";

// Create an object ADOX.Catalog
Catalog = New COMObject("ADOX.Catalog");
Catalog.ActiveConnection = ConnectionString;

// Create table and add to it two columns 
Table = New COMObject("ADOX.Table");
Table.Name = "Table";
Table.Columns.Append("Code");
Table.Columns.Append("Description");

// Append table
Catalog.Tables.Append(Table);
Table = Undefined;
Catalog = Undefined;

// Create connection 
Connection = New COMObject ("ADODB.Connection");
Connection.Open(ConnectionString);

Command = New COMObject ("ADODB.Command");
Command.ActiveConnection = Connection;
Command.CommandType = 1;

// Receive nomenclature to dump 
Query = New Query("
|SELECT
|    Goods.Code,
|    Goods.Description
|FROM
|    Catalog.Goods AS Goods");

Result = Query.Execute();
Selection = Result.Select();

// Fill table with sample data 
While Selection.Next() Do
	Command.CommandText = "
	|INSERT INTO Table VALUES ('" + Selection.Code + "','" + Selection.Description + "')";
	Command.Execute();
EndDo;

// Close connection
Command = Undefined;
Connection.Close();
Connection = Undefined;

Everything is ready!
The advantages of this method to create an excel file:
— High operating speed.
— No need to start the process excel.exe itself as in the case of use of OLE-server excel.application, and, therefore, the resources are not wasted for it. In addition, there is no need to track and capture the errors associated with the process excel.exe, those who used OLE-connection will understand what I am talking about, there is a little joy when in case of a software or user error the process excel.exe remains hanging in the memory.
— No need of EXCEL application itself, it is enough to have on the computer the installed OLEDB provider Microsoft.Jet.OLEDB.4.0 (it is a part of almost all OS of Windows series). This is especially urgent when this code is executed on the 1С:Enterprise server, for example, like a scheduled job.
The disadvantages are also available:
— it is impossible to design beautifully the cells — font, borders, etc.
P.S. A «combined» method is possible: using ADO a file is generated and then, using OLE, «a makeup is got».

[Total: 0    Average: 0/5]

Leave a Reply

Your email address will not be published. Required fields are marked *