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

Окружающая среда установки:

Я разрабатываю надстройку уровня приложения Excel 2010 с использованием vb.net с .NET framework 4.

Моя цель:

  1. Попросите пользователя ввести несколько имен для поиска
  2. Используйте список имен для выполнения SQL-запроса в крупной электронной таблице (30 000+ строк)
  3. Верните набор записей и вставьте в новый рабочий лист

Производительность – мой приоритет. Я хотел бы узнать самый быстрый способ сделать это, используя платформу .NET.

Использование ADO Connection Object в моем коде работает, но процесс занимает слишком много времени (5-8 секунд).

Это SQL-запрос, который я использую в таблице с именами:

SELECT * FROM wells WHERE padgroup in (SELECT padgroup FROM wells WHERE name LIKE 'TOMCHUCK 21-30' OR name LIKE 'FEDERAL 41-25PH') 

Вот часть таблицы:

Таблица Excel

Я использую этот код прямо сейчас, чтобы создать объект соединения ADO для получения моих результатов:

  'Create Recordset Object rsCon = CreateObject("ADODB.Connection") rsData = CreateObject("ADODB.Recordset") rsCon.Open(szConnect) rsData.Open(mySQLQueryToExecute, rsCon, 0, 1, 1) 'Check to make sure data is received, then copy the data If Not rsData.EOF Then TargetRange.Cells(1, 1).CopyFromRecordset(rsData) Else MsgBox("No records returned from : " & SourceFile, vbCritical) End If 'Clean up the Recordset object rsData.Close() rsData = Nothing rsCon.Close() rsCon = Nothing 

Основываясь на том, что я знаю, электронные таблицы Excel хранятся в формате Open XML, а среда .NET включает встроенную поддержку для анализа XML.

Изучив его, я столкнулся с несколькими различными вариантами:

  • Open XML SDK
  • Простой API для XML (SAX)
  • LINQ to SQL

Может ли кто-нибудь указать указатель на то, какой метод лучше всего использовать? Я бы очень признателен.

Дополнительные замечания:

  • Все запросы должны выполняться без подключения к онлайн-базе данных
  • Мне нужен только один раз доступ к электронной таблице для извлечения необработанных данных из строк

Сейчас я просто вставляю таблицу в качестве ресурса проекта.

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

  'Create temp file path in the commonapplicationdata folder Dim excelsheetpath As StringBuilder excelsheetpath = New StringBuilder(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData)) excelsheetpath.Append("\MasterList.xlsm") 'Save resources into temp location in HD System.IO.File.WriteAllBytes(excelsheetpath.ToString, My.Resources.MasterList) 'Now call the function to use ADO to get records from the MasterList.xlsm file just created GetData(excelsheetpath.ToString, "Sheet1", "A1:S40000", True, False) 'Store the results in-memory and display by adding to a datagridview control (in a custom task pane) 'Delete the spreadsheet System.IO.File.Delete(excelsheetpath.ToString()) 

Вы неправильно выполняете 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:

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 
  • удаление названных диапазонов в VSTO
  • VSTO c #: Как выделить строку и столбец активной ячейки в Excel?
  • VSTO - Прерывание открытия книги Excel
  • Как получить размер (ширину и высоту) области с помощью excel vsto c #, исключая область ленты?
  • Как мне программным образом получить адрес скопированного выделения из буфера обмена
  • screenupdating = false, пользовательский интерфейс Excel замерзнет
  • Как я могу изменить версию Excel, которая используется Visual Studio для отладки?
  • Сохранение пользовательских входов для последующего использования C #
  • Excel / Visual Studio / C #. Как изменить отображаемое имя ячейки, но сохранить формулу
  • Исключение из HRESULT: 0x800A03EC - Запись массива в диапазон в надстройке Excel
  • Удаление Excel ListObject после его отмены
  • Interesting Posts

    Как получить значения из раскрывающегося списка в MsExcel?

    Формула HEX2OCT в MS Excel возвращает неверный результат

    Защищенный рабочий лист позволяет редактировать формат ячейки путем копирования и вставки

    поиск кода для конкретного текста, затем удаление его и замена его другим словом

    Вложенные IF-предложения не анализируются, поскольку я ожидаю

    SQL Query Excel – возвращает «0», если значение не найдено

    Проверить запись на несколько столбцов

    Аргумент Excel не является необязательным

    Как десериализовать файл Excel в 2-мерный массив?

    Excel – вытягивание нескольких значений из одной ячейки

    VBA вы можете получить элементы по источнику (GetElementBySrc)

    Проверьте, является ли конкретный лист активным

    Excel (или Python / R / SQL): размещение всех совпадений в отдельных строках

    VBA: Как сохранить формат csv при передаче данных Excel?

    Process tracker – формулы, vba или условное форматирование?

    Давайте будем гением компьютера.