private void CheckBox_Click(object sender, RoutedEventArgs e)
{
CheckBox checkBox = (CheckBox)sender;
ExternalDataHelper externalDataHelper = new ExternalDataHelper();
TextileNameMappings = externalDataHelper.GetTextileNameMappings();
if (checkBox.IsChecked ?? false)
{
string fileNamePath = string.Concat(AppSettingConfig.FilePath(), "/", AppSettingConfig.StoreManageFileName());
Tuple <List <string>, IWorkbook> tuple = ExcelModule.GetExcelWorkbook(fileNamePath);
TextileInventoryHeader textileInventoryHeader = ExcelModule.GetShippingDate(tuple.Item2.GetSheetAt(1));
Workbook = tuple.Item2;
Window parentWindow = Window.GetWindow(this);
var textileNameMapping = TextileNameMappings.ToList().Find(f => f.ProcessOrder.Contains(ProcessOrder == null ? string.Empty : ProcessOrder.Fabric));
List <TextileColorInventory> selectedTextiles = new List <TextileColorInventory>();
if (textileNameMapping != null)
{
ExcelHelper excelHelper = new ExcelHelper();
foreach (var item in textileNameMapping.Inventory)
{
selectedTextiles.AddRange(excelHelper.GetInventoryData(Workbook, item));
}
textileInventoryHeader.Textile = ProcessOrder.Fabric;
}
InventoryListDialog = new InventoryListDialog(AppSettingConfig.StoreManageFileName(), textileInventoryHeader, selectedTextiles)
{
Owner = Window.GetWindow(this),
Top = parentWindow.Top + parentWindow.Height,
Left = parentWindow.Left,
Height = 300
};
InventoryListDialog.Show();
InventoryListDialog.Closed += InventoryListDialog_Closed;
InventoryUpdateTime.Content = DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss");
}
else
{
InventoryListDialog.Close();
}
}
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);
}
public ShipFeatureViewModel(string shipFeatureDate)
{
string defaultDate = DateTime.Now.ToString("yyyyMMdd");
string[] dateNumber = shipFeatureDate.Split('-');
string fileNameDate = string.Concat(AppSettingConfig.FilePath(), "\\出貨", dateNumber[0].Length > 5 ? dateNumber[0] : defaultDate, "-", dateNumber[0].Length > 5 ? dateNumber[1] : dateNumber[0], ".xlsx");
//IEnumerable<string> shipFileName = Directory.GetFiles(AppSettingConfig.FilePath(), fileNameDate).Select(System.IO.Path.GetFileName).OrderByDescending(o => o);
IWorkbook workbook = null; //新建IWorkbook對象
using (FileStream fs = new FileStream(fileNameDate, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(fs);
}
IEnumerable <TrashItem> trashItems = TrashModule.GetTrashItems().Where(w => w.I_03 != null).OrderBy(o => o.I_01);
ExternalDataHelper externalDataHelper = new ExternalDataHelper();
IEnumerable <TextileNameMapping> textileNameMappings = externalDataHelper.GetTextileNameMappings();
for (int sheetCount = 1; sheetCount < workbook.NumberOfSheets; sheetCount++)
{
ISheet sheet = workbook.GetSheetAt(sheetCount); //獲取第i個工作表
string textileName = null;
sheet.SetColumnWidth(4, 12200);
ICell customerCell = sheet.GetRow(4).GetCell(2);
IEnumerable <TrashCustomer> trashCustomers = TrashModule.GetCustomerList().Where(w => customerCell.StringCellValue.Contains(w.C_NAME));
if (trashCustomers != null && trashCustomers.FirstOrDefault() != null)
{
customerCell.SetCellValue(string.Concat(customerCell.StringCellValue, "-", trashCustomers.FirstOrDefault().CARD_NO, trashCustomers.FirstOrDefault().C_NAME));
}
for (int rowCount = 6; rowCount <= 18; rowCount++)
{
IRow row = sheet.GetRow(rowCount);
if (row == null)
{
break;
}
ICell textileNameCell = row.GetCell(1);
if (textileNameCell != null && textileNameCell.StringCellValue != null && textileNameCell.StringCellValue != "")
{
textileName = textileNameCell.StringCellValue;
}
ICell colorCell = row.GetCell(4);
if (colorCell == null)
{
break;
}
string colorCellValue = colorCell.CellType == CellType.String ? colorCell.StringCellValue : colorCell.NumericCellValue.ToString();
if (colorCellValue == null || colorCellValue == "")
{
break;
}
string textileColor = colorCellValue.Split('-')[0];
int textileColorNumber = colorCellValue.Split('-').Length >= 2 ? colorCellValue.Split('-')[1].ToInt() : 0;
if (textileColorNumber / 7 >= 1)
{
rowCount += textileColorNumber / 7;
}
TrashItem trashItem = externalDataHelper.GetTrashItemFromInventoryMapping(trashItems, textileName, textileColor, textileNameMappings);
string accountFactoryID = string.Empty;
string accountTextileID = string.Empty;
string accountTextileName = string.Empty;
if (trashItem == null)
{
}
else
{
accountFactoryID = trashItem.F_01;
accountTextileID = trashItem.I_01;
accountTextileName = trashItem.I_03;
}
string shipFeatureString = colorCellValue + "*" + accountFactoryID + "_" + accountTextileID + "-" + accountTextileName;
colorCell.SetCellValue(shipFeatureString);
IFont font = workbook.CreateFont();
font.Color = HSSFColor.Blue.Index2;
font.FontName = "新細明體";
font.IsBold = true;
colorCell.RichStringCellValue.ApplyFont(
shipFeatureString.Length - 1 - accountTextileID.Length - accountTextileName.Length
, shipFeatureString.Length - accountTextileName.Length - 1, font);
}
}
using (FileStream fs = new FileStream(fileNameDate, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
private void ExportToExcel()
{
//建立Excel 2003檔案
IWorkbook wb = new XSSFWorkbook();
ISheet ws = wb.CreateSheet("Class");
XSSFRow row = (XSSFRow)ws.CreateRow(0);
row.Height = 440;
ws.SetColumnWidth(0, 3000);
ws.SetColumnWidth(1, 3150);
ws.SetColumnWidth(2, 1700);
ws.SetColumnWidth(4, 1700);
ICellStyle positionStyle = wb.CreateCellStyle();
positionStyle.WrapText = true;
positionStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
positionStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
ICellStyle greyStyle = wb.CreateCellStyle();
greyStyle.WrapText = true;
greyStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
greyStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
greyStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
greyStyle.FillPattern = FillPattern.SolidForeground;
ICellStyle lightGreenStyle = wb.CreateCellStyle();
lightGreenStyle.WrapText = true;
lightGreenStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
lightGreenStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
lightGreenStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
lightGreenStyle.FillPattern = FillPattern.SolidForeground;
ICellStyle lightTurquoiseStyle = wb.CreateCellStyle();
lightTurquoiseStyle.WrapText = true;
lightTurquoiseStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
lightTurquoiseStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
lightTurquoiseStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightTurquoise.Index;
lightTurquoiseStyle.FillPattern = FillPattern.SolidForeground;
ICellStyle coralStyle = wb.CreateCellStyle();
coralStyle.WrapText = true;
coralStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
coralStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
coralStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Coral.Index;
coralStyle.FillPattern = FillPattern.SolidForeground;
ExcelHelper.CreateCell(row, 0, "布種", positionStyle);
ExcelHelper.CreateCell(row, 1, "顏色", positionStyle);
ExcelHelper.CreateCell(row, 2, "織廠", positionStyle);
ExcelHelper.CreateCell(row, 3, "整理", positionStyle);
ExcelHelper.CreateCell(row, 4, "出貨量", positionStyle);
ExcelHelper.CreateCell(row, 5, "計算庫存量", positionStyle);
ExcelHelper.CreateCell(row, 6, "時間", positionStyle);
ExcelHelper.CreateCell(row, 7, "10天內", positionStyle);
ExcelHelper.CreateCell(row, 8, "20天內", positionStyle);
ExcelHelper.CreateCell(row, 9, "30天內", positionStyle);
ExcelHelper.CreateCell(row, 10, "60天內", positionStyle);
int rowIndex = 1;
string textileName = string.Empty;
ExternalDataHelper externalDataHelper = new ExternalDataHelper();
IEnumerable <TextileNameMapping> textileNameMappings = externalDataHelper.GetTextileNameMappings();
IEnumerable <TrashItem> trashItems = TrashModule.GetTrashItems().Where(w => w.I_03 != null).OrderBy(o => o.I_01);
DateTime tenDays = DateTime.Now.Date.AddDays(-10);
DateTime twentyDays = DateTime.Now.Date.AddDays(-20);
DateTime thirtyDays = DateTime.Now.Date.AddDays(-30);
List <TrashShipped> xtrashCustomerShippeds = TrashModule.GetTrashShippedList(DateTime.Now.AddDays(-60), DateTime.Now).ToList();
foreach (StoreData storeData in ShippingHistoryStoreDataList)
{
XSSFRow rowTextile = (XSSFRow)ws.CreateRow(rowIndex);
ExcelHelper.CreateCell(rowTextile, 0, storeData.TextileName, positionStyle);
if (!string.IsNullOrEmpty(storeData.TextileName))
{
textileName = storeData.TextileName;
rowIndex++;
continue;
}
TrashItem trashItem = externalDataHelper.GetTrashItemFromInventoryMapping(trashItems, textileName, storeData.ColorName.Split('-')[0], textileNameMappings);
List <TrashShipped> trashShippeds = new List <TrashShipped>();
if (trashItem != null)
{
trashShippeds = xtrashCustomerShippeds.Where(w => w.I_03 == trashItem.I_03).ToList();
}
ExcelHelper.CreateCell(rowTextile, 1, storeData.ColorName, positionStyle);
ExcelHelper.CreateCell(rowTextile, 2, storeData.FabricFactory, positionStyle);
ExcelHelper.CreateCell(rowTextile, 3, storeData.ClearFactory, positionStyle);
ExcelHelper.CreateCell(rowTextile, 4, storeData.ShippedCount.ToString(), positionStyle);
ExcelHelper.CreateCell(rowTextile, 5, storeData.CountInventory, positionStyle);
ExcelHelper.CreateCell(rowTextile, 6, storeData.CheckDate, positionStyle);
ExcelHelper.CreateCell(rowTextile, 7, Math.Round(trashShippeds.Where(w => w.IN_DATE.Date >= tenDays).Sum(s => s.Quantity) / 22, 0), greyStyle);
ExcelHelper.CreateCell(rowTextile, 8, Math.Round(trashShippeds.Where(w => w.IN_DATE.Date >= twentyDays).Sum(s => s.Quantity) / 22, 0), lightGreenStyle);
ExcelHelper.CreateCell(rowTextile, 9, Math.Round(trashShippeds.Where(w => w.IN_DATE.Date >= thirtyDays).Sum(s => s.Quantity) / 22, 0), lightTurquoiseStyle);
ExcelHelper.CreateCell(rowTextile, 10, Math.Round(trashShippeds.Sum(s => s.Quantity) / 22, 0), coralStyle);
rowIndex++;
}
var selectedDateTime = ShippingHistoryDate == null?DateTime.Now.ToString("yyyyMMdd") : ShippingHistoryDate.ToString("yyyyMMdd");
FileStream file = new FileStream(string.Concat(AppSettingConfig.StoreSearchFilePath(), "出貨查詢", selectedDateTime, ".xlsx"), FileMode.Create);//產生檔案
wb.Write(file);
file.Close();
}
