Exemplo n.º 1
0
        //================================================================
        // 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();
            }
        }
Exemplo n.º 2
0
        //================================================================
        // 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);
                    }
                }
            }
        }