What is the best way to execute an SQL query in a large excel file using vb.net?

Question

Environment setup:

I am developing an Excel 2010 application level add-in using vb.net with the .NET framework 4.

My goal:

• Ask user to enter multiple names for search
• Use a list of names to execute an SQL query in a large spreadsheet (30,000 rows)
• Return the recordset and paste into a new worksheet

Performance is my priority. I would like to know the fastest way to do this using the .NET platform.

Using the ADO Connection object in my code works, but the process takes too much time (5-8 seconds).

This is the SQL query that I use in the table with the names:

    SELECT * 
    FROM wells 
    WHERE padgroup in 

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

Here is a part of the table:

I am using this code right now to create an ADO connection object to get my results:

    '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())

Answer 1

:

:

• ADO SQL ( )
• VSTO Excel Autofilter ()
• 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 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.

. , .

Autocilter Excel

, , :

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

, Excel Auotfilter, 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

Answer 2

VSTO;) SQL Excel. , VSTO API- Excel. ADODB/OLEDB Excel, Autofilter Excel, SpecialCells , Value .

VSTO 2010, 58k , "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

    }
}

Answer 3

Excel 2010 XML. XLSX ( XMSM) .zip. . XML, , XLSX zip , , XML.

, , ACE- (JET ) ODBC. , , ; , .