public RevenueExportViewModel(string revenueDate)//2021-5 { DateTime date = DateTime.Parse(revenueDate); List <TrashCustomerShipped> invoSubList = new List <TrashCustomerShipped>(TrashModule.GetInvoSub(date)); List <CustomerRevenue> customerRevenue = invoSubList.GroupBy(g => new { cusotmerID = g.C_01, customerName = g.C_Name }).Select(s => new CustomerRevenue { CustomerID = s.Key.cusotmerID, CustomerName = s.Key.customerName, Revenue = s.Sum(sum => Math.Ceiling(sum.Price * sum.Quantity)) }).OrderBy(o => o.CustomerID).ToList(); customerRevenue.Add(new CustomerRevenue { CustomerName = "總金額", Revenue = customerRevenue.Sum(s => s.Revenue) }); List <ExcelRowContent> excelRowContents = new List <ExcelRowContent>(); foreach (var item in customerRevenue) { ExcelRowContent excelRowContent = new ExcelRowContent { ExcelCellContents = new List <ExcelCellContent>() { new ExcelCellContent { CellValue = item.CustomerName }, new ExcelCellContent { CellValue = item.Revenue.ToString() } } }; excelRowContents.Add(excelRowContent); } ; var excelHelper = new ExcelHelper(); ExcelContent excelContent = new ExcelContent { FileName = string.Concat("營收表", date.ToString("yyyy-MM")), ExcelSheetContents = new List <ExcelSheetContent> { new ExcelSheetContent { SheetName = "營業額", ExcelColumnContents = new List <ExcelColumnContent> { new ExcelColumnContent { CellValue = "客戶名稱", Width = 3000 }, new ExcelColumnContent { CellValue = "營業額", Width = 5000 }, new ExcelColumnContent { CellValue = "實收金額", Width = 5000 }, new ExcelColumnContent { CellValue = "差額", Width = 5000 }, new ExcelColumnContent { CellValue = "0.99", Width = 5000 }, new ExcelColumnContent { CellValue = "單價折", Width = 5000 }, new ExcelColumnContent { CellValue = "故障折", Width = 5000 }, new ExcelColumnContent { CellValue = "尾數折", Width = 5000 }, new ExcelColumnContent { CellValue = "稅金發票1", Width = 5000 }, new ExcelColumnContent { CellValue = "稅金發票2", Width = 5000 }, new ExcelColumnContent { CellValue = "稅金發票3", Width = 5000 } }, ExcelRowContents = excelRowContents }, new ExcelSheetContent { SheetName = "人事開銷", ExcelColumnContents = new List <ExcelColumnContent>(), ExcelRowContents = new List <ExcelRowContent>() }, new ExcelSheetContent { SheetName = "基本開銷", ExcelColumnContents = new List <ExcelColumnContent>(), ExcelRowContents = new List <ExcelRowContent>() }, new ExcelSheetContent { SheetName = "紗商", ExcelColumnContents = new List <ExcelColumnContent>(), ExcelRowContents = new List <ExcelRowContent>() }, new ExcelSheetContent { SheetName = "織廠", ExcelColumnContents = new List <ExcelColumnContent>(), ExcelRowContents = new List <ExcelRowContent>() }, new ExcelSheetContent { SheetName = "加工廠", ExcelColumnContents = new List <ExcelColumnContent>(), ExcelRowContents = new List <ExcelRowContent>() }, new ExcelSheetContent { SheetName = "其他開銷", ExcelColumnContents = new List <ExcelColumnContent>(), ExcelRowContents = new List <ExcelRowContent>() } } }; IWorkbook wb = new XSSFWorkbook(); excelHelper.CreateExcelFile(wb, excelContent); }
private void ExportShippingCheckExecute() { List <StoreSearchData <StoreSearchColorDetail> > excelDailyShippedList = ExcelModule.GetExcelDailyShippedList(ShippingCheckDate); IEnumerable <TrashShipped> trashShipped = TrashModule.GetTrashShippedQuantitySum(ShippingCheckDate, ShippingCheckDate); ExternalDataHelper externalDataHelper = new ExternalDataHelper(); IEnumerable <TextileNameMapping> textileNameMappings = externalDataHelper.GetTextileNameMappings(); List <OriginalSource> trashItems = new List <OriginalSource>(); foreach (TrashShipped shipped in trashShipped) { trashItems.Add(new OriginalSource { DateTime = shipped.IN_DATE, TextileNo = shipped.I_01, TextileColorName = shipped.I_03, Weight = shipped.Quantity }); } List <Container> trashPrimary = new List <Container>(); foreach (OriginalSource trashItem in trashItems) { int priviousDistance = 10; string textileName = string.Empty; string textileColor = string.Empty; int shippedCount = 0; foreach (StoreSearchData <StoreSearchColorDetail> excelDailyShippedItem in excelDailyShippedList) { TextileNameMapping textileNameMapping = textileNameMappings.ToList().Find(f => f.Inventory.Contains(excelDailyShippedItem.TextileName)) ?? new TextileNameMapping(); foreach (StoreSearchColorDetail colorDetail in excelDailyShippedItem.StoreSearchColorDetails) { string accountMapping = textileNameMapping.Account == null ? string.Empty : textileNameMapping.Account.FirstOrDefault(); if (trashItem.TextileColorName == string.Concat(accountMapping.Split('*')[0], colorDetail.ColorName.Split('-')[0])) { textileColor = colorDetail.ColorName; textileName = excelDailyShippedItem.TextileName; priviousDistance = 0; shippedCount = colorDetail.ShippedCount; break; } } } trashPrimary.Add(new Container() { OriginalSource = trashItem, TextileName = textileName, ColorName = textileColor, ShippedCount = shippedCount, Distance = priviousDistance }); } List <Container> excelPrimary = new List <Container>(); foreach (StoreSearchData <StoreSearchColorDetail> excelDailyShippedItem in excelDailyShippedList) { var priviousDistance = 10; var textileName = string.Empty; var textileColor = string.Empty; foreach (var colorDetail in excelDailyShippedItem.StoreSearchColorDetails) { TextileNameMapping textileNameMapping = textileNameMappings.ToList().Find(f => f.Inventory.Contains(excelDailyShippedItem.TextileName)) ?? new TextileNameMapping(); OriginalSource originalSource = new OriginalSource(); foreach (var trashItem in trashItems) { string accountMapping = textileNameMapping.Account == null ? string.Empty : textileNameMapping.Account.FirstOrDefault(); if (trashItem.TextileColorName == string.Concat(accountMapping.Split('*')[0], colorDetail.ColorName.Split('-')[0])) { originalSource.DateTime = trashItem.DateTime; originalSource.TextileColorName = trashItem.TextileColorName; originalSource.Weight = trashItem.Weight; originalSource.TextileNo = trashItem.TextileNo; break; } } excelPrimary.Add(new Container() { OriginalSource = originalSource, TextileName = excelDailyShippedItem.TextileName, ColorName = colorDetail.ColorName, ShippedCount = colorDetail.ShippedCount, Distance = priviousDistance }); } } ExcelHelper excelHelper = new ExcelHelper(); IWorkbook wb = new XSSFWorkbook(); ICellStyle positionStyle = wb.CreateCellStyle(); positionStyle.WrapText = true; positionStyle.Alignment = HorizontalAlignment.Center; positionStyle.VerticalAlignment = VerticalAlignment.Center; ICellStyle estyle = wb.CreateCellStyle(); estyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index; estyle.FillPattern = FillPattern.SolidForeground; ICellStyle a2style = wb.CreateCellStyle(); a2style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Coral.Index; a2style.FillPattern = FillPattern.SolidForeground; List <ExcelRowContent> trashPrimaryExcelRowContent = new List <ExcelRowContent>(); foreach (var item in trashPrimary.OrderByDescending(t => t.OriginalSource.TextileColorName == null).ThenBy(t => t.TextileName).ThenBy(o => o.ColorName)) { var approximateNumber = item.OriginalSource.Weight / 20; var round = Math.Round(approximateNumber, 0, MidpointRounding.AwayFromZero); var isEqual = round == item.ShippedCount; ExcelRowContent excelCellContents = new ExcelRowContent { ExcelCellContents = new List <ExcelCellContent> { new ExcelCellContent { CellValue = item.OriginalSource.TextileColorName, CellStyle = positionStyle }, new ExcelCellContent { CellValue = item.OriginalSource.Weight.ToString(), CellStyle = positionStyle }, new ExcelCellContent { CellValue = (approximateNumber).ToString(), CellStyle = positionStyle }, new ExcelCellContent { CellValue = item.TextileName, CellStyle = positionStyle }, new ExcelCellContent { CellValue = item.ColorName, CellStyle = positionStyle }, new ExcelCellContent { CellValue = item.ShippedCount.ToString(), CellStyle = isEqual ? positionStyle : estyle }, } }; trashPrimaryExcelRowContent.Add(excelCellContents); } ; List <ExcelRowContent> excelPrimaryExcelRowContent = new List <ExcelRowContent>(); foreach (var item in excelPrimary.OrderByDescending(t => t.OriginalSource.TextileColorName == null).ThenBy(t => t.TextileName).ThenBy(o => o.ColorName)) { var approximateNumber = item.OriginalSource.Weight / 20; var round = Math.Round(approximateNumber, 0, MidpointRounding.AwayFromZero); var isEqual = round == item.ShippedCount; ExcelRowContent excelCellContents = new ExcelRowContent { ExcelCellContents = new List <ExcelCellContent> { new ExcelCellContent { CellValue = item.OriginalSource.TextileColorName, CellStyle = positionStyle }, new ExcelCellContent { CellValue = item.OriginalSource.Weight.ToString(), CellStyle = positionStyle }, new ExcelCellContent { CellValue = (approximateNumber).ToString(), CellStyle = positionStyle }, new ExcelCellContent { CellValue = item.TextileName, CellStyle = positionStyle }, new ExcelCellContent { CellValue = item.ColorName, CellStyle = positionStyle }, new ExcelCellContent { CellValue = item.ShippedCount.ToString(), CellStyle = isEqual ? positionStyle : estyle } } }; excelPrimaryExcelRowContent.Add(excelCellContents); } ; ExcelContent excelContent = new ExcelContent { FileName = string.Concat("庫存對照清單", ShippingCheckDate.ToString("yyyy-MM-dd")), ExcelSheetContents = new List <ExcelSheetContent> { new ExcelSheetContent { SheetName = "Super為主", ExcelColumnContents = new List <ExcelColumnContent> { new ExcelColumnContent { CellValue = "Super布種名稱顏色", Width = 6450 }, new ExcelColumnContent { CellValue = "出貨重量", Width = 2800 }, new ExcelColumnContent { CellValue = "約略出貨數", Width = 2000 }, new ExcelColumnContent { CellValue = "布種名稱", Width = 4550 }, new ExcelColumnContent { CellValue = "顏色", Width = 5550 }, new ExcelColumnContent { CellValue = "出貨數量", Width = 1850 } }, ExcelRowContents = trashPrimaryExcelRowContent }, new ExcelSheetContent { SheetName = "Excel為主", ExcelColumnContents = new List <ExcelColumnContent> { new ExcelColumnContent { CellValue = "Super布種名稱顏色", Width = 6450 }, new ExcelColumnContent { CellValue = "出貨重量", Width = 2800 }, new ExcelColumnContent { CellValue = "約略出貨數", Width = 2000 }, new ExcelColumnContent { CellValue = "布種名稱", Width = 4550 }, new ExcelColumnContent { CellValue = "顏色", Width = 5550 }, new ExcelColumnContent { CellValue = "出貨數量", Width = 1850 } }, ExcelRowContents = excelPrimaryExcelRowContent } } }; excelHelper.CreateExcelFile(wb, excelContent); }