//gavdcodeend 04
//gavdcodebegin 05
public static void ExcelEpplusInsertPieChart()
{
FileInfo myFileInfo = new FileInfo(@"C:\Temporary\ExcelEPPlus01.xlsx");
using (ExcelPackage excelPackage = new ExcelPackage(myFileInfo))
{
// Create a WorkSheet
ExcelWorksheet myWorksheet =
excelPackage.Workbook.Worksheets.Add("PieChartSheet");
// Fill cell data with a loop. The row and column indexes start at 1
Random rnd = new Random();
for (int counter = 1; counter <= 10; counter++)
{
myWorksheet.Cells[1, counter].Value = "Value " + counter;
myWorksheet.Cells[2, counter].Value = rnd.Next(5, 15);
}
// Create a new pie chart of type Pie3D
ExcelPieChart myPieChart = myWorksheet.Drawings.AddChart("pieChart",
eChartType.Pie3D) as ExcelPieChart;
// Set the properties of the chart
myPieChart.Title.Text = "PieChart Example";
myPieChart.Series.Add(ExcelRange.GetAddress(2, 1, 2, 10),
ExcelRange.GetAddress(1, 1, 1, 10));
myPieChart.Legend.Position = eLegendPosition.Bottom;
myPieChart.DataLabel.ShowPercent = true;
myPieChart.SetSize(500, 400);
myPieChart.SetPosition(4, 0, 2, 0);
excelPackage.SaveAs(myFileInfo);
}
}
private static void PlotGraphs(ExcelWorksheet wsDt, int startPos, int series, int EnergyColIndex)
{
var chart = (ExcelBarChart)wsDt.Drawings.AddChart("Energy Report", eChartType.ColumnClustered);
chart.Border.LineStyle = OfficeOpenXml.Drawing.eLineStyle.Solid;
chart.SetSize(710, 310);
chart.Title.Text = "Energy Report";
chart.Legend.Remove();
var serie1 = chart.Series.Add(ExcelRange.GetAddress(startPos, EnergyColIndex, series, EnergyColIndex), ExcelRange.GetAddress(startPos, 1, series, 1));
chart.YAxis.Title.Text = "KWh";
var chartz = (ExcelPieChart)wsDt.Drawings.AddChart("Energy Reportz", eChartType.Pie);
if (EnergyColIndex <= 8)
{
chart.SetPosition((series + 13) * 20, 22);
chartz.SetPosition(((series + 13) * 20), 766);
}
else
{
chart.SetPosition((series + 12) * 18, 22);
chartz.SetPosition(((series + 12) * 18), 766);
}
chartz.Border.LineStyle = OfficeOpenXml.Drawing.eLineStyle.Solid;
chartz.SetSize(420, 310);
chartz.Title.Text = "Energy Report";
var serie2 = chartz.Series.Add(ExcelRange.GetAddress(startPos, EnergyColIndex, series, EnergyColIndex), ExcelRange.GetAddress(startPos, 1, series, 1));
chartz.Legend.Position = eLegendPosition.Top;
}
public static void BindExcelCellListData(ExcelPackage package, ExcelWorksheet workSheet, string hiddenDataSheetName, int columnIndex, List <string> listData, bool allowNewEntries)
{
var dataCell = workSheet.DataValidations.AddListValidation(ExcelRange.GetAddress(2, columnIndex, ExcelPackage.MaxRows, columnIndex));
ExcelWorksheet dataSheet = package.Workbook.Worksheets.Add(hiddenDataSheetName);
dataSheet.Hidden = eWorkSheetHidden.Hidden;//hiding the hidden Sheet from user
int rowIndex = 1;
foreach (string strData in listData)
{
dataSheet.Cells[rowIndex, 1].Value = strData;
rowIndex++;
}
string cellRangeStatusList = "=" + dataSheet.ToString() + "!" + "$A$1:$A$" + rowIndex.ToString();
dataCell.Formula.ExcelFormula = cellRangeStatusList;
if (!allowNewEntries)
{
dataCell.ShowErrorMessage = true;
dataCell.PromptTitle = "Invalid Selection";
dataCell.Error = "Please select from list";
}
dataCell.AllowBlank = true;
}
public void Line2Test()
{
ExcelWorksheet worksheet = _pck.Workbook.Worksheets.Add("LineIssue");
ExcelChart chart = worksheet.Drawings.AddChart("LineChart", eChartType.Line);
worksheet.Cells["A1"].Value = 1;
worksheet.Cells["A2"].Value = 2;
worksheet.Cells["A3"].Value = 3;
worksheet.Cells["A4"].Value = 4;
worksheet.Cells["A5"].Value = 5;
worksheet.Cells["A6"].Value = 6;
worksheet.Cells["B1"].Value = 10000;
worksheet.Cells["B2"].Value = 10100;
worksheet.Cells["B3"].Value = 10200;
worksheet.Cells["B4"].Value = 10150;
worksheet.Cells["B5"].Value = 10250;
worksheet.Cells["B6"].Value = 10200;
chart.Series.Add(ExcelRange.GetAddress(1, 2, worksheet.Dimension.End.Row, 2),
ExcelRange.GetAddress(1, 1, worksheet.Dimension.End.Row, 1));
var Series = chart.Series[0];
chart.Series[0].Header = "Blah";
}
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>();
}
}
private static void PlotMonthDataGraphsMachineWise(string monthName, ExcelWorksheet wsDt, int startPos, int noOfMonths, int rowStartForGraphPos, int columnStartForGraphPos)
{
var chart = (ExcelBarChart)wsDt.Drawings.AddChart(monthName, eChartType.ColumnClustered);
chart.Border.LineStyle = OfficeOpenXml.Drawing.eLineStyle.Solid;
chart.SetSize(400, 200);
chart.Title.Font.Bold = true;
chart.Title.Font.Size = 11;
chart.Title.Text = monthName;
var serie1 = chart.Series.Add(ExcelRange.GetAddress(startPos, 2, startPos, noOfMonths), ExcelRange.GetAddress(9, 2, 10, noOfMonths));
chart.YAxis.Title.Text = "KWh";
chart.YAxis.Title.Font.Bold = true;
chart.YAxis.Title.Font.Size = 8;
chart.XAxis.Font.Size = 8;
chart.Legend.Remove();
chart.SetPosition(rowStartForGraphPos, 0, columnStartForGraphPos, 0);
}
public static void AddClusterGraph(ExcelPackage package, ExcelWorksheet inputsheet) //, int rows, int columns)
{
string sheetName = "Spend";
var sheet = package.Workbook.Worksheets.Copy(inputsheet.Name, sheetName);
// now find the position where $$ is less than the spendLimit value for graphing
// (We don't want to graph small numbers)
Double cellValue = sheet.Cells[sheet.Dimension.End.Row, sheet.Dimension.End.Column].GetValue <double>();
int rows = sheet.Dimension.End.Row;
int columns = sheet.Dimension.End.Column;
int position = sheet.Dimension.End.Row;
while (cellValue < Utils.upperSpendLimit)
{
position--;
cellValue = sheet.Cells[position, sheet.Dimension.End.Column].GetValue <double>();
}
// Add the chart
var barChart = sheet.Drawings.AddChart("Chart", eChartType.ColumnStacked) as ExcelBarChart;
barChart.SetSize(1000, 800);
// Notes: for Series.Add() first series is values - in this case will be Bx to <some other letter>x
// second series is labels for X Axis - should be just B1 to M1
for (int i = 2; i <= position; i++)
{
var valueRange = ExcelRange.GetAddress(i, 2, i, sheet.Dimension.End.Column);
var labelRange = ExcelRange.GetAddress(1, 2, 1, sheet.Dimension.End.Column);
var series = barChart.Series.Add(valueRange, labelRange);
series.HeaderAddress = new ExcelAddress($"'{sheetName}'!A{i}");
}
barChart.Title.Text = "";
barChart.YAxis.Font.Size = Utils.defaultFontSize;
barChart.RoundedCorners = false;
barChart.Style = Utils.chartStyle;
}
private void AddPatientDetailsIgSeries()
{
var groupedIgSeries = _patientDetailsVM.PatientImmunoglobulines.
GroupBy(pi => pi.ImmunoglobulinTypeId).
ToList();
int startRowIndex = 0;
int endRowIndex = 0;
for (var cursor = 0; cursor < groupedIgSeries.Count; cursor++)
{
var chart = (ExcelLineChart)_worksheet.Drawings.AddChart(IgChartName + cursor + "_Chart", eChartType.Line3D);
chart.SetSize(400, 300);
chart.SetPosition(10 + (cursor * 300), 500);
var groupNumber = cursor + 1;
var listGroup = groupedIgSeries[cursor];
var listGroupCount = listGroup.Count();
if (cursor == 0)
{
startRowIndex = groupNumber + 1;
endRowIndex = groupNumber + listGroupCount;
}
else
{
startRowIndex = startRowIndex + groupedIgSeries[cursor - 1].Count();
endRowIndex = endRowIndex + listGroupCount;
}
chart.Series.Add(ExcelRange.GetAddress(startRowIndex, 4, endRowIndex, 4),
ExcelRange.GetAddress(startRowIndex, 3, endRowIndex, 3));
string title = _worksheet.Cells["B" + startRowIndex.ToString()].
GetValue <string>();
chart.Series[0].Header = title;
chart.Title.Text = title;
}
}
public static void InsertLineGraph(ExcelPackage package, ExcelWorksheet inputSheet)
{
int seriesStartColumn = 2; // i.e. first column of data, column 1 is header
int seriesHeaderRow = 1;
string sheetname = "Trend";
var graphRows = new List <int>()
{
6, 11
}; // This list holds the rows for the graphs - 6 is for top 5, 11 is for top 10 (header is one row)
var sheet = package.Workbook.Worksheets.Copy(inputSheet.Name, sheetname);
int counter = 0;
// create a top 10 graph & a top 5 graph
foreach (var graphLimit in graphRows)
{
var chart = sheet.Drawings.AddChart($"Top{graphLimit}", eChartType.Line);
chart.SetSize(Utils.chartWidth, Utils.chartHeight);
chart.Style = Utils.chartStyle;
chart.Title.Text = "";
chart.YAxis.Font.Size = Utils.defaultFontSize;
chart.XAxis.Font.Size = Utils.defaultFontSize;
chart.RoundedCorners = false;
chart.SetPosition(Utils.chartHeight * counter, 0);
for (int i = 2; i <= graphLimit; i++)
{
var valueRange = ExcelRange.GetAddress(i, seriesStartColumn, i, sheet.Dimension.End.Column);
var labelRange = ExcelRange.GetAddress(seriesHeaderRow, seriesStartColumn, seriesHeaderRow, sheet.Dimension.End.Column);
var series = chart.Series.Add(valueRange, labelRange);
series.HeaderAddress = new ExcelAddress($"'{sheetname}'!A{i}");
}
counter++;
}
}
/// <summary>
/// This sample creates a new workbook from a template file containing a chart and populates it with Exchange rates from
/// the database and set the three series on the chart.
/// </summary>
/// <param name="connectionString">Connectionstring to the db</param>
/// <param name="template">the template</param>
/// <param name="outputdir">output dir</param>
/// <returns></returns>
public static string Run(string connectionString)
{
FileInfo template = FileInputUtil.GetFileInfo("17-FXReportFromDatabase", "GraphTemplate.xlsx");
using (ExcelPackage p = new ExcelPackage(template, true))
{
//Set up the headers
ExcelWorksheet ws = p.Workbook.Worksheets[0];
ws.Cells["A20"].Value = "Date";
ws.Cells["B20"].Value = "EOD Rate";
ws.Cells["B20:F20"].Merge = true;
ws.Cells["G20"].Value = "Change";
ws.Cells["G20:K20"].Merge = true;
ws.Cells["B20:K20"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
using (ExcelRange row = ws.Cells["A20:G20"])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
row.Style.Font.Color.SetColor(Color.White);
row.Style.Font.Bold = true;
}
ws.Cells["B21"].Value = "USD/SEK";
ws.Cells["C21"].Value = "USD/EUR";
ws.Cells["D21"].Value = "USD/INR";
ws.Cells["E21"].Value = "USD/CNY";
ws.Cells["F21"].Value = "USD/DKK";
ws.Cells["G21"].Value = "USD/SEK";
ws.Cells["H21"].Value = "USD/EUR";
ws.Cells["I21"].Value = "USD/INR";
ws.Cells["J21"].Value = "USD/CNY";
ws.Cells["K21"].Value = "USD/DKK";
using (ExcelRange row = ws.Cells["A21:K21"])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
row.Style.Font.Color.SetColor(Color.Black);
row.Style.Font.Bold = true;
}
int startRow = 22;
//Connect to the database and fill the data
using (var sqlConn = new SQLiteConnection(connectionString))
{
int row = startRow;
sqlConn.Open();
using (var sqlCmd = new SQLiteCommand("SELECT date, SUM(Case when CurrencyCodeTo = 'SEK' Then rate Else 0 END) AS [SEK], SUM(Case when CurrencyCodeTo = 'EUR' Then rate Else 0 END) AS [EUR], SUM(Case when CurrencyCodeTo = 'INR' Then rate Else 0 END) AS [INR], SUM(Case when CurrencyCodeTo = 'CNY' Then rate Else 0 END) AS [CNY], SUM(Case when CurrencyCodeTo = 'DKK' Then rate Else 0 END) AS [DKK] FROM CurrencyRate where [CurrencyCodeFrom]='USD' AND CurrencyCodeTo in ('SEK', 'EUR', 'INR','CNY','DKK') GROUP BY date ORDER BY date", sqlConn))
{
using (var sqlReader = sqlCmd.ExecuteReader())
{
// get the data and fill rows 22 onwards
while (sqlReader.Read())
{
ws.Cells[row, 1].Value = sqlReader[0];
ws.Cells[row, 2].Value = sqlReader[1];
ws.Cells[row, 3].Value = sqlReader[2];
ws.Cells[row, 4].Value = sqlReader[3];
ws.Cells[row, 5].Value = sqlReader[4];
ws.Cells[row, 6].Value = sqlReader[5];
row++;
}
}
//Set the numberformat
ws.Cells[startRow, 1, row - 1, 1].Style.Numberformat.Format = "yyyy-mm-dd";
ws.Cells[startRow, 2, row - 1, 6].Style.Numberformat.Format = "#,##0.0000";
//Set the Formulas
ws.Cells[startRow + 1, 7, row - 1, 11].Formula = $"B${startRow}/B{startRow+1}-1";
ws.Cells[startRow, 7, row - 1, 11].Style.Numberformat.Format = "0.00%";
}
//Set the series for the chart. The series must exist in the template or the program will crash.
var chart = ws.Drawings["SampleChart"].As.Chart.LineChart; //We know the chart is a linechart, so we can use the As.Chart.LineChart Property directly
chart.Title.Text = "Exchange rate %";
chart.Series[0].Header = "USD/SEK";
chart.Series[0].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[0].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 7, row - 1, 7);
chart.Series[1].Header = "USD/EUR";
chart.Series[1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 8, row - 1, 8);
chart.Series[2].Header = "USD/INR";
chart.Series[2].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[2].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 9, row - 1, 9);
var serie = chart.Series.Add("'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 10, row - 1, 10),
"'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1));
serie.Header = "USD/CNY";
serie.Marker.Style = eMarkerStyle.None;
serie = chart.Series.Add("'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 11, row - 1, 11),
"'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1));
serie.Header = "USD/DKK";
serie.Marker.Style = eMarkerStyle.None;
chart.Legend.Position = eLegendPosition.Bottom;
//Set the chart style
chart.StyleManager.SetChartStyle(236);
}
//Get the documet as a byte array from the stream and save it to disk. (This is useful in a webapplication) ...
var bin = p.GetAsByteArray();
FileInfo file = FileOutputUtil.GetFileInfo("17-FxReportFromDatabase.xlsx");
File.WriteAllBytes(file.FullName, bin);
return(file.FullName);
}
}
public static void ExcelBriefExport(DateTime datestart, DateTime datefinish, BindingListView <WrapBriefData> Data)
{
if (Data != null)
{
SystemArgs.PrintLog("Началось формирование выгрузки краткого анализа");
SaveFileDialog sfd = new SaveFileDialog()
{
DefaultExt = "",
Title = "Сохранение анализа",
Filter = "Файл Excel| *.xlsx",
FileName = Title + " от " + DateTime.Now.ToString().Replace('.', '_').Replace(':', '_'),
RestoreDirectory = true
};
if (sfd.ShowDialog() == DialogResult.OK)
{
if (!String.IsNullOrEmpty(sfd.FileName))
{
using (ExcelPackage excelPackage = new ExcelPackage())
{
excelPackage.Workbook.Properties.Author = "Дефекты";
excelPackage.Workbook.Properties.Title = "Анализ за период от " + datestart.ToShortDateString() + " до " + datefinish.ToShortDateString();
excelPackage.Workbook.Properties.Created = DateTime.Now;
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Анализ");
worksheet.Cells["G1:M1"].Merge = true;
worksheet.Cells["G1"].Value = "Анализ по дефектности";
worksheet.Cells["G1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["G2:M2"].Merge = true;
worksheet.Cells["G2"].Value = "слитков цилиндрических";
worksheet.Cells["G2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["G3:M3"].Merge = true;
worksheet.Cells["G3"].Value = "за период с " + datestart.ToShortDateString() + " по " + datefinish.ToShortDateString();
worksheet.Cells["G3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["B7"].Value = TitleX;
worksheet.Cells["C7"].Value = TitleY;
int DimensionEndRow;
foreach (var item in Data)
{
DimensionEndRow = worksheet.Dimension.End.Row + 1;
worksheet.Cells["B" + DimensionEndRow.ToString()].Value = item.Name;
worksheet.Cells["C" + DimensionEndRow.ToString()].Value = item.Value;
}
DimensionEndRow = worksheet.Dimension.End.Row;
var modelTable = worksheet.Cells["B7:C" + DimensionEndRow.ToString()];
modelTable.Style.Border.Top.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Left.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Right.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
modelTable.AutoFitColumns();
var chart = (ExcelBarChart)worksheet.Drawings.AddChart(Title, eChartType.ColumnClustered);
chart.SetSize(600, 600);
chart.SetPosition(6, 0, 10, 0);
chart.Title.Text = Title;
chart.Series.Add(ExcelRange.GetAddress(8, 3, DimensionEndRow, 3), ExcelRange.GetAddress(8, 2, DimensionEndRow, 2));
chart.Legend.Remove();
chart.XAxis.Title.Text = TitleX;
chart.YAxis.Title.Font.Size = 14;
chart.XAxis.Title.Font.Size = 14;
chart.YAxis.Title.Text = TitleY;
FileInfo fi = new FileInfo(sfd.FileName);
excelPackage.SaveAs(fi);
}
}
else
{
SystemArgs.PrintLog("Место сохранения отчета равно null");
MessageBox.Show("Необходимо ввести названия файла", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
SystemArgs.PrintLog("Не смогло начатся формирование выгрузки краткого отчета лист равен null");
MessageBox.Show("Для экспорта необходимо сформировать анализ", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// This sample creates a new workbook from a template file containing a chart and populates it with Exchangrates from
/// the Adventureworks database and set the three series on the chart.
/// </summary>
/// <param name="connectionString">Connectionstring to the Adventureworks db</param>
/// <param name="template">the template</param>
/// <param name="outputdir">output dir</param>
/// <returns></returns>
public static string RunSample4(string connectionString, FileInfo template, DirectoryInfo outputdir)
{
using (ExcelPackage p = new ExcelPackage(template, true))
{
//Set up the headers
ExcelWorksheet ws = p.Workbook.Worksheets[1];
ws.Cells["A20"].Value = "Date";
ws.Cells["B20"].Value = "EOD Rate";
ws.Cells["B20:D20"].Merge = true;
ws.Cells["E20"].Value = "Change";
ws.Cells["E20:G20"].Merge = true;
ws.Cells["B20:E20"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
using (ExcelRange row = ws.Cells["A20:G20"])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
row.Style.Font.Color.SetColor(Color.White);
row.Style.Font.Bold = true;
}
ws.Cells["B21"].Value = "USD/JPY";
ws.Cells["C21"].Value = "USD/EUR";
ws.Cells["D21"].Value = "USD/GBP";
ws.Cells["E21"].Value = "USD/JPY";
ws.Cells["F21"].Value = "USD/EUR";
ws.Cells["G21"].Value = "USD/GBP";
using (ExcelRange row = ws.Cells["A21:G21"])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
row.Style.Font.Color.SetColor(Color.Black);
row.Style.Font.Bold = true;
}
int startRow = 22;
//Connect to the database and fill the data
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
int row = startRow;
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand("SELECT CurrencyRateDate, SUM(Case when ToCurrencyCode = 'JPY' Then EndOfDayRate Else 0 END) AS [JPY], SUM(Case when ToCurrencyCode = 'EUR' Then EndOfDayRate Else 0 END) AS [EUR], SUM(Case when ToCurrencyCode = 'GBP' Then EndOfDayRate Else 0 END) AS [GBP] FROM [AdventureWorks].[Sales].[CurrencyRate] where [FromCurrencyCode]='USD' AND ToCurrencyCode in ('JPY', 'EUR', 'GBP') GROUP BY CurrencyRateDate ORDER BY CurrencyRateDate", sqlConn))
{
using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
{
// get the data and fill rows 22 onwards
while (sqlReader.Read())
{
ws.Cells[row, 1].Value = sqlReader[0];
ws.Cells[row, 2].Value = sqlReader[1];
ws.Cells[row, 3].Value = sqlReader[2];
ws.Cells[row, 4].Value = sqlReader[3];
row++;
}
}
//Set the numberformat
ws.Cells[startRow, 1, row - 1, 1].Style.Numberformat.Format = "yyyy-mm-dd";
ws.Cells[startRow, 2, row - 1, 4].Style.Numberformat.Format = "#,##0.0000";
//Set the Formulas
ws.Cells[startRow + 1, 5, row - 1, 7].Formula = string.Format("B${0}/B{1}-1", startRow, startRow + 1);
ws.Cells[startRow, 5, row - 1, 7].Style.Numberformat.Format = "0.00%";
}
//Set the series for the chart. The series must exist in the template or the program will crash.
ExcelChart chart = ((ExcelChart)ws.Drawings["SampleChart"]);
chart.Title.Text = "Exchange rate %";
chart.Series[0].Header = "USD/JPY";
chart.Series[0].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[0].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 5, row - 1, 5);
chart.Series[1].Header = "USD/EUR";
chart.Series[1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 6, row - 1, 6);
chart.Series[2].Header = "USD/GBP";
chart.Series[2].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[2].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 7, row - 1, 7);
}
//Get the documet as a byte array from the stream and save it to disk. (This is usefull in a webapplication) ...
Byte[] bin = p.GetAsByteArray();
string file = outputdir + "\\sample4.xlsx";
File.WriteAllBytes(file, bin);
return(file);
}
}
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();
}
}
/// <summary>
/// Create excel file with match generated
/// </summary>
/// <param name="menteeList"></param>
/// <param name="mentorList"></param>
public void CreateExcel(IEnumerable <Person> menteeList, IEnumerable <Person> mentorList, IEnumerable <Person> listPerson)
{
using (ExcelPackage excel = new ExcelPackage())
{
excel.Workbook.Worksheets.Add("Match");
// Target a worksheet
var worksheet = excel.Workbook.Worksheets["Match"];
int row = 1;
int col = 1;
worksheet.Cells[row, col++].Value = "Code";
worksheet.Cells[row, col++].Value = "Type";
worksheet.Cells[row, col++].Value = "Firs Name";
worksheet.Cells[row, col++].Value = "Last Name";
worksheet.Cells[row, col++].Value = "Email";
worksheet.Cells[row, col++].Value = "Score";
worksheet.Cells[row, col++].Value = "Match %";
worksheet.Cells[row, col++].Value = "Function";
worksheet.Cells[row, col++].Value = "Subfunction";
worksheet.Cells[row, col++].Value = "Industry";
worksheet.Cells[row, col++].Value = "Age Group";
worksheet.Cells[row, col++].Value = "Job Currently";
worksheet.Cells[row, col++].Value = "Member Type";
worksheet.Cells[row, col++].Value = "Mentorship Before";
worksheet.Cells[row, col++].Value = "Years Experience Canada";
worksheet.Cells[row, col++].Value = "LinkedIn";
worksheet.Cells[ExcelRange.GetAddress(row, 1, row, col)].Style.Font.Bold = true;
var dicMentorList = mentorList.ToDictionary(t => t.OrderNo);
int cod = 1;
bool colorChange = false;
foreach (var item in menteeList.Where(t => t.OrderNoAssigned != null))
{
row++;
col = 1;
Person person = null;
if (item.OrderNoAssigned != null && dicMentorList.ContainsKey(item.OrderNoAssigned))
{
person = dicMentorList.GetValueOrDefault(item.OrderNoAssigned);
}
worksheet.Cells[row, col++].Value = person != null ? cod: 0;
worksheet.Cells[row, col++].Value = "Mentee";
worksheet.Cells[row, col++].Value = item.FirsName;
worksheet.Cells[row, col++].Value = item.LastName;
worksheet.Cells[row, col++].Value = item.Email;
worksheet.Cells[row, col++].Value = item.Coincidences;
worksheet.Cells[row, col++].Value = (item.Coincidences * 100) / MAX_VALUE;
worksheet.Cells[row, col++].Value = item.Function;
worksheet.Cells[row, col++].Value = item.Subfunction;
worksheet.Cells[row, col++].Value = item.Industry;
worksheet.Cells[row, col++].Value = item.AgeGroup;
worksheet.Cells[row, col++].Value = item.JobCurrently;
worksheet.Cells[row, col++].Value = item.MemberType;
col += 2;
worksheet.Cells[row, col++].Value = item.LinkedIn;
if (colorChange)
{
worksheet.Cells[ExcelRange.GetAddress(row, 1, row, 16)].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[ExcelRange.GetAddress(row, 1, row, 16)].Style.Fill.BackgroundColor.SetColor(Color.LightSkyBlue);
}
else
{
worksheet.Cells[ExcelRange.GetAddress(row, 1, row, 16)].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[ExcelRange.GetAddress(row, 1, row, 16)].Style.Fill.BackgroundColor.SetColor(Color.LightYellow);
}
if (person != null)
{
row++;
col = 1;
worksheet.Cells[row, col++].Value = cod;
worksheet.Cells[row, col++].Value = "Mentor";
worksheet.Cells[row, col++].Value = person.FirsName;
worksheet.Cells[row, col++].Value = person.LastName;
worksheet.Cells[row, col++].Value = person.Email;
worksheet.Cells[row, col++].Value = ""; //person.Coincidences;
worksheet.Cells[row, col++].Value = ""; //(item.Coincidences * 100) / MAX_VALUE;
worksheet.Cells[row, col++].Value = person.Function;
worksheet.Cells[row, col++].Value = person.Subfunction;
worksheet.Cells[row, col++].Value = person.Industry;
worksheet.Cells[row, col++].Value = person.AgeGroup;
col++;
worksheet.Cells[row, col++].Value = person.MemberType;
worksheet.Cells[row, col++].Value = person.MentorshipBefore;
worksheet.Cells[row, col++].Value = person.YearsExperienceCanada;
worksheet.Cells[row, col++].Value = person.LinkedIn;
cod++;
if (colorChange)
{
worksheet.Cells[ExcelRange.GetAddress(row, 1, row, 16)].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[ExcelRange.GetAddress(row, 1, row, 16)].Style.Fill.BackgroundColor.SetColor(Color.LightSkyBlue);
}
else
{
worksheet.Cells[ExcelRange.GetAddress(row, 1, row, 16)].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[ExcelRange.GetAddress(row, 1, row, 16)].Style.Fill.BackgroundColor.SetColor(Color.LightYellow);
}
}
colorChange = colorChange ? false : true;
}
////////////////////////////////////////////////////////////////////////////
excel.Workbook.Worksheets.Add("Unmatch");
// Target a worksheet
var unmatch = excel.Workbook.Worksheets["Unmatch"];
row = 1;
col = 1;
unmatch.Cells[row, col++].Value = "Code";
unmatch.Cells[row, col++].Value = "Type";
unmatch.Cells[row, col++].Value = "Firs Name";
unmatch.Cells[row, col++].Value = "Last Name";
unmatch.Cells[row, col++].Value = "Email";
unmatch.Cells[row, col++].Value = "Function";
unmatch.Cells[row, col++].Value = "Subfunction";
unmatch.Cells[row, col++].Value = "Industry";
unmatch.Cells[row, col++].Value = "Age Group";
unmatch.Cells[row, col++].Value = "Member Type";
unmatch.Cells[row, col++].Value = "LinkedIn";
unmatch.Cells[ExcelRange.GetAddress(row, 1, row, col)].Style.Font.Bold = true;
foreach (var item in menteeList.Where(t => t.OrderNoAssigned == null).OrderBy(t => t.Function))
{
col = 1;
row++;
unmatch.Cells[row, col++].Value = row - 1;
unmatch.Cells[row, col++].Value = "Mentee";
unmatch.Cells[row, col++].Value = item.FirsName;
unmatch.Cells[row, col++].Value = item.LastName;
unmatch.Cells[row, col++].Value = item.Email;
unmatch.Cells[row, col++].Value = item.Function;
unmatch.Cells[row, col++].Value = item.Subfunction;
unmatch.Cells[row, col++].Value = item.Industry;
unmatch.Cells[row, col++].Value = item.AgeGroup;
unmatch.Cells[row, col++].Value = item.MemberType;
unmatch.Cells[row, col++].Value = item.LinkedIn;
}
var dicMenteeList = menteeList.Where(t => t.OrderNoAssigned != null).ToDictionary(t => t.OrderNoAssigned);
foreach (var item in mentorList)
{
if (!dicMenteeList.ContainsKey(item.OrderNo))
{
col = 1;
row++;
unmatch.Cells[row, col++].Value = row - 1;
unmatch.Cells[row, col++].Value = item.Type;
unmatch.Cells[row, col++].Value = item.FirsName;
unmatch.Cells[row, col++].Value = item.LastName;
unmatch.Cells[row, col++].Value = item.Email;
unmatch.Cells[row, col++].Value = item.Function;
unmatch.Cells[row, col++].Value = item.Subfunction;
unmatch.Cells[row, col++].Value = item.Industry;
unmatch.Cells[row, col++].Value = item.AgeGroup;
unmatch.Cells[row, col++].Value = item.MemberType;
unmatch.Cells[row, col++].Value = item.LinkedIn;
}
}
////////////////////////////////////////////////////////////////////////////
excel.Workbook.Worksheets.Add("Excluded");
// Target a worksheet
var excluded = excel.Workbook.Worksheets["Excluded"];
row = 1;
col = 1;
excluded.Cells[row, col++].Value = "Code";
excluded.Cells[row, col++].Value = "Type";
excluded.Cells[row, col++].Value = "Firs Name";
excluded.Cells[row, col++].Value = "Last Name";
excluded.Cells[row, col++].Value = "Email";
excluded.Cells[row, col++].Value = "Function";
excluded.Cells[row, col++].Value = "Subfunction";
excluded.Cells[row, col++].Value = "Industry";
excluded.Cells[row, col++].Value = "Age Group";
excluded.Cells[row, col++].Value = "Work permit";
excluded.Cells[ExcelRange.GetAddress(row, 1, row, col)].Style.Font.Bold = true;
foreach (var item in listPerson.Where(t => t.Type.Equals("Super Mentor") ||
t.Type.Equals("Volunteer") ||
(t.LegallyWorkCanada != null && t.LegallyWorkCanada.Equals("No"))
).OrderBy(t => t.Type))
{
col = 1;
row++;
excluded.Cells[row, col++].Value = row - 1;
excluded.Cells[row, col++].Value = item.Type;
excluded.Cells[row, col++].Value = item.FirsName;
excluded.Cells[row, col++].Value = item.LastName;
excluded.Cells[row, col++].Value = item.Email;
excluded.Cells[row, col++].Value = item.Function;
excluded.Cells[row, col++].Value = item.Subfunction;
excluded.Cells[row, col++].Value = item.Industry;
excluded.Cells[row, col++].Value = item.AgeGroup;
excluded.Cells[row, col++].Value = item.LegallyWorkCanada;
}
////////////////////////////////////////////////////////////////////////////
//worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
FileInfo excelFile = new FileInfo(@PATH_TO);
excel.SaveAs(excelFile);
Console.WriteLine("Match Process Finished");
}
}
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();
}
}
}
public void RunSample4()
{
using (ExcelPackage p = new ExcelPackage(new FileInfo("SampleApp/GraphTemplate.xlsx"), true))
{
//Set up the headers
ExcelWorksheet ws = p.Workbook.Worksheets[1];
ws.Cells["A20"].Value = "Date";
ws.Cells["B20"].Value = "EOD Rate";
ws.Cells["B20:D20"].Merge = true;
ws.Cells["E20"].Value = "Change";
ws.Cells["E20:G20"].Merge = true;
ws.Cells["B20:E20"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
using (ExcelRange r = ws.Cells["A20:G20"])
{
r.Style.Fill.PatternType = ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
r.Style.Font.Color.SetColor(Color.White);
r.Style.Font.Bold = true;
}
ws.Cells["B21"].Value = "USD/JPY";
ws.Cells["C21"].Value = "USD/EUR";
ws.Cells["D21"].Value = "USD/GBP";
ws.Cells["E21"].Value = "USD/JPY";
ws.Cells["F21"].Value = "USD/EUR";
ws.Cells["G21"].Value = "USD/GBP";
using (ExcelRange r = ws.Cells["A21:G21"])
{
r.Style.Fill.PatternType = ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
r.Style.Font.Color.SetColor(Color.Black);
r.Style.Font.Bold = true;
}
int startRow = 22;
//Connect to the database and fill the data
int row = startRow;
// get the data and fill rows 22 onwards
var rnd = new Random();
while (row < 50)
{
ws.Cells[row, 1].Value = rnd.Next();
ws.Cells[row, 2].Value = rnd.Next();
ws.Cells[row, 3].Value = rnd.Next();
ws.Cells[row, 4].Value = rnd.Next();
row++;
}
//Set the numberformat
ws.Cells[startRow, 1, row - 1, 1].Style.Numberformat.Format = "yyyy-mm-dd";
ws.Cells[startRow, 2, row - 1, 4].Style.Numberformat.Format = "#,##0.0000";
//Set the Formulas
ws.Cells[startRow + 1, 5, row - 1, 7].Formula = string.Format("B${0}/B{1}-1", startRow, startRow + 1);
ws.Cells[startRow, 5, row - 1, 7].Style.Numberformat.Format = "0.00%";
//Set the series for the chart. The series must exist in the template or the program will crash.
ExcelChart chart = ((ExcelChart)ws.Drawings["SampleChart"]);
chart.Title.Text = "Exchange rate %";
chart.Series[0].Header = "USD/JPY";
chart.Series[0].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[0].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 5, row - 1, 5);
chart.Series[1].Header = "USD/EUR";
chart.Series[1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 6, row - 1, 6);
chart.Series[2].Header = "USD/GBP";
chart.Series[2].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[2].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 7, row - 1, 7);
//Get the documet as a byte array from the stream and save it to disk. (This is usefull in a webapplication) ...
Byte[] bin = p.GetAsByteArray();
string file = Path.Combine("bin", "sample4.xlsx");
File.WriteAllBytes(file, bin);
}
}
/// <summary>
/// Add the second sheet containg the graphs
/// </summary>
/// <param name="pck">Package</param>
/// <param name="rows"></param>
/// <param name="header"></param>
private static void AddGraphs(ExcelPackage pck, int rows, string dir)
{
var ws = pck.Workbook.Worksheets.Add("Statistics");
ws.View.ShowGridLines = false;
//Set first the header and format it
ws.Cells["A1"].Value = "Statistics for ";
using (ExcelRange r = ws.Cells["A1:O1"])
{
r.Merge = true;
r.Style.Font.SetFromFont(new Font("Arial", 22, FontStyle.Regular));
r.Style.Font.Color.SetColor(Color.White);
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
}
//Use the RichText property to change the font for the directory part of the cell
var rtDir = ws.Cells["A1"].RichText.Add(dir);
rtDir.FontName = "Consolas";
rtDir.Size = 18;
//Start with the Extention Size
List <StatItem> lst = new List <StatItem>(_extStat.Values);
lst.Sort();
//Add rows
int row = AddStatRows(ws, lst, 2, "Extensions", "Size");
//Add commets to the Extensions header
AddComments(ws);
//Add the piechart
var pieChart = ws.Drawings.AddChart("crtExtensionsSize", eChartType.PieExploded3D) as ExcelPieChart;
//Set top left corner to row 1 column 2
pieChart.SetPosition(1, 0, 2, 0);
pieChart.SetSize(400, 400);
pieChart.Series.Add(ExcelRange.GetAddress(3, 2, row - 1, 2), ExcelRange.GetAddress(3, 1, row - 1, 1));
pieChart.Title.Text = "Extension Size";
//Set datalabels and remove the legend
pieChart.DataLabel.ShowCategory = true;
pieChart.DataLabel.ShowPercent = true;
pieChart.DataLabel.ShowLeaderLines = true;
pieChart.Legend.Remove();
//Resort on Count and add the rows
lst.Sort((first, second) => first.Count <second.Count ? -1 : first.Count> second.Count ? 1 : 0);
row = AddStatRows(ws, lst, 16, "", "Count");
//Add the Doughnut chart
var doughtnutChart = ws.Drawings.AddChart("crtExtensionCount", eChartType.DoughnutExploded) as ExcelDoughnutChart;
//Set position to row 1 column 7 and 16 pixels offset
doughtnutChart.SetPosition(1, 0, 8, 16);
doughtnutChart.SetSize(400, 400);
doughtnutChart.Series.Add(ExcelRange.GetAddress(16, 2, row - 1, 2), ExcelRange.GetAddress(16, 1, row - 1, 1));
doughtnutChart.Title.Text = "Extension Count";
doughtnutChart.DataLabel.ShowPercent = true;
doughtnutChart.DataLabel.ShowLeaderLines = true;
doughtnutChart.Style = eChartStyle.Style26; //3D look
//Top-10 filesize
_fileSize.Sort();
row = AddStatRows(ws, _fileSize, 29, "Files", "Size");
var barChart = ws.Drawings.AddChart("crtFiles", eChartType.BarClustered3D) as ExcelBarChart;
//3d Settings
barChart.View3D.RotX = 0;
barChart.View3D.Perspective = 0;
barChart.SetPosition(22, 0, 2, 0);
barChart.SetSize(800, 398);
barChart.Series.Add(ExcelRange.GetAddress(30, 2, row - 1, 2), ExcelRange.GetAddress(30, 1, row - 1, 1));
//barChart.Series[0].Header = "Size";
barChart.Title.Text = "Top File size";
//Format the Size and Count column
ws.Cells["B3:B42"].Style.Numberformat.Format = "#,##0";
//Set a border around
ws.Cells["A1:A43"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
ws.Cells["A1:O1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
ws.Cells["O1:O43"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
ws.Cells["A43:O43"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
ws.Cells[1, 1, row, 2].AutoFitColumns(1);
//And last the printersettings
ws.PrinterSettings.Orientation = eOrientation.Landscape;
ws.PrinterSettings.FitToPage = true;
ws.PrinterSettings.Scale = 67;
}
public async Task <IActionResult> ExportExcel()
{
//step1: create array to holder header labels
string[] col_names = new string[] {
"Type",
"Main Group",
"Sub Group",
"Competency",
"Required Level",
"Current Level",
"Gap"
};
//step2: create result byte array
byte[] byteData;
var result = await GetUserCompetency();
if (result == null)
{
SetNotification("Failed to execute the request!!", NotificationType.Failure, "Failed");
return(View("UserCompetency"));
}
//step3: create a new package using memory safe structure
using (var package = new ExcelPackage())
{
//step4: create a new worksheet
var worksheet = package.Workbook.Worksheets.Add("final");
//step5: fill in header row
//worksheet.Cells[row,col]. {Style, Value}
for (int i = 0; i < col_names.Length; i++)
{
worksheet.Cells[1, i + 1].Style.Font.Size = 14; //font
worksheet.Cells[1, i + 1].Value = col_names[i]; //value
worksheet.Cells[1, i + 1].Style.Font.Bold = true; //bold
//border the cell
worksheet.Cells[1, i + 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
//set background color for each sell
worksheet.Cells[1, i + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[1, i + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 243, 214));
}
int row = 2;
//step6: loop through query result and fill in cells
foreach (var item in result.ToList())
{
for (int col = 1; col <= col_names.Length; col++)
{
worksheet.Cells[row, col].Style.Font.Size = 12;
//worksheet.Cells[row, col].Style.Font.Bold = true;
worksheet.Cells[row, col].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
//set row,column data
//worksheet.Cells[row, 1,].Merge
worksheet.Cells[row, 1].Value = item.Type;
worksheet.Cells[row, 2].Value = item.MainGroup;
worksheet.Cells[row, 3].Value = item.SubGroup;
worksheet.Cells[row, 4].Value = item.Competency;
worksheet.Cells[row, 5].Value = item.RequiredLevel;
worksheet.Cells[row, 6].Value = item.CurrentLevel;
worksheet.Cells[row, 7].Style.Fill.PatternType = ExcelFillStyle.Solid;
switch (item.Gap)
{
case 0:
worksheet.Cells[row, 7].Style.Fill.BackgroundColor.SetColor(Color.Red);
break;
case 1:
worksheet.Cells[row, 7].Style.Fill.BackgroundColor.SetColor(Color.Orange);
break;
case 2:
worksheet.Cells[row, 7].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
break;
default:
worksheet.Cells[row, 7].Style.Fill.BackgroundColor.SetColor(Color.White);
break;
}
worksheet.Cells[row, 7].Value = item.Gap;
//toggle background color
//even row with ribbon style
if (row % 2 == 0)
{
for (int i = 1; i < col_names.Length; i++)
{
worksheet.Cells[row, i].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, i].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
}
}
row++;
}
//step7: auto fit columns
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
//---------------------------------------------------------------------------------------------
#region PieChart
//create a WorkSheet
//ExcelWorksheet worksheet1 = package.Workbook.Worksheets.Add("DummyChart");
////fill cell data with a loop, note that row and column indexes start at 1
//Random rnd = new Random();
//for (int i = 1; i <= 10; i++)
//{
// worksheet1.Cells[1, i].Value = "Value " + i;
// worksheet1.Cells[2, i].Value = rnd.Next(5, 15);
//}
////create a new piechart of type Pie3D
//ExcelPieChart pieChart = worksheet1.Drawings.AddChart("pieChart", eChartType.Pie3D) as ExcelPieChart;
////set the title
//pieChart.Title.Text = "PieChart Example";
////select the ranges for the pie. First the values, then the header range
//pieChart.Series.Add(ExcelRange.GetAddress(2, 1, 2, 10), ExcelRange.GetAddress(1, 1, 1, 10));
////position of the legend
//pieChart.Legend.Position = eLegendPosition.Bottom;
////show the percentages in the pie
//pieChart.DataLabel.ShowPercent = true;
////size of the chart
//pieChart.SetSize(500, 400);
////add the chart at cell C5
//pieChart.SetPosition(4, 0, 2, 0);
#endregion
//----------------------------------------------------------------------------------------------------------
#region Line Chart
//ExcelWorksheet worksheet2 = package.Workbook.Worksheets.Add("Dummy Line");
////fill cell data with a loop, note that row and column indexes start at 1
//Random rnd1 = new Random();
//for (int i = 2; i <= 11; i++)
//{
// worksheet2.Cells[1, i].Value = "Value " + (i - 1);
// worksheet2.Cells[2, i].Value = rnd1.Next(5, 25);
// worksheet2.Cells[3, i].Value = rnd1.Next(5, 25);
//}
//worksheet2.Cells[2, 1].Value = "Age 1";
//worksheet2.Cells[3, 1].Value = "Age 2";
////create a new piechart of type Line
//ExcelLineChart lineChart = worksheet2.Drawings.AddChart("lineChart", eChartType.Line) as ExcelLineChart;
////set the title
//lineChart.Title.Text = "LineChart Example";
////create the ranges for the chart
//var rangeLabel = worksheet2.Cells["B1:K1"];
//var range1 = worksheet2.Cells["B2:K2"];
//var range2 = worksheet2.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(700, 300);
////add the chart at cell B6
//lineChart.SetPosition(5, 0, 1, 0);
#endregion
//------------------------------------------------------------------------------------------------
List <ColumnChart> chartData = new List <ColumnChart>();
var grpType = result.GroupBy(x => x.Type);
foreach (var grp in grpType)
{
chartData.Add(new ColumnChart()
{
Type = grp.Key,
RequiredLevel = grp.Average(x => x.RequiredLevel),
CurrentLevel = grp.Average(x => x.CurrentLevel)
});
}
if (chartData.Any())
{
var ws = package.Workbook.Worksheets.Add("newsheet");
var count = chartData.Count;
//Some data
for (var i = 1; i <= chartData.Count; i++)
{
var item = chartData[i - 1];
var area = (i + 1 + 10).ToString();
ws.Cells["A" + area].Value = item.Type;
ws.Cells["B" + area].Value = item.RequiredLevel;
ws.Cells["C" + area].Value = item.CurrentLevel;
}
//Create the chart
var chartRequiredLevel = (ExcelBarChart)ws.Drawings.AddChart("RequiredLevel", eChartType.ColumnClustered);
chartRequiredLevel.SetSize(1000, 600);
chartRequiredLevel.SetPosition(10, 10);
chartRequiredLevel.Style = eChartStyle.Style10;
chartRequiredLevel.Title.Text = "User Competency Graph";
chartRequiredLevel.Series.Add(ExcelRange.GetAddress(12, 2, chartData.Count + 1 + 10, 2), ExcelRange.GetAddress(12, 1, chartData.Count + 1 + 10, 1)).Header = "Average of Required Level";
chartRequiredLevel.Series.Add(ExcelRange.GetAddress(12, 3, chartData.Count + 1 + 10, 3), ExcelRange.GetAddress(12, 1, chartData.Count + 1 + 10, 1)).Header = "Average of Current Level";
chartRequiredLevel.Legend.Position = eLegendPosition.Right;
chartRequiredLevel.VaryColors = true;
}
//step8: convert the package as byte array
byteData = package.GetAsByteArray();
}//end using
//step9: return byte array as a file
return(File(byteData, "application/vnd.ms-excel", $"UserCompetency{DateTime.Now.ToString("MMddyyyy")}.xls"));
}//end fun
private void SaveXlsx(string spath)
{
FileInfo newFile = new FileInfo(spath + ".xlsx");
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(spath + ".xlsx");
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
ExcelWorksheet ws = package.Workbook.Worksheets.Add(zg[0].GraphPane.Title.Text);
ExcelChart chart = ws.Drawings.AddChart(zg[0].GraphPane.Title.Text, eChartType.XYScatterLinesNoMarkers);
int b = 1;
string a, c;
for (int i = 0; i < zg[0].GraphPane.CurveList[0].Points.Count; i++)
{
ws.Cells[i + 2, b].Value = i;
}
foreach (var _c in zg[0].GraphPane.CurveList)
{
b++;
for (int i = 0; i < _c.Points.Count; i++)
{
ws.Cells[(int)_c.Points[i].Y + 2, b].Value = _c.Points[i].X;
}
a = ExcelRange.GetAddress((int)_c.Points[0].Y + 2, 1, (int)_c.Points[_c.Points.Count - 1].Y + 2, 1);
c = ExcelRange.GetAddress((int)_c.Points[0].Y + 2, b, (int)_c.Points[_c.Points.Count - 1].Y + 2, b);
chart.Series.Add(a, c);
chart.Legend.Remove();
}
ws = package.Workbook.Worksheets.Add(zg[1].GraphPane.Title.Text);
chart = ws.Drawings.AddChart(zg[1].GraphPane.Title.Text, eChartType.XYScatterLinesNoMarkers);
for (int i = 0; i < zg[1].GraphPane.CurveList[1].Points.Count; i++)
{
ws.Cells[i + 2, 1].Value = zg[1].GraphPane.CurveList[1].Points[i].X;
ws.Cells[i + 2, 2].Value = zg[1].GraphPane.CurveList[1].Points[i].Y;
}
a = ExcelRange.GetAddress(2, 1, zg[1].GraphPane.CurveList[1].Points.Count + 1, 1);
c = ExcelRange.GetAddress(2, 2, zg[1].GraphPane.CurveList[1].Points.Count + 1, 2);
chart.Series.Add(c, a);
chart.Legend.Remove();
ws.Cells[2, 3].Value = zg[1].GraphPane.CurveList[0].Points[0].X;
ws.Cells[2, 4].Value = zg[1].GraphPane.CurveList[0].Points[0].Y;
ws.Cells[3, 3].Value = zg[1].GraphPane.CurveList[0].Points[1].X;
ws.Cells[3, 4].Value = zg[1].GraphPane.CurveList[0].Points[1].Y;
a = ExcelRange.GetAddress(2, 3, 3, 3);
c = ExcelRange.GetAddress(2, 4, 3, 4);
chart.Series.Add(c, a);
chart.Legend.Remove();
ws = package.Workbook.Worksheets.Add(zg[2].GraphPane.Title.Text);
chart = ws.Drawings.AddChart(zg[2].GraphPane.Title.Text, eChartType.XYScatterLinesNoMarkers);
for (int i = 0; i < zg[2].GraphPane.CurveList[0].Points.Count; i++)
{
ws.Cells[i + 2, 1].Value = zg[2].GraphPane.CurveList[0].Points[i].X;
ws.Cells[i + 2, 2].Value = zg[2].GraphPane.CurveList[0].Points[i].Y;
}
a = ExcelRange.GetAddress(2, 1, zg[2].GraphPane.CurveList[0].Points.Count + 1, 1);
c = ExcelRange.GetAddress(2, 2, zg[2].GraphPane.CurveList[0].Points.Count + 1, 2);
chart.Series.Add(c, a);
chart.Legend.Remove();
package.Workbook.Properties.Title = textBox4.Text;
package.Workbook.Properties.Author = Environment.UserName;
package.Workbook.Properties.Comments = "Расчёт выполнен при помощи POSProgram";
package.Save();
}
}
public static void AddChangeGraph(ExcelPackage package, ExcelWorksheet inputsheet)
{
string sheetName = "Change";
// Copy the existing worksheet to a new one as we need to make changes
ExcelWorksheet sheet = package.Workbook.Worksheets.Copy(inputsheet.Name, sheetName);
// Add a new column called Change, and then add a formula into each cell to calculate the change
// formula is typically M2-L2 or something like that.
int columns = sheet.Dimension.End.Column + 1;
int rows = sheet.Dimension.End.Row;
sheet.Cells[1, columns].Value = "Change";
// NOTE TO SELF: You have to use a variable that won't change when you're adding entries to a spreadsheet
// If you just refer to the end of the spreadsheet, that tends to move when you add stuff.
for (int i = 2; i <= rows; i++)
{
var thisMonthCell = sheet.Cells[i, columns - 1].Address;
var lastMonthCell = sheet.Cells[i, columns - 2].Address;
sheet.Cells[i, columns].Formula = $"{thisMonthCell}-{lastMonthCell}";
sheet.Cells[i, columns].Style.Numberformat.Format = "\"$\"#,##0.00";
}
sheet.Calculate();
// Sort from highest to lowest.
using (ExcelRange excelRange = sheet.Cells[1, 1, sheet.Dimension.End.Row, sheet.Dimension.End.Column])
{
// sort is zero based, the range isn't so be careful
// also remember that you're sorting the range, not the entire sheet
excelRange.Sort(excelRange.Columns - 1, Utils.sortDescending);
}
// Now remove any rows that are <$100 & >-$100
for (int i = sheet.Dimension.End.Row; i >= 2; i--)
{
double cellValue;
Double.TryParse(sheet.Cells[i, sheet.Dimension.End.Column].Value.ToString(), out cellValue);
if (cellValue < Utils.upperSpendLimit && cellValue > Utils.lowerSpendLimit)
{
sheet.DeleteRow(i);
}
}
// This might be weird, but when you have done a sort on a column with formulas, the sort is "correct"
// but the formulas in the cells still refer to their source cells. So if you do anything like a recalculate or a sort in Excel
// things go sideways. So re-enter the formulas to get it to be sorted and correct.
for (int i = 2; i <= sheet.Dimension.End.Row; i++)
{
var thisMonthCell = sheet.Cells[i, sheet.Dimension.End.Column - 1].Address;
var lastMonthCell = sheet.Cells[i, sheet.Dimension.End.Column - 2].Address;
sheet.Cells[i, columns].Formula = $"{thisMonthCell}-{lastMonthCell}";
}
// Insert our clustered column chart
var chart = sheet.Drawings.AddChart("Chart", eChartType.ColumnClustered);
chart.SetSize(Utils.chartWidth, Utils.chartHeight);
// Add the values from each row as a separate series - this is the same as graphing the data in one lump and clicking "Switch row/column" in Excel.
// because of how we want this to display our label range stays constant as the header cell for the change column (should be "Change")
for (int i = 2; i <= sheet.Dimension.End.Row; i++)
{
var valueRange = ExcelRange.GetAddress(i, sheet.Dimension.End.Column);
var labelRange = ExcelRange.GetAddress(1, sheet.Dimension.End.Column);
var series = chart.Series.Add(valueRange, labelRange);
series.HeaderAddress = new ExcelAddress($"'{sheetName}'!A{i}");
}
//Formatting
chart.XAxis.Title.Text = "";
chart.Title.Text = "";
chart.YAxis.Font.Size = Utils.defaultFontSize;
chart.XAxis.Font.Size = Utils.defaultFontSize;
chart.RoundedCorners = false;
chart.Style = Utils.chartStyle;
}
public static void BindDataValidationForExcel(Database db, ExcelWorksheet worksheet, ExcelPackage package, int columnIndex, string enumSheetName, string enumType, int minValue, int maxValue, string columnName, ExcelDataValidation dataValidationType)
{
try
{
switch (dataValidationType)
{
case ExcelDataValidation.TextLength:
#region Text Length Data Validation
var textcolumn = worksheet.DataValidations.AddTextLengthValidation(ExcelRange.GetAddress(2, columnIndex, ExcelPackage.MaxRows, columnIndex));
textcolumn.ShowErrorMessage = true;
textcolumn.ErrorStyle = ExcelDataValidationWarningStyle.stop;
textcolumn.ErrorTitle = "Error";
textcolumn.Error = string.Format(columnName + " must be less than or equal to {0} characters.", maxValue);
textcolumn.Formula.Value = minValue;
textcolumn.Formula2.Value = maxValue;
textcolumn.AllowBlank = false;
textcolumn.ShowInputMessage = true;
//textcolumn.Prompt = "Please enter " + columnName;
break;
#endregion
case ExcelDataValidation.Integer:
#region Integer Data Validation
var integercolumn = worksheet.DataValidations.AddIntegerValidation(ExcelRange.GetAddress(2, columnIndex, ExcelPackage.MaxRows, columnIndex));
integercolumn.ErrorStyle = ExcelDataValidationWarningStyle.stop;
integercolumn.PromptTitle = "Enter a integer value here";
integercolumn.Prompt = "Value should be between 0 and 9";
integercolumn.ShowInputMessage = true;
integercolumn.ErrorTitle = "Invalid data error";
integercolumn.Error = string.Format(columnName + " must be between {0} and {1}.", 1, 9);
integercolumn.ShowErrorMessage = true;
integercolumn.Operator = ExcelDataValidationOperator.between;
integercolumn.Formula.Value = 0;
integercolumn.Formula2.Value = maxValue;
break;
#endregion
case ExcelDataValidation.DecimalValidationWithMaxLength:
#region Decimal Data Validation
var decimalcolumn2 = worksheet.DataValidations.AddDecimalValidation(ExcelRange.GetAddress(2, columnIndex, ExcelPackage.MaxRows, columnIndex));
decimalcolumn2.ShowErrorMessage = true;
decimalcolumn2.ErrorStyle = ExcelDataValidationWarningStyle.stop;
decimalcolumn2.ErrorTitle = "Invalid data error";
decimalcolumn2.Error = "Value must be numeric and less than or equal to " + maxValue + " digits";
decimalcolumn2.Formula.Value = minValue;
//decimalcolumn2.Formula.Value = 0D;
string maxChar = string.Empty;
for (int i = 0; i < maxValue; i++)
{
maxChar += "9";
}
decimalcolumn2.Operator = ExcelDataValidationOperator.between;
decimalcolumn2.Formula2.Value = Convert.ToDouble(maxChar);
break;
#endregion
default:
break;
}
}
catch
{
throw;
}
}
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();
}
}
private static void PlotGraphsForDayType(ExcelWorksheet wsDt, int startPos, int series, int EnergyColIndex)
{
int val = 18;
string ColValue = string.Format("A11:A{0}", series.ToString());
//string NextColValue = string.Format("J11:J{0}", series.ToString());
string NextColValue = string.Format("R11:R{0}", series.ToString());
wsDt.Cells[ColValue].Copy(wsDt.Cells[NextColValue]);
wsDt.Cells[NextColValue].Style.Numberformat.Format = "dd-MMM-yyyy";
//wsDt.Column(10).Width = 0.3;
wsDt.Cells[NextColValue].Style.Font.Color.SetColor(Color.White);
var chart = (ExcelBarChart)wsDt.Drawings.AddChart("Energy Report", eChartType.ColumnClustered);
chart.Title.Text = "Energy Report";
chart.YAxis.Title.Text = "KWh";
chart.Series.Add(ExcelRange.GetAddress(startPos, EnergyColIndex, series, EnergyColIndex), ExcelRange.GetAddress(startPos, val, series, val));
chart.Border.LineStyle = OfficeOpenXml.Drawing.eLineStyle.Solid;
chart.Legend.Remove();
var chartz = (ExcelPieChart)wsDt.Drawings.AddChart("Energy Reportz", eChartType.Pie);
chartz.Border.LineStyle = OfficeOpenXml.Drawing.eLineStyle.Solid;
chartz.Title.Text = "Energy Report";
chartz.Series.Add(ExcelRange.GetAddress(startPos, EnergyColIndex, series, EnergyColIndex), ExcelRange.GetAddress(startPos, val, series, val));
chart.XAxis.Format = "dd-MMM-yyyy";
if (series <= 27)
{
chart.SetPosition((series + 12) * 15, 22);
chartz.SetPosition(((series + 12) * 15), 930);
chartz.Legend.Position = eLegendPosition.TopRight;
chart.SetSize(900, 350);
chartz.SetSize(460, 350);
}
else
{
if (series >= 36 && series < 40)
{
chart.SetPosition(((series + 12) * 15) + 55, 22);
chartz.SetPosition(((series + 12) * 15) + 400, 22);
}
else if (series >= 40)
{
chart.SetPosition(((series + 12) * 15) + 270, 22);
chartz.SetPosition(((series + 12) * 15) + 620, 22);
}
else
{
chart.SetPosition(((series + 12) * 15) + 100, 22);
chartz.SetPosition(((series + 12) * 15) + 420, 22);
}
chart.SetSize(1255, 300);
chartz.SetSize(1255, 500);
}
chartz.Legend.Position = eLegendPosition.Top;
}
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();
}
}
public static void ByDate(DateTime DateStart, DateTime DateFinish)
{
SystemArgs.PrintLog("Началось формирование отчета");
SaveFileDialog sfd = new SaveFileDialog();
sfd.DefaultExt = "";
sfd.Title = "Сохранение отчета";
sfd.Filter = "Файл Excel| *.xlsx";
sfd.FileName = "Отчет от " + DateTime.Now.ToString().Replace('.', '_').Replace(':', '_');
sfd.RestoreDirectory = true;
if (sfd.ShowDialog() == DialogResult.OK)
{
SystemArgs.PrintLog("Выбрано место сохранения отчета");
if (!String.IsNullOrEmpty(sfd.FileName))
{
using (ExcelPackage excelPackage = new ExcelPackage())
{
//Настройки excel отчета
excelPackage.Workbook.Properties.Author = "Дефекты";
excelPackage.Workbook.Properties.Title = "Отчет за период от " + DateStart.ToShortDateString() + " до " + DateFinish.ToShortDateString();
excelPackage.Workbook.Properties.Created = DateTime.Now;
//Создания листа
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Отчет");
//Добавление шапки
worksheet.Cells["G1:M1"].Merge = true;
worksheet.Cells["G1"].Value = "Отчет по дефектности";
worksheet.Cells["G1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["G2:M2"].Merge = true;
worksheet.Cells["G2"].Value = "слитков цилиндрических";
worksheet.Cells["G2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["G3:M3"].Merge = true;
worksheet.Cells["G3"].Value = "за период с " + DateStart.ToShortDateString() + " по " + DateFinish.ToShortDateString();
worksheet.Cells["G3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var PositionsReport = SystemArgs.Positions.Where(date => (date.DateCreate >= DateStart) && (date.DateCreate <= DateFinish)).ToList();
//Брак
worksheet.Cells["A6"].Value = "Брак:";
var WeightReport = PositionsReport.Sum(pos => pos.Weight);
var CountReport = PositionsReport.Sum(pos => pos.Count);
worksheet.Cells["A7"].Value = "-";
worksheet.Cells["A8"].Value = "-";
worksheet.Cells["B7"].Value = WeightReport.ToString() + " тонн(ы)";
worksheet.Cells["B8"].Value = CountReport.ToString() + " слитки(ов)";
//По видам деффектов
worksheet.Cells["A10:F10"].Merge = true;
worksheet.Cells["A10"].Value = "По видам деффектов (пропуская нулевые):";
var DefectCount = from dc in PositionsReport
group dc by dc.Description.Name into g
select new { NameDefect = g.Key, Value = g.Count() };
int DimensionEndRow;
foreach (var item in DefectCount)
{
DimensionEndRow = worksheet.Dimension.End.Row + 1;
worksheet.Cells["A" + DimensionEndRow.ToString()].Value = "-";
worksheet.Cells["B" + DimensionEndRow.ToString()].Value = item.NameDefect;
worksheet.Cells["C" + DimensionEndRow.ToString()].Value = item.Value;
}
//По бригадам (включая нулевые)
DimensionEndRow = worksheet.Dimension.End.Row + 2;
int ForDiag = DimensionEndRow + 1;
worksheet.Cells["A" + DimensionEndRow.ToString() + ":F" + DimensionEndRow.ToString()].Merge = true;
worksheet.Cells["A" + DimensionEndRow.ToString()].Value = "По бригадам (включая нулевые):";
var BrigadeCount = from dc in PositionsReport
group dc by dc.NumBrigade.Name into g
select new { NameBrigade = g.Key, Weight = g.Sum(t => t.Weight), Melt = g.Count() };
foreach (var item in BrigadeCount)
{
DimensionEndRow = worksheet.Dimension.End.Row + 1;
worksheet.Cells["A" + DimensionEndRow.ToString()].Value = "-";
worksheet.Cells["B" + DimensionEndRow.ToString()].Value = item.NameBrigade;
worksheet.Cells["C" + DimensionEndRow.ToString()].Value = item.Weight;
worksheet.Cells["D" + DimensionEndRow.ToString()].Value = " тонн(ы)";
worksheet.Cells["E" + DimensionEndRow.ToString()].Value = item.Melt;
worksheet.Cells["F" + DimensionEndRow.ToString()].Value = " плавка(и)";
}
worksheet.Cells["A6:F" + DimensionEndRow + 1.ToString()].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["B7:B8"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
worksheet.Cells["A6:G" + DimensionEndRow + 1.ToString()].AutoFitColumns();
//Добавление диаграммы
var chart = (ExcelBarChart)worksheet.Drawings.AddChart("Отчет по параметрам", eChartType.ColumnClustered);
chart.Legend.Position = eLegendPosition.Bottom;
chart.SetSize(600, 300);
chart.SetPosition(4, 0, 10, 0);
chart.Title.Text = "Отчет по параметрам";
chart.Series.Add(ExcelRange.GetAddress(ForDiag, 3, DimensionEndRow, 3), ExcelRange.GetAddress(ForDiag, 2, DimensionEndRow, 2));
chart.Series[0].Header = "тонн(ы)";
var chartType2 = chart.PlotArea.ChartTypes.Add(eChartType.LineMarkers);
var serie2 = chartType2.Series.Add(ExcelRange.GetAddress(ForDiag, 5, DimensionEndRow, 5), ExcelRange.GetAddress(ForDiag, 2, DimensionEndRow, 2));
serie2.Header = "плавка(и)";
//Сохранение
try
{
FileInfo fi = new FileInfo(sfd.FileName);
excelPackage.SaveAs(fi);
}
catch
{
SystemArgs.PrintLog("Директория для сохранения отчета была утеряна");
MessageBox.Show("Невозможно найти директорию для сохранения отчета", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
SystemArgs.PrintLog("Место сохранения отчета равно null");
MessageBox.Show("Необходимо ввести названия файла", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public string GetAddress(int fromRow, int fromColumn, int toRow, int toColumn)
{
return(ExcelRange.GetAddress(fromRow, fromColumn, toRow, toColumn));
}
public Task <ExportResultResponse> GetExportExcel(PagedOrderResultRequestDto input)
{
var result = GetAll(input).Result.Data.Items;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var products = _productTypeRepository.GetAll().ToList();
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Orders");
int row = 1, col = 1;
worksheet.Cells[row, col++].Value = L("Company");
worksheet.Cells[row, col++].Value = L("Serial");
worksheet.Cells[row, col++].Value = L("DateBooked");
worksheet.Cells[row, col++].Value = L("Sgi");
worksheet.Cells[row, col++].Value = L("SalesRep");
worksheet.Cells[row, col++].Value = L("RepEmail");
worksheet.Cells[row, col++].Value = L("CustomerFirstName");
worksheet.Cells[row, col++].Value = L("CustomerLastName");
worksheet.Cells[row, col++].Value = L("ContactPhone");
worksheet.Cells[row, col++].Value = L("Email");
worksheet.Cells[row, col++].Value = L("DateOfBirth");
worksheet.Cells[row, col++].Value = L("FirstIdentification");
worksheet.Cells[row, col++].Value = L("SecondIdentification");
worksheet.Cells[row, col++].Value = L("ExistingAccountNo");
worksheet.Cells[row, col++].Value = L("StreetNo");
worksheet.Cells[row, col++].Value = L("CustomerAddress");
worksheet.Cells[row, col++].Value = L("Unit");
worksheet.Cells[row, col++].Value = L("City");
worksheet.Cells[row, col++].Value = L("PostalCode");
worksheet.Cells[row, col++].Value = L("PromoDetails");
foreach (var product in products)
{
worksheet.Cells[row, col++].Value = product.Name;
}
worksheet.Cells[row, col++].Value = L("TimeSlot");
worksheet.Cells[row, col++].Value = L("Notes");
worksheet.Cells[row, col++].Value = L("OrderNo");
worksheet.Cells[row, col++].Value = L("AccountNo");
worksheet.Cells[row, col++].Value = L("InstallDate");
worksheet.Cells[row, col++].Value = L("OrderState");
worksheet.Cells[row, col++].Value = L("Remarks");
worksheet.Cells[row, col++].Value = L("Followed");
worksheet.Cells[row, col++].Value = L("Explanation");
if (PermissionChecker.IsGranted(PermissionNames.Order_Admin_Invoice))
{
worksheet.Cells[row, col++].Value = L("PaymentStatus");
worksheet.Cells[row, col++].Value = L("InvoiceNo");
}
if (PermissionChecker.IsGranted(PermissionNames.Order_Admin_Ready))
{
worksheet.Cells[row, col++].Value = L("IsReady");
}
worksheet.Cells[ExcelRange.GetAddress(1, 1, 1, col)].Style.Font.Bold = true;
foreach (var item in result.ToList())
{
row++;
col = 1;
worksheet.Cells[row, col++].Value = item.Company.Name;
worksheet.Cells[row, col++].Value = item.Serial;
worksheet.Cells[row, col++].Value = item.DateBooked;
worksheet.Cells[row, col++].Value = item.Sgi;
worksheet.Cells[row, col++].Value = item.SalesRep.Name;
worksheet.Cells[row, col++].Value = item.SalesRep.EmailAddress;
worksheet.Cells[row, col++].Value = item.CustomerFirstName;
worksheet.Cells[row, col++].Value = item.CustomerLastName;
worksheet.Cells[row, col++].Value = item.ContactPhone;
worksheet.Cells[row, col++].Value = item.Email;
worksheet.Cells[row, col++].Value = item.DateOfBirth;
worksheet.Cells[row, col++].Value = item.FirstIdentification.Name;
worksheet.Cells[row, col++].Value = item.SecondIdentification.Name;
worksheet.Cells[row, col++].Value = item.ExistingAccountNo;
worksheet.Cells[row, col++].Value = item.StreetNo;
worksheet.Cells[row, col++].Value = item.CustomerAddress;
worksheet.Cells[row, col++].Value = item.Unit;
worksheet.Cells[row, col++].Value = item.City;
worksheet.Cells[row, col++].Value = item.PostalCode;
worksheet.Cells[row, col++].Value = item.PromoDetails;
var dic = item.OrdersProductType.ToList().ToDictionary(t => t.ProductTypeId, t => t.ProductTypeId);
foreach (var product in products)
{
worksheet.Cells[row, col++].Value = dic.ContainsKey(product.Id) ? "1": "0";
}
worksheet.Cells[row, col++].Value = item.TimeSlot.Name;
worksheet.Cells[row, col++].Value = item.Notes;
worksheet.Cells[row, col++].Value = item.OrderNo;
worksheet.Cells[row, col++].Value = item.AccountNo;
worksheet.Cells[row, col++].Value = item.InstallDate;
worksheet.Cells[row, col++].Value = item.OrderState.Name;
worksheet.Cells[row, col++].Value = item.Remarks;
worksheet.Cells[row, col++].Value = item.Followed;
worksheet.Cells[row, col++].Value = item.Explanation;
if (PermissionChecker.IsGranted(PermissionNames.Order_Admin_Invoice))
{
worksheet.Cells[row, col++].Value = item.PaymentStatus.Name;
worksheet.Cells[row, col++].Value = item.InvoiceNo;
}
if (PermissionChecker.IsGranted(PermissionNames.Order_Admin_Ready))
{
worksheet.Cells[row, col++].Value = item.IsReady;
}
}
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
var response = Task.FromResult(new ExportResultResponse {
FileName = "Orders.xlsx",
Data = package.GetAsByteArray()
});
return(response);
}
}
private static void GenJson_old(object data, FileInfo filename)
{
int x_diag = 10, y_diag = 10;
var smpoData = new List <SMPO>();
SqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
using (var exclelFile = new ExcelPackage(filename))
{
smpoData = QueryEmployee(conn);
// Добавим новые листы
var gist = exclelFile.Workbook.Worksheets.Add("Графики");
var ws = exclelFile.Workbook.Worksheets.Add("Данные");
// Создаем таблицу на странице Сводная таблица
var row = 1;
var col = 1;
var tempdate = "";
foreach (var item in smpoData)
{
if (!item.date_start.Equals(tempdate))
{
ws.Cells[row, col].Value = item.date_start;
// Создаем гистограмму
var chart = (ExcelBarChart)gist.Drawings.AddChart(item.date_start, eChartType.ColumnClustered);
chart.SetSize(480, 320);
chart.SetPosition(x_diag, y_diag);
chart.Title.Text = string.Format("Гистограмма работы станков от {0}", item.date_start);
chart.Series.Add(ExcelRange.GetAddress(3, 2, 4, 2), ExcelRange.GetAddress(3, 1, 4, 1));
// Настраиваем шапку даты
using (var range = ws.Cells[row, col, row, 7])
{
range.Merge = true;
range.Style.Font.Bold = true;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.Blue);
range.Style.Font.Color.SetColor(Color.White);
}
row++;
ws.Cells[row, col++].Value = "Номер станка";
ws.Cells[row, col++].Value = "Имя станка";
ws.Cells[row, col++].Value = "в работе";
ws.Cells[row, col++].Value = "в наладке";
ws.Cells[row, col++].Value = "в простое";
ws.Cells[row, col++].Value = "кол-во часов в месяце";
ws.Cells[row, col++].Value = "% работы станка";
row++;
col = 1;
x_diag = x_diag + 340;
}
ws.Cells[row, col++].Value = item.IdStanok;
ws.Cells[row, col++].Value = item.name_stan;
gist.Cells[string.Format("A{0}", row)].Value = item.name_stan; // Гист
gist.Cells[string.Format("B{0}", row)].Value = Convert.ToInt32(item.ВРаботе);
ws.Cells[row, col++].Value = Convert.ToInt32(item.ВРаботе);
ws.Cells[row, col++].Value = Convert.ToInt32(item.ВНаладке);
ws.Cells[row, col++].Value = Convert.ToInt32(item.ВПростое);
ws.Cells[row, col++].Value = Convert.ToInt32(item.КолвоЧасовВМесяце);
ws.Cells[row, col++].Value = Convert.ToInt32(item.ПроцРаботыСтанка);
row++;
col = 1;
tempdate = item.date_start;
}
// добавим всем ячейкам рамку
using (var cells = ws.Cells[ws.Dimension.Address])
{
cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
cells.AutoFitColumns();
}
exclelFile.Save();
// сохраняем в файл
//var bin = p.GetAsByteArray();
//File.WriteAllBytes(@"d:\result.xlsx", bin);
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
// Закрыть соединение.
conn.Close();
// Разрушить объект, освободить ресурс.
conn.Dispose();
}
}
public void addSeries(string chartName, double[,] data, string dataName, string infoChart = "", string infoData = "")
{
// Finding the chart
ExcelDrawing objChart = null;
int nCharts = charts_.Count;
for (int i = 0; i != nCharts; ++i)
{
ExcelDrawing chart = charts_[i];
if (chart.Name == chartName)
{
objChart = chart;
lastSheet_ = chartSheets_[i];
break;
}
}
if (objChart == null)
{
return;
}
// Check if the chart is currently in use
ChartPosition pos;
int iRow, jCol;
if (!posCharts_.ContainsKey(objChart))
{
pos = new ChartPosition()
{
header = new Position {
row = ChartPosition.lastRow + 1, col = 1
},
length = data.GetLength(0),
availablePosition = new Position {
row = ChartPosition.lastRow + 3, col = 1
}
};
// Write the header
workSheet_.Cells[pos.header.row, pos.header.col].Value = objChart.Name + infoChart;
posCharts_.Add(objChart, pos);
ChartPosition.lastRow += pos.length + 3;
}
else
{
pos = posCharts_[objChart];
}
// Add the function values
iRow = pos.availablePosition.row;
jCol = pos.availablePosition.col;
int nData = data.GetLength(0);
for (int k = 0; k != nData; ++k)
{
workSheet_.Cells[iRow + k, jCol].Value = data[k, 0];
workSheet_.Cells[iRow + k, jCol + 1].Value = data[k, 1];
}
workSheet_.Cells[pos.header.row + 1, jCol].Value = infoData; // Set the data info
workSheet_.Cells[pos.header.row + 1, jCol + 1].Value = dataName; // Set the name
// Retrieving the data address
ExcelScatterChart scatterChart = (ExcelScatterChart)objChart;
string xVals = ExcelRange.GetAddress(iRow, jCol,
iRow + nData - 1, jCol);
string yVals = ExcelRange.GetAddress(iRow, jCol + 1,
iRow + nData - 1, jCol + 1);
xVals = ExcelRange.GetFullAddress(workSheetName_, xVals);
yVals = ExcelRange.GetFullAddress(workSheetName_, yVals);
// Creating the serie
ExcelScatterChartSerie serie = scatterChart.Series.Add(yVals, xVals);
// Using the standard markers when custom ones are not available
List <MarkerProperty> markers = customMarkers_[chartName];
if (markers == null || markers.Count == 0)
{
markers = standardMarkers_;
}
MarkerProperty markerProperties = markers[indMarkers_[chartName]];
// Using the standard lines when custom ones are not available
List <LineProperty> lines = customLines_[chartName];
if (lines == null || lines.Count == 0)
{
lines = standardLines_;
}
LineProperty lineProperties = lines[indLines_[chartName]];
int transparency = lineProperties.isTransparent ? 100 : 0; // Perecentage
// Specifying the properties
serie.Border.Fill.Color = Color.Black; // Line color
serie.Border.LineStyle = lineProperties.lineStyle; // Line style
serie.Border.Fill.Transparancy = transparency; // Line transparency
serie.Border.Width = 1.0; // Line width
if (serie.Marker != null)
{
serie.Marker.Border.Fill.Color = Color.Black; // Marker border color
serie.Marker.Border.Width = 0.75; // Marker border width
serie.Marker.Size = 5; // Marker size
// Marker fill color
if (markerProperties.fillColor != Color.Transparent)
{
serie.Marker.Fill.Color = markerProperties.fillColor;
}
else
{
serie.Marker.Fill.Style = eFillStyle.NoFill;
}
// Marker style
if (lineProperties.isMarkersEnabled)
{
serie.Marker.Style = markerProperties.style;
}
else
{
serie.Marker.Style = eMarkerStyle.None;
}
// Increment markers and lines indices
++indMarkers_[chartName];
if (indMarkers_[chartName] >= markers.Count)
{
indMarkers_[chartName] = 0;
}
}
++indLines_[chartName];
if (indLines_[chartName] >= lines.Count)
{
indLines_[chartName] = 0;
}
// Legend
serie.Header = dataName;
// Shifting data locations
pos.availablePosition.col = pos.availablePosition.col + 2;
pos.length = Math.Max(pos.length, nData);
int lastRowColumn = pos.availablePosition.row + pos.length;
if (lastRowColumn > ChartPosition.lastRow)
{
ChartPosition.lastRow = lastRowColumn;
}
}
public async Task <ResponseResult <Response> > Handle(
Command request,
CancellationToken cancellationToken)
{
var queryResult = new MaterialTypeExport();
IEnumerable <MaterialPropertyExport> basicProperties;
IEnumerable <Lunz.ProductCenter.MService.QueryStack.Models.Trade> trades;
using (var scope = _databaseScopeFactory.CreateReadOnly())
{
queryResult = await _repository.FindCustomPropertiesByTypeAsync <MaterialTypeExport>(request.TypeId);
basicProperties = await _repository.FindBasicPropertiesAsync <MaterialPropertyExport>();
trades = await _repository.FindAllTradePropertiesAsync <QueryStack.Models.Trade>();
}
string url = string.Empty;
if (queryResult != null)
{
var createResult = await CreateFolder();
var path = createResult.Item1;
var fileName = $"物料模板-{request.TypeName}{request.UserName}-{createResult.Item2}.xlsx";
FileInfo file = new FileInfo($@"{path}\{fileName}");
if (file?.Exists ?? false)
{
file.Delete();
}
using (ExcelPackage package = new ExcelPackage(file))
{
// 物料模板sheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("物料");
// 物料属性sheet
ExcelWorksheet propertysheet = package.Workbook.Worksheets.Add("属性");
// 第一行
worksheet.Cells[1, 1].Value = $"物料类型Id:{queryResult.Id}";
worksheet.Cells[1, 2].Value = $"物料类型:{queryResult.TypeName}";
worksheet.Cells[1, 3].Value = $"物料编码:{request.TypeCode}";
worksheet.Cells[1, 4].Value = $"模板导出时间:{string.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now)}";
// row 2, col 1, 物料名称列
worksheet.Cells[2, 1].IsRichText = true;
ExcelRichText ert = worksheet.Cells[2, 1].RichText.Add("物料名称");
ert = worksheet.Cells[2, 1].RichText.Add("*");
ert.Color = Color.Red;
// 从row 3到maxRows, col 1, 物料名称列验证
var minLength = 1;
var maxLength = 40;
int maxRows = ExcelPackage.MaxRows;
var textValidation = worksheet.DataValidations.AddTextLengthValidation($"A3:A{maxRows}");
textValidation.ShowErrorMessage = true;
textValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
textValidation.ErrorTitle = "物料名称";
textValidation.Error = string.Format("物料名称不能为空，且最大长度不能超过{0}个字符以内的汉字、字母、数字、符号的组合", maxLength);
textValidation.Formula.Value = minLength;
textValidation.Formula2.Value = maxLength;
// 属性sheet
var optionRow = 2;
var optionCol = 1;
// 物料sheet, row 2, col 2, col 3, DT0000000015: 物料单位, DT0000000031: 物料规格
if (basicProperties?.Count() == 2)
{
optionCol = 1;
// 物料sheet, 从row 3到maxRows, col 2, col 3, 物料规格列下拉列表, 物料单位列下拉列表
var col = 2;
foreach (var property in basicProperties)
{
worksheet.Cells[2, col].IsRichText = true;
ert = worksheet.Cells[2, col].RichText.Add(property.DisplayName);
ert = worksheet.Cells[2, col].RichText.Add("*");
ert.Color = Color.Red;
var basicPropertiesRange = ExcelRange.GetAddress(3, col, ExcelPackage.MaxRows, col);
// 属性值
var options = property.Options;
if (options?.Any() ?? false)
{
var basicPropertiesValidation = worksheet.DataValidations.AddListValidation(basicPropertiesRange);
basicPropertiesValidation.ShowErrorMessage = true;
basicPropertiesValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
basicPropertiesValidation.ErrorTitle = "选择基本属性";
basicPropertiesValidation.Error = "请从属性列表中选择一项";
var valueRange = ExcelRange.GetAddress(2, optionCol, options.Count + 1, optionCol, true);
basicPropertiesValidation.Formula.ExcelFormula = $@"属性!{valueRange}";
// 属性sheet, 属性名
propertysheet.Cells[1, optionCol].Value = property.DisplayName;
optionRow = 2;
foreach (var option in options)
{
// 属性sheet, 所有属性选项值
propertysheet.Cells[optionRow, optionCol].Value = $"{option.OptionId}:{option.OptionName}";
optionRow++;
}
}
optionCol++;
col++;
}
}
else
{
worksheet.Cells[2, 2].IsRichText = true;
ert = worksheet.Cells[2, 2].RichText.Add("物料规格");
ert = worksheet.Cells[2, 2].RichText.Add("*");
ert.Color = Color.Red;
worksheet.Cells[2, 3].IsRichText = true;
ert = worksheet.Cells[2, 3].RichText.Add("物料单位");
ert = worksheet.Cells[2, 3].RichText.Add("*");
ert.Color = Color.Red;
}
// row 2, col 4, 是否为生产物料列, 【是否为生产物料】选择为“是”时，【是否有独立编码】默认为“否”且不可修改。
worksheet.Cells[2, 4].IsRichText = true;
ert = worksheet.Cells[2, 4].RichText.Add("是否为生产物料");
ert = worksheet.Cells[2, 4].RichText.Add("*");
ert.Color = Color.Red;
// 是否为生产物料下拉列表
var range = ExcelRange.GetAddress(3, 4, ExcelPackage.MaxRows, 4);
var listValidation = worksheet.DataValidations.AddListValidation(range);
listValidation.ShowErrorMessage = true;
listValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
listValidation.ErrorTitle = "选择基本属性";
listValidation.Error = "请从属性列表中选择一项";
listValidation.Formula.Values.Add("0:否");
listValidation.Formula.Values.Add("1:是");
// row 2, col 5, 是否有独立编号列
worksheet.Cells[2, 5].IsRichText = true;
ert = worksheet.Cells[2, 5].RichText.Add("是否有独立编号");
ert = worksheet.Cells[2, 5].RichText.Add("*");
ert.Color = Color.Red;
// 是否有独立编号下拉列表
range = ExcelRange.GetAddress(3, 5, ExcelPackage.MaxRows, 5);
listValidation = worksheet.DataValidations.AddListValidation(range);
listValidation.ShowErrorMessage = true;
listValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
listValidation.ErrorTitle = "选择基本属性";
listValidation.Error = "请从属性列表中选择一项";
listValidation.Formula.Values.Add("0:否");
listValidation.Formula.Values.Add("1:是");
// row 2, col 6, 采购主体列
StringBuilder tradeValue = new StringBuilder();
tradeValue.Append(Environment.NewLine);
tradeValue.Append("(多个采购主体用英文逗号隔开)");
if (trades?.Any() ?? false)
{
tradeValue.Append(Environment.NewLine);
foreach (var trade in trades)
{
tradeValue.Append($"{trade.Id}:{trade.TradeName},");
}
}
var value = tradeValue.ToString();
worksheet.Cells[2, 6].IsRichText = true;
ert = worksheet.Cells[2, 6].RichText.Add("采购主体");
if (!string.IsNullOrEmpty(request.TypeCode) && request.TypeCode.StartsWith(_tradCode))
{
ert = worksheet.Cells[2, 6].RichText.Add("*");
ert.Color = Color.Red;
}
ert = worksheet.Cells[2, 6].RichText.Add(value.Remove(value.LastIndexOf(',')));
ert.Color = Color.Black;
worksheet.Cells[2, 6].Style.WrapText = true;
// row 2, col 7, 建议采购价列
worksheet.Cells[2, 7].Value = "建议采购价(5位以内的正数,两位小数)";
worksheet.Cells[2, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
// 从row 3到maxRows, col 7, 建议采购价列验证
range = ExcelRange.GetAddress(3, 7, ExcelPackage.MaxRows, 7);
var decimalValidation = worksheet.DataValidations.AddDecimalValidation(range);
decimalValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
decimalValidation.ErrorTitle = "建议采购价";
decimalValidation.Error = "建议采购价是5位以内的正数";
decimalValidation.ShowErrorMessage = true;
decimalValidation.Operator = ExcelDataValidationOperator.between;
decimalValidation.Formula.Value = 0.00;
decimalValidation.Formula2.Value = 99999.99;
// 属性sheet
optionRow = 2;
// row 2, 从col 8开始, 自定义属性列
var properties = queryResult.Properties;
if (properties?.Any() ?? false)
{
optionCol = 3;
var col = 8;
foreach (var property in properties)
{
// 从row 3开始，添加自定义属性选项值下拉列表
range = ExcelRange.GetAddress(3, col, ExcelPackage.MaxRows, col);
// 属性选项值
var options = property.Options;
if (options?.Any() ?? false)
{
listValidation = worksheet.DataValidations.AddListValidation(range);
listValidation.ShowErrorMessage = true;
listValidation.ErrorTitle = "选择自定义属性";
listValidation.Error = "请从属性列表中选择一项";
var customerOptionRange = ExcelRange.GetAddress(2, optionCol, options.Count + 1, optionCol, true);
listValidation.Formula.ExcelFormula = $@"属性!{customerOptionRange}";
// 属性sheet, 属性名
propertysheet.Cells[1, optionCol].Value = $"{property.PropId}:{property.DisplayName}";
optionRow = 2;
foreach (var option in options)
{
// 属性sheet, 所有属性选项值
propertysheet.Cells[optionRow, optionCol].Value = $"{option.OptionId}:{option.OptionName}";
optionRow++;
}
}
// 自定义属性是否必填
if (property.IsNecessary)
{
worksheet.Cells[2, col].IsRichText = true;
ert = worksheet.Cells[2, col].RichText.Add($"{property.PropId}:{property.DisplayName}");
ert = worksheet.Cells[2, col].RichText.Add("*");
ert.Color = Color.Red;
if (options?.Any() ?? false)
{
listValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
}
}
else
{
worksheet.Cells[2, col].Value = $"{property.PropId}:{property.DisplayName}";
if (options?.Any() ?? false)
{
listValidation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
}
}
optionCol++;
col++;
}
}
worksheet.View.FreezePanes(3, 1);
worksheet.Cells.AutoFitColumns();
propertysheet.Cells.AutoFitColumns();
worksheet.Column(6).Width = 32;
package.Save();
}
string ossEndpoint = "http://oss-cn-hangzhou.aliyuncs.com";
string ossAccessKeyId = "LTAIFAiipRXidbYT";
string ossAccessKeySecret = "62zh9kaAd60NAqMxNPCqVcvxhNxH0H";
string ossBucketName = "basichz";
string ossRootFolder = "productcenter";
string ossAccessUrl = "//oss.lunz.cn/";
var fileManager = new OssFileManager(ossEndpoint, ossAccessKeyId, ossAccessKeySecret, ossBucketName, ossRootFolder, ossAccessUrl);
// 上传至阿里云
var uploadResult = fileManager.Upload(file.Name.Trim(), $@"{path}\{file.Name.Trim()}");
// 返回阿里云Url地址
url = $@"http://basichz.lunz.cn/{uploadResult.Key}";
// 删除文件夹
DirectoryInfo directory = new DirectoryInfo(path);
if (directory.Exists)
{
directory.Delete(true);
}
}
return(ResponseResult <Response> .Ok(new Response(url)));
}