protected int wf_42011_2(int minusRow, Worksheet ws, DataTable dt)
{
int rowIndex, startRow, totalRow, f, headRow;
string rptName;
Range range;
startRow = 322 - 1;
totalRow = 300;
startRow = startRow - minusRow;
rowIndex = startRow;
headRow = 4 - 1;
if (!cbx1.Checked)
{
headRow = headRow - 1;
}
if (!cbx2.Checked)
{
//刪明細
startRow = 321 - minusRow;
range = ws.Range[(startRow - 4 + 1) + ":" + (startRow + totalRow + 2 + 1)];
range.Delete(DeleteMode.EntireRow);
minusRow = minusRow + (totalRow + 5) + 2; //5表首,2表尾
//刪表頭
range = ws.Range[(headRow + 1) + ":" + (headRow + 1)];
range.Delete(DeleteMode.EntireRow);
minusRow = minusRow + 1;
//改編號
if (cbx1.Checked)
{
f = 2;
}
else
{
f = 1;
}
ws.Cells[headRow, 1].Value = f.AsString();
ws.Cells[headRow + 1, 1].Value = (f + 1).AsString();
ws.Cells[headRow + 2, 1].Value = (f + 2).AsString();
ws.Cells[headRow + 3, 1].Value = (f + 3).AsString();
return(minusRow);
}
if (!cbx1.Checked)
{
ws.Cells[rowIndex - 4, 1].Value = "1";
}
if (txtRange.Text != "8.5" || txtRate2.Text != "10.5" || txtRate3.Text != "13.5" || txtRate4.Text != "1.0" || lblCmRate.Text != "15")
{
//表首
RichTextString richText = new RichTextString();
richText = ws.Cells[headRow, 2].GetRichText();
if (txtRange.Text != "8.5")
{
f = richText.Text.IndexOf("8.5 %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 5).Text = txtRate1.Text + "%";
}
}
if (txtRate2.Text != "10.5")
{
f = richText.Text.IndexOf("10.5 %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 6).Text = txtRate2.Text + "%";
}
}
if (txtRate3.Text != "13.5")
{
f = richText.Text.IndexOf("13.5 %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 6).Text = txtRate3.Text + "%";
}
}
if (txtRate4.Text != "1.0")
{
f = richText.Text.IndexOf("1.0 %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 5).Text = txtRate4.Text + "%";
}
}
if (lblCmRate.Text != "12")
{
f = richText.Text.IndexOf("12 %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 4).Text = lblCmRate.Text + "%";
}
}
ws.Cells[headRow, 2].SetRichText(richText);
//表頭
richText = ws.Cells[rowIndex - 4, 2].GetRichText();
if (txtRange.Text != "8.5")
{
f = richText.Text.IndexOf("8.5 %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 5).Text = txtRate1.Text + "%";
}
}
if (txtRate2.Text != "10.5")
{
f = richText.Text.IndexOf("10.5 %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 6).Text = txtRate2.Text + "%";
}
}
if (txtRate3.Text != "13.5")
{
f = richText.Text.IndexOf("13.5 %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 6).Text = txtRate3.Text + "%";
}
}
if (txtRate4.Text != "1.0")
{
f = richText.Text.IndexOf("1.0 %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 5).Text = txtRate4.Text + "%";
}
}
if (lblCmRate.Text != "12")
{
f = richText.Text.IndexOf("12 %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 4).Text = lblCmRate.Text + "%";
}
}
ws.Cells[rowIndex - 4, 2].SetRichText(richText);
}
if (cbxRate.Checked)
{
DataView dv = dt.AsDataView();
dv.RowFilter = "rpt3_flag='Y'";
dt = dv.ToTable();
}
dt = dt.Sort("MGR2_DAY_RATE DESC, APDK_KIND_GRP2, APDK_KIND_LEVEL DESC, MGR3_KIND_ID");
f = 0;
foreach (DataRow dr in dt.Rows)
{
rowIndex++;
f++;
ws.Cells[rowIndex, 1].Value = f.AsString();
ws.Cells[rowIndex, 2].Value = dr["MGR3_KIND_ID"].AsString();
ws.Cells[rowIndex, 3].Value = dr["APDK_NAME"].AsString();
ws.Cells[rowIndex, 4].Value = dr["MGR3_SID"].AsString();
ws.Cells[rowIndex, 5].Value = dr["PID_NAME"].AsString();
ws.Cells[rowIndex, 6].SetValue(dr["MGR2_DAY_RATE"]);
ws.Cells[rowIndex, 7].SetValue(dr["T_30_RATE"]);
ws.Cells[rowIndex, 8].SetValue(dr["MGR2_DAY_RATE_AVG_1Y"]);
if (dr["MGR3_CUR_LEVEL"].AsString() == "Z")
{
ws.Cells[rowIndex, 9].Value = "從其高(" + (dr["MGR3_CUR_CM"].AsDecimal() * 100).AsString() + "%)";
}
else
{
ws.Cells[rowIndex, 9].Value = dr["MGR3_CUR_LEVEL"].AsString();
}
if (dr["DAY_CNT_3"].AsInt() == 0)
{
RichTextString richText = new RichTextString();
richText.AddTextRun("-", new RichTextRunFont("Times New Roman", 12));
ws.Cells[rowIndex, 10].SetRichText(richText);
}
else
{
RichTextString richText = new RichTextString();
richText.AddTextRun(dr["DAY_CNT_3"].AsInt().ToString(), new RichTextRunFont("Times New Roman", 12));
ws.Cells[rowIndex, 10].SetRichText(richText);
}
ws.Cells[rowIndex, 11].SetValue(dr["TFXM1_PRICE"]);
ws.Cells[rowIndex, 12].SetValue(dr["AI5_PRICE"]);
ws.Cells[rowIndex, 13].SetValue(dr["TS_UPDOWN"]);
ws.Cells[rowIndex, 14].SetValue(dr["TI_UPDOWN"]);
ws.Cells[rowIndex, 15].SetValue(dr["YS_UPDOWN"]);
ws.Cells[rowIndex, 16].SetValue(dr["YI_UPDOWN"]);
ws.Cells[rowIndex, 17].SetValue(dr["AI2_OI"]);
ws.Cells[rowIndex, 18].SetValue(dr["AI2_M_QNTY"]);
}
//刪除空白列
//刪"本日無"
if (dt.Rows.Count > 0)
{
range = ws.Range[(startRow - 3 + 1) + ":" + (startRow - 3 + 1)];
range.Delete(DeleteMode.EntireRow);
startRow = startRow - 1;
minusRow = minusRow + 1;
}
//刪多餘空白列
if (dt.Rows.Count < totalRow)
{
//沒資料連表頭都刪
if (dt.Rows.Count == 0)
{
startRow = startRow - 3;
totalRow = totalRow + 4;
//表頭3列+表尾註1列
}
range = ws.Range[(startRow + dt.Rows.Count + 1 + 1) + ":" + (startRow + totalRow + 1)];
range.Delete(DeleteMode.EntireRow);
minusRow = minusRow + (totalRow - dt.Rows.Count);
}
return(minusRow);
}
protected int wf_42011_3(int minusRow, Worksheet ws, DataTable dt)
{
int rowIndex, startRow, totalRow, f, headRow;
string rptName;
Range range;
//3.現貨或期貨連續二日漲跌幅度≧12%之股票期貨(以現貨連續二日漲跌幅度之絕對值由大至小排序)
startRow = 629 - 1;
totalRow = 300;
startRow = startRow - minusRow;
rowIndex = startRow;
headRow = 5 - 1;
f = dt.Rows.Count;
if (!cbx1.Checked)
{
headRow--;
f--;
}
if (!cbx2.Checked)
{
headRow--;
f--;
}
if (!cbx3.Checked)
{
//刪明細
range = ws.Range[(startRow - 4 + 1) + ":" + (startRow + totalRow + 2 + 1)];
range.Delete(DeleteMode.EntireRow);
minusRow = minusRow + (totalRow + 5);
//刪表頭
f = 3;
if (!cbx1.Checked)
{
f--;
}
if (!cbx2.Checked)
{
f--;
}
range = ws.Range[(headRow + 1) + ":" + (headRow + 1)];
range.Delete(DeleteMode.EntireRow);
minusRow = minusRow + 1;
//改編號
ws.Cells[headRow, 1].Value = f.AsString();
ws.Cells[headRow + 1, 1].Value = (f + 1).AsString();
ws.Cells[headRow + 2, 1].Value = (f + 2).AsString();
return(minusRow);
}
if (!cbx1.Checked)
{
ws.Cells[rowIndex - 4, 1].Value = ws.Cells[rowIndex - 4, 1].Value.AsInt() - 1;
}
if (!cbx2.Checked)
{
ws.Cells[rowIndex - 4, 1].Value = ws.Cells[rowIndex - 4, 1].Value.AsInt() - 1;
}
if (txtUpDown.Text != "12")
{
//表首
//使用RichTextString才能保留原本設定的格式
RichTextString richText = new RichTextString();
richText = ws.Cells[headRow, 2].GetRichText();
f = richText.Text.IndexOf("12%") + 1;
if (f > 0)
{
richText.Characters(f - 1, 3).Text = txtUpDown.Text + "%";
ws.Cells[headRow, 2].SetRichText(richText);
}
//表頭
richText = ws.Cells[rowIndex - 4, 2].GetRichText();
f = richText.Text.IndexOf("12%") + 1;
if (f > 0)
{
richText.Characters(f - 1, 3).Text = txtUpDown.Text + "%";
ws.Cells[rowIndex - 4, 2].SetRichText(richText);
}
}
DataView dv = dt.AsDataView();
dv.RowFilter = "(YS_UPDOWN_ABS*100) >= " + txtUpDown.Text + " or (YI_UPDOWN_ABS*100) >= " + txtUpDown.Text;
dv.Sort = "YS_UPDOWN_ABS DESC, APDK_KIND_GRP2, APDK_KIND_LEVEL DESC, MGR3_KIND_ID";
dt = dv.ToTable();
//dt = dt.AsEnumerable().Where(x => Math.Round(Math.Abs(x.Field<decimal>("YS_UPDOWN") * 100),16) >= txtUpDown.AsDecimal() ||
// Math.Round(Math.Abs(x.Field<decimal>("YI_UPDOWN") * 100), 16) >= txtUpDown.AsDecimal())
// .OrderByDescending(x => Math.Round(Math.Abs(x.Field<decimal>("YS_UPDOWN")),16))
// .ThenBy(x => x.Field<string>("APDK_KIND_GRP2"))
// .ThenByDescending(x => x.Field<int>("APDK_KIND_LEVEL"))
// .ThenBy(x => x.Field<string>("MGR3_KIND_ID")).CopyToDataTable();
//dt = dt.AsEnumerable().Where(x => Math.Round(Math.Abs(x.Field<decimal>("YS_UPDOWN") * 100), 15,MidpointRounding.AwayFromZero) >= txtUpDown.AsDecimal() ||
// Math.Round(Math.Abs(x.Field<decimal>("YI_UPDOWN") * 100), 15, MidpointRounding.AwayFromZero) >= txtUpDown.AsDecimal())
// .OrderByDescending(x => Math.Abs(x.Field<decimal>("YS_UPDOWN")))
// .ThenBy(x => x.Field<string>("APDK_KIND_GRP2"))
// .ThenByDescending(x => x.Field<Int16>("APDK_KIND_LEVEL"))
// .ThenBy(x => x.Field<string>("MGR3_KIND_ID"))
// .CopyToDataTable();
f = 0;
foreach (DataRow dr in dt.Rows)
{
rowIndex++;
f++;
ws.Cells[rowIndex, 1].Value = f.AsString();
ws.Cells[rowIndex, 2].Value = dr["MGR3_KIND_ID"].AsString();
ws.Cells[rowIndex, 3].Value = dr["APDK_NAME"].AsString();
ws.Cells[rowIndex, 4].Value = dr["MGR3_SID"].AsString();
ws.Cells[rowIndex, 5].Value = dr["PID_NAME"].AsString();
ws.Cells[rowIndex, 6].SetValue(dr["TFXM1_PRICE"]);
ws.Cells[rowIndex, 7].SetValue(dr["AI5_PRICE"]);
ws.Cells[rowIndex, 8].SetValue(dr["TS_UPDOWN"]);
ws.Cells[rowIndex, 9].SetValue(dr["TI_UPDOWN"]);
ws.Cells[rowIndex, 10].SetValue(dr["YS_UPDOWN"]);
ws.Cells[rowIndex, 11].SetValue(dr["YI_UPDOWN"]);
ws.Cells[rowIndex, 12].SetValue(dr["T_30_RATE"]);
ws.Cells[rowIndex, 13].SetValue(dr["MGR2_DAY_RATE"]);
if (dr["MGR3_CUR_LEVEL"].AsString() == "Z")
{
RichTextString richText = new RichTextString();
richText.AddTextRun("從其高", new RichTextRunFont("標楷體", 12));
richText.AddTextRun("(" + (dr["MGR3_CUR_CM"].AsDecimal() * 100).AsString() + "%)", new RichTextRunFont("Times New Roman", 12));
ws.Cells[rowIndex, 14].SetRichText(richText);
}
else
{
RichTextString richText = new RichTextString();
richText.AddTextRun(dr["MGR3_CUR_LEVEL"].AsString(), new RichTextRunFont("Times New Roman", 12));
ws.Cells[rowIndex, 14].SetRichText(richText);
}
ws.Cells[rowIndex, 15].SetValue(dr["AI2_OI"]);
ws.Cells[rowIndex, 16].SetValue(dr["AI2_M_QNTY"]);
}
//刪除空白列
//刪"本日無"
if (dt.Rows.Count > 0)
{
range = ws.Range[(startRow - 3 + 1) + ":" + (startRow - 3 + 1)];
range.Delete(DeleteMode.EntireRow);
startRow = startRow - 1;
minusRow = minusRow + 1;
}
//刪多餘空白列
if (dt.Rows.Count < totalRow)
{
//沒資料連表頭都刪
if (dt.Rows.Count == 0)
{
startRow = startRow - 3;
totalRow = totalRow + 3;
}
range = ws.Range[(startRow + dt.Rows.Count + 1 + 1) + ":" + (startRow + totalRow + 1)];
range.Delete(DeleteMode.EntireRow);
minusRow = minusRow + (totalRow - dt.Rows.Count);
}
return(minusRow);
}
protected override ResultStatus Export()
{
try {
if (txtSID.Text.AsString() == "")
{
MessageDisplay.Error("請輸入標的證券代號");
return(ResultStatus.Fail);
}
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
lblProcessing.Visible = true;
ShowMsg("開始轉檔...");
string rptId = "42012", file, rptName = "股票期貨風險價格係數分析表";
//讀取資料(保證金適用比例級距)
DataTable dt42012 = dao42012.d_42012_detl(txtSDate.DateTimeValue.ToString("yyyyMMdd"), txtEDate.DateTimeValue.ToString("yyyyMMdd"), txtSID.Text.AsString(),
txtRange.Text.AsDecimal() / 100, txtRate2Ref.Text.AsDecimal() / 100, txtRate3Ref.Text.AsDecimal() / 100, txtRate4Ref.Text.AsDecimal() / 100,
txtRate1.Text.AsDecimal() / 100, txtRate2.Text.AsDecimal() / 100, txtRate3.Text.AsDecimal() / 100, txtRate4.Text.AsDecimal() / 100);
if (dt42012.Rows.Count == 0)
{
MessageDisplay.Info(txtSDate.Text + "～" + txtEDate.Text + "," + rptId + '－' + rptName + ",無任何資料!");
lblProcessing.Visible = false;
return(ResultStatus.Fail);
}
dt42012 = dt42012.Sort("MGR3_YMD");
//複製檔案
file = PbFunc.wf_copy_file(rptId, rptId);
if (file == "")
{
return(ResultStatus.Fail);
}
//開啟檔案
Workbook workbook = new Workbook();
workbook.LoadDocument(file);
//切換Sheet
Worksheet ws = workbook.Worksheets[0];
ws.Cells[0, 15].Value = txtSDate.Text + "～" + txtEDate.Text;
//填資料
//表首
int f, rowIndex, startRow = 3 - 1, totalRow = 300, minusRow = 0;
Range range;
if (cbx1.Checked)
{
if (txtRange.Text != "10")
{
//表首
//使用RichTextString才能保留原本設定的格式
RichTextString richText = new RichTextString();
richText = ws.Cells[2, 2].GetRichText();
f = richText.Text.IndexOf("10%") + 1;
if (f > 0)
{
richText.Characters(f - 1, 3).Text = txtRange.Text + "%";
richText.Characters(f - 1, txtRange.Text.Length).Font.Color = Color.Red;
}
ws.Cells[startRow, 2].SetRichText(richText);
}
}
else
{
range = ws.Range[(startRow + 1).AsString() + ":" + (startRow + 1).AsString()];
range.Delete(DeleteMode.EntireRow);
minusRow = minusRow + 1;
//改編號
ws.Cells[startRow, 1].Value = "1";
ws.Cells[startRow + 1, 1].Value = "2";
ws.Cells[startRow + 2, 1].Value = "3";
ws.Cells[startRow + 3, 1].Value = "4";
}
startRow = 4 - 1;
startRow = startRow - minusRow;
if (cbx2.Checked)
{
if (txtRange.Text != _CON1 || txtRate2.Text != _CON2 || txtRate3.Text != _CON3 || txtRate4.Text != _CON4 || lblCmRate.Text != _CON5)
{
//表首
RichTextString richText = new RichTextString();
richText = ws.Cells[startRow, 2].GetRichText();
if (txtRange.Text != _CON1)
{
f = richText.Text.IndexOf(_CON1 + " %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 5).Text = txtRate1.Text + "%";
richText.Characters(f - 1, txtRate1.Text.Length).Font.Color = Color.Red;
}
}
if (txtRate2.Text != _CON2)
{
f = richText.Text.IndexOf(_CON2 + " %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 6).Text = txtRate2.Text + "%";
richText.Characters(f - 1, txtRate2.Text.Length).Font.Color = Color.Red;
}
}
if (txtRate3.Text != _CON3)
{
f = richText.Text.IndexOf(_CON3 + " %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 6).Text = txtRate3.Text + "%";
richText.Characters(f - 1, txtRate3.Text.Length).Font.Color = Color.Red;
}
}
if (txtRate4.Text != _CON4)
{
f = richText.Text.IndexOf(_CON4 + " %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 5).Text = txtRate4.Text + "%";
richText.Characters(f - 1, txtRate4.Text.Length).Font.Color = Color.Red;
}
}
if (lblCmRate.Text != _CON5)
{
f = richText.Text.IndexOf(_CON5 + "%") + 1;
if (f > 0)
{
richText.Characters(f - 1, 5).Text = lblCmRate.Text + "%";
richText.Characters(f - 1, lblCmRate.Text.Length).Font.Color = Color.Red;
}
}
ws.Cells[startRow, 2].SetRichText(richText);
}
}
else
{
range = ws.Range[(startRow + 1).AsString() + ":" + (startRow + 1).AsString()];
range.Delete(DeleteMode.EntireRow);
minusRow = minusRow + 1;
//改編號
ws.Cells[startRow, 1].Value = "2";
ws.Cells[startRow + 1, 1].Value = "3";
ws.Cells[startRow + 2, 1].Value = "4";
}
#region 表1
startRow = 13 - 1;
startRow = startRow - minusRow;
totalRow = 1000;
rowIndex = startRow;
if (cbx1.Checked)
{
if (txtRange.Text != "10")
{
//表頭
RichTextString richText = new RichTextString();
richText = ws.Cells[rowIndex - 3, 2].GetRichText();
f = richText.Text.IndexOf("10%") + 1;
if (f > 0)
{
richText.Characters(f - 1, 3).Text = txtRange.Text + "%";
richText.Characters(f - 1, txtRange.Text.Length).Font.Color = Color.Red;
ws.Cells[rowIndex - 3, 2].SetRichText(richText);
}
}
if (cbxRate.Checked)
{
DataView dv = dt42012.AsDataView();
dv.RowFilter = "(mgr2_level = 'Z' and T_30_RATE >= (MGR3_CUR_CM - " +
(txtRate4Ref.Text.AsDecimal() / 100).AsString() + ")) or (mgr2_level = '1' and t_30_rate >= " +
(txtRate1Ref.Text.AsDecimal() / 100).AsString() + ") or (mgr2_level = '2' and t_30_rate >= " +
(txtRate2Ref.Text.AsDecimal() / 100).AsString() + ")";
dt42012 = dv.ToTable();
}
foreach (DataRow dr in dt42012.Rows)
{
rowIndex++;
ws.Cells[rowIndex, 1].Value = dr["MGR3_YMD"].AsDateTime("yyyyMMdd").ToString("yyyy/MM/dd");
ws.Cells[rowIndex, 2].Value = dr["MGR3_KIND_ID"].AsString();
ws.Cells[rowIndex, 3].Value = dr["APDK_NAME"].AsString();
ws.Cells[rowIndex, 4].Value = dr["MGR3_SID"].AsString();
ws.Cells[rowIndex, 5].Value = dr["PID_NAME"].AsString();
ws.Cells[rowIndex, 6].SetValue(dr["T_30_RATE"]);
ws.Cells[rowIndex, 7].SetValue(dr["MGR2_DAY_RATE"]);
if (dr["MGR3_CUR_LEVEL"].AsString() == "Z")
{
RichTextString richText = new RichTextString();
richText.AddTextRun("從其高", new RichTextRunFont("標楷體", 12, Color.Red));
richText.AddTextRun("(" + (dr["MGR3_CUR_CM"].AsDecimal() * 100).AsString() + "%)", new RichTextRunFont("Times New Roman", 12, Color.Red));
ws.Cells[rowIndex, 8].SetRichText(richText);
}
else
{
RichTextString richText = new RichTextString();
richText.AddTextRun(dr["MGR3_CUR_LEVEL"].AsString(), new RichTextRunFont("Times New Roman", 12));
ws.Cells[rowIndex, 8].SetRichText(richText);
}
if (dr["DAY_CNT"].AsInt() == 0)
{
ws.Cells[rowIndex, 9].Value = "-";
}
else
{
ws.Cells[rowIndex, 9].SetValue(dr["DAY_CNT"]);
}
ws.Cells[rowIndex, 10].SetValue(dr["TFXM1_PRICE"]);
ws.Cells[rowIndex, 11].SetValue(dr["AI5_PRICE"]);
ws.Cells[rowIndex, 12].SetValue(dr["TS_UPDOWN"]);
ws.Cells[rowIndex, 13].SetValue(dr["TI_UPDOWN"]);
ws.Cells[rowIndex, 14].SetValue(dr["YS_UPDOWN"]);
ws.Cells[rowIndex, 15].SetValue(dr["YI_UPDOWN"]);
ws.Cells[rowIndex, 16].SetValue(dr["AI2_OI"]);
ws.Cells[rowIndex, 17].SetValue(dr["AI2_M_QNTY"]);
}//foreach (DataRow dr in dt42012.Rows)
//刪除空白列
if (dt42012.Rows.Count < totalRow)
{
range = ws.Range[(startRow + dt42012.Rows.Count + 1 + 1) + ":" + (startRow + totalRow + 1)];
range.Delete(DeleteMode.EntireRow);
minusRow = minusRow + (totalRow - dt42012.Rows.Count);
}
}
else
{
range = ws.Range[(startRow - 3 + 1) + ":" + (startRow + totalRow + 1)];
range.Delete(DeleteMode.EntireRow);
minusRow = minusRow + (totalRow + 4);
}
#endregion
#region 表2
startRow = 1018 - 1;
startRow = startRow - minusRow;
totalRow = 1000;
rowIndex = startRow;
if (cbx2.Checked)
{
if (txtRange.Text != _CON1 || txtRate2.Text != _CON2 || txtRate3.Text != _CON3 || txtRate4.Text != _CON4 || lblCmRate.Text != _CON5)
{
//表頭
RichTextString richText = new RichTextString();
richText = ws.Cells[rowIndex - 3, 2].GetRichText();
if (txtRange.Text != _CON1)
{
f = richText.Text.IndexOf(_CON1 + " %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 5).Text = txtRate1.Text + "%";
richText.Characters(f - 1, txtRate1.Text.Length).Font.Color = Color.Red;
}
}
if (txtRate2.Text != _CON2)
{
f = richText.Text.IndexOf(_CON2 + " %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 6).Text = txtRate2.Text + "%";
richText.Characters(f - 1, txtRate2.Text.Length).Font.Color = Color.Red;
}
}
if (txtRate3.Text != _CON3)
{
f = richText.Text.IndexOf(_CON3 + " %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 6).Text = txtRate3.Text + "%";
richText.Characters(f - 1, txtRate3.Text.Length).Font.Color = Color.Red;
}
}
if (txtRate4.Text != _CON4)
{
f = richText.Text.IndexOf(_CON4 + " %") + 1;
if (f > 0)
{
richText.Characters(f - 1, 5).Text = txtRate4.Text + "%";
richText.Characters(f - 1, txtRate4.Text.Length).Font.Color = Color.Red;
}
}
if (lblCmRate.Text != _CON5)
{
f = richText.Text.IndexOf(_CON5 + "%") + 1;
if (f > 0)
{
richText.Characters(f - 1, 5).Text = lblCmRate.Text + "%";
richText.Characters(f - 1, lblCmRate.Text.Length).Font.Color = Color.Red;
}
}
ws.Cells[rowIndex - 3, 2].SetRichText(richText);
if (!cbx1.Checked)
{
ws.Cells[rowIndex - 3, 1].Value = "1";
}
}
if (cbxRate.Checked)
{
DataView dv = dt42012.AsDataView();
dv.RowFilter = "(mgr2_level = 'Z' and mgr2_day_rate >= (MGR3_CUR_CM - " +
(txtRate4.Text.AsDecimal() / 100).AsString() + ")) or (mgr2_level = '1' and mgr2_day_rate >= " +
(txtRate1.Text.AsDecimal() / 100).AsString() + ") or (mgr2_level = '2' and mgr2_day_rate >= " +
(txtRate2.Text.AsDecimal() / 100).AsString() + ")";
dt42012 = dv.ToTable();
}
foreach (DataRow dr in dt42012.Rows)
{
rowIndex++;
ws.Cells[rowIndex, 1].Value = dr["MGR3_YMD"].AsDateTime("yyyyMMdd").ToString("yyyy/MM/dd");
ws.Cells[rowIndex, 2].Value = dr["MGR3_KIND_ID"].AsString();
ws.Cells[rowIndex, 3].Value = dr["APDK_NAME"].AsString();
ws.Cells[rowIndex, 4].Value = dr["MGR3_SID"].AsString();
ws.Cells[rowIndex, 5].Value = dr["PID_NAME"].AsString();
ws.Cells[rowIndex, 6].SetValue(dr["MGR2_DAY_RATE"]);
ws.Cells[rowIndex, 7].SetValue(dr["T_30_RATE"]);
if (dr["MGR3_CUR_LEVEL"].AsString() == "Z")
{
RichTextString richText = new RichTextString();
richText.AddTextRun("從其高", new RichTextRunFont("標楷體", 12, Color.Red));
richText.AddTextRun("(" + (dr["MGR3_CUR_CM"].AsDecimal() * 100).AsString() + "%)", new RichTextRunFont("Times New Roman", 12, Color.Red));
ws.Cells[rowIndex, 8].SetRichText(richText);
}
else
{
RichTextString richText = new RichTextString();
richText.AddTextRun(dr["MGR3_CUR_LEVEL"].AsString(), new RichTextRunFont("Times New Roman", 12));
ws.Cells[rowIndex, 8].SetRichText(richText);
}
if (dr["DAY_CNT_3"].AsInt() == 0)
{
ws.Cells[rowIndex, 9].Value = "-";
}
else
{
ws.Cells[rowIndex, 9].SetValue(dr["DAY_CNT_3"]);
}
ws.Cells[rowIndex, 10].SetValue(dr["TFXM1_PRICE"]);
ws.Cells[rowIndex, 11].SetValue(dr["AI5_PRICE"]);
ws.Cells[rowIndex, 12].SetValue(dr["TS_UPDOWN"]);
ws.Cells[rowIndex, 13].SetValue(dr["TI_UPDOWN"]);
ws.Cells[rowIndex, 14].SetValue(dr["YS_UPDOWN"]);
ws.Cells[rowIndex, 15].SetValue(dr["YI_UPDOWN"]);
ws.Cells[rowIndex, 16].SetValue(dr["AI2_OI"]);
ws.Cells[rowIndex, 17].SetValue(dr["AI2_M_QNTY"]);
}//foreach (DataRow dr in dt42012.Rows)
//刪除空白列
if (dt42012.Rows.Count < totalRow)
{
range = ws.Range[(startRow + dt42012.Rows.Count + 1 + 1) + ":" + (startRow + totalRow + 1)];
range.Delete(DeleteMode.EntireRow);
minusRow = minusRow + (totalRow - dt42012.Rows.Count);
}
}
else
{
range = ws.Range[(startRow - 3 + 1) + ":" + (startRow + totalRow + 1)];
range.Delete(DeleteMode.EntireRow);
minusRow = minusRow + (totalRow + 4);
}
#endregion
//存檔
ws.ScrollToRow(0);
workbook.SaveDocument(file);
ShowMsg("轉檔成功");
} catch (Exception ex) {
ShowMsg("轉檔錯誤");
MessageDisplay.Error("輸出錯誤");
throw ex;
} finally {
this.Cursor = Cursors.Arrow;
this.Refresh();
Thread.Sleep(5);
}
return(ResultStatus.Success);
}