private void InitContentRows(Worksheet worksheet, int sheetIndex, DataTable data) { List <FinanceExportInfo> list = new List <FinanceExportInfo>((int)(data.Rows.Count * 1.3)); foreach (DataRow row in data.Rows) { FinanceExportInfo entity = FinanceExportInfo.Create(row); list.Add(entity); } ushort currentRowIndex = 2; ushort currentColIndex; list.ForEach(delegate(FinanceExportInfo item) { currentRowIndex++; currentColIndex = 0; worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.VendorSysNo.ToString(), GetDataCenterCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.VendorName, GetDataLeftCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.IsConsignDisplay, GetDataLeftCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.AccountID, GetDataLeftCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.BankName, GetDataLeftCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.PayAmtMatureString, GetDataRightCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.VendorPayTypeDisplay, GetDataLeftCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.C1NameStr, GetDataLeftCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.KCAmtDisplay, GetDataRightCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.PayAmtLeftString, GetDataRightCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, (item.KCAmt - item.PayAmtLeft).ToString("###,###,###0.00"), GetDataRightCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.ZXAmtDisplay, GetDataRightCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.ReceiveByInvoiceDisplay, GetDataRightCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.ReceiveByAcctDisplay, GetDataRightCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.ReceiveByPODisplay, GetDataRightCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.ReceiveByConsignDisplay, GetDataRightCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.CashDisplay, GetDataRightCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.DetailOrderSysNoStr, GetDataLeftCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.PendingInvoiceAmountDisplay, GetDataRightCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.EndBalanceAccruedDisplay, GetDataRightCellXF()); }); }
private void InitContentRows(Worksheet worksheet, int sheetIndex, DataTable data) { List <FinanceExportInfo> list = new List <FinanceExportInfo>((int)(data.Rows.Count * 1.3)); foreach (DataRow row in data.Rows) { FinanceExportInfo entity = FinanceExportInfo.Create(row); list.Add(entity); } ushort currentRowIndex = 2; ushort currentColIndex = 0; var result = from p in list.AsQueryable() group p by new { p.VendorSysNo, p.VendorName } into g select new { g.Key.VendorSysNo, g.Key.VendorName, TotalAmt = g.Sum(x => x.OrderAmt).ToString("###,###,###0.00"), Detail = g.Where(x => x.VendorSysNo == g.Key.VendorSysNo).ToList() }; foreach (var vendor in result) { currentColIndex = 0; ++currentColIndex; worksheet.AddMergeArea(new MergeArea(currentRowIndex, currentRowIndex + vendor.Detail.Count - 1, currentColIndex, currentColIndex)); worksheet.Cells.Add(currentRowIndex, currentColIndex, vendor.VendorSysNo, GetDataCenterCellXF()); ++currentColIndex; worksheet.AddMergeArea(new MergeArea(currentRowIndex, currentRowIndex + vendor.Detail.Count - 1, currentColIndex, currentColIndex)); worksheet.Cells.Add(currentRowIndex, currentColIndex, vendor.VendorName, GetDataCenterCellXF()); ++currentColIndex; worksheet.AddMergeArea(new MergeArea(currentRowIndex, currentRowIndex + vendor.Detail.Count - 1, currentColIndex, currentColIndex)); worksheet.Cells.Add(currentRowIndex, currentColIndex, vendor.TotalAmt, GetDataCenterCellXF()); bool flag = false; vendor.Detail.ForEach(delegate(FinanceExportInfo item) { currentColIndex = 3; if (flag) { currentColIndex = 0; worksheet.Cells.Add(currentRowIndex, ++currentColIndex, string.Empty, GetDataLeftCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, string.Empty, GetDataLeftCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, string.Empty, GetDataLeftCellXF()); } worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.OrderIDDisplay, GetDataLeftCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.OrderTypeDisplay, GetDataLeftCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.PMName, GetDataCenterCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.CreateTimeDisplay, GetDataCenterCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.ETPDisplay, GetDataCenterCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.InvoiceStatusDisplay, GetDataCenterCellXF()); //worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.ZXAmtString, GetDataRightCellXF()); worksheet.Cells.Add(currentRowIndex, ++currentColIndex, item.OrderAmtDisplay, GetDataCenterCellXF()); ++currentRowIndex; flag = true; }); } }