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; }
/// <summary> /// 确定Chart的宽度值,以磅为单位 /// </summary> /// <param name="DrawingChart">进行绘图的Chart</param> /// <param name="PointsCount">选择的基坑区域的数量</param> /// <param name="LeastWidth_Chart">Chart对象的宽度的最小值,即使图表中只有很少的点</param> /// <param name="LeastWidth_Column">柱形图中每一个柱形的最小宽度,用来进行基本的文本书写</param> /// <returns></returns> /// <remarks></remarks> private double GetChartWidth(Microsoft.Office.Interop.Excel.Chart DrawingChart, UInt16 PointsCount, double LeastWidth_Chart, double LeastWidth_Column, double Margin, ref double InsideWidth) { double ChartWidth = LeastWidth_Chart; InsideWidth = LeastWidth_Chart - Margin; // Microsoft.Office.Interop.Excel.Chart with_1 = DrawingChart; Microsoft.Office.Interop.Excel.SeriesCollection src = with_1.SeriesCollection(); UInt16 n = src.Count; double hh = LeastWidth_Chart / PointsCount; ChartGroup ChtGroup = with_1.ChartGroups(1); // ------------------------------------------------------------------------------------------------ //1、在已知最小的chart宽度(即最小的PlotArea.InsideWidth)的情况下,验算柱形的宽度是否比指定的最小柱形宽度要大 double H = InsideWidth; // Dim O As Single = ChtGroup.Overlap / 100 ChtGroup.Overlap = 0; float G = (float)((double)ChtGroup.GapWidth / 100); double ColumnWidth = hh / (1 + G + n); if (ColumnWidth < LeastWidth_Column) { // ------------------------------------------------------------------------------------------------ //2、在已知柱体的最小宽度的情况下,去推算整个PlotArea.InsideWidth的值 } return(ChartWidth); }
protected void CreateGraph(int columnIndex) { // Get the alphabetical column index... string columnAlphabetical = ((char)(columnIndex + 64)).ToString( ); // Activate the test Chart worksheet... this.testChartWorkSheet.Activate( ); // Get any charts currently existing in the worksheet... Excel.ChartObjects charts = (Excel.ChartObjects) this.testChartWorkSheet.ChartObjects(Type.Missing); // Adds a chart at x = 100, y = 300, 500 points wide and 300 tall. Excel.ChartObject chartObj = (Excel.ChartObject)charts.Item(columnIndex - 1); Excel.Chart chart = chartObj.Chart; // Gets the data cells ( X values ) Excel.Range chartRange = this.testWorkSheet.get_Range((columnAlphabetical + "2"), (columnAlphabetical + (rows).ToString( ))); chart.SetSourceData(chartRange, Type.Missing); // Set the type of the chart... chart.ChartType = Excel.XlChartType.xlLine; // Get any existing series collection (there should be only one)... Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.Item(seriesCollection.Count); // Gets the data cells ( Y values ) series.XValues = this.testWorkSheet.get_Range("A2", "A" + (rows).ToString( )); }
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; }
void elementChart_MouseDown(int Button, int Shift, int x, int y) { if (flag == 1) { Excel.Chart chart = Globals.ThisAddIn.Application.ActiveChart; //Int32 ; Int32 elementID = 0; Int32 arg1 = 0; Int32 arg2 = 0; chart.GetChartElement(x, y, ref elementID, ref arg1, ref arg2); // string element = ((Excel.XlChartItem)elementID).ToString(); if (element == "xlSeries") { Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries = series.Item(arg1); Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(SelectRGB[2], SelectRGB[1], SelectRGB[0]).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(SelectRGB[2], SelectRGB[1], SelectRGB[0]).ToArgb(); flag = 0; } } // MessageBox.Show("Chart element is: " + ((Excel.XlChartItem)elementID).ToString() // + "\n arg1 is: " + arg1.ToString() + "\n arg2 is: " + arg2.ToString()); }
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(); }
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]); }
/// <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); }
public void ExcelDiagr2() { Excel.Application excelapp = new Excel.Application(); try { // excelapp.Visible = true; Excel.Workbooks excelappworkbooks = excelapp.Workbooks; Excel.Workbook excelappworkbook = excelapp.Workbooks.Open("book.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); Excel.Sheets excelsheets = excelappworkbook.Worksheets; //excelappworkbook = excelappworkbooks[2]; //excelappworkbook.Activate(); //excelsheets = excelapp.ActiveWorkbook.Worksheets; Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); excelworksheet.Activate(); //Excel.Worksheet excelworksheet = (Excel.Worksheet)excelappworkbook.Sheets[2]; Excel.Range excelcells = excelworksheet.get_Range("B1", "B12"); excelcells.Select(); Excel.Chart excelchart = (Excel.Chart)excelapp.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); excelapp.ActiveChart.HasTitle = true; excelapp.ActiveChart.ChartTitle.Text = "Количество заказов"; DataForBD.iter = 5; ((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.xlValue, Excel.XlAxisGroup.xlPrimary)).HasTitle = true; ((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Количество"; Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)excelapp.ActiveChart.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.Item(1); series.Name = "Количество заказов"; //excelworksheet.Name = "Количество заказов"; excelapp.DisplayAlerts = false; excelappworkbook.SaveAs("book.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); excelapp.Quit(); } catch (Exception exc) { excelapp.Quit(); throw exc; } }
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); }
private void button1_Click(object sender, EventArgs e) { colorDialog1.ShowDialog(); RGB0 = colorDialog1.Color; button_ColorSelection.BackColor = RGB0; button_ColorSelection.ForeColor = RGB0; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Point point; Excel.Series Sseries; int SquareSize; //RGB0 = System.Drawing.Color.FromArgb(255, 109, 118, 248); for (int i = 1; i < rows; i++) { Sseries = series.Item(i); Sseries.Format.Line.Visible = Office.MsoTriState.msoFalse; Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle; Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb(); Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb(); Sseries.Format.Fill.Solid(); Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue; Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb(); Sseries.Format.Fill.Transparency = 0.2F; for (int j = 1; j < cols; j++) { point = (Excel.Point)Sseries.Points(j); //SquareSize = (int)(double.Parse(str[i, j]) / Max_size * ratio) + 2; SquareSize = (int)((Math.Sqrt(double.Parse(str[i, j]) * ratio) / Max_size) * Min_MarkerSize + 2); //SquareSize = (int)((double.Parse(str[i, j]) / Max_size * ratio) + 2);// - Min_size) / (Max_size - Min_size) * ratio) + 2; if (SquareSize > 72) { SquareSize = 72; } point.MarkerSize = SquareSize; } } chart.Refresh(); worksheet.Activate(); }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } Hrange = double.Parse(textBox_Bandwidth.Text); if (Hrange == 0) { return; } Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries = series.Item(1); Excel.Point point; System.Drawing.Color RGB1 = System.Drawing.Color.FromArgb(255, 96, 157, 202); int[] HSV0 = new int[3]; Graphic.RGB2HSV(RGB0, ref HSV0); int[] HSV = new int[3]; HSV0.CopyTo(HSV, 0); double ratio; for (int i = 1; i < rows; i++) { point = (Excel.Point)Sseries.Points(i); 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(); ratio = (double.Parse(str[i, 2]) - Min_Value) / (Max_Value - Min_Value); HSV[2] = HSV0[2] + Convert.ToInt32(Hrange * (ratio - 0.5)); Graphic.HSV2RGB(ref RGB1, HSV); point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB1.B, RGB1.G, RGB1.R).ToArgb(); point.Format.Fill.Transparency = 0.0F; } chart.Refresh(); worksheet.Activate(); }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } ratio = double.Parse(textBox_Bandwidth.Text);; if (ratio == 0) { return; } Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries = series.Item(1); Excel.Point point; int SquareSize; for (int i = 1; i < rows; i++) { point = (Excel.Point)Sseries.Points(i); //SquareSize = (int)((Math.Sqrt(double.Parse(str[i, 2]))));// - Min_size) / (Max_size - Min_size) * ratio) + 2; SquareSize = (int)((Math.Sqrt(double.Parse(str[i, 2])) / Max_size * ratio) + 2); //point = (Excel.Point)Sseries.Points(i); //SquareSize = (int)((double.Parse(str[i, 2]) - Min_size) / (Max_size - Min_size) * ratio) + 2; //PointSize[i - 1, 0] = SquareSize; //((Excel.Range)worksheet.Cells[Graphic.start_row + i, Graphic.start_col + 3]).Value2 = SquareSize; point.MarkerSize = SquareSize; } Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.Format.Fill.Solid(); Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue; Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb(); Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb(); Sseries.Format.Fill.Transparency = 0.3F; chart.Refresh(); worksheet.Activate(); }
/// <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> /// 开始生成整个绘图图表 /// </summary> /// <param name="SelectedRegion">窗口中所有选择的要进行绘图的基坑区域</param> /// <remarks></remarks> private void GenerateChart(List <clsData_ProcessRegionData> SelectedRegion) { //列表中选择的基坑区域 int count = System.Convert.ToInt32(SelectedRegion.Count); if (count > 0) { //---------------- 打开用于绘图的Excel程序,并进行界面设计 Microsoft.Office.Interop.Excel.Worksheet DrawingSheet = this.GetDrawingSheet(); Microsoft.Office.Interop.Excel.Application DrawingApp = DrawingSheet.Application; try { //------------------- 在绘图工作表中进行绘图 this.F_DrawingChart = DrawChart(DrawingSheet, SelectedRegion); // ----------- 绘制数据系列图 --------------------------- Microsoft.Office.Interop.Excel.SeriesCollection src = this.F_DrawingChart.SeriesCollection(); Series series_DeepestExca = src.Item(1); Series series_Depth = src.Item(2); Series[] DataSeries = new Series[2]; DataSeries = SetDataSeries(this.F_DrawingChart, series_DeepestExca, series_Depth, SelectedRegion); //------------------------------------------------------- DateSpan date_Span = GetDateSpan(SelectedRegion); //------------------------------------------------------- ClsDrawing_ExcavationElevation shtEle = new ClsDrawing_ExcavationElevation(series_DeepestExca, series_Depth, SelectedRegion, date_Span, this.F_Textbox_Info, DrawingType.Xls_SectionalView); } catch (Exception ex) { MessageBox.Show("绘制基坑区域开挖标高图失败!" + "\r\n" + ex.Message + "\r\n" + "报错位置:" + ex.TargetSite.Name, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { //------- Excel的界面美化 -------------------- ExcelAppBeauty(DrawingApp); } } }
/// <summary> /// 构造函数 /// </summary> /// <param name="DataSheet">图表对应的数据工作表</param> /// <param name="DrawingChart">Excel图形所在的Chart对象</param> /// <param name="ParentApp">此图表所在的Excel类的实例对象</param> /// <param name="type">此图表所属的类型,由枚举drawingtype提供</param> /// <param name="CanRoll">是图表是否可以滚动,即是动态图还是静态图</param> /// <param name="DateSpan">此图表的TimeSpan跨度</param> /// <remarks></remarks> public clsDrawing_Mnt_RollingBase(Excel.Worksheet DataSheet, Excel.Chart DrawingChart, Cls_ExcelForMonitorDrawing ParentApp, DrawingType type, bool CanRoll, Excel.TextFrame2 Info, MonitorInfo DrawingTag, MntType MonitorType, DateSpan DateSpan, SeriesTag theFirstSeriesTag) : base(DataSheet, DrawingChart, ParentApp, type, CanRoll, Info, DrawingTag, MonitorType) { // VBConversions Note: Non-static class variable initialization is below. Class variables cannot be initially assigned non-static values in C#. F_Chart = this.Chart; // this.DateSpan = DateSpan; //刷新滚动窗口的列表框的界面显示 APPLICATION_MAINFORM.MainForm.Form_Rolling.OnRollingDrawingsRefreshed(); //启用主界面的程序滚动按钮 APPLICATION_MAINFORM.MainForm.MainUI_RollingObjectCreated(); //--------------------------- 设置与数据系列的曲线相关的属性值 clsDrawing_Mnt_RollingBase with_1 = this; //以数据列中第一个元素作为进行滚动的那个series with_1.MovingSeries = theFirstSeriesTag.series; // ----- 集合数据的记录 with_1.F_DicSeries_Tag.Add(cst_LboundOfSeriesInCollection, theFirstSeriesTag); //刚开始时,图表中只有一条数据曲线 with_1.F_CurvesCount = 1; // this.F_List_HasCurve.Clear(); this.F_List_HasCurve.Add(true); //第一个数据列是有曲线的,所以将其值设置为true Excel.SeriesCollection seriesColl = Chart.SeriesCollection() as Excel.SeriesCollection; for (var i = 1; i <= seriesColl.Count - 1; i++) { this.F_List_HasCurve.Add(false); } // -----对图例进行更新--------- LegendRefresh(F_List_HasCurve); }
/// <summary> /// Initialize Excel Application /// </summary> /// <param name="sheetname">Desired Sheetname</param> /// <param name="visible">Determine whether Excel-Sheet is visible or not</param> public static void InitializeExcel(string sheetname, bool visible = true) { // Create a new Excel Application _app = new Excel.Application(); _app.Visible = visible; // Create a new, empty workbook and add it to the collection returned _workbook = _app.Workbooks.Add(Missing.Value); _workbook.Worksheets.Item[1].Name = sheetname; // Initialize _mainChart. var worksheet = _workbook.Worksheets.Item[1] as Microsoft.Office.Interop.Excel.Worksheet; var charts = worksheet.ChartObjects() as Microsoft.Office.Interop.Excel.ChartObjects; var chartObject = charts.Add(10, 10, 800, 450) as Microsoft.Office.Interop.Excel.ChartObject; _mainChart = chartObject.Chart; _mainChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLinesNoMarkers; _mainChart.ChartWizard(Title: sheetname); _mainChartSeriesCollection = _mainChart.SeriesCollection(); }
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(); }
/// <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; }
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; } }
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; } } } }
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; }
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(); }
public ColumnFrequency() { InitializeComponent(); //hScrollBar_Parameter1.Value = 2; //Graphics Graphic = new Graphics(); Bandwidth = double.Parse(textBox_Bandwidth.Text); //if (Form_Parater.flag == 0) return; //int rows = 1; //int cols = 1; //string[,] str = new string[1, 1]; 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; Max_Value = Double.MinValue; Min_Value = Double.MaxValue; for (int i = 1; i < rows; i++) { if (double.Parse(str[i, 0]) > Max_Value) { Max_Value = double.Parse(str[i, 0]); } if (double.Parse(str[i, 0]) < Min_Value) { Min_Value = double.Parse(str[i, 0]); } } int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Bandwidth + 2); double[,] data = new double[Nrows, 1]; int idx = 0; for (int i = 1; i < rows; i++) { idx = Convert.ToInt32((double.Parse(str[i, 0]) - Min_Value) / Bandwidth); data[idx, 0] = data[idx, 0] + 1; } data[Nrows - 2, 0] = data[Nrows - 2, 0] + data[Nrows - 1, 0]; double[,] x = new double[Nrows - 1, 1]; string[,] label = new string[Nrows - 1, 1]; for (int i = 0; i < Nrows - 1; i++) { label[i, 0] = "[" + Convert.ToString(Math.Floor((i * Bandwidth + Min_Value) * 100) / 100) + "," + Convert.ToString(Math.Floor(((i + 1) * Bandwidth + Min_Value) * 100) / 100) + ")"; //((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 1]).Value2 = label[i]; //((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 3]).Value2 = data[i]; x[i, 0] = i * Bandwidth + Min_Value; } ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X bandwidth"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "X axis"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 3]).Value2 = "Y axis"; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 1]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = label; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2]; range = worksheet.get_Range(c1, c2); range.Value = x; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]; range = worksheet.get_Range(c1, c2); range.Value = data; ((Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]).Value2 = "";; string ChartOrder = "FrequencyColumn" + Convert.ToString(Nchart); chart = worksheet.Controls.AddChart(300, 50, 450, 400, ChartOrder); Nchart = Nchart + 1; c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3]; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); chart.ChartType = Excel.XlChartType.xlColumnClustered; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); if (series.Count == 2) { Excel.Series Sseries2 = series.Item(2); Sseries2.Delete(); } Excel.Series Sseries = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2]; Sseries.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3]; Sseries.Values = worksheet.get_Range(c1, c2); Sseries.Format.Fill.Visible = Office.MsoTriState.msoTrue; Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 88, 180, 13).ToArgb(); Sseries.Format.Line.Visible = Office.MsoTriState.msoTrue; Sseries.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb(); Excel.ChartGroup group = (Excel.ChartGroup)chart.ChartGroups(1); group.GapWidth = 0; chart.HasLegend = false; chart.HasTitle = false; worksheet.Activate(); }
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e) { if (textBox_Bandwidth.Text == "") { return; } Bandwidth = double.Parse(textBox_Bandwidth.Text);; if (Bandwidth == 0) { return; } int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Bandwidth + 2); double[,] data = new double[Nrows, 1]; int idx = 0; for (int i = 1; i < rows; i++) { idx = Convert.ToInt32((double.Parse(str[i, 0]) - Min_Value) / Bandwidth); data[idx, 0] = data[idx, 0] + 1; } data[Nrows - 2, 0] = data[Nrows - 2, 0] + data[Nrows - 1, 0]; double[,] x = new double[Nrows - 1, 1]; string[,] label = new string[Nrows - 1, 1]; for (int i = 0; i < Nrows - 1; i++) { label[i, 0] = "[" + Convert.ToString(Math.Floor((i * Bandwidth + Min_Value) * 100) / 100) + "," + Convert.ToString(Math.Floor(((i + 1) * Bandwidth + Min_Value) * 100) / 100) + ")"; //((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 1]).Value2 = label[i]; //((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 3]).Value2 = data[i]; x[i, 0] = i * Bandwidth + Min_Value; } ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X bandwidth"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "X axis"; ((Excel.Range)worksheet.Cells[start_row, start_col + cols + 3]).Value2 = "Y axis"; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 1]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = label; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2]; range = worksheet.get_Range(c1, c2); range.Value = x; c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]; range = worksheet.get_Range(c1, c2); range.Value = data; ((Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]).Delete();; c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3]; chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns); Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries2 = series.Item(2); Sseries2.Delete(); Excel.Series Sseries = series.Item(1); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2]; Sseries.XValues = worksheet.get_Range(c1, c2); c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3]; c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3]; Sseries.Values = worksheet.get_Range(c1, c2); chart.Refresh(); worksheet.Activate(); }
// データテーブルをエクセルへエクスポート(XR管理図・ヒストグラム付き) public void ExportToExcelWithXrChart(DataTable dt) { if (dt.Rows.Count == 0) { return; } 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 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // column headings for (int i = 0; i < dt.Columns.Count; i++) { xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName; } // rows for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j]; } } int row = xlWorkSheet.UsedRange.Rows.Count; string address1 = "B1:B" + row.ToString() + ",K1:K" + row.ToString() + ",M1:N" + row.ToString(); string address2 = "B1:B" + row.ToString() + ",L1:L" + row.ToString(); string address3 = "AB41:AC53"; string address4 = "F1:J" + row.ToString(); // チャート1(X-R管理図1) Excel.Range chartRange1; Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250); Excel.Chart chartPage1 = myChart1.Chart; chartRange1 = xlWorkSheet.get_Range(address1); chartPage1.SetSourceData(chartRange1, misValue); chartPage1.ChartType = Excel.XlChartType.xlLine; chartPage1.HasLegend = false; chartPage1.HasTitle = true; chartPage1.ChartTitle.Text = "X " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale; Excel.SeriesCollection SeriesCollection1 = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue); Excel.Series s2 = SeriesCollection1.Item(2); s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral; Excel.Series s3 = SeriesCollection1.Item(3); s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral; // チャート2(X-R管理図2) Excel.Range chartRange2; Excel.ChartObjects xlCharts2 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart2 = (Excel.ChartObject)xlCharts1.Add(800, 280, 600, 250); Excel.Chart chartPage2 = myChart2.Chart; chartRange2 = xlWorkSheet.get_Range(address2); chartPage2.SetSourceData(chartRange2, misValue); chartPage2.ChartType = Excel.XlChartType.xlLine; chartPage2.HasLegend = false; chartPage2.HasTitle = true; chartPage2.ChartTitle.Text = "R " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); Excel.Axis xAxis2 = (Excel.Axis)chartPage2.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis2.CategoryType = Excel.XlCategoryType.xlCategoryScale; // チャート3(ヒストグラム) Excel.Range chartRange3; Excel.ChartObjects xlCharts3 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart3 = (Excel.ChartObject)xlCharts1.Add(800, 550, 350, 250); Excel.Chart chartPage3 = myChart3.Chart; string[,] formulas = new string[13, 3]; string[] formula1 = new string[] { "BIN", "=MIN(" + address4 + ")", "=AA42+(AA$53-AA$42)/10", "=AA43+(AA$53-AA$42)/10", "=AA44+(AA$53-AA$42)/10", "=AA45+(AA$53-AA$42)/10", "=AA46+(AA$53-AA$42)/10", "=AA47+(AA$53-AA$42)/10", "=AA48+(AA$53-AA$42)/10", "=AA49+(AA$53-AA$42)/10", "=AA50+(AA$53-AA$42)/10", "=AA51+(AA$53-AA$42)/10", "=MAX(" + address4 + ")", }; string[] formula2 = new string[] { @"LABEL", @"=TEXT(AA42,""0.0"")", @"=TEXT(AA42,""0.0"")&"" - ""&TEXT(AA43,""0.0"")", @"=TEXT(AA43,""0.0"")&"" - ""&TEXT(AA44,""0.0"")", @"=TEXT(AA44,""0.0"")&"" - ""&TEXT(AA45,""0.0"")", @"=TEXT(AA45,""0.0"")&"" - ""&TEXT(AA46,""0.0"")", @"=TEXT(AA46,""0.0"")&"" - ""&TEXT(AA47,""0.0"")", @"=TEXT(AA47,""0.0"")&"" - ""&TEXT(AA48,""0.0"")", @"=TEXT(AA48,""0.0"")&"" - ""&TEXT(AA49,""0.0"")", @"=TEXT(AA49,""0.0"")&"" - ""&TEXT(AA50,""0.0"")", @"=TEXT(AA50,""0.0"")&"" - ""&TEXT(AA51,""0.0"")", @"=TEXT(AA51,""0.0"")&"" - ""&TEXT(AA52,""0.0"")", @"=TEXT(AA53,""0.0"")" }; string[] formula3 = new string[] { @"FREQUENCY", @"=COUNTIF(" + address4 + @",""<=""&AA42)", @"=COUNTIF(" + address4 + @","">""&AA42)-COUNTIF(" + address4 + @","">""&AA43)", @"=COUNTIF(" + address4 + @","">""&AA43)-COUNTIF(" + address4 + @","">""&AA44)", @"=COUNTIF(" + address4 + @","">""&AA44)-COUNTIF(" + address4 + @","">""&AA45)", @"=COUNTIF(" + address4 + @","">""&AA45)-COUNTIF(" + address4 + @","">""&AA46)", @"=COUNTIF(" + address4 + @","">""&AA46)-COUNTIF(" + address4 + @","">""&AA47)", @"=COUNTIF(" + address4 + @","">""&AA47)-COUNTIF(" + address4 + @","">""&AA48)", @"=COUNTIF(" + address4 + @","">""&AA48)-COUNTIF(" + address4 + @","">""&AA49)", @"=COUNTIF(" + address4 + @","">""&AA49)-COUNTIF(" + address4 + @","">""&AA50)", @"=COUNTIF(" + address4 + @","">""&AA50)-COUNTIF(" + address4 + @","">""&AA51)", @"=COUNTIF(" + address4 + @","">""&AA51)-COUNTIF(" + address4 + @","">=""&AA52)", @"=COUNTIF(" + address4 + @","">=""&AA53)" }; for (int i = 0; i < 13; i++) { xlWorkSheet.Cells[41 + i, 27].Formula = formula1[i]; xlWorkSheet.Cells[41 + i, 28].Formula = formula2[i]; xlWorkSheet.Cells[41 + i, 29].Formula = formula3[i]; } chartRange3 = xlWorkSheet.get_Range(address3); chartPage3.SetSourceData(chartRange3, misValue); chartPage3.ChartType = Excel.XlChartType.xlColumnClustered; chartPage3.HasLegend = false; chartPage3.HasTitle = true; chartPage3.ChartTitle.Text = "Frequency " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); Excel.ChartGroup ChartGroup1 = (Excel.ChartGroup)myChart3.Chart.ChartGroups(1); ChartGroup1.GapWidth = 0; xlApp.Visible = true; }
// データテーブルをエクセルへエクスポート(箱ヒゲ図付き) public void ExportToExcelWithBoxPlotChart(DataTable dt) { if (dt.Rows.Count == 0) { return; } 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 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // column headings for (int i = 0; i < dt.Columns.Count; i++) { xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName; } // rows for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j]; } } int row = xlWorkSheet.UsedRange.Rows.Count; string address1 = "C1:C" + row.ToString() + ",H1:L" + row.ToString(); string address2 = "M2:M" + row.ToString(); string address3 = "N2:N" + row.ToString(); Excel.Range chartRange1; Excel.Range chartRange2; Excel.Range chartRange3; Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250); Excel.Chart chartPage1 = myChart1.Chart; chartRange1 = xlWorkSheet.get_Range(address1); chartRange2 = xlWorkSheet.get_Range(address2); chartRange3 = xlWorkSheet.get_Range(address3); chartPage1.SetSourceData(chartRange1, misValue); chartPage1.ChartType = Excel.XlChartType.xlColumnStacked; chartPage1.HasTitle = true; chartPage1.ChartTitle.Text = dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); chartPage1.HasLegend = false; Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale; Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue); Excel.Series s5 = oSeriesCollection.Item(5); s5.ChartType = Excel.XlChartType.xlLine; s5.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid; Excel.Series s4 = oSeriesCollection.Item(4); s4.ChartType = Excel.XlChartType.xlLine; s4.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid; Excel.Series s1 = oSeriesCollection.Item(1); s1.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbWhite; s1.Format.Fill.Transparency = 1; s1.Format.Line.Weight = 0; s1.HasErrorBars = true; s1.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludeMinusValues, Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange2, chartRange2); Excel.Series s3 = oSeriesCollection.Item(3); s3.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua; s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack; s3.Format.Line.Weight = 1.0F; s3.HasErrorBars = true; s3.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludePlusValues, Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange3, chartRange3); Excel.Series s2 = oSeriesCollection.Item(2); s2.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua; s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack; s2.Format.Line.Weight = 1.0F; xlApp.Visible = true; }
public CurveLOESS() { InitializeComponent(); //int rows = 1; //int cols = 1; //string[,] str = new string[1, 1]; 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; Y = new double[rows - 1]; X = new double[rows - 1]; int j; for (j = 1; j < rows; j++) { X[j - 1] = double.Parse(str[j, 0]); Y[j - 1] = double.Parse(str[j, 1]); } Span = long.Parse(textBox_Bandwidth.Text); double[] yLoess = new double[rows - 1]; QLOESS(ref Y, ref X, ref yLoess, Span); double[,] data = new double[rows - 1, 1]; for (j = 1; j < rows; j++) { data[j - 1, 0] = yLoess[j - 1]; } ((Excel.Range)worksheet.Cells[start_row, start_col + cols]).Value2 = "Smooth Y"; Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols]; Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + 1 + rows - 2, start_col + cols]; Excel.Range range = worksheet.get_Range(c1, c2); range.Value = data; c1 = (Excel.Range)worksheet.Cells[start_row, start_col]; c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols]; string ChartOrder = "CurveLOESS" + 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.xlXYScatter; Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection(); Excel.Series Sseries2 = series.Item(2); Sseries2.Format.Line.Visible = Office.MsoTriState.msoFalse; Sseries2.Format.Fill.Solid(); Sseries2.Format.Fill.Visible = Office.MsoTriState.msoCTrue; //Sseries2.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb(); //Sseries2.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255,229, 184,0).ToArgb(); //Sseries2.Format.Fill.Transparency = 0.6F; Sseries2.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle; Sseries2.MarkerSize = 6; Excel.Series Sseries3 = series.Item(3); Sseries3.Format.Fill.Visible = Office.MsoTriState.msoFalse; Sseries3.Format.Line.Visible = Office.MsoTriState.msoTrue; Sseries3.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone; //Sseries3.Format.Line.ForeColor.RGB= System.Drawing.Color.FromArgb(255, 248, 118, 109).ToArgb(); Sseries3.Format.Line.Weight = 1.25F; Excel.Series Sseries1 = series.Item(1); Sseries1.Delete(); }