//================================================================
// 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);
}
}
}
}