public void FillExcel <T>(string name, IEnumerable <T> values, double step)
{
Excel.Application ex = new Excel.Application();
ex.Visible = true;
ex.SheetsInNewWorkbook = 1;
var workBook = ex.Workbooks.Add(Type.Missing);
var sheet = (Excel.Worksheet)ex.Worksheets.Item[1];
sheet.Name = name;
sheet.Cells[1, 1] = "x";
sheet.Cells[1, 2] = "y";
for (int i = 1; i < values.Count() + 1; i++)
{
sheet.Cells[i + 1, 1] = step * (i - 1);
sheet.Cells[i + 1, 2] = values.ElementAt(i - 1);
}
Excel.ChartObjects xlCharts = (Excel.ChartObjects)sheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(110, 0, 350, 250);
Excel.Chart chart = myChart.Chart;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.NewSeries();
series.XValues = sheet.get_Range("A2", "A" + (values.Count() + 1).ToString());
series.Values = sheet.get_Range("B2", "B" + (values.Count() + 1).ToString());
chart.ChartType = Excel.XlChartType.xlXYScatterSmooth;
}
public void AddChart(Tools.Worksheet worksheet, Excel.Range range, Template template)
{
var charts = worksheet.Application.ActiveWorkbook.Charts;
Tools.Chart chart;
if (worksheet.Controls.Contains("chart"))
{
chart = (Tools.Chart)worksheet.Controls["chart"];
Excel.SeriesCollection sc = (Excel.SeriesCollection)chart.SeriesCollection();
while (sc.Count > 0)
{
sc.Item(1).Delete();
}
}
else
{
chart = worksheet.Controls.AddChart(range, "chart");
}
//chart.ChartType = Excel.XlChartType.xlLine;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.NewSeries();
series.Values = GenerateTestData(this.random, 100);
//series.XValues = new string[] { "A", "B", "C", "D" };
series.Name = "Series Name";
chart.ApplyChartTemplate(TemplateDictionary[template]);
}
public void Excel_Create(string file_name, int latin, int kiril, int num)
{
exel.Application application = new exel.Application();
exel.Workbook workbook = application.Workbooks.Add();
exel.Worksheet worksheet = (exel.Worksheet)workbook.ActiveSheet;
worksheet.Name = file_name;
worksheet.Cells[1, 1] = "Латинские буквы";
worksheet.Cells[2, 1] = "Кириллица";
worksheet.Cells[3, 1] = "Числа";
worksheet.Cells[1, 2] = latin;
worksheet.Cells[2, 2] = kiril;
worksheet.Cells[3, 2] = num;
worksheet.Columns.AutoFit();
exel.ChartObjects chartObjects = (exel.ChartObjects)worksheet.ChartObjects(Type.Missing);
exel.ChartObject chartObject = chartObjects.Add(50, 50, 250, 250);
exel.Chart chart = chartObject.Chart;
exel.SeriesCollection seriesCollection = (exel.SeriesCollection)chart.SeriesCollection(Type.Missing);
exel.Series series = seriesCollection.NewSeries();
chart.ChartType = exel.XlChartType.xl3DPie;
series.XValues = worksheet.get_Range("A1", "A3");
series.Values = worksheet.get_Range("B1", "B3");
workbook.SaveAs();
workbook.Close();
application.Quit();
}
private void ButtonApply_Click(object sender, RoutedEventArgs e)
{
Excel.Worksheet workSheet2 = (Excel.Worksheet)_workBook.Sheets[2];
Excel.Worksheet workSheet3 = (Excel.Worksheet)_workBook.Sheets[3];
int rowIndex = 0;
for (int x = 0; x <= 10; x++)
{
workSheet2.Cells[3, "F"].Value = x;
double y = workSheet2.Cells[6, "F"].Value;
workSheet3.Cells[rowIndex + 1, "H"].Value = x;
workSheet3.Cells[rowIndex + 1, "I"].Value = y;
rowIndex++;
}
Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet3.ChartObjects(Type.Missing);
Excel.ChartObject myChart = chartObjs.Add(20, 60, 200, 200);
Excel.Chart chart = myChart.Chart;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.NewSeries();
series.XValues = workSheet3.get_Range("H1", "H10");
series.Values = workSheet3.get_Range("I1", "I10");
chart.ChartType = Excel.XlChartType.xlXYScatterSmooth;
}
/// <summary>
/// Auto Generate Chart according to stored RingBuffer-Values
/// </summary>
/// <param name="sheetname">Desired Workscheet</param>
/// <param name="rowStart">Starting Row Index</param>
/// <param name="rowEnd">Ending Row Index</param>
private static void GenerateChart(string sheetname, int rowStart, int rowEnd)
{
// Add chart.
var worksheet = _workbook.Sheets[sheetname] as
Microsoft.Office.Interop.Excel.Worksheet;
var charts = worksheet.ChartObjects() as
Microsoft.Office.Interop.Excel.ChartObjects;
var chartObject = charts.Add(120, 10, 600, 300) as
Microsoft.Office.Interop.Excel.ChartObject;
var chart = chartObject.Chart;
// Set chart range.
var range = worksheet.get_Range(CellAddress(worksheet, rowStart, 1), CellAddress(worksheet, rowEnd, 2));
chart.SetSourceData(range);
// Set chart properties.
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLinesNoMarkers;
chart.ChartWizard(Source: range,
Title: sheetname);
AddAxisLabels(chart);
// Add to main Chart
var line = _mainChartSeriesCollection.NewSeries();
line.XValues = worksheet.get_Range(CellAddress(worksheet, rowStart, 1), CellAddress(worksheet, rowEnd, 1));
line.Values = worksheet.get_Range(CellAddress(worksheet, rowStart, 2), CellAddress(worksheet, rowEnd, 2));
line.Name = sheetname;
AddAxisLabels(_mainChart);
}
/// <summary>
/// Creates a new graph in a new excel workbook. Values is a list of data series, where a series is a list of X,Y value pairs.
/// </summary>
/// <param name="values"></param>
/// <param name="seriesTitle"></param>
/// <param name="xAxis"></param>
/// <param name="yAxis"></param>
/// <param name="headers"></param>
public static void PlotEachSeriesSeperately(List <List <double[]> > values, string seriesTitle = "",
string xAxis = "", string yAxis = "", List <string> headers = null)
{
//Create a new excel workbook and sheet
Excel.Application xlApp;
Excel.Workbook xlWorkbook;
Excel.Worksheet xlWorksheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Add(misValue);
xlWorksheet = xlWorkbook.Worksheets.get_Item(1);
//Export data to Excel Sheet from List<List<double>>
//Set headers if given.
if (headers != null)
{
for (var i = 0; i < headers.Count; i++)
{
xlWorksheet.Cells[1, i].Value = headers[i];
}
}
//Create Chart
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorksheet.ChartObjects(Type.Missing);
for (var i = 0; i < values.Count; i++) //For each series
{
var xValues = new double[values[i].Count];
var yValues = new double[values[i].Count];
for (var j = 0; j < values[i].Count; j++) //For each point in series
{
xValues[j] = values[i][j][0];
yValues[j] = values[i][j][1];
}
//Create a new chart and offset from previous
var offset = 300 * (i);
Excel.ChartObject myChart = xlCharts.Add(200, 30 + offset, 400, 300);
Excel.Chart chart = myChart.Chart;
chart.ChartType = Excel.XlChartType.xlXYScatterLines;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Series series = seriesCollection.NewSeries();
series.Values = yValues;
series.XValues = xValues;
var title = seriesTitle + " " + i;
chart.ChartWizard(
Title: title,
CategoryTitle: xAxis,
ValueTitle: yAxis);
}
xlApp.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
}
private void CreateChart(int length, Worksheet excelWorkSheet)
{
ChartObjects xlCharts = (ChartObjects)excelWorkSheet.ChartObjects(Type.Missing);
ChartObject myChart = (ChartObject)xlCharts.Add(300, 0, length / 1.5, 350);
Microsoft.Office.Interop.Excel.Chart chart = myChart.Chart;
Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Microsoft.Office.Interop.Excel.Series series = seriesCollection.NewSeries();
series.XValues = excelWorkSheet.get_Range("B2", "B" + (length + 1));
series.Values = excelWorkSheet.get_Range("C2", "C" + (length + 1));
series.Name = "Fluctuation";
chart.ChartType = XlChartType.xlXYScatterSmooth;
}
private void DrawFractionChart(Excel.Worksheet activeSheet, Excel.ChartObjects xlCharts, Excel.Range xRange, Excel.Range yRange)
{
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(200, 500, 200, 100);
Excel.Chart chartPage = myChart.Chart;
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
series1.XValues = activeSheet.Range["E1", "E3"];
series1.Values = activeSheet.Range["F1", "F3"];
chartPage.ChartType = Excel.XlChartType.xlDoughnut;
Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis;
series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent, true, true, false, true, true, true, true);
}
/// <summary>
/// 开始绘制开挖剖面图的Chart对象
/// </summary>
/// <returns>进行绘图的Chart对象的高度值，以磅为单位，可以用来确定Excel Application的高度值</returns>
/// <remarks>绘图时，标高值与Excel中的Y坐标值的对应关系：
/// 在程序中，定义了eleTop变量（以米为单位）与F_sngTopRef变量（以磅为单位），
/// 它们指示的是此基坑区域中，地下室顶板的标高值与其在Excel绘图中对应的Y坐标值</remarks>
private Microsoft.Office.Interop.Excel.Chart DrawChart(Microsoft.Office.Interop.Excel.Worksheet DrawingSheet,
List <clsData_ProcessRegionData> SelectedRegion)
{
Microsoft.Office.Interop.Excel.Application DrawingApp = DrawingSheet.Application;
DrawingApp.ScreenUpdating = false;
DrawingApp.Caption = "开挖标高图";
//---------------- 创建一个新的，进行绘图的Chart对象 -------------------------------
Excel.Chart DrawingChart = default(Excel.Chart);
DrawingChart = DrawingSheet.Shapes.AddChart(Top: 0, Left: 0).Chart;
string TemplatePath = System.IO.Path.Combine(System.Convert.ToString(My.Settings.Default.Path_Template),
Constants.FolderOrFileName.File_Template.Chart_Elevation);
DrawingChart.Parent.Activate();
DrawingChart.ApplyChartTemplate(TemplatePath);
this.F_Textbox_Info = DrawingChart.Shapes.Item(1).TextFrame2;
DrawingChart.ChartTitle.Text = "开挖标高图";
//
Microsoft.Office.Interop.Excel.SeriesCollection src = DrawingChart.SeriesCollection();
for (short i = 0; i <= 1 - src.Count; i++) //确保Chart中至少有两个数据系列
{
src.NewSeries();
}
// ----------------------- 设置绘图及Excel窗口的尺寸 ----------------------------
double ChartHeight = 400;
double InsideLeft = 60;
double InsideRight = 20;
double LeastWidth_Chart = 500;
double LeastWidth_Column = 100;
//
double ChartWidth = LeastWidth_Chart;
double insideWidth = LeastWidth_Chart - InsideLeft - InsideRight;
//
ChartWidth = GetChartWidth(DrawingChart, SelectedRegion.Count,
LeastWidth_Chart, LeastWidth_Column,
InsideLeft + InsideRight, ref insideWidth);
ChartSize Size_Chart_App = new ChartSize(ChartHeight,
ChartWidth,
26,
9);
ExcelFunction.SetLocation_Size(Size_Chart_App, DrawingChart, DrawingChart.Application, true);
//With DrawingChart.PlotArea
// .InsideLeft = InsideLeft
// .InsideWidth = insideWidth
//End With
// --------------------------------------------------
return(DrawingChart);
}
/// <summary>
/// Обновление Диаграммы
/// </summary>
internal void UpdateDiagramm()
{
Excel.ChartObject shp = SheetUrv11.ChartObjects("Chart 2");
Excel.Chart chartPage = shp.Chart;
Excel.SeriesCollection seriesCol = (Excel.SeriesCollection)chartPage.SeriesCollection();
Excel.FullSeriesCollection fullColl = chartPage.FullSeriesCollection();
Debug.WriteLine(fullColl.Count);
int lastCol = GetLastColumnUrv(SheetUrv11, _rowStart);
int lastRow = GetLastRowUrv11();
int ix = 1;
string letterCost = "G";
fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10";
fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}";
fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}";
for (int col = 9; col <= lastCol; col += 3)
{
Excel.Range cellFirstCost = SheetUrv11.Cells[_rowStart, col];
string text = cellFirstCost.Value?.ToString() ?? "";
if (string.IsNullOrEmpty(text))
{
continue;
}
letterCost = ExcelHelper.GetColumnLetter(cellFirstCost);
ix++;
if (ix > fullColl.Count)
{
seriesCol.NewSeries();
}
fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10";
fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}";
fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}";
}
if (ix < fullColl.Count)
{
for (int i = ix + 1; i <= fullColl.Count; i++)
{
fullColl.Item(i).Delete();
}
}
}
/// <summary>
/// Рисует график
public void drawGraph(string filename, int numSheets, string firstCell, string lastCell /*, double[] approxY*/)
{
ObjWorkBooks = ObjWorkExcel.Workbooks;
ObjSheets = ObjWorkBook.Worksheets;
Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)ObjWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject chartsobjrct = chartsobjrcts.Add(1, 360, 400, 250);
Excel.Chart chart = chartsobjrct.Chart;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.NewSeries();
Excel.Trendlines trendlines = (Excel.Trendlines)series.Trendlines(System.Type.Missing);
Excel.Trendline newTrendline = trendlines.Add(Excel.XlTrendlineType.xlPower, 2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, true, Type.Missing);
newTrendline.Select();
chart.Legend.Delete();
series.XValues = ObjWorkSheet.get_Range("E7", "E26");
series.Values = ObjWorkSheet.get_Range("D7", "D26");
chart.ChartType = Excel.XlChartType.xlXYScatter;
}
private void GerarGraficoExcel(String c1, String c2, int tamanho, double left, double top, double width, double height)
{
// Cria um Chart(Gráfico)
Ex.ChartObjects cb = (Ex.ChartObjects)oSheetChart.ChartObjects(Type.Missing);
Ex.ChartObject cbc = (Ex.ChartObject)cb.Add(left, top, width, height);
Ex.Chart cp = cbc.Chart;
Ex.Range valores = oSheet.get_Range(c1, c2);
// Seta o título do gráfico
cp.HasTitle = true;
cp.ChartTitle.Text = "MÉDIA DE TEMPO EM VETORES DE TAMANHO " + tamanho + " EM " + (tamanho <= 1000 ? "NANO" : "MILLI");
cp.ChartTitle.Font.Name = "Arial";
// Seta os nomes das colunas
Ex.SeriesCollection seriesCollection = cp.SeriesCollection();
Ex.Series series = seriesCollection.NewSeries();
series.Values = valores;
series.XValues = oSheet.get_Range("B1", "J1");
// Oculta a legenda da serie
cp.Legend.Clear();
}
private void GenerateReportTopGenres(Dictionary <string, double> dictionary)
{
excel.Application excelApp = new excel.Application();
excel.Workbook workbook;
excel.Worksheet workSheet;
workbook = excelApp.Workbooks.Add();
//workSheet = (excel.Worksheet)workbook.Worksheets.get_Item(1);
workSheet = workbook.ActiveSheet;
int i = 1;
foreach (var word in dictionary.OrderByDescending(q => q.Value))
{
workSheet.Cells[1, i] = word.Key;
workSheet.Cells[2, i] = word.Value;
i++;
}
excel.Range Erange = workSheet.Range["B3"];
Erange.Formula = "=SUM(A2:J2)";
Erange.FormulaHidden = false;
excel.Borders border = Erange.Borders;
border.LineStyle = excel.XlLineStyle.xlContinuous;
excel.ChartObjects chObs = (excel.ChartObjects)workSheet.ChartObjects();
excel.ChartObject chOb = chObs.Add(5, 50, 300, 300);
excel.Chart xlchart = chOb.Chart;
excel.Range Erange2 = workSheet.Range["A1:J1"];
excel.Range Erange3 = workSheet.Range["A3:J1"];
xlchart.ChartType = excel.XlChartType.xlColumnClustered;
excel.SeriesCollection seriesCollection = (excel.SeriesCollection)xlchart.SeriesCollection(Type.Missing);
excel.Series series = seriesCollection.NewSeries();
//series.XValues = workSheet.Range["A1:J1"];
string[] matrix = new string[10];
for (int j = 0; j < 10; j++)
{
matrix[j] = (string)(workSheet.Cells[1, j + 1] as excel.Range).Value;
}
//series.XValues = workSheet.Range[workSheet.Cells[1, 3]];
series.XValues = matrix;
series.Values = workSheet.get_Range("A2", "J2");
xlchart.HasTitle = true;
xlchart.ChartTitle.Text = "Жанры и их рейтинги";
xlchart.HasLegend = true;
series.Name = "Жанры";
excelApp.Visible = true;
excelApp.UserControl = true;
string outputPath = @"C:\Users\acer\Desktop\Учеба\АИС\Отчет по рейтингу жанров (" + Guid.NewGuid().ToString() + ").xlsx";
workbook.SaveAs(outputPath);
object misValue = System.Reflection.Missing.Value;
xlchart.Export("C:\\Users\\acer\\Desktop\\Учеба\\АИС\\Graf.bmp", "BMP", misValue);
string path = @"C:\Users\acer\Desktop\Учеба\АИС\Отчет по рейтингу жанров.doc";
WordReportTopBookOfGenre wordReport = new WordReportTopBookOfGenre(path);
wordReport.GenerateReportTopGenres(dictionary);
excelApp.Quit();
}
/// <summary>
/// 给工作表添加一个图表
/// </summary>
/// <param name="firstRange">数据工作表</param>
/// <param name="xData">x轴数据区域：格式A1:A14</param>
/// <param name="ydata">y轴数据区域：格式B1:E14</param>
/// <param name="chartName">图标名称</param>
/// <param name="xname">y轴名称</param>
/// <param name="yname">y轴名称</param>
/// <param name="chartType">图表类型</param>
public static void createInnerChart(Excel.Worksheet dataSheet, List <ChartStuct> charts, string chartName, string categoryName)
{
Excel.ChartObjects ChartObjects = (Excel.ChartObjects)dataSheet.ChartObjects(Missing.Value);
int cols = 0;
foreach (ChartStuct chartStuct in charts)
{
cols += chartStuct.dataRange.Columns.Count;
}
Excel.ChartObject chartObject = ChartObjects.Add(cols * 60, 30, 800, 350);
if (charts.Count < 1)
{
return; //无数不处理
}
//绘制第一维图表
Excel.Range firstRange = charts[0].dataRange;
Excel.XlChartType chartType = charts[0].chartType;
string yname = charts[0].yname;
chartObject.Chart.ChartWizard(firstRange, chartType, Missing.Value, Excel.XlRowCol.xlColumns, 1, 1, true, chartName, categoryName, yname, Missing.Value);
chartObject.Chart.ChartArea.ClearFormats();
chartObject.Chart.ChartType = chartType;
chartObject.Chart.ChartArea.Fill.BackColor.SchemeColor = 2;//整个图表区背景
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor);
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB);
chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor = 2;
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB);
//Console.WriteLine(chartObject.Chart.PlotArea.Interior.ColorIndex);
//chartObject.Chart.PlotArea.Interior.PatternColorIndex = 19;
chartObject.Chart.PlotArea.Interior.ColorIndex = 2;//图例区域背景
Excel.Axis firstAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
firstAxis.HasMajorGridlines = true;//不显示横向网格线
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartObject.Chart.SeriesCollection(Type.Missing);
int sc = seriesCollection.Count;
if (seriesCollection.Count > 0)
{
Excel.Series seires1 = (Excel.Series)seriesCollection.Item(1);//具体数据图表区颜色
//seires1.Fill.BackColor.SchemeColor = 18;
seires1.Fill.ForeColor.SchemeColor = 46;
seires1.MarkerForegroundColorIndex = Excel.XlColorIndex.xlColorIndexNone;
seires1.HasLeaderLines = false;
}
//绘制第二维图表
//修改第一维图表的各个序列的图例,这里有个bug，会用最后的标题覆盖前面的
// for (int i = 0; i < charts[0].seriesTitles.Length; i++)
//{
//string stitle = charts[0].seriesTitles[i];
//Excel.Series tmpSeires = (Excel.Series)seriesCollection.Item(1);
//tmpSeires.Name = stitle;
//}
//绘制第二个图表,多维这里设置 当有图表类型为XlChartType.xl3DColumn或其他3d效果时，抛 xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;异常
for (int i = 1; i < charts.Count; i++)
{
ChartStuct cs = charts[i];
string stitle = cs.seriesTitles[0];
Excel.Series xlSeries = (Excel.Series)seriesCollection.NewSeries();
xlSeries.Name = stitle;
xlSeries.HasLeaderLines = true;
xlSeries.Fill.ForeColor.SchemeColor = 46;
try
{
xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;
}
catch (Exception eee) {
Console.WriteLine(eee.Message);
}
xlSeries.ChartType = cs.chartType;
xlSeries.Values = cs.dataRange;
Excel.Axis valueAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary);
valueAxis.HasTitle = true;
valueAxis.AxisTitle.Text = cs.yname;
}
}
/// <summary>
/// Creates a new graph in a new excel workbook. Values is a list of data series, where a series is a list of X,Y value pairs.
/// </summary>
/// <param name="dataSet2"></param>
/// <param name="seriesTitle"></param>
/// <param name="xAxis"></param>
/// <param name="yAxis"></param>
/// <param name="headers"></param>
/// <param name="dataSet1"></param>
public static void PlotDataSets(List <List <double[]> > dataSet1, List <List <double[]> > dataSet2, string seriesTitle = "",
string xAxis = "", string yAxis = "", List <string> headers = null)
{
//Create a new excel workbook and sheet
Excel.Application xlApp;
Excel.Workbook xlWorkbook;
Excel.Worksheet xlWorksheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Add(misValue);
xlWorksheet = xlWorkbook.Worksheets.get_Item(1);
//Create Chart
var xlCharts = (Excel.ChartObjects)xlWorksheet.ChartObjects(Type.Missing);
for (var i = 0; i < dataSet1.Count; i++) //For each series in dataSet1
{
//Create a new chart and offset from previous
var offset = 300 * (i);
Excel.ChartObject myChart = xlCharts.Add(200, 30 + offset, 400, 300);
Excel.Chart chart = myChart.Chart;
chart.ChartType = Excel.XlChartType.xlXYScatterLines;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection();
//Add first series
var xValues = new double[dataSet1[i].Count];
var yValues = new double[dataSet1[i].Count];
for (var j = 0; j < dataSet1[i].Count; j++) //For each point in series
{
xValues[j] = dataSet1[i][j][0];
yValues[j] = dataSet1[i][j][1];
}
Excel.Series series1 = seriesCollection.NewSeries();
series1.Values = yValues;
series1.XValues = xValues;
//Add second series
xValues = new double[dataSet2[i].Count];
yValues = new double[dataSet2[i].Count];
for (var j = 0; j < dataSet2[i].Count; j++) //For each point in series
{
xValues[j] = dataSet2[i][j][0];
yValues[j] = dataSet2[i][j][1];
}
Excel.Series series2 = seriesCollection.NewSeries();
series2.Values = yValues;
series2.XValues = xValues;
var title = seriesTitle + " " + i;
chart.ChartWizard(
Title: title,
CategoryTitle: xAxis,
ValueTitle: yAxis);
}
xlApp.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
}
private void button_OK_Click(object sender, EventArgs e)
{
height = int.Parse(textBox_height.Text);
int width = (int)Math.Ceiling(Convert.ToDouble(rows) / Convert.ToDouble(height));
int[,] data = new int[height, width];
int[] temp = new int[width];
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
int N_Ser = series.Count;
Excel.Series Sseries;
if (N_Ser > height)
{
for (int i = N_Ser; i > height; i--)
{
series.Item(i).Delete();
}
}
else
{
for (int i = N_Ser + 1; i <= height; i++)
{
Sseries = series.NewSeries();
}
}
Excel.Point point;
int RGB_B, RGB_G, RGB_R;
for (int i = 0; i < height; i++)
{
temp = new int[width];
for (int j = 0; j < width; j++)
{
temp[j] = 1;
data[i, j] = 1;
}
//Sseries = series.NewSeries();
series.Item(i + 1).Values = temp;
}
for (int i = 1; i <= height; i++)
{
Sseries = series.Item(i);
for (int j = 1; j <= width; j++)
{
{
point = (Excel.Point)Sseries.Points(j);
point.Format.Fill.Solid();
point.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
point.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb();
int index = (i - 1) * width + j;
if (index < rows)
{
RGB_B = Convert.ToInt32(double.Parse(str[index, cols - 1])); if (RGB_B < 0)
{
RGB_B = 0;
}
if (RGB_B > 255)
{
RGB_B = 255;
}
RGB_G = Convert.ToInt32(double.Parse(str[index, cols - 2])); if (RGB_G < 0)
{
RGB_G = 0;
}
if (RGB_G > 255)
{
RGB_G = 255;
}
RGB_R = Convert.ToInt32(double.Parse(str[index, cols - 3])); if (RGB_R < 0)
{
RGB_R = 0;
}
if (RGB_R > 255)
{
RGB_R = 255;
}
point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB_B, RGB_G, RGB_R).ToArgb();
point.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb();
}
else
{
point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb();
point.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb();
}
}
}
}
}
// Generates an excel spreadsheet with graphs of each summary element by depth and saves each graph as a PNG file.
public static void CreateGTGraphs(string domainName, string timeStamp, int depth, List <List <Tuple <String, String> > > summaries)
{
// The top level log directory.
string directory = Parser.GetTopDirectory() + @"GameTrees\" + domainName + @"\" + timeStamp + @"\";
// The graph image directory.
string imageDir = directory + @"graphs\";
// Check if the image directory exists.
if (!File.Exists(imageDir))
{
// If not, create it.
Directory.CreateDirectory(imageDir);
}
// This is needed for a lot of the Excel initialization tasks.
object misValue = System.Reflection.Missing.Value;
// Create an Excel application object.
Excel.Application xl = new Excel.Application();
// Create an Excel work sheet variable.
Excel.Worksheet xlWorkSheet;
// Create an Excel work books variable and point it at the application object's work books..
Excel.Workbooks xlWorkBooks = xl.Workbooks;
// Make the Excel application visible? Not sure, it's magic.
xl.Visible = true;
// Open the summary CSV file in the Excel work book.
xlWorkBooks.OpenText(directory + "summary.csv", misValue, misValue, Excel.XlTextParsingType.xlDelimited,
Excel.XlTextQualifier.xlTextQualifierNone, misValue, misValue,
misValue, misValue, misValue, misValue, misValue, misValue, misValue,
misValue, misValue, misValue, misValue);
// Grab the work sheet that represents the CSV file.
xlWorkSheet = (Excel.Worksheet)xlWorkBooks[1].Worksheets.get_Item(1);
// Loop through every summary element, excluding the depth count which should be first.
for (int summary = 1; summary < summaries[0].Count; summary++)
{
// Create a new Excel chart holder in the work sheet.
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
// Create a new Excel chart and position it below the table and other charts.
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, (15 * summaries[0].Count) + (220 * (summary - 1)), 360, 210);
// Select the chart object's chart. Don't ask me, this Excel interface is strange.
Excel.Chart chartPage = myChart.Chart;
// Make the chart object active.
myChart.Select();
// Set the chart's style. 227 should be a white background with a blue line.
chartPage.ChartStyle = 227;
// Set the type of chart. We are using a line chart.
chartPage.ChartType = Excel.XlChartType.xlLine;
// Turn the legend off.
chartPage.HasLegend = false;
// Create a new series collection to hold the series that will contain the chart data. Again, Excel's interface is dumb.
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection();
// Create a new series.
Excel.Series series1 = seriesCollection.NewSeries();
// Select the chart's X values. These should be the depth counts.
series1.XValues = xlWorkSheet.Range["summary!$A$2:$A$" + depth];
// Select the chart's Y values. These should be the current summary element data.
series1.Values = xlWorkSheet.Range["summary!$" + ToLetter(summary) + "$2:$" + ToLetter(summary) + "$" + depth];
// Name the chart according to the current summary element.
series1.Name = summaries[0][summary].First;
// Export the current chart as a PNG image.
chartPage.Export(imageDir + series1.Name + ".png", "PNG", false);
}
// Save the current work book as an XLS file.
xlWorkBooks[1].SaveAs(directory + "graphsummary-" + timeStamp + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
// Close the current work book.
xlWorkBooks[1].Close(true, misValue, misValue);
// Quit the Excel application.
xl.Quit();
// Do some garbage collection? Not sure, all examples I saw had this.
releaseObject(xlWorkSheet);
releaseObject(xlWorkBooks);
releaseObject(xl);
}
public static void AnalyseVisits(string fileName)
{
using (ModelMedDBContainer db = new ModelMedDBContainer())
{
object misValue = System.Reflection.Missing.Value;
// Создаём экземпляр нашего приложения
Excel.Application excelApp = new Excel.Application();
// Создаём экземпляр рабочий книги Excel
Excel.Workbook workBook;
// Создаём экземпляр листа Excel
Excel.Worksheet workSheet;
workBook = excelApp.Workbooks.Add(misValue);
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
var specials = (from docs in db.PersonSet where (docs is Doctor)select(docs as Doctor).Job).Distinct().ToArray();
//var workTime = (from works in db.WorkTimeSet group works by works.Start.Date);
var workT = (from works in db.WorkTimeSet select new { works.Start, works.Doctor.Job }).ToList();
//List<DateTime> workDays = new List<DateTime>();
//foreach (DateTime t in workT)
// workDays.Add(t.Date);
DateTime start = (from works in db.WorkTimeSet select works.Start).Min();
DateTime finish = (from works in db.WorkTimeSet select works.Start).Max();
int[,] days = new int[specials.Length, (finish.Date - start.Date).Days + 1];
//Подсчёт прёмов по профессиям и по дням
for (int i = 0; i < specials.Length; i++)
{
foreach (var t in workT)
{
if (specials[i] == t.Job)
{
days[i, (t.Start.Date - start.Date).Days]++;
}
}
}
//Заполнение строчек и столбцов посчитанными значениями
for (int i = 1; i <= specials.Length; i++)
{
workSheet.Cells[i, 1] = specials[i - 1];
for (int j = 2; j <= days.GetLength(1) + 1; j++)
{
workSheet.Cells[i, j] = days[i - 1, j - 2];
}
}
workSheet.Cells[specials.Length + 1, 1] = start;
for (int i = 2; i <= days.GetLength(1) + 1; i++)
{
workSheet.Cells[specials.Length + 1, i] = start;
start = start.AddDays(1);
}
/*
* //Вычисляем сумму этих чисел
* Excel.Range rng = workSheet.Range["A2"];
* rng.Formula = "=SUM(A1:L1)";
* rng.FormulaHidden = false;
*
* // Выделяем границы у этой ячейки
* Excel.Borders border = rng.Borders;
* border.LineStyle = Excel.XlLineStyle.xlContinuous;
*/
Excel.ChartObjects xlCharts = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
// Excel.ChartObject chartObj = myChart.Add(5, 50, 300, 300);
Excel.Chart chartPage = myChart.Chart;
//chartPage.ChartType = Excel.XlChartType.xlXYScatterLines;
//chartPage.ChartType = Excel.XlChartType.xlBarClustered;
//chartPage.ChartType = Excel.XlChartType.xlLine;
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
//char a = char.ConvertFromUtf32((char.ConvertToUtf32('A',0) + days.GetLength(1)));
Excel.Range rngX = workSheet.Range[workSheet.Cells[specials.Length + 1, 2], workSheet.Cells[specials.Length + 1, days.GetLength(1) + 1]];
// workSheet.Cells[specials.Length+1, 1], workSheet.Cells[specials.Length+1, days.GetLength(1)]];
for (int i = 1; i <= specials.Length; i++)
{
Excel.Series series = seriesCollection.NewSeries();
Excel.Range rng = workSheet.Range[workSheet.Cells[i, 2], workSheet.Cells[i, days.GetLength(1) + 1]];
series.XValues = rngX;
series.Values = rng;
series.Name = specials[i - 1];
}
workSheet.Columns.AutoFit();
workBook.SaveAs(fileName, Excel.XlFileFormat.xlExcel12, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workBook.Close(true, misValue, misValue);
excelApp.Quit();
/*
* xlChart.Activate();
* xlChart.Select(Type.Missing);
*
*
* //Даем названия осей
* ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlCategory,
* Excel.XlAxisGroup.xlPrimary)).HasTitle = true;
* ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlCategory,
* Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Дата";
* ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlSeriesAxis,
* Excel.XlAxisGroup.xlPrimary)).HasTitle = false;
* ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlValue,
* Excel.XlAxisGroup.xlPrimary)).HasTitle = true;
* ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlValue,
* Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Приёмов";
*
* excelApp.ActiveChart.HasTitle = true;
* excelApp.ActiveChart.ChartTitle.Text = "Количество приёмов на каждую специальность по дням";
*
* //Будем отображать легенду
* excelApp.ActiveChart.HasLegend = true;
* //Расположение легенды
* excelApp.ActiveChart.Legend.Position
* = Excel.XlLegendPosition.xlLegendPositionLeft;
*/
// Открываем созданный excel-файл
//excelApp.Visible = true;
//excelApp.UserControl = true;
}
}
public void Document_Create(Document_Type type, Document_Format format, string name, DataTable table)
{
Configuration_class configuration_Class = new Configuration_class();
configuration_Class.Document_Configuration_Get();
switch (name != "" || name != null)
{
case true:
switch (format)
{
case Document_Format.Word:
word.Application application = new word.Application();
word.Document document = application.Documents.Add(Visible: true);
try
{
word.Range range = document.Range(0, 0);
document.Sections.PageSetup.LeftMargin = application.CentimetersToPoints((float)Configuration_class.doc_Left_Merge);
document.Sections.PageSetup.RightMargin = application.CentimetersToPoints((float)Configuration_class.doc_Right_Merge);
document.Sections.PageSetup.TopMargin = application.CentimetersToPoints((float)Configuration_class.doc_Top_Merge);
document.Sections.PageSetup.BottomMargin = application.CentimetersToPoints((float)Configuration_class.doc_Bottom_Merge);
range.Text = Configuration_class.Organiztion_Name;
range.ParagraphFormat.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter;
range.ParagraphFormat.SpaceAfter = 1;
range.ParagraphFormat.SpaceBefore = 1;
range.ParagraphFormat.LineSpacingRule = word.WdLineSpacing.wdLineSpaceSingle;
range.Font.Name = "Times New Roman";
range.Font.Size = 12;
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph Document_Name = document.Paragraphs.Add();
Document_Name.Format.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter;
Document_Name.Range.Font.Name = "Times New Roman";
Document_Name.Range.Font.Size = 16;
switch (type)
{
case Document_Type.Report:
Document_Name.Range.Text = "ОТЧЁТ";
break;
case Document_Type Statistic:
Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ";
break;
}
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph statparg = document.Paragraphs.Add();
word.Table stat_table = document.Tables.Add(statparg.Range, table.Rows.Count, table.Columns.Count);
stat_table.Borders.InsideLineStyle = word.WdLineStyle.wdLineStyleSingle;
stat_table.Borders.OutsideLineStyle = word.WdLineStyle.wdLineStyleSingle;
stat_table.Rows.Alignment = word.WdRowAlignment.wdAlignRowCenter;
stat_table.Range.Cells.VerticalAlignment = word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
stat_table.Range.Font.Size = 12;
stat_table.Range.Font.Name = "Times New Roman";
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 1; col <= table.Columns.Count; col++)
{
stat_table.Cell(row, col).Range.Text = table.Rows[row - 1][col - 1].ToString();
}
}
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph Footparg = document.Paragraphs.Add();
Footparg.Range.Text = string.Format("Дата создания \t\t\t{0}", DateTime.Now.ToString("dd.MM.yyyy"));
}
catch
{
}
finally
{
switch (format)
{
case Document_Format.Word:
document.SaveAs2(name, word.WdSaveFormat.wdFormatDocument);
break;
case Document_Format.PDF:
document.SaveAs2(name, word.WdSaveFormat.wdFormatPDF);
break;
}
document.Close();
application.Quit();
}
break;
case Document_Format.Excel:
excel.Application application_ex = new excel.Application();
excel.Workbook workbook = application_ex.Workbooks.Add();
excel.Worksheet worksheet = (excel.Worksheet)workbook.ActiveSheet();
try
{
switch (type)
{
case Document_Type.Report:
worksheet.Name = "Отчет";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString();
}
}
excel.Range border = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[worksheet.Rows.Count + 1][table.Columns.Count + 1]];
border.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString());
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge();
break;
case Document_Type.Statistic:
worksheet.Name = "Статистический отчет";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString();
}
}
excel.Range border1 = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[worksheet.Rows.Count + 1][table.Columns.Count + 1]];
border1.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
border1.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
border1.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString());
excel.ChartObjects chartObjects = (excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
excel.ChartObject chartObject = chartObjects.Add(300, 50, 250, 250);
excel.Chart chart = chartObject.Chart;
excel.SeriesCollection seriesCollection = (excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
excel.Series series = seriesCollection.NewSeries();
chart.ChartType = excel.XlChartType.xl3DColumn;
series.XValues = worksheet.get_Range("B2", "B" + table.Rows.Count + 1);
series.Values = worksheet.get_Range("C2", "C" + table.Rows.Count + 1);
break;
}
}
catch
{
}
finally
{
workbook.SaveAs(name, application_ex.DefaultSaveFormat);
workbook.Close();
application_ex.Quit();
}
break;
}
break;
case false:
System.Windows.Forms.MessageBox.Show("Введите название документа");
break;
}
}
//Revenues Comparison
public string RevenuesComparison(int ByDDMMYYYY, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "")
{
try
{
string ReportName = "Revenues Comparison Report";
DataTable aTable = ReportsMgmt.RevenuesComparions(ByDDMMYYYY, DateFrom, DateTo);
if (aTable.Rows.Count > 0)
{
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlApp.Visible = false;
xlApp.SheetsInNewWorkbook = 1;
xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue
int RowCnt = 1;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = ReportName;
List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1);
List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1);
for (int i = 0; i < aHeader.Count; i++)
{
xlWorkSheet.Cells[RowCnt, 2] = aHeader[i];
RowCnt++;
}
xlWorkSheet.Cells[RowCnt++, 1] = ReportName;
xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom;
xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo;
RowCnt++;
xlWorkSheet.Cells[RowCnt, 1] = "Date";
xlWorkSheet.Cells[RowCnt, 2] = "Total Cost JOD";
xlWorkSheet.Cells[RowCnt, 3] = "Total Sales JOD";
xlWorkSheet.Cells[RowCnt, 4] = "Gross Profit JOD";
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true;
//xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
RowCnt++;
int DataStart = RowCnt;
foreach (DataRow aRow in aTable.Rows)
{
if (ByDDMMYYYY == 0) //BY DAY
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["Day"].ToString() + '\\' + aRow["Month"].ToString() + "\\" + aRow["Year"].ToString();
}
else if (ByDDMMYYYY == 1)//BY MONTH
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["Month"].ToString() + '\\' + aRow["Year"].ToString();
}
else //BY YEAR
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["Year"].ToString();
}
xlWorkSheet.Cells[RowCnt, 2] = Math.Round(double.Parse(aRow["TotalCost"].ToString()), 2);
xlWorkSheet.Cells[RowCnt, 3] = Math.Round(double.Parse(aRow["TotalPrice"].ToString()), 2);
xlWorkSheet.Cells[RowCnt, 4] = Math.Round(double.Parse(aRow["TotalProfit"].ToString()), 2);
RowCnt++;
}
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString());
oRng.EntireColumn.AutoFit();
Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
xlWorkSheet.DisplayRightToLeft = false;
int size = aTable.Rows.Count * 100;
if (size > 600)
{
size = 600;
}
if (size < 300)
{
size = 300;
}
Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300);
Excel.Chart chartPage = myChart.Chart;
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
Excel.Series series2 = seriesCollection.NewSeries();
Excel.Series series3 = seriesCollection.NewSeries();
RowCnt--; //because we started from 1 suppose to be 0
series1.Name = "Total Cost";
series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()];
series2.Name = "Total Sales";
series2.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series2.Values = xlWorkSheet.Range["C" + DataStart.ToString(), "C" + RowCnt.ToString()];
series3.Name = "Gross Profit";
series3.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series3.Values = xlWorkSheet.Range["D" + DataStart.ToString(), "D" + RowCnt.ToString()];
chartPage.ChartType = Excel.XlChartType.xlLineMarkers;
Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis;
series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
series2.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
series3.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format);
if (ExportToExcel)
{
format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7;
xlWorkSheet.SaveAs(ExportPath, format);
}
xlApp.UserControl = false;
//Once done close and quit Excel
return("TRUE");
}
else
{
return("EMPTY");
}
}
catch (Exception ex)
{
MessageBox.Show("{EXCEPTION in Revenues Comparison}" + ex.Message + ex.ToString());
return("ERROR " + ex.Message);
}
finally
{
//Once done close and quit Excel
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
/// <summary>
/// Метод создания и сохранения документов
/// в форматах Microsoft Word (doc, PDF),
/// Excel (exls)
/// </summary>
/// <param name="type">Тип создаваемого документа
/// отчёт или статистика</param>
/// <param name="format">Формат сохранения
/// документ или таблица</param>
/// <param name="name">Название документа</param>
/// <param name="table">Входная таблица с данными</param>
public void Document_Create(Document_Type type,
Document_Format format, string name,
DataTable table)
{
//Получение данных о конфигурации документа
Configuration_class configuration_Class
= new Configuration_class();
configuration_Class.Document_Configuration_Get();
//Проверка на пустоту названия
switch (name != "" || name != null)
{
case true:
//Выбор формата либо Word либо Excel
switch (format)
{
case Document_Format.Word:
//Запуск процесса в дистпечере задач
word.Application application
= new word.Application();
//создание документа в процессе
word.Document document
//Присвоение документа процессу, Visible: true
//возможность редактирования документа
= application.Documents.Add(Visible: true);
try
{
//Объявление дипапазона для формирования текста
word.Range range = document.Range(0, 0);
//89Настройка отступов в документе
document.Sections.PageSetup.LeftMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Left_Merge);
document.Sections.PageSetup.TopMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Top_Merge);
document.Sections.PageSetup.RightMargin
= application.
CentimetersToPoints((float)
Configuration_class.doc_Right_Merg);
document.Sections.PageSetup.BottomMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Bottom_Merge);
//Присвоение текстового знеачения в дипазон
range.Text =
"Мвидео";
//Настройка выравнивания текста
range.ParagraphFormat.Alignment =
word.WdParagraphAlignment.
wdAlignParagraphCenter;
//Настройка интервала после абзаца
range.ParagraphFormat.SpaceAfter = 1;
//Настройка интервала перед абзаца
range.ParagraphFormat.SpaceBefore = 1;
//Настройка межстрочного интервала
range.ParagraphFormat.LineSpacingRule
= word.WdLineSpacing.wdLineSpaceSingle;
//Настройка названия шрифта
range.Font.Name = "Times New Roman";
//Настройка размера шрифта
range.Font.Size = 12;
//Добавление параграфов
document.Paragraphs.Add(); //В конце текста
document.Paragraphs.Add(); //Свободный
document.Paragraphs.Add(); //Для будущего текста
//Параграф для названия документа
word.Paragraph Document_Name
= document.Paragraphs.Add();
//Настройка параграфа через свойство диапазона
Document_Name.Format.Alignment
= word.WdParagraphAlignment.wdAlignParagraphCenter;
Document_Name.Range.Font.Name = "Times New Roman";
Document_Name.Range.Font.Size = 16;
//Проверка на тип документа, отчёт или статистика
switch (type)
{
case Document_Type.Report:
Document_Name.Range.Text = "ОТЧЁТ";
break;
case Document_Type.Statistic:
Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ";
break;
}
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph statparg = document.Paragraphs.Add();
//Создание области таблицы в документе
word.Table stat_table
//Добавление таблицы в область документа
//Указывается параграф в котором документ создан
//Количество строк и столбцов
= document.Tables.Add(statparg.Range,
table.Rows.Count, table.Columns.Count);
//Настройка границ таблицы внутренние
stat_table.Borders.InsideLineStyle
= word.WdLineStyle.wdLineStyleSingle;
//Настройка границ таблицы внешние
stat_table.Borders.OutsideLineStyle
= word.WdLineStyle.wdLineStyleSingle;
//Выравнивание текста внутри ячеек по ширине
stat_table.Rows.Alignment
= word.WdRowAlignment.wdAlignRowCenter;
//Выравнивание текста внутри ячеек по высоте
stat_table.Range.Cells.VerticalAlignment =
word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
stat_table.Range.Font.Size = 11;
stat_table.Range.Font.Name = "Times New Roman";
//Индексация столбцов и строк в Word начинается с 1,1
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 1; col <= table.Columns.Count; col++)
{
stat_table.Cell(row, col).Range.Text
= table.Rows[row - 1][col - 1].ToString();
}
}
document.Paragraphs.Add();
document.Paragraphs.Add();
//Парадграф с фиксациейц даты создания документа
word.Paragraph Footparg = document.Paragraphs.Add();
Footparg.Range.Text =
string.Format("Дата создания \t\t\t{0}",
DateTime.Now.ToString("dd.MM.yyyy"));
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
switch (format)
{
case Document_Format.Word:
//Сохранение документа с названием из метода,
//и в формате doc
document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
word.WdSaveFormat.wdFormatDocument);
break;
case Document_Format.PDF:
//Сохранение документа в формате PDF
document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
word.WdSaveFormat.wdFormatPDF);
break;
}
//Закрываем документ
document.Close();
//Выходим из процесса с его закрытием
application.Quit();
}
break;
case Document_Format.Excel:
//Создание процесса Excel
excel.Application application_ex
= new excel.Application();
//Создание книги
excel.Workbook workbook
= application_ex.Workbooks.Add();
//Создание страницы
excel.Worksheet worksheet
= (excel.Worksheet)workbook.ActiveSheet;
try
{
switch (type)
{
case Document_Type.Report:
//Название страницы
worksheet.Name = "Отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
//ЗАнесение данных в ячейку
worksheet.Cells[row + 1][col + 1]
= table.Rows[row][col].ToString();
}
}
//Указание диапазона работы с ячеёками листа
excel.Range border
//Начало диапазона
= worksheet.Range[worksheet.Cells[1, 1],
//Динамический конец диапазона в зависимости от
//выдодимых данных
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
//Стиль линий границ ячеек
border.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
//Выравнивание во высоте
border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
//Выравнивание по ширине
border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
//Внесение даты создания документа
worksheet.Cells[table.Rows.Count + 3][2]
= string.Format("Дата создания {0}",
DateTime.Now.ToString());
//Объединение ячеек
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
worksheet.Cells[table.Rows.Count + 2,
table.Columns.Count + 2]].Merge();
break;
case Document_Type.Statistic:
worksheet.Name = "Статистический отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1]
= table.Rows[row][col].ToString();
}
}
excel.Range border1
= worksheet.Range[worksheet.Cells[1, 1],
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
border1.Borders.LineStyle
= excel.XlLineStyle.xlContinuous;
border1.VerticalAlignment
= excel.XlHAlign.xlHAlignCenter;
border1.HorizontalAlignment
= excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2]
= string.Format("Дата создания {0}",
DateTime.Now.ToString());
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
worksheet.Cells[table.Rows.Count + 2,
table.Columns.Count + 2]].Merge();
//Класс области графиков
excel.ChartObjects chartObjects
= (excel.ChartObjects)worksheet.ChartObjects(
Type.Missing);
//Область размещения графиков: отступы слева сверху,
//размер ширина и высота
excel.ChartObject chartObject
= chartObjects.Add(300, 50, 250, 250);
//Объявление области графика
excel.Chart chart = chartObject.Chart;
//Объявление колекции построений графиков
excel.SeriesCollection seriesCollection
= (excel.SeriesCollection)chart.SeriesCollection(
Type.Missing);
//Объявление посторения графика
excel.Series series = seriesCollection.NewSeries();
//Тип графика
chart.ChartType = excel.XlChartType.xl3DColumn;
//Диапазон значений по оси X
series.XValues =
worksheet.get_Range("B2", "B" + table.Rows.Count + 1);
//Диапазон значений по оси Y
series.Values =
worksheet.get_Range("C2", "C" + table.Rows.Count + 1);
break;
}
}
catch
{
}
finally
{
//Сохранение книги
workbook.SaveAs(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), application_ex.DefaultSaveFormat);
//Закрытие книги
workbook.Close();
//Завершение процесса
application_ex.Quit();
}
break;
}
break;
case false:
System.Windows.Forms.MessageBox.Show
("Введите название документа");
break;
}
}
private void button2_Click(object sender, EventArgs e)
{
try
{
string fileName = Dir + "\\" + string.Format("{0}.xls", Guid.NewGuid()).Replace(" ", "_");
//if (File.Exists(fileName))
//{
// try
// {
// File.Delete(fileName);
// }
// catch (Exception)
// {
// MessageBox.Show(string.Format("В настоящий момент используется файл:\r\n{0}\r\nДля создания договора закройте пожалуйста файл.", fileName),
// "Невозможно создать договор", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return;
// }
//}
Excel.Application xla = new Excel.Application();
xla.Visible = true;
Excel.Workbook wb = xla.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet ws = (Excel.Worksheet)xla.ActiveSheet;
// Now create the chart.
Excel.ChartObjects chartObjs = (Excel.ChartObjects)ws.ChartObjects();
Excel.ChartObject chartObj = chartObjs.Add(512, 80, 300, 300);
Excel.Chart xlChart = chartObj.Chart;
xlChart.ChartType = Excel.XlChartType.xlBarClustered;
// generate some random data
dateTimePicker1.Value = dateTimePicker1.Value.Date.AddDays(1 - dateTimePicker1.Value.Day);
dateTimePicker2.Value = dateTimePicker2.Value.Date.AddMonths(1).AddDays(-dateTimePicker2.Value.Day);
ws.Cells[2, 2] = string.Format("Результативность работы фирмы за период с {0} по {1}", dateTimePicker1.Value, dateTimePicker2.Value);
ws.Cells[4, 3] = "Период";
ws.Cells[4, 4] = "Количество сделок";
ws.Cells[4, 5] = "Сумма";
int row = 5;
for (var dateFrom = dateTimePicker1.Value; dateFrom <= dateTimePicker2.Value; dateFrom = dateFrom.AddMonths(1))
{
var dateTo = dateFrom.AddMonths(1).AddDays(-1);
var trips = dbj.Jurnal.Where(t => t.data >= dateFrom && t.data <= dateTo);
foreach (var i in trips)
{
ws.Cells[row, 3] = GetMonth(dateFrom.Month);
ws.Cells[row, 4] = trips.Count();
ws.Cells[row, 5] = i.summa;
}
row++;
}
Excel.Range xValues = ws.Range["C5", "C" + (row - 1)];
Excel.Range values = ws.Range["E5", "E" + (row - 1)];
Excel.SeriesCollection seriesCollection = xlChart.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
series1.XValues = xValues;
series1.Values = values;
//xla.ActiveChart.HasTitle = true;
//xla.ActiveChart.ChartTitle.Text = "Тестовая диаграмма";
//xla.ActiveChart.ChartTitle.Font.Size = 13;
//xla.ActiveChart.ChartTitle.Font.Color = 254;
//xla.ActiveChart.ChartTitle.Shadow = true;
//xla.ActiveChart.ChartTitle.Border.LineStyle = Excel.Constants.xlSolid;
}
catch { MessageBox.Show("Ошибка Excel не найден"); }
}
private void Exportar(String Empresa, Int32 Anio, String Titulo, String SubTitulo, String Nombre, System.Data.DataTable Reporte, System.Data.DataTable Comparacion1, System.Data.DataTable Comparacion2, Boolean MostrarRebate = false)
{
XLExcel.Application xlApplication;
XLExcel.Workbook xlWorkbook;
XLExcel.Worksheet xlWorksheetData;
XLExcel.Worksheet xlWorksheetPivot;
XLExcel.Worksheet xlWorksheetComparacion;
XLExcel.Worksheet xlWorksheetPivotRebate = null;
XLExcel.Worksheet xlWorksheetPivotFFVV = null;
XLExcel.Worksheet xlWorksheetPivotFFVVServicios = null;
XLExcel.Range xlRange;
XLExcel.Range xlRangeComparacion;
object misValue = System.Reflection.Missing.Value;
xlApplication = new XLExcel.Application();
Int32 _WorksheetsIndex = 1;
xlWorkbook = xlApplication.Workbooks.Add(misValue);
xlWorksheetData = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetData.Name = "BD";
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetData, misValue, misValue);
}
xlWorksheetPivot = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetPivot.Name = "TOTAL " + Nombre + (MostrarRebate ? " SIN REBATE" : "");
if (MostrarRebate)
{
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue);
}
xlWorksheetPivotRebate = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetPivotRebate.Name = "TOTAL " + Nombre + " CON REBATE";
}
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue);
}
xlWorksheetPivotFFVV = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetPivotFFVV.Name = "FUERZA VENTAS";
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue);
}
xlWorksheetPivotFFVVServicios = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetPivotFFVVServicios.Name = "FFVV POR SERVICIOS";
_WorksheetsIndex += 1;
if (xlWorkbook.Worksheets.Count < _WorksheetsIndex)
{
xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue);
}
xlWorksheetComparacion = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex];
xlWorksheetComparacion.Name = "COMPARATIVO";
try
{
String fuente = "Calibri";
int fontsize = 11;
int _sheetIndex = 1;
Int32 rowIndex = 0;
Int32 colIndex = 0;
#region [ Statment ]
xlWorksheetData.Activate();
//HEADER
var _columns = new Object[1, Reporte.Columns.Count];
foreach (System.Data.DataColumn _column in Reporte.Columns)
{
_columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1;
}
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[1, Reporte.Columns.Count]];
xlRange.Value2 = _columns;
rowIndex = 0;
colIndex = 0;
//CELLS
var _data = new Object[Reporte.Rows.Count, Reporte.Columns.Count];
foreach (System.Data.DataRow _row in Reporte.Rows)
{
colIndex = 0;
foreach (System.Data.DataColumn _column in Reporte.Columns)
{
_data[rowIndex, colIndex] = _row[_column.ColumnName];
colIndex += 1;
}
rowIndex += 1;
}
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[2, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlRange.Value2 = _data;
#endregion
#region [ Comparacion ]
xlWorksheetComparacion = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 6 : 5)];
xlWorksheetComparacion.Name = "COMPARATIVO";
xlWorksheetComparacion.Activate();
rowIndex = 0;
colIndex = 0;
//HEADER
_columns = new Object[1, Comparacion1.Columns.Count];
foreach (System.Data.DataColumn _column in Comparacion1.Columns)
{
_columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1;
}
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, Comparacion1.Columns.Count]];
xlRangeComparacion.Value2 = _columns;
rowIndex = 0;
colIndex = 0;
//CELLS
_data = new Object[Comparacion1.Rows.Count, Comparacion1.Columns.Count];
foreach (System.Data.DataRow _row in Comparacion1.Rows)
{
colIndex = 0;
foreach (System.Data.DataColumn _column in Comparacion1.Columns)
{
_data[rowIndex, colIndex] = _row[_column.ColumnName]; colIndex += 1;
}
rowIndex += 1;
}
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[8, 1], xlWorksheetComparacion.Cells[Comparacion1.Rows.Count + 7, Comparacion1.Columns.Count]];
xlRangeComparacion.Value2 = _data;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, 1]];
xlRangeComparacion.Value = (Anio - 1).ToString();
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[10, 14]];
setBorderCelda(xlRangeComparacion, XLExcel.XlBorderWeight.xlThin, XLExcel.XlLineStyle.xlContinuous, true, true, true, true, true, true, true, false, false);
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, 14]];
xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid;
xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic;
xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2;
xlRangeComparacion.Interior.TintAndShade = 0;
xlRangeComparacion.Interior.PatternTintAndShade = 0;
xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1;
xlRangeComparacion.Font.TintAndShade = 0;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[10, 1]];
xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid;
xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic;
xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2;
xlRangeComparacion.Interior.TintAndShade = 0;
xlRangeComparacion.Interior.PatternTintAndShade = 0;
xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1;
xlRangeComparacion.Font.TintAndShade = 0;
rowIndex = 0;
colIndex = 0;
//HEADER
_columns = new Object[1, Comparacion2.Columns.Count];
foreach (System.Data.DataColumn _column in Comparacion2.Columns)
{
_columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1;
}
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, Comparacion2.Columns.Count]];
xlRangeComparacion.Value2 = _columns;
rowIndex = 0;
colIndex = 0;
//CELLS
_data = new Object[Comparacion2.Rows.Count, Comparacion2.Columns.Count];
foreach (System.Data.DataRow _row in Comparacion2.Rows)
{
colIndex = 0;
foreach (System.Data.DataColumn _column in Comparacion2.Columns)
{
_data[rowIndex, colIndex] = _row[_column.ColumnName]; colIndex += 1;
}
rowIndex += 1;
}
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[13, 1], xlWorksheetComparacion.Cells[Comparacion2.Rows.Count + 12, Comparacion2.Columns.Count]];
xlRangeComparacion.Value2 = _data;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, 1]];
xlRangeComparacion.Value = Anio.ToString();
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[15, 14]];
setBorderCelda(xlRangeComparacion, XLExcel.XlBorderWeight.xlThin, XLExcel.XlLineStyle.xlContinuous, true, true, true, true, true, true, true, false, false);
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, 14]];
xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid;
xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic;
xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2;
xlRangeComparacion.Interior.TintAndShade = 0;
xlRangeComparacion.Interior.PatternTintAndShade = 0;
xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1;
xlRangeComparacion.Font.TintAndShade = 0;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[15, 1]];
xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid;
xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic;
xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2;
xlRangeComparacion.Interior.TintAndShade = 0;
xlRangeComparacion.Interior.PatternTintAndShade = 0;
xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1;
xlRangeComparacion.Font.TintAndShade = 0;
XLExcel.Chart XLChart1 = (XLExcel.Chart)xlWorksheetComparacion.Shapes.AddChart(XLExcel.XlChartType.xl3DColumnClustered, 100, 250, 500, 300).Chart;
XLExcel.SeriesCollection XLSeriesCollection1 = XLChart1.SeriesCollection();
XLExcel.Series XLSerie1 = XLSeriesCollection1.NewSeries();
XLSerie1.Name = "=COMPARATIVO!$A$7";
XLSerie1.Values = "=COMPARATIVO!$B$8:$M$8";
XLSerie1.XValues = "=COMPARATIVO!$B$7:$M$7";
XLExcel.Series XLSerie2 = XLSeriesCollection1.NewSeries();
XLSerie2.Name = "=COMPARATIVO!$A$12";
XLSerie2.Values = "=COMPARATIVO!$B$13:$M$13";
XLSerie2.XValues = "=COMPARATIVO!$B$7:$M$7";
XLChart1.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart);
XLChart1.ChartTitle.Text = String.Format("COMPARATIVO {0} - TEUS {1} VS. {2}", Titulo, (Anio - 1), Anio);
XLChart1.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis);
XLExcel.Axis XlAxis1 = XLChart1.Axes(XLExcel.XlAxisType.xlValue, XLExcel.XlAxisGroup.xlPrimary) as XLExcel.Axis;
XlAxis1.HasTitle = true;
XlAxis1.AxisTitle.Caption = "TEUS";
//XLChart1.Activate();
XLChart1.Refresh();
XLExcel.Chart XLChart2 = (XLExcel.Chart)xlWorksheetComparacion.Shapes.AddChart(XLExcel.XlChartType.xl3DColumnClustered, 100, 600, 500, 300).Chart;
XLExcel.SeriesCollection XLSeriesCollection2 = XLChart2.SeriesCollection();
XLExcel.Series XLSerie3 = XLSeriesCollection2.NewSeries();
XLSerie3.Name = "=COMPARATIVO!$A$7";
XLSerie3.Values = "=COMPARATIVO!$B$9:$M$9";
XLSerie3.XValues = "=COMPARATIVO!$B$7:$M$7";
XLExcel.Series XLSerie4 = XLSeriesCollection2.NewSeries();
XLSerie4.Name = "=COMPARATIVO!$A$12";
XLSerie4.Values = "=COMPARATIVO!$B$14:$M$14";
XLSerie4.XValues = "=COMPARATIVO!$B$7:$M$7";
XLChart2.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart);
XLChart2.ChartTitle.Text = String.Format("COMPARATIVO {0} - PROFIT {1} VS. {2}", Titulo, (Anio - 1), Anio);
XLChart2.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis);
XLExcel.Axis XlAxis2 = XLChart2.Axes(XLExcel.XlAxisType.xlValue, XLExcel.XlAxisGroup.xlPrimary) as XLExcel.Axis;
XlAxis2.HasTitle = true;
XlAxis2.AxisTitle.Caption = "PROFIT";
//XLChart2.Activate();
XLChart2.Refresh();
//TITLE
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[2, 1], xlWorksheetComparacion.Cells[2, 14]];
xlRangeComparacion.MergeCells = true;
xlRangeComparacion.Value = Titulo + " - COMPARATIVO TEUS Y PROFIT";
xlRangeComparacion.Font.Bold = true;
xlRangeComparacion.Font.Size = 16;
xlRangeComparacion.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangeComparacion.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[3, 1], xlWorksheetComparacion.Cells[3, 14]];
xlRangeComparacion.MergeCells = true;
xlRangeComparacion.Value = SubTitulo + " - " + (Anio - 1).ToString() + " VS. " + Anio.ToString();
xlRangeComparacion.Font.Bold = true;
xlRangeComparacion.Font.Size = 12;
xlRangeComparacion.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangeComparacion.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
#endregion
#region [ TOTAL SIN REBATE ]
xlWorksheetData.Activate();
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlWorksheetPivot = (XLExcel.Worksheet)xlWorkbook.Worksheets[2];
xlWorksheetPivot.Name = "TOTAL " + Nombre + (MostrarRebate ? " SIN REBATE" : "");
xlWorksheetPivot.Activate();
XLExcel.Range xlRangePivot;
//TITLE
xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[2, 1], xlWorksheetPivot.Cells[2, 14]];
xlRangePivot.MergeCells = true;
xlRangePivot.Value = Empresa + "-" + Titulo;
xlRangePivot.Font.Bold = true;
xlRangePivot.Font.Size = 16;
xlRangePivot.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivot.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[3, 1], xlWorksheetPivot.Cells[3, 14]];
xlRangePivot.MergeCells = true;
xlRangePivot.Value = SubTitulo + " " + Anio.ToString();
xlRangePivot.Font.Bold = true;
xlRangePivot.Font.Size = 12;
xlRangePivot.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivot.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[6, 1], xlWorksheetPivot.Cells[6, 1]];;
// create Pivot Cache and Pivot Table
XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange);
XLExcel.PivotTable XLPivotTable = (XLExcel.PivotTable)xlWorksheetPivot.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivot, TableName: "SIN REBATE");
// create Pivot Field, note that name will be the same as column name on sheet one
XLExcel.PivotField XLPivotFieldMES = (XLExcel.PivotField)XLPivotTable.PivotFields("MES");
XLPivotFieldMES.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField;
XLPivotFieldMES.Name = "MES";
XLExcel.PivotField XLPivotFieldVENDEDOR = (XLExcel.PivotField)XLPivotTable.PivotFields("VENDEDOR");
XLPivotFieldVENDEDOR.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldVENDEDOR.Name = "VENDEDOR";
XLExcel.PivotField XLPivotFieldTEUS = (XLExcel.PivotField)XLPivotTable.PivotFields("TEUS");
XLPivotFieldTEUS.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldTEUS.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldTEUS.Name = "Sum TEUS";
XLExcel.PivotField XLPivotFieldRENT_TOTAL = (XLExcel.PivotField)XLPivotTable.PivotFields("RENT_TOTAL");
XLPivotFieldRENT_TOTAL.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTAL.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTAL.Name = "Sum RENT_TOTAL";
XLPivotTable.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom);
XLPivotTable.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotTable.DataPivotField.Position = 2;
XLPivotTable.TableStyle2 = "PivotStyleLight18";
XLPivotTable.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow);
#endregion
if (MostrarRebate)
{
#region [ TOTAL CON REBATE ]
xlWorksheetData.Activate();
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlWorksheetPivotRebate = (XLExcel.Worksheet)xlWorkbook.Worksheets[3];
xlWorksheetPivotRebate.Name = "TOTAL " + Nombre + " CON REBATE";
xlWorksheetPivotRebate.Activate();
XLExcel.Range xlRangePivotRebate;
//TITLE
xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[2, 1], xlWorksheetPivotRebate.Cells[2, 14]];
xlRangePivotRebate.MergeCells = true;
xlRangePivotRebate.Value = Empresa + "-" + Titulo;
xlRangePivotRebate.Font.Bold = true;
xlRangePivotRebate.Font.Size = 16;
xlRangePivotRebate.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotRebate.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[3, 1], xlWorksheetPivotRebate.Cells[3, 14]];
xlRangePivotRebate.MergeCells = true;
xlRangePivotRebate.Value = SubTitulo + " " + Anio.ToString();
xlRangePivotRebate.Font.Bold = true;
xlRangePivotRebate.Font.Size = 12;
xlRangePivotRebate.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotRebate.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[6, 1], xlWorksheetPivotRebate.Cells[6, 1]];;
// create Pivot Cache and Pivot Table
//XLExcel.PivotCache XLPivotCacheRebate = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange);
XLExcel.PivotTable XLPivotTableRebate = (XLExcel.PivotTable)xlWorksheetPivotRebate.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotRebate, TableName: "CON REBATE");
// create Pivot Field, note that name will be the same as column name on sheet one
XLExcel.PivotField XLPivotFieldMESRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("MES");
XLPivotFieldMESRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField;
XLPivotFieldMESRebate.Name = "MES";
XLExcel.PivotField XLPivotFieldVENDEDORRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("VENDEDOR");
XLPivotFieldVENDEDORRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldVENDEDORRebate.Name = "VENDEDOR";
XLExcel.PivotField XLPivotFieldTEUSRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("TEUS");
XLPivotFieldTEUSRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldTEUSRebate.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldTEUSRebate.Name = "Sum TEUS";
XLExcel.PivotField XLPivotFieldRENT_TOTALRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("RENTABILIDAD_CON_REBATE");
XLPivotFieldRENT_TOTALRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALRebate.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALRebate.Name = "Sum RENTABILIDAD_CON_REBATE";
XLPivotTableRebate.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom);
XLPivotTableRebate.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotTableRebate.DataPivotField.Position = 2;
XLPivotTableRebate.TableStyle2 = "PivotStyleLight18";
XLPivotTableRebate.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow);
#endregion
}
#region [ FUERZA DE VENTAS ]
xlWorksheetData.Activate();
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlWorksheetPivotFFVV = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 4 : 3)];
xlWorksheetPivotFFVV.Name = "FUERZA VENTAS";
xlWorksheetPivotFFVV.Activate();
XLExcel.Range xlRangePivotFFVV;
//TITLE
xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[2, 1], xlWorksheetPivotFFVV.Cells[2, 14]];
xlRangePivotFFVV.MergeCells = true;
xlRangePivotFFVV.Value = Empresa + "-" + Titulo;
xlRangePivotFFVV.Font.Bold = true;
xlRangePivotFFVV.Font.Size = 16;
xlRangePivotFFVV.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotFFVV.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[3, 1], xlWorksheetPivotFFVV.Cells[3, 14]];
xlRangePivotFFVV.MergeCells = true;
xlRangePivotFFVV.Value = SubTitulo + " " + Anio.ToString();
xlRangePivotFFVV.Font.Bold = true;
xlRangePivotFFVV.Font.Size = 12;
xlRangePivotFFVV.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotFFVV.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[6, 1], xlWorksheetPivotFFVV.Cells[6, 1]];;
// create Pivot Cache and Pivot Table
//XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange);
XLExcel.PivotTable XLPivotTableFFVV = (XLExcel.PivotTable)xlWorksheetPivotFFVV.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotFFVV, TableName: "FFVV");
// create Pivot Field, note that name will be the same as column name on sheet one
XLExcel.PivotField XLPivotFieldMESFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("MES");
XLPivotFieldMESFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField;
XLPivotFieldMESFFVV.Name = "MES";
XLExcel.PivotField XLPivotFieldVENDEDORFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("VENDEDOR");
XLPivotFieldVENDEDORFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldVENDEDORFFVV.Name = "VENDEDOR";
XLExcel.PivotField XLPivotFieldTEUSFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("TEUS");
XLPivotFieldTEUSFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldTEUSFFVV.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldTEUSFFVV.Name = "Sum TEUS";
if (MostrarRebate)
{
XLExcel.PivotField XLPivotFieldRENT_TOTALFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("RENTABILIDAD_CON_REBATE");
XLPivotFieldRENT_TOTALFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALFFVV.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALFFVV.Name = "Sum RENTABILIDAD_CON_REBATE";
}
else
{
XLExcel.PivotField XLPivotFieldRENT_TOTALFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("RENT_TOTAL");
XLPivotFieldRENT_TOTALFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALFFVV.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALFFVV.Name = "Sum RENT_TOTAL";
}
XLPivotTableFFVV.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom);
XLPivotTableFFVV.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotTableFFVV.DataPivotField.Position = 2;
XLPivotTableFFVV.TableStyle2 = "PivotStyleLight18";
XLPivotTableFFVV.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow);
#endregion
#region [ FUERZA DE VENTAS TRAFICO ]
xlWorksheetData.Activate();
xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]];
xlWorksheetPivotFFVVServicios = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 5 : 4)];
xlWorksheetPivotFFVVServicios.Name = "FF VV Servicios";
xlWorksheetPivotFFVVServicios.Activate();
XLExcel.Range xlRangePivotFFVVServicios;
//TITLE
xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[2, 1], xlWorksheetPivotFFVVServicios.Cells[2, 14]];
xlRangePivotFFVVServicios.MergeCells = true;
xlRangePivotFFVVServicios.Value = Empresa + "-" + Titulo;
xlRangePivotFFVVServicios.Font.Bold = true;
xlRangePivotFFVVServicios.Font.Size = 16;
xlRangePivotFFVVServicios.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotFFVVServicios.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[3, 1], xlWorksheetPivotFFVVServicios.Cells[3, 14]];
xlRangePivotFFVVServicios.MergeCells = true;
xlRangePivotFFVVServicios.Value = SubTitulo + " " + Anio.ToString();
xlRangePivotFFVVServicios.Font.Bold = true;
xlRangePivotFFVVServicios.Font.Size = 12;
xlRangePivotFFVVServicios.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter;
xlRangePivotFFVVServicios.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter;
xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[6, 1], xlWorksheetPivotFFVVServicios.Cells[6, 1]];;
// create Pivot Cache and Pivot Table
//XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange);
XLExcel.PivotTable XLPivotTableFFVVServicios = (XLExcel.PivotTable)xlWorksheetPivotFFVVServicios.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotFFVVServicios, TableName: "FFVV Servicios");
// create Pivot Field, note that name will be the same as column name on sheet one
XLExcel.PivotField XLPivotFieldMESFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("MES");
XLPivotFieldMESFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField;
XLPivotFieldMESFFVVServicios.Name = "MES";
XLExcel.PivotField XLPivotFieldVENDEDORFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("TRAFICO");
XLPivotFieldVENDEDORFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldVENDEDORFFVVServicios.Name = "TRAFICO";
XLExcel.PivotField XLPivotFieldSERVICIOFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("VENDEDOR");
XLPivotFieldSERVICIOFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotFieldSERVICIOFFVVServicios.Name = "VENDEDOR";
XLExcel.PivotField XLPivotFieldTEUSFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("TEUS");
XLPivotFieldTEUSFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldTEUSFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldTEUSFFVVServicios.Name = "Sum TEUS";
if (MostrarRebate)
{
XLExcel.PivotField XLPivotFieldRENT_TOTALFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("RENTABILIDAD_CON_REBATE");
XLPivotFieldRENT_TOTALFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALFFVVServicios.Name = "Sum RENTABILIDAD_CON_REBATE";
}
else
{
XLExcel.PivotField XLPivotFieldRENT_TOTALFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("RENT_TOTAL");
XLPivotFieldRENT_TOTALFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField;
XLPivotFieldRENT_TOTALFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum;
XLPivotFieldRENT_TOTALFFVVServicios.Name = "Sum RENT_TOTAL";
}
XLPivotTableFFVVServicios.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom);
XLPivotTableFFVVServicios.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField;
XLPivotTableFFVVServicios.DataPivotField.Position = 2;
XLPivotTableFFVVServicios.TableStyle2 = "PivotStyleLight18";
XLPivotTableFFVVServicios.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow);
#endregion
xlApplication.Visible = true;
releaseObject(xlWorksheetData);
releaseObject(xlWorksheetPivot);
if (MostrarRebate)
{
releaseObject(xlWorksheetPivotRebate);
}
releaseObject(xlWorksheetComparacion);
releaseObject(xlWorkbook);
releaseObject(xlApplication);
}
catch (Exception ex)
{
releaseObject(xlWorksheetData);
releaseObject(xlWorksheetPivot);
if (MostrarRebate)
{
releaseObject(xlWorksheetPivotRebate);
}
releaseObject(xlWorksheetComparacion);
releaseObject(xlWorkbook);
releaseObject(xlApplication);
throw ex;
}
}
public PlotGraph(double[] x, double[] y, double k, double b)
{
string path = GetTemplate();
var n = x.Length.ToString();
object misValue = Missing.Value;
var graph = new Excel.Application()
{
Visible = true
};
var workbook = graph.Workbooks.Add(misValue);
var worksheet = (Excel.Worksheet)workbook.Worksheets.Item[1];
worksheet.Cells[1, 1] = "";
worksheet.Cells[1, 2] = "";
worksheet.Cells[1, 3] = "";
worksheet.Cells[1, 4] = "";
for (int i = 1; i < 5; i++)
{
for (int j = 1; j <= x.Length; j++)
{
switch (i)
{
case 1:
worksheet.Cells[j, i] = x[j - 1];
break;
case 2:
worksheet.Cells[j, i] = y[j - 1];
break;
case 3:
worksheet.Cells[j, i] = x[j - 1];
break;
case 4:
worksheet.Cells[j, i] = k * x[j - 1] + b;
break;
}
}
}
Excel.Range range;
var excelCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
var chart = excelCharts.Add(10, 80, 500, 300);
var chartPage = chart.Chart;
chartPage.ApplyChartTemplate(path); //Используем шаблон
//Коллекция данных
Excel.SeriesCollection seriesCollection = chart.Chart.SeriesCollection();
//Данные выборки
Excel.Series defaultSeries = seriesCollection.NewSeries();
defaultSeries.ChartType = Excel.XlChartType.xlXYScatter;
defaultSeries.XValues = worksheet.Range["A1", "A" + n];
defaultSeries.Values = worksheet.Range["B1", "B" + n];
defaultSeries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;
//Данные полученных значений
var plotedSeries = seriesCollection.NewSeries();
plotedSeries.XValues = worksheet.Range["C1", "C" + n];
plotedSeries.Values = worksheet.Range["D1", "D" + n];
plotedSeries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;
chartPage.HasLegend = true;
chartPage.Legend.LegendEntries(1).Delete();
chartPage.Legend.LegendEntries(1).Delete();
var counter = 1;
foreach (Excel.Series series in chartPage.SeriesCollection())
{
series.Name = counter++ == 3 ? "Данные выборки" : "Функция регрессии";
}
chartPage.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
foreach (Excel.ChartObject co in excelCharts)
{
co.Select();
Excel.Chart thisChart = (Excel.Chart)co.Chart;
thisChart.Export(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + @"\chart.png", "PNG", false);
}
workbook.Close(false, misValue, misValue);
graph.Quit();
}
public void Document_Create(Document_Type type, Document_format format, string name, DataTable table)
{
Configuration_Class configuration_Class = new Configuration_Class();
configuration_Class.Document_configuration_Get();
switch (name != "" || name != null)
{
case true:
switch (format)
{
case Document_format.Word:
word.Application application = new word.Application();
word.Document document = application.Documents.Add(Visible: true);
try
{
word.Range range = document.Range(0, 0);
document.Sections.PageSetup.LeftMargin = application.CentimetersToPoints((float)Configuration_Class.doc_Left_Merge);
document.Sections.PageSetup.TopMargin = application.CentimetersToPoints((float)Configuration_Class.doc_Top_Merge);
document.Sections.PageSetup.RightMargin = application.CentimetersToPoints((float)Configuration_Class.doc_Right_Merge);
range.Text = Configuration_Class.Organization_Name;
//Выравнивание
range.ParagraphFormat.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter;
//Интревал
range.ParagraphFormat.SpaceAfter = 1;
range.ParagraphFormat.SpaceBefore = 1;
range.ParagraphFormat.LineSpacingRule = word.WdLineSpacing.wdLineSpaceSingle;
range.Font.Name = "Times New Roman";
range.Font.Size = 12;
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph Document_Name = document.Paragraphs.Add();
Document_Name.Format.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter;
Document_Name.Range.Font.Name = "Times New Roman";
Document_Name.Range.Font.Size = 16;
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph statparg = document.Paragraphs.Add();
//Создание области таблицы в документе
word.Table stat_table
//ие таблицы в области документа
//указывается параграф в котором документ создан
//Количество строк и столбцов
= document.Tables.Add(statparg.Range, table.Rows.Count, table.Columns.Count);
//Настройка границ таблицы внутренние
stat_table.Borders.InsideLineStyle = word.WdLineStyle.wdLineStyleSingle;
//Настройка границ таблицы внешние
stat_table.Borders.OutsideLineStyle = word.WdLineStyle.wdLineStyleSingle;
//Выравнивание текста внутри ячеек по ширине
stat_table.Rows.Alignment = word.WdRowAlignment.wdAlignRowCenter;
//Выравнивание текста внутри ячеек по высоте
stat_table.Range.Cells.VerticalAlignment = word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
stat_table.Range.Font.Size = 11;
stat_table.Range.Font.Name = "Times New Romman";
//Индексикация столбцов и строк в Word начинается с 1,1
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 1; col <= table.Columns.Count; col++)
{
stat_table.Cell(row, col).Range.Text = table.Rows[row - 1][col - 1].ToString();
}
}
document.Paragraphs.Add();
document.Paragraphs.Add();
//Параграф с фиксацией даты слздания документа
word.Paragraph Footparg = document.Paragraphs.Add();
Footparg.Range.Text = string.Format("Дата создания \t\t\t{0}", DateTime.Now.ToString("dd.MM.yyyy"));
}
catch
{
}
finally
{
switch (format)
{
case Document_format.Word:
//Сохранение документа с названием из метода и формата doc
document.SaveAs2(name, word.WdSaveFormat.wdFormatDocument);
break;
case Document_format.PDF:
//Сохранение документа в формате PDF
document.SaveAs2(name, word.WdSaveFormat.wdFormatPDF);
break;
}
//Закрываем документ
document.Close();
//Выходим из процесса с его закрытием
application.Quit();
}
break;
case Document_format.Excel:
//Создание процесса Excel
excel.Application application_ex = new excel.Application();
//Создание книги
excel.Workbook workbook = application_ex.Workbooks.Add();
//Создание страницы
excel.Worksheet worksheet = (excel.Worksheet)workbook.ActiveSheet;
try
{
switch (type)
{
case Document_Type.Report:
//Название страницы
worksheet.Name = "Отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString();
//Указание диапазона работы с ячееками листа
excel.Range border
//Начало диапазона
= worksheet.Range[worksheet.Cells[1, 1],
//Динамический конец диапазона в зависимости от выходимых данных
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
//Стиль линий границ ячеек
border.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
//Выравнивание во высоте
border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
//Выравнивание по ширине
border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
//Внесение даты создания документа
worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString());
//Объединение ячеек
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge();
}
}
break;
case Document_Type.Statistic:
worksheet.Name = "Статический отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString();
}
}
excel.Range border1 = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1][table.Columns.Count + 1]];
border1.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
border1.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
border1.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString());
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge();
//Класс области графики
excel.ChartObjects chartObjects = (excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
//Область размещения графиков: отступы слева сверху, размер ширина и высота
excel.ChartObject chartObject = chartObjects.Add(300, 50, 250, 250);
//Объявление области графики
excel.Chart chart = chartObject.Chart;
//Объявление коллекции построений графиков
excel.SeriesCollection seriesCollection = (excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
//Объявление построения графиков
excel.Series series = seriesCollection.NewSeries();
//Тип графика
chart.ChartType = excel.XlChartType.xl3DColumn;
//Диапазон значений по оси Х
series.XValues = worksheet.get_Range("B2", "B" + table.Rows.Count + 1);
//Диапазон значений по оси У
series.Values = worksheet.get_Range("C2", "C" + table.Rows.Count + 1);
break;
}
}
catch
{
}
finally
{
//Сохранение книги
worksheet.SaveAs(name, application_ex.DefaultSaveFormat);
//Закрытие книги
workbook.Close();
//Завершение процесса
application_ex.Quit();
}
break;
}
break;
case false:
System.Windows.Forms.MessageBox.Show("Введите название документа");
break;
}
}
private void CreateDocument()
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
app.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(@"C:\LPA_IPAMLx_V2\LPA_IPAM.xlsx",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Worksheet ws = wb.Worksheets[1];
int col = 1;
int row = 16;
ws.Range["B8"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B9"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B10"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B11"].Value = Convert.ToString(replaceData).TrimStart();
GetNotas();
foreach (Notas nota in listaNotas)
{
ws.Cells[row, col].Font.Size = 10;
ws.Cells[row, col].Value = nota.UC.TrimStart();
ws.Cells[row, col + 5].Font.Size = 10;
ws.Cells[row, col + 5].Value = nota.Nota;
ws.Cells[row, col + 6].Font.Size = 10;
ws.Cells[row, col + 6].Value = nota.ECTS;
row++;
}
GetMedia((string)replaceNrAluno, (string)replaceCdCurso);
ws.Range["B59"].Value = (string)replaceMedia + " valores";
ws.Range["B73"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B74"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B75"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B76"].Value = Convert.ToString(replaceData).TrimStart();
Competencias competencias = GetDadosCompetencias((string)replaceNrAluno);
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(5, 1165, 470, 300);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chartPage.SeriesCollection();
var ser = seriesCollection.NewSeries();
chartPage.Legend.Delete();
ser.Values = new double[] { competencias.adaptacao, competencias.trabalhar, competencias.decisoes, competencias.objactivos, competencias.ideias, competencias.aprendizagem, competencias.mentalidadeGlobal, competencias.gestaoEquipas };
ser.XValues = new string[] { "Adaptação", "Trabalhar com os Outros", "Tomar Decisões", "Alcançar Objetivos", "Geração de Ideias", "Aprendizagem", "Mentalidade Global", "Gestão de Equipas" };
//chartRange = ws.get_Range("A1", "d5");
//chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlBarClustered;
chartPage.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(5).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(6).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(7).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(8).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Range["B127"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B128"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B129"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B130"].Value = Convert.ToString(replaceData).TrimStart();
GetLinguas((string)replaceNrAluno);
GetRespAcademicas((string)replaceNrAluno);
GetEstagios((string)replaceNrAluno);
GetMobilidade((string)replaceNrAluno);
GetPremios((string)replaceNrAluno);
GetAtividades((string)replaceNrAluno);
GetOutrasExperiencias((string)replaceNrAluno);
GetRespSocial((string)replaceNrAluno);
col = 1;
row = 135;
if (listaLinguas.Count > 0)
{
string header = "LÍNGUAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Linguas l in listaLinguas)
{
row++;
ws.Cells[row, col].Value = " > " + l.lingua + " | Nível " + l.nivel;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaRespAcademicas.Count > 0)
{
string header = "RESPONSABILIDADES ACADÉMICAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (ResponsabilidadesAcademicas l in listaRespAcademicas)
{
row++;
ws.Cells[row, col].Value = " > " + l.responsabilidade + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaEstagios.Count > 0)
{
string header = "ESTÁGIOS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Estagios l in listaEstagios)
{
if (l.dataInicio != "01/01/1900")
{
row++;
ws.Cells[row, col].Value = " > " + l.tipoEstagio + " na " + l.empresa + " | De " + l.dataInicio.Substring(0, 10) + " a " + l.dataFim.Substring(0, 10);
}
else
{
row++;
ws.Cells[row, col].Value = " > " + l.tipoEstagio + " na " + l.empresa;
}
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaMobilidade.Count > 0)
{
string header = "MOBILIDADE INTERNACIONAL";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Mobilidade l in listaMobilidade)
{
row++;
ws.Cells[row, col].Value = " > " + l.tipo + " na " + l.programa + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaRespSocial.Count > 0)
{
string header = "RESPONSABILIDADE SOCIAL & VOLUNTARIADO";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (ResposabilidadeSocial l in listaRespSocial)
{
row++;
ws.Cells[row, col].Value = " > " + l.AcaoSocial + " | " + l.AnoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaPremios.Count > 0)
{
string header = "PRÉMIOS & RECONHECIMENTOS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Premios l in listaPremios)
{
row++;
ws.Cells[row, col].Value = " > " + l.premio + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaAtividades.Count > 0)
{
string header = "ATIVIDADES DESPORTIVAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (ActDesportivas l in listaAtividades)
{
row++;
ws.Cells[row, col].Value = " > " + l.atividade + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaExperiencias.Count > 0)
{
string header = "OUTRAS EXPERIÊNCIAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (OutrasExperiencias l in listaExperiencias)
{
row++;
ws.Cells[row, col].Value = " > " + l.experiencia + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
string data = Convert.ToString(DateTime.Now.ToLongDateString());
ws.Cells[164, 1].Value = "Lisboa, " + data;
int nrCertificado = GravaCertificado(competencias);
string anoLetivo = getAnoLetivo((string)replaceNrAluno, (string)replaceCdCurso);
ws.Range["E11"].Value = nrCertificado + " | POR | " + anoLetivo;
ws.Range["E76"].Value = nrCertificado + " | POR | " + anoLetivo;
ws.Range["E130"].Value = nrCertificado + " | POR | " + anoLetivo;
}
public static void CreateChart(ref Excel.Worksheet ws)
{
Excel.Workbook wb;
Excel.Chart chart;
Excel.ChartObject chartobject;
wb = (Excel.Workbook)ws.Parent;
long lastrow = ws.UsedRange.Rows.Count;
long lastcol = ws.UsedRange.Columns.Count;
//Console.WriteLine("lastrow: {0}, lastcol: {1}", lastrow, lastcol);
if (lastrow <= 2)
{
Console.WriteLine("有效数据不足");
return;
}
// delete old chart
if (((Excel.ChartObjects)ws.ChartObjects()).Count >= 1)
{
//Console.WriteLine("Chart num: {0}", ((Excel.ChartObjects)ws.ChartObjects()).Count);
((Excel.ChartObjects)ws.ChartObjects()).Delete();
}
for (int col = 2; col <= lastcol; col++)
{
if (((Excel.Range)ws.Cells[1, col]).Value == null)
{
break;
}
List <double> data = new List <double>();
for (int row = 2; row <= lastrow; row++)
{
double?d = ((Excel.Range)ws.Cells[row, col]).Value;
if (d != null)
{
data.Add((double)d);
}
}
double mean = data.Average();
double sd = data.StandardDeviation();
double cv = sd / mean;
string chartTitle = string.Format("{0} {1} 累积均值: {2:F2} 累积标准差: {3:F2} CV: {4:P2}", ws.Name, ((Excel.Range)ws.Cells[1, col]).Value, mean, sd, cv);
Console.WriteLine(chartTitle);
//固定均值，标准差设置
ws.Range["J1"].Offset[0, col - 2].Value = ws.Cells[1, col].Value;
ws.Range["I2"].Value = "固定均值";
ws.Range["I3"].Value = "固定标准差";
if (ws.Range["J2"].Offset[0, col - 2].Value != null)
{
mean = ws.Range["J2"].Offset[0, col - 2].Value;
}
else
{
Console.WriteLine("未设置{0}的固定均值", ws.Cells[1, col].Value);
}
if (ws.Range["J3"].Offset[0, col - 2].Value != null)
{
sd = ws.Range["J3"].Offset[0, col - 2].Value;
}
else
{
Console.WriteLine("未设置{0}的固定标准差", ws.Cells[1, col].Value);
}
double[] meanarray = new double[lastrow - 1];
double[] plusOneSDarray = new double[lastrow - 1];
double[] plusTwoSDarray = new double[lastrow - 1];
double[] plusThreeSDarray = new double[lastrow - 1];
double[] minusOneSDarray = new double[lastrow - 1];
double[] minusTwoSDarray = new double[lastrow - 1];
double[] minusThreeSDarray = new double[lastrow - 1];
// 初始化
for (int i = 0; i < lastrow - 1; i++)
{
meanarray[i] = mean;
plusOneSDarray[i] = mean + sd;
plusTwoSDarray[i] = mean + 2 * sd;
plusThreeSDarray[i] = mean + 3 * sd;
minusOneSDarray[i] = mean - sd;
minusTwoSDarray[i] = mean - 2 * sd;
minusThreeSDarray[i] = mean - 3 * sd;
}
// Create a new chart
chart = (Excel.Chart)wb.Charts.Add();
Excel.Range XRng = ws.Range[ws.Cells[2, 1], ws.Cells[lastrow, 1]];
Excel.Range dataRng = ws.Range[ws.Cells[1, col], ws.Cells[lastrow, col]];
chart.SetSourceData(dataRng, Excel.XlRowCol.xlColumns);
chart.ChartType = Excel.XlChartType.xlLineMarkers;
chart.HasLegend = false;
chart.HasTitle = true;
chart.ChartTitle.Text = chartTitle;
// 纵坐标范围
string min = (mean - 3.75 * sd) > data.Min() ? data.Min().ToString("F2") : (mean - 3.75 * sd).ToString("F2");
string max = data.Max() > (mean + 3.75 * sd) ? data.Max().ToString("F2") : (mean + 3.75 * sd).ToString("F2");
chart.Axes(Excel.XlAxisType.xlValue).MinimumScale = min;
chart.Axes(Excel.XlAxisType.xlValue).MaximumScale = max;
// Embedding chart on a worksheet
chart.Location(Excel.XlChartLocation.xlLocationAsObject, ws.Name);
// get activate chartobject
chartobject = (Excel.ChartObject)ws.ChartObjects(col - 1);
// 去除网格线
chartobject.Chart.Axes(Excel.XlAxisType.xlValue).HasMajorGridlines = false;
// 位置大小
chartobject.Left = 439;
chartobject.Top = 105 + (col - 2) * 255;
chartobject.Height = 255;
chartobject.Width = 810;
Excel.SeriesCollection se = chartobject.Chart.SeriesCollection();
se.Item(1).XValues = XRng; // 横坐标值
se.Item(1).Format.Line.Weight = 1.2F;
// mean
se.NewSeries();
se.Item(2).Name = "mean";
se.Item(2).Values = meanarray;
se.Item(2).Format.Line.Weight = 1;
se.Item(2).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(2).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbSkyBlue;
// mean + sd
se.NewSeries();
se.Item(3).Name = "mean+sd";
se.Item(3).Values = plusOneSDarray;
se.Item(3).Format.Line.Weight = 1;
se.Item(3).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(3).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlue;
// mean - sd
se.NewSeries();
se.Item(4).Name = "mean-sd";
se.Item(4).Values = minusOneSDarray;
se.Item(4).Format.Line.Weight = 1;
se.Item(4).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(4).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlue;
// mean + 2sd
se.NewSeries();
se.Item(5).Name = "mean+2sd";
se.Item(5).Values = plusTwoSDarray;
se.Item(5).Format.Line.Weight = 1;
se.Item(5).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(5).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbOliveDrab;
// mean - 2sd
se.NewSeries();
se.Item(6).Name = "mean-2sd";
se.Item(6).Values = minusTwoSDarray;
se.Item(6).Format.Line.Weight = 1;
se.Item(6).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(6).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbOliveDrab;
// mean + 3sd
se.NewSeries();
se.Item(7).Name = "mean+3sd";
se.Item(7).Values = plusThreeSDarray;
se.Item(7).Format.Line.Weight = 1;
se.Item(7).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(7).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbIndianRed;
// mean - 3sd
se.NewSeries();
se.Item(8).Name = "mean-3sd";
se.Item(8).Values = minusThreeSDarray;
se.Item(8).Format.Line.Weight = 1;
se.Item(8).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(8).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbIndianRed;
// 修改超过Mean +- 2SD的点的颜色
// 修改超过Mean +- 3SD的点的颜色
double meanplus2sd = mean + 2 * sd;
double meanminus2sd = mean - 2 * sd;
double meanplus3sd = mean + 3 * sd;
double meanminus3sd = mean - 3 * sd;
for (int i = 2; i <= lastrow; i++)
{
Excel.Range rng = (Excel.Range)ws.Cells[i, col];
if (rng.Value == null)
{
continue;
}
if ((double)rng.Value >= meanplus3sd)
{
rng.Interior.Color = 255; // vbred
se.Item(1).Points(i - 1).Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbRed;
}
else if ((double)rng.Value >= meanplus2sd)
{
rng.Interior.Color = 65535; // vbYellow
se.Item(1).Points(i - 1).Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbYellow;
}
else if ((double)rng.Value <= meanminus3sd)
{
rng.Interior.Color = 255;
se.Item(1).Points(i - 1).Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbRed;
}
else if ((double)rng.Value <= meanminus2sd)
{
rng.Interior.Color = 65535;
se.Item(1).Points(i - 1).Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbYellow;
}
}
}
}
public string FastMoveItems(bool FastOrSlowReport, int NumberOfItems, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "")
{
try
{
Excel.Range chartRange;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlApp.Visible = false;
xlApp.SheetsInNewWorkbook = 1;
xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue
string ReportName = (FastOrSlowReport ? "Fast Move Items Report" : "Slow Move Items Report");
DataTable aTable = ReportsMgmt.FastMovItemBasedOnQty(NumberOfItems, DateFrom, DateTo, FastOrSlowReport);
if (aTable.Rows.Count > 0)
{
int RowCnt = 1;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = ReportName;
List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1);
List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1);
for (int i = 0; i < aHeader.Count; i++)
{
string astringss = aHeader[i];
xlWorkSheet.Cells[RowCnt, 2] = aHeader[i];
RowCnt++;
}
xlWorkSheet.Cells[RowCnt++, 1] = ReportName;
xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom;
xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo;
RowCnt++;
xlWorkSheet.Cells[RowCnt, 1] = "Item Description";
xlWorkSheet.Cells[RowCnt, 2] = "Qty Sold";
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true;
//xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
RowCnt++;
int DataStart = RowCnt;
foreach (DataRow aRow in aTable.Rows)
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["ItemDescription"].ToString();
xlWorkSheet.Cells[RowCnt, 2] = aRow["Summation"].ToString();
RowCnt++;
}
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString());
oRng.EntireColumn.AutoFit();
Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
xlWorkSheet.DisplayRightToLeft = false;
int size = aTable.Rows.Count * 100;
if (size >= 600)
{
size = 600;
}
if (size < 300)
{
size = 300;
}
Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300);
Excel.Chart chartPage = myChart.Chart;
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
RowCnt--; //because we started from 1 suppose to be 0
series1.Name = ReportName;
series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()];
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis;
series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format);
if (ExportToExcel)
{
format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7;
xlWorkSheet.SaveAs(ExportPath, format);
}
xlApp.UserControl = false;
return("TRUE");
}
else
{
return("EMPTY");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return("ERROR");
}
finally
{
//Once done close and quit Excel
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
public Form_Color_Matrix()
{
InitializeComponent();
height = int.Parse(textBox_height.Text);
Graphic.RangeData(ref str, ref rows, ref cols);
worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell;
start_col = activecells.Column;
start_row = activecells.Row;
int width = (int)Math.Ceiling(Convert.ToDouble(rows) / Convert.ToDouble(height));
int[,] data = new int[height, width];
int[] temp = new int[width];
// for (int i = 0; i < height; i++)
// {
//temp = new int[width];
// for (int j = 0; j < width; j++)
// {
// // temp[j] = 1;
// data[i, j] = 1;
// ((range)worksheet.Cells[start_row + rows + 1 + i , start_col + j ]).Value2 = 1;
// }
//Sseries = series.NewSeries();
//Sseries.Values = temp;
// }
range c1 = (range)worksheet.Cells[start_row + rows + 1, start_col];
range c2 = (range)worksheet.Cells[start_row + rows + 1 + height - 1, start_col + width - 1];
string ChartOrder = "chart" + Convert.ToString(Nchart);
chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder);
Nchart = Nchart + 1;
//chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns);
chart.ChartType = Excel.XlChartType.xlColumnStacked100;
Excel.ChartGroup group = (Excel.ChartGroup)chart.ChartGroups(1);
group.GapWidth = 0;
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Series Sseries;
Excel.Point point;
int RGB_B, RGB_G, RGB_R;
for (int i = 0; i < height; i++)
{
temp = new int[width];
for (int j = 0; j < width; j++)
{
temp[j] = 1;
data[i, j] = 1;
}
Sseries = series.NewSeries();
Sseries.Values = temp;
}
for (int i = 1; i <= height; i++)
{
Sseries = series.Item(i);
for (int j = 1; j <= width; j++)
{
{
point = (Excel.Point)Sseries.Points(j);
point.Format.Fill.Solid();
point.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
point.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb();
int index = (i - 1) * width + j;
if (index < rows)
{
RGB_B = Convert.ToInt32(double.Parse(str[index, cols - 1])); if (RGB_B < 0)
{
RGB_B = 0;
}
if (RGB_B > 255)
{
RGB_B = 255;
}
RGB_G = Convert.ToInt32(double.Parse(str[index, cols - 2])); if (RGB_G < 0)
{
RGB_G = 0;
}
if (RGB_G > 255)
{
RGB_G = 255;
}
RGB_R = Convert.ToInt32(double.Parse(str[index, cols - 3])); if (RGB_R < 0)
{
RGB_R = 0;
}
if (RGB_R > 255)
{
RGB_R = 255;
}
point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB_B, RGB_G, RGB_R).ToArgb();
point.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb();
}
else
{
point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb();
point.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb();
}
}
}
}
}
public void AddCharts(string reportFile)
{
var excel = new Excel.Application()
{
DisplayAlerts = false
};
excel.Workbooks.Open(reportFile);
Excel._Worksheet workSheet = (Excel.Worksheet)excel.ActiveSheet;
var sheetNumber = 1;
var chartPositionNumber = 2;
foreach (Excel.Worksheet sheet in excel.Worksheets)
{
if (sheet.Name != "Charts")
{
// Set the datasheet for the source of the data
Excel.Worksheet dataSheet = excel.Worksheets[sheetNumber];
// Open the chart sheet to save the charts
workSheet = excel.ActiveWorkbook.Sheets["Charts"];
workSheet.Select();
// Get column & row length
var colomn = dataSheet.UsedRange.Columns.Count;
var rows = dataSheet.UsedRange.Rows.Count - 1;
// Chart settings and stuff
Excel.ChartObjects xlCharts = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing);
Excel.ChartObject runChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart runChartPage = runChart.Chart;
runChartPage.ChartType = Excel.XlChartType.xlLine;
// set ChartStyle based on Office version
var chartStyle = 301;
if (Type.GetTypeFromProgID("Excel.Application.14") != null)
{
chartStyle = 2;
}
runChartPage.HasTitle = true;
runChartPage.HasLegend = true;
runChartPage.ChartTitle.Text = sheet.Name;
runChartPage.ChartStyle = chartStyle;
// Position of chart
var runChartPosition = "B" + chartPositionNumber;
Excel.Range runChartPlacementRange = workSheet.get_Range(runChartPosition, runChartPosition);
runChart.Top = runChartPlacementRange.Top;
runChart.Left = runChartPlacementRange.Left;
chartPositionNumber = chartPositionNumber + 21;
// Size of Chart
runChart.Width = 500;
runChart.Height = 250;
Excel.SeriesCollection runSeriesCollection = runChartPage.SeriesCollection();
// Create run line chart
for (int i = 2; i <= (colomn); i++)
{
Excel.Series runSeries = runSeriesCollection.NewSeries();
runSeries.Name = dataSheet.Cells[1, i].Value;
// set correct range for chart data
var ia = i;
// Time range
var xValuesBegin = ParseColumnName(1) + "2";
var xValuesEnd = ParseColumnName(1) + (rows.ToString());
var valuesBegin = ParseColumnName(ia) + (2).ToString();
var valuesEnd = ParseColumnName(ia) + (rows + 1).ToString();
runSeries.XValues = dataSheet.get_Range(xValuesBegin, xValuesEnd);
runSeries.Values = dataSheet.get_Range(valuesBegin, valuesEnd);
}
}
sheetNumber++;
}
var error = new ErrorHandler();
try
{
// Save excel sheet
workSheet.SaveAs(reportFile);
}
catch (Exception)
{
excel.Quit();
error.Exit(93);
}
// Close sheet
excel.Quit();
}
