Need to export data on combobox selection

Question

0.00/5 (No votes)

See more:

Hi Good Developers,

I have five comboboxes (cmbRestaurant,cmbServiceWindow,cmbEquipments,cmbSubSystems and cmbMeter)

and one date time picker. Now on selection of those i need to export data to excel sheet can someone guide me on the same.

Code is below.
I need to insert these values into Excel on the basis of combobox selection.

How can i write stored procedure for the same.
I tried with this but this stored procedure execution is taking too long time.
Can someone guide me with the same.

SQL

CREATE PROCEDURE [dbo].[Get_op_meter_data]
@Point_location VARCHAR(50),
@sw_name VARCHAR(30),
@p_equip_id CHAR(10),
@equip_id CHAR(10),
@log_date datetime,
@log_time datetime,
@ASE_Reading numeric,
@G_Reading numeric 
AS
DECLARE Create_Col CURSOR SCROLL FOR 
SELECT T.Point_location,T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time, SUM(E_Reading) ASE_Reading , SUM(G_reading) AS G_Reading
FROM (
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, C.Act_value AS E_Reading, 0 AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
AND B.point_location = C.point_Location
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'RAW_POW'
UNION
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, 0 AS E_Reading, C.Act_value AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
AND B.point_location = C.point_Location
AND B.point_device = C.point_device
AND B.functionblock = C.FunctionBlock
AND B.source_type_id = 'GAS') T
GROUP BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
ORDER BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time

Declare @TempTableVariable TABLE
(
Point_location VARCHAR(50),
sw_name VARCHAR(30), 
p_equip_id CHAR(10), 
equip_id CHAR(10),
log_date datetime,
log_time datetime, 
ASE_Reading numeric, 
G_Reading numeric
)

OPEN Create_Col

FETCH FIRST FROM Create_Col INTO @Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading

WHILE @@FETCH_STATUS = 0
Begin
insert into @TempTableVariable(Point_location,sw_name,p_equip_id,equip_id,log_date,log_time,ASE_Reading,G_Reading) 
values(@Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading)
End
FETCH NEXT FROM Create_Col INTO @Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading

CLOSE Create_Col
DEALLOCATE Create_Col

C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.CSharp;
using Excel = Microsoft.Office.Interop.Excel; 


