//================================================================ // Writes Data into Database .xlsx and .txt Files MONTHLY //================================================================ public void WriteIntoDatabaseXlsxNEW(string FilePath, string AssetName) { BunkerReportHandling NewHandlingTools = new BunkerReportHandling(); string[] CoordinatesOfLastElement = NewHandlingTools.NewEntry(FilePath, AssetName); FileInfo File = new FileInfo(FilePath); //TODO ADD: Data and creation of new worksheet option using (var BunkerReport = new ExcelPackage(File)) { ExcelWorksheet worksheet = BunkerReport.Workbook.Worksheets[1]; //int colIndex_1 = worksheet.Cells[CoordinatesOfLastElement[0]].Start.Column; //int rowIndex_1 = worksheet.Cells[CoordinatesOfLastElement[0]].Start.Row; int colIndex_2 = worksheet.Cells[CoordinatesOfLastElement[1]].Start.Column; int rowIndex_2 = worksheet.Cells[CoordinatesOfLastElement[1]].Start.Row; // ADD DATA worksheet.Column(colIndex_2 + 1).Width = 15; worksheet.Column(colIndex_2 + 2).Width = 18; worksheet.Column(colIndex_2 + 3).Width = 18; string[] DateSplit = Vessel.RecordDateTime.Split(' '); worksheet.Cells[1, colIndex_2 + 1].Style.WrapText = true; int WeekNumber = new int(); WeekNumber = colIndex_2 / 3 + 1; worksheet.Cells[1, colIndex_2 + 1].Value = "Week " + WeekNumber.ToString(); //+ DateSplit[0].ToString();// + "\t Difference in [MT]"; worksheet.Cells[1, colIndex_2 + 1].Style.Font.Bold = true; worksheet.Cells[1, colIndex_2 + 1].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[1, colIndex_2 + 1].Style.Fill.BackgroundColor.SetColor(Color.LightBlue); worksheet.Cells[1, colIndex_2 + 1, 1, colIndex_2 + 3].Merge = true; //Merge columns start and end range worksheet.Cells[1, colIndex_2 + 1, 1, colIndex_2 + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Alignment is center worksheet.Cells[rowIndex_2, colIndex_2 + 1].Value = Vessel.RecordDateTime; // DateSplit[0].ToString(); worksheet.Cells[rowIndex_2, colIndex_2 + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[rowIndex_2, colIndex_2 + 2].Value = Vessel.FO; worksheet.Cells[rowIndex_2, colIndex_2 + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[rowIndex_2, colIndex_2 + 3].Value = Vessel.DO; worksheet.Cells[rowIndex_2, colIndex_2 + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // DATE REPORTED worksheet.Cells[2, colIndex_2 + 1].Value = "Reported"; worksheet.Cells[2, colIndex_2 + 1].Style.Font.Bold = true; worksheet.Cells[2, colIndex_2 + 1].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[2, colIndex_2 + 1].Style.Fill.BackgroundColor.SetColor(Color.AliceBlue); worksheet.Cells[2, colIndex_2 + 1, 3, colIndex_2 + 1].Merge = true; //Merge columns start and end range worksheet.Cells[2, colIndex_2 + 1, 3, colIndex_2 + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Alignment is center // FO worksheet.Cells[1, colIndex_2 + 2].Style.WrapText = true; worksheet.Cells[2, colIndex_2 + 2].Value = "FO Difference [MT]"; worksheet.Cells[2, colIndex_2 + 2].Style.Font.Bold = true; worksheet.Cells[2, colIndex_2 + 2].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[2, colIndex_2 + 2].Style.Fill.BackgroundColor.SetColor(Color.AliceBlue); worksheet.Cells[2, colIndex_2 + 2, 3, colIndex_2 + 2].Merge = true; //Merge columns start and end range worksheet.Cells[2, colIndex_2 + 2, 3, colIndex_2 + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Alignment is center // DO worksheet.Cells[1, colIndex_2 + 3].Style.WrapText = true; worksheet.Cells[2, colIndex_2 + 3].Value = "DO Difference [MT]"; worksheet.Cells[2, colIndex_2 + 3].Style.Font.Bold = true; worksheet.Cells[2, colIndex_2 + 3].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[2, colIndex_2 + 3].Style.Fill.BackgroundColor.SetColor(Color.AliceBlue); worksheet.Cells[2, colIndex_2 + 3, 3, colIndex_2 + 3].Merge = true; //Merge columns start and end range worksheet.Cells[2, colIndex_2 + 3, 3, colIndex_2 + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Alignment is center // Colour cells when values are out of range worksheet.Cells[rowIndex_2, colIndex_2 + 2].Style.Numberformat.Format = "0.00"; if (Vessel.FO >= Math.Abs(AcceptableRangeFO) || Vessel.FO <= -Math.Abs(AcceptableRangeFO)) { worksheet.Cells[rowIndex_2, colIndex_2 + 2].Style.Font.Color.SetColor(Color.Red); } worksheet.Cells[rowIndex_2, colIndex_2 + 3].Style.Numberformat.Format = "0.00"; if (Vessel.DO >= Math.Abs(AcceptableRangeDO) || Vessel.DO <= -Math.Abs(AcceptableRangeDO)) { worksheet.Cells[rowIndex_2, colIndex_2 + 3].Style.Font.Color.SetColor(Color.Red); } BunkerReport.Save(); } }
//================================================================ // Creates the final Database and Output Files based on User Info //================================================================ public void DataBaseHandling() { BunkerReportHandling NewHandlingTools = new BunkerReportHandling(); // WRITE DATABASE IN .CSV if (ActionTwo == true) { PathString = BunkerDatabaseDirectoryPath + @"\Database\"; // If the fileName is defined by the user in textBox4 if (DatabaseFileNameState) { if (Weekly) { string pathWrite = PathString + DatabaseFileName + @".xlsx"; BunkerDatabaseFilePathWeekly = pathWrite; ResultsFilePathWeekly = PathString + DatabaseFileName + @".txt"; Console.WriteLine("BunkerDatabaseFilePath {0}", BunkerDatabaseFilePathWeekly); Console.WriteLine("ResultsFilePathWeekly {0}", ResultsFilePathWeekly); } if (NonWeekly) { string pathWrite = PathString + DatabaseFileName + @".xlsx"; //@".csv";// @"\BunkerReportDatabaseWrite.csv"; BunkerDatabaseFilePathMonthly = pathWrite; ResultsFilePathMonthly = PathString + DatabaseFileName + @".txt"; } } else { // CREATE NEW FILES if (Weekly) { string pathWrite = PathString + CreateNewDatabase + @"Weekly" + @".xlsx";// @"\BunkerReportDatabaseWrite.csv"; BunkerDatabaseFilePathWeekly = pathWrite; ResultsFilePathWeekly = PathString + CreateNewDatabase + @"Weekly" + @".txt"; } if (NonWeekly) { string pathWrite = PathString + CreateNewDatabase + @"Monthly" + @".xlsx";// @"\BunkerReportDatabaseWrite.csv"; BunkerDatabaseFilePathMonthly = pathWrite; ResultsFilePathMonthly = PathString + CreateNewDatabase + @"Monthly" + @".txt"; } } } if (ActionThree == true) { PathString = BunkerDatabaseDirectoryPath + @"\"; string pathWrite = PathString + DatabaseFileName + @".xlsx";// @"\BunkerReportDatabaseWrite.csv"; if (Weekly) { BunkerDatabaseFilePathWeekly = pathWrite; ResultsFilePathWeekly = PathString + DatabaseFileName + @".txt"; Console.WriteLine(BunkerDatabaseFilePathWeekly); } if (NonWeekly) { BunkerDatabaseFilePathMonthly = pathWrite; ResultsFilePathMonthly = PathString + DatabaseFileName + @".txt"; Console.WriteLine(BunkerDatabaseFilePathMonthly); } //Console.WriteLine(PathString); } if (!File.Exists(BunkerDatabaseFilePathWeekly) && Weekly) { //Console.WriteLine("File already exists. Oups!"); System.IO.Directory.CreateDirectory(PathString); Data.State = true; // Created Console.WriteLine(" ================================================================"); Console.WriteLine(" CREATE NEW EXCEL FILE: WEEKLY"); Console.WriteLine(" ================================================================"); Console.WriteLine(); if (Weekly) { //WEEKLY NewHandlingTools.NewBunkerReport(BunkerDatabaseFilePathWeekly); using (FileStream fs = File.Create(ResultsFilePathWeekly)) { Byte[] info = new UTF8Encoding(true).GetBytes("STATUS: Weekly Bunker Report Overview"); // Add some information to the file. fs.Write(info, 0, info.Length); } } } else { Data.State = false; } if (!File.Exists(BunkerDatabaseFilePathMonthly) && NonWeekly) { Console.WriteLine("File already exists. Oups!"); System.IO.Directory.CreateDirectory(PathString); Data.State = true; // Created Console.WriteLine(" ================================================================"); Console.WriteLine(" CREATE NEW EXCEL FILE: MONTHLY/YEARLY"); Console.WriteLine(" ================================================================"); Console.WriteLine(); if (NonWeekly) { //OVERVIEW NewHandlingTools.OverviewSheet(BunkerDatabaseFilePathMonthly, CurrentFleet); using (FileStream fs = File.Create(ResultsFilePathMonthly)) { Byte[] info = new UTF8Encoding(true).GetBytes("STATUS: Bunker Report Monthly/Yearly Overview"); // Add some information to the file. fs.Write(info, 0, info.Length); } } } }