/// <summary> /// /// </summary> /// <param name="FilePath">Excel_Template</param> /// <param name="datetime">em_month.Text</param> public B30398(Workbook Workbook, string datetime) { _workbook = Workbook; _emMonthText = datetime; daoAI3 = new AI3(); daoAM2 = new AM2(); }
public IList search(Int32 yearid, Int64 GrNo, DateTime?dtfrom, DateTime?dtto, int FromCity, Int32 SenderIdno) { using (TransportMandiEntities db = new TransportMandiEntities(MultipleDBDAL.strDynamicConString())) { var lst = (from CH in db.tblChlnAmntPayment_Head //join cifrom in db.tblCityMasters on CH.BaseCity_Idno equals cifrom.City_Idno join cito in db.tblCityMasters on CH.Loc_Idno equals cito.City_Idno join AM in db.AcntMasts on CH.Party_IdNo equals AM.Acnt_Idno join Am1 in db.AcntMasts on CH.RcptType_Idno equals Am1.Acnt_Idno into AM2 from Am3 in AM2.DefaultIfEmpty() select new { CH.BaseCity_Idno, CH.Rcpt_date, CH.Rcpt_No, CH.Id, CH.Inst_Dt, CH.Inst_No, FromCity = cito.City_Name, CH.Year_IdNo, AM.Acnt_Name, AM.Acnt_Idno, ReciptName = Am3.Acnt_Name, CH.Net_Amnt }).ToList(); if (GrNo > 0) { lst = lst.Where(l => l.Rcpt_No == GrNo).ToList(); } if (dtto != null) { lst = lst.Where(l => Convert.ToDateTime(l.Rcpt_date).Date <= Convert.ToDateTime(dtto).Date).ToList(); } if (dtfrom != null) { lst = lst.Where(l => Convert.ToDateTime(l.Rcpt_date).Date >= Convert.ToDateTime(dtfrom).Date).ToList(); } if (FromCity > 0) { lst = lst.Where(l => l.BaseCity_Idno == FromCity).ToList(); } if (yearid > 0) { lst = lst.Where(l => l.Year_IdNo == yearid).ToList(); } if (SenderIdno > 0) { lst = lst.Where(l => l.Acnt_Idno == SenderIdno).ToList(); } return(lst); } }
public W30722(string programID, string programName) : base(programID, programName) { daoRPT = new RPT(); daoAM2 = new AM2(); daoAI2 = new AI2(); daoRamm1 = new RAMM1(); InitializeComponent(); this.Text = _ProgramID + "─" + _ProgramName; txtDate.DateTimeValue = GlobalInfo.OCF_DATE; ExportShow.Hide(); }
/// <summary> /// 寫入 Data_30333.30334 sheet /// </summary> /// <returns></returns> public string Wf30333() { Workbook workbook = new Workbook(); try { string SheetName = "Data_30333.30334"; string lsKindID = "GBF"; //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[SheetName]; DateTime ldtYMD = new DateTime(1900, 1, 1); //總列數 int rowIndex = 3; //int li_month_cnt=0; int RowTotal = 12; //Excel的Column預留空白行數12 int sumRowIndex = RowTotal + rowIndex + 1; //小計行數 int addRowCount = 0; //總計寫入的行數 worksheet.Rows[sumRowIndex][1 - 1].Value = $"{PbFunc.Left(_emMonthText, 4).AsInt() - 1911}小計"; string lsYMD = ""; //讀取資料 DataTable dt = new AM2().ListAM2(lsKindID, $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));//月份 foreach (DataRow row in dt.Rows) { if (lsYMD != row["AM2_YMD"].AsString()) { rowIndex = rowIndex + 1; lsYMD = row["AM2_YMD"].AsString(); //li_month_cnt = li_month_cnt + 1; addRowCount++; //月份 worksheet.Rows[rowIndex][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}/{PbFunc.Right(lsYMD, 2)}"; } //判斷買賣欄位位置 int columnIndex = 0; switch (row["AM2_IDFG_TYPE"].AsString()) { case "1": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 2 : 3) - 1; break; case "2": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 4 : 5) - 1; break; case "3": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 6 : 7) - 1; break; case "5": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 8 : 9) - 1; break; case "6": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 10 : 11) - 1; break; case "8": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 12 : 13) - 1; break; case "7": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 14 : 15) - 1; break; } //columnIndex指定要寫入的買賣欄位 worksheet.Rows[rowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal(); } //刪除空白列 if (RowTotal > addRowCount) { worksheet.Rows.Remove(rowIndex + 1, RowTotal - addRowCount); } worksheet.ScrollTo(0, 0); } catch (Exception ex) { throw ex; } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }