protected override ResultStatus Export()
{
if (txtStartDate.DateTimeValue > txtEndDate.DateTimeValue)
{
MessageDisplay.Error("起始日期不得大於結束日期!");
return(ResultStatus.Fail);
}
ExportShow.Text = "轉檔中...";
ExportShow.Show();
try
{
base.Export();
string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID); //CopyExcelTemplateFile(_ProgramID, FileType.XLS);
return(ManipulateExcel(excelDestinationPath));
}
catch (Exception ex)
{
ExportShow.Text = "轉檔失敗";
WriteLog(ex);
return(ResultStatus.Fail);
}
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
string excelDestinationPath = "";
try
{
base.Export();
excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
flag = 0;
ManipulateExcel(excelDestinationPath);
if (flag == 0)
{
File.Delete(excelDestinationPath);
ExportShow.Text = "轉檔失敗";
return(ResultStatus.Fail);
}
}
catch (Exception ex)
{
File.Delete(excelDestinationPath);
ExportShow.Text = "轉檔失敗";
WriteLog(ex);
return(ResultStatus.Fail);
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
try
{
base.Export();
List <DbParameterEx> listParams = new List <DbParameterEx>();
DbParameterEx dbParaEx = new DbParameterEx("as_ym", txtMonth.FormatValue);
listParams.Add(dbParaEx);
//轉統計資料W_AM
ResultData resultData = new ServiceCommon().ExecuteStoredProcedure("ci.sp_H_stt_W_AM", listParams, true);
string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID); //CopyExcelTemplateFile(_ProgramID, FileType.XLS);
ManipulateExcel(excelDestinationPath);
}
catch (Exception ex)
{
ExportShow.Text = "轉檔失敗";
WriteLog(ex);
throw;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
try {
object[] args = { new D40xxx(), TxtDate, AdjType, _ProgramID };
IExport40xxxData xmlData = CreateXmlData(GetType(), "ExportXml" + AdjType, args);
ReturnMessageClass msg = xmlData.GetData();
//無資料時不產檔
if (msg.Status != ResultStatus.Success)
{
ExportShow.Text = MessageDisplay.MSG_IMPORT_FAIL;
MessageDisplay.Info($"{txtDate.DateTimeValue.ToShortDateString()},{_ProgramID}-{ddlAdjType.Properties.GetDisplayText(AdjType)},{MessageDisplay.MSG_NO_DATA}");
return(msg.Status);
}
msg = xmlData.Export();
if (msg.Status != ResultStatus.Success)
{
ExportShow.Text = MessageDisplay.MSG_IMPORT_FAIL;
MessageDisplay.Info(MessageDisplay.MSG_IMPORT_FAIL);
return(msg.Status);
}
} catch (Exception ex) {
ExportShow.Text = MessageDisplay.MSG_IMPORT_FAIL;
WriteLog(ex);
return(ResultStatus.Fail);
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
try {
Workbook workbook = new Workbook();
DataTable dtSP4 = new DataTable();
DataTable dtSP5 = new DataTable();
dtSP4 = daoS0020.GetSP4Data(txtDate.DateTimeValue.ToString("yyyy/MM/dd"));
dtSP5 = daoS0020.GetSP5Data(txtDate.DateTimeValue.ToString("yyyy/MM/dd"));
if (dtSP4.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info("無任何資料");
return(ResultStatus.Fail);
}
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
workbook.LoadDocument(destinationFilePath);
//將SP4資料放入Excel
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells[0, 3].Value = txtDate.DateTimeValue.ToString("yyyy/MM/dd");
for (int i = 0; i < dtSP4.Rows.Count; i++)
{
int row = dtSP4.Rows[i]["SP4_TYPE"].AsInt();
worksheet.Cells[row + 7, 1].Value = dtSP4.Rows[i]["sp4_span_cnt"].AsInt();
worksheet.Cells[row + 7, 2].Value = dtSP4.Rows[i]["sp4_mkt_cnt"].AsInt();
}
//將SP5資料放入Excel(◎尚未申報者:)
if (dtSP5.Rows.Count > 0)
{
//從Excel 第40列開始塞資料
for (int i = 39; i < dtSP5.Rows.Count + 39; i++)
{
worksheet.Cells[i, 0].Value = dtSP5.Rows[i - 39]["sp5_brk_no"].AsString() + '－' +
dtSP5.Rows[i - 39]["sp5_brk_abbr_name"].AsString();
}
}
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
DataTable dt = new DataTable();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);//Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, ls_filename);
DateTime date = txtDate.DateTimeValue;
int rowStart = 4, colStart = 0;
if (txt.EditValue == null)
{
MessageDisplay.Info("請輸入季度資訊 ! ");
ExportShow.Hide();
return(ResultStatus.FailButNext);
}
string yearQ = txt.EditValue.ToString();
try {
workbook.LoadDocument(destinationFilePath);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells[2, 0].Select();
worksheet.Cells[2, 0].Value = yearQ;
dt = dao35030.GetData(yearQ, date, date);
if (dt.Rows.Count <= 0)
{
ExportShow.Text = date.ToShortDateString() + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!";
return(ResultStatus.Fail);
}
worksheet.Import(dt, false, rowStart, colStart);
//刪除空白列
Range ra = worksheet.Range[(dt.Rows.Count + rowStart + 1).ToString() + ":1004"];
ra.Delete(DeleteMode.EntireRow);
workbook.SaveDocument(destinationFilePath);
}
catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
DataTable dt = new DataTable();
try
{
string ymd = txtDate.DateTimeValue.ToString("yyyyMMdd");
_Data = daoTPRICES_OPT.ListAllByDate(ymd);
if (_Data.Rows.Count <= 0)
{
MessageDisplay.Info(txtDate.FormatValue + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
ExportShow.Text = "轉檔失敗";
return(ResultStatus.Fail);
}
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
workbook.LoadDocument(destinationFilePath);
Worksheet worksheet = workbook.Worksheets[0];
//買權
dt = _Data.Filter("TRIM(TPRICES_PC_CODE) = 'C'");
ExportData(dt, worksheet);
worksheet = workbook.Worksheets[1];
//賣權
dt = _Data.Filter("TRIM(TPRICES_PC_CODE) = 'P'");
ExportData(dt, worksheet);
workbook.SaveDocument(destinationFilePath);
}
catch (Exception ex)
{
ExportShow.Text = "轉檔失敗";
WriteLog(ex);
return(ResultStatus.Fail);
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
FooterMemo = "";
string destinationFilePath = PbFunc.wf_GetFileSaveName(_ProgramID + "_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss"));
string txtFilePath = Path.Combine(GlobalInfo.DEFAULT_EXCEL_TEMPLATE_DIRECTORY_PATH, _ProgramID + ".txt");
DataTable dtSource = (DataTable)gcExport.DataSource;
try {
gvExport.Columns["PROD_ID"].SortOrder = DevExpress.Data.ColumnSortOrder.Ascending;
gcExport.ExportToXlsx(destinationFilePath);
Workbook workbook = new Workbook();
workbook.LoadDocument(destinationFilePath);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.ScrollTo(0, 0);
worksheet.Rows.Insert(0);
worksheet.Cells[0, 7].Value = labTime.Text;
using (TextReader tr = new StreamReader(txtFilePath, System.Text.Encoding.Default)) {
string line = "";
int startRow = dtSource.Rows.Count + 3;
while ((line = tr.ReadLine()) != null)
{
worksheet.Cells[startRow, 0].Value = line;
FooterMemo += line + Environment.NewLine;
startRow++;
}
}
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
WriteLog(ex);
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
if (txtStartDate.DateTimeValue > txtEndDate.DateTimeValue)
{
MessageDisplay.Error("起始日期不得大於結束日期!");
return(ResultStatus.Fail);
}
ExportShow.Text = "轉檔中...";
ExportShow.Show();
try
{
base.Export();
return(ManipulateExcel());
}
catch (Exception ex)
{
ExportShow.Text = "轉檔失敗";
WriteLog(ex);
return(ResultStatus.Fail);
}
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
DataTable dtTse = new DataTable();
DataTable dtTW50 = new DataTable();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);//Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, Filename);
DateTime date = txtDate.DateTimeValue;
int oleRow = 2, oleCol = 0;
try {
workbook.LoadDocument(destinationFilePath);
//Sheet TSE_OTC
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells[0, 0].Value = date.ToShortDateString();
dtTse = dao35050.GetTseOtcData(date);
worksheet.Import(dtTse, false, oleRow, oleCol);
//Sheet TW50
worksheet = workbook.Worksheets[1];
worksheet.Cells[0, 0].Value = date.ToShortDateString();
dtTW50 = dao35050.GetTW50Data(date);
worksheet.Import(dtTW50, false, oleRow, oleCol);
workbook.SaveDocument(destinationFilePath);
}
catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
string sYmd = txtSDate.DateTimeValue.ToString("yyyyMM");
string eYmd = txtEDate.DateTimeValue.ToString("yyyyMM");
int inputYear = txtEDate.DateTimeValue.Year - txtSDate.DateTimeValue.Year;
int oleRow = 2, rowStart = oleRow + 2, rowEnd = rowStart;
int rowTol = 0;
int colTot = dao30750.GetColTot();
try {
workbook.LoadDocument(destinationFilePath);
Worksheet worksheet = workbook.Worksheets[0];
//insert Row (按年分)
if (inputYear > 0)
{
for (int i = 1; i <= inputYear; i++)
{
rowEnd += 12;
//新增12筆空白列
for (int j = 1; j <= 12; j++)
{
worksheet.Rows[rowEnd - 1].Insert();
}
//將上面的12列 copy 過去
Range ra = worksheet.Range[(rowStart).ToString() + ":" + (rowStart + 11).ToString()];
worksheet.Rows[rowEnd - 1].CopyFrom(ra);
}
}
rowTol = rowStart + ((inputYear + 1) * 12);
DataTable dtAI2 = dao30750.GetAI2Data(sYmd, eYmd);
DataTable dtDayCount = dao30750.GetDayCount(sYmd, eYmd);
if (dtAI2.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info(sYmd + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
File.Delete(destinationFilePath);
return(ResultStatus.Fail);
}
//補上未選月
//if (txtSDate.DateTimeValue.Month != 1) {
// for (int i = 1; i <= txtSDate.DateTimeValue.Month - 1; i++) {
// DataRow addRow = dtDayCount.NewRow();
// addRow["ai2_ymd"] = txtSDate.DateTimeValue.Year.ToString() + i.ToString("D2");
// addRow["cp_day_count"] = 0;
// dtDayCount.Rows.Add(addRow);
// }
// dtDayCount = dtDayCount.Sort("AI2_YMD");
//}
string lsYear = "";
foreach (DataRow r in dtDayCount.Rows)
{
DateTime aiYm = r["ai2_ymd"].AsDateTime("yyyyMM");
TaiwanCalendar tai = new TaiwanCalendar();
oleRow += 1;
//清除未選月公式
if (r["ai2_ymd"].AsDateTime("yyyyMM") < txtSDate.DateTimeValue)
{
worksheet.Rows[oleRow].ClearContents();
}
if (lsYear != aiYm.Year.ToString())
{
if (aiYm.Month != 1)
{
for (int i = 1; i < aiYm.Month; i++)
{
worksheet.Cells[oleRow, 0].Value = tai.GetYear(aiYm).ToString();
worksheet.Cells[oleRow, 1].Value = i.ToString();
worksheet.Cells[oleRow, 2].Value = "";
oleRow++;
}
}
lsYear = aiYm.Year.ToString();
}
worksheet.Cells[oleRow, 0].Value = tai.GetYear(aiYm).ToString();
worksheet.Cells[oleRow, 1].Value = aiYm.Month.ToString();
worksheet.Cells[oleRow, 2].Value = r["cp_day_count"].ToString() != "0" ? r["cp_day_count"].ToString() : "";
//日均量總計
if (colTot > 0)
{
int cpMQnty = dtAI2.Compute("SUM(ai2_m_qnty)", "ai2_ymd=" + r["ai2_ymd"].ToString()).AsInt();
int cpDayCount = r["cp_day_count"].AsInt();
if (cpDayCount > 0)
{
worksheet.Cells[oleRow, colTot].Value = Math.Round((double)cpMQnty / cpDayCount, 0, MidpointRounding.AwayFromZero);
}
}
if (dtAI2.Select("ai2_ymd=" + r["ai2_ymd"].ToString()).Length != 0)
{
DataTable dtAI2ByYmd = dtAI2.Filter("ai2_ymd=" + r["ai2_ymd"].ToString());
foreach (DataRow row in dtAI2ByYmd.Rows)
{
//月總量
int col = row["rpt_seq_no"].AsInt();
if (col > 0)
{
worksheet.Cells[oleRow, col - 1].Value = row["ai2_m_qnty"].AsInt();
}
//日均量
int col2 = row["rpt_seq_no_2"].AsInt();
if (col2 > 0)
{
int ai2DayCount = row["ai2_day_count"].AsInt();
if (ai2DayCount > 0)
{
worksheet.Cells[oleRow, col2 - 1].Value = Math.Round((double)row["ai2_m_qnty"].AsInt() / ai2DayCount, 0, MidpointRounding.AwayFromZero);
}
}
}
}// if
}
//清除空白列
if (rowTol != oleRow + 2)
{
Range rowRange = worksheet.Range[(oleRow + 2).ToString() + ":" + (rowTol - 1).ToString()];
rowRange.ClearContents();
}
//總計
//insert Row (按年分)
if (inputYear >= 2)
{
rowStart = rowTol;
for (int i = 1; i <= inputYear - 1; i++)
{
worksheet.Rows[rowStart].Insert();
//將上面的總計列 copy 過去
Range ra = worksheet.Rows[rowStart - 1];
worksheet.Rows[rowStart].CopyFrom(ra);
rowStart += 1;
}
}
DataTable dtAI2Sum = dao30750.GetAI2Sum(sYmd, eYmd);
string ymd = "";
oleRow = rowTol - 2;
foreach (DataRow sumR in dtAI2Sum.Rows)
{
if (ymd != sumR["ai2_ymd"].ToString())
{
DateTime aiYm = sumR["ai2_ymd"].AsDateTime("yyyy");
TaiwanCalendar tai = new TaiwanCalendar();
oleRow += 1;
worksheet.Cells[oleRow, 0].Value = tai.GetYear(aiYm).ToString() + "年";
worksheet.Cells[oleRow, 2].Value = dtAI2Sum.Compute("MAX(ai2_day_count)", "ai2_ymd=" + sumR["ai2_ymd"].ToString()).AsInt();
ymd = sumR["ai2_ymd"].ToString();
}
//日均量總計
if (colTot > 0)
{
int cpMQnty = dtAI2Sum.Compute("SUM(ai2_m_qnty)", "ai2_ymd=" + sumR["ai2_ymd"].ToString()).AsInt();
int cpDayCount = dtAI2Sum.Compute("MAX(ai2_day_count)", "ai2_ymd=" + sumR["ai2_ymd"].ToString()).AsInt();
if (cpDayCount > 0)
{
worksheet.Cells[oleRow, colTot].Value = Math.Round((double)cpMQnty / cpDayCount, 0, MidpointRounding.AwayFromZero);
}
}
//月總量
int col = sumR["rpt_seq_no"].AsInt();
if (col > 0)
{
worksheet.Cells[oleRow, col - 1].Value = sumR["ai2_m_qnty"].AsInt();
}
//日均量
int col2 = sumR["rpt_seq_no_2"].AsInt();
if (col2 > 0)
{
int ai2DayCount = sumR["ai2_day_count"].AsInt();
if (ai2DayCount > 0)
{
worksheet.Cells[oleRow, col2 - 1].Value = Math.Round((double)sumR["ai2_m_qnty"].AsInt() / ai2DayCount, 0, MidpointRounding.AwayFromZero);
}
}
}
//刪除空白列
if (rowTol > oleRow)
{
Range ra = worksheet.Range[(oleRow + 2).ToString() + ":" + (rowTol + 2).ToString()];
ra.Delete(DeleteMode.EntireRow);
}
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
gvMain.CloseEditor();
gvMain.UpdateCurrentRow();
ExportShow.Text = "轉檔中...";
ExportShow.Show();
ResultStatus result = ResultStatus.Fail;
try {
DataTable exportDt = (DataTable)gcMain.DataSource;
exportDt = exportDt.Filter("RUN_FLAG ='Y'");
Workbook workbook = new Workbook();
//user 所選之要匯出商品
foreach (DataRow exportDr in exportDt.Rows)
{
int noData = 0;
//user 所選要匯出商品的 modelType
foreach (CheckedListBoxItem ch in ModelTypies.CheckedItems)
{
string kindId = exportDr["MG1_KIND_ID"].AsString();
string subType = exportDr["MG1_PROD_SUBTYPE"].AsString();
string prodType = exportDr["MG1_PROD_TYPE"].AsString();
string newFileName = _ProgramID + "(" + kindId + ")_" + ch.Description + "_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss") + ".xlsx";
int sheetIndex = prodType == "O" ? 2 : 0;
DataTable importData = dao40041.GetExportData(kindId, exportDr["DATA_SDATE"].AsDateTime(), txtDate.DateTimeValue, ch.Value.ToString());
DataTable accountingData = dao40041.GetExportData(kindId, exportDr["MG1_SDATE"].AsDateTime(), exportDr["MG1_SDATE"].AsDateTime(), ch.Value.ToString());
if (importData == null)
{
MessageDisplay.Info($"({kindId }_{ch.Description})資料不足2筆，無法產出報表!");
result = ResultStatus.FailButNext;
noData++;
continue;
}
if (importData.Rows.Count < 2)
{
MessageDisplay.Info($"({kindId }_{ch.Description})資料不足2筆，無法產出報表!");
result = ResultStatus.FailButNext;
noData++;
continue;
}
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID, newFileName);
destinationFilePath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, destinationFilePath);
workbook.LoadDocument(destinationFilePath);
#region Write Data
Worksheet worksheet = workbook.Worksheets[sheetIndex];
worksheet.Cells[2, 2].Value = prodType == "O" ? kindId : kindId + "結算價";
worksheet.Cells[0, 1].Value = accountingData.Rows[0]["MG1_DATE"].AsDateTime();
for (int i = 2; i <= accountingData.Columns.Count - 1; i++)
{
worksheet.Cells[0, i].Value = accountingData.Rows[0][i].AsDecimal();
}
worksheet.Import(importData, false, 3, 0);
//delete empty Rows
Range emptyRa = worksheet.Range[(importData.Rows.Count + 4).ToString() + ":1003"];
emptyRa.Delete(DeleteMode.EntireRow);
#endregion
#region Gen Figure 重組圖表資料
sheetIndex = prodType == "O" ? 3 : 1;
worksheet = workbook.Worksheets[sheetIndex];
if (subType != "S")
{
Range ra = worksheet.Range["4:5"];
ra.Delete(DeleteMode.EntireRow);
}
else
{
worksheet.Cells[4, 1].Value = exportDr["APDK_NAME"].AsString();
worksheet.Cells[4, 2].Value = exportDr["APDK_STOCK_ID"].AsString();
worksheet.Cells[4, 3].Value = exportDr["PID_NAME"].AsString();
Range ra = worksheet.Range["2:3"];
ra.Delete(DeleteMode.EntireRow);
}
//表頭日期
worksheet.Cells[3, 8].Value = importData.Rows[importData.Rows.Count - 1]["MG1_DATE"].AsDateTime();
//填寫圖表資料來源
int count = 2;
for (int f = 7; f >= 6; f--)
{
DataRow dataRow = importData.Rows[importData.Rows.Count - count];
count--;
worksheet.Cells[f, 1].Value = dataRow[3].AsDecimal();
worksheet.Cells[f, 3].Value = dataRow[2].AsDecimal();
worksheet.Cells[f, 5].Value = dataRow[4].AsDecimal();
worksheet.Cells[f, 6].Value = dataRow[5].AsDecimal();
worksheet.Cells[f, 7].Value = dataRow[6].AsDecimal();
worksheet.Cells[f, 8].Value = dataRow[7].AsDecimal();
worksheet.Cells[f, 9].Value = dataRow[8].AsDecimal();
}
#endregion
workbook.SaveDocument(destinationFilePath);
result = ResultStatus.Success;
}
//如果不相等, 表示並非所有參數都無資料
if (noData != ModelTypies.CheckedItems.Count)
{
result = ResultStatus.Success;
}
}
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
WriteLog(ex);
}
ExportShow.Text = "轉檔成功!";
return(result);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
try {
Workbook workbook = new Workbook();
DataTable dt = new DataTable();
string kindId = "TXF";
DateTime sdate = PbFunc.f_get_last_day("AI3", kindId, txtDate.DateTimeValue.ToString("yyyy/MM"), 1);
DateTime edate = PbFunc.f_get_end_day("AI3", kindId, txtDate.DateTimeValue.ToString("yyyy/MM"));
dt = daoAI3.ListAI3(kindId, sdate, edate);
//查無資料時不產檔
if (dt.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info(sdate + "~" + edate + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
return(ResultStatus.Fail);
}
//複製Template
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
workbook.LoadDocument(destinationFilePath);
//切換sheet
Worksheet worksheet = workbook.Worksheets["30711"];
DateTime ldt_ymd = new DateTime(1900, 1, 1);
int row_tol = 33;
//寫入資料
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (ldt_ymd != Convert.ToDateTime(dt.Rows[i]["ai3_date"]))
{
ldt_ymd = Convert.ToDateTime(dt.Rows[i]["ai3_date"]);
worksheet.Cells[i + 1, 0].Value = ldt_ymd;
}
worksheet.Cells[i + 1, 1].Value = float.Parse(dt.Rows[i]["ai3_index"].ToString());
worksheet.Cells[i + 1, 2].Value = float.Parse(dt.Rows[i]["ai3_close_price"].ToString());
}
}
//刪除空白列
if (row_tol > dt.Rows.Count)
{
Range ra = worksheet.Range[(dt.Rows.Count + 2).ToString() + ":" + row_tol.ToString()];
ra.Delete(DeleteMode.EntireRow);
}
//圖表重選範圍
ChartObject chartObjs = workbook.ChartSheets[0].Chart;
chartObjs.Series[0].SeriesName.SetValue("近月份期貨契約指數");
ChartData closePrice = new ChartData();
closePrice.RangeValue = worksheet.Range["C2:C" + (dt.Rows.Count + 1).ToString()];
chartObjs.Series[0].Values = closePrice;
ChartData index = new ChartData();
index.RangeValue = worksheet.Range["B2:B" + (dt.Rows.Count + 1).ToString()];
chartObjs.Series[1].Values = index;
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
DataTable dtYearData = new DataTable();
DataTable dtMontData = new DataTable();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
string inputMonth = txtDate.DateTimeValue.ToString("yyyyMM");
string inputYear = txtDate.DateTimeValue.Year.ToString();
try {
workbook.LoadDocument(destinationFilePath);
Worksheet worksheet = workbook.Worksheets[0];
string startYear = worksheet.Cells[2, 1].Value.ToString();
int rowTol = worksheet.Cells[2, 0].Value.AsInt();
List <int> ListBIndex = new List <int>();
int rowStart = 4;
for (int i = 1; i <= idfgCount; i++)
{
rowStart = 4;
int bIndex = GetBIndex(i, ListBIndex);
int ymd = 0;
ListBIndex.Add(bIndex);
if (i == 6)
{
i = 7;// idfgtype 跳過6
}
dtYearData = dao30530.ListYearData(startYear, inputYear, inputYear + "01", inputMonth, i.ToString(), bIndex.ToString(), (bIndex + 1).ToString());
dtMontData = dao30530.ListMonthData(inputYear + "01", inputMonth, i.ToString(), bIndex.ToString(), (bIndex + 1).ToString());
if (dtYearData.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info(inputMonth + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
File.Delete(destinationFilePath);
return(ResultStatus.Fail);
}
// 年分
for (int j = startYear.AsInt(); j <= inputYear.AsInt(); j++)
{
DataTable dtYear = dtYearData.Filter("AM2_YMD = " + j.ToString());
if (ymd != j)
{
//有資料時才增加列數
if (dtYear.Rows.Count > 0)
{
rowStart++;
}
ymd = j;
}
foreach (DataRow dr in dtYear.Rows)
{
worksheet.Cells[rowStart, 0].Value = ymd;
worksheet.Cells[rowStart, dr["BS_Index"].AsInt()].Value = dr["am2_m_qnty"].AsDecimal();
}
}
//每月
for (int j = (inputYear.ToString() + "01").AsInt(); j <= inputMonth.AsInt(); j++)
{
DataTable dtMon = dtMontData.Filter("AM2_YMD = " + j.ToString());
if (ymd != j)
{
//有資料時才增加列數
if (dtMon.Rows.Count > 0)
{
rowStart++;
}
ymd = j;
}
foreach (DataRow dr in dtMon.Rows)
{
worksheet.Cells[rowStart, 0].Value = dr["am2_ymd"].AsDateTime("yyyyMM").ToString("MMM", CultureInfo.CreateSpecificCulture("en-US")) + ".";
worksheet.Cells[rowStart, dr["BS_Index"].AsInt()].Value = dr["am2_m_qnty"].AsDecimal();
}
}
}
if (rowTol < 38)
{
Range ra = worksheet.Range[(rowStart + 2).ToString() + ":39"];
ra.Delete(DeleteMode.EntireRow);
}
worksheet.ScrollToRow(0);
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
try {
Workbook workbook = new Workbook();
DataTable dtAM6 = new DataTable();
DataTable dtAM0 = new DataTable();
DateTime date = txtDate.DateTimeValue;
//交易輔助人
dtAM6 = dao30730.GetAM6Data(date.ToString("yyyyMM"));
dtAM0 = dao30730.GetAM0Data(date.ToString("yyyyMM"));
if (dtAM6.Rows.Count <= 0 && dtAM0.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info(date + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
return(ResultStatus.Fail);
}
if (dtAM6.Rows.Count <= 0)
{
dtAM6.Rows.Add();
dtAM6.Rows[0]["am6_ym"] = date.ToString("yyyyMM");
dtAM6.Rows[0]["am6_trade_aux"] = 0;
}
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
int oleRow = 1;
workbook.LoadDocument(destinationFilePath);
#region Export
foreach (DataRow r in dtAM0.Rows)
{
int v = r["am0_m_qnty"].AsInt();
string brkNo = r["am0_brk_no"].AsString();
//判斷是否是期貨
if (brkNo == "F")
{
if (r["am0_brk_type"].ToString() == "9")
{
dtAM6.Rows[0]["am6_f999"] = v;
}
else
{
dtAM6.Rows[0]["am6_f000"] = v;
}
}
else
{
if (r["am0_brk_type"].ToString() == "9")
{
dtAM6.Rows[0]["am6_999"] = v;
}
else
{
dtAM6.Rows[0]["am6_000"] = v;
}
}
}
dtAM6.AcceptChanges();
Worksheet worksheet = workbook.Worksheets["30731"];
//寫入資料
dtAM6.Columns.Remove("AM6_YM");
worksheet.Import(dtAM6, false, oleRow, 0);
worksheet = workbook.Worksheets["30730"];
worksheet.Range["A1"].Select();
worksheet.Cells[0, 1].Value = "期貨商及交易輔助人" + date.Year.ToString() + "年" + date.Month.ToString() + "月份期貨交易量統計表";
#endregion
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
string sYm = txtSDate.DateTimeValue.ToString("yyyyMM");
string eYm = txtEDate.DateTimeValue.ToString("yyyyMM");
string eymd = string.IsNullOrEmpty(dao30760.GetMaxDate(eYm)) ? eYm + "01" : dao30760.GetMaxDate(eYm);//讀取迄年月的最大之交易日
int oleRow = 5, oleCol = 0;
try {
workbook.LoadDocument(destinationFilePath);
int noData = 0;
//各產品資料
Worksheet worksheet = workbook.Worksheets[0];
DataTable dtProdData = dao30760.GetProdData(sYm, eYm, eymd);
DataTable dtSum = dao30760.GetSumData(sYm, eYm, eymd);
if (dtProdData.Rows.Count <= 0)
{
ExportShow.Text = "各產品無任何資料!";
noData += 1;
}
ProdData(worksheet, dtProdData, dtSum, oleRow, oleCol);
//個股選擇權資料
worksheet = workbook.Worksheets[1];
DataTable dtTradedData = dao30760.GetTradedData(sYm, eYm, eymd, "O");
if (dtTradedData.Rows.Count <= 0)
{
ExportShow.Text = "個股選擇權無任何資料!";
noData += 1;
}
oleRow = 5;
TradedData(worksheet, dtTradedData, oleRow, oleCol);
//個股期貨資料
worksheet = workbook.Worksheets[2];
DataTable dtFuturesData = dao30760.GetFuturesData(sYm, eYm, eymd, "F");
if (dtFuturesData.Rows.Count <= 0)
{
ExportShow.Text = "個股期貨無任何資料!";
noData += 1;
}
oleRow = 5;
FuturesData(worksheet, dtFuturesData, oleRow, oleCol);
//Save
workbook.SaveDocument(destinationFilePath);
//完全無資料時, 刪檔
if (noData == 3)
{
File.Delete(destinationFilePath);
return(ResultStatus.FailButNext);
}
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
DataTable dtAdd = new DataTable();
DataTable dtSub = new DataTable();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);//Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, ls_filename);
DateTime date = txtDate.DateTimeValue;
string yearQ = "";
int sheetIndex = exportType.EditValue.AsInt(), rowStart = 6, colStart = 0, rowEnd = 0;
int sheetType = exportType.EditValue.AsInt();
if (txt.EditValue == null)
{
MessageDisplay.Info("請輸入季度資訊 ! ");
ExportShow.Hide();
return(ResultStatus.FailButNext);
}
try {
workbook.LoadDocument(destinationFilePath);
Worksheet worksheet = workbook.Worksheets[sheetType];
yearQ = txt.EditValue.ToString();
dtAdd = dao35020.GenAddReport(yearQ, date, date);
if (dtAdd.Rows.Count <= 0)
{
ExportShow.Text = date.ToShortDateString() + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!";
return(ResultStatus.Fail);
}
dtSub = dao35020.GenSubReport(yearQ, date, date);
if (dtSub.Rows.Count <= 0)
{
ExportShow.Text = date.ToShortDateString() + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!";
return(ResultStatus.Fail);
}
worksheet.Cells[2, 0].Value = worksheet.Cells[2, 0].Value + txt.EditValue.ToString();
switch (sheetType)
{
case 0: {
//新增部分報表
worksheet.Import(dtAdd, false, rowStart, colStart);
worksheet.Import(dtSub, false, rowStart, colStart + 2);
rowEnd = Math.Max(dtAdd.Rows.Count, dtSub.Rows.Count);
break;
}
case 1: {
//減少部分報表
worksheet.Import(dtAdd, false, rowStart, colStart);
rowEnd = dtAdd.Rows.Count;
break;
}
case 2: {
//減少部分報表
worksheet.Import(dtSub, false, rowStart, colStart);
rowEnd = dtSub.Rows.Count;
break;
}
}
//刪除空白列
Range ra = worksheet.Range[(rowEnd + rowStart + 1).ToString() + ":250"];
ra.Delete(DeleteMode.EntireRow);
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
DataTable dtAM2 = new DataTable();
DataTable dtRPT = new DataTable();
DataTable dtAi2 = new DataTable();
DataTable dtRamm1 = new DataTable();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
DateTime date = txtDate.DateTimeValue;
string asParamKey = "TXO";
int oleRow = 1;
try {
int noData = 0;
workbook.LoadDocument(destinationFilePath);
#region Get AM2 Data
dtAM2 = daoAM2.ListAm2DataByYmd(date.ToString("yyyyMM"), asParamKey);
if (dtAM2.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info(date + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
noData += 1;
File.Delete(destinationFilePath);
return(ResultStatus.Fail);
}
#endregion
Worksheet worksheet = workbook.Worksheets["30720"];
#region Get RPT
dtRPT = daoRPT.ListData("%" + _ProgramID + "%");
if (dtRPT.Rows.Count <= 0)
{
MessageDisplay.Info(_ProgramID + '－' + "RPT無任何資料!");
noData += 1;
}
//填寫日期
TaiwanCalendar tai = new TaiwanCalendar();
worksheet.Cells[0, 6].Value = tai.GetYear(date).ToString() + "年" + tai.GetMonth(date) + "月" + worksheet.Cells[0, 6].Value;
worksheet.Cells[1, 4].Value = date.ToString("MMM", CultureInfo.CreateSpecificCulture("en-US")) + "." + date.Year.ToString() + worksheet.Cells[1, 4].Value;
string paramKey = "";
foreach (DataRow r in dtAM2.Rows)
{
if (paramKey != (r["am2_param_key"].ToString().Trim()))
{
paramKey = r["am2_param_key"].ToString().Trim();
oleRow = dtRPT.Rows.IndexOf(dtRPT.Select("trim(rpt_value) = '" + paramKey + "'")[0]);
if (oleRow >= 0)
{
oleRow = dtRPT.Rows[oleRow]["rpt_seq_no"].AsInt();
}
}
int oleCol = GetCol(r["am2_idfg_type"].AsInt(), r["am2_bs_code"].AsString());
if (oleRow > 0 && oleCol > 0)
{
worksheet.Cells[oleRow - 1, oleCol - 1].Value = r["am2_m_qnty"].AsInt();
}
}
#endregion
#region Get AI2
dtAi2 = daoAI2.ListAI2ByYmd(date.ToString("yyyyMM"), date.ToString("yyyyMM"), asParamKey);
if (dtAi2.Rows.Count <= 0)
{
MessageDisplay.Info(_ProgramID + '－' + "AI2無任何資料!");
noData += 1;
}
//切換sheet
paramKey = "";
foreach (DataRow r in dtAi2.Rows)
{
if (paramKey != (r["ai2_param_key"].ToString().Trim()))
{
paramKey = r["ai2_param_key"].ToString().Trim();
oleRow = dtRPT.Rows.IndexOf(dtRPT.Select("trim(rpt_value) = '" + paramKey + "'")[0]);
if (oleRow >= 0)
{
oleRow = dtRPT.Rows[oleRow]["rpt_seq_no"].AsInt();
}
}
if (oleRow > 0)
{
worksheet.Cells[oleRow - 1, 2].Value = r["ai2_m_qnty"].AsInt();
worksheet.Cells[oleRow - 1, 3].Value = r["ai2_oi"].AsInt();
}
}
#endregion
#region Get Ramm1
dtRamm1 = daoRamm1.ListRamm1Ymd(date.ToString("yyyyMM") + "01", date.ToString("yyyyMM") + "31", asParamKey);
if (dtRamm1.Rows.Count <= 0)
{
MessageDisplay.Info(_ProgramID + '－' + "Ramm1無任何資料!");
noData += 1;
}
paramKey = "";
foreach (DataRow r in dtRamm1.Rows)
{
if (paramKey != (r["param_key"].ToString().Trim()))
{
paramKey = r["param_key"].ToString().Trim();
oleRow = dtRPT.Rows.IndexOf(dtRPT.Select("trim(rpt_value) = '" + paramKey + "'")[0]);
if (oleRow >= 0)
{
oleRow = dtRPT.Rows[oleRow]["rpt_seq_no"].AsInt();
}
}
if (oleRow > 0)
{
worksheet.Cells[oleRow - 1, 16].Value = r["bo"].AsInt();
worksheet.Cells[oleRow - 1, 17].Value = r["bq"].AsInt();
worksheet.Cells[oleRow - 1, 18].Value = r["so"].AsInt();
worksheet.Cells[oleRow - 1, 19].Value = r["sq"].AsInt();
}
}
#endregion
workbook.SaveDocument(destinationFilePath);
//全查無資料時, 刪除檔案
if (noData == 4)
{
File.Delete(destinationFilePath);
}
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
DataTable dtAM1 = new DataTable();
DataTable dtAM4 = new DataTable();
DateTime date = txtDate.DateTimeValue;
try {
//市場總成交量雙邊(A)
dtAM1 = dao30740.GetAM1Data(date);
if (dtAM1.Rows.Count <= 0)
{
MessageDisplay.Info(date.ToString("yyyyMM") + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
}
//網路下單
dtAM4 = dao30740.GetAM4Data(date);
if (dtAM4.Rows.Count <= 0)
{
MessageDisplay.Info(date.ToString("yyyyMM") + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
ExportShow.Text = "轉檔失敗";
return(ResultStatus.Fail);
}
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
int startRow = 6, oleRow = startRow, oleTol = startRow + 24;
string ymd = "";
workbook.LoadDocument(destinationFilePath);
Worksheet worksheet = workbook.Worksheets[0];
TaiwanCalendar tai = new TaiwanCalendar();
worksheet.Cells[oleRow, 0].Value = tai.GetYear(date).ToString();
foreach (DataRow r in dtAM1.Rows)
{
if (ymd != r["am1_ymd"].ToString())
{
oleRow += 2;
DateTime am1Ymd = r["am1_ymd"].AsDateTime("yyyyMM");
worksheet.Cells[oleRow, 0].Value = am1Ymd.Month.ToString();
ymd = r["am1_ymd"].ToString();
}
if (r["am1_prod_type"].ToString() == "F")
{
worksheet.Cells[oleRow, 1].Value = r["am1_m_qnty"].AsInt();
}
else
{
worksheet.Cells[oleRow + 1, 1].Value = r["am1_m_qnty"].AsInt();
}
}
//刪除空白列
if (oleTol > oleRow)
{
Range ra = worksheet.Range[(oleRow + 3).ToString() + ":32"];
ra.Delete(DeleteMode.EntireRow);
}
ymd = "";
oleRow = startRow;
foreach (DataRow r in dtAM4.Rows)
{
if (ymd != r["am4_ym"].ToString())
{
oleRow += 2;
ymd = r["am4_ym"].ToString();
}
worksheet.Cells[oleRow, 3].Value = r["am4_f_qnty"].AsInt();
worksheet.Cells[oleRow, 5].Value = r["am4_o_qnty"].AsInt();
worksheet.Cells[oleRow, 8].Value = r["am4_trade_count"].AsInt();
}
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
WriteLog(ex);
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}