📄 我的自选股.cs
字号:
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data;
using System.Text;
using Excel;
namespace stock_Log
{
/// <summary>
/// 我的自选股 的摘要说明。
/// </summary>
public class 我的自选股 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.TabControl tabControl1;
private System.Windows.Forms.TabPage tabPage1;
private System.Windows.Forms.TabPage tabPage2;
private System.Windows.Forms.TabPage tabPage3;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.OpenFileDialog openFileDialog1;
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.Button button2;
private System.Data.DataSet dp_DS;
private System.Windows.Forms.Label strStatus;
/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.Container components = null;
public 我的自选股()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent();
this.dataGrid1.DataSource = ExceSql.GetBySqlToDS("select * from zxg order by 代码 ");
this.dataGrid1.DataMember = "Main";
//
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
//
}
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows 窗体设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.label1 = new System.Windows.Forms.Label();
this.tabControl1 = new System.Windows.Forms.TabControl();
this.tabPage1 = new System.Windows.Forms.TabPage();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.tabPage2 = new System.Windows.Forms.TabPage();
this.tabPage3 = new System.Windows.Forms.TabPage();
this.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();
this.textBox1 = new System.Windows.Forms.TextBox();
this.button2 = new System.Windows.Forms.Button();
this.strStatus = new System.Windows.Forms.Label();
this.tabControl1.SuspendLayout();
this.tabPage1.SuspendLayout();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(605, 6);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(75, 23);
this.button1.TabIndex = 0;
this.button1.Text = "选择数据";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// label1
//
this.label1.Location = new System.Drawing.Point(2, 13);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(40, 16);
this.label1.TabIndex = 2;
this.label1.Text = "路径:";
//
// tabControl1
//
this.tabControl1.Controls.Add(this.tabPage1);
this.tabControl1.Controls.Add(this.tabPage2);
this.tabControl1.Controls.Add(this.tabPage3);
this.tabControl1.Location = new System.Drawing.Point(0, 66);
this.tabControl1.Name = "tabControl1";
this.tabControl1.SelectedIndex = 0;
this.tabControl1.Size = new System.Drawing.Size(1024, 654);
this.tabControl1.TabIndex = 3;
//
// tabPage1
//
this.tabPage1.Controls.Add(this.dataGrid1);
this.tabPage1.Location = new System.Drawing.Point(4, 21);
this.tabPage1.Name = "tabPage1";
this.tabPage1.Size = new System.Drawing.Size(1016, 629);
this.tabPage1.TabIndex = 0;
this.tabPage1.Text = "我的自选股";
this.tabPage1.UseVisualStyleBackColor = true;
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(8, 8);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(1000, 522);
this.dataGrid1.TabIndex = 0;
//
// tabPage2
//
this.tabPage2.Location = new System.Drawing.Point(4, 21);
this.tabPage2.Name = "tabPage2";
this.tabPage2.Size = new System.Drawing.Size(1016, 629);
this.tabPage2.TabIndex = 1;
this.tabPage2.Text = "交易历史";
this.tabPage2.UseVisualStyleBackColor = true;
//
// tabPage3
//
this.tabPage3.Location = new System.Drawing.Point(4, 21);
this.tabPage3.Name = "tabPage3";
this.tabPage3.Size = new System.Drawing.Size(1016, 629);
this.tabPage3.TabIndex = 2;
this.tabPage3.Text = "其他数据";
this.tabPage3.UseVisualStyleBackColor = true;
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(36, 8);
this.textBox1.Name = "textBox1";
this.textBox1.Size = new System.Drawing.Size(536, 21);
this.textBox1.TabIndex = 4;
this.textBox1.Text = "E:\\Program Files\\海通大智慧2005专业版\\海通证券大智慧2005专业版\\export\\hq.xls";
//
// button2
//
this.button2.Location = new System.Drawing.Point(702, 6);
this.button2.Name = "button2";
this.button2.Size = new System.Drawing.Size(75, 23);
this.button2.TabIndex = 5;
this.button2.Text = "导入数据库";
this.button2.Click += new System.EventHandler(this.button2_Click);
//
// strStatus
//
this.strStatus.Location = new System.Drawing.Point(240, 40);
this.strStatus.Name = "strStatus";
this.strStatus.Size = new System.Drawing.Size(288, 23);
this.strStatus.TabIndex = 6;
//
// 我的自选股
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(1016, 739);
this.Controls.Add(this.strStatus);
this.Controls.Add(this.button2);
this.Controls.Add(this.textBox1);
this.Controls.Add(this.tabControl1);
this.Controls.Add(this.label1);
this.Controls.Add(this.button1);
this.Name = "我的自选股";
this.Text = "我的自选股";
this.WindowState = System.Windows.Forms.FormWindowState.Maximized;
this.tabControl1.ResumeLayout(false);
this.tabPage1.ResumeLayout(false);
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private void button1_Click(object sender, System.EventArgs e)
{
DialogResult dr = openFileDialog1.ShowDialog();
if(dr == DialogResult.Cancel) return;
string path = openFileDialog1.FileName;
this.textBox1.Text = path;
//try
//{
// // string mystring="Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = 'F:/Documents and Settings/fzlhx/My Documents/Visual Studio Projects/stock Log/hq.xls';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
// string mystring = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
// OleDbConnection cnnxls = new OleDbConnection(mystring);
// OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [hq$]", cnnxls);
// DataSet myDs = new DataSet();
// myDa.Fill(myDs, "hq");
// cnnxls.Close();
//}
//catch (Exception eww)
//{
// string ee = eww.ToString();
//}
//dp_DS = ExceSql.import(path);
//this.dataGrid1.DataSource = dp_DS;//ExceSql.ExcelToDataTable(path,"hq");
//this.dataGrid1.DataMember = "hq";
//create(path);
}
private void create(string path1)
{
try
{
string sql="";
// string sql="drop table zxg ";
// ExceSql.insertUpdate(sql);
// sql = "SELECT * into zxg FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="+path1+";User ID=Admin;Password=;Extended properties=Excel 8.0')...[hq$] ";
string tableName="zrrxg";
// sql = "SELECT * into " +tableName+" FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="+path1+";User ID=Admin;Password=;Extended properties=Excel 8.0')...[hq$] ";
sql = "SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source='"+path1+"';User ID=Admin;Password=;Extended properties=Excel 8.0')...[hq$] ";
ExceSql.insertUpdate(sql);
//ExceSql.insertUpdate(sql);
strStatus.Text ="上传成功,请点击'导入数据'来导入数据到数据库!";
}
catch(Exception e)
{
strStatus.Text ="出错了:"+ e.Message;
throw e;
}
}
private void button2_Click(object sender, System.EventArgs e)
{
//产生一个Excel.Application的新进程
Excel.Application app = new Excel.Application();
if (app == null)
{
strStatus.Text = "ERROR: EXCEL couldn't be started!";
return;
}
app.Visible = true; //如果只想用程序控制该excel而不想让用户操作时候，可以设置为false
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
//_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); //根据模板产生新的workbook
_Workbook workbook = workbooks.Add(this.textBox1.Text); //或者根据绝对路径打开工作簿文件a.xls
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
strStatus.Text = "ERROR: worksheet == null";
return;
}
//xbook.xlSheet(1).UsedRange.Cells.Rows.Count
//列的话同上，用xbook.xlSheet(1).UsedRange.Cells.Columns.Count
int jj = worksheet.UsedRange.Cells.Rows.Count;//行 29
int jj2 = worksheet.UsedRange.Cells.Columns.Count - 1;//列 5
string sdd = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,AZ";
string[] dd = sdd.Split(',');
StringBuilder commandText = new StringBuilder();
ArrayList li = new ArrayList();
string dd2 = dd[jj2];
for (int k = 2; k <= jj; k++)
{
Range range1 = worksheet.get_Range(string.Format("A{0}", k), string.Format("{0}{1}", dd2, k));
System.Array values = (System.Array)range1.Formula;
if (values != null)
{
int len1 = values.GetLength(0);
int len2 = values.GetLength(1);
for (int i = 1; i <= len1; i++)
{
commandText = new StringBuilder();
commandText.Append("INSERT INTO zxg values(");
string textB_v = "0";
for (int j = 1; j <= len2; j++)
{
if (values.GetValue(i, j).ToString().Length == 0)
{ textB_v = "0"; }
else
{
textB_v = values.GetValue(i, j).ToString();
}
commandText.Append("'" + textB_v + "',");
}
commandText.Append(")");
li.Add(commandText.ToString().Replace(",)", ")"));
//ExceSql.insertUpdate(commandText.ToString());
}
}
}
//app.Quit();
app = null;
ExceSql.insertUpdate("delete from zxg");
bool kkk = ExceSql.execTX(li);
if (kkk)
{
MessageBox.Show("自选股同步成功！", "确认", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
this.dataGrid1.DataSource = ExceSql.GetBySqlToDS("select * from zxg order by 代码 ");
this.dataGrid1.DataMember = "Main";
}
private void DataTableToDB()
{
//DataTable dtExcel =ExceSql.import(this.textBox1.Text).Tables[0];
//StringBuilder commandText = new StringBuilder();
//string var= "0";
//for (int i = 0; i < dtExcel.Rows.Count; i++)
//{
// commandText = new StringBuilder();
// commandText.Append("INSERT INTO zxg values(");
// for (int j = 0; j < dtExcel.Columns.Count; j++)
// {
// var =ExceSql.isStringTrim( dtExcel.Rows[i][j].ToString(),"0");
// commandText.Append("'" + var + "',");
// }
// commandText.Append(")");
// ExceSql.insertUpdate(commandText.ToString().Replace(",)",")"));
//}
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -