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(); }