Conventional methods to work with Excel files (using COM-object “Excel.Application”) satisfy as long as you meet the files with 30 000 lines and a few dozen of columns. The time of execution and processing of such file grows terribly. It is certainly possible to write: “and now you can go to smoke” like some developers do, but this is the wrong approach. You should always try to find more efficient and elegant solution.
So, there is a need to download specific Excel files from the client site to the base of 1С:Enterprise system. The standard method of subsequent read showed that 30 000 lines are downloaded for 12 minutes a little after. After using the method with ADO, the download speed was reduced to 20 seconds.

After a little acquaintance with documentation of ADO by reading MSDN, an idea appeared to access the Excel table as a regular table of some DB using a request.
Here is a code used in the function, you can modify the code for yourself.

Let me describe briefly the current algorithm:

1. Form the initial parameters and call the import function.
2. Create COM object.
3. Form a string of required fields (according to the columns selected by the user).
4. Build a request.
5. Execute a request to file.
6. Build a table for the further processing.

The code:

Procedure ReadData()
	
	ImportedFileName = "C:\temp\price.xls";
	
	//Read data from this Sheet
	TableName = "Sheet1";
	
	//Let's read from Excel just this columns
	ColumnsTable = New ValueTable;
	ColumnsTable.Columns.Add("ColumnNumber");
	ColumnsTable.Columns.Add("ColumnName");
	
	NewRow = ColumnsTable.Add();
	NewRow.ColumnNumber = 2;
	NewRow.ColumnName = "GoodsCode";
	
	NewRow = ColumnsTable.Add();
	NewRow.ColumnNumber = 3;
	NewRow.ColumnName = "GoodsDescr";
	
	NewRow = ColumnsTable.Add();
	NewRow.ColumnNumber = 4;
	NewRow.ColumnName = "Quantity";
	
	NewRow = ColumnsTable.Add();
	NewRow.ColumnNumber = 5;
	NewRow.ColumnName = "Price";
	
	NewRow = ColumnsTable.Add();
	NewRow.ColumnNumber = 6;
	NewRow.ColumnName = "Sum";
		
	Result = ReadDataFromExcel(ImportedFileName, TableName, ColumnsTable);
	
	//Do something with data in table Result...
	
EndProcedure

Function ReadDataFromExcel(ImportedFileName, TableName, ColumnsTable)
	
	ImportedTable = New ValueTable;
	
	Connection = New COMObject("ADODB.Connection");
	
	ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " + ImportedFileName;
	ConnectString = ConnectString + "; Extended Properties =""Excel 8.0;HDR=NO;IMEX=1"";";
	
	Try
		Connection.Open(ConnectString);
	Except
		Message(ErrorDescription());
		Return ImportedTable;
	EndTry;
	
	Command = New COMObject("ADODB.Command");
	
	AxCatalog = New COMObject("ADOX.Catalog");                                                
	AxCatalog.ActiveConnection = Connection;
	
	ColumnsTable.Sort("ColumnNumber ASC");

	QueryTextColumns = "";
	StringColumn = "";
	
	For Each TableRow In ColumnsTable Do
		
		QueryTextColumns = QueryTextColumns + ?(QueryTextColumns = "", "", ", ") + "F" + TableRow.ColumnNumber;
		
		ImportedTable.Columns.Add(TableRow.ColumnName);
		
	EndDo;
	
	RecordSet = New COMObject("ADODB.RecordSet");
	
	Command.ActiveConnection = Connection;
	Command.CommandText = "SELECT " + QueryTextColumns + " FROM [" + TableName + "]";
	Command.CommandType = 1;
	
	Try         
		RecordSet = Command.Execute();
	Except
		Message(ErrorDescription());
		Return ImportedTable;
	EndTry;
	
	//Skip first row with header
	RecordSet.MoveNext();
	
	While RecordSet.EOF() = 0 Do
		
		NewRow = ImportedTable.Add();
		
		Count = 0;
		For Each Column In ImportedTable.Columns Do
			
			NewRow[Column.Name] = TrimAll(String(RecordSet.Fields(Count).Value));
			Count = Count + 1;
			
		EndDo;
		
		RecordSet.MoveNext();
		
	EndDo;
	
	RecordSet.Close();
	Connection.Close();

EndFunction

So, we have the output table “Result” which can be processed as you wish - display the content for the user or save in database.
It should be noted that all the values are obtained in a string type, so you need to process them correctly. But that’s worth the effort, because the speed increment is really significant. The most interesting in the work with Excel file like with COM object is that it is possible to receive all the necessary properties and access the required sheets. The request can be built with more difficulty with filters and joins, so the possibilities are enormous.

P.S. if there are different value types in a single column of Excel file (for example, in the first 10 lines - numbers and then the strings are here and there), then such data can be lost when reading through ADO. You can fin in the Internet a solution for these problems.
IMEX=1 - the parameter can be specified in the connection string
TypeGuessRows - the parameter must be set in the registry equal to “0”, otherwise, the driver checks only the first 8 rows to determine the value type in the column.