Каков наилучший способ выполнить SQL-запрос в большом файле excel с помощью vb.net?
Окружающая среда установки:
Я разрабатываю надстройку уровня приложения Excel 2010 с использованием vb.net с .NET framework 4.
- Доступ к рабочему листу Excel в файле класса C # с использованием VSTO
- Сохранение буфера обмена при загрузке VSTO
- В Excel VSTO, как я могу проверить, принадлежит ли лист закрытой книге?
- Программирование Excel
- VSTO Excel: обнаружение последней строки объекта ListObject удалена
Моя цель:
- Попросите пользователя ввести несколько имен для поиска
- Используйте список имен для выполнения SQL-запроса в крупной электронной таблице (30 000+ строк)
- Верните набор записей и вставьте в новый рабочий лист
Производительность – мой приоритет. Я хотел бы узнать самый быстрый способ сделать это, используя платформу .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')
Вот часть таблицы:
Я использую этот код прямо сейчас, чтобы создать объект соединения 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())
- Как работает Excel VSTO?
- C # VSTO: получить текущее название рабочей книги из класса листа
- F # Excel UsedRange не имеет свойств или методов
- vsto: Отказывание разных Excel-книг из приложений Windows Forms в одних и тех же решениях с использованием c #
- Создание UDF с VSTO в Excel
- Асинхронный вызов inprocess COM-DLL из VSTO Excel Addin?
- Как скопировать лист Excel в новую книгу и привести все диаграммы, изображения и т. Д.?
- Range.address не соответствует адресу range.cell
Вы неправильно выполняете 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