private void InitRichEditControl()
{
using (new UsingProcessor(() => Editor.BeginUpdate(), () => Editor.EndUpdate()))
{
if (cell.HasRichText)
{
RichTextString richText = cell.GetRichText();
Document document = Editor.Document;
foreach (RichTextRun run in richText.Runs)
{
DocumentRange range = document.InsertText(document.Range.End, run.Text);
CharacterProperties cp = document.BeginUpdateCharacters(range);
try
{
cp.Bold = run.Font.Bold;
cp.ForeColor = run.Font.Color;
cp.Italic = run.Font.Italic;
cp.FontName = run.Font.Name;
cp.FontSize = (float)run.Font.Size;
cp.Strikeout = run.Font.Strikethrough ? StrikeoutType.Single : StrikeoutType.None;
switch (run.Font.Script)
{
case ScriptType.Subscript:
cp.Subscript = true;
break;
case ScriptType.Superscript:
cp.Superscript = true;
break;
default:
cp.Subscript = false;
cp.Superscript = false;
break;
}
cp.Underline = run.Font.UnderlineType switch
{
DevExpress.Spreadsheet.UnderlineType.Single => DevExpress.XtraRichEdit.API.Native.UnderlineType.Single,
DevExpress.Spreadsheet.UnderlineType.Double => DevExpress.XtraRichEdit.API.Native.UnderlineType.Double,
_ => DevExpress.XtraRichEdit.API.Native.UnderlineType.None
};
}
finally
{
document.EndUpdateCharacters(cp);
}
}
}
else
{
Editor.Text = cell.DisplayText;
}
}
}
private void OKButton_Click(object sender, EventArgs e)
{
var visitor = new SpreadRichDocumentVisitor(Editor.Document.Range.End.ToInt());
using (DocumentIterator iterator = new DocumentIterator(Editor.Document, true))
{
while (iterator.MoveNext())
{
iterator.Current.Accept(visitor);
}
}
RichTextString richText = visitor.RichText;
cell.SetRichText(richText);
if (Editor.Document.Paragraphs.Count > 1)
{
cell.Alignment.WrapText = true;
}
}
private static void ProcessFontRuns(RecordInputStream in1, RichTextString str,
int formattingRunDataLength)
{
if (formattingRunDataLength % FORMAT_RUN_ENCODED_SIZE != 0)
{
throw new RecordFormatException("Bad format run data length " + formattingRunDataLength
+ ")");
}
if (in1.Remaining != formattingRunDataLength)
{
throw new RecordFormatException("Expected " + formattingRunDataLength
+ " bytes but got " + in1.Remaining);
}
int nRuns = formattingRunDataLength / FORMAT_RUN_ENCODED_SIZE;
for (int i = 0; i < nRuns; i++)
{
short index = in1.ReadShort();
short iFont = in1.ReadShort();
in1.ReadInt(); // skip reserved.
str.ApplyFont(index, str.Length, iFont);
}
}
private static byte[] CreateFormatData(RichTextString str)
{
int nRuns = str.NumFormattingRuns;
byte[] result = new byte[(nRuns + 1) * FORMAT_RUN_ENCODED_SIZE];
int pos = 0;
for (int i = 0; i < nRuns; i++)
{
LittleEndian.PutUShort(result, pos, str.GetIndexOfFormattingRun(i));
pos += 2;
int fontIndex = ((HSSFRichTextString)str).GetFontOfFormattingRun(i);
LittleEndian.PutUShort(result, pos, fontIndex == HSSFRichTextString.NO_FONT ? 0 : fontIndex);
pos += 2;
pos += 4; // skip reserved
}
LittleEndian.PutUShort(result, pos, str.Length);
pos += 2;
LittleEndian.PutUShort(result, pos, 0);
pos += 2;
pos += 4; // skip reserved
return(result);
}
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 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);
}
void OnGUI()
{
// 准星贴图
Rect rect = new Rect(Input.mousePosition.x - (FrontSightTex.width / 2),
Screen.height - Input.mousePosition.y - (FrontSightTex.height / 2),
FrontSightTex.width,
FrontSightTex.height);
GUI.DrawTexture(rect, FrontSightTex);
// 总血条贴图
GUI.DrawTexture(new Rect(0, 0, BloodBgTex.width, BloodBgTex.height), BloodBgTex);
GUI.DrawTexture(new Rect(0, 0, BloodTex.width * (_data.Health * 0.01f), BloodTex.height), BloodTex);
// 血量全屏贴图
Color alpha = GUI.color;
HealthAlpha = (100.0f - _data.Health) / 120.0f;
if (HealthAlpha < 0.42)
{
HealthAlpha = 0;
}
alpha.a = HealthAlpha;
GUI.color = alpha;
GUI.DrawTexture(new Rect(0, 0, Screen.width, Screen.height), HealthWarnTex);
alpha.a = 1.0f;
GUI.color = alpha;
GUI.skin = Skin;
RichTextString rts = new RichTextString();
GUILayout.BeginArea(new Rect(0, 0, Screen.width, Screen.height));
GUILayout.FlexibleSpace();
GUILayout.BeginHorizontal();
GUILayout.FlexibleSpace();
GUILayout.Label(HealthTex);
rts.color = _data.HealthColor(NormalColor, WarningColor, DangerousColor);
rts.Text = _data.Health.ToString();
GUILayout.Label(rts);
GUILayout.FlexibleSpace();
GUILayout.Label(DefenseTex);
rts.color = _data.DefenseColor(NormalColor, WarningColor, DangerousColor);
rts.Text = _data.Defense.ToString();
GUILayout.Label(rts);
GUILayout.FlexibleSpace();
GUILayout.Label(TimeTex);
rts.color = _data.SecondColor(NormalColor, WarningColor, DangerousColor);
rts.Text = string.Format("{0:D}:{1:D2}", _data.Second / 60, _data.Second % 60);
GUILayout.Label(rts);
GUILayout.FlexibleSpace();
GUILayout.Label(BulletTex);
rts.color = _data.BulletColor(NormalColor, WarningColor, DangerousColor);
rts.Text = _data.Bullet.ToString();
GUILayout.Label(rts);
GUILayout.FlexibleSpace();
GUILayout.Label(MoneyTex);
rts.color = NormalColor;
rts.Text = _data.Money.ToString();
GUILayout.Label(rts);
GUILayout.FlexibleSpace();
GUILayout.EndHorizontal();
GUILayout.EndArea();
}
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);
}
public RichTextString tietToRich(TietHoc element, string type, int fontSize)
{
RichTextString richText = new RichTextString();
if (element.diaDiem != null && element.diaDiem != "" && element.diaDiem != Lop.giangDuong.ToString())
{
RichTextRunFont font = new RichTextRunFont("Times New Roman", size - 2);
font.Script = ScriptType.Superscript;
font.Bold = true;
richText.AddTextRun(element.diaDiem, font);
richText.AddTextRun(" ", new RichTextRunFont("Times New Roman", 2));
}
RichTextRunFont font2;
if (element.mon.ten.Contains("|"))
{ //Nếu là dạng môn có mã phức tạp
string[] arr = element.mon.ten.Split(new char[] { '|' });
string[] maMon = arr[1].Split(new char[] { '_' });
foreach (string ma in maMon)
{
if (ma[0] == '^')
{
font2 = new RichTextRunFont("Times New Roman", size - 2);
font2.Script = ScriptType.None;
}
else
{
font2 = new RichTextRunFont("Times New Roman", size - 2);
font2.Script = ScriptType.Subscript;
}
font2.Bold = true;
richText.AddTextRun(ma.Substring(1), font2);
}
}
else
{ //Nếu là dạng môn có mã mặc định
font2 = new RichTextRunFont("Times New Roman", size);
font2.Script = ScriptType.None;
font2.Bold = true;
richText.AddTextRun(element.mon.ma, font2);
}
//Thêm kc ngắn sau mã môn
richText.AddTextRun(" ", new RichTextRunFont("Times New Roman", 2));
if (element.bai != "" && element.bai != null && type != "tuan" && type != "ngay")
{
RichTextRunFont font3 = new RichTextRunFont("Times New Roman", size - 2);
font3.Script = ScriptType.Superscript;
font3.Bold = true;
richText.AddTextRun(element.bai, font3);
richText.AddTextRun(" ", new RichTextRunFont("Times New Roman", 2));
}
if (element.hinhThuc != "" && element.hinhThuc != "LT" && element.hinhThuc != null)
{
RichTextRunFont font4 = new RichTextRunFont("Times New Roman", size - 2);
font4.Bold = true;
if (element.hinhThuc == "+" || element.hinhThuc == ".")
{
if (element.hinhThuc == ".")
{
font4.Size = 13;
}
else
{
font4.Size = 9;
}
}
else
{
font4.Script = ScriptType.Subscript;
}
richText.AddTextRun(element.hinhThuc, font4);
}
return(richText);
}
public ExcelHelper SetCellValue(RichTextString val)
{
_cell.SetCellValue(val);
return this;
}
public RichTextString(RichTextString rts)
{
Text = rts;
color = Color.white;
}
public SpreadRichDocumentVisitor(int endPos)
{
richTextString = new RichTextString();
endPosition = endPos;
}
public ExcelHelper SetCellValue(RichTextString val)
{
_cell.SetCellValue(val);
return(this);
}
private static byte[] CreateFormatData(RichTextString str)
{
int nRuns = str.NumFormattingRuns;
byte[] result = new byte[(nRuns + 1) * FORMAT_RUN_ENCODED_SIZE];
int pos = 0;
for (int i = 0; i < nRuns; i++)
{
LittleEndian.PutUShort(result, pos, str.GetIndexOfFormattingRun(i));
pos += 2;
int fontIndex = ((HSSFRichTextString)str).GetFontOfFormattingRun(i);
LittleEndian.PutUShort(result, pos, fontIndex == HSSFRichTextString.NO_FONT ? 0 : fontIndex);
pos += 2;
pos += 4; // skip reserved
}
LittleEndian.PutUShort(result, pos, str.Length);
pos += 2;
LittleEndian.PutUShort(result, pos, 0);
pos += 2;
pos += 4; // skip reserved
return result;
}
private static void ProcessFontRuns(RecordInputStream in1, RichTextString str,
int formattingRunDataLength)
{
if (formattingRunDataLength % FORMAT_RUN_ENCODED_SIZE != 0)
{
throw new RecordFormatException("Bad format run data length " + formattingRunDataLength
+ ")");
}
if (in1.Remaining != formattingRunDataLength)
{
throw new RecordFormatException("Expected " + formattingRunDataLength
+ " bytes but got " + in1.Remaining);
}
int nRuns = formattingRunDataLength / FORMAT_RUN_ENCODED_SIZE;
for (int i = 0; i < nRuns; i++)
{
short index = in1.ReadShort();
short iFont = in1.ReadShort();
in1.ReadInt(); // skip reserved.
str.ApplyFont(index, str.Length, iFont);
}
}