Каков наилучший способ выполнить SQL-запрос в большом файле excel с помощью vb.net?

Вы неправильно выполняете VSTO;) Не используйте SQL с Excel. Если вам нужна скорость, используйте VSTO и собственные API-интерфейсы Excel. Вы можете пропустить накладные расходы слоев ADODB / OLEDB и перейти прямо к объектной модели Excel, чтобы использовать пылающий быстрый Autofilter в Excel, метод SpecialCells для получения только видимых ячеек в многодиапазонный диапазон, а метод Value для быстрой копирования диапазон до массива.

Вот примерная настраиваемая книга VSTO 2010, которая быстро ищет список из 58 тыс. Слов для слов, содержащих «aba», «cat» или «zon».

 using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Windows.Forms; using System.Xml.Linq; using Microsoft.Office.Tools.Excel; using Microsoft.VisualStudio.Tools.Applications.Runtime; using Excel = Microsoft.Office.Interop.Excel; using Office = Microsoft.Office.Core; namespace ExcelWorkbook1 { public partial class ThisWorkbook { private void ThisWorkbook_Startup(object sender, System.EventArgs e) { const int Sheet1 = 1; // you can use Linq to find a sheet by name if needed const int ColumnB = 2; List<List<object>> results = Query(Sheet1, ColumnB, "aba", "cat", "zon"); foreach (List<object> record in results) { System.Diagnostics.Debug.Print("{0,-10} {1,30} {2}", record[0], record[1], record[2]); } } private void ThisWorkbook_Shutdown(object sender, System.EventArgs e) { } /// <summary> /// Removes any existing Excel autofilters from the worksheet /// </summary> private void ClearFilter(Microsoft.Office.Interop.Excel._Worksheet worksheet) { if (worksheet.AutoFilter != null) { worksheet.Cells.AutoFilter(); } } /// <summary> /// Applies an Excel Autofilter to the worksheet for search for an array of substring predicates /// </summary> private void ApplyFilter(Microsoft.Office.Interop.Excel._Worksheet worksheet, int column, params string[] predicates) { string[] criteria = new string[predicates.Length]; int i = 0; ClearFilter(worksheet); foreach (string value in predicates) { criteria[i++] = String.Concat("=*", value, "*"); } worksheet.Cells.AutoFilter(column, criteria, Excel.XlAutoFilterOperator.xlOr); } /// <summary> /// Returns a list of rows that are hits on a search for an array of substrings in Column B of Sheet1 /// </summary> private List<List<object>> Query(int sheetIndex, int columnIndex, params string[] words) { Microsoft.Office.Interop.Excel._Worksheet worksheet; Excel.Range range; List<List<object>> records = new List<List<object>>(); List<object> record; object[,] cells; object value; int row, column, rows, columns; bool hit; try { worksheet = (Microsoft.Office.Interop.Excel._Worksheet)Globals.ThisWorkbook.Sheets[sheetIndex]; if (null == worksheet) { return null; } // apply the autofilter ApplyFilter(worksheet, columnIndex, words); // get the range = worksheet.Range["$A:$C"].SpecialCells(Excel.XlCellType.xlCellTypeVisible); foreach (Excel.Range subrange in range.Areas) { // copy the cells to a multidimensional array for perfomance cells = subrange.Value; // transform the multidimensional array to a List for (row = cells.GetLowerBound(0), rows = cells.GetUpperBound(0); row <= rows; row++) { record = new List<object>(); hit = false; for (column = cells.GetLowerBound(1), columns = cells.GetUpperBound(1); column <= columns; column++) { value = cells[row, column]; hit = hit || (null != value); if (hit) { record.Add(cells[row, column]); } } if (hit) { records.Add(record); } } } } catch { } finally { // use GC.Collect over Marshal.ReleaseComObject() to release all RCWs per http://stackoverflow.com/a/17131389/1995977 and more cells = null; GC.Collect(); GC.WaitForPendingFinalizers(); } return records; } #region VSTO Designer generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InternalStartup() { this.Startup += new System.EventHandler(ThisWorkbook_Startup); this.Shutdown += new System.EventHandler(ThisWorkbook_Shutdown); } #endregion } } 

Файлы Excel 2010 не совсем XML. Возьмите файл XLSX (или XMSM) и переименуйте его с расширением .zip. Затем извлеките его в новую папку. Файлы в подпапках будут файлами XML, да, но фактический файл XLSX представляет собой zip-файл, содержащий коллекцию папок, содержащих XML-файлы.

Я считаю, что лучше всего использовать драйверы ACE (JET больше не поддерживается) и получить доступ к нему через ODBC. Если это не достаточно быстро, вы можете извлечь извлечение в определенное время и загрузить их в базу данных, из которой вы можете запускать свои запросы; запросы должны быть быстрее, но будут потенциально устаревшими.

Мое решение:

Я пробовал три разных подхода:

• Объект соединения ADO с SQL (самый медленный)
• Автономщик VSTO и Excel (надежный)
• LINQ to XML (самый быстрый)

LINQ to XML предложили лучшую производительность. Я преобразовал таблицу в файл XML:

Затем в моем коде я использовал StringReader для ввода файла XMLwellData (который сохраняется как ресурс проекта).

  'welldoc will be the file to do queries on using LINQ to XML Dim stream As System.IO.StringReader stream = New StringReader(My.Resources.XMLwellData) welldoc = XDocument.Load(stream) 'clean up stream now that it's no longer needed stream.Close() stream.Dispose() '***** later in the code perform my query on XML file ********* Dim query = _ From well In welldoc.<wellList>.<well> _ Where well.<name>.Value Like "TOMCHUCK 21-30" _ Select well For Each well in query MessageBox.Show(well.<padgroup>.value) Next 

Это было удивительно просто сделать то, что я хотел, и лучше всего это было FAST.

Благодарим вас за помощь и предложения. Для меня это все изменило.

Альтернативный метод с использованием автофильтра Excel

Если вы пытаетесь использовать код, предложенный в другом ответе, это будет фильтровать только два значения:

  worksheet.Cells.AutoFilter(column, criteria, Excel.XlAutoFilterOperator.xlOr); 

Таким образом, для фильтрации с несколькими критериями с использованием Auotfilter из Excel вы должны передать свои аргументы в виде массива и фильтровать по xlFilterValues.

  Dim wrkbk As Excel.Workbook Dim wrksht As Excel.Worksheet Dim myRange As Excel.Range Dim cell As Excel.Range 'You would add all of your wellnames to search to this List Dim wellNames As New List(Of String) wrksht = wrkbk.Sheets(1) 'In my excel file there is a Named Range which includes the all the information myRange = wrksht.Range("WellLookUpTable") 'Notice, for `Criteria1:=` you MUST convert the List to an array With wrksht.Range("WellLookUpTable") .AutoFilter(Field:=2, Criteria1:=wellNames.ToArray, Operator:=Excel.XlAutoFilterOperator.xlFilterValues) End With myRange = wrksht.Range("A2", wrksht.Range("A2").End(Excel.XlDirection.xlDown)).Cells.SpecialCells(Excel.XlCellType.xlCellTypeVisible) For Each cell In myRange 'column 11 is padgroup MessageBox.Show(cell.Offset(0, 11).Value) Next