namespace ImportDataToExcelSP
{
public partial class Form2 : Form
{

private static readonly string connectionStringName = ConfigurationManager.AppSettings.Get("ConnectionString");
private static readonly string connectionString = connectionStringName;

public Form2()
{
InitializeComponent();

this.cmbRestaurant.SelectedIndexChanged -= new System.EventHandler(this.cmbRestaurant_SelectedIndexChanged);
Restaurant();
this.cmbRestaurant.SelectedIndexChanged += new System.EventHandler(this.cmbRestaurant_SelectedIndexChanged);

}

public DataSet Restaurant()
{


SqlConnection connection;
SqlDataAdapter adapter;
SqlCommand cmd = new SqlCommand();
DataSet D = new DataSet();


connection = new SqlConnection(connectionString);

connection.Open();
cmd.Connection = connection;
// cmbRestaurant.Items.Insert(0, new List<>"Select Region", "NA"));

//Load user list
cmd.CommandType = CommandType.Text;
cmd.CommandText = "dbo.om_Equipment";
adapter = new SqlDataAdapter(cmd);
string Q = "select distinct point_location from dbo.om_Equipment";
adapter.SelectCommand = new SqlCommand(Q, connection);
adapter.SelectCommand.ExecuteNonQuery();
DataSet DS5 = new DataSet();
adapter.Fill(DS5, "DS5");
DataTable DSTbl5 = DS5.Tables["DS5"];
cmbRestaurant.DataSource = DS5.Tables[0].DefaultView;
cmbRestaurant.DisplayMember = "point_location";
cmbRestaurant.ValueMember = "point_location";
connection.Close();
connection.Dispose();
cmd.Parameters.Clear();
return D;


}

public DataSet ServiceWindow()
{

String selectedStoreName = ((DataRowView)cmbRestaurant.SelectedItem).Row["point_location"].ToString();

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "dbo.om_service_window, dbo.om_sw_location_map, dbo.om_Equipment";
connection.Open();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

string Q2 = "select distinct sw.sw_name from dbo.om_service_window sw join dbo.om_sw_location_map s on sw.sw_id=s.sw_id join dbo.om_Equipment e on s.point_location=e.point_location where s.point_location='" + selectedStoreName.ToString() + "'";
adapter.SelectCommand = new SqlCommand(Q2, connection);
adapter.SelectCommand.ExecuteNonQuery();
DataSet DS2 = new DataSet();
adapter.Fill(DS2, "DS2");
DataTable DSTbl2 = DS2.Tables["DS2"];
cmbServiceWindow.DataSource = DS2.Tables[0].DefaultView;
cmbServiceWindow.DisplayMember = "sw_name";
cmbServiceWindow.ValueMember = "sw_name";
connection.Close();
connection.Dispose();
cmd.Parameters.Clear();
return DS2;
}

public DataSet Equipments()
{
// String selectedStoreName = ((DataRowView)cmbRestaurant.SelectedItem).Row["point_location"].ToString();
String ServiceWindow = ((DataRowView)cmbServiceWindow.SelectedItem).Row["sw_name"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "dbo.om_service_window, dbo.om_sw_location_map, dbo.om_Equipment";
connection.Open();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

string Q2 = "select distinct e.equip_id from dbo.om_Equipment e join dbo.om_sw_location_map s on s.point_location=e.point_location join dbo.om_service_window sw on sw.sw_id=s.sw_id where e.p_equip_id is null and sw.sw_name='" + ServiceWindow.ToString() + "'";
adapter.SelectCommand = new SqlCommand(Q2, connection);
adapter.SelectCommand.ExecuteNonQuery();
DataSet DS2 = new DataSet();
adapter.Fill(DS2, "DS2");
DataTable DSTbl2 = DS2.Tables["DS2"];
cmbEquipments.DataSource = DS2.Tables[0].DefaultView;
cmbEquipments.DisplayMember = "sw_name";
cmbEquipments.ValueMember = "equip_id";
connection.Close();
connection.Dispose();
cmd.Parameters.Clear();
return DS2;
}


public DataSet SubSystems()
{
// String selectedStoreName = ((DataRowView)cmbRestaurant.SelectedItem).Row["point_location"].ToString();
String Equipments = ((DataRowView)cmbEquipments.SelectedItem).Row["equip_id"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "dbo.om_Equipment";
connection.Open();
// cmd = new SqlCommand("select equip_id from dbo.om_equip_functionblock", connection);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);

string Q3 = "select distinct equip_id from dbo.om_Equipment where p_equip_id='" + Equipments.ToString() + "'";
adapter.SelectCommand = new SqlCommand(Q3, connection);
adapter.SelectCommand.ExecuteNonQuery();
DataSet DS3 = new DataSet();
adapter.Fill(DS3, "DS3");
DataTable DSTbl3 = DS3.Tables["DS3"];
cmbSubSystems.DataSource = DS3.Tables[0].DefaultView;
cmbSubSystems.DisplayMember = "equip_id";
cmbSubSystems.ValueMember = "equip_id";
connection.Close();
connection.Dispose();
cmd.Parameters.Clear();
return DS3;
}

public DataSet Meter()
{
// String selectedStoreName = ((DataRowView)cmbRestaurant.SelectedItem).Row["point_location"].ToString();
String SubSystems = ((DataRowView)cmbSubSystems.SelectedItem).Row["equip_id"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "dbo.r_equp_meter_map, dbo.om_Equipment";
connection.Open();
// cmd = new SqlCommand("select equip_id from dbo.om_equip_functionblock", connection);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);

string Q4 = "select distinct source_type_id from dbo.r_equp_meter_map r join dbo.om_Equipment e on r.point_location=e.point_location where e.equip_id='"+SubSystems.ToString()+"'";
adapter.SelectCommand = new SqlCommand(Q4, connection);
adapter.SelectCommand.ExecuteNonQuery();
DataSet DS4 = new DataSet();
adapter.Fill(DS4, "DS4");
DataTable DSTbl4 = DS4.Tables["DS4"];
cmbMeter.DataSource = DS4.Tables[0].DefaultView;
cmbMeter.DisplayMember = "source_type_id";
cmbMeter.ValueMember = "source_type_id";
connection.Close();
connection.Dispose();
cmd.Parameters.Clear();
return DS4;
}

private void Form2_Load(object sender, EventArgs e)
{

}

private void cmbRestaurant_SelectedIndexChanged(object sender, EventArgs e)
{
this.cmbServiceWindow.SelectedIndexChanged -= new System.EventHandler(this.cmbServiceWindow_SelectedIndexChanged);
ServiceWindow();
this.cmbServiceWindow.SelectedIndexChanged += new System.EventHandler(this.cmbServiceWindow_SelectedIndexChanged); 

}

private void cmbServiceWindow_SelectedIndexChanged(object sender, EventArgs e)
{
this.cmbEquipments.SelectedIndexChanged -= new System.EventHandler(this.cmbEquipments_SelectedIndexChanged);
Equipments();
this.cmbEquipments.SelectedIndexChanged += new System.EventHandler(this.cmbEquipments_SelectedIndexChanged); 

}

private void cmbEquipments_SelectedIndexChanged(object sender, EventArgs e)
{
this.cmbSubSystems.SelectedIndexChanged -= new System.EventHandler(this.cmbSubSystems_SelectedIndexChanged);
SubSystems();
this.cmbSubSystems.SelectedIndexChanged += new System.EventHandler(this.cmbSubSystems_SelectedIndexChanged); 
}

private void cmbSubSystems_SelectedIndexChanged(object sender, EventArgs e)
{
Meter();
}
private void button1_Click(object sender, EventArgs e)
{
//SqlConnection cnn;
//string connectionString = null;
//string sql = null;
string data = null;
int i = 0;
int j = 0;

Excel.Application xlApp;
Excel.Workbook xlWorkbook;
Excel._Worksheet xlWorksheet;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Add(misValue);
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);

this.button1.Click -= new System.EventHandler(this.button1_Click);
String selectedStoreName = ((DataRowView)cmbRestaurant.SelectedItem).Row["point_location"].ToString();
String ServiceWindow = ((DataRowView)cmbServiceWindow.SelectedItem).Row["sw_name"].ToString();
String Equipments = ((DataRowView)cmbEquipments.SelectedItem).Row["equip_id"].ToString();
String SubSystems = ((DataRowView)cmbSubSystems.SelectedItem).Row["equip_id"].ToString();
String Meter = ((DataRowView)cmbMeter.SelectedItem).Row["source_type_id"].ToString();
this.button1.Click += new System.EventHandler(this.button1_Click);

SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = new SqlCommand("SELECT * FROM dbo.om_equipment WHERE DateColumn=:DateValue");
SqlParameter parameter = new SqlParameter();
parameter.DbType = DbType.Date;
parameter.Value = dateTimePicker1.Value;
SqlDataAdapter dscmd = new SqlDataAdapter(command);
DataSet ds = new DataSet();
dscmd.Fill(ds);

for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorksheet.Cells[i + 1, j + 1] = data;
}
}

xlWorkbook.SaveAs("Template_Energy_Data_Analysis.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkbook.Close(true, misValue, misValue);
xlApp.Quit();

releaseObject(xlWorksheet);
releaseObject(xlWorkbook);
releaseObject(xlApp);

MessageBox.Show("Excel file created , you can find the file C:\\Template_Energy_Data_Analysis.xls");
}

private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}

Posted 16-Jun-12 21:50pm

Kiran S.V

Updated 16-Jun-12 21:55pm

Mohamed Mitwalli

v2

Add a Solution

Comments

Mohamed Mitwalli 17-Jun-12 3:59am

Hi Kiran Try to make it simple Try to improve your Questions

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kiran S.V · Answer 1 · 2012-06-16T22:03:00

I have five comboboxes (cmbRestaurant,cmbServiceWindow,cmbEquipments,cmbSubSystems and cmbMeter)

and one date time picker. Now on selection of those i need to export data to excel sheet can someone guide me on the same.

Code is below.
I need to insert these values into Excel on the basis of combobox selection.

How can i write stored procedure for the same.
I tried with this but this stored procedure execution is taking too long time.
Can someone guide me with the same.

SQL

CREATE PROCEDURE [dbo].[Get_op_meter_data]
  	@Point_location VARCHAR(50),
	@sw_name VARCHAR(30),
	@p_equip_id CHAR(10),
	@equip_id CHAR(10),
	@log_date datetime,
	@log_time datetime,
	@ASE_Reading numeric,
	@G_Reading numeric
	

		
AS
DECLARE  Create_Col CURSOR SCROLL FOR 
SELECT T.Point_location,T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time, SUM(E_Reading) ASE_Reading , SUM(G_reading) AS G_Reading
FROM (
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, C.Act_value AS E_Reading, 0 AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
AND   B.point_location = C.point_Location
AND   B.point_device = C.point_device
AND   B.functionblock = C.FunctionBlock
AND B.source_type_id = 'RAW_POW'
UNION
SELECT A.Point_location,SW.sw_name, A.p_equip_id, A.equip_id, CONVERT(DATE,C.log_date) AS log_date, CONVERT(VARCHAR(20), C.log_hrs)+ ':' + CONVERT(VARCHAR(20),C.log_min) AS log_time, 0 AS E_Reading, C.Act_value AS G_reading
FROM om_equipment A, om_equip_meter_map B, om_service_window SW, om_sw_location_map S, tt_raw_meter C
WHERE A.equip_id = B.equip_id
AND SW.sw_id=S.sw_id
AND   B.point_location = C.point_Location
AND   B.point_device = C.point_device
AND   B.functionblock = C.FunctionBlock
AND B.source_type_id = 'GAS') T
GROUP BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time
ORDER BY T.Point_location, T.sw_name, T.p_equip_id, T.equip_id, log_date, log_time

Declare @TempTableVariable TABLE
(
Point_location  VARCHAR(50),
sw_name VARCHAR(30), 
p_equip_id CHAR(10), 
equip_id CHAR(10),
log_date datetime,
log_time datetime, 
ASE_Reading  numeric, 
G_Reading  numeric
)

OPEN Create_Col

FETCH FIRST FROM Create_Col INTO @Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading
 
WHILE @@FETCH_STATUS = 0
Begin
insert into @TempTableVariable(Point_location,sw_name,p_equip_id,equip_id,log_date,log_time,ASE_Reading,G_Reading) 
values(@Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading)
	End

	FETCH NEXT FROM Create_Col INTO @Point_location,@sw_name,@p_equip_id,@equip_id,@log_date,@log_time,@ASE_Reading,@G_Reading


CLOSE Create_Col
DEALLOCATE Create_Col

sinhasourabh · Answer 2 · 2012-06-16T22:10:00

You can use OLEDB Class to do so,

Create you query string
Set the selected values in the insert query and then execute the non query

C#

//Note I have missed some code out for simplicities sake, this all works fine however
OleDbConnection oledbConn = null;

OleDbCommand cmd = null;

OleDbConnection = new OleDbConnection(connString);           
OleDbConnection.Open();

string connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0; \"", TargetFile);

sting InsertCommand = string.Format("INSERT INTO [{0}${1}:{1}] Values({2})", WorksheetName, Coord, valuestring);

cmd = new OleDbCommand(InsertCommand, oledbConn);

cmd.ExecuteNonQuery();

//close etc

Kiran S.V · Answer 3 · 2012-06-17T01:45:00

Solution 3

Sourabh,

Its working fine thanks for your help. Need more help in future also.
I am new to this field and trying to learn the things. Please send your Email ID so that i can often take tips from you.

Regards,
Kiran

Posted 17-Jun-12 1:45am

Kiran S.V

Comments

[no name] 17-Jun-12 10:19am

Kiran you are welcomed. My email ID is sourabh.sinha.ss@gmail.com

Anyways dont forget to click 'accept this answer' this is mark this thread as answered. :)

Need to export data on combobox selection

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Need to export data on combobox selection

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us