public AdjRateDatum CalculateAdjRateDatum(DateTime targetDate, MarketDataSet data)
{
RowInfo curRow = GetCurRowWithBasicInfo(targetDate, data);
//MovingAverage 계산
SetSpotDataMinMaxRange(curRow);
//포지션 정보를 설정한다.
SetPositionInfo(targetDate, data, curRow);
// 전일대비 변동율을 구한다.
SetFutureIncrement(curRow, _prevRow);
this._cumData.Add(curRow);
// 코스피 선물 평균 변동성을 구한다.
curRow.KospiVol = this._cumData.GetKospiAvgVol();
// 비중을 구한다.
AdjRateDatum adj = GetAdj(curRow);
curRow.AssetAdj = adj;
this._prevRow = curRow;
return adj;
}
protected int GetRowHandleToSelect(RowInfo rowInfo) {
int dataRowHandle = view.LocateByValue(0, view.Columns[keyFieldName], rowInfo.Id);
if(dataRowHandle != GridControl.InvalidRowHandle)
if (view.GetRowLevel(dataRowHandle) != rowInfo.level)
return FindParentRowHandle(rowInfo, dataRowHandle);
return dataRowHandle;
}
protected void ExpandRowByRowInfo(RowInfo rowInfo){
int dataRowHandle = view.LocateByValue(0, view.Columns[keyFieldName], rowInfo.Id);
if(dataRowHandle != GridControl.InvalidRowHandle){
int parentRowHandle = FindParentRowHandle(rowInfo, dataRowHandle);
view.SetRowExpanded(parentRowHandle, true, false);
}
}
public void Clear()
{
_logs.Clear();
_cumData.Clear();
_prevRow = null;
_nextAdjustment.Clear();
}
protected override void CalcStateImage(RowInfo ri)
{
base.CalcStateImage(ri);
if (Size.Empty != RC.StateImageSize && !((EmptyImageTreeList)TreeList).CanCheckNode(ri.Node))
ri.StateImageLocation.X -= RC.CheckBoxSize.Width;
if (Size.Empty != RC.StateImageSize && -1 == ri.Node.StateImageIndex)
ri.StateImageLocation.X -= RC.StateImageSize.Width;
}
public Layer(int _width, int _height, int index)
{
this.Index = index;
RowList = new RowInfo[_height];
for (int i = 0; i < RowList.Length; i++)
{
RowList[i] = new RowInfo(_width);
}
}
AdjRateDatum GetAdj(RowInfo row)
{
if (row.IsFirstRow)
{
return new AdjRateDatum(1, 1, 1);
}
if (row.KospiVol < 0)
{
return new AdjRateDatum(1, 1, 1);
}
double dollarPortion = GetDollarPortion(row);
double kospiPortion = GetKospiPortion(row, dollarPortion);
double ktbPortion = 1.0 - dollarPortion - kospiPortion;
return new AdjRateDatum(kospiPortion, ktbPortion, dollarPortion);
}
public AssetAdjustment CalculateAssetWeight_Raw(OperationSet opSet, DateTime targetDate, MarketDataSet data)
{
RowInfo curRow = GetCurRowWithBasicInfo(targetDate, data);
//포지션 정보를 설정한다.
SetPositionInfo(targetDate, data, curRow);
// 전일대비 변동율을 구한다.
SetFutureIncrement(curRow, _prevRow);
this._cumData.Add(curRow);
// 코스피 선물 평균 변동성을 구한다.
curRow.KospiVol = this._cumData.GetKospiAvgVol();
// 비중을 구한다.
AssetAdjustment adj = GetAdj(curRow);
curRow.AssetAdj = adj;
this._prevRow = curRow;
return adj;
}
public static MockFolderMediaLocation[] CreateMockLocations(string config)
{
var builder = new Builder();
var depth = 0;
foreach (var line in config.Split(new string[] { Environment.NewLine },
StringSplitOptions.RemoveEmptyEntries))
{
var rowInfo = new RowInfo(line);
if (rowInfo.Depth == depth) {
builder.AddSibling(rowInfo);
} else if (rowInfo.Depth > depth) {
builder.AddChild(rowInfo);
} else {
builder.AddParent(rowInfo, depth - rowInfo.Depth);
}
depth = rowInfo.Depth;
}
return builder.RootFolders;
}
public static MockFolderMediaLocation[] CreateMockLocations(string config)
{
var builder = new Builder();
var depth = 0;
foreach (var line in config.Split('\n')) {
if (line.Trim().Length == 0) continue;
var rowInfo = new RowInfo(line.TrimEnd());
if (rowInfo.Depth == depth) {
builder.AddSibling(rowInfo);
} else if (rowInfo.Depth > depth) {
builder.AddChild(rowInfo);
} else {
builder.AddParent(rowInfo, depth - rowInfo.Depth);
}
depth = rowInfo.Depth;
}
return builder.RootFolders;
}
RowInfo GetCurRowWithBasicInfo(DateTime curDate, MarketDataSet data)
{
MarketData mdKospiFuture = data.GetData(MarketDataSetKey.KospiFuture);
MarketData mdKtbFuture = data.GetData(MarketDataSetKey.KtbFuture);
MarketData mdDollarFuture = data.GetData(MarketDataSetKey.DollarFuture);
MarketData mdKospiSpot = data.GetData(MarketDataSetKey.KospiSpot);
MarketData mdKtbSpot = data.GetData(MarketDataSetKey.KtbSpot);
MarketData mdDollarSpot = data.GetData(MarketDataSetKey.DollarSpot);
DOHLC kospiFuture = mdKospiFuture.GetData(curDate);
DOHLC ktbFuture = mdKtbFuture.GetData(curDate);
DOHLC dollarFuture = mdDollarFuture.GetData(curDate);
DOHLC kospiSpot = mdKospiSpot.GetData(curDate);
DOHLC ktbSpot = mdKtbSpot.GetData(curDate);
DOHLC dollarSpot = mdDollarSpot.GetData(curDate);
RowInfo row = new RowInfo();
row.CurDate = curDate;
row.KospiFuturePrice = kospiFuture.OHLC.Close;
row.KtbFuturePrice = ktbFuture.OHLC.Close;
row.DollarFuturePrice = dollarFuture.OHLC.Close;
row.KospiSpotPrice = kospiSpot.OHLC.Close;
row.KtbSpotPrice = ktbSpot.OHLC.Close;
row.DollarSpotPrice = dollarSpot.OHLC.Close;
return row;
}
private void LoadRowByRowInfo(RowInfo rowInfo)
{
if (rowInfo.IsSelected)
{
SelectRow(GetRowHandleToSelect(rowInfo));
}
}
private void SaveExpansionViewInfo(IList list)
{
if (GroupedColumns.Count == 0)
{
return;
}
list.Clear();
var column = Columns[idFieldName];
for (var i = -1; i > Int32.MinValue; i--)
{
if (!IsValidRowHandle(i))
{
break;
}
if (!GetRowExpanded(i))
{
continue;
}
var rowInfo = new RowInfo();
var dataRowHandle = GetDataRowHandleByGroupRowHandle(i);
rowInfo.Value = GetRowCellValue(dataRowHandle, column);
rowInfo.Level = GetRowLevel(i);
list.Add(rowInfo);
}
}
private int FindParentRowHandle(RowInfo rowInfo, int rowHandle)
{
var result = GetParentRowHandle(rowHandle);
while (GetRowLevel(result) != rowInfo.Level)
{
result = GetParentRowHandle(result);
}
return result;
}
private int GetRowHandleToSelect(RowInfo rowInfo)
{
var dataRowHandle = LocateByValue(idFieldName, rowInfo.Value);
if (dataRowHandle == DevExpress.XtraGrid.GridControl.InvalidRowHandle)
{
return dataRowHandle;
}
return GetRowLevel(dataRowHandle) != rowInfo.Level
? FindParentRowHandle(rowInfo, dataRowHandle)
: dataRowHandle;
}
void SetSpotDataMinMaxRange(RowInfo curRow)
{
_maKospiSpot.Add(curRow.KospiSpotPrice);
_maKtbSpot.Add(curRow.KtbSpotPrice);
_maDollarSpot.Add(curRow.DollarSpotPrice);
_kospiSpotMaxRange = _maKospiSpot.GetCurMA() * 2.0;
_kospiSpotMinRange = _maKospiSpot.GetCurMA() * 0.5;
//_ktbSpotMaxRange = _maKtbSpot.GetCurMA() * 2.0;
//_ktbSpotMinRange = _maKtbSpot.GetCurMA() * 0.5;
//_dollarSpotMaxRange = _maDollarSpot.GetCurMA() * 2;
//_dollarSpotMinRange = _maDollarSpot.GetCurMA() * 0.5;
}
void SetPositionInfo(DateTime curDate, MarketDataSet data, RowInfo row)
{
MarketData mdKospiSpot = data.GetData(MarketDataSetKey.KospiSpot);
MarketData mdKtbSpot = data.GetData(MarketDataSetKey.KtbSpot);
MarketData mdDollarSpot = data.GetData(MarketDataSetKey.DollarSpot);
DOHLC kospiSpot = mdKospiSpot.GetData(curDate);
DOHLC ktbSpot = mdKtbSpot.GetData(curDate);
DOHLC dollarSpot = mdDollarSpot.GetData(curDate);
double kospiSpotPos = (kospiSpot.OHLC.Close - MagicNumber.KOSPI_SPOT_MIN_RANGE) /
(MagicNumber.KOSPI_SPOT_MAX_RANGE - MagicNumber.KOSPI_SPOT_MIN_RANGE);
double ktbSpotPos = (MagicNumber.KTB_SPOT_MAX_RANGE - ktbSpot.OHLC.Close) /
(MagicNumber.KTB_SPOT_MAX_RANGE - MagicNumber.KTB_SPOT_MIN_RANGE);
double dollarSpotPos = (dollarSpot.OHLC.Close - MagicNumber.DOLLAR_SPOT_MIN_RANGE) /
(MagicNumber.DOLLAR_SPOT_MAX_RANGE - MagicNumber.DOLLAR_SPOT_MIN_RANGE);
row.KospiSpotPosition = kospiSpotPos;
row.KtbSpotPosition = ktbSpotPos;
row.DollarSpotPosition = dollarSpotPos;
}
double GetDollarPortion(RowInfo row)
{
double reverseDollarPosition = 1.0 - row.DollarSpotPosition;
if (bRateRange)
{
//reverseDollarPosition = 1.0 / row.DollarSpotPosition;
//if (reverseDollarPosition < 0)
//{
// reverseDollarPosition = 0;
//}
}
double basePortion = MagicNumber.DOLLAR_MAX_WEIGHT * reverseDollarPosition;
basePortion = Math.Max(basePortion, 0);
Trace.Assert(basePortion <= 1.0);
return basePortion;
}
void SetFutureIncrement(RowInfo curRow, RowInfo prevRow)
{
if (prevRow == null)
{
return;
}
curRow.IsFirstRow = false;
double kospiFutureIncrement = curRow.KospiFuturePrice / prevRow.KospiFuturePrice;
double ktbFutureIncrement = curRow.KtbFuturePrice / prevRow.KtbFuturePrice;
double dollarFutureIncrement = curRow.DollarFuturePrice / prevRow.DollarFuturePrice;
curRow.KospiFutureIncrement = kospiFutureIncrement;
curRow.KtbFutureIncrement = ktbFutureIncrement;
curRow.DollarFutureIncrement = dollarFutureIncrement;
}
public static MethodInfo GetCRUDMethodInfo(DbSetInfo dbInfo, RowInfo rowInfo)
{
MethodInfo methInfo = null;
switch (rowInfo.changeType)
{
case ChangeType.Added:
methInfo = dbInfo.getOperationMethodInfo(MethodType.Insert);
break;
case ChangeType.Deleted:
methInfo = dbInfo.getOperationMethodInfo(MethodType.Delete);
break;
case ChangeType.Updated:
methInfo = dbInfo.getOperationMethodInfo(MethodType.Update);
break;
default:
throw new DomainServiceException(string.Format(ErrorStrings.ERR_REC_CHANGETYPE_INVALID, dbInfo.EntityType.Name, rowInfo.changeType));
}
return methInfo;
}
public void AddSibling(RowInfo info)
{
MockMediaLocation newLocation;
if (info.IsFolder) {
newLocation = new MockFolderMediaLocation();
} else {
newLocation = new MockMediaLocation();
}
if (location.Path.Length > 0) {
newLocation.Path = location.Path + "\\" + info.Path;
} else {
newLocation.Path = info.Path;
}
newLocation.Parent = this.location;
location.Children.Add(newLocation);
}
public void AddParent(RowInfo info,int depth)
{
while (depth > 0) {
depth--;
location = (MockFolderMediaLocation)location.Parent;
}
AddSibling(info);
}
public void AddChild(RowInfo info)
{
location = (MockFolderMediaLocation)location.Children.Last();
AddSibling(info);
}
protected void btnExport_Click(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument();//新建一个xls文档
xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
string MainID = Request.QueryString["ID"].ToString();
cs.DBCommand dbc = new cs.DBCommand();
string sqlmain = "select * from ETravel where ID=" + MainID;
DataTable dtMain = dbc.GetData("eReimbursement", sqlmain);
if (dtMain.Rows.Count != 1)
{
ErrorHandle("Data Error.");
return;
}
string sqlTocity = "select (ROW_NUMBER() OVER (ORDER BY Tocity)-1) % 4 AS SubRow,(ROW_NUMBER() OVER (ORDER BY Tocity) - 1) / 4 AS Row,Tocity from (select distinct Tocity from ETraveleDetail where [No]='" + MainID + "') t1";
DataTable dtTocity = dbc.GetData("eReimbursement", sqlTocity);
int pagecount = 0;
for (int i = 0; i < dtTocity.Rows.Count; i++)
{
if (Convert.ToInt32(dtTocity.Rows[i]["Row"].ToString())>pagecount)
{
pagecount = Convert.ToInt32(dtTocity.Rows[i]["Row"].ToString());
}
}
for (int j = 0; j < pagecount + 1; j++)
{
Worksheet sheet;
sheet = xls.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyyMMddHHmmss" + j.ToString()));
//首行空白行
XF titleXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF.UseBorder = false; // 使用边框
titleXF.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
//第二行
XF titleXF1 = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF1.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
titleXF1.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF1.UseBorder = false; // 使用边框
titleXF1.Font.Underline = UnderlineTypes.Single;
titleXF1.Font.Height = 18 * 20;
XF columnTitleXF41 = xls.NewXF();
XF columnTitleXF42 = xls.NewXF();
columnTitleXF42.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF42.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF42.Font.Height = 12 * 20;
XF columnTitleXF43 = xls.NewXF();
columnTitleXF43.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF43.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF43.Font.Height = 12 * 20;
columnTitleXF43.UseBorder = true;
columnTitleXF43.LeftLineStyle = 2;
columnTitleXF43.TopLineStyle = 2;
columnTitleXF43.RightLineStyle = 2;
columnTitleXF43.BottomLineStyle = 2;
XF columnTitleXF44 = xls.NewXF();
columnTitleXF44.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF44.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF44.Font.Height = 12 * 20;
columnTitleXF44.UseBorder = true;
columnTitleXF44.TopLineStyle = 2;
columnTitleXF44.BottomLineStyle = 2;
XF columnTitleXF46 = xls.NewXF();
columnTitleXF46.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF46.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF46.Font.Height = 12 * 20;
columnTitleXF46.UseBorder = true;
columnTitleXF46.TopLineStyle = 2;
columnTitleXF46.BottomLineStyle = 2;
columnTitleXF46.RightLineStyle = 2;
XF columnTitleXF412 = xls.NewXF();
columnTitleXF412.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF412.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF412.Font.Height = 12 * 20;
columnTitleXF412.UseBorder = true;
columnTitleXF412.TopLineStyle = 2;
columnTitleXF412.BottomLineStyle = 2;
columnTitleXF412.RightLineStyle = 2;
XF columnTitleXF62 = xls.NewXF();
columnTitleXF62.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF62.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF62.Font.Height = 10 * 20;
columnTitleXF62.Font.Bold = true;
columnTitleXF62.UseBorder = true;
columnTitleXF62.LeftLineStyle = 2;
columnTitleXF62.TopLineStyle = 2;
columnTitleXF62.RightLineStyle = 2;
columnTitleXF62.BottomLineStyle = 2;
XF columnTitleXF63 = xls.NewXF();
columnTitleXF63.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF63.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF63.Font.Height = 10 * 20;
columnTitleXF63.UseBorder = true;
columnTitleXF63.TopLineStyle = 2;
columnTitleXF63.LeftLineStyle = 1;
XF columnTitleXF64 = xls.NewXF();
columnTitleXF64.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF64.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF64.Font.Height = 10 * 20;
columnTitleXF64.UseBorder = true;
columnTitleXF64.TopLineStyle = 2;
columnTitleXF64.RightLineStyle = 1;
XF columnTitleXF66 = xls.NewXF();
columnTitleXF66.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF66.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF66.Font.Height = 10 * 20;
columnTitleXF66.UseBorder = true;
columnTitleXF66.TopLineStyle = 2;
XF columnTitleXF67 = xls.NewXF();
columnTitleXF67.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF67.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF67.Font.Height = 10 * 20;
columnTitleXF67.Font.Bold = true;
columnTitleXF67.UseBorder = true;
columnTitleXF67.LeftLineStyle = 2;
columnTitleXF67.TopLineStyle = 2;
columnTitleXF67.BottomLineStyle = 1;
XF columnTitleXF68 = xls.NewXF();
columnTitleXF68.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF68.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF68.Font.Height = 10 * 20;
columnTitleXF68.UseBorder = true;
columnTitleXF68.TopLineStyle = 2;
columnTitleXF68.RightLineStyle = 2;
XF columnTitleXF72 = xls.NewXF();
columnTitleXF72.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF72.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF72.Font.Height = 10 * 20;
columnTitleXF72.Font.Bold = true;
columnTitleXF72.UseBorder = true;
columnTitleXF72.LeftLineStyle = 2;
columnTitleXF72.RightLineStyle = 2;
columnTitleXF72.BottomLineStyle = 2;
XF columnTitleXF73 = xls.NewXF();
columnTitleXF73.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF73.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF73.Font.Height = 10 * 20;
columnTitleXF73.UseBorder = true;
columnTitleXF73.LeftLineStyle = 1;
columnTitleXF73.TopLineStyle = 1;
columnTitleXF73.RightLineStyle = 1;
columnTitleXF73.BottomLineStyle = 2;
XF columnTitleXF77 = xls.NewXF();
columnTitleXF77.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF77.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF77.Font.Height = 10 * 20;
columnTitleXF77.Font.Bold = true;
columnTitleXF77.UseBorder = true;
columnTitleXF77.LeftLineStyle = 2;
columnTitleXF77.TopLineStyle = 1;
columnTitleXF77.RightLineStyle = 1;
columnTitleXF77.BottomLineStyle = 2;
XF columnTitleXF78 = xls.NewXF();
columnTitleXF78.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF78.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF78.Font.Height = 10 * 20;
columnTitleXF78.Font.Bold = true;
columnTitleXF78.UseBorder = true;
columnTitleXF78.TopLineStyle = 1;
columnTitleXF78.RightLineStyle = 2;
columnTitleXF78.BottomLineStyle = 2;
XF columnTitleXF82 = xls.NewXF();
columnTitleXF82.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF82.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF82.Font.Height = 10 * 20;
columnTitleXF82.UseBorder = true;
columnTitleXF82.LeftLineStyle = 2;
columnTitleXF82.TopLineStyle = 2;
columnTitleXF82.RightLineStyle = 2;
columnTitleXF82.BottomLineStyle = 1;
XF columnTitleXF83 = xls.NewXF();
columnTitleXF83.HorizontalAlignment = HorizontalAlignments.Right;
columnTitleXF83.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF83.Font.Height = 10 * 20;
columnTitleXF83.UseBorder = true;
columnTitleXF83.LeftLineStyle = 1;
columnTitleXF83.TopLineStyle = 1;
columnTitleXF83.RightLineStyle = 1;
columnTitleXF83.BottomLineStyle = 1;
XF columnTitleXF812 = xls.NewXF();
columnTitleXF812.HorizontalAlignment = HorizontalAlignments.Right;
columnTitleXF812.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF812.Font.Height = 10 * 20;
columnTitleXF812.UseBorder = true;
columnTitleXF812.LeftLineStyle = 1;
columnTitleXF812.TopLineStyle = 1;
columnTitleXF812.RightLineStyle = 2;
columnTitleXF812.BottomLineStyle = 1;
XF columnTitleXF92 = xls.NewXF();
columnTitleXF92.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF92.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF92.Font.Height = 10 * 20;
columnTitleXF92.UseBorder = true;
columnTitleXF92.LeftLineStyle = 2;
columnTitleXF92.TopLineStyle = 1;
columnTitleXF92.RightLineStyle = 2;
columnTitleXF92.BottomLineStyle = 1;
XF columnTitleXF93 = xls.NewXF();
columnTitleXF93.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF93.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF93.Font.Height = 10 * 20;
columnTitleXF93.UseBorder = true;
columnTitleXF93.LeftLineStyle = 1;
columnTitleXF93.TopLineStyle = 1;
XF columnTitleXF192 = xls.NewXF();
columnTitleXF192.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF192.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF192.Font.Height = 10 * 20;
columnTitleXF192.UseBorder = true;
columnTitleXF192.LeftLineStyle = 2;
columnTitleXF192.TopLineStyle = 1;
columnTitleXF192.RightLineStyle = 2;
columnTitleXF192.BottomLineStyle = 2;
XF columnTitleXF202 = xls.NewXF();
columnTitleXF202.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF202.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF202.Font.Height = 10 * 20;
columnTitleXF202.UseBorder = true;
columnTitleXF202.LeftLineStyle = 2;
columnTitleXF202.TopLineStyle = 2;
XF columnTitleXF208 = xls.NewXF();
columnTitleXF208.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF208.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF208.Font.Height = 10 * 20;
columnTitleXF208.UseBorder = true;
columnTitleXF208.LeftLineStyle = 1;
XF columnTitleXF215 = xls.NewXF();
columnTitleXF215.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF215.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF215.Font.Height = 10 * 20;
columnTitleXF215.UseBorder = true;
columnTitleXF215.LeftLineStyle = 1;
// 列标题行
ColumnInfo col1 = new ColumnInfo(xls, sheet); // 列对象
col1.ColumnIndexStart = 0; // 起始列为第1列，索引从0开始
col1.ColumnIndexEnd = 0; // 终止列为第1列，索引从0开始
col1.Width = 256; // 列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(col1); // 把格式附加到sheet页上
ColumnInfo col2 = new ColumnInfo(xls, sheet);
col2 = new ColumnInfo(xls, sheet); // 列对象
col2.ColumnIndexStart = 1;
col2.ColumnIndexEnd = 1;
col2.Width = 7680;
sheet.AddColumnInfo(col2);
ColumnInfo col3 = new ColumnInfo(xls, sheet);
col3 = new ColumnInfo(xls, sheet); // 列对象
col3.ColumnIndexStart = 2;
col3.ColumnIndexEnd = 9;
col3.Width = 1800;
sheet.AddColumnInfo(col3);
ColumnInfo col4 = new ColumnInfo(xls, sheet);
col4 = new ColumnInfo(xls, sheet); // 列对象
col4.ColumnIndexStart = 10;
col4.ColumnIndexEnd = 11;
col4.Width = 3900;
sheet.AddColumnInfo(col4);
//行
RowInfo rol1 = new RowInfo();
rol1.RowHeight = 10 * 20;
rol1.RowIndexStart = 1;
rol1.RowIndexEnd = 1;
sheet.AddRowInfo(rol1);
rol1 = new RowInfo();
rol1.RowHeight = 20 * 20;
rol1.RowIndexStart = 2;
rol1.RowIndexEnd = 2;
sheet.AddRowInfo(rol1);
rol1 = new RowInfo();
rol1.RowHeight = 5 * 20;
rol1.RowIndexStart = 3;
rol1.RowIndexEnd = 3;
sheet.AddRowInfo(rol1);
rol1 = new RowInfo();
rol1.RowHeight = 5 * 20;
rol1.RowIndexStart = 5;
rol1.RowIndexEnd = 5;
sheet.AddRowInfo(rol1);
rol1 = new RowInfo();
rol1.RowHeight = 18 * 20;
rol1.RowIndexStart = 6;
rol1.RowIndexEnd = 23;
sheet.AddRowInfo(rol1);
// 数据单元格样式
XF dataXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
dataXF.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
dataXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
dataXF.UseBorder = true; // 使用边框
dataXF.LeftLineStyle = 1; // 左边框样式
dataXF.LeftLineColor = Colors.Black; // 左边框颜色
dataXF.BottomLineStyle = 1; // 下边框样式
dataXF.BottomLineColor = Colors.Black; // 下边框颜色
dataXF.Font.FontName = "宋体";
dataXF.Font.Height = 9 * 20; // 设定字大小（字体大小是以 1/20 point 为单位的）
dataXF.UseProtection = false; // 默认的就是受保护的，导出后需要启用编辑才可修改
dataXF.TextWrapRight = true; // 自动换行
// 合并单元格
MergeArea titleArea = new MergeArea(1, 1, 1, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(2, 2, 1, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(3, 3, 1, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(4, 4, 3, 6);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(4, 4, 7, 10);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(4, 4, 11, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 11, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 3, 4);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 5, 6);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 7, 8);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 9, 10);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 7, 2, 2);
sheet.AddMergeArea(titleArea);
// 开始填充数据到单元格
org.in2bits.MyXls.Cells cells = sheet.Cells;
cells.Add(1, 1, "", titleXF);
cells.Add(2, 1, "Travel Expense Report", titleXF1);
cells.Add(3, 1, "", titleXF);
cells.Add(4, 1, "", columnTitleXF41);
cells.Add(4, 2, "Applicant:", columnTitleXF42);
cells.Add(4, 3, dtMain.Rows[0]["Person"].ToString(), columnTitleXF43);
cells.Add(4, 4, "", columnTitleXF44);
cells.Add(4, 5, "", columnTitleXF44);
cells.Add(4, 6, "", columnTitleXF46);
cells.Add(4, 7, "Travel Period:", columnTitleXF42);
string bb = "";
if (dtMain.Rows[0]["Bdate"].ToString()!="")
{
bb += Convert.ToDateTime(dtMain.Rows[0]["Bdate"].ToString()).ToString("yyyy/MM/dd");
}
if (dtMain.Rows[0]["Edate"].ToString() != "")
{
bb += " - " + Convert.ToDateTime(dtMain.Rows[0]["Edate"].ToString()).ToString("yyyy/MM/dd");
}
cells.Add(4, 11, bb, columnTitleXF43);
cells.Add(4, 12, "", columnTitleXF412);
cells.Add(5, 1, "", columnTitleXF41);
cells.Add(6, 1, "", columnTitleXF41);
cells.Add(6, 2, "Travel Destination", columnTitleXF62);
cells.Add(6, 3, "", columnTitleXF63);
cells.Add(6, 4, "", columnTitleXF64);
cells.Add(6, 5, "", columnTitleXF63);
cells.Add(6, 6, "", columnTitleXF64);
cells.Add(6, 7, "", columnTitleXF63);
cells.Add(6, 8, "", columnTitleXF64);
cells.Add(6, 9, "", columnTitleXF63);
cells.Add(6, 10, "", columnTitleXF64);
cells.Add(6, 11, "Total Expenses", columnTitleXF67);
cells.Add(6, 12, "", columnTitleXF68);
cells.Add(7, 1, "", columnTitleXF41);
cells.Add(7, 2, "", columnTitleXF72);
cells.Add(7, 3, "", columnTitleXF73);
cells.Add(7, 4, "", columnTitleXF73);
cells.Add(7, 5, "", columnTitleXF73);
cells.Add(7, 6, "", columnTitleXF73);
cells.Add(7, 7, "", columnTitleXF73);
cells.Add(7, 8, "", columnTitleXF73);
cells.Add(7, 9, "", columnTitleXF73);
cells.Add(7, 10, "", columnTitleXF73);
cells.Add(7, 11, "Reimbursement", columnTitleXF77);
cells.Add(7, 12, "Company Paid", columnTitleXF78);
cells.Add(8, 1, "", columnTitleXF41);
cells.Add(8, 2, "1. Air Ticket - Int'l", columnTitleXF82);
cells.Add(9, 1, "", columnTitleXF41);
cells.Add(9, 2, "Domestic", columnTitleXF92);
cells.Add(10, 1, "", columnTitleXF41);
cells.Add(10, 2, "2. Hotel Bill", columnTitleXF92);
cells.Add(11, 1, "", columnTitleXF41);
cells.Add(11, 2, "3. Meals", columnTitleXF92);
cells.Add(12, 1, "", columnTitleXF41);
cells.Add(12, 2, "4. Entertainment", columnTitleXF92);
cells.Add(13, 1, "", columnTitleXF41);
cells.Add(13, 2, "5. Car Rental/Transportation", columnTitleXF92);
cells.Add(14, 1, "", columnTitleXF41);
cells.Add(14, 2, "6. Communication", columnTitleXF92);
cells.Add(15, 1, "", columnTitleXF41);
cells.Add(15, 2, "7. Local Trip NTD800(CNY60)/day", columnTitleXF92);
cells.Add(16, 1, "", columnTitleXF41);
cells.Add(16, 2, "8. Overseas Trip USD15/day", columnTitleXF92);
cells.Add(17, 1, "", columnTitleXF41);
cells.Add(17, 2, "9. Airport Tax/Travel Insurance", columnTitleXF92);
cells.Add(18, 1, "", columnTitleXF41);
cells.Add(18, 2, "10. Others", columnTitleXF92);
cells.Add(19, 1, "", columnTitleXF41);
cells.Add(19, 2, "Total", columnTitleXF192);
cells.Add(20, 1, "", columnTitleXF41);
cells.Add(20, 2, "Remarks: USD: NTD =", columnTitleXF202);
cells.Add(20, 12, "Total Trip Expense", columnTitleXF208);
cells.Add(21, 7, "Less: Advance", columnTitleXF215);
cells.Add(22, 7, "Bal Due to Company", columnTitleXF215);
cells.Add(23, 7, "Bal. Due to Employee", columnTitleXF215);
for (int i = 0; i < 12; i++)
{
for (int ii = 0; ii < 9; ii++)
{
cells.Add(8 + i, 3 + ii, "", columnTitleXF83);
}
}
for (int i = 0; i < 12; i++)
{
cells.Add(8 + i, 12, "", columnTitleXF812);
}
string sqlDetail = "select * from ETraveleDetail where [No]='" + MainID + "'";
DataTable dtDetail = dbc.GetData("eReimbursement", sqlDetail);
decimal row1TC = 0M;
decimal row1TP = 0M;
decimal row2TC = 0M;
decimal row2TP = 0M;
decimal row3TC = 0M;
decimal row3TP = 0M;
decimal row4TC = 0M;
decimal row4TP = 0M;
decimal row5TC = 0M;
decimal row5TP = 0M;
decimal row6TC = 0M;
decimal row6TP = 0M;
decimal row7TC = 0M;
decimal row7TP = 0M;
decimal row8TC = 0M;
decimal row8TP = 0M;
decimal row9TC = 0M;
decimal row9TP = 0M;
decimal row10TC = 0M;
decimal row10TP = 0M;
decimal row11TC = 0M;
decimal row11TP = 0M;
for (int p = 0; p < dtTocity.Rows.Count; p++)
{
if (Convert.ToInt32(dtTocity.Rows[p]["Row"].ToString()) == j)
{
decimal row1Pamount = 0M;
decimal row1Camount = 0M;
decimal row2Pamount = 0M;
decimal row2Camount = 0M;
decimal row3Pamount = 0M;
decimal row3Camount = 0M;
decimal row4Pamount = 0M;
decimal row4Camount = 0M;
decimal row5Pamount = 0M;
decimal row5Camount = 0M;
decimal row6Pamount = 0M;
decimal row6Camount = 0M;
decimal row7Pamount = 0M;
decimal row7Camount = 0M;
decimal row8Pamount = 0M;
decimal row8Camount = 0M;
decimal row9Pamount = 0M;
decimal row9Camount = 0M;
decimal row10Pamount = 0M;
decimal row10Camount = 0M;
decimal row11Pamount = 0M;
decimal row11Camount = 0M;
decimal column0TC = 0M;
decimal column1TP = 0M;
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
if (dtDetail.Rows[i]["Tocity"].ToString() == dtTocity.Rows[p]["Tocity"].ToString())
{
if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012023")//Air Ticket - Int'l
{
row1Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row1Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row1TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row1TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012011" || dtDetail.Rows[i]["AccountCode"].ToString() == "62012021")//Hotel Bill
{
row3Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row3Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row3TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row3TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62010901" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010910" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010920")//Enter
{
row5Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row5Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row5TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row5TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62011901" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011910" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011920" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011930" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011940" || dtDetail.Rows[i]["AccountCode"].ToString() == "62012013")//Car
{
row6Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row6Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row6TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row6TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62010501" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010510" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010520" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010530" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010540" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010550" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010560")//Commu
{
row7Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row6Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row7TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row7TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012012")//Local
{
row8Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row8Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row8TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row8TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012022")//Oversea
{
row9Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row9Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row9TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row9TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62020630")//Airport
{
row10Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row10Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row10TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row10TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012014" || dtDetail.Rows[i]["AccountCode"].ToString() == "62012024")//Others
{
row11Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row11Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row11TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row11TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else
{
row11Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row11Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row11TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row11TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
column0TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
column1TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
}
cells.Add(6, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, dtTocity.Rows[p]["Tocity"].ToString(), columnTitleXF63);
cells.Add(7, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, "Reim", columnTitleXF73);
cells.Add(7, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, "Comp", columnTitleXF73);
if (row1Pamount != 0M)
{
cells.Add(8, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row1Pamount, columnTitleXF83);
}
if (row1Camount != 0M)
{
cells.Add(8, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row1Camount, columnTitleXF83);
}
if (row2Pamount != 0M)
{
cells.Add(9, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row2Pamount, columnTitleXF83);
}
if (row2Camount != 0M)
{
cells.Add(9, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row2Camount, columnTitleXF83);
}
if (row3Pamount != 0M)
{
cells.Add(10, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row3Pamount, columnTitleXF83);
}
if (row3Camount != 0M)
{
cells.Add(10, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row3Camount, columnTitleXF83);
}
if (row4Pamount != 0M)
{
cells.Add(11, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row4Pamount, columnTitleXF83);
}
if (row4Camount != 0M)
{
cells.Add(11, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row4Camount, columnTitleXF83);
}
if (row5Pamount != 0M)
{
cells.Add(12, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row5Pamount, columnTitleXF83);
}
if (row5Camount != 0M)
{
cells.Add(12, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row5Camount, columnTitleXF83);
}
if (row6Pamount != 0M)
{
cells.Add(13, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row6Pamount, columnTitleXF83);
}
if (row6Camount != 0M)
{
cells.Add(13, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row6Camount, columnTitleXF83);
}
if (row7Pamount != 0M)
{
cells.Add(14, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row7Pamount, columnTitleXF83);
}
if (row7Camount != 0M)
{
cells.Add(14, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row7Camount, columnTitleXF83);
}
if (row8Pamount != 0M)
{
cells.Add(15, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row8Pamount, columnTitleXF83);
}
if (row8Camount != 0M)
{
cells.Add(15, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row8Camount, columnTitleXF83);
}
if (row9Pamount != 0M)
{
cells.Add(16, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row9Pamount, columnTitleXF83);
}
if (row9Camount != 0M)
{
cells.Add(16, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row9Camount, columnTitleXF83);
}
if (row10Pamount != 0M)
{
cells.Add(17, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row10Pamount, columnTitleXF83);
}
if (row10Camount != 0M)
{
cells.Add(17, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row10Camount, columnTitleXF83);
}
if (row11Pamount != 0M)
{
cells.Add(18, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row11Pamount, columnTitleXF83);
}
if (row11Camount != 0M)
{
cells.Add(18, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row11Camount, columnTitleXF83);
}
if (column0TC != 0M)
{
cells.Add(19, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, column0TC, columnTitleXF83);
}
if (column1TP != 0M)
{
cells.Add(19, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, column1TP, columnTitleXF83);
}
}
}
if (row1TC != 0M)
{
cells.Add(8, 11, row1TC, columnTitleXF83);
}
if (row1TP != 0M)
{
cells.Add(8, 12, row1TP, columnTitleXF812);
}
if (row2TC != 0M)
{
cells.Add(9, 11, row2TC, columnTitleXF83);
}
if (row2TP != 0M)
{
cells.Add(9, 12, row2TP, columnTitleXF812);
}
if (row3TC != 0M)
{
cells.Add(10, 11, row3TC, columnTitleXF83);
}
if (row3TP != 0M)
{
cells.Add(10, 12, row3TP, columnTitleXF812);
}
if (row4TC != 0M)
{
cells.Add(11, 11, row4TC, columnTitleXF83);
}
if (row4TP != 0M)
{
cells.Add(11, 12, row4TP, columnTitleXF812);
}
if (row5TC != 0M)
{
cells.Add(12, 11, row5TC, columnTitleXF83);
}
if (row5TP != 0M)
{
cells.Add(12, 12, row5TP, columnTitleXF812);
}
if (row6TC != 0M)
{
cells.Add(13, 11, row6TC, columnTitleXF83);
}
if (row6TP != 0M)
{
cells.Add(13, 12, row6TP, columnTitleXF812);
}
if (row7TC != 0M)
{
cells.Add(14, 11, row7TC, columnTitleXF83);
}
if (row7TP != 0M)
{
cells.Add(14, 12, row7TP, columnTitleXF812);
}
if (row8TC != 0M)
{
cells.Add(15, 11, row8TC, columnTitleXF83);
}
if (row8TP != 0M)
{
cells.Add(15, 12, row8TP, columnTitleXF812);
}
if (row9TC != 0M)
{
cells.Add(16, 11, row9TC, columnTitleXF83);
}
if (row9TP != 0M)
{
cells.Add(16, 12, row9TP, columnTitleXF812);
}
if (row10TC != 0M)
{
cells.Add(17, 11, row10TC, columnTitleXF83);
}
if (row10TP != 0M)
{
cells.Add(17, 12, row10TP, columnTitleXF812);
}
if (row11TC != 0M)
{
cells.Add(18, 11, row11TC, columnTitleXF83);
}
if (row11TP != 0M)
{
cells.Add(18, 12, row11TP, columnTitleXF812);
}
decimal tc = row1TC + row2TC + row3TC + row4TC + row5TC + row6TC + row7TC + row8TC + row9TC + row10TC + row11TC;
if (tc!=0M)
{
cells.Add(19, 11, tc, columnTitleXF83);
}
decimal tp = row1TP + row2TP + row3TP + row4TP + row5TP + row6TP + row7TP + row8TP + row9TP + row11TP;
if (tp != 0M)
{
cells.Add(19, 12, tp, columnTitleXF812);
}
}
xls.Send();
}
double GetKospiPortion(RowInfo row, double dollarPortion)
{
//=MAX(remainPortion * (1 - 코스피현물위치) * 국채현물리버스위치 * volWeight, 0) * MULTIPLIER
double kospiPortion = 0;
double remainPortion = 1.0 - dollarPortion;
double reverseKospiSpotPosition = 1.0 - row.KospiSpotPosition;
if (bRateRange)
{
reverseKospiSpotPosition = 1.0 / row.KospiSpotPosition;
if (reverseKospiSpotPosition < 0)
{
reverseKospiSpotPosition = 0;
}
}
double volWeight = MagicNumber.KOSPI_VOL_PIVOT / row.KospiVol;
double basePortion = remainPortion * reverseKospiSpotPosition * row.KtbSpotPosition * volWeight;
kospiPortion = Math.Max(basePortion, 0) * MagicNumber.KOSPI_MULTIPLIER;
kospiPortion = Math.Min(kospiPortion, remainPortion);
return kospiPortion;
}
private float RowHeight(RowInfo r)
{
return(TableY(r.bottomLine.p1) - TableY(r.topLine.p1));
}
void SetPositionInfo(DateTime curDate, MarketDataSet data, RowInfo row)
{
MarketData mdKospiSpot = data.GetData(MarketDataSetKey.KospiSpot);
MarketData mdKtbSpot = data.GetData(MarketDataSetKey.KtbSpot);
MarketData mdDollarSpot = data.GetData(MarketDataSetKey.DollarSpot);
DOHLC kospiSpot = mdKospiSpot.GetData(curDate);
DOHLC ktbSpot = mdKtbSpot.GetData(curDate);
DOHLC dollarSpot = mdDollarSpot.GetData(curDate);
double kospiSpotPos = (kospiSpot.OHLC.Close - _kospiSpotMinRange) /
(_kospiSpotMaxRange - _kospiSpotMinRange);
double ktbSpotPos = (_ktbSpotMaxRange - ktbSpot.OHLC.Close) /
(_ktbSpotMaxRange - _ktbSpotMinRange);
double dollarSpotPos = (dollarSpot.OHLC.Close - _dollarSpotMinRange) /
(_dollarSpotMaxRange - _dollarSpotMinRange);
if (bRateRange)
{
kospiSpotPos = (kospiSpot.OHLC.Close / _maKospiSpot.GetCurMA());
//dollarSpotPos = (dollarSpot.OHLC.Close / _maDollarSpot.GetCurMA());
}
row.KospiSpotPosition = kospiSpotPos;
row.KtbSpotPosition = ktbSpotPos;
row.DollarSpotPosition = dollarSpotPos;
}
/// <summary>
/// Removes the specified rows.
/// </summary>
/// <param name="rowInfos">The row infos.</param>
public virtual void RemoveRows(RowInfo[] rowInfos)
{
List<Range> rangeToRemove = new List<Range>(rowInfos.Length);
foreach (RowInfo rowInfo in rowInfos)
{
rangeToRemove.Add(new Range(rowInfo.Index, 0, rowInfo.Index, this.ColumnsCount - 1));
}
RemoveRangeRows(rangeToRemove);
}
public void Add(RowInfo row)
{
if (this.KospiFutureIncrements.Count == this.KospiN)
{
this.KospiFutureIncrements.Dequeue();
}
this.KospiFutureIncrements.Enqueue(row.KospiFutureIncrement);
}
private void ExpandRowByRowInfo(RowInfo rowInfo)
{
var dataRowHandle = LocateByValue(0, Columns[idFieldName], rowInfo.Value);
if (dataRowHandle == DevExpress.XtraGrid.GridControl.InvalidRowHandle)
{
return;
}
var parentRowHandle = FindParentRowHandle(rowInfo, dataRowHandle);
SetRowExpanded(parentRowHandle, true, false);
}
protected override void CalcSelectImage(RowInfo ri)
{
base.CalcSelectImage(ri);
if (-1 == ri.Node.SelectImageIndex)
ri.SelectImageLocation = Point.Empty;
}
public async Task CreateOrUpdateRow(RowInfo row)
{
await repo_.CreateOrUpdateRowAsync(row);
}
