public static void CreateChart(
ref Excel.Worksheet page,
ExcelChartInfo excelCI)
{
try
{
var xlCharts =
page.ChartObjects() as Excel.ChartObjects;
Excel.ChartObject myChart =
xlCharts.Add(excelCI.m_Left, excelCI.m_Top, excelCI.m_Width, excelCI.m_Height);
Excel.Chart chartPage = myChart.Chart;
var chartRange = page.get_Range(excelCI.m_NameLRange, excelCI.m_NameHRange);
var chartRange2 = page.get_Range(excelCI.m_SourceLRange, excelCI.m_SourceHRange);
chartPage.SetSourceData(chartRange);
chartPage.ChartType =
excelCI.m_ChartType; //xlLine || xlColumnClustered
chartPage.ChartWizard(
Source: chartRange2,
Title: excelCI.m_Title,
CategoryTitle: excelCI.m_CategoryTitle,
ValueTitle: excelCI.m_ValueTitle);
}
catch (System.Runtime.InteropServices.COMException ex)
{
MessageBox.Show(ex.Message);
}
}
public void FillThePage(out Excel.Worksheet page)
{
page =
(Excel.Worksheet)m_WorkBook.Worksheets.get_Item(1);
page = m_WorkBook.Worksheets.Add();
List <ParsedTables> parsedTables = m_Tables.ParseTablesFirstName();
page.StandardWidth = parsedTables[0].LongestName().Length * 1.29f;
page.Name = "FinalReport";
page.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
page.Cells.Font.Size = 14;
page.Cells.Font.Name = "Times New Roman";
//1
page.Cells[2, 1].Value = "Кол-во таблиц";
page.Cells[2, 2].Value = parsedTables.Count;
(var names1, var values1) = m_Tables.SplitList(m_Tables.ParseTablesFirstName);
ExcelPrototype.SetTable2(
ref page,
3, names1.Count + 3,
names1, values1);
ExcelChartInfo excelCI =
new ExcelChartInfo().Left(530).Top(20).Width(450).Heigth(200)
.NameLRange($"B{3}").NameHRange($"B{values1.Count + 2}")
.SourceLRange($"A{3}").SourceHRange($"B{values1.Count + 2}")
.Title("По дням").CategoryTitle("Дата").ValueTitle("Деньги")
.ChartType(Excel.XlChartType.xlLine);
ExcelPrototype.CreateChart(ref page, excelCI);
//2
(var names2, var values2) = m_Tables.SplitList(m_Tables.ParseTablesSecondName);
ExcelPrototype.SetTable2(
ref page,
(names1.Count + 5), (names1.Count + names2.Count + 5),
names2, values2);
ExcelChartInfo excelCI2 =
new ExcelChartInfo().Left(530).Top(240).Width(450).Heigth(350)
.NameLRange($"B{names1.Count + 5}")
.NameHRange($"B{(names1.Count + names2.Count + 4)}")
.SourceLRange($"A{names1.Count + 5}")
.SourceHRange($"B{(names1.Count + names2.Count + 4)}")
.Title("По типам трат").CategoryTitle("Дата").ValueTitle("Деньги")
.ChartType(Excel.XlChartType.xlColumnClustered);
//3
var(mostPopularValue, mostPopularName) = m_Tables.GetMostPopularProduct();
var(mostExpensiveValue, mostExpensiveName) = m_Tables.GetMostExpensiveProduct();
page.Cells[(names1.Count + names2.Count + 7), 1].Value = "Самый популярный";
page.Cells[(names1.Count + names2.Count + 7), 2].Value = mostPopularName;
page.Cells[(names1.Count + names2.Count + 7), 3].Value = mostPopularValue;
page.Cells[(names1.Count + names2.Count + 8), 1].Value = "Самый дорогой";
page.Cells[(names1.Count + names2.Count + 8), 2].Value = mostExpensiveName;
page.Cells[(names1.Count + names2.Count + 8), 3].Value = mostExpensiveValue;
ExcelPrototype.CreateChart(ref page, excelCI2);
}