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();
}