需要在组合框选择上导出数据 [英] Need to export data on combobox selection
本文介绍了需要在组合框选择上导出数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
Good Developers，
我有五个组合框(cmbRestaurant，cmbServiceWindow，cmbEquipments，cmbSubSystems和cmbMeter)
和一个日期时间选择器.现在，在选择那些我需要将数据导出到Excel工作表的人时，有人可以指导我使用相同的方法.
代码如下.
我需要根据组合框选择将这些值插入Excel.
我该如何写存储过程呢?
我尝试过这样做，但是此存储过程的执行时间太长.
有人可以指导我吗?
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.
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
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();
}
}
推荐答案
我有五个组合框(cmbRestaurant，cmbServiceWindow，cmbEquipments，cmbSubSystems和cmbMeter)
和一个日期时间选择器.现在，在选择那些我需要将数据导出到Excel工作表的人时，有人可以指导我使用相同的方法.
代码如下.
我需要根据组合框选择将这些值插入Excel.
我该如何写存储过程呢?
我尝试过这样做，但是此存储过程的执行时间太长.
有人可以指导我吗?
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.
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
您可以使用OLEDB类这样做，
创建您的查询字符串
在插入查询中设置选定的值，然后执行非查询
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
//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}]值({2})"，WorksheetName，Coord，值字符串)； cmd = 新 OleDbCommand(InsertCommand，oledbConn); cmd.ExecuteNonQuery(); // 关闭等
{1}:{1}] Values({2})", WorksheetName, Coord, valuestring); cmd = new OleDbCommand(InsertCommand, oledbConn); cmd.ExecuteNonQuery(); //close etc
这篇关于需要在组合框选择上导出数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
