public static void InvisibleSerie(this ExcelBarChart chart, ExcelChartSerie series)
{
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.");
}
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:barChart/c:ser[c:idx[@val='" + i.ToString(CultureInfo.InvariantCulture) + "']]", nsm);
var doc = chart.ChartXml;
var spPr = doc.CreateElement("c:spPr", nschart);
spPr.AppendChild(doc.CreateElement("a:noFill", nsa));
var ln = spPr.AppendChild(doc.CreateElement("a:ln", nsa));
ln.Attributes.Append(doc.CreateAttribute("w")).Value = "25400";
ln.AppendChild(doc.CreateElement("a:noFill", nsa));
node.AppendChild(spPr);
}
private void CreateChart(ExcelWorksheet ws, ExcelPivotTable pivotTable)
{
ExcelBarChart chart = ws.Drawings.AddChart("crtTiempos", eChartType.BarClustered, pivotTable) as ExcelBarChart;
chart.SetPosition(1, 0, 4, 0);
chart.SetSize(600, 400);
chart.Title.Text = "Tiempos?";
chart.Title.Font.Size = 18;
chart.Title.Font.Bold = true;
chart.GapWidth = 25;
chart.DataLabel.ShowValue = true;
chart.Legend.Remove();
chart.XAxis.MajorTickMark = eAxisTickMark.None;
chart.XAxis.MinorTickMark = eAxisTickMark.None;
chart.YAxis.DisplayUnit = 1000; // K
chart.YAxis.Deleted = true;
ExcelBarChartSerie serie = chart.Series[0] as ExcelBarChartSerie;
serie.Fill.Color = Color.FromArgb(91, 155, 213);
chart.SetAxisGridlines(true, true, false);
chart.SetCategoriesOrder(true);
}
public static void FirstAxisDate(this ExcelBarChart chart, int maxVal)
{
var nsm = chart.WorkSheet.Drawings.NameSpaceManager;
var nschart = nsm.LookupNamespace("c");
var node = chart.ChartXml.SelectSingleNode(@"c:chartSpace/c:chart/c:plotArea/c:catAx[c:axId[@val='1']]", nsm);
var doc = chart.ChartXml;
node = RenameNode(node, nschart, "c:dateAx");
node.SelectSingleNode("c:auto/@val", nsm).Value = "0";
node.SelectSingleNode("c:tickLblPos/@val", nsm).Value = "none";
node.SelectSingleNode("c:crosses/@val", nsm).Value = "max";
var scale = node.SelectSingleNode("c:scaling", nsm);
var max = doc.CreateElement("c:max", nschart);
max.Attributes.Append(doc.CreateAttribute("val")).Value = maxVal.ToString();
scale.AppendChild(max);
var layout = doc.CreateElement("c:manualLayout", nschart);
var child = doc.CreateElement("c:layoutTarget", nschart);
child.Attributes.Append(doc.CreateAttribute("val")).Value = "inner";
layout.AppendChild(child);
child = doc.CreateElement("c:xMode", nschart);
child.Attributes.Append(doc.CreateAttribute("val")).Value = "edge";
layout.AppendChild(child);
child = doc.CreateElement("c:yMode", nschart);
child.Attributes.Append(doc.CreateAttribute("val")).Value = "edge";
layout.AppendChild(child);
child = doc.CreateElement("c:x", nschart);
child.Attributes.Append(doc.CreateAttribute("val")).Value = "0.005";
layout.AppendChild(child);
child = doc.CreateElement("c:h", nschart);
child.Attributes.Append(doc.CreateAttribute("val")).Value =
(1F - 1.6F / (maxVal + 2)).ToString(CultureInfo.InvariantCulture);
layout.AppendChild(child);
node = doc.SelectSingleNode(@"c:chartSpace/c:chart/c:plotArea/c:layout", nsm);
node.AppendChild(layout);
}
private ExcelChart AddBarChart(ExcelWorksheet ws, string NameChart, eChartType type, List <DataSerie> ListSeries)
{
ExcelBarChart chart = ws.Drawings.AddChart(NameChart, eChartType.ColumnClustered) as ExcelBarChart;
chart.Title.Font.Size = 18;
chart.Title.Font.Bold = true;
chart.Legend.Remove();
chart.YAxis.Border.Fill.Style = eFillStyle.NoFill;
chart.YAxis.Font.Size = 9;
chart.XAxis.MajorTickMark = eAxisTickMark.None;
chart.XAxis.MinorTickMark = eAxisTickMark.None;
chart.YAxis.MajorTickMark = eAxisTickMark.None;
chart.YAxis.MinorTickMark = eAxisTickMark.None;
for (int i = 0; i < ListSeries.Count; i++)
{
DataSerie dat = ListSeries[i];
ExcelBarChartSerie ser = (ExcelBarChartSerie)(chart.Series.Add(dat.Series, dat.xSeries));
ser.DataLabel.ShowValue = true;
ser.DataLabel.Position = eLabelPosition.OutEnd;
ser.Header = dat.name;
if (dat.name == "Move")
{
ser.DataLabel.Border.Fill.Color = Color.Red;
ser.DataLabel.Border.LineStyle = eLineStyle.Solid;
}
}
chart.YAxis.Format = string.Format("{0};{1}", "#,##0", "(#,##0)");
return(chart);
}
public ActionResult GenerarExcel(HistoricoVentasConsulta modelo = null)
{
if (Session["StringToken"] == null)
{
return(RedirectToAction("Index", "Home"));
}
tkn = Session["StringToken"].ToString();
ExcelPackage sLDocument = new ExcelPackage();
modelo.Years = (List <YearsDTO>)TempData["year"];
TempData["year"] = modelo.Years;
var worksheet = sLDocument.Workbook.Worksheets.Add("Ventas Generales");
var datos = (List <object>)TempData["data"];
var mes = (List <string>)TempData["meses"];
List <YearsDTO> Montototal = HistoricoServicio.GetVentasTotalesxMes(modelo, tkn);
string pahtFile = "";
//create a new piechart of type Line
ExcelBarChart lineChart = worksheet.Drawings.AddChart("BarChart", eChartType.ColumnClustered) as ExcelBarChart;
if (modelo.IdTipoReporte == 1)
{
pahtFile = "VentasGeneral.xlsx";
var rangeLabel = worksheet.Cells["B1:M1"];
var rows = 2;
var rowsMes = 2;
for (int r = 0; r < datos.Count; r++)
{
char[] charArray = datos[r].ToString().ToCharArray();
rows = rowsMes;
//create the ranges for the chart
var range1 = worksheet.Cells["B" + rows.ToString() + ":K" + rows.ToString()];
//add the ranges to the chart
lineChart.Series.Add(range1, rangeLabel);
if (charArray[6].Equals('y'))
{
worksheet.Cells[rows, 1].Value = charArray[11].ToString() + charArray[12].ToString() + charArray[13].ToString() + charArray[14].ToString();
}
lineChart.Series[r].Header = worksheet.Cells["A" + rows.ToString()].Value.ToString();
rows = 2;
for (int i = 0; i < mes.Count; i++)
{
char[] charMes = mes[i].ToCharArray();
string mesfila = "";
for (int x = 0; x < charMes.Length; x++)
{
mesfila = mesfila += charMes[x];
}
if (mesfila != "")
{
worksheet.Cells[1, rows].Value = mesfila;
}
for (int o = 0; o < Montototal[r].MesesVenta.Count; o++)
{
if (Montototal[r].MesesVenta[o].mes == mesfila)
{
worksheet.Cells[rowsMes, rows].Value = Montototal[r].MesesVenta[o].montoTotal;
}
}
rows++;
}
rowsMes++;
}
//set the title
lineChart.Title.Text = "Venta General";
}
else
{
if (modelo.IdTipoReporte == 2)
{
pahtFile = "PipasvsCamionetas.xlsx";
}
else
{
pahtFile = "VentasLocalvsForaneas.xlsx";
}
var rangeLabel = worksheet.Cells["B1:M1"];
var rowsMes = 2;
var rowsaños = 2;
var rowPipa = 2;
var tempa = "";
var rows = 2;
var cont = 0;
List <string> rangelabe = new List <string>();
for (int r = 0; r < datos.Count; r++)
{
char[] charArray = datos[r].ToString().ToCharArray();
var m = datos[r].ToString().Split('{');
foreach (var item in m)
{
var rowCamio = 1;
if (item != "")
{
var p = m[1].Split(',');
foreach (var prop in p)
{
var valores = prop.ToString().Split(':');
if (valores.ToList()[0].Contains("y"))
{
//El mes y el año
var año = valores[1].ToString().Split(' ').ToList()[2];
char[] años = año.ToCharArray();
if (tempa == "" || tempa != año)
{
for (int i = 0; i < mes.Count; i++)
{
var range1 = worksheet.Cells["B" + rowsaños.ToString() + ":K" + rowsaños.ToString()];
worksheet.Cells[rowsaños, 1].Value = mes[i] + "-" + años[2] + años[3];
rangelabe.Add(mes[i] + "-" + años[2] + años[3]);
lineChart.Series.Add(range1, rangeLabel);
rowsaños++;
}
if (tempa != "")
{
rows = 2;
cont += 2;
rowPipa = 2;
}
}
if (r >= 3)
{
rowCamio += cont;
//rowPipa++;
}
var meses = valores[1].ToString().Split(' ').ToList()[1];
char[] tamañoMes = meses.ToCharArray();
worksheet.Cells[1, rows].Value = meses.Substring(1, tamañoMes.Length - 1);
rows++;
tempa = año;
//rowCamio++;
}
if (valores.ToList()[0].Contains("a"))
{
//Ventas de Cmaionetas
var sumaCamioneta = valores[1].ToString().Split(' ').ToList()[1];
char[] tamSuma = sumaCamioneta.ToCharArray();
if (tamSuma[1] == '0')
{
var doubleValue = Double.Parse(sumaCamioneta.Substring(1, tamSuma.Length - 2));
worksheet.Cells[rowCamio, rowPipa].Value = doubleValue;
}
else
{
var doubleValue = Double.Parse(sumaCamioneta.Substring(1, tamSuma.Length - 2));
worksheet.Cells[rowCamio, rowPipa].Value = doubleValue;
}
sumaCamioneta = "";
//rowPipa++;
}
if (valores.ToList()[0].Contains("b"))
{
//Ventas de Pipas
var sumaPipa = valores[1].ToString().Split(' ').ToList()[1];
char[] tamSumaPi = sumaPipa.ToCharArray();
if (tamSumaPi[1] == '0')
{
var doubleValue = Double.Parse(sumaPipa.Substring(1, tamSumaPi.Length - 5));
worksheet.Cells[rowCamio, rowPipa].Value = doubleValue;
}
else
{
var doubleValue = Double.Parse(sumaPipa.Substring(1, tamSumaPi.Length - 5));
worksheet.Cells[rowCamio, rowPipa].Value = doubleValue;
}
sumaPipa = "";
}
rowCamio++;
}
rowPipa++;
}
}
}
for (int e = 0; e < rangelabe.Count(); e++)
{
lineChart.Series[e].Header = worksheet.Cells["A" + rowsMes.ToString()].Value.ToString();
rowsMes++;
}
rangelabe = null;
rowsaños++;
//rowPipa = 2;
//set the title
if (modelo.IdTipoReporte == 2)
{
lineChart.Title.Text = "PipasvsCamionetas";
}
else
{
lineChart.Title.Text = "LocalvsForanea";
}
}
//position of the legend
lineChart.Legend.Position = eLegendPosition.Right;
//size of the chart
lineChart.SetSize(700, 600);
//add the chart at cell B6
lineChart.SetPosition(1, 0, 8, 0);
// FileInfo infor = new FileInfo(pahtFile);
MemoryStream memoryStream = new MemoryStream();
sLDocument.SaveAs(memoryStream);
memoryStream.Position = 0;
TempData["data"] = datos;
TempData["meses"] = mes;
return(new FileStreamResult(memoryStream, "application/vnd.ms-excel")
{
FileDownloadName = pahtFile
});
// Descraga(pahtFile, memoryStream);
// return RedirectToAction("HistoricoVentas", modelo);
}
private void CreateChart(ExcelWorksheet worksheet, String nameKpi, Dictionary <String, List <Tuple <int, int> > > points,
ExcelRangeBase startCell, int paddingChart)
{
ExcelBarChart barChart = worksheet.Drawings.AddChart(nameKpi, eChartType.ColumnClustered) as ExcelBarChart;
barChart.Legend.Position = eLegendPosition.Bottom;
barChart.Title.Text = nameKpi;
string monthRangeString = points["month"]
.Select(e => ExcelCellBase.GetAddress(e.Item1, e.Item2))
.Join(",");
var rangeMonth = worksheet.Cells[$"{monthRangeString}"];
/* ----------------------- Fact ---------------------------------*/
string rangeFactString = points["fact"]
.Select(e => ExcelCellBase.GetAddress(e.Item1, e.Item2))
.Join(",");
var rangeFact = worksheet.Cells[$"{rangeFactString}"];
ExcelChartSerie factChartSerie = barChart.Series.Add(rangeFact, rangeMonth);
factChartSerie.Header = "Факт";
factChartSerie.TrendLines.Add(eTrendLine.Linear);
/* -------------------------- Target ------------------------------*/
string rangeTargetString = points["target"]
.Select(e => ExcelCellBase.GetAddress(e.Item1, e.Item2))
.Join(",");
var rangeTarget = worksheet.Cells[$"{rangeTargetString}"];
ExcelChart chartTarget = barChart.PlotArea.ChartTypes.Add(eChartType.Line);
chartTarget.Series.Add(rangeTarget, rangeMonth).Header = "Цель";
chartTarget.SetLineChartColor(1, 0, Color.Red);
/*/* -------------------------- Deviation ------------------------------#1#
* string rangeDeviationString = points["deviation"]
* .Select(e => ExcelCellBase.GetAddress(e.Item1,e.Item2))
* .Join(",");
* var rangeDeviation = worksheet.Cells[$"{rangeDeviationString}"];
*
* ExcelChart chartDeviation = barChart.PlotArea.ChartTypes.Add(eChartType.Line);
* chartDeviation.Series.Add(rangeDeviation,rangeMonth ).Header= "Отклонение";*/
/* -------------------------- CountOrder ------------------------------*/
string rangeCountOrderString = points["countOrder"]
.Select(e => ExcelCellBase.GetAddress(e.Item1, e.Item2))
.Join(",");
var rangeCountOrder = worksheet.Cells[$"{rangeCountOrderString}"];
ExcelChart chartCountOrder = barChart.PlotArea.ChartTypes.Add(eChartType.Line);
chartCountOrder.Series.Add(rangeCountOrder, rangeMonth).Header = "Кол-во заказов";
barChart?.SetSize(500, 400);
barChart?.SetPosition(startCell.Start.Row, 0, startCell.Start.Column, 0);
//barChart?.SetPosition(10+(paddingChart*500),500);
}
public byte[] GetGeneralReport()
{
ExcelPackage excelPackage = new ExcelPackage();
var workSheet = excelPackage.Workbook.Worksheets.Add("Отчет по тестам");
int testerCount = Database.TestResultRepository.GetAll().Count();
using (ExcelRange testerCountER = workSheet.Cells["A1:C1"])
{
testerCountER["A1"].Value = $"Кол-во тестируемых: {testerCount}";
workSheet.Cells["A1:C1"].Merge = true;
}
ExcelRange tableHeader = workSheet.Cells["A2:C2"];
tableHeader.Style.Border.Left.Style = ExcelBorderStyle.Thin;
tableHeader.Style.Border.Right.Style = ExcelBorderStyle.Thin;
tableHeader.Style.Border.BorderAround(ExcelBorderStyle.Thin);
tableHeader["A2"].Value = "Вопрос";
tableHeader["B2"].Value = "Правильно";
tableHeader["C2"].Value = "Неправильно";
ExcelRange tableBody = workSheet.Cells["A3:C7"];
tableBody.Style.Border.Left.Style = ExcelBorderStyle.Thin;
tableBody.Style.Border.Right.Style = ExcelBorderStyle.Thin;
tableBody.Style.Border.BorderAround(ExcelBorderStyle.Thin);
if (testerCount != 0)
{
int num = 0;
foreach (var question in Database.QuestionRepository.GetAllWithInclude(q => q.TestQuestionAnswers))
{
num++;
tableBody[$"A{num + 2}"].Value = num;
int rightAnswerCount = question.TestQuestionAnswers.Count(qa => qa.AnswerNum.Equals(question.RightAnswerNum));
tableBody[$"B{num + 2}"].Value = rightAnswerCount;
int notRightAnswerCount = question.TestQuestionAnswers.Count(qa => !qa.AnswerNum.Equals(question.RightAnswerNum));
tableBody[$"C{num + 2}"].Value = notRightAnswerCount;
}
}
workSheet.Column(tableBody.End.Column - 1).AutoFit();
workSheet.Column(tableBody.End.Column).AutoFit();
ExcelBarChart chart = workSheet.Drawings.AddChart("barChart", eChartType.ColumnStacked) as ExcelBarChart;
chart.SetSize(500, 300);
chart.SetPosition(tableHeader.Start.Row, -2, tableHeader.End.Column, 10);
chart.Title.Text = workSheet.Name;
string serie = "B3:B7";
string xSerie = "A3:A7";
var serie1 = chart.Series.Add(serie, xSerie);
serie1.Header = "Правильно";
serie1.Fill.Color = Color.FromArgb(79, 129, 189);
serie = "C3:C7";
xSerie = "A3:A7";
var serie2 = chart.Series.Add(serie, xSerie);
serie2.Header = "Неправильно";
serie2.Fill.Color = Color.FromArgb(192, 80, 77);
tableHeader.Dispose();
tableBody.Dispose();
return(excelPackage.GetAsByteArray());
}
public static void Run()
{
string fileName = "ExcelReport-" + DateTime.Now.ToString("yyyy_MM_dd_HHmmss") + ".xlsx";
string reportTitle = "2013年度五大公司实际情况与原计划的百分比";
FileInfo file = new FileInfo("C:\\Excel\\" + fileName);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = null;
ExcelChartSerie chartSerie = null;
ExcelBarChart chart = null;
#region research
worksheet = package.Workbook.Worksheets.Add("Data");
DataTable dataPercent = GetDataPercent();
//chart = Worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.Line) as ExcelLineChart;
chart = worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.ColumnStacked) as ExcelBarChart;//设置图表样式
//chart.Legend.Position = eLegendPosition.Top;
//chart.Legend.Add();
chart.Legend.Remove();
chart.Title.Text = reportTitle; //设置图表的名称
//chart.SetPosition(200, 50);//设置图表位置
chart.SetSize(800, 400); //设置图表大小
chart.ShowHiddenData = true;
// chart.PlotArea.Fill.Color = Color.Red;
// chart.Fill.Color = Color.DarkBlue;
//chart.YAxis.MinorUnit = 1;
chart.XAxis.MinorUnit = 1;//设置X轴的最小刻度
chart.DataLabel.ShowValue = true;
//chart.YAxis.LabelPosition = eTickLabelPosition.High;
// chart.YAxis.TickLabelPosition = eTickLabelPosition.NextTo;
//chart.DataLabel.ShowCategory = true;
//chart.DataLabel.ShowPercent = true;//显示百分比
//设置月份
for (int col = 1; col <= dataPercent.Columns.Count; col++)
{
worksheet.Cells[1, col].Value = dataPercent.Columns[col - 1].ColumnName;
}
//设置数据
for (int row = 1; row <= dataPercent.Rows.Count; row++)
{
for (int col = 1; col <= dataPercent.Columns.Count; col++)
{
string strValue = dataPercent.Rows[row - 1][col - 1].ToString();
if (col == 1)
{
worksheet.Cells[row + 1, col].Value = strValue;
}
else
{
double realValue = double.Parse(strValue);
worksheet.Cells[row + 1, col].Style.Fill.PatternType = ExcelFillStyle.Solid;
// worksheet.Cells[row + 1, col].Style.Numberformat.Format = "#0\\.00%";//设置数据的格式为百分比
worksheet.Cells[row + 1, col].Value = realValue;
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.White);
worksheet.Cells[row + 1, col].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
//if (realValue < 0.90d)//如果小于90%则该单元格底色显示为红色
//{
// worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Red);
//}
//else if (realValue >= 0.90d && realValue <= 0.95d)//如果在90%与95%之间则该单元格底色显示为黄色
//{
// worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
//}
//else
//{
// worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Green);//如果大于95%则该单元格底色显示为绿色
//}
}
}
//chartSerie = chart.Series.Add(worksheet.Cells["A2:M2"], worksheet.Cells["B1:M1"]);
//chartSerie.HeaderAddress = worksheet.Cells["A2"];
//chart.Series.Add()方法所需参数为：chart.Series.Add(X轴数据区,Y轴数据区)
chart.Series.Add(worksheet.Cells[row + 1, 2, row + 1, 2 + dataPercent.Columns.Count - 2], worksheet.Cells[row, 2, row, 2 + dataPercent.Columns.Count - 2]);
//chartSerie.HeaderAddress = worksheet.Cells[row + 1, 1];//设置每条线的名称
}
//因为假定每家公司至少完成了80%以上，所以这里设置Y轴的最小刻度为80%，这样使图表上的折线更清晰
//chart.YAxis.MinValue = 0.8d;
//chart.SetPosition(200, 50);//可以通过制定左上角坐标来设置图表位置
//通过指定图表左上角所在的行和列及对应偏移来指定图表位置
//这里CommpanyNames.Length + 1及3分别表示行和列
chart.SetPosition(CommpanyNames.Length + 1, 0, 3, 0);
//chart.Border.Fill.Color = Color.Yellow;
#endregion research
package.Save();//保存文件
}
}
private static void CreateReport(MySqlDataReader reader)
{
FileInfo fi = getReportFileName();
if (fi == null)
{
return;
}
int lvl, testres;
using (ExcelPackage book = new ExcelPackage(fi))
{
ExcelWorksheet ws = book.Workbook.Worksheets.Add("Report");
ws.Cells[1, 1].Value = "ПІБ";
ws.Cells[1, 2].Value = "Група";
ws.Cells[1, 3].Value = "Стать";
ws.Cells[1, 4].Value = "Результат тестування";
ws.Cells[1, 5].Value = "Рівень";
int currentrow = 1;
while (reader.Read())
{
currentrow++;
ws.Cells[currentrow, 1].Value = reader.GetValue(0);
ws.Cells[currentrow, 2].Value = reader.GetValue(1);
ws.Cells[currentrow, 3].Value = ((bool)reader.GetValue(2))?"М":"Ж";
testres = Convert.ToInt32(reader.GetValue(3));
lvl = Convert.ToInt16(reader.GetValue(4));
ws.Cells[currentrow, 4].Value = testres;
if (lvl == 2)
{
ws.Cells[currentrow, 5].Value = "В";
}
else if (lvl == 1)
{
ws.Cells[currentrow, 5].Value = "С";
}
else
{
ws.Cells[currentrow, 5].Value = "Н";
}
}
currentrow += 2;
ws.Cells[currentrow, 1].Value = "Всього:";
ws.Cells[currentrow + 1, 1].Value = "Рівень:";
ws.Cells[currentrow + 2, 1].Value = "Високий";
ws.Cells[currentrow + 3, 1].Value = "Середній";
ws.Cells[currentrow + 4, 1].Value = "Низький";
ws.Cells[currentrow + 1, 2].Value = "М";
ws.Cells[currentrow + 1, 3].Value = "Ж";
ws.Cells[currentrow + 2, 2].Formula = String.Format("COUNTIFS({0},\"В\",{1},\"М\")", ExcelRange.GetAddress(2, 5, currentrow - 2, 5), ExcelRange.GetAddress(2, 3, currentrow - 2, 3));
ws.Cells[currentrow + 3, 2].Formula = String.Format("COUNTIFS({0},\"С\",{1},\"М\")", ExcelRange.GetAddress(2, 5, currentrow - 2, 5), ExcelRange.GetAddress(2, 3, currentrow - 2, 3));
ws.Cells[currentrow + 4, 2].Formula = String.Format("COUNTIFS({0},\"Н\",{1},\"М\")", ExcelRange.GetAddress(2, 5, currentrow - 2, 5), ExcelRange.GetAddress(2, 3, currentrow - 2, 3));
ws.Cells[currentrow + 2, 3].Formula = String.Format("COUNTIFS({0},\"В\",{1},\"Ж\")", ExcelRange.GetAddress(2, 5, currentrow - 2, 5), ExcelRange.GetAddress(2, 3, currentrow - 2, 3));
ws.Cells[currentrow + 3, 3].Formula = String.Format("COUNTIFS({0},\"С\",{1},\"Ж\")", ExcelRange.GetAddress(2, 5, currentrow - 2, 5), ExcelRange.GetAddress(2, 3, currentrow - 2, 3));
ws.Cells[currentrow + 4, 3].Formula = String.Format("COUNTIFS({0},\"Н\",{1},\"Ж\")", ExcelRange.GetAddress(2, 5, currentrow - 2, 5), ExcelRange.GetAddress(2, 3, currentrow - 2, 3));
var tb = ws.Tables.Add(ws.Cells[1, 1, currentrow - 2, 5], null);
tb.TableStyle = OfficeOpenXml.Table.TableStyles.Medium13;
tb = ws.Tables.Add(ws.Cells[currentrow + 1, 1, currentrow + 4, 3], null);
tb.TableStyle = OfficeOpenXml.Table.TableStyles.Medium14;
ExcelBarChart barchart = ws.Drawings.AddChart("График", OfficeOpenXml.Drawing.Chart.eChartType.ColumnStacked3D) as ExcelBarChart;
barchart.SetPosition(0, 0, 7, 0);
barchart.SetSize(600, 400);
var ser = (ExcelBarChartSerie)(barchart.Series.Add(ExcelRange.GetAddress(currentrow + 2, 2, currentrow + 4, 2), ExcelRange.GetAddress(currentrow + 2, 1, currentrow + 4, 1)));
var headeradr = ws.Cells[currentrow + 1, 2];
ser.HeaderAddress = headeradr;
ser = (ExcelBarChartSerie)(barchart.Series.Add(ExcelRange.GetAddress(currentrow + 2, 3, currentrow + 4, 3), ExcelRange.GetAddress(currentrow + 2, 1, currentrow + 4, 1)));
headeradr = ws.Cells[currentrow + 1, 3];
ser.HeaderAddress = headeradr;
ws.Calculate();
ws.Cells.AutoFitColumns();
book.Save();
}
}
private static void CreateFullReport(MySqlDataReader student)
{
FileInfo fi = getReportFileName();
if (fi == null)
{
return;
}
string[] testnamearr = new string[] { "test1", "test2", "test5", "test6", "test7", "test8", "test9", "test10", "test11", "test12", "test13", "test14", "test15" };
SqlQueries sql = new SqlQueries();
MySqlDataReader reader;
if (student.HasRows)
{
using (ExcelPackage book = new ExcelPackage(fi))
{
ExcelWorksheet ws = book.Workbook.Worksheets.Add("Report");
ws.Cells[1, 1].Value = "ПІБ";
ws.Cells[1, 2].Value = "Група";
ws.Cells[1, 3].Value = "Стать";
ws.Cells[1, 4].Value = "Тест 2";
ws.Cells[1, 5].Value = "Тест 3";
ws.Cells[1, 6].Value = "Тест 6";
ws.Cells[1, 7].Value = "Тест 7";
ws.Cells[1, 8].Value = "Тест 8";
ws.Cells[1, 9].Value = "Тест 9";
ws.Cells[1, 10].Value = "Тест 10";
ws.Cells[1, 11].Value = "Тест 11";
ws.Cells[1, 12].Value = "Тест 12";
ws.Cells[1, 13].Value = "Тест 13";
ws.Cells[1, 14].Value = "Тест 14";
ws.Cells[1, 15].Value = "Тест 15";
ws.Cells[1, 16].Value = "Тест 16";
ws.Cells[1, 17].Value = "Загальний рівень конкурентноспроможності";
int currentrow = 1, sum, curr_lvl, compl_test_num;
string studentName, groupName;
while (student.Read())
{
sum = 0; compl_test_num = 0;
currentrow++;
studentName = student.GetString(1);
groupName = student.GetString(2);
ws.Cells[currentrow, 1].Value = studentName;
ws.Cells[currentrow, 2].Value = groupName;
ws.Cells[currentrow, 3].Value = student.GetBoolean(3) ? "М" : "Ж";
for (int i = 0; i < 13; i++)
{
reader = sql.SelectResult(testnamearr[i], studentName, groupName, "");
if (reader.HasRows)
{
reader.Read();
curr_lvl = reader.GetInt16(4);
if (curr_lvl == 2)
{
ws.Cells[currentrow, i + 4].Value = "В";
}
else if (curr_lvl == 1)
{
ws.Cells[currentrow, i + 4].Value = "С";
}
else
{
ws.Cells[currentrow, i + 4].Value = "Н";
}
sum += curr_lvl;
compl_test_num++;
}
reader.Close();
}
curr_lvl = (int)Math.Round((double)sum / compl_test_num, 0, MidpointRounding.AwayFromZero);
if (curr_lvl == 2)
{
ws.Cells[currentrow, 17].Value = "В";
}
else if (curr_lvl == 1)
{
ws.Cells[currentrow, 17].Value = "С";
}
else
{
ws.Cells[currentrow, 17].Value = "Н";
}
}
currentrow += 2;
ws.Cells[currentrow, 1].Value = "Всього:";
ws.Cells[currentrow + 1, 1].Value = "Рівень:";
ws.Cells[currentrow + 2, 1].Value = "Високий";
ws.Cells[currentrow + 3, 1].Value = "Середній";
ws.Cells[currentrow + 4, 1].Value = "Низький";
ws.Cells[currentrow + 1, 2].Value = "М";
ws.Cells[currentrow + 1, 3].Value = "Ж";
ws.Cells[currentrow + 2, 2].Formula = String.Format("COUNTIFS({0},\"В\",{1},\"М\")", ExcelRange.GetAddress(2, 17, currentrow - 2, 17), ExcelRange.GetAddress(2, 3, currentrow - 2, 3));
ws.Cells[currentrow + 3, 2].Formula = String.Format("COUNTIFS({0},\"С\",{1},\"М\")", ExcelRange.GetAddress(2, 17, currentrow - 2, 17), ExcelRange.GetAddress(2, 3, currentrow - 2, 3));
ws.Cells[currentrow + 4, 2].Formula = String.Format("COUNTIFS({0},\"Н\",{1},\"М\")", ExcelRange.GetAddress(2, 17, currentrow - 2, 17), ExcelRange.GetAddress(2, 3, currentrow - 2, 3));
ws.Cells[currentrow + 2, 3].Formula = String.Format("COUNTIFS({0},\"В\",{1},\"Ж\")", ExcelRange.GetAddress(2, 17, currentrow - 2, 17), ExcelRange.GetAddress(2, 3, currentrow - 2, 3));
ws.Cells[currentrow + 3, 3].Formula = String.Format("COUNTIFS({0},\"С\",{1},\"Ж\")", ExcelRange.GetAddress(2, 17, currentrow - 2, 17), ExcelRange.GetAddress(2, 3, currentrow - 2, 3));
ws.Cells[currentrow + 4, 3].Formula = String.Format("COUNTIFS({0},\"Н\",{1},\"Ж\")", ExcelRange.GetAddress(2, 17, currentrow - 2, 17), ExcelRange.GetAddress(2, 3, currentrow - 2, 3));
var tb = ws.Tables.Add(ws.Cells[1, 1, currentrow - 2, 17], null);
tb.TableStyle = OfficeOpenXml.Table.TableStyles.Medium13;
tb = ws.Tables.Add(ws.Cells[currentrow + 1, 1, currentrow + 4, 3], null);
tb.TableStyle = OfficeOpenXml.Table.TableStyles.Medium14;
ExcelBarChart barchart = ws.Drawings.AddChart("График", OfficeOpenXml.Drawing.Chart.eChartType.ColumnStacked3D) as ExcelBarChart;
barchart.SetPosition(currentrow, 0, 4, 0);
barchart.SetSize(600, 400);
var ser = (ExcelBarChartSerie)(barchart.Series.Add(ExcelRange.GetAddress(currentrow + 2, 2, currentrow + 4, 2), ExcelRange.GetAddress(currentrow + 2, 1, currentrow + 4, 1)));
var headeradr = ws.Cells[currentrow + 1, 2];
ser.HeaderAddress = headeradr;
ser = (ExcelBarChartSerie)(barchart.Series.Add(ExcelRange.GetAddress(currentrow + 2, 3, currentrow + 4, 3), ExcelRange.GetAddress(currentrow + 2, 1, currentrow + 4, 1)));
headeradr = ws.Cells[currentrow + 1, 3];
ser.HeaderAddress = headeradr;
ws.Calculate();
ws.Cells.AutoFitColumns();
book.Save();
}
}
}
private static void CreateSpecialReportT0(MySqlDataReader reader)
{
FileInfo fi = getReportFileName();
if (fi == null)
{
return;
}
string[] scaleName = new string[] { "Технічне мислення", "Креативність", "Мобільність ", "Здатність до роботи в команді", "Адекватна оцінка власної діяльності", "Логічне мислення", "Організаторські здібності", "Готовність до ризику", "Критичне мислення", "Лідерські здібності", "Комунікабельність", "Вміння планувати власну діяльність", "Бажання досягати успіху", "Прагнення до самовдосконалення", "Наявність професійних знань", "Стресостійкість", "Відповідальність", "Ініціативність", "Cамостійність у прийнятті рішень", "Амбіційність", "Наполегливість", "Цілеспрямованість", "Старанність", "Уважність", "Терплячість" };
using (ExcelPackage book = new ExcelPackage(fi))
{
ExcelWorksheet ws = book.Workbook.Worksheets.Add("Report");
int currentrow = 1;
ws.Cells[1, 1].Value = "ПІБ";
ws.Cells[1, 2].Value = "Група";
ws.Cells[1, 3].Value = "Стать";
for (int i = 0; i < 25; i++)
{
ws.Cells[1, 4 + i].Value = scaleName[i];
}
while (reader.Read())
{
currentrow++;
ws.Cells[currentrow, 1].Value = reader.GetValue(0);
ws.Cells[currentrow, 2].Value = reader.GetValue(1);
ws.Cells[currentrow, 3].Value = ((bool)reader.GetValue(2)) ? "М" : "Ж";
for (int i = 0; i < 25; i++)
{
ws.Cells[currentrow, i + 4].Value = reader.GetValue(i + 3);
}
}
ws.Cells[currentrow + 2, 1].Value = "Середнє значення:";
ws.Cells[currentrow + 4, 1].Value = "Шкала:";
ws.Cells[currentrow + 4, 2].Value = "М";
ws.Cells[currentrow + 4, 3].Value = "Ж";
ws.Cells[currentrow + 4, 4].Value = "Загальнє";
for (int i = 0; i < 15; i++)
{
ws.Cells[currentrow + 5 + i, 1].Value = scaleName[i];
ws.Cells[currentrow + 5 + i, 2].FormulaR1C1 = String.Format("IF(COUNTIFS(R{4}C{5}:R{6}C{7},\"=М\")>0,AVERAGEIFS(R{0}C{1}:R{2}C{3}, R{4}C{5}:R{6}C{7}, \"=М\"),0)", 2, 4 + i, currentrow, 4 + i, 2, 3, currentrow, 3);
ws.Cells[currentrow + 5 + i, 3].FormulaR1C1 = String.Format("IF(COUNTIFS(R{4}C{5}:R{6}C{7},\"=Ж\")>0,AVERAGEIFS(R{0}C{1}:R{2}C{3}, R{4}C{5}:R{6}C{7}, \"=Ж\"),0)", 2, 4 + i, currentrow, 4 + i, 2, 3, currentrow, 3);
ws.Cells[currentrow + 5 + i, 4].FormulaR1C1 = String.Format("AVERAGE(R{0}C{1}:R{2}C{3})", 2, 4 + i, currentrow, 4 + i);
}
ws.Cells[currentrow + 20, 1].Value = "Шкала:";
ws.Cells[currentrow + 20, 2].Value = "М";
ws.Cells[currentrow + 20, 3].Value = "Ж";
ws.Cells[currentrow + 20, 4].Value = "Загальнє";
for (int i = 0; i < 10; i++)
{
ws.Cells[currentrow + 21 + i, 1].Value = scaleName[i + 15];
ws.Cells[currentrow + 21 + i, 2].FormulaR1C1 = String.Format("IF(COUNTIFS(R{4}C{5}:R{6}C{7},\"=М\")>0,AVERAGEIFS(R{0}C{1}:R{2}C{3}, R{4}C{5}:R{6}C{7}, \"=М\"),0)", 2, 19 + i, currentrow, 19 + i, 2, 3, currentrow, 3);
ws.Cells[currentrow + 21 + i, 3].FormulaR1C1 = String.Format("IF(COUNTIFS(R{4}C{5}:R{6}C{7},\"=Ж\")>0,AVERAGEIFS(R{0}C{1}:R{2}C{3}, R{4}C{5}:R{6}C{7}, \"=Ж\"),0)", 2, 19 + i, currentrow, 19 + i, 2, 3, currentrow, 3);
ws.Cells[currentrow + 21 + i, 4].FormulaR1C1 = String.Format("AVERAGE(R{0}C{1}:R{2}C{3})", 2, 19 + i, currentrow, 19 + i);
}
var tb = ws.Tables.Add(ws.Cells[1, 1, currentrow, 28], null);
tb.TableStyle = OfficeOpenXml.Table.TableStyles.Medium13;
tb = ws.Tables.Add(ws.Cells[currentrow + 4, 1, currentrow + 19, 4], null);
tb.TableStyle = OfficeOpenXml.Table.TableStyles.Medium14;
tb = ws.Tables.Add(ws.Cells[currentrow + 20, 1, currentrow + 30, 4], null);
tb.TableStyle = OfficeOpenXml.Table.TableStyles.Medium12;
ExcelBarChart barchart = ws.Drawings.AddChart("График ч1", OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered) as ExcelBarChart;
barchart.SetPosition(currentrow + 2, 0, 5, 0);
barchart.SetSize(500, 400);
var ser = (ExcelBarChartSerie)(barchart.Series.Add(ExcelRange.GetAddress(currentrow + 5, 2, currentrow + 19, 2), ExcelRange.GetAddress(currentrow + 5, 1, currentrow + 19, 1)));
var headeradr = ws.Cells[currentrow + 4, 2];
ser.HeaderAddress = headeradr;
ser = (ExcelBarChartSerie)(barchart.Series.Add(ExcelRange.GetAddress(currentrow + 5, 3, currentrow + 19, 3), ExcelRange.GetAddress(currentrow + 5, 1, currentrow + 19, 1)));
headeradr = ws.Cells[currentrow + 4, 3];
ser.HeaderAddress = headeradr;
ser = (ExcelBarChartSerie)(barchart.Series.Add(ExcelRange.GetAddress(currentrow + 5, 4, currentrow + 19, 4), ExcelRange.GetAddress(currentrow + 5, 1, currentrow + 19, 1)));
headeradr = ws.Cells[currentrow + 4, 4];
ser.HeaderAddress = headeradr;
ExcelBarChart barchart2 = ws.Drawings.AddChart("График ч2", OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered) as ExcelBarChart;
barchart2.SetPosition(currentrow + 20, 0, 5, 0);
barchart2.SetSize(500, 400);
var ser2 = (ExcelBarChartSerie)(barchart2.Series.Add(ExcelRange.GetAddress(currentrow + 21, 2, currentrow + 30, 2), ExcelRange.GetAddress(currentrow + 21, 1, currentrow + 30, 1)));
var headeradr2 = ws.Cells[currentrow + 20, 2];
ser2.HeaderAddress = headeradr2;
ser2 = (ExcelBarChartSerie)(barchart2.Series.Add(ExcelRange.GetAddress(currentrow + 21, 3, currentrow + 30, 3), ExcelRange.GetAddress(currentrow + 21, 1, currentrow + 30, 1)));
headeradr2 = ws.Cells[currentrow + 20, 3];
ser2.HeaderAddress = headeradr2;
ser2 = (ExcelBarChartSerie)(barchart2.Series.Add(ExcelRange.GetAddress(currentrow + 21, 4, currentrow + 30, 4), ExcelRange.GetAddress(currentrow + 21, 1, currentrow + 30, 1)));
headeradr2 = ws.Cells[currentrow + 20, 4];
ser2.HeaderAddress = headeradr2;
ws.Cells[currentrow + 3, 1, currentrow + 19, 4].Style.Numberformat.Format = "0.00";
ws.Cells[currentrow + 21, 1, currentrow + 30, 4].Style.Numberformat.Format = "0.00";
ws.Calculate();
ws.Cells.AutoFitColumns();
book.Save();
}
}
private static void CreateSpecialReport(MySqlDataReader reader, string testName)
{
FileInfo fi = getReportFileName();
if (fi == null)
{
return;
}
string[] scaleName = null;
int count = 0;
if (testName == "test3")
{
scaleName = new string[] { "Комунікативні мотиви", "Мотиви уникнення", "Мотиви престижу", "Професійні мотиви", "Мотиви творчої самореалізації", "Навчально-пізнавальні мотиви", "Соціальні мотиви" };
count = 7;
}
else
{
scaleName = new string[] { "активне діяльне життя", "життєва мудрість", "здоров'я", "цікава робота", "краса природи і мистецтва", "любов", "матеріально забезпечене життя", "наявність гарних і вірних друзів", "суспільне визнання", "пізнання", "продуктивне життя", "розвиток", "розваги", "воля", "щасливе сімейне життя", "щастя інших", "творчість", "впевненість у собі" };
count = 18;
}
using (ExcelPackage book = new ExcelPackage(fi))
{
ExcelWorksheet ws = book.Workbook.Worksheets.Add("Report");
int currentrow = 1;
ws.Cells[1, 1].Value = "ПІБ";
ws.Cells[1, 2].Value = "Група";
ws.Cells[1, 3].Value = "Стать";
for (int i = 0; i < count; i++)
{
ws.Cells[1, 4 + i].Value = scaleName[i];
}
while (reader.Read())
{
currentrow++;
ws.Cells[currentrow, 1].Value = reader.GetValue(0);
ws.Cells[currentrow, 2].Value = reader.GetValue(1);
ws.Cells[currentrow, 3].Value = ((bool)reader.GetValue(2)) ? "М" : "Ж";
for (int i = 0; i < count; i++)
{
ws.Cells[currentrow, i + 4].Value = reader.GetValue(i + 3);
}
}
ws.Cells[currentrow + 2, 1].Value = "Середнє значення:";
ws.Cells[currentrow + 4, 1].Value = "Шкала:";
ws.Cells[currentrow + 4, 2].Value = "М";
ws.Cells[currentrow + 4, 3].Value = "Ж";
ws.Cells[currentrow + 4, 4].Value = "Загальнє";
if (testName == "test0")
{
ws.Cells[currentrow + 3, 1].Value = "Перша частина"; ws.Cells[currentrow + 20, 1].Value = "Друга частина"; count = 15;
}
for (int i = 0; i < count; i++)
{
ws.Cells[currentrow + 5 + i, 1].Value = scaleName[i];
ws.Cells[currentrow + 5 + i, 2].FormulaR1C1 = String.Format("IF(COUNTIFS(R{4}C{5}:R{6}C{7},\"=М\")>0,AVERAGEIFS(R{0}C{1}:R{2}C{3}, R{4}C{5}:R{6}C{7}, \"=М\"),0)", 2, 4 + i, currentrow, 4 + i, 2, 3, currentrow, 3);
ws.Cells[currentrow + 5 + i, 3].FormulaR1C1 = String.Format("IF(COUNTIFS(R{4}C{5}:R{6}C{7},\"=Ж\")>0,AVERAGEIFS(R{0}C{1}:R{2}C{3}, R{4}C{5}:R{6}C{7}, \"=Ж\"),0)", 2, 4 + i, currentrow, 4 + i, 2, 3, currentrow, 3);
ws.Cells[currentrow + 5 + i, 4].FormulaR1C1 = String.Format("AVERAGE(R{0}C{1}:R{2}C{3})", 2, 4 + i, currentrow, 4 + i);
}
var tb = ws.Tables.Add(ws.Cells[currentrow + 4, 1, currentrow + 4 + count, 4], null);
tb.TableStyle = OfficeOpenXml.Table.TableStyles.Medium14;
ExcelBarChart barchart = ws.Drawings.AddChart("График", OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered) as ExcelBarChart;
barchart.SetPosition(currentrow + 1, 0, 6, 0);
barchart.SetSize(500, 400);
var ser = (ExcelBarChartSerie)(barchart.Series.Add(ExcelRange.GetAddress(currentrow + 5, 2, currentrow + count + 5, 2), ExcelRange.GetAddress(currentrow + 5, 1, currentrow + count + 5, 1)));
var headeradr = ws.Cells[currentrow + 4, 2];
ser.HeaderAddress = headeradr;
ser = (ExcelBarChartSerie)(barchart.Series.Add(ExcelRange.GetAddress(currentrow + 5, 3, currentrow + count + 5, 3), ExcelRange.GetAddress(currentrow + 5, 1, currentrow + count + 5, 1)));
headeradr = ws.Cells[currentrow + 4, 3];
ser.HeaderAddress = headeradr;
ser = (ExcelBarChartSerie)(barchart.Series.Add(ExcelRange.GetAddress(currentrow + 5, 4, currentrow + count + 5, 4), ExcelRange.GetAddress(currentrow + 5, 1, currentrow + count + 5, 1)));
headeradr = ws.Cells[currentrow + 4, 4];
ser.HeaderAddress = headeradr;
ws.Cells[currentrow + 3, 1, currentrow + count + 3, 4].Style.Numberformat.Format = "0.00";
tb = ws.Tables.Add(ws.Cells[1, 1, currentrow, count + 3], null);
tb.TableStyle = OfficeOpenXml.Table.TableStyles.Medium13;
ws.Calculate();
ws.Cells.AutoFitColumns();
book.Save();
}
}
public IActionResult Excel()
{
var comlumHeadrs = new string[]
{
"Employee Id",
"Name",
"Position",
"Salary",
"Joined Date"
};
byte[] result;
using (var package = new ExcelPackage())
{
// add a new worksheet to the empty workbook
var worksheet = package.Workbook.Worksheets.Add("Current Employee"); //Worksheet name
using (var cells = worksheet.Cells[1, 1, 1, 5]) //(1,1) (1,5)
{
cells.Style.Font.Bold = true;
}
//First add the headers
for (var i = 0; i < comlumHeadrs.Count(); i++)
{
worksheet.Cells[1, i + 1].Value = comlumHeadrs[i];
}
//Add values
var j = 2;
foreach (var employee in DummyData2.GetEmployeeData())
{
worksheet.Cells["A" + j].Value = employee.Id;
worksheet.Cells["B" + j].Value = employee.Name;
worksheet.Cells["C" + j].Value = employee.Position;
worksheet.Cells["D" + j].Value = employee.Salary;
worksheet.Cells["E" + j].Value = employee.JoinedDate.ToString("MM/dd/yyyy");
j++;
}
ExcelBarChart lineChart = worksheet.Drawings.AddChart("barChart", eChartType.BarStacked) as ExcelBarChart;
//set the title
lineChart.Title.Text = "LineChart Example";
var test1 = DummyData2.GetEmployeeData().Count() + 1;
var rangeLabel = worksheet.Cells[$"B2:B{test1}"];
var range1 = worksheet.Cells[$"D2:D{test1}"];
var rangeLabel2 = worksheet.Cells["B2:B4"];
var range2 = worksheet.Cells["D2:D4"];
lineChart.Series.Add(range1, rangeLabel);
//position of the legend
//size of the chart
lineChart.SetSize(600, 300);
//add the chart at cell B6
lineChart.SetPosition(5, 0, 1, 0);
result = package.GetAsByteArray();
}
return(File(result, "application/ms-excel", $"Employee.xlsx"));
}
