Home
我寫了一個可以從證交所抓到股票資訊的簡單程式 程式碼裡面有中文備註功能
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Diagnostics;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using System.IO;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;
using System.Collections;
namespace WindowsFormsApp2
{
public partial class Form1 : Form
{
CStock myDeserializedClass;
CStockWebAccess mStockWebAccess=new CStockWebAccess();
string mStockid;//定義字串(股票編號)
string mYear;//定義字串(年分)
string mMonth;//定義字串(月份)
string mDay;//定義字串(日)
public Form1()
{
InitializeComponent();
InitialListView();
}
private void InitialListView()//建立InitialListView
{
listView1.View = View.Details;
this.listView1.GridLines = true; //顯示錶格線
this.listView1.View = View.Details;//表格在窗體顯示細節的格式，如果做成表格，這個檢視必須選擇Details
this.listView1.Scrollable = true;//滾動條
this.listView1.FullRowSelect = true;//表示在控制元件上，是否可以選擇一整行
this.listView1.LabelWrap = true;
listView1.Columns.Add("股票編號");
listView1.Columns.Add("日期");
listView1.Columns.Add("成交股數");
listView1.Columns.Add("成交金額");
listView1.Columns.Add("開盤價");
listView1.Columns.Add("最高價");
listView1.Columns.Add("最低價");
listView1.Columns.Add("收盤價");
listView1.Columns.Add("漲跌價差");
listView1.Columns.Add("成交筆數");
}
async void GetRequest(string URI)//獲取來自網址的資料
{
try
{
using (HttpClient client = new HttpClient())//定義新的HttpClient(client)
{
using (HttpResponseMessage response = await client.GetAsync(URI))
{
using (HttpContent content = response.Content)
{
string mycontent = await content.ReadAsStringAsync();
myDeserializedClass = JsonConvert.DeserializeObject<CStock>(mycontent);//將資料轉成json格式
fillDataToListViewCtrl();//呼叫方法
}
}
}
}
catch (Exception ex)
{
Debug.Print(ex.Message);
}
}
void fillDataToListViewCtrl()//定義方法
{
foreach (List<string> datas in myDeserializedClass.data)//拆解json資料
{
ListViewItem item1 = new ListViewItem(mStockid,0) ;//定義新的ListViewItem(item1)
foreach (string data in datas)//拆解json資料
{
Debug.Print(data);
item1.SubItems.Add(data);//將資料填入SubItem
}
listView1.Items.AddRange(new ListViewItem[] { item1 });//將item1丟進listView1
}
}
private void button1_Click(object sender, EventArgs e)
{
mYear = numericUpDown2.Value.ToString();//取得股票編號
mMonth = numericUpDown3.Value.ToString();//取得年分
mStockid = numericUpDown1.Value.ToString();//取得月份
if (numericUpDown3.Value > 10)//將變數帶入網址
{
string URI = "https://www.twse.com.tw/exchangeReport/STOCK_DAY?response=json&date=" + mYear + mMonth + "01&stockNo=" + mStockid;
Debug.Print(URI);
GetRequest(URI);
}
else//將變數帶入網址
{
string URI = "https://www.twse.com.tw/exchangeReport/STOCK_DAY?response=json&date=" + mYear + "0" + mMonth + "01&stockNo=" + mStockid;
Debug.Print(URI);
GetRequest(URI);
}
}
private void button2_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();//定義新物件
saveFileDialog1.Filter = "csv (*.csv)|*.csv||*.*";//取得或設定目前的檔名篩選字串，以決定出現在對話方塊中 [另存檔案類型] 或 [檔案類型] 方塊的選項。
saveFileDialog1.Title = "Save STOCK";//取得或設定檔案對話方塊的標題。
saveFileDialog1.RestoreDirectory = true;//取得或設定值，指出對話方塊是否在關閉前將目錄還原至先前選取的目錄。
saveFileDialog1.ShowDialog();//以預設的擁有人來執行通用對話方塊。
string filename = saveFileDialog1.FileName;//取得來自saveFileDialog1的檔案名稱。
CMyFileAccess fileAccess = new CMyFileAccess();
bool success = fileAccess.SaveCSV(filename, myDeserializedClass.data);
}
private void button3_Click(object sender, EventArgs e)
{
OpenFileDialog OpenFileDialog1 = new OpenFileDialog();//定義新物件
OpenFileDialog1.Filter = "csv (*.csv)|*.csv||*.*";//取得或設定目前的檔名篩選字串，以決定出現在對話方塊中 [另存檔案類型] 或 [檔案類型] 方塊的選項。
OpenFileDialog1.Title = "get STOCK";//取得或設定檔案對話方塊的標題。
OpenFileDialog1.RestoreDirectory = true;//取得或設定值，指出對話方塊是否在關閉前將目錄還原至先前選取的目錄。
OpenFileDialog1.ShowDialog();//以預設的擁有人來執行通用對話方塊。
try
{
var fileStream = OpenFileDialog1.OpenFile();//開啟資料夾
var filePath = OpenFileDialog1.FileName;
CMyFileAccess fileAccess = new CMyFileAccess() ;
List<List<string>> subss = fileAccess.getCSV(filePath);
foreach (List<string> datas in subss)//拆解json資料
{
ListViewItem item1 = new ListViewItem(mStockid, 0);//定義新的ListViewItem(item1)
foreach (string data in datas)//拆解json資料
{
Debug.Print(data);
item1.SubItems.Add(data);//將資料填入SubItem
}
listView1.Items.AddRange(new ListViewItem[] { item1 });//將item1丟進listView1
}
}
catch (Exception ex)
{
Debug.Print(ex.Message);
}
}
private void Ceatetable_Click(object sender, EventArgs e)
{
string server = "127.0.0.1";
string database = "STOCK";
string user = "root";
string password = "RAY880203";
string port = "3306";
string sslM = "none";
string connString = String.Format("server={0};port={1};user id={2}; password={3}; database={4}; SslMode={5}", server, port, user, password, database, sslM);
using (MySqlConnection conn = new MySqlConnection(connString))
{
try
{
//
// Open the SqlConnection.
//
conn.Open();
//
// The following code uses an SqlCommand based on the SqlConnection.
//
using (MySqlCommand command = new MySqlCommand("USE `STOCK`;CREATE TABLE `INFOMATION`(`STOCKID`int,`DAY`date ,`Number of shares traded` int,`Turnover`int,`Opening price`decimal(4,2),`Highest price` decimal(4,2),`Lowest price`decimal(4,2),`Closing price`decimal(4,2),`Price difference`decimal(4,2),`Number of transactions`int);", conn))
command.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void Droptable_Click(object sender, EventArgs e)
{
string server = "127.0.0.1";
string database = "STOCK";
string user = "root";
string password = "RAY880203";
string port = "3306";
string sslM = "none";
string connString = String.Format("server={0};port={1};user id={2}; password={3}; database={4}; SslMode={5}", server, port, user, password, database, sslM);
using (MySqlConnection conn = new MySqlConnection(connString))
{
try
{
//
// Open the SqlConnection.
//
conn.Open();
//
// The following code uses an SqlCommand based on the SqlConnection.
//
using (MySqlCommand command = new MySqlCommand("DROP table `INFOMATION`;", conn))
command.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void Insertdata_Click(object sender, EventArgs e)
{
string server = "127.0.0.1";
string database = "STOCK";
string user = "root";
string password = "RAY880203";
string port = "3306";
string sslM = "none";
string connString = String.Format("server={0};port={1};user id={2}; password={3}; database={4}; SslMode={5}", server, port, user, password, database, sslM);
using (MySqlConnection conn = new MySqlConnection(connString))
{
try
{
conn.Open();
foreach (List<string> datas in myDeserializedClass.data)//將data轉成csv檔
{
string szLine = "";
szLine += mStockid + ",";
foreach (string data in datas)
{
string[] charsToRemove = new string[] { ",", "/", "\"", " " };
string str = data;
foreach (var c in charsToRemove)
{
str = str.Replace(c, string.Empty);
}
Debug.Print(str);
szLine += str;
szLine += ",";
}
szLine = szLine.Trim(new Char[] { ',' });
Debug.Print(szLine);
//
// The following code uses an SqlCommand based on the SqlConnection.
//
using (MySqlCommand command = new MySqlCommand("insert into `INFOMATION` values(" + szLine + ");", conn))
command.ExecuteNonQuery();
}
conn.Close();
}
catch (Exception ex)
{
Debug.Print(ex.Message);
}
try
{
//
// Open the SqlConnection.
//
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void Cleardata_Click(object sender, EventArgs e)
{
string server = "127.0.0.1";
string database = "STOCK";
string user = "root";
string password = "RAY880203";
string port = "3306";
string sslM = "none";
string connString = String.Format("server={0};port={1};user id={2}; password={3}; database={4}; SslMode={5}", server, port, user, password, database, sslM);
using (MySqlConnection conn = new MySqlConnection(connString))
{
try
{
//
// Open the SqlConnection.
//
conn.Open();
//
// The following code uses an SqlCommand based on the SqlConnection.
//
using (MySqlCommand command = new MySqlCommand("delete FROM `INFOMATION`;", conn))
command.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void clearlistview_Click(object sender, EventArgs e)
{
listView1.Items.Clear();
}
private void ReadDatabasebutton_Click(object sender, EventArgs e)
{
ClinkSQL linkSQL = new ClinkSQL();
mYear = numericUpDown2.Value.ToString();//取得股票編號
mMonth = numericUpDown3.Value.ToString();//取得年分
mStockid = numericUpDown1.Value.ToString();//取得月份
List<List<string>> IDsubss = linkSQL.ID(mStockid);
foreach (List<string> datas in IDsubss)//拆解json資料
{
ListViewItem item1 = new ListViewItem(mStockid, 0);//定義新的ListViewItem(item1)
foreach (string data in datas)//拆解json資料
{
Debug.Print(data);
item1.SubItems.Add(data);//將資料填入SubItem
}
listView1.Items.AddRange(new ListViewItem[] { item1 });//將item1丟進listView1
}
}
private void getwithday_Click(object sender, EventArgs e)
{
ClinkSQL linkSQL = new ClinkSQL();
mYear = numericUpDown2.Value.ToString();//取得年分
mMonth = numericUpDown3.Value.ToString();//取得月分
mDay = numericUpDown4.Value.ToString();//取得日期
mStockid = numericUpDown1.Value.ToString();//取得月份
List<List<string>> DAYsubss = linkSQL.DAY(mYear, mMonth,mDay);
foreach (List<string> datas in DAYsubss)//拆解json資料
{
ListViewItem item1 = new ListViewItem(mStockid, 0);//定義新的ListViewItem(item1)
foreach (string data in datas)//拆解json資料
{
Debug.Print(data);
item1.SubItems.Add(data);//將資料填入SubItem
}
listView1.Items.AddRange(new ListViewItem[] { item1 });//將item1丟進listView1
}
}
private void getwithdayid_Click(object sender, EventArgs e)
{
ClinkSQL linkSQL = new ClinkSQL();
mYear = numericUpDown2.Value.ToString();//取得年分
mMonth = numericUpDown3.Value.ToString();//取得月份
mDay = numericUpDown4.Value.ToString();//取得日期
mStockid = numericUpDown1.Value.ToString();//取得股票編號
List<List<string>> IDDAYsubss = linkSQL.DAYID(mYear, mMonth,mDay, mStockid);
foreach (List<string> datas in IDDAYsubss)//拆解json資料
{
ListViewItem item1 = new ListViewItem(mStockid, 0);//定義新的ListViewItem(item1)
foreach (string data in datas)//拆解json資料
{
Debug.Print(data);
item1.SubItems.Add(data);//將資料填入SubItem
}
listView1.Items.AddRange(new ListViewItem[] { item1 });//將item1丟進listView1
}
}
private void relist_Click(object sender, EventArgs e)
{
Random random = new Random();
int judge = 0;
judge=random.Next(0,2);
List<int> list = new List<int>();
int k = 0;
do
{
k = random.Next (1,11);
if (!list.Contains(k))
list.Add(k);
}
while (list.Count < 10);
CMySort mySort = new CMySort();
List<int> returnsort = mySort.Sort(list,judge);
Debug.WriteLine(returnsort);
}
}
}
namespace WindowsFormsApp2
{
internal class CStockWebAccess
{
CStock myDeserializedClass;
public CStock getstock(string mYear, string Month, string mStockid)
{
return getstockAsync(mYear, Month, mStockid).GetAwaiter().GetResult();
}
private async System.Threading.Tasks.Task<CStock> getstockAsync(string mYear, string Month, string mStockid)
{
CStock myDeserializedClass = new CStock();
int mMonth = Int32.Parse(Month); ;
if (mMonth > 10)//將變數帶入網址
{
string URI = "https://www.twse.com.tw/exchangeReport/STOCK_DAY?response=json&date=" + mYear + Month + "01&stockNo=" + mStockid;
Debug.Print(URI);
return await GetRequestAsync(URI);
}
else//將變數帶入網址
{
string URI = "https://www.twse.com.tw/exchangeReport/STOCK_DAY?response=json&date=" + mYear + "0" + Month + "01&stockNo=" + mStockid;
Debug.Print(URI);
return await GetRequestAsync(URI);
}
}
private async System.Threading.Tasks.Task<CStock> GetRequestAsync(string URI)//獲取來自網址的資料
{
CStock data = new CStock();
try
{
using (HttpClient client = new HttpClient())//定義新的HttpClient(client)
{
using (HttpResponseMessage response = await client.GetAsync(URI))
{
using (HttpContent content = response.Content)
{
string mycontent = await content.ReadAsStringAsync();
data = JsonConvert.DeserializeObject<CStock>(mycontent);//將資料轉成json格式
}
}
}
}
catch (Exception ex)
{
Debug.Print(ex.Message);
}
return data;
}
}
}
class CStock
{
public string stat { get; set; }
public string date { get; set; }
public string title { get; set; }
public List<string> fields { get; set; }
public List<List<string>> data { get; set; }
public List<string> datas { get; set; }
public List<string> notes { get; set; }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;
using System.Diagnostics;
namespace WindowsFormsApp2
{
class ClinkSQL
{
public string server = "127.0.0.1";
public string database = "STOCK";
public string user = "root";
public string password = "RAY880203";
public string port = "3306";
public string sslM = "none";
public List<List<string>> ID (string stockid)
{
string connString = String.Format("server={0};port={1};user id={2}; password={3}; database={4}; SslMode={5}", server, port, user, password, database, sslM);
using (MySqlConnection conn = new MySqlConnection(connString))
{
conn.Open();
MySqlCommand command = new MySqlCommand("select`DAY` ,`Number of shares traded` ,`Turnover`,`Opening price`,`Highest price` ,`Lowest price`,`Closing price`,`Price difference`,`Number of transactions` from `INFOMATION` where STOCKID ="+stockid+";", conn);
MySqlDataReader reader = command.ExecuteReader();
List<List<string>> datass = new List<List<string>>();
while (reader.Read())
{
ReadSingleRow((IDataRecord)reader);
}
void ReadSingleRow(IDataRecord record)
{
string str = string.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}", record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[8]);
string[] subs = str.Split(',');
List<string> datas = new List<string>();
foreach (string data in subs)//拆解json資料
{
Debug.Print(data);
datas.Add(data);
}
datass.Add(datas);
}
conn.Close();
return datass;
}
}
public List<List<string>> DAY(string syear,string smonth,string sday)
{
int iyear = Int32.Parse(syear);
int imonth = Int32.Parse(smonth);
int year =( iyear - 1911)*10;
int month = imonth * 10;
string day = "";
day += "0" +year + month+sday;
string connString = String.Format("server={0};port={1};user id={2}; password={3}; database={4}; SslMode={5}", server, port, user, password, database, sslM);
using (MySqlConnection conn = new MySqlConnection(connString))
{
conn.Open();
MySqlCommand command = new MySqlCommand("select`DAY` ,`Number of shares traded` ,`Turnover`,`Opening price`,`Highest price` ,`Lowest price`,`Closing price`,`Price difference`,`Number of transactions` from `INFOMATION` where `DAY` ="+ day+"; ", conn);
MySqlDataReader reader = command.ExecuteReader();
List<List<string>> datass = new List<List<string>>();
while (reader.Read())
{
ReadSingleRow((IDataRecord)reader);
}
void ReadSingleRow(IDataRecord record)
{
string str = string.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}", record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[8]);
string[] subs = str.Split(',');
List<string> datas = new List<string>();
foreach (string data in subs)//拆解json資料
{
Debug.Print(data);
datas.Add(data);
}
datass.Add(datas);
}
conn.Close();
return datass;
}
}
public List<List<string>> DAYID(string syear, string smonth,string sday ,string stockid)
{
int iyear = Int32.Parse(syear);
int imonth = Int32.Parse(smonth);
int year = (iyear - 1911) * 10;
int month = imonth * 10;
string day = "";
day +="0"+ year + month + sday;
string connString = String.Format("server={0};port={1};user id={2}; password={3}; database={4}; SslMode={5}", server, port, user, password, database, sslM);
using (MySqlConnection conn = new MySqlConnection(connString))
{
conn.Open();
MySqlCommand command = new MySqlCommand("select`DAY` ,`Number of shares traded` ,`Turnover`,`Opening price`,`Highest price` ,`Lowest price`,`Closing price`,`Price difference`,`Number of transactions` from `INFOMATION` where `DAY` = "+day+" and `STOCKID`="+stockid+";", conn);
MySqlDataReader reader = command.ExecuteReader();
List<List<string>> datass = new List<List<string>>();
while (reader.Read())
{
ReadSingleRow((IDataRecord)reader);
}
void ReadSingleRow(IDataRecord record)
{
string str = string.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}", record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[8]);
string[] subs = str.Split(',');
List<string> datas = new List<string>();
foreach (string data in subs)//拆解json資料
{
Debug.Print(data);
datas.Add(data);
}
datass.Add(datas);
}
conn.Close();
return datass;
}
}
}
}
public class CMyFileAccess
{
public bool SaveCSV(string filename, List<List<string>> datass)
{
var stockCSV = new StreamWriter(filename);
try
{
foreach (List<string> datas in datass)//將data轉成csv檔
{
string szLine = "";
foreach (string data in datas)
{
string[] charsToRemove = new string[] { "," };
string str = data;
foreach (var c in charsToRemove)
{
str = str.Replace(c, string.Empty);
}
Debug.Print(str);
szLine += str;
szLine += ",";
}
stockCSV.WriteLine(szLine);
}
stockCSV.Close();//關閉資料夾
}
catch (Exception ex)
{
Debug.Print(ex.Message);
}
return true;
}
public List<List<string>> getCSV(string filename)
{
List<List<string>> datass = new List<List<string>>();
StreamReader reader = new StreamReader(filename);//讀取資料
while (true)
{
string line = reader.ReadLine();
if (line == null) break;
string[] subs = line.Split(',');
if (subs == null) break;
List<string> datas = new List<string>();
foreach (string data in subs)//拆解json資料
{
Debug.Print(data);
datas.Add(data);
}
datass.Add(datas);
}
return datass;
}
}
可以用日期查詢股票，也可以用股票編號查詢，並用FORM呈現出來。並連接資料庫，用按鈕可以把資料匯入資料庫，或是存入本機資料夾
這是用股票編號查詢的結果
這是清除後的結果