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