private void btnChangeVal_Click(object sender, System.EventArgs e)
{
try
{
Cursor = Cursors.WaitCursor;
InitExcel(ref _exr);
if (this._intPKCol>-1)
{
int[] intPKCols = new int[] { _intPKCol};
_exr.PKCols = intPKCols;
}
this.lblGetValueData.Text = _exr.GetValue(txtCell.Text).ToString() ;
int intNewValue;
try
{
intNewValue = Convert.ToInt32(lblGetValueData.Text);
}
catch (Exception)
{
intNewValue=0;
}
intNewValue++;
_exr.SetValue(txtCell.Text,intNewValue.ToString());
this.lblGetValueData.Text += "->" + _exr.GetValue(txtCell.Text).ToString() ;
_exr.Close();
_exr.Dispose();
_exr=null;
Cursor = Cursors.Default;
}
catch (Exception ex)
{
Cursor = Cursors.Default;
MessageBox.Show(ex.Message,"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
void LoadExcelData()
{
ExcelReader exr = new ExcelReader();
exr.KeepConnectionOpen = false;
exr.ExcelFilename = _fileName;
exr.Headers = true;
exr.SheetName = _sheetName;
exr.SetPrimaryKey(0);
DataTable dt = exr.GetTable();
foreach (DataRow row in dt.Rows)
{
DateTime curDate = Convert.ToDateTime(row[0]);
double k = Convert.ToDouble(row[1]);
double b = Convert.ToDouble(row[2]);
double d = Convert.ToDouble(row[3]);
this._adjs.Add(curDate, new Tuple<double, double, double>(k * _kospiWeight, b, d));
}
}
private void InitExcel(ref ExcelReader exr)
{
//Excel must be open
if (exr == null)
{
exr = new ExcelReader();
exr.ExcelFilename = _strExcelFilename;
exr.Headers =false;
exr.MixedData =true;
}
if (_dt==null) _dt = new DataTable("par");
exr.KeepConnectionOpen =true;
//Check excel sheetname is selected
if (this.cboSheetnames.SelectedIndex>-1)
exr.SheetName = this.cboSheetnames.Text;
else
throw new Exception("Select a sheet first!");
//Set excel sheet range
exr.SheetRange = this.txtRange.Text;
}
private void RetrieveSheetnames()
{
try
{
this.cboSheetnames.Items.Clear();
if (_exr !=null)
{
_exr.Dispose();
_exr=null;
}
_exr = new ExcelReader();
_exr.ExcelFilename = _strExcelFilename;
_exr.Headers =false;
_exr.MixedData =true;
string[] sheetnames = this._exr.GetExcelSheetNames();
this.cboSheetnames.Items.AddRange(sheetnames);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
private void btnGetData_Click(object sender, System.EventArgs e)
{
try
{
Cursor = Cursors.WaitCursor;
InitExcel(ref _exr);
_dt = _exr.GetTable();
this.lblGetValueData.Text = _exr.GetValue(txtCell.Text).ToString() ;
this.dataGrid1.DataSource=_dt;
_exr.Close();
_exr.Dispose();
_exr=null;
Cursor = Cursors.Default;
if (_dt !=null && this.txtPK.Text.Length >0) SetPK();
}
catch (Exception ex)
{
Cursor = Cursors.Default;
MessageBox.Show(ex.Message,"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
public Boolean LoadDataFromExcel()
{
ExcelReader exr = new ExcelReader();
exr.KeepConnectionOpen = false;
exr.ExcelFilename = "..\\..\\ExcelData\\BokRate.xlsx";
exr.Headers = true;
exr.SheetName = "Sheet1";
exr.SetPrimaryKey(0);
DataTable dt = exr.GetTable();
foreach (DataRow row in dt.Rows)
{
DateTime curDate = Convert.ToDateTime(row[0]);
double rate = Convert.ToDouble(row[1]);
_data.Add(new Tuple<DateTime, double>(curDate, rate));
}
return true;
}
public Boolean LoadDataFromExcel()
{
ExcelReader exr = new ExcelReader();
exr.KeepConnectionOpen = true;
exr.ExcelFilename = "..\\..\\ExcelData\\StockPerFinanceAsset.xlsx";
exr.Headers = true;
exr.SheetName = "Sheet1";
exr.SetPrimaryKey(0);
DataTable dt = exr.GetTable();
foreach (DataRow row in dt.Rows)
{
DateTime curDate = GetDateTimeFromQuarterDate(Convert.ToString(row[0]));
//double deposit = Convert.ToDouble(row[1]);
//double credit = Convert.ToDouble(row[2]);
double rate = Convert.ToDouble(row[3]);
_data.Add(new Tuple<DateTime, double>(curDate, rate));
}
return true;
}
public Boolean LoadDataFromExcel()
{
ExcelReader exr = new ExcelReader();
exr.KeepConnectionOpen = false;
exr.ExcelFilename = "..\\..\\ExcelData\\ConsumerPriceIndex.xlsx";
exr.Headers = true;
exr.SheetName = "Sheet1";
exr.SetPrimaryKey(0);
DataTable dt = exr.GetTable();
MarketData mdPivot = null;
foreach (DataRow row in dt.Rows)
{
string strDate = Convert.ToString(row[0]);
DateTime curDate = DateTime.ParseExact(strDate, "yyyy-MM", null);
if (mdPivot == null)
{
mdPivot = DataUtil.GetKeyMarketData(MarketDataSetKey.KospiFuture, curDate, DateTime.Now);
}
curDate = curDate.AddMonths(1).AddDays(-1); //월 데이터의 날짜는 월 마지막 날로 설정
curDate = DataUtil.GetLastTradingDay(mdPivot, curDate); // 월말이 주말인 경우 마지막 금요일로 변환
double value = Convert.ToDouble(row[1]);
_data.Add(new Tuple<DateTime, double>(curDate, value));
}
return true;
}
public Boolean LoadDataFromExcel()
{
ExcelReader exr = new ExcelReader();
exr.KeepConnectionOpen = false;
exr.ExcelFilename = "..\\..\\BokRate\\BokRate.xlsx";
exr.Headers = true;
exr.SheetName = "Sheet1";
exr.SetPrimaryKey(0);
DataTable dt = exr.GetTable();
foreach (DataRow row in dt.Rows)
{
String raw = Convert.ToString(row[0]);
DateTime curDate = DateTime.ParseExact(raw.Substring(0, 10), "yyyy MM dd", null);
double rate = Convert.ToDouble(raw.Substring(11));
_data.Add(new Tuple<DateTime, double>(curDate, rate));
}
return true;
}
