public ProcessOrderRecordText ReadProcessOrderRecordText() { var shippingCacheFileName = string.Concat(AppSettingConfig.ProcessOrderRecordDateFilePath(), AppSettingConfig.ProcessOrderRecordDateFileName()); //this code segment read data from the file. FileStream fs2 = new FileStream(shippingCacheFileName, FileMode.Open, FileAccess.Read); StreamReader reader = new StreamReader(fs2); var text = reader.ReadToEnd(); var processOrderRecordText = JsonConvert.DeserializeObject <ProcessOrderRecordText>(text); reader.Close(); return(processOrderRecordText); }
public void SetProcessOrderRecordDateExecute() { var existsFileName = Directory.GetFiles(AppSettingConfig.ProcessOrderRecordDateFilePath(), "*.txt").Select(System.IO.Path.GetFileName); var processOrderRecordFileName = string.Concat(AppSettingConfig.ProcessOrderRecordDateFilePath(), AppSettingConfig.ProcessOrderRecordDateFileName()); ProcessOrderRecordText processOrderRecordText = new ProcessOrderRecordText { DateTime = DateTime.Now, OrderNos = null }; // Create a new file File.WriteAllText(processOrderRecordFileName, JsonConvert.SerializeObject(processOrderRecordText)); }
void TestWrite(List <ProcessOrderStructure> processOrderStructures) { var tempPath = string.Concat(AppSettingConfig.ProcessOrderRecordDateFilePath(), "加工訂單紀錄表.xlsx"); IWorkbook templateWorkbook; using (FileStream fs = new FileStream(tempPath, FileMode.Open, FileAccess.Read)) { templateWorkbook = new XSSFWorkbook(fs); } var containOrderString = new List <string>(); Dictionary <string, List <ProcessOrderRecordOrderStringRow> > keyValuePairs = new Dictionary <string, List <ProcessOrderRecordOrderStringRow> >(); #region 更新邏輯 ////取得每一個sheet內的訂單編號 //foreach (var item in processOrderStructures) //{ // string sheetName = item.ProcessOrder.OrderString.Substring(2, 5); // ISheet sheet = templateWorkbook.GetSheet(sheetName) ?? templateWorkbook.CreateSheet(sheetName); // int lastRow = sheet.LastRowNum; // string priviousSheetName = string.Empty, priviousOrderString = string.Empty; // for (int row = 0; row <= lastRow; row++) // { // //為了防止cell null 錯誤 // if (lastRow == 0) // break; // IRow dataRow = sheet.GetRow(row); // ICell cell = dataRow.GetCell(0) ?? null; // //判斷Excel是否含有值 // if (cell != null && cell.CellType == CellType.String) // { // if (item.ProcessOrder.OrderString == cell.StringCellValue) // { // //判斷是否已存在於字典中 如不在字典中則加入key // if (!keyValuePairs.ContainsKey(sheetName)) // { // keyValuePairs.Add(sheetName, new List<ProcessOrderRecordOrderStringRow> // { // new ProcessOrderRecordOrderStringRow { OrderString = cell.StringCellValue, StartRow = row } // }); // } // else // { // keyValuePairs[sheetName].Add(new ProcessOrderRecordOrderStringRow { OrderString = cell.StringCellValue, StartRow = row }); // } // } // //if (!keyValuePairs.ContainsKey(priviousSheetName) || keyValuePairs[priviousSheetName].Count() == 0) // //{ // // priviousSheetName = sheetName; // // priviousOrderString = cell.StringCellValue; // // continue; // //} // if (priviousOrderString != cell.StringCellValue && priviousSheetName != string.Empty && keyValuePairs[priviousSheetName].Where(w => w.OrderString == priviousOrderString).Count() > 0) // keyValuePairs[priviousSheetName].Where(w => w.OrderString == priviousOrderString).First().EndRow = row; // priviousSheetName = sheetName; // priviousOrderString = cell.StringCellValue; // } // } //} #endregion ICellStyle positionStyle = templateWorkbook.CreateCellStyle(); positionStyle.WrapText = true; positionStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; positionStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; positionStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; positionStyle.FillPattern = FillPattern.SolidForeground; ICellStyle colorDetailStyle = templateWorkbook.CreateCellStyle(); colorDetailStyle.WrapText = true; colorDetailStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; colorDetailStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; colorDetailStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index; colorDetailStyle.FillPattern = FillPattern.SolidForeground; ICellStyle colorDetailStyle2 = templateWorkbook.CreateCellStyle(); colorDetailStyle2.WrapText = true; colorDetailStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; colorDetailStyle2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; colorDetailStyle2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index; colorDetailStyle2.FillPattern = FillPattern.SolidForeground; do { templateWorkbook.RemoveSheetAt(0); } while (templateWorkbook.NumberOfSheets != 0); foreach (var item in processOrderStructures) { string sheetName = item.ProcessOrder.OrderString.Substring(2, 5); ISheet sheet = templateWorkbook.GetSheet(sheetName) ?? templateWorkbook.CreateSheet(sheetName); sheet.SetColumnWidth(0, 3300); sheet.SetColumnWidth(1, 6300); sheet.SetColumnWidth(2, 4100); sheet.SetColumnWidth(3, 3700); sheet.SetColumnWidth(4, 5000); sheet.SetColumnWidth(5, 6000); sheet.SetColumnWidth(6, 2500); int lastRow = sheet.LastRowNum; //判斷是否已存在於字典中 如果存在則是使用更新 if (keyValuePairs.ContainsKey(sheetName) && keyValuePairs[sheetName].Where(w => w.OrderString == item.ProcessOrder.OrderString).Count() > 0) { #region 更新邏輯 int totalRoqCount = 1 + item.ProcessOrderColorGroups.Count() + item.ProcessOrderColorGroups.Sum(s => s.FactoryShippings.Count() + s.ProcessOrderFlowDateDetails.Count()); var currentOrder = keyValuePairs[sheetName].Where(w => w.OrderString == item.ProcessOrder.OrderString).First(); sheet.ShiftRows(currentOrder.EndRow, lastRow, totalRoqCount - (currentOrder.EndRow - currentOrder.StartRow)); int updateRowNumber = currentOrder.StartRow; IRow dataRow = sheet.CreateRow(updateRowNumber); CreateStringCell(dataRow, 0, item.ProcessOrder.OrderString, positionStyle); CreateStringCell(dataRow, 1, item.ProcessOrder.Fabric, positionStyle); CreateStringCell(dataRow, 2, item.ProcessOrder.Specification, positionStyle); CreateStringCell(dataRow, 3, item.ProcessOrder.ProcessItem, positionStyle); CreateStringCell(dataRow, 4, item.ProcessOrder.Precautions, positionStyle); CreateStringCell(dataRow, 5, item.ProcessOrder.Memo, positionStyle); CreateStringCell(dataRow, 6, item.ProcessOrder.HandFeel, positionStyle); foreach (var orderDetail in item.ProcessOrderColorGroups) { updateRowNumber++; IRow colorDetailRow = sheet.CreateRow(updateRowNumber); CreateStringCell(colorDetailRow, 1, orderDetail.ProcessOrderColorDetail.Color, positionStyle); CreateStringCell(colorDetailRow, 2, orderDetail.ProcessOrderColorDetail.ColorNumber, positionStyle); CreateStringCell(colorDetailRow, 3, orderDetail.ProcessOrderColorDetail.Quantity + "疋", positionStyle); foreach (var orderFlowDate in orderDetail.ProcessOrderFlowDateDetails) { updateRowNumber++; IRow orderFlowDateRow = sheet.CreateRow(updateRowNumber); CreateStringCell(orderFlowDateRow, 2, orderFlowDate.Name, positionStyle); CreateStringCell(orderFlowDateRow, 3, orderFlowDate.InputDate?.ToString("yyyy/MM/dd"), positionStyle); CreateStringCell(orderFlowDateRow, 4, orderFlowDate.CompleteDate?.ToString("yyyy/MM/dd"), positionStyle); } foreach (var factoryShipping in orderDetail.FactoryShippings) { updateRowNumber++; IRow factoryShippingRow = sheet.CreateRow(updateRowNumber); string sentence = string.Concat(factoryShipping.CreateDate.ToString("yyyy/MM/dd"), "載", factoryShipping.Quantity, "疋到", factoryShipping.Name); CreateStringCell(factoryShippingRow, 1, sentence, positionStyle); } } #endregion } else { if (lastRow != 0) { lastRow++; } IRow dataRow = sheet.CreateRow(lastRow); CreateStringCell(dataRow, 0, item.ProcessOrder.OrderString, positionStyle); CreateStringCell(dataRow, 1, item.ProcessOrder.Fabric, positionStyle); CreateStringCell(dataRow, 2, item.ProcessOrder.Specification, positionStyle); CreateStringCell(dataRow, 3, item.ProcessOrder.ProcessItem, positionStyle); CreateStringCell(dataRow, 4, item.ProcessOrder.Precautions, positionStyle); CreateStringCell(dataRow, 5, item.ProcessOrder.Memo, positionStyle); CreateStringCell(dataRow, 6, item.ProcessOrder.HandFeel, positionStyle); int index = 0; foreach (var orderDetail in item.ProcessOrderColorGroups) { lastRow++; bool isOdd = index % 2 == 1; index++; IRow colorDetailRow = sheet.CreateRow(lastRow); CreateBlankCell(colorDetailRow, 0, isOdd ? colorDetailStyle : colorDetailStyle2); CreateStringCell(colorDetailRow, 1, orderDetail.ProcessOrderColorDetail.Color, isOdd ? colorDetailStyle : colorDetailStyle2); CreateStringCell(colorDetailRow, 2, orderDetail.ProcessOrderColorDetail.ColorNumber, isOdd ? colorDetailStyle : colorDetailStyle2); CreateStringCell(colorDetailRow, 3, orderDetail.ProcessOrderColorDetail.Quantity + "疋", isOdd ? colorDetailStyle : colorDetailStyle2); CreateBlankCell(colorDetailRow, 4, isOdd ? colorDetailStyle : colorDetailStyle2); CreateBlankCell(colorDetailRow, 5, isOdd ? colorDetailStyle : colorDetailStyle2); CreateBlankCell(colorDetailRow, 6, isOdd ? colorDetailStyle : colorDetailStyle2); foreach (var orderFlowDate in orderDetail.ProcessOrderFlowDateDetails) { lastRow++; IRow orderFlowDateRow = sheet.CreateRow(lastRow); CreateBlankCell(orderFlowDateRow, 0, isOdd ? colorDetailStyle : colorDetailStyle2); CreateBlankCell(orderFlowDateRow, 1, isOdd ? colorDetailStyle : colorDetailStyle2); CreateStringCell(orderFlowDateRow, 2, orderFlowDate.Name, isOdd ? colorDetailStyle : colorDetailStyle2); CreateStringCell(orderFlowDateRow, 3, orderFlowDate.InputDate?.ToString("yyyy/MM/dd"), isOdd ? colorDetailStyle : colorDetailStyle2); CreateStringCell(orderFlowDateRow, 4, orderFlowDate.CompleteDate?.ToString("yyyy/MM/dd"), isOdd ? colorDetailStyle : colorDetailStyle2); CreateBlankCell(orderFlowDateRow, 5, isOdd ? colorDetailStyle : colorDetailStyle2); CreateBlankCell(orderFlowDateRow, 6, isOdd ? colorDetailStyle : colorDetailStyle2); } foreach (var factoryShipping in orderDetail.FactoryShippings) { lastRow++; IRow factoryShippingRow = sheet.CreateRow(lastRow); string sentence = string.Concat(factoryShipping.CreateDate.ToString("yyyy/MM/dd"), "載", factoryShipping.Quantity, "疋到", factoryShipping.Name); CreateBlankCell(factoryShippingRow, 0, isOdd ? colorDetailStyle : colorDetailStyle2); CreateStringCell(factoryShippingRow, 1, sentence, isOdd ? colorDetailStyle : colorDetailStyle2); CreateBlankCell(factoryShippingRow, 2, isOdd ? colorDetailStyle : colorDetailStyle2); CreateBlankCell(factoryShippingRow, 3, isOdd ? colorDetailStyle : colorDetailStyle2); CreateBlankCell(factoryShippingRow, 4, isOdd ? colorDetailStyle : colorDetailStyle2); CreateBlankCell(factoryShippingRow, 5, isOdd ? colorDetailStyle : colorDetailStyle2); CreateBlankCell(factoryShippingRow, 6, isOdd ? colorDetailStyle : colorDetailStyle2); } } } } using (FileStream fs = new FileStream(tempPath, FileMode.Create, FileAccess.Write)) { templateWorkbook.Write(fs); } }