private void builWorkBook(Xls.Workbook workBook, IEnumerable <ChartInfo> graphs) { Xls.Chart graphTemplate = workBook.Sheets[1]; Xls.Worksheet dataSheet = workBook.Sheets[2]; int chartsCounter = 1; int currentDataColumn = 1; try { foreach (var graph in graphs) { int dataColumnsWritten; graphTemplate.Copy(dataSheet); workBook.Sheets[workBook.Sheets.Count - 1].Name = string.Format(Strings.ExcelChartSheetName, chartsCounter++); buildXlsChart(workBook.Sheets[workBook.Sheets.Count - 1], dataSheet, graph, currentDataColumn, out dataColumnsWritten); currentDataColumn += dataColumnsWritten; } graphTemplate.Delete(); } finally { if (dataSheet != null) { Marshal.FinalReleaseComObject(dataSheet); } Marshal.FinalReleaseComObject(graphTemplate); } }
/// <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); }
public static void CreateChart( ref Excel.Worksheet page, ExcelChartInfo excelCI) { try { var xlCharts = page.ChartObjects() as Excel.ChartObjects; Excel.ChartObject myChart = xlCharts.Add(excelCI.m_Left, excelCI.m_Top, excelCI.m_Width, excelCI.m_Height); Excel.Chart chartPage = myChart.Chart; var chartRange = page.get_Range(excelCI.m_NameLRange, excelCI.m_NameHRange); var chartRange2 = page.get_Range(excelCI.m_SourceLRange, excelCI.m_SourceHRange); chartPage.SetSourceData(chartRange); chartPage.ChartType = excelCI.m_ChartType; //xlLine || xlColumnClustered chartPage.ChartWizard( Source: chartRange2, Title: excelCI.m_Title, CategoryTitle: excelCI.m_CategoryTitle, ValueTitle: excelCI.m_ValueTitle); } catch (System.Runtime.InteropServices.COMException ex) { MessageBox.Show(ex.Message); } }
/// <summary> /// 构造函数 /// </summary> /// <param name="DataSheet">图表对应的数据工作表</param> /// <param name="DrawingChart">Excel图形所在的Chart对象</param> /// <param name="ParentApp">此图表所在的Excel类的实例对象</param> /// <param name="DateSpan">此图表的TimeSpan跨度</param> /// <param name="type">此图表的类型,则枚举DrawingType提供</param> /// <param name="CanRoll">是图表是否可以滚动,即是动态图还是静态图</param> /// <param name="date_ColNum">此测斜点的监测数据工作表中的每一天与其在工作表中对应的列号, /// 以监测数据的日期key索引数据所在列号item</param> /// <param name="usedRg">监测曲线的数据范围,此Range对象中, /// 包括此工作表的UsedRange中的第一列,即深度的数据;但是不包括第一行的日期数据</param> /// <param name="Info">记录数据信息的文本框</param> /// <param name="FirstSeriesTag">第一条数据系列对应的Tag信息</param> /// <param name="ProcessRegionData">在施工进度工作表中,每一个基坑区域相关的各种信息,比如区域名称,区域的描述, /// 区域数据的Range对象,区域所属的基坑ID及其ID的数据等</param> /// <remarks></remarks> public ClsDrawing_Mnt_Incline(Excel.Worksheet DataSheet, Excel.Chart DrawingChart, Cls_ExcelForMonitorDrawing ParentApp, DateSpan DateSpan, DrawingType type, bool CanRoll, TextFrame2 Info, MonitorInfo DrawingTag, MntType MonitorType, Dictionary <DateTime, int> date_ColNum, Excel.Range usedRg, SeriesTag_Incline FirstSeriesTag, clsData_ProcessRegionData ProcessRegionData = null) : base(DataSheet, DrawingChart, ParentApp, type, CanRoll, Info, DrawingTag, MonitorType, DateSpan, new clsDrawing_Mnt_RollingBase.SeriesTag(FirstSeriesTag.series, FirstSeriesTag.ConstructionDate)) { // // -------------------------------------------- try { ClsDrawing_Mnt_Incline with_1 = this; with_1.P_rgMntData = usedRg; //'包括第一列,但是不包括第一行的日期。 Excel.Range colRange = usedRg.Columns[1] as Excel.Range; with_1.F_YValues = ExcelFunction.ConvertRangeDataToVector <Single>(colRange); with_1.Information = Info; with_1._ExcavationDepth_lineAndTextbox = FirstSeriesTag; with_1.F_dicDateAndColumnNumber = date_ColNum; with_1.P_ProcessRegionData = ProcessRegionData; with_1._inclineTopElevaion = Project_Expo.InclineTopElevaion; // ----- 集合数据的记录 with_1.F_DicSeries_Tag[(int)LowIndexOfObjectsInExcel.SeriesInSeriesCollection] = FirstSeriesTag; // -----对图例进行更新--------- //Call LegendRefresh(List_HasCurve) } catch (Exception ex) { MessageBox.Show("构造测斜曲线图出错。" + "\r\n" + ex.Message + "\r\n" + ex.TargetSite.Name, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
static void Main(string[] excel) { Excel.Application imgconverter = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Microsoft.Office.Interop.Excel.Application; ConsoleColor c = Console.ForegroundColor; Console.ForegroundColor = ConsoleColor.Red; Console.Write("Export To: "); Console.ForegroundColor = c; string exportPath = Console.ReadLine(); if (exportPath == "") { exportPath = EXPORT_TO_DIRECTORY; } Excel.Workbook wb = imgconverter.ActiveWorkbook; foreach (Excel.Worksheet ws in wb.Worksheets) { Excel.ChartObjects chartobjects = (Excel.ChartObjects)(ws.ChartObjects(Type.Missing)); foreach (Excel.ChartObject co in chartobjects) { co.Select(); Excel.Chart chart = co.Chart; chart.Export(exportPath + chart.Name + ".png", "PNG", false); } } Process.Start(exportPath); }
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 AddBugChart( ExcelInterop.Worksheet sheet, int chartStartRow, string chartStartCol, int chartEndRow, string chartEndCol, string bugDataSource, string chartTitle) { ExcelInterop.Range bugChartRange = sheet.Range[sheet.Cells[chartStartRow, chartStartCol], sheet.Cells[chartEndRow, chartEndCol]]; ExcelInterop.ChartObjects charts = sheet.ChartObjects(Type.Missing) as ExcelInterop.ChartObjects; Utility.AddNativieResource(charts); ExcelInterop.ChartObject bugChartObject = charts.Add(0, 0, bugChartRange.Width, bugChartRange.Height); Utility.AddNativieResource(bugChartObject); ExcelInterop.Chart bugChart = bugChartObject.Chart;//设置图表数据区域。 Utility.AddNativieResource(bugChart); ExcelInterop.Range datasource = sheet.get_Range(bugDataSource);//不是:"B14:B25","F14:F25" Utility.AddNativieResource(datasource); bugChart.SetSourceData(datasource); bugChart.ChartType = ExcelInterop.XlChartType.xlColumnClustered; //bugChart.ChartWizard(datasource, XlChartType.xlColumnClustered, Type.Missing, XlRowCol.xlColumns, 1, 1, false, chartTitle, "", "", Type.Missing); bugChart.ApplyDataLabels();//图形上面显示具体的值 //将图表移到数据区域之下。 bugChartObject.Left = Convert.ToDouble(bugChartRange.Left) + 20; bugChartObject.Top = Convert.ToDouble(bugChartRange.Top) + 20; bugChartObject.Locked = false; bugChartObject.Select(); bugChartObject.Activate(); }
/// <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="Info">图表中用来显示相关信息的那个文本框对象</param> /// <param name="DrawingTag">每一个监测曲线图的相关信息</param> /// <param name="MonitorType">监测数据的类型,比如测斜数据、立柱垂直位移数据、支撑轴力数据等</param> /// <remarks></remarks> public ClsDrawing_Mnt_Base(Excel.Worksheet DataSheet, Microsoft.Office.Interop.Excel.Chart DrawingChart, Cls_ExcelForMonitorDrawing ParentApp, DrawingType type, bool CanRoll, Excel.TextFrame2 Info, MonitorInfo DrawingTag, MntType MonitorType) { try { //设置Excel窗口与Chart的尺寸 this.F_Application = ParentApp.Application; ExcelFunction.SetLocation_Size(this.ChartSize_sugested, DrawingChart, this.Application, true); // this.Sheet_Data = DataSheet; this.F_myChart = DrawingChart; this.F_textbox_Info = Info; this.Sheet_Drawing = DrawingChart.Parent.Parent; this.F_blnCanRoll = CanRoll; this.F_DrawingType = type; this.P_MntType = MonitorType; //将此对象添加进其所属的集合中 F_Class_ParentApp = ParentApp; // this.P_Key = System.Convert.ToInt32(F_Class_ParentApp.Mnt_Drawings.Add(this)); this.F_UniqueID = GeneralMethods.GetUniqueID(); // this.Tags = DrawingTag; } catch (Exception ex) { MessageBox.Show("创建基本监测曲线图出错。" + "\r\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
private void Openexcel_Click_1(object sender, EventArgs e) { List <int> processesbeforegen = getRunningProcesses(); if (excelac.ShowDialog() == DialogResult.OK) { textBox3.Text = excelac.FileName; Excel.Application exc = new Excel.Application(); { exc.Visible = false; } exc.Workbooks.Open(textBox3.Text); Worksheet sheet = exc.Worksheets[1]; sheet.Activate(); foreach (Worksheet ws in exc.Worksheets) { ChartObjects chartobjects = ws.ChartObjects(); foreach (ChartObject co in chartobjects) { co.Select(); Excel.Chart chart = co.Chart; chart.Export(exportpath.SelectedPath + @"\" + chart.Name + ".png", "PNG", false); } } progressBar1.Value = 40; MessageBox.Show("The graphs are imported successfully."); List <int> processesaftergen = getRunningProcesses(); killProcesses(processesbeforegen, processesaftergen); } }
/// <summary> /// 设置图例对象的UI格式:图例方框的线型、背景、位置与大小等 /// </summary> /// <param name="chart">图例对象所属的Chart对象</param> /// <param name="legend">要进行UI格式设置的图例对象</param> /// <remarks></remarks> private void SetLegendFormat(Excel.Chart chart, Excel.Legend legend) { //开始设置图例的格式 //1、设置图例的格式 legend.Format.Fill.Visible = Office.MsoTriState.msoTrue; legend.Format.Fill.ForeColor.RGB = Information.RGB(255, 255, 255); //图例对象的背景色 legend.Format.Shadow.Type = Microsoft.Office.Core.MsoShadowType.msoShadow21; //图例的阴影 //.Line.ForeColor.RGB = RGB(0, 0, 0) ' 图例对象的边框 legend.Format.TextFrame2.TextRange.Font.Name = AMEApplication.FontName_TNR; //2、设置图例的位置与大小 //图例方框的高度 float ExpectedHeight = System.Convert.ToSingle(this.Legend_Location.Legend_Height); // Data_Drawing_Format.Drawing_Mnt_RollingBase.Legend_Height legend.Select(); //对于Chart中的图例的位置与大小,其设置的原则是: //对于Top值,其始终不改变图例的大小,而将图例对象一直向下推进,直到图例对象的下边缘到达Chart的下边缘为止; //对于Height值,其始终不改变图例的Top的位置,而将图例对象的下边缘一直向下拉伸,直到图例对象的下边缘到达Chart的下边缘为止; //所以,如果要将图例对象成功地定位到Chart底部,应该先设置其Height值为0,然后设置其Top值,最后再回过来设置其Height值。 var selection = legend.Application.Selection as Excel.Range; selection.Left = 0; selection.Width = this.Legend_Location.Legend_Width; selection.Height = 0; //以此来让Selection的Top值成功定位 selection.Top = chart.ChartArea.Height - ExpectedHeight; selection.Height = ExpectedHeight; }
/// <summary> /// re modify the chart with numbers of row changed /// </summary> /// <param name="numRows"></param> /// <returns></returns> public bool ModifyChart(int numRows) { try { object misValue = System.Reflection.Missing.Value; // assign fomular for each sheet for (int i = 2; i <= xlWorkBook.Worksheets.Count; i++)// sheet { xlWorkSheet = xlWorkBook.Worksheets[i]; // delete old chart ChartObjects objs = xlWorkSheet.ChartObjects(); int chartCount = objs.Count; for (int j = 2; j <= chartCount; j++) { ChartObject currChartObj = objs.Item(j - 1); Microsoft.Office.Interop.Excel.Range chartRange; Microsoft.Office.Interop.Excel.Chart chartPage = currChartObj.Chart; chartRange = xlWorkSheet.Range[String.Format("A1:A{0},{1}1:{1}{0}", numRows + "", (char)((int)'A' + j - 1))]; chartPage.SetSourceData(chartRange, misValue); } } return(true); } catch (Exception ex) { MessageBox.Show(ex.Message); return(false); } }
private void AddLegendAndClearDummySeriesNames(Excel.Chart chart) { var legend = chart.Legend; var seriesCollection = chart.SeriesCollection(); for (var i = 1; i <= _projectTypeNames.Count; i++) { var series = seriesCollection.NewSeries(); series.Name = _projectTypeNames[i]; series.Format.Fill.ForeColor.RGB = _projectTypeColors[i]; } while (legend.LegendEntries().Count > 3) { legend.LegendEntries(1).Delete(); } legend.Position = Excel.XlLegendPosition.xlLegendPositionTop; DrawBubbleLegend(chart); legend.Top = chart.PlotArea.Height + BubbleLegendTop; legend.Left = BubbleLegendLeft; }
/// <summary> /// Returns the worksheet or chart's name. /// </summary> /// <param name="item">Worksheet or chart.</param> /// <returns>The worksheet or chart's name.</returns> static internal string GetName(object item) { string itemName; Excel.Worksheet sheet = item as Excel.Worksheet; if (sheet != null) { itemName = sheet.Name; } else { Excel.Chart chart = item as Excel.Chart; if (chart != null) { itemName = chart.Name; } else { itemName = null; } } return(itemName); }
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; }
private void buttonGrafik_Click(object sender, EventArgs e) { excelOrnek = new EXCEL.Application(); excelKitap = excelOrnek.Workbooks.Add(kayipDeger); excelSayfa = (EXCEL.Worksheet)excelKitap.Worksheets.get_Item(1); excelSayfa.Cells[1, 1] = ""; excelSayfa.Cells[1, 2] = "İstanbul"; excelSayfa.Cells[1, 3] = "Ankara"; excelSayfa.Cells[2, 1] = "Ocak"; excelSayfa.Cells[2, 2] = "350"; excelSayfa.Cells[2, 3] = "500"; excelSayfa.Cells[3, 1] = "Şubat"; excelSayfa.Cells[3, 2] = "300"; excelSayfa.Cells[3, 3] = "450"; excelSayfa.Cells[4, 1] = "Mart"; excelSayfa.Cells[4, 2] = "400"; excelSayfa.Cells[4, 3] = "600"; EXCEL.ChartObjects excelGrafikler = (EXCEL.ChartObjects)excelSayfa.ChartObjects(Type.Missing); EXCEL.ChartObject excelGrafik = (EXCEL.ChartObject)excelGrafikler.Add(10, 80, 300, 250); EXCEL.Chart grafikSayfa = excelGrafik.Chart; EXCEL.Range grafikAlan = excelSayfa.get_Range("A1", "C4"); grafikSayfa.SetSourceData(grafikAlan, kayipDeger); grafikSayfa.ChartType = EXCEL.XlChartType.xlColumnClustered; excelKitap.SaveAs("C:\\Users\\ufukcelik\\OneDrive\\ornekGrafik.xlsx", EXCEL.XlFileFormat.xlWorkbookNormal, kayipDeger, kayipDeger, kayipDeger, kayipDeger, EXCEL.XlSaveAsAccessMode.xlExclusive, kayipDeger, kayipDeger, kayipDeger, kayipDeger, kayipDeger); excelKitap.Close(true, kayipDeger, kayipDeger); excelOrnek.Quit(); richTextBoxEXCEL.Text = "Excel Grafik Dosyası oluşturuldu"; System.Runtime.InteropServices.Marshal.ReleaseComObject(excelOrnek); excelOrnek = null; GC.Collect(); }
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()); }
private Excel.Chart AddChart() { Excel.ChartObjects charts = (Excel.ChartObjects)Quelle.ChartObjects(Type.Missing); Excel.Chart newCh = charts.Add(50, 50, 500, 300).Chart; return(newCh); }
public void DrawChart() { var processes = Process.GetProcesses() .OrderBy(p => p.WorkingSet64); int i = 2; foreach (var p in processes) { if (p.ProcessName == "WINWORD" || p.ProcessName == "OUTLOOK" || p.ProcessName == "EXCEL") { excel.get_Range("A" + i).Value2 = p.ProcessName; excel.get_Range("B" + i).Value2 = p.WorkingSet64; i++; } } Excel.Range range = excel.get_Range("A1"); Excel.Chart chart = (Excel.Chart)excel.ActiveWorkbook.Charts.Add( After: excel.ActiveSheet); chart.ChartWizard(Source: range.CurrentRegion, Title: "Memory Usage of Office Applications"); chart.ChartType = Excel.XlChartType.xl3DArea; chart.ChartStyle = 14; chart.ChartArea.Copy(); }
private void DrawChart_FeedbackVsQuarter_per_FeedbackNature(int startYear, bool isPercentage = false) { Excel.Chart chart = (startYear == reportDate.Year) ? InitialiseChart($"Feedback trend in {reportDate.Year}-Q{reportDateQuarter}{(isPercentage ? " (percentage)" : "")}", isPercentage) : InitialiseChart($"Feedback trend since {startYear}{(isPercentage ? " (percentage)" : "")}", isPercentage); var data = new Dictionary <string, IList <Feedback> >(); for (int year = startYear; year <= reportDate.Year; year++) { for (int quarter = 1; quarter <= 4; quarter++) { data.Add($"{year}-Q{quarter}", feedbacks.Where(f => f.DateReceived.Year == year && ((f.DateReceived.Month + 2) / 3) == quarter).ToList()); if (year == reportDate.Year && quarter == reportDateQuarter) { break; } } } var feedbackNatures = FeedbackNature.FeedbackNaturesForChart; SetChartData(chart, data, feedbackNatures, isPercentage); ExportChart(chart); }
/// <summary> /// geterate thumbnail for a shape or a chart /// </summary> /// <param name="shape"> desired shape</param> /// <param name="path">path where the thumbnail will be created, excluding the .extension</param> /// <returns>full path of the created thumbnail</returns> private String GenerateThumbnail(Microsoft.Office.Interop.Excel.Chart shape, String path) { shape.Export(path + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false); Bitmap bmp = new Bitmap(path + DocumentService.IMAGE_EXTENSION); int T_H, T_W; if (bmp.Width <= bmp.Height) { T_W = (int)(DocumentService.THUMB_WIDTH * bmp.Width / bmp.Height); T_H = DocumentService.THUMB_WIDTH; } else { T_H = (int)(DocumentService.THUMB_WIDTH * bmp.Height / bmp.Width); T_W = DocumentService.THUMB_WIDTH; } Size s = new Size(T_W, T_H); Bitmap bmp1 = new Bitmap(bmp, s); bmp.Dispose(); FileInfo fi = new FileInfo(path + DocumentService.IMAGE_EXTENSION); fi.Delete(); bmp1.Save(path + DocumentService.IMAGE_EXTENSION); return(path + DocumentService.IMAGE_EXTENSION); }
public void click_exportChart(Office.IRibbonControl control) { // Get Active Elements Excel.Chart chart_active = GetActiveChart(); Excel.Workbook workbook_active = GetActiveWorkbook(); if (chart_active != null && workbook_active != null) { // Save File Dialog SaveFileDialog dialog_saveFile = new SaveFileDialog(); dialog_saveFile.Filter = "JPEG Image|*.jpg|Bitmap Image|*.bmp|GIF Image|*.gif|PNG Image|*.png|JPE Image|*.jpe"; dialog_saveFile.Title = "Export Chart As..."; // Set Default File Name dialog_saveFile.FileName = chart_active.ChartTitle.Text; // Set Default Directory SetDefaultDirectory(dialog_saveFile, workbook_active); // Show Save File Dialog dialog_saveFile.ShowDialog(); // Export Chart if (!String.IsNullOrEmpty(dialog_saveFile.FileName)) { chart_active.Export(dialog_saveFile.FileName); } } }
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; }
private static void ReportExcelToImage(String filePath, String[] columnList, String title, ChartType chartType = ChartType.COLUMN) { FileInfo file = new FileInfo(filePath); String directory = ""; if (Constant.ANALYZE_CHART_DIR.Equals("")) { directory = file.DirectoryName; } else { directory = Constant.ANALYZE_CHART_DIR; } String fileName = file.Name.Substring(0, file.Name.IndexOf('.')); String imagePath = directory + @"\" + fileName + "-" + title + ".jpg"; Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(filePath); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 1000, 850); Excel.Chart chartPage = myChart.Chart; //non-empty count of csv file int count = Convert.ToInt32(xlApp.WorksheetFunction.CountA(xlWorkSheet.get_Range("A:A"))); //non-empty count of header int headerCount = Convert.ToInt32(xlApp.WorksheetFunction.CountA(xlWorkSheet.Rows[1])); string chartString = "A1:A" + count; foreach (var column in columnList) { int columnIndex = GetColumnIndex(xlWorkSheet, headerCount, column); chartString += "," + GetString(columnIndex) + "1:" + GetString(columnIndex) + count; } chartRange = xlWorkSheet.get_Range(chartString); chartPage.SetSourceData(chartRange, misValue); //chartPage.ChartType = Excel.XlChartType.xlColumnClustered; chartPage.ChartType = GetChartType(chartType); chartPage.Axes(Excel.XlAxisType.xlValue); //export chart as picture file chartPage.Export(imagePath, "JPG", misValue); xlWorkBook.Close(false, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
private void btnGetColor_Click(object sender, EventArgs e) { flag = 1; //formLoad = this.Location; //this.Location = formLeft; //this.TopMost = true; tm = new Timer(); tm.Interval = 1; tm.Tick += new EventHandler(tm_Tick); tm.Enabled = true; Form_ColorWheel_GetColor getColors = new Form_ColorWheel_GetColor(); getColors.Tag = this; getColors.ShowDialog(); tm.Enabled = false; //this.Location = formLoad; //this.TopMost = false; txtARGB.SelectAll(); Excel.Chart chart = Globals.ThisAddIn.Application.ActiveChart; chart.MouseDown += new Excel.ChartEvents_MouseDownEventHandler(elementChart_MouseDown); }
private void button3_Click(object sender, EventArgs e) { 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); //add data //xlWorkSheet.Cells[1, 1] = ""; //xlWorkSheet.Cells[1, 2] = "Student1"; //xlWorkSheet.Cells[1, 3] = "Student2"; //xlWorkSheet.Cells[1, 4] = "Student3"; //xlWorkSheet.Cells[2, 1] = "Term1"; //xlWorkSheet.Cells[2, 2] = "80"; //xlWorkSheet.Cells[2, 3] = "65"; //xlWorkSheet.Cells[2, 4] = "45"; //xlWorkSheet.Cells[3, 1] = "Term2"; //xlWorkSheet.Cells[3, 2] = "78"; //xlWorkSheet.Cells[3, 3] = "72"; //xlWorkSheet.Cells[3, 4] = "60"; //xlWorkSheet.Cells[4, 1] = "Term3"; //xlWorkSheet.Cells[4, 2] = "82"; //xlWorkSheet.Cells[4, 3] = "80"; //xlWorkSheet.Cells[4, 4] = "65"; //xlWorkSheet.Cells[5, 1] = "Term4"; //xlWorkSheet.Cells[5, 2] = "75"; //xlWorkSheet.Cells[5, 3] = "82"; //xlWorkSheet.Cells[5, 4] = "68"; Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; chartRange = xlWorkSheet.get_Range("A1", "d5"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xl3DArea; xlWorkBook.SaveAs("\\\\dsview.pcoip.ki.agh.edu.pl\\Biblioteki-Pracownicy$\\szsz\\Desktop\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls"); }
private void SpreadOutDataLabels(Excel.Chart chart) { var occupiedRectangles = new List <Rectangle>(); var occupiedCircles = new List <Circle>(); var leaderLineAttachingPoints = new List <double[]>(); var seriesCollection = chart.SeriesCollection(); // First populated the list with all data bubbles... double i = 0; foreach (Excel.Series series in seriesCollection) { foreach (Excel.Point point in series.Points()) { var circle = new Circle(point.Left + point.Width / 2, point.Top + point.Height / 2, point.Width / 2, i); occupiedCircles.Add(circle); Debug.WriteLine("Added bubble " + point.DataLabel.Text + " at " + circle.CenterX + ", " + circle.CenterY + " with radius " + circle.Radius); i++; } } // ... and denote the X-axis as an occupied spot... var xAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory); occupiedRectangles.Add(new Rectangle(xAxis.Left, xAxis.Top, xAxis.Width, xAxis.Height)); // ... then make another pass, this time placing the data labels i = 0; foreach (Excel.Series series in seriesCollection) { foreach (Excel.Point point in series.Points()) { Excel.DataLabel dataLabel = series.DataLabels(1); var bubble = new Circle(point.Left + point.Width / 2, point.Top + point.Height / 2, point.Width / 2, i); Debug.WriteLine("Now finding placement for data label " + dataLabel.Text + "..."); var unoccupiedRectangle = FindUnoccupiedRectangleNearCircle(dataLabel.Width, dataLabel.Height, occupiedRectangles, occupiedCircles, bubble, out var leaderLineAttachingCoordinates); dataLabel.Left = unoccupiedRectangle.MinX; dataLabel.Top = unoccupiedRectangle.MinY; leaderLineAttachingPoints.Add(leaderLineAttachingCoordinates); occupiedRectangles.Add(unoccupiedRectangle); Debug.WriteLine("Added data label " + point.DataLabel.Text + " at " + unoccupiedRectangle.MinX + ", " + unoccupiedRectangle.MinY); i++; } } DrawLeaderLines(chart, leaderLineAttachingPoints); }
private void ExportChart(Excel.Chart chart) { chart.Export($@"{destinationFolder}\{chartNumber} - {chart.ChartTitle.Text}.png", "PNG"); chartNumber++; chart.Delete(); ChartCreated?.Invoke(this, $"{chart.ChartTitle.Text} created"); }
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 button3_Click(object sender, EventArgs e) { exel.Application application = new exel.Application(); exel.Sheets sheets; exel.Worksheet worksheet; exel.Range cells; application.SheetsInNewWorkbook = 1; application.Workbooks.Add(Type.Missing); sheets = application.ActiveWorkbook.Sheets; worksheet = (exel.Worksheet)sheets.get_Item(1); worksheet.Name = "Статистика обновления"; worksheet.Activate(); int row = chart1.Series[0].Points.Count; int col = chart1.Series.Count; for (int i = 0; i < row; i++) { cells = (exel.Range)worksheet.Cells[i + 1, 1]; cells.Value2 = chart1.Series[0].Points[i].XValue + " г"; } for (int i = 0; i < col; i++) { for (int j = 0; j < row; j++) { cells = (exel.Range)worksheet.Cells[j + 1, i + 2]; cells.Value2 = chart1.Series[i].Points[j].YValues; } } string cur = "c" + (row); cells = worksheet.get_Range("a1", cur.ToString()); cells.Select(); exel.Chart chart = (exel.Chart)application.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); chart.Activate(); chart.Select(Type.Missing); application.ActiveChart.PlotBy = exel.XlRowCol.xlColumns; application.ActiveChart.HasTitle = true; application.ActiveChart.ChartTitle.Text = "Обновления"; application.ActiveChart.HasLegend = true; application.ActiveChart.Legend.Position = exel.XlLegendPosition.xlLegendPositionBottom; application.ActiveChart.SeriesCollection(1).Name = "Требуется обновить"; application.ActiveChart.SeriesCollection(2).Name = "Обновлено"; application.ActiveChart.ChartType = exel.XlChartType.xlColumnClustered; application.ActiveChart.Location(exel.XlChartLocation.xlLocationAsObject, "Статистика обновления"); application.ActiveChart.ApplyDataLabels(exel.XlDataLabelsType.xlDataLabelsShowValue, false, true, false, false, false, false, true, false, true); application.Visible = true; }
//添加图表路径 private void addGraph() { oxml.Path.Add(new OfficeElement("Workbooks", "0")); if (radioSingleChart.Checked == true) { oxml.Path.Add(new OfficeElement("Charts",(comboChart.SelectedIndex + 1).ToString() + ":" + "0")); //该图表是所有图表中的第几个 ch = (Excel.Chart)xls.Sheets[chartIndex[comboChart.SelectedIndex]]; //获取图表 } else { oxml.Path.Add(new OfficeElement("Charts", (comboSheet.SelectedIndex + 1).ToString() //该工作簿是所有工作簿的第几个 + ":" + (comboChartInSheet.SelectedIndex + 1).ToString())); //该图表是工作簿里面的第几个图表 ws = (Excel.Worksheet)xls.Sheets[worksheetIndex[comboSheet.SelectedIndex]]; ch = ((Excel.ChartObject)ws.ChartObjects(comboChartInSheet.SelectedIndex + 1)).Chart; //获取图表 } }
//评分核心函数 public int check_Kernel(List<OfficeElement> ls) { int stuPage = 0, ansPage = 0; //学生和标答使用的工作表页码 int points = 0; //考生考点得分 int i; for (i = 0; i < ls.Count; i++) { OfficeElement oe = ls[i]; if (oe.AttribName == "Root") continue; if (oe.AttribName == "Workbooks") continue; if (oe.AttribName == "Sheets") //文字考点 { #region 获取学生和标答工作表位置 stuPage = ansPage = int.Parse(oe.AttribValue); ansWs = (Excel.Worksheet)ansXls.Worksheets[ansPage]; if (stuXls.Worksheets.Count >= int.Parse(oe.AttribValue)) //学生的xls能找到第AttribValue张工作表 stuWs = (Excel.Worksheet)stuXls.Worksheets[stuPage]; else //学生的xls不能找到第AttribValue张工作表 { try { stuWs = (Excel.Worksheet)stuXls.Worksheets[1]; //取这个学生的第一个工作表代替 } catch //天哪,居然一个工作表都没有 { stuWs = null; } } #endregion points = check_Words(ls, i + 1, stuWs, ansWs); break; } if (oe.AttribName == "Charts") //图表考点 { ansCh = getAnsChartPosition(oe.AttribValue); stuCh = getStudentChartPosition(); points = check_Chart(ls, i + 1, stuCh, ansCh, stuObj, ansObj); break; } } return points; }
public void AddInfo(FunctionTableItem[] fun, ValueIs value = ValueIs.Magnitude, bool addChart = false) { Excel.Series series; Excel.Range temp; range = range.get_Resize(2, fun.Length); range.set_Value(Missing.Value, GetArray(fun, value)); if (addChart) { LastIndex += chartHeight + 10; chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight); } currentChart = chartObject.Chart; series = ((Excel.SeriesCollection) currentChart.SeriesCollection(Type.Missing)).NewSeries(); temp = range.get_Resize(1, fun.Length); series.Values = temp; series.XValues = temp.get_Offset(1, 0); currentChart.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers; range = range.get_Offset(3, 0); }
private void getChartInSheet(int index) { comboChartInSheet.Items.Clear(); if (index != 0) { ws = (Excel.Worksheet)xls.Sheets[index]; chobjs = (Excel.ChartObjects)ws.ChartObjects(nullobj); //Console.WriteLine(chobjs.Count); for (int i = 1; i <= chobjs.Count; i++) { chobj = (Excel.ChartObject)ws.ChartObjects(i); ch = chobj.Chart; string str = chobj.Name + ": "; try { string title = ch.ChartTitle.Text; str += title; } catch { str += "(未命名图表)"; } comboChartInSheet.Items.Add(str); } } if (comboChartInSheet.Items.Count == 0) { comboChartInSheet.Items.Add("(该页无图表!)"); buttonGraph.Enabled = false; groupGraphPoint.Enabled = false; } else { buttonGraph.Enabled = true; groupGraphPoint.Enabled = true; } comboChartInSheet.SelectedIndex = 0; }
private void CreateChart() { chart = oWS.ChartObjects().Add(0,0,800,500).Chart; chart.SetSourceData(oWS.UsedRange); chart.ChartType = Excel.XlChartType.xlColumnClustered; chart.ChartStyle = 17; foreach (Excel.ChartGroup group in chart.ChartGroups()) { group.GapWidth = 0; group.Overlap = 0; } }