static void AddChart()//В этом методе из полученных даных я создаю диаграммы
{
List <string> well = new List <string>();
GetWell(ref well);
double deb = 0;
int f = 1;
using (ExcelPackage excelPackage = new ExcelPackage())
{
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet 1");
ExcelLineChart lineChart = worksheet.Drawings.AddChart("lineChart", eChartType.Line) as ExcelLineChart;
lineChart.Title.Text = "Дебит нефти";
List <string> Q_OIL = new List <string>();
foreach (string w in well)
{
GetQOilWell(w, ref Q_OIL);
for (int i = 0; i < Q_OIL.Count; i++)
{
deb = deb + Convert.ToDouble(Q_OIL[i].Replace('.', ','));
worksheet.Cells[i + 1, f].Value = deb;
worksheet.Cells[i + 1, 5].Value = i + 1;
}
var range1 = worksheet.Cells[1, f, Q_OIL.Count, f];
var range2 = worksheet.Cells[1, 5, Q_OIL.Count, 5];
var serias = lineChart.Series.Add(range1, range2);
serias.Header = w;
serias.TrendLines.Add(eTrendLine.Linear);
f++;
Q_OIL.Clear();
deb = 0;
}
deb = 0;
ExcelLineChart lineChart1 = worksheet.Drawings.AddChart("lineChart1", eChartType.Line) as ExcelLineChart;
lineChart1.Title.Text = "Дебит жидкости";
List <string> Q_FLUID = new List <string>();
foreach (string w in well)
{
GetFluidWell(w, ref Q_FLUID);
for (int i = 0; i < Q_FLUID.Count; i++)
{
deb = deb + Convert.ToDouble(Q_FLUID[i].Replace('.', ','));
worksheet.Cells[i + 1, f + 1].Value = deb;
worksheet.Cells[i + 1, 10].Value = i + 1;
}
var range1 = worksheet.Cells[1, f + 1, Q_FLUID.Count, f + 1];
var range2 = worksheet.Cells[1, 10, Q_FLUID.Count, 10];
var serias1 = lineChart1.Series.Add(range1, range2);
serias1.Header = w;
serias1.TrendLines.Add(eTrendLine.Exponential);//Почитал про линейные тренды, думал взять логарифмическую или экспоненциальную , но так как при логарафмической он показывает возможность отрицательных данных, остановился на экспоненциальной
f++;
Q_FLUID.Clear();
deb = 0;
}
FileInfo fi = new FileInfo(@"C:\Users\Мася кун\Desktop\2.xlsx");//не смог понять как сохранять файл в папку проекта, так что сохранял себе на рабочий стол
excelPackage.SaveAs(fi);
}
}
static void AddChart()//В этом методе из полученных даных я создаю диаграммы
{
List <string> well = new List <string>();
GetWell(ref well);
double deb = 0;
int f = 1;
using (ExcelPackage excelPackage = new ExcelPackage())
{
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet 1");
ExcelLineChart lineChart = worksheet.Drawings.AddChart("lineChart", eChartType.Line) as ExcelLineChart;
lineChart.Title.Text = "Дебит нефти";
List <string> Q_OIL = new List <string>();
foreach (string w in well)
{
GetQOilWell(w, ref Q_OIL);
for (int i = 0; i < Q_OIL.Count; i++)
{
worksheet.Cells[i + 1, f].Value = Convert.ToDouble(Q_OIL[i].Replace('.', ','));
worksheet.Cells[i + 1, 5].Value = i + 1;
}
var range1 = worksheet.Cells[1, f, Q_OIL.Count, f];
var range2 = worksheet.Cells[1, 5, Q_OIL.Count, 5];
var serias = lineChart.Series.Add(range1, range2);
serias.Header = w;
serias.TrendLines.Add(eTrendLine.Polynomial);//Выбрал полиномиальную, так как данные попеременно возрастают и убывают
f++;
Q_OIL.Clear();
deb = 0;
}
deb = 0;
ExcelLineChart lineChart1 = worksheet.Drawings.AddChart("lineChart1", eChartType.Line) as ExcelLineChart;
lineChart1.Title.Text = "Дебит жидкости";
List <string> Q_FLUID = new List <string>();
foreach (string w in well)
{
GetFluidWell(w, ref Q_FLUID);
for (int i = 0; i < Q_FLUID.Count; i++)
{
worksheet.Cells[i + 1, f + 1].Value = Convert.ToDouble(Q_FLUID[i].Replace('.', ','));
worksheet.Cells[i + 1, 10].Value = i + 1;
}
var range1 = worksheet.Cells[1, f + 1, Q_FLUID.Count, f + 1];
var range2 = worksheet.Cells[1, 10, Q_FLUID.Count, 10];
var serias1 = lineChart1.Series.Add(range1, range2);
serias1.Header = w;
serias1.TrendLines.Add(eTrendLine.Polynomial);//Выбрал полиномиальную, так как данные попеременно возрастают и убывают
Q_FLUID.Clear();
deb = 0;
}
FileInfo fi = new FileInfo(@"C:\Users\Мася кун\Desktop\2.xlsx");//не смог понять как сохранять файл в папку проекта, так что сохранял себе на рабочий стол
excelPackage.SaveAs(fi);
}
}
private void AddPatientDetailsSGRQSeries(ExcelLineChart chart)
{
var seriesLabel = ExcelRange.GetAddress(2, 6, _lastSGRQChartDataRowIndex, 6);
var addressesColumn = new string[] { "B", "C", "D", "E" };
for (var cursor = 0; cursor < 4; cursor++)
{
var fromColumn = cursor + 2;
chart.Series.Add(ExcelRange.GetAddress(2, fromColumn, _lastSGRQChartDataRowIndex, fromColumn), seriesLabel);
var addressLetter = addressesColumn[cursor];
chart.Series[cursor].Header = _worksheet.Cells[addressLetter + "1"].GetValue <string>();
}
}
public IActionResult ExcelExport2()
{
//create a new ExcelPackage
using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a WorkSheet
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet 1");
//fill cell data with a loop, note that row and column indexes start at 1
Random rnd = new Random();
for (int i = 2; i <= 11; i++)
{
worksheet.Cells[1, i].Value = "Value " + (i - 1);
worksheet.Cells[2, i].Value = rnd.Next(5, 25);
worksheet.Cells[3, i].Value = rnd.Next(5, 25);
}
worksheet.Cells[2, 1].Value = "Age 1";
worksheet.Cells[3, 1].Value = "Age 2";
//create a new piechart of type Line
ExcelLineChart lineChart = worksheet.Drawings.AddChart("lineChart", eChartType.Line) as ExcelLineChart;
//set the title
lineChart.Title.Text = "LineChart Example";
//create the ranges for the chart
var rangeLabel = worksheet.Cells["B1:K1"];
var range1 = worksheet.Cells["B2:K2"];
var range2 = worksheet.Cells["B3:K3"];
//add the ranges to the chart
lineChart.Series.Add(range1, rangeLabel);
lineChart.Series.Add(range2, rangeLabel);
//set the names of the legend
lineChart.Series[0].Header = worksheet.Cells["A2"].Value.ToString();
lineChart.Series[1].Header = worksheet.Cells["A3"].Value.ToString();
//position of the legend
lineChart.Legend.Position = eLegendPosition.Right;
//size of the chart
lineChart.SetSize(600, 300);
//add the chart at cell B6
lineChart.SetPosition(5, 0, 1, 0);
Byte[] bin = excelPackage.GetAsByteArray();
return(File(bin, "application/octet-stream", "CycleTimeData.xlsx"));
}
}
public static void SetSeriesStyle(this ExcelLineChart chart, ExcelChartSerie series, Color color, decimal?thickness = null)
{
if (thickness < 0)
{
throw new ArgumentOutOfRangeException("thickness");
}
var i = 0;
var found = false;
foreach (var s in chart.Series)
{
if (s == series)
{
found = true;
break;
}
++i;
}
if (!found)
{
throw new InvalidOperationException("series not found.");
}
//Get the nodes
var nsm = chart.WorkSheet.Drawings.NameSpaceManager;
var nschart = nsm.LookupNamespace("c");
var nsa = nsm.LookupNamespace("a");
var node = chart.ChartXml.SelectSingleNode(@"c:chartSpace/c:chart/c:plotArea/c:lineChart/c:ser[c:idx[@val='" + i.ToString(CultureInfo.InvariantCulture) + "']]", nsm);
var doc = chart.ChartXml;
//Add the solid fill node
var spPr = doc.CreateElement("c:spPr", nschart);
var ln = spPr.AppendChild(doc.CreateElement("a:ln", nsa));
if (thickness.HasValue)
{
var w = ln.Attributes.Append(doc.CreateAttribute("w"));
w.Value = Math.Round(thickness.Value * 12700).ToString(CultureInfo.InvariantCulture);
var cap = ln.Attributes.Append(doc.CreateAttribute("cap"));
cap.Value = "rnd";
}
var solidFill = ln.AppendChild(doc.CreateElement("a:solidFill", nsa));
var srgbClr = solidFill.AppendChild(doc.CreateElement("a:srgbClr", nsa));
var valattrib = srgbClr.Attributes.Append(doc.CreateAttribute("val"));
//Set the color
valattrib.Value = color.ToHex().Substring(1);
node.AppendChild(spPr);
}
private void AddRaceProgress(ExcelWorksheet sheet, SessionSummary sessionSummary)
{
int maxLaps = sessionSummary.Drivers.Select(x => x.Laps.Count).Max();
List <Driver> orderedDrivers = sessionSummary.Drivers.OrderBy(x => x.Laps.LastOrDefault()?.LapEndSnapshot.PlayerData.Position).ToList();
int startRow = 100;
int startColumn = 1;
sheet.Cells[startRow + 1, startColumn].Value = "Start";
GenerateNumberColumn(sheet, new ExcelCellAddress(startRow + 2, startColumn), maxLaps);
GenerateDriversRow(sheet, new ExcelCellAddress(startRow, startColumn + 1), orderedDrivers.Select(x => x.DriverName));
ExcelCellAddress startAddress = new ExcelCellAddress(startRow + 1, startColumn + 1);
orderedDrivers.ForEach(
x =>
{
GenerateLapsPositionColumn(sheet, startAddress, x.Laps, maxLaps);
startAddress = new ExcelCellAddress(startAddress.Row, startAddress.Column + 1);
});
ExcelLineChart chart = (ExcelLineChart)sheet.Drawings.AddChart("Race Progress", eChartType.LineMarkers);
chart.SetPosition(0, 0, 0, 0);
int currentColumn = 2;
orderedDrivers.ForEach(
x =>
{
ExcelLineChartSerie series = (ExcelLineChartSerie)chart.Series.Add(ExcelCellBase.GetAddress(startRow + 1, currentColumn, startRow + 1 + maxLaps, currentColumn), ExcelCellBase.GetAddress(startRow + 1, 1, startRow + 1 + maxLaps, 1));
series.Header = x.DriverName;
currentColumn++;
});
chart.ShowDataLabelsOverMaximum = false;
chart.DataLabel.ShowValue = true;
chart.ShowHiddenData = true;
chart.Axis[1].MinValue = 0;
chart.Axis[1].TickLabelPosition = eTickLabelPosition.NextTo;
chart.Axis[1].MajorUnit = 1;
chart.Axis[1].MinorUnit = 1;
chart.Axis[1].Orientation = eAxisOrientation.MaxMin;
chart.Axis[1].MaxValue = orderedDrivers.Count;
chart.SetSize(70 * maxLaps, 30 * orderedDrivers.Count);
chart.Axis[0].MajorUnit = 1;
chart.Axis[0].MinorUnit = 1;
chart.Title.Text = "Race Progress";
}
//gavdcodeend 03
//gavdcodebegin 04
public static void ExcelEpplusInsertLineChart()
{
FileInfo myFileInfo = new FileInfo(@"C:\Temporary\ExcelEPPlus01.xlsx");
using (ExcelPackage excelPackage = new ExcelPackage(myFileInfo))
{
// Create a WorkSheet
ExcelWorksheet myWorksheet =
excelPackage.Workbook.Worksheets.Add("LineChartSheet");
// Fill cell data with a loop. The row and column indexes start at 1
Random rnd = new Random();
for (int counter = 2; counter <= 11; counter++)
{
myWorksheet.Cells[1, counter].Value = "Value " + (counter - 1);
myWorksheet.Cells[2, counter].Value = rnd.Next(5, 25);
myWorksheet.Cells[3, counter].Value = rnd.Next(5, 25);
}
myWorksheet.Cells[2, 1].Value = "Age 1";
myWorksheet.Cells[3, 1].Value = "Age 2";
// Create a new chart of type Line
ExcelLineChart myLineChart = myWorksheet.Drawings.AddChart(
"lineChart", eChartType.Line) as ExcelLineChart;
myLineChart.Title.Text = "LineChart Example";
// Create and add the ranges for the chart
ExcelRange rangeLabel = myWorksheet.Cells["B1:K1"];
ExcelRange range1 = myWorksheet.Cells["B2:K2"];
ExcelRange range2 = myWorksheet.Cells["B3:K3"];
myLineChart.Series.Add(range1, rangeLabel);
myLineChart.Series.Add(range2, rangeLabel);
// Set the properties of the chart
myLineChart.Series[0].Header = myWorksheet.Cells["A2"].Value.ToString();
myLineChart.Series[1].Header = myWorksheet.Cells["A3"].Value.ToString();
myLineChart.Legend.Position = eLegendPosition.Right;
myLineChart.SetSize(600, 300);
myLineChart.SetPosition(5, 0, 1, 0);
excelPackage.SaveAs(myFileInfo);
}
}
public static void Write(string p_strPath, Schedule sch, string elapsed, Dictionary <int, double> generationFitness, double[] finalScores, Context context)
{
using (ExcelPackage xlPackage_new = new ExcelPackage())
{
ExcelWorksheet ws_scheduling = xlPackage_new.Workbook.Worksheets.Add("Scheduling");
ExcelWorksheet ws_info = xlPackage_new.Workbook.Worksheets.Add("Information");
ExcelWorksheet ws_workload = xlPackage_new.Workbook.Worksheets.Add("Workloads");
#region Scheduling
ws_scheduling.Cells[1, 1].Value = "Student";
ws_scheduling.Cells[1, 2].Value = "Supervisor";
ws_scheduling.Cells[1, 3].Value = "President";
ws_scheduling.Cells[1, 4].Value = "Secretary";
ws_scheduling.Cells[1, 5].Value = "Member";
ws_scheduling.Cells[1, 6].Value = "Examiner";
ws_scheduling.Cells[1, 7].Value = "Course";
for (int j = 1; j <= 7; j++)
{
var cell = ws_scheduling.Cells[1, j];
cell.Style.Border.Bottom.Style = ExcelBorderStyle.Thick;
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 14;
}
string author = "Szilvia Erdős";
int i = 2;
foreach (FinalExam exam in sch.FinalExams)
{
ws_scheduling.Cells[i, 1].Value = exam.Student.Name;
double studentScore = sch.Details[exam.Id].StudentScore;
if (studentScore > 0)
{
ws_scheduling.Cells[i, 1].AddComment(sch.Details[exam.Id].StudentComment, author);
ws_scheduling.Cells[i, 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws_scheduling.Cells[i, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, GetGreen(studentScore), 0));
}
ws_scheduling.Cells[i, 2].Value = exam.Supervisor.Name;
double supervisorScore = sch.Details[exam.Id].SupervisorScore;
if (supervisorScore > 0)
{
ws_scheduling.Cells[i, 2].AddComment(sch.Details[exam.Id].SupervisorComment, author);
ws_scheduling.Cells[i, 2].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws_scheduling.Cells[i, 2].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, GetGreen(supervisorScore), 0));
}
ws_scheduling.Cells[i, 3].Value = exam.President.Name;
double presidentScore = sch.Details[exam.Id].PresidentScore;
if (presidentScore > 0)
{
ws_scheduling.Cells[i, 3].AddComment(sch.Details[exam.Id].PresidentComment, author);
ws_scheduling.Cells[i, 3].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws_scheduling.Cells[i, 3].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, GetGreen(presidentScore), 0));
}
ws_scheduling.Cells[i, 4].Value = exam.Secretary.Name;
double secretaryScore = sch.Details[exam.Id].SecretaryScore;
if (secretaryScore > 0)
{
ws_scheduling.Cells[i, 4].AddComment(sch.Details[exam.Id].SecretaryComment, author);
ws_scheduling.Cells[i, 4].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws_scheduling.Cells[i, 4].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, GetGreen(secretaryScore), 0));
}
ws_scheduling.Cells[i, 5].Value = exam.Member.Name;
double memberScore = sch.Details[exam.Id].MemberScore;
if (memberScore > 0)
{
ws_scheduling.Cells[i, 5].AddComment(sch.Details[exam.Id].MemberComment, author);
ws_scheduling.Cells[i, 5].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws_scheduling.Cells[i, 5].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, GetGreen(memberScore), 0));
}
ws_scheduling.Cells[i, 6].Value = exam.Examiner.Name;
double examinerScore = sch.Details[exam.Id].ExaminerScore;
if (examinerScore > 0)
{
ws_scheduling.Cells[i, 6].AddComment(sch.Details[exam.Id].ExaminerComment, author);
ws_scheduling.Cells[i, 6].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws_scheduling.Cells[i, 6].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, GetGreen(examinerScore), 0));
}
ws_scheduling.Cells[i, 7].Value = exam.Student.ExamCourse.Name;
ws_scheduling.Cells[i, 8].Value = exam.Id;
if (i % 10 == 1)
{
for (int j = 1; j <= 7; j++)
{
var cell = ws_scheduling.Cells[i, j];
cell.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
}
}
if (i % 5 == 1 && i % 10 != 1)
{
for (int j = 1; j <= 7; j++)
{
var cell = ws_scheduling.Cells[i, j];
cell.Style.Border.Bottom.Style = ExcelBorderStyle.Dotted;
}
}
i++;
}
ws_scheduling.Cells.AutoFitColumns();
#endregion
#region Information
if (Parameters.GetInfo)
{
int rowGen = 2;
ws_info.Cells[1, 1].Value = "Generation number";
ws_info.Cells[1, 2].Value = "Actual best fitness";
ws_info.Cells[1, 4].Value = "Best fitness";
foreach (var element in generationFitness)
{
ws_info.Cells[rowGen, 1].Value = element.Key;
ws_info.Cells[rowGen, 2].Value = element.Value;
rowGen++;
}
ws_info.Cells[1, 5].Value = generationFitness.Values.Last();
ws_info.Cells[2, 4].Value = "Min size of population";
ws_info.Cells[3, 4].Value = "Max size of population";
ws_info.Cells[4, 4].Value = "Stagnation termination";
ws_info.Cells[2, 5].Value = Parameters.MinPopulationSize;
ws_info.Cells[3, 5].Value = Parameters.MaxPopulationSize;
ws_info.Cells[4, 5].Value = Parameters.StagnationTermination;
ws_info.Cells[6, 4].Value = "Scores";
int row = 7;
foreach (FieldInfo info in typeof(Scores).GetFields().Where(x => x.IsStatic && x.IsLiteral))
{
ws_info.Cells[row, 4].Value = info.Name;
ws_info.Cells[row, 5].Value = info.GetValue(info);
ws_info.Cells[row, 6].Value = finalScores[row - 7];
row++;
}
ws_info.Cells[row + 1, 4].Value = "Time elapsed";
ws_info.Cells[row + 1, 5].Value = elapsed;
rowGen--;
ExcelLineChart lineChart = ws_info.Drawings.AddChart("lineChart", eChartType.Line) as ExcelLineChart;
lineChart.Title.Text = "Fitness alakulása a generációk előrehaladtával";
var rangeLabel = ws_info.Cells["A2:A" + rowGen];
var range1 = ws_info.Cells["B2:B" + rowGen];
lineChart.Series.Add(range1, rangeLabel);
lineChart.Series[0].Header = ws_info.Cells["A1"].Text;
lineChart.Legend.Remove();
int width = (rowGen * 20 > 300) ? rowGen * 20 : 300;
lineChart.SetSize(width, 500);
lineChart.SetPosition(1, 0, 7, 0);
}
ws_info.Cells.AutoFitColumns();
#endregion
#region Workload
int[] presidentWorkloads = new int[context.Presidents.Length];
int[] secretaryWorkloads = new int[context.Secretaries.Length];
int[] memberWorkloads = new int[context.Members.Length];
foreach (FinalExam fi in sch.FinalExams)
{
//presidentWorkloads[Array.FindIndex(context.Presidents, item => item == fi.President)]++;
//secretaryWorkloads[Array.FindIndex(context.Secretaries, item => item == fi.Secretary)]++;
//memberWorkloads[Array.FindIndex(context.Members, item => item == fi.Member)]++;
presidentWorkloads[Array.IndexOf(context.Presidents, fi.President)]++;
secretaryWorkloads[Array.IndexOf(context.Secretaries, fi.Secretary)]++;
memberWorkloads[Array.IndexOf(context.Members, fi.Member)]++;
}
ws_workload.Cells[1, 1].Value = "Presidents";
ws_workload.Cells[1, 2].Value = "Nr of exams";
ws_workload.Cells[1, 3].Value = "Secretaries";
ws_workload.Cells[1, 4].Value = "Nr of exams";
ws_workload.Cells[1, 5].Value = "Members";
ws_workload.Cells[1, 6].Value = "Nr of exams";
for (int j = 0; j < context.Presidents.Length; j++)
{
ws_workload.Cells[j + 2, 1].Value = context.Presidents[j].Name;
ws_workload.Cells[j + 2, 2].Value = presidentWorkloads[j];
}
for (int j = 0; j < context.Secretaries.Length; j++)
{
ws_workload.Cells[j + 2, 3].Value = context.Secretaries[j].Name;
ws_workload.Cells[j + 2, 4].Value = secretaryWorkloads[j];
}
for (int j = 0; j < context.Members.Length; j++)
{
ws_workload.Cells[j + 2, 5].Value = context.Members[j].Name;
ws_workload.Cells[j + 2, 6].Value = memberWorkloads[j];
}
ws_workload.Cells.AutoFitColumns();
#endregion
if (File.Exists(p_strPath))
{
File.Delete(p_strPath);
}
FileStream objFileStrm = File.Create(p_strPath);
objFileStrm.Close();
File.WriteAllBytes(p_strPath, xlPackage_new.GetAsByteArray());
}
}
private void DoWorksheetCalcs(ExcelWorksheet ws, int numOfLines, DateTime startDt, DateTime endDt)
{
//gerar gráfico
//1º registo | ultimo registo
var cell = ws.Cells["D8"];
//1º registo
ws.Cells["F8"].Formula = "=R8"; //dt
ws.Cells["F8"].Style.Numberformat.Format = "dd-MM-yyyy hh:mm:ss";
ws.Cells["D8"].Formula = "=S8"; //valor
//ULTIMO REGISTO
ws.Cells["F9"].Formula = "=R" + (numOfLines - 1).ToString(); //dt
ws.Cells["F9"].Style.Numberformat.Format = "dd-MM-yyyy hh:mm:ss";
ws.Cells["D9"].Formula = "=S" + (numOfLines - 1).ToString(); //valor
List <string> auxL = new List <string> {
"D", "E", "F"
};
for (int i = 8; i <= 9; i++)
{
foreach (string letra in auxL)
{
cell = ws.Cells[letra + i.ToString()];
this.ApplyBackColorConditionFormating(ws, cell, "=E" + i.ToString() + "=\"Conforme\"", Color.PaleGreen);
this.ApplyBackColorConditionFormating(ws, cell, "=E" + i.ToString() + "=\"Classe 2\"", Color.Yellow);
this.ApplyBackColorConditionFormating(ws, cell, "=E" + i.ToString() + "=\"Classe 3\"", Color.Orange);
this.ApplyBackColorConditionFormating(ws, cell, "=E" + i.ToString() + "=\"Não Conforme\"", Color.Red);
}
}
//total
ws.Cells["D10"].Formula = "=COUNT(S8:S" + (numOfLines - 1).ToString() + ")";
//medias MOVEIS
ws.Cells["I8"].Formula = "=AVERAGE(S8:S" + (numOfLines - 1).ToString() + ")";
ws.Cells["I9"].Formula = "=MIN(S8:S" + (numOfLines - 1).ToString() + ")";
ws.Cells["I10"].Formula = "=MAX(S8:S" + (numOfLines - 1).ToString() + ")";
ws.Cells["I11"].Formula = "=ABS(I10-I9)"; //range
//INDEX(F3: F11, MATCH(MAX(C3: C11), C3: C11, 0))
ws.Cells["K9"].Formula = "=INDEX(R8:R" + (numOfLines - 1).ToString() + ",MATCH(I9,S8:S" + (numOfLines - 1).ToString() + ",0))";
ws.Cells["K9"].Style.Numberformat.Format = "hh:mm:ss";
ws.Cells["K10"].Formula = "=INDEX(R8:R" + (numOfLines - 1).ToString() + ",MATCH(I10,S8:S" + (numOfLines - 1).ToString() + ",0))";
ws.Cells["K10"].Style.Numberformat.Format = "hh:mm:ss";
auxL = new List <string> {
"I", "J", "K"
};
for (int i = 8; i <= 10; i++)
{
foreach (string letra in auxL)
{
cell = ws.Cells[letra + i.ToString()];
this.ApplyBackColorConditionFormating(ws, cell, "=J" + i.ToString() + "=\"Conforme\"", Color.PaleGreen);
this.ApplyBackColorConditionFormating(ws, cell, "=J" + i.ToString() + "=\"Classe 2\"", Color.Yellow);
this.ApplyBackColorConditionFormating(ws, cell, "=J" + i.ToString() + "=\"Classe 3\"", Color.Orange);
this.ApplyBackColorConditionFormating(ws, cell, "=J" + i.ToString() + "=\"Não Conforme\"", Color.Red);
}
}
//pontos fora de especificcao
ws.Cells["N8"].Formula = "=COUNTIF(T8:T" + (numOfLines - 1).ToString() + ",\"Conforme\")";
ws.Cells["N9"].Formula = "=COUNTIF(T8:T" + (numOfLines - 1).ToString() + ",\"Classe 2\")";
ws.Cells["N10"].Formula = "=COUNTIF(T8:T" + (numOfLines - 1).ToString() + ",\"Classe 3\")";
ws.Cells["N11"].Formula = "=COUNTIF(T8:T" + (numOfLines - 1).ToString() + ",\"Não Conforme\")";
//classificacao
cell = ws.Cells["D11"];
cell.Formula = "=IF(N11>0,\"Não Conforme\",IF(OR(N10>=1,N9>1),\"Classe 3\", IF(N9=1,\"Classe 2\",\"Conforme\")))";
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.Font.Size = 12;
cell.Style.Font.Bold = true;
//Green
this.ApplyForeColorConditionFormating(ws, cell, "=D11=\"Conforme\"", Color.Green);
//red
this.ApplyForeColorConditionFormating(ws, cell, "=D11=\"Não Conforme\"", Color.Red);
//DarkOrange
this.ApplyForeColorConditionFormating(ws, cell, "=D11=\"Classe 3\"", Color.DarkOrange);
//orange
this.ApplyForeColorConditionFormating(ws, cell, "=D11=\"Classe 2\"", Color.Orange);
//"=IF(AND(I" + (8 + i).ToString() + " >= O8,I" + (8 + i).ToString() + " <= P8),\"Conforme\", IF(AND(I" + (8 + i).ToString() + " >= O9,I" + (8 + i).ToString() + " < P9),\"Classe 2\", IF(AND(I" + (8 + i).ToString() + " >= O10, I" + (8 + i).ToString() + " < P10),\"Classe 3\",\"Não Conforme\")))";
cell = ws.Cells["R6:T" + (numOfLines - 1).ToString()];
SetBorderCells(cell, ExcelBorderStyle.Thin); //border around table
cell.Style.Border.BorderAround(ExcelBorderStyle.Medium);
//add chart
//create a new piechart of type Line
ExcelLineChart lineChart = ws.Drawings.AddChart("lineChart", eChartType.Line) as ExcelLineChart;
if (!string.IsNullOrWhiteSpace(txtGraphTitle.Value))
{
lineChart.Title.Text = txtGraphTitle.Value;
}
else
{
lineChart.Title.Text = this.Maquina.Nome + " - Produção de " + startDt.ToString("dd/MM/yyyy HH:mm") + " a " + endDt.ToString("dd/MM/yyyy HH:mm");
}
lineChart.Title.Font.Size = 14;
lineChart.Title.Font.Bold = true;
lineChart.Series.Add(ws.Cells["S8:S" + (numOfLines - 1).ToString()], ws.Cells["R8:R" + (numOfLines - 1).ToString()]);
//size of the chart
lineChart.SetSize(1300, 500);
//set the names of the legend
lineChart.Series[0].Header = "Diâmetro";
//position of the legend
lineChart.Legend.Position = eLegendPosition.Bottom;
//add the chart at cell B6
lineChart.SetPosition(13, 0, 0, 25);
#region Alertas de Produção
try
{
int line = 42;
bool firstRow = true;
StringBuilder strQuery = new StringBuilder();
strQuery.Append("SELECT Alertas.Id, TipoAlerta.Tipo, Alertas.Diametro, Alertas.DataHora FROM Alertas ");
strQuery.Append("INNER JOIN TipoAlerta ON Alertas.IdAlerta = TipoAlerta.Id ");
strQuery.Append("WHERE Alertas.IdMaquina = @IdMaquina AND Alertas.DataHora BETWEEN @DATA1 AND @DATA2 ");
strQuery.Append("ORDER BY Alertas.Id");
if (this.Maquina == null)
{
throw new Exception("máquina não encontrada!");
}
else
{
using (SqlConnection sqlConn = new SqlConnection(VARS.ConnectionString))
using (SqlCommand sqlCmd = new SqlCommand(strQuery.ToString(), sqlConn))
{
sqlCmd.Parameters.Add("@IdMaquina", SqlDbType.TinyInt).Value = Convert.ToInt32(this.Maquina.Id);
sqlCmd.Parameters.Add("@DATA1", SqlDbType.DateTime).Value = startDt;
sqlCmd.Parameters.Add("@DATA2", SqlDbType.DateTime).Value = endDt;
sqlConn.Open();
using (SqlDataReader dr = sqlCmd.ExecuteReader())
while (dr.Read())
{
if (firstRow)
#region 1º registo
{
cell = ws.Cells["A40:O40"];
this.SetCellStyle(cell, "Alertas de Produção", true, 12, ExcelHorizontalAlignment.Center);
cell.Merge = true;
this.SetCellStyle(ws.Cells["A41"], "ID", true, 12, ExcelHorizontalAlignment.Center);
cell = ws.Cells["B41:L41"];
this.SetCellStyle(cell, "Descrição do Alerta", true, 12, ExcelHorizontalAlignment.Center);
cell.Merge = true;
this.SetCellStyle(ws.Cells["M41"], "Diâmetro", true, 12, ExcelHorizontalAlignment.Center);
this.SetCellStyle(ws.Cells["N41"], "Classe", true, 12, ExcelHorizontalAlignment.Center);
this.SetCellStyle(ws.Cells["O41"], "Hora", true, 12, ExcelHorizontalAlignment.Center);
firstRow = false;
}
#endregion
ws.Cells["A" + line.ToString()].Value = Convert.ToInt32(dr[0]);
cell = ws.Cells["B" + line.ToString() + ":L" + line.ToString()];
cell.Value = Convert.ToString(dr[1]);
cell.Merge = true;
cell = ws.Cells["M" + line.ToString()];
cell.Value = Convert.ToDouble(dr[2]);
cell.Style.Numberformat.Format = "0.000";
switch (this.Maquina.Classe.GetClassificacao(Convert.ToDouble(dr[2])))
{
case Classificacoes.Classificacao.Conforme:
ws.Cells["N" + line.ToString()].Value = "Conforme";
break;
case Classificacoes.Classificacao.Classe2:
ws.Cells["N" + line.ToString()].Value = "Classe 2";
break;
case Classificacoes.Classificacao.Classe3:
ws.Cells["N" + line.ToString()].Value = "Classe 3";
break;
case Classificacoes.Classificacao.NaoConforme:
ws.Cells["N" + line.ToString()].Value = "Não Conforme";
break;
}
cell = ws.Cells["O" + line.ToString()];
cell.Value = Convert.ToDateTime(dr[3]);
cell.Style.Numberformat.Format = "hh:mm";
if (line % 2 == 0)
{
this.SetCellsBackcolor(ws.Cells["A" + line.ToString() + ":O" + line.ToString()], Color.LightGray);
}
line++;
}
}
}
if (!firstRow)
{
//fecha a tabela
cell = ws.Cells["A40:O" + (line - 1).ToString()];
SetBorderCells(cell, ExcelBorderStyle.Thin); //border around table
cell.Style.Border.BorderAround(ExcelBorderStyle.Medium);
}
}
catch (Exception ex)
{
Debug.WriteLine("AlertasdeProducao(): " + ex.Message);
}
#endregion
}
void AddChartsWithEPPlus() // Добавляем графики
{
string[] Titles = { "Сила тока", "Напряжение", "Мощность" };
//create a WorkSheet
for (int i = 0; i < 2; i++)
{
if (SelectedData[i]) // Сила тока и напряжение
{
//create a new piechart of type Line
ExcelLineChart lineChart =
Counter.Drawings.AddChart("lineChart" + i, eChartType.Line) as ExcelLineChart;
//set the title
lineChart.Title.Text = Titles[i];
//create the ranges for the chart
var rangeLabel = Counter.Cells["A32:A" + (CountIdnications[0] + 31)]; // Время
var range1 = Counter.Cells[32, 2 + i, CountIdnications[0] + 31, 2 + i]; // Фаза А
var range2 = Counter.Cells[32, 5 + i, CountIdnications[0] + 31, 5 + i]; // Фаза B
var range3 = Counter.Cells[32, 8 + i, CountIdnications[0] + 31, 8 + i]; // Фаза C
//add the ranges to the chart
lineChart.Series.Add(range1, rangeLabel);
lineChart.Series.Add(range2, rangeLabel);
lineChart.Series.Add(range3, rangeLabel);
//set the names of the legend
lineChart.Series[0].Header = Counter.Cells["B30"].Value.ToString();
lineChart.Series[1].Header = Counter.Cells["E30"].Value.ToString();
lineChart.Series[2].Header = Counter.Cells["H30"].Value.ToString();
// Красим легенды
lineChart.SetLineChartColor(0, i, Color.FromArgb(0xFFEC8B));
lineChart.SetLineChartColor(1, i, Color.FromArgb(0x98FB98));
lineChart.SetLineChartColor(2, i, Color.FromArgb(0xFA5050));
//position of the legend
lineChart.Legend.Position = eLegendPosition.Right;
//size of the chart
lineChart.SetSize(600 + i * 170, 500);
//add the chart at cell B6
lineChart.SetPosition(3, 0, 0 + i * 10, 0);
}
}
if (SelectedData[2]) // Мощность
{
//create a new piechart of type Line
ExcelLineChart lineChart =
Counter.Drawings.AddChart("lineChart2", eChartType.Line) as ExcelLineChart;
//set the title
lineChart.Title.Text = Titles[2];
//create the ranges for the chart
var rangeLabel = Counter.Cells["A32:A" + (CountIdnications[0] + 31)]; // Время
var range1 = Counter.Cells["K32:K" + (CountIdnications[0] + 31)]; // Активная
var range2 = Counter.Cells["L32:L" + (CountIdnications[0] + 31)]; // Полная
//add the ranges to the chart
lineChart.Series.Add(range1, rangeLabel);
lineChart.Series.Add(range2, rangeLabel);
//set the names of the legend
lineChart.Series[0].Header = Counter.Cells["K31"].Value.ToString();
lineChart.Series[1].Header = Counter.Cells["L31"].Value.ToString();
//position of the legend
lineChart.Legend.Position = eLegendPosition.Right;
//size of the chart
lineChart.SetSize(780, 500);
//add the chart at cell B6
lineChart.SetPosition(3, 0, 23, 0);
}
CountIdnications.RemoveAt(0);
}
// Save to Excel File Daily Forecast
public FileInfo SaveExcelFileToDisk(DailyViewModel dvm, IWebHostEnvironment environment, DateTime data)
{
using (var pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(data.ToString("dd.MM.yyyy"));
ws.Cells["B5:Z5"].Style.Font.Bold = true;
// Set Values from report in excel file
// Rand 1
ws.Cells["B5"].Style.Font.Bold = true;
ws.Cells["B5"].Value = "Ora";
for (int i = 0; i < 24; i++)
{
ws.Cells[$"{GetCharStringsFromNumber(67 + i)}5"].Value = i + 1;
}
// Rand 2
ws.Cells["B6"].Style.Font.Bold = true;
ws.Cells["B6"].Value = "Prognoza energie [MWh]";
for (int i = 0; i < dvm.ListaPrognozaPerZi.Count; i++)
{
ws.Cells[$"{GetCharStringsFromNumber(67 + i)}6"].Value = dvm.ListaPrognozaPerZi[i].Valoare;
}
// Rand 3
ws.Cells["B7"].Style.Font.Bold = true;
ws.Cells["B7"].Value = "Consum realizat [MWh]";
for (int i = 0; i < dvm.ListaConsumPerZi.Count; i++)
{
ws.Cells[$"{GetCharStringsFromNumber(67 + i)}7"].Value = dvm.ListaConsumPerZi[i].ValueEnergyPlusA / 1000;
}
ws.Cells["A:AZ"].AutoFitColumns();
// Add chart
ExcelLineChart lineChart = ws.Drawings.AddChart("lineChart", eChartType.Line) as ExcelLineChart;
// Set Chart Title
lineChart.Title.Text = "Prognoza energie";
// Create Ranges for Chart
var rangeLabel = ws.Cells["C5:Z5"];
var range1 = ws.Cells["C6:Z6"];
var range2 = ws.Cells["C7:Z7"];
// Add ranges to the chart
lineChart.Series.Add(range1, rangeLabel);
lineChart.Series.Add(range2, rangeLabel);
// Set names of the legend
lineChart.Series[0].Header = ws.Cells["B6"].Value.ToString();
lineChart.Series[1].Header = ws.Cells["B7"].Value.ToString();
// Position of the legend
lineChart.Legend.Position = eLegendPosition.Right;
// Size of the chart
lineChart.SetSize(1200, 400);
// Add the chart at cell ..
lineChart.SetPosition(10, 0, 1, 0);
// Write File to Disk
string excelFileName = $"Prognoza energie zilnic.xlsx";
string wwwPath = environment.WebRootPath;
string filePath = Path.Combine(wwwPath, "Fisiere");
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
filePath = Path.Combine(filePath, excelFileName);
FileInfo fileInfo = new FileInfo(filePath);
pck.SaveAs(fileInfo);
return(fileInfo);
}
}
public async Task <IActionResult> ExcelExport(int workerID, int btnID)
{
var data = await _workerService.ExcelExport(workerID, btnID);
try
{
ExcelPackage.LicenseContext = LicenseContext.Commercial;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var memoryStream = new MemoryStream();
using (ExcelPackage p = new ExcelPackage(memoryStream))
{
// đặt tên người tạo file
p.Workbook.Properties.Author = "Henry Pham";
// đặt tiêu đề cho file
p.Workbook.Properties.Title = "Cycle Time Data";
//Tạo một sheet để làm việc trên đó
p.Workbook.Worksheets.Add("Cycle Time Data");
// lấy sheet vừa add ra để thao tác
ExcelWorksheet ws = p.Workbook.Worksheets["Cycle Time Data"];
// đặt tên cho sheet
ws.Name = "Cycle Time Data";
// fontsize mặc định cho cả sheet
ws.Cells.Style.Font.Size = 11;
// font family mặc định cho cả sheet
ws.Cells.Style.Font.Name = "Calibri";
ws.Cells[1, 1].Value = data.Header1.Type;
ws.Cells[1, 2].Value = data.Header1.Value;
ws.Cells[2, 1].Value = data.Header2.Type;
ws.Cells[2, 2].Value = data.Header2.Value;
ws.Cells[3, 1].Value = data.HeaderForDataDto.PressTime;
ws.Cells[3, 2].Value = data.HeaderForDataDto.CT;
ws.Cells[3, 3].Value = data.HeaderForDataDto.TT;
ws.Cells[3, 4].Value = data.HeaderForDataDto.Standard;
ws.Cells[3, 5].Value = data.HeaderForDataDto.AVG;
int colIndex = 1;
int rowIndex = 3;
// với mỗi item trong danh sách sẽ ghi trên 1 dòng
foreach (var item in data.DataExcelExportDtos)
{
// bắt đầu ghi từ cột 1. Excel bắt đầu từ 1 không phải từ 0
colIndex = 1;
// rowIndex tương ứng từng dòng dữ liệu
rowIndex++;
//gán giá trị cho từng cell
ws.Cells[rowIndex, colIndex++].Value = item.PressTime;
ws.Cells[rowIndex, colIndex++].Value = item.CT;
ws.Cells[rowIndex, colIndex++].Value = item.TT;
ws.Cells[rowIndex, colIndex++].Value = item.Standard;
ws.Cells[rowIndex, colIndex++].Value = item.AVG;
}
//create a new piechart of type Line
ExcelLineChart lineChart = ws.Drawings.AddChart("lineChart", eChartType.Line) as ExcelLineChart;
lineChart.Style = eChartStyle.Style48;
// lineChart.Style = eChartStyle.Style30;
//set the title
lineChart.Title.Text = $"Cycle Time Data - {data.Header1.Value}";
//create the ranges for the chart
var rangeLabel = ws.Cells[4, 1, rowIndex, 1];
var rangeCT = ws.Cells[4, 2, rowIndex, 2];
var rangeTT = ws.Cells[4, 3, rowIndex, 3];
var rangeStandard = ws.Cells[4, 4, rowIndex, 4];
var rangeAVG = ws.Cells[4, 5, rowIndex, 5];
//add the ranges to the chart
lineChart.Series.Add(rangeCT, rangeLabel);
lineChart.Series.Add(rangeTT, rangeLabel);
lineChart.Series.Add(rangeStandard, rangeLabel);
lineChart.Series.Add(rangeAVG, rangeLabel);
//set the names of the legend
lineChart.Series[0].Header = "Cycle Time";
lineChart.Series[1].Header = "TAKT Time";
lineChart.Series[2].Header = "Standard";
lineChart.Series[3].Header = "Average";
Color ctColor = (Color)ColorTranslator.FromHtml("orange");
Color ttColor = (Color)ColorTranslator.FromHtml("#808080");
Color stdColor = (Color)ColorTranslator.FromHtml("#36a2eb");
Color avgColor = (Color)ColorTranslator.FromHtml("#4bc0c0");
lineChart.Series[0].MarkerLineColor = ctColor;
lineChart.Series[1].MarkerLineColor = ttColor;
lineChart.Series[2].MarkerLineColor = stdColor;
lineChart.Series[3].MarkerLineColor = avgColor;
lineChart.Series[0].LineColor = ctColor;
lineChart.Series[1].LineColor = ttColor;
lineChart.Series[2].LineColor = stdColor;
lineChart.Series[3].LineColor = avgColor;
//position of the legend
lineChart.Legend.Position = eLegendPosition.Top;
//size of the chart
lineChart.SetSize(700, 350);
//add the chart at cell B6
lineChart.SetPosition(1, 0, 6, 0);
//Lưu file lại
Byte[] bin = p.GetAsByteArray();
return(File(bin, "application/octet-stream", "CycleTimeData.xlsx"));
}
}
catch (Exception ex)
{
var mes = ex.Message;
Console.Write(mes);
return(NotFound());
}
}
