public List <string> GetExcelSheetName() { IWorkbook workbook = null; //新建IWorkbook對象 string fileName = string.Concat(AppSettingConfig.FilePath(), "/", AppSettingConfig.StoreManageFileName()); FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read); workbook = new XSSFWorkbook(fileStream); //xlsx數據讀入workbook var sheetListName = new List <string>(); for (int sheetCount = 1; sheetCount < workbook.NumberOfSheets; sheetCount++) { var sheetName = workbook.GetSheetName(sheetCount); //獲取第i個工作表 sheetListName.Add(sheetName); } return(sheetListName); }
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(); } }
/// <summary> /// 讀取Excel後匯出Excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="readExcelAction"></param> /// <param name="createExcelAction"></param> /// <param name="timeRange"></param> /// <param name="excelFormat"></param> public void InventoryCheckSheet <T>(ReadExcelAction <T> readExcelAction, CreateExcelAction <T> createExcelAction, int timeRange, ExcelFormat excelFormat) { IWorkbook workbook = null; //新建IWorkbook對象 string fileName = string.Concat(AppSettingConfig.FilePath(), "/", AppSettingConfig.StoreManageFileName()); FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read); workbook = new XSSFWorkbook(fileStream); //xlsx數據讀入workbook var list = new List <T>(); for (int sheetCount = 1; sheetCount < workbook.NumberOfSheets; sheetCount++) { ISheet sheet = workbook.GetSheetAt(sheetCount); //獲取第i個工作表 IRow row; var firstRow = sheet.GetRow(0); for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++) //對工作表每一行 { if (rowIndex > 70) { break; } row = sheet.GetRow(rowIndex); //row讀入第i行數據 if (row != null) { //該筆資料沒有顏色則不讀取該Row if (row.GetCell(1) == null) { break; } readExcelAction(list, row, sheet.SheetName, timeRange); } else { break; } } } CreateExcelFile(createExcelAction, list, excelFormat); }
/// <summary> /// 取得Excel每日出貨清單 /// </summary> /// <param name="shippedDate"></param> /// <returns></returns> public List <StoreSearchData <StoreSearchColorDetail> > GetExcelDailyShippedList(DateTime?shippedDate) { IWorkbook workbook = null; //新建IWorkbook對象 string fileName = string.Concat(AppSettingConfig.FilePath(), "/", AppSettingConfig.StoreManageFileName()); FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read); workbook = new XSSFWorkbook(fileStream); //xlsx數據讀入workbook var list = new List <StoreSearchData <StoreSearchColorDetail> >(); var selectedDateTime = shippedDate == null?DateTime.Now.ToString("MM/dd") : shippedDate?.ToString("MM/dd"); var currentDateValue = "出貨" + selectedDateTime; for (int sheetCount = 1; sheetCount < workbook.NumberOfSheets; sheetCount++) { ISheet sheet = workbook.GetSheetAt(sheetCount); //獲取第i個工作表 IRow row; var currentDateCellIndex = -1; var firstRow = sheet.GetRow(0); for (int columnIndex = 5; columnIndex < 14; columnIndex++) { var cell = firstRow.GetCell(columnIndex); var cellValue = cell.StringCellValue; if (cellValue == currentDateValue) { currentDateCellIndex = columnIndex; break; } } var colorList = new List <StoreData>(); for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++) //對工作表每一行 { if (rowIndex > 120) { break; } row = sheet.GetRow(rowIndex); //row讀入第i行數據 if (row != null) { if (row.GetCell(ExcelEnum.ExcelInventoryColumnIndexEnum.ColorName.ToInt()) == null) { break; } if (row.GetCell(currentDateCellIndex) != null && row.GetCell(currentDateCellIndex).CellType != CellType.String && row.GetCell(currentDateCellIndex).NumericCellValue != 0) { var countInventory = row.GetCell(ExcelEnum.ExcelInventoryColumnIndexEnum.CountInventory.ToInt()); if (countInventory == null || string.IsNullOrEmpty(countInventory.ToString()) || (countInventory.CellType == CellType.Formula && countInventory.CachedFormulaResultType == CellType.Error)) { continue; } var cellValue = countInventory.NumericCellValue; //獲取i行j列數據 //清單裡若沒有該布種則加入 if (list.Where(w => w.TextileName == sheet.SheetName).Count() == 0) { list.Add(new StoreSearchData <StoreSearchColorDetail> { TextileName = sheet.SheetName, StoreSearchColorDetails = new List <StoreSearchColorDetail>() }); } var currentTextile = list.Where(w => w.TextileName == sheet.SheetName).First(); currentTextile.StoreSearchColorDetails.Add(new StoreSearchColorDetail { ColorName = row.GetCell(ExcelEnum.ExcelInventoryColumnIndexEnum.ColorName.ToInt()) == null ? "" : row.GetCell(ExcelEnum.ExcelInventoryColumnIndexEnum.ColorName.ToInt()).ToString(), FabricFactory = row.GetCell(ExcelEnum.ExcelInventoryColumnIndexEnum.FabricFactory.ToInt()) == null ? "" : row.GetCell(ExcelEnum.ExcelInventoryColumnIndexEnum.FabricFactory.ToInt()).ToString(), ClearFactory = row.GetCell(ExcelEnum.ExcelInventoryColumnIndexEnum.ClearFactory.ToInt()) == null ? "" : row.GetCell(ExcelEnum.ExcelInventoryColumnIndexEnum.ClearFactory.ToInt()).ToString(), ShippedCount = Convert.ToInt32(row.GetCell(currentDateCellIndex).NumericCellValue), CountInventory = cellValue.ToString(), CheckDate = row.GetCell(ExcelEnum.ExcelInventoryColumnIndexEnum.CheckDate.ToInt()) == null ? "" : row.GetCell(ExcelEnum.ExcelInventoryColumnIndexEnum.CheckDate.ToInt()).ToString() }); } } else { break; } } } return(list); }
private void InventoryNumberRangeSearchExecute() { StoreDataList.Clear(); IWorkbook workbook = null; //新建IWorkbook對象 string fileName = string.Concat(AppSettingConfig.FilePath(), "/", AppSettingConfig.StoreManageFileName()); FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read); workbook = new XSSFWorkbook(fileStream); //xlsx數據讀入workbook Regex checkStoreAreaPattern = new Regex(string.Concat("(^", StoreArea.Replace(",", ")+|(^"), ")+")); Regex checkExceptAreaPattern = new Regex(string.Concat("(", ExceptArea.Replace(",", ")+|("), ")+")); Regex checkTextileNamePattern = new Regex(string.Concat("(", TextileName.Replace(",", ")+|("), ")+")); for (int sheetCount = 1; sheetCount < workbook.NumberOfSheets; sheetCount++) { ISheet sheet = workbook.GetSheetAt(sheetCount); //獲取第i個工作表 IRow row; // = sheet.GetRow(0); //新建當前工作表行數據 if (!checkTextileNamePattern.IsMatch(sheet.SheetName)) { continue; } var colorList = new List <StoreData>(); for (int rowNumber = 1; rowNumber <= sheet.LastRowNum; rowNumber++) //對工作表每一行 { if (rowNumber > 70) { break; } row = sheet.GetRow(rowNumber); //row讀入第i行數據 if (row != null) { if (row.GetCell((int)ExcelEnum.ExcelInventoryColumnIndexEnum.ColorName) == null) { break; } ICell countInventory = row.GetCell((int)ExcelEnum.ExcelInventoryColumnIndexEnum.CountInventory); if (countInventory == null || string.IsNullOrEmpty(countInventory.ToString()) || (countInventory.CellType == CellType.Formula && countInventory.CachedFormulaResultType == CellType.Error)) { continue; } double cellValue = countInventory.NumericCellValue; //獲取i行j列數據 string storeArea = row.GetCell((int)ExcelEnum.ExcelInventoryColumnIndexEnum.StorageSpaces) == null ? "" : row.GetCell((int)ExcelEnum.ExcelInventoryColumnIndexEnum.StorageSpaces).ToString(); if (cellValue <= MaxNumber && cellValue >= MinNumber && checkStoreAreaPattern.IsMatch(storeArea) && !checkExceptAreaPattern.IsMatch(storeArea)) { colorList.Add(new StoreData { ColorName = row.GetCell((int)ExcelEnum.ExcelInventoryColumnIndexEnum.ColorName) == null ? "" : row.GetCell((int)ExcelEnum.ExcelInventoryColumnIndexEnum.ColorName).ToString(), StoreArea = storeArea, FabricFactory = row.GetCell((int)ExcelEnum.ExcelInventoryColumnIndexEnum.FabricFactory) == null ? "" : row.GetCell((int)ExcelEnum.ExcelInventoryColumnIndexEnum.FabricFactory).ToString(), ClearFactory = row.GetCell((int)ExcelEnum.ExcelInventoryColumnIndexEnum.ClearFactory) == null ? "" : row.GetCell((int)ExcelEnum.ExcelInventoryColumnIndexEnum.ClearFactory).ToString(), CountInventory = cellValue.ToString(), CheckDate = row.GetCell((int)ExcelEnum.ExcelInventoryColumnIndexEnum.CheckDate) == null ? "" : row.GetCell((int)ExcelEnum.ExcelInventoryColumnIndexEnum.CheckDate).ToString() }); } else { continue; } } else { break; } } DateTime result; var soretColorList = colorList.OrderBy(o => o.FabricFactory).ThenBy(o => o.ClearFactory).ThenBy(o => Convert.ToDouble(o.CountInventory)).ThenByDescending(O => DateTime.TryParse(O.CheckDate, out result) == true ? result : DateTime.Now.AddDays(-360)); if (soretColorList.Count() > 0) { StoreDataList.Add(new StoreData { TextileName = sheet.SheetName, ColorName = "", CountInventory = "" }); } foreach (var item in soretColorList) { StoreDataList.Add(item); } } fileStream.Close(); workbook.Close(); }