Click here to Skip to main content
Click here to Skip to main content
Go to top

ExcelQueryAnalyzer .Net 2.0

, 26 Feb 2007
Rate this:
Please Sign up or sign in to vote.
ExcelQueryAnalyzer for Excel Queries

Sample Image - Eqa.jpg

Introduction

This article shows how can you use excel with SQL queries.Like SQL Query Analyzer it shows your query results on result panel.You can see your sql code with highlighted keywords like select,update etc. and can easily select your excel file as data source and run your query on it.

Usage

On the main menu type your query and click submit.

SELECT * FROM [SHEET1$] WHERE USERS IN(SELECT US_ID FROM [SHEET2$])

SHEET1 AND SHEET2 your excel sheets.You can modify it with your own.

Code

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.IO

// Running Query On Selected Excel File 

public DataSet DataReader(string SqlQ,string DataSource) 


        {
            string ConStr="Provider=Microsoft.Jet.OleDb.4.0;Data source="+ DataSource +";Extended Properties=\"Excel 8.0;IMEX=1\"";
            OleDbConnection Conn = new OleDbConnection(ConStr);
            Conn.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(SqlQ,Conn);
            OleDbCommand cmd = new OleDbCommand();
            DataSet ds = new DataSet();
            ds.DataSetName = "Result Panel";
            adapter.Fill(ds);
            return ds;
        }


//Exports Result To Excel File 
public void ExportToExcel() 

        {
            Excel.ApplicationClass excel = new Excel.ApplicationClass();
            excel.UserControl= true;
            System.Globalization.CultureInfo oldCI= System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
            excel.Application.Workbooks.Add(true);
            System.Data.DataTable table = DataReader(Query,DataSource).Tables[0];//dataset.Tables[0];
            int ColumnIndex=0;
            foreach( System.Data.DataColumn col in table.Columns) 
            {
                ColumnIndex++; 
                excel.Cells[1,ColumnIndex]=col.ColumnName; 
            } 
            int rowIndex=0;
            foreach(DataRow row in table.Rows) 
            {
                rowIndex++; 
                ColumnIndex=0; 
                foreach(DataColumn col in table.Columns) 
                {
                    ColumnIndex++; 
                    excel.Cells[rowIndex+1,ColumnIndex]=row[col.ColumnName]; 
                } 
            }

            excel.Visible = true;
            Excel.Worksheet Worksheet= (Excel.Worksheet)excel.ActiveSheet;
            Worksheet.Activate();
        
        }


//show results in result panel 
public void ExportToGrid(ref DataGrid grid) 

grid.DataSource = DataReader(Query,DataSource);
grid.Show();


//export result to txt file

public void ExportToTxt() 


        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "Excel (.xls)|*.xls|Text (.txt)|*.txt|Word (.doc)|*.doc";
            sfd.FileName = "NewDocument";
            sfd.ShowDialog();
            FileStream fs = new FileStream(sfd.FileName.ToString(),FileMode.Create);
            StreamWriter sw = new StreamWriter(fs);

            System.Data.DataTable table = DataReader(Query,DataSource).Tables[0];
            
            foreach(DataRow row in table.Rows) 
            {
                foreach(DataColumn col in table.Columns) 
                {
                    sw.Write(row[col].ToString()+ "\t");
                } 
                sw.Write("\n");
            }
            sw.Close();
        }

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

omerfarukirmak
Web Developer
Turkey Turkey
No Biography provided

Comments and Discussions

 
BugI could not open the executable PinmemberAhmadeidmohamed25-Nov-12 19:24 
QuestionWant to Add NEW sheet Pinmemberbhaskar00118-Oct-07 3:57 
Generalmissing dlls Pinmemberbalazs_hideghety11-Jan-07 2:22 
GeneralRe: missing dlls Pinmemberomerfarukirmak14-Jan-07 22:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 27 Feb 2007
Article Copyright 2006 by omerfarukirmak
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid