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]);
}
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());
}
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( ));
}
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;
}
}
/// <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);
}
}
}
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();
}
public DensityScatter()
{
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;
int i;
((Excel.Range)worksheet.Cells[start_row, start_col + cols]).Value2 = "X-axis Value";
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "Y-axis Value";
double Pdensity = Bandwidth * 100;
double Maxtemp = 1000;
double[,] data = new double[rows, 2];
for (i = 1; i < rows; i++)
{
data[i - 1, 0] = Math.Floor(double.Parse(str[i, 0]) * Maxtemp / Pdensity) * Pdensity / Maxtemp;
data[i - 1, 1] = Math.Floor(double.Parse(str[i, 1]) * Maxtemp / Pdensity) * Pdensity / Maxtemp;
//((Excel.Range)worksheet.Cells[start_row + i, start_col + cols]).Value2 = Math.Floor(double.Parse(str[i, 0]) * Maxtemp / Pdensity) * Pdensity / Maxtemp;
//((Excel.Range)worksheet.Cells[start_row + i, start_col + cols + 1]).Value2 = Math.Floor(double.Parse(str[i, 1]) * Maxtemp / Pdensity) * Pdensity / Maxtemp;
}
Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols];
Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + cols - 1];
Excel.Range range = worksheet.get_Range(c1, c2);
range.Value = data;
string ChartOrder = "DensityScatter" + Convert.ToString(Nchart);
chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder);
Nchart = Nchart + 1;
c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols];
c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + cols - 1];
chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns);
chart.ChartType = Excel.XlChartType.xlXYScatter;
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];
c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols];
Sseries.XValues = worksheet.get_Range(c1, c2);
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1];
c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + 1];
Sseries.Values = worksheet.get_Range(c1, c2);
Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;
//Sseries.Format.Line.Visible = Office.MsoTriState.msoFalse;
Sseries.MarkerSize = 5;
Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, 109, 118, 248).ToArgb();
Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, 109, 118, 248).ToArgb();
//Sseries.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;
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, 109, 118, 248).ToArgb();
Sseries.Format.Fill.Transparency = 0.9F;
worksheet.Activate();
}
private void buildXlsChart(Xls.Chart chart, Xls.Worksheet dataSheet, ChartInfo graph, int currentDataColumn, out int dataColumnsWritten)
{
chart.ChartTitle.Text = string.Format(Strings.ChartTitle, graph.PrimaryTitle, graph.SecondaryTitle);
dataColumnsWritten = 0;
double[,] xValues = null, yValues = null;
int seriesCounter = 0;
foreach (var s in graph.Series)
{
if (seriesCounter == 0)
{
dataSheet.Cells[2, currentDataColumn].Value = s.XUnitName;
xValues = ConvertToTwoDimensionalArray(s.XValues);
dataSheet.Range[dataSheet.Cells[3, currentDataColumn], dataSheet.Cells[3 + xValues.Length - 1, currentDataColumn]].Value2 = xValues;
dataColumnsWritten += 1;
}
dataSheet.Cells[1, currentDataColumn + dataColumnsWritten].Value = s.Name;
dataSheet.Cells[2, currentDataColumn + dataColumnsWritten].Value = s.YUnitName;
yValues = ConvertToTwoDimensionalArray(s.YValues);
dataSheet.Range[dataSheet.Cells[3, currentDataColumn + dataColumnsWritten], dataSheet.Cells[3 + yValues.Length - 1, currentDataColumn + dataColumnsWritten]].Value2 = yValues;
dataColumnsWritten += 1;
seriesCounter++;
}
if (seriesCounter > 0)
{
chart.SetSourceData(Source: dataSheet.Range[dataSheet.Cells[3, currentDataColumn], dataSheet.Cells[3 + xValues.Length - 1, currentDataColumn + dataColumnsWritten - 1]]);
Xls.SeriesCollection collection = chart.SeriesCollection();
Xls.Axes axes = null;
try
{
for (int i = 0; i < collection.Count; i++)
{
collection.Item(i + 1).Name = graph.Series.ElementAt(i).Name;
if (graph.Series.ElementAt(i).ShowInSecondaryVerticalAxis&& graph.HasSecondaryVerticalAxis)
{
collection.Item(i + 1).AxisGroup = Xls.XlAxisGroup.xlSecondary;
}
}
axes = chart.Axes();
axes.Item(Xls.XlAxisType.xlCategory, Xls.XlAxisGroup.xlPrimary).AxisTitle.Text = graph.GetHorizontalAxisTitle();
axes.Item(Xls.XlAxisType.xlValue, Xls.XlAxisGroup.xlPrimary).AxisTitle.Text = graph.GetPrimaryVerticalAxisTitle();
if (graph.HasSecondaryVerticalAxis)
{
axes.Item(Xls.XlAxisType.xlValue, Xls.XlAxisGroup.xlSecondary).HasTitle = true;
axes.Item(Xls.XlAxisType.xlValue, Xls.XlAxisGroup.xlSecondary).AxisTitle.Text = graph.GetSecondaryVerticalAxisTitle();
}
}
finally
{
if (axes != null)
{
Marshal.FinalReleaseComObject(axes);
}
Marshal.FinalReleaseComObject(collection);
}
}
}
public ColumnColor()
{
InitializeComponent();
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;
Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row, start_col];
Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols - 2];
string ChartOrder = "ColorColumn" + 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.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];
c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col];
Sseries.XValues = worksheet.get_Range(c1, c2);
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + 1];
c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + 1];
Sseries.Values = worksheet.get_Range(c1, c2);
Excel.Point point;
int[] HSV0 = new int[3];
RGB0 = System.Drawing.Color.FromArgb(255, 96, 157, 202);
System.Drawing.Color RGB1 = System.Drawing.Color.FromArgb(255, 96, 157, 202);
Graphic.RGB2HSV(RGB0, ref HSV0);
Max_Value = Double.MinValue;
Min_Value = Double.MaxValue;
for (int i = 1; i < rows; i++)
{
if (double.Parse(str[i, 2]) > Max_Value)
{
Max_Value = double.Parse(str[i, 1]);
}
if (double.Parse(str[i, 2]) < Min_Value)
{
Min_Value = double.Parse(str[i, 1]);
}
}
int[] HSV = new int[3];
HSV0.CopyTo(HSV, 0);
double ratio;
Hrange = double.Parse(textBox_Bandwidth.Text);
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;
}
//Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb();
//Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb();
Sseries.Format.Line.Visible = Office.MsoTriState.msoCTrue;
Sseries.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
Sseries.Format.Line.Weight = 0.75F;
Excel.ChartGroup group = (Excel.ChartGroup)chart.ChartGroups(1);
group.GapWidth = 0;
chart.HasLegend = false;
chart.HasTitle = false;
//chart.ChartTitle.Delete();
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;
}
double Step = Bandwidth / Nstep;
int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Step + 2);
double[,] data = new double[Nrows, 1];
double[,] x = new double[Nrows, 1];
double tempx;
for (int i = 0; i < Nrows; i++)
{
x[i, 0] = i * Step + Min_Value;
for (int j = 1; j < rows; j++)
{
tempx = (x[i, 0] - double.Parse(str[j, 0])) / Bandwidth;
data[i, 0] = data[i, 0] + 1 / ((rows - 1) * Bandwidth) * Math.Exp(-tempx * tempx / 2) / Math.Sqrt(2 * 3.124259);
}
}
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X axis";
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).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, start_col + cols + 1];
Excel.Range range = worksheet.get_Range(c1, c2);
range.Value = x;
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2];
range = worksheet.get_Range(c1, c2);
range.Value = data;
c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 1];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2];
chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns);
//chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlArea;
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 + 1];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 1];
Sseries.XValues = worksheet.get_Range(c1, c2);
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2];
Sseries.Values = worksheet.get_Range(c1, c2);
chart.Refresh();
worksheet.Activate();
}
public Form_Color_Matrix()
{
InitializeComponent();
height = int.Parse(textBox_height.Text);
Graphic.RangeData(ref str, ref rows, ref cols);
worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell;
start_col = activecells.Column;
start_row = activecells.Row;
int width = (int)Math.Ceiling(Convert.ToDouble(rows) / Convert.ToDouble(height));
int[,] data = new int[height, width];
int[] temp = new int[width];
// for (int i = 0; i < height; i++)
// {
//temp = new int[width];
// for (int j = 0; j < width; j++)
// {
// // temp[j] = 1;
// data[i, j] = 1;
// ((range)worksheet.Cells[start_row + rows + 1 + i , start_col + j ]).Value2 = 1;
// }
//Sseries = series.NewSeries();
//Sseries.Values = temp;
// }
range c1 = (range)worksheet.Cells[start_row + rows + 1, start_col];
range c2 = (range)worksheet.Cells[start_row + rows + 1 + height - 1, start_col + width - 1];
string ChartOrder = "chart" + Convert.ToString(Nchart);
chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder);
Nchart = Nchart + 1;
//chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns);
chart.ChartType = Excel.XlChartType.xlColumnStacked100;
Excel.ChartGroup group = (Excel.ChartGroup)chart.ChartGroups(1);
group.GapWidth = 0;
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Series Sseries;
Excel.Point point;
int RGB_B, RGB_G, RGB_R;
for (int i = 0; i < height; i++)
{
temp = new int[width];
for (int j = 0; j < width; j++)
{
temp[j] = 1;
data[i, j] = 1;
}
Sseries = series.NewSeries();
Sseries.Values = temp;
}
for (int i = 1; i <= height; i++)
{
Sseries = series.Item(i);
for (int j = 1; j <= width; j++)
{
{
point = (Excel.Point)Sseries.Points(j);
point.Format.Fill.Solid();
point.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
point.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb();
int index = (i - 1) * width + j;
if (index < rows)
{
RGB_B = Convert.ToInt32(double.Parse(str[index, cols - 1])); if (RGB_B < 0)
{
RGB_B = 0;
}
if (RGB_B > 255)
{
RGB_B = 255;
}
RGB_G = Convert.ToInt32(double.Parse(str[index, cols - 2])); if (RGB_G < 0)
{
RGB_G = 0;
}
if (RGB_G > 255)
{
RGB_G = 255;
}
RGB_R = Convert.ToInt32(double.Parse(str[index, cols - 3])); if (RGB_R < 0)
{
RGB_R = 0;
}
if (RGB_R > 255)
{
RGB_R = 255;
}
point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB_B, RGB_G, RGB_R).ToArgb();
point.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb();
}
else
{
point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb();
point.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb();
}
}
}
}
}
// データテーブルをエクセルへエクスポート（ＸＲ管理図・ヒストグラム付き）
public void 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();
// チャート１（Ｘ－Ｒ管理図１）
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;
// チャート２（Ｘ－Ｒ管理図２）
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;
// チャート３（ヒストグラム）
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 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();
}
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();
}
/// <summary>
/// Draws Chart or Graph for given component.
/// </summary>
/// <param name="componentName"></param>
/// <param name="oSummarySheet"></param>
/// <param name="webAppUrlColumn"></param>
/// <param name="componentColumnCount"></param>
/// <param name="counter"></param>
/// <param name="row"></param>
/// <param name="chartType"></param>
/// <param name="chartWidth"></param>
/// <param name="chartHeight"></param>
/// <param name="chartStyle"></param>
/// <param name="CellIndex"></param>
public static void DrawGraph(string componentName, Excel.Worksheet oSummarySheet, string webAppUrlColumn, int componentColumnCount, int counter,
int row, string chartType, int chartWidth, int chartHeight, int chartStyle, char CellIndex)
{
//Create chart object
Excel.Shape _Shape = oSummarySheet.Shapes.AddChart2();
//Specify type of chart
if (chartType.Equals("pie"))
{
_Shape.Chart.ChartType = Excel.XlChartType.xlPie;
}
else if (chartType.Equals("3dpie"))
{
_Shape.Chart.ChartType = Excel.XlChartType.xl3DPie;
}
else if (chartType.Equals("line"))
{
_Shape.Chart.ChartType = Excel.XlChartType.xlLine;
}
else if (chartType.Equals("3dline"))
{
_Shape.Chart.ChartType = Excel.XlChartType.xl3DLine;
}
else if (chartType.Equals("3dcolumn"))
{
_Shape.Chart.ChartType = Excel.XlChartType.xl3DColumn;
}
else if (chartType.Equals("clusteredcolumn"))
{
_Shape.Chart.ChartType = Excel.XlChartType.xlColumnClustered;
}
else if (chartType.Equals("3dclusteredcolumn"))
{
_Shape.Chart.ChartType = Excel.XlChartType.xl3DColumnClustered;
}
//Series object for the graph
Excel.Series series = null;
string exceptionComment = "[DrawGraph] Processing for Component :" + componentName;
Logger.LogInfoMessage(String.Format("[GeneratePivotReports][DrawGraph] Processing Started for (" + componentName + ")"), false);
try
{
//Get Series Column from SummaryView table
string componentColumn = ((Char)(Convert.ToUInt16(CellIndex) + componentColumnCount)).ToString();
//Set Series column for the Graph
series = _Shape.Chart.SeriesCollection().Add(oSummarySheet.Range[componentColumn + row + ":"
+ componentColumn + (counter).ToString()]);
//Set Categories column for the graph
series.XValues = oSummarySheet.Range[webAppUrlColumn + (row + 1).ToString() + ":"
+ webAppUrlColumn + (counter).ToString()];
//Apply data labels for the graph
_Shape.Chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
//Apply legend for the graph
_Shape.Chart.HasLegend = true;
//apply style to chart
_Shape.Chart.ChartStyle = chartStyle;
//Hide Display Labels when their value is zero (0)
Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)_Shape.Chart.SeriesCollection(Type.Missing);
for (int j = 1; j <= oSeriesCollection.Count; j++)
{
Excel.Series oSeries = (Excel.Series)oSeriesCollection.Item(j);
System.Array Values = (System.Array)((object)oSeries.Values);
//Array Values = (Array)oSeries.Values;
for (int k = 1; k <= Values.Length; k++)
{
Excel.DataLabel oDataLabel = (Excel.DataLabel)oSeries.DataLabels(k);
string caption = oDataLabel.Caption.ToString();
if (caption.Equals("0"))
{
oDataLabel.ShowValue = false;
}
}
}
//Set the Size of the Chart
_Shape.Width = chartWidth;
_Shape.Height = chartHeight;
//Calculations for the position of Chart
int columnIndex = counter + 3;
if (componentColumnCount > 3 && (componentColumnCount / 3 > 0))
{
columnIndex = (counter + 3) + (((componentColumnCount - 1) / 3) * 16);
}
string charPositionColumn = webAppUrlColumn;
if (componentColumnCount % 3 == 2)
{
charPositionColumn = ((Char)(Convert.ToUInt16(CellIndex) + 7)).ToString();
}
if (componentColumnCount % 3 == 0)
{
charPositionColumn = ((Char)(Convert.ToUInt16(CellIndex) + 14)).ToString();
}
_Shape.Left = (float)oSummarySheet.get_Range(charPositionColumn + columnIndex.ToString()).Left;
_Shape.Top = (float)oSummarySheet.get_Range(charPositionColumn + columnIndex.ToString()).Top;
Logger.LogInfoMessage(String.Format("[GeneratePivotReports][DrawGraph] Process Completed for (" + componentName + ")"), true);
}
catch (Exception ex)
{
Logger.LogErrorMessage(String.Format("[GeneratePivotReports][DrawGraph][Exception]: " + ex.Message + ", " + exceptionComment), true);
ExceptionCsv.WriteException(Constants.NotApplicable, Constants.NotApplicable, Constants.NotApplicable, "Pivot", ex.Message, ex.ToString(),
"[GeneratePivotReports]: DrawGraph", ex.GetType().ToString(), exceptionComment);
}
finally
{
_Shape = null;
series = null;
oSummarySheet = null;
}
}
private void diagram_Click(object sender, EventArgs e)
{
KURS.allDataSet kursds = new KURS.allDataSet();
KURS.allDataSetTableAdapters.ZakazTableAdapter zta = new KURS.allDataSetTableAdapters.ZakazTableAdapter();
zta.Fill(kursds.Zakaz);
//
object misValue = System.Reflection.Missing.Value;
//
Excel.Application excelapp = new Excel.Application();
excelapp.Visible = true;
excelapp.SheetsInNewWorkbook = 1;
excelapp.Workbooks.Add(misValue);
Excel.Workbooks excelappworkbooks = excelapp.Workbooks;
Excel.Workbook excelappworkbook = excelappworkbooks[1];
Excel.Sheets excelsheets = excelappworkbook.Worksheets;
Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
//excelworksheet.Activate();
//
var c = from p in kursds.Zakaz.AsEnumerable() where p.Data.Month == dateTimePicker1.Value.Month select p;
int count = 0;
excelapp.Cells[1][1] = "date";
excelapp.Cells[2][1] = "summ";
foreach (var p in c)
{
count++;
}
//
int[] a = new int[count]; double[] b = new double[count]; int i = 0; double sum = 0;
while (i < count)
{
foreach (var p in c)
{
a[i] = p.Data.Day;
if (i == 0)
{
sum += p.Summ;
}
if (i > 0)
{
if (a[i] == a[i - 1])
{
sum += p.Summ;
}
else
{
b[i - 1] = sum; sum = p.Summ; i++;
}
}
else
{
i++;
}
}
b[i - 1] = sum;
count = i; i = 0;
break;
}
Dictionary <int, double> dic = new Dictionary <int, double>();
while (i < count)
{
excelapp.Cells[1][i + 2] = a[i];
excelapp.Cells[2][i + 2] = b[i];
dic.Add(a[i], b[i]);
i++;
}
//
string str = (count + 1).ToString();
//
//Excel.Window excelWindow = null;
Excel.Range excelcells = null;
//Определяем диаграммы как объекты Excel.ChartObjects
Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excelworksheet.ChartObjects(Type.Missing);
//Добавляем одну диаграмму в Excel.ChartObjects - диаграмма пока не выбрана, но место для нее выделено в методе Add
Excel.ChartObject chartsobjrct = chartsobjrcts.Add(100, 40, 300, 300);
Excel.Chart chartPage = chartsobjrct.Chart;
excelcells = excelworksheet.get_Range("B1", "B" + str);
chartPage.SetSourceData(excelcells, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis axis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "Data";
//
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.Item(1);
series.XValues = a;
//
excelappworkbook.SaveAs(@"D:\siple.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
excelappworkbook.Close(true, misValue, misValue);
excelapp.Quit();
this.chart1.Titles.Add("Отчет за месяц");
this.chart1.Series[0].Points.Clear();
Series ser = this.chart1.Series[0];
ser.LegendText = "Summ";
foreach (KeyValuePair <int, double> pair in dic)
{
ser.Points.AddXY(pair.Key, pair.Value);
}
//
}
/*'***************************************************************
* 'FFT0 数组下标以0开始 FFT1 数组下标以1开始
* 'AR() 数据实部 AI() 数据虚部
* 'N 数据点数，为2的整数次幂
* 'NI 变换方向 1为正变换，-1为反变换
* '*************************************************************** */
private void Form_Fourier_Load(object sender, EventArgs e)
{
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;
//double[] FFTProjectionArray = new double[rows - 1];
double[,] NewFFTProjectionArray = new double[rows - 1, 1]; //store the projection curve of FFT
double TwoTime = 0;
double TwoResult = rows - 1;
while (TwoResult > 1)
{
TwoResult = TwoResult / 2;
TwoTime = TwoTime + 1;
}
Nrows = Convert.ToInt32(Math.Pow(2.0, TwoTime));
double[] AR = new double[Nrows];
//double[] NewAR = new double[Nrows];
for (int j = 1; j < rows; j++)
{
//FFTProjectionArray[j - 1] = double.Parse(str[j, 1]);
AR[j - 1] = double.Parse(str[j, 1]);
}
double[] AI = new double[Nrows];
FFT0(ref AR, ref AI, Nrows, 1);
Span = double.Parse(textBox_Bandwidth.Text);
comboBox_FourierMethod.Text = "Low Pass";
for (int j = Convert.ToInt32(rows * Span) + 1; j < Nrows; j++)
{
AR[j] = 0;
AI[j] = 0;
}
FFT0(ref AR, ref AI, Nrows, -1);
for (int j = 1; j < rows; j++)
{
NewFFTProjectionArray[j - 1, 0] = AR[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 = NewFFTProjectionArray;
//*******************************************************************************************8
c1 = (Excel.Range)worksheet.Cells[start_row, start_col];
c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols];
string ChartOrder = "CurveFourier" + 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();
}
public DensityCurve()
{
InitializeComponent();
Bandwidth = double.Parse(textBox_Bandwidth.Text);
Graphic.RangeData(ref str, ref rows, ref cols);
worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell;
start_col = activecells.Column;
start_row = activecells.Row;
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]);
}
}
double Step = Bandwidth / Nstep;
int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Step + 2);
double[,] data = new double[Nrows, 1];
double[,] x = new double[Nrows, 1];
double tempx;
for (int i = 0; i < Nrows; i++)
{
x[i, 0] = i * Step + Min_Value;
for (int j = 1; j < rows; j++)
{
tempx = (x[i, 0] - double.Parse(str[j, 0])) / Bandwidth;
data[i, 0] = data[i, 0] + 1 / ((rows - 1) * Bandwidth) * Math.Exp(-tempx * tempx / 2) / Math.Sqrt(2 * 3.124259);
}
}
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X axis";
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).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, start_col + cols + 1];
Excel.Range range = worksheet.get_Range(c1, c2);
range.Value = x;
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2];
range = worksheet.get_Range(c1, c2);
range.Value = data;
string ChartOrder = "DensityCurve" + 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 + 1];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2];
chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns);
chart.ChartType = Excel.XlChartType.xlArea;
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 + 1];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 1];
Sseries.XValues = worksheet.get_Range(c1, c2);
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 2];
Sseries.Values = worksheet.get_Range(c1, c2);
chart.HasLegend = false;
chart.HasTitle = false;
worksheet.Activate();
}
public AreaThreshold()
{
InitializeComponent();
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;
double Max_Value = Double.MinValue;
double 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, 1]);
}
if (double.Parse(str[i, 0]) < Min_Value)
{
Min_Value = double.Parse(str[i, 1]);
}
}
ThreholdArea = (Max_Value + Min_Value) / 2;
textBox_Bandwidth.Text = Convert.ToString(ThreholdArea);
textBox_Bandwidth.Refresh();
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "Assiatant";
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "<=Threshold";
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 3]).Value2 = ">Threshold";
double[,] data = new double[rows - 1, 3];
for (int i = 1; i < rows; i++)
{
if (double.Parse(str[i, 1]) > ThreholdArea)
{
data[i - 1, 2] = double.Parse(str[i, 1]) - ThreholdArea;
data[i - 1, 1] = 0;
data[i - 1, 0] = ThreholdArea - data[i - 1, 1];
}
else
{
data[i - 1, 2] = 0;
data[i - 1, 1] = ThreholdArea - double.Parse(str[i, 1]);
data[i - 1, 0] = ThreholdArea - data[i - 1, 1];
}
}
//double Max_data = Double.MinValue;
//double Min_data = Double.MaxValue;
//for (int i = 1; i < rows; i++)
//{
// if (data[i - 1, 0] > Max_data) Max_data = data[i - 1, 0];
// if (data[i - 1, 1] < Min_data) Min_data = data[i - 1, 1];
//}
Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1];
Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + 3];
Excel.Range range = worksheet.get_Range(c1, c2);
range.Value = data;
string ChartOrder = "AreaThreshold" + 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 + 1];
c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + 3];
chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns);
chart.ChartType = Excel.XlChartType.xlAreaStacked;
//*****************************************Primary Axis********************************************
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Series Sseries1 = series.Item(1);
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col];
c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col];
Sseries1.XValues = worksheet.get_Range(c1, c2);
//c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1];
//c2 = (Excel.Range)worksheet.Cells[start_row + rows-1, start_col + cols + 1];
//Sseries1.Values = worksheet.get_Range(c1, c2);
//c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col];
//c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col];
//Sseries2.XValues = worksheet.get_Range(c1, c2);
//c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2];
//c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols + 2];
//Sseries2.Values = worksheet.get_Range(c1, c2);
((Excel.Range)worksheet.Cells[start_row, start_col + cols]).Value2 = "Threshold";
((Excel.Range)worksheet.Cells[start_row + 1, start_col + cols]).Value2 = ThreholdArea;
//*****************************************Chart Style********************************************
//Excel.Series Sseries1 = series.Item(1);
Sseries1.Format.Fill.Visible = Office.MsoTriState.msoFalse;
Sseries1.Format.Line.Visible = Office.MsoTriState.msoFalse;
Excel.Series Sseries2 = series.Item(2);
Sseries2.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 196, 191, 0).ToArgb();
Sseries2.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb();
Sseries2.Format.Line.Weight = 0.25F;
Excel.Series Sseries3 = series.Item(3);
Sseries3.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 109, 118, 248).ToArgb();
Sseries3.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb();
Sseries3.Format.Line.Weight = 0.25F;
//************************************************************************************************
chart.PlotArea.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
chart.PlotArea.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(229, 229, 229).ToArgb();
chart.PlotArea.Format.Fill.Transparency = 0;
chart.PlotArea.Format.Line.Visible = Office.MsoTriState.msoFalse;
// Legend
chart.SetElement(Office.MsoChartElementType.msoElementLegendRight);
chart.Legend.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
chart.Legend.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
chart.Legend.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
chart.Legend.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
chart.Legend.Format.TextFrame2.TextRange.Font.Size = 10;
// ChartArea Line
chart.ChartArea.Format.Line.Visible = Office.MsoTriState.msoFalse;
chart.ChartArea.Height = 340.157480315;
chart.ChartArea.Width = 380.5039370079;
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryValueGridLinesMinorMajor);
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryCategoryGridLinesMinorMajor);
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis);
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryCategoryAxisTitleAdjacentToAxis);
//y axis
Excel.Axis axis = (Excel.Axis)chart.Axes(
Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);
axis.MinorUnit = axis.MajorUnit / 2;
axis.MajorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue;
axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
axis.MajorGridlines.Format.Line.Weight = (float)1.25;
axis.MajorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.MinorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue;
axis.MinorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(242, 242, 242).ToArgb();
axis.MinorGridlines.Format.Line.Weight = (float)0.25;
axis.MinorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.Format.Line.Visible = Office.MsoTriState.msoFalse;
axis.HasTitle = true;
axis.AxisTitle.Text = "y axis";
axis.TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
axis.TickLabels.Font.Name = "Times New Roman";
axis.TickLabels.Font.Size = 10;
axis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow;
axis.AxisTitle.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
axis.AxisTitle.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
axis.AxisTitle.Format.TextFrame2.TextRange.Font.Size = 10;
axis.AxisTitle.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse;
//x axis
axis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
axis.TickMarkSpacing = 3;
axis.MajorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue;
axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
axis.MajorGridlines.Format.Line.Weight = (float)1.25;
axis.MajorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.MinorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue;
axis.MinorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(242, 242, 242).ToArgb();
axis.MinorGridlines.Format.Line.Weight = (float)0.25;
axis.MinorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.Format.Line.Visible = Office.MsoTriState.msoFalse;
axis.HasTitle = true;
axis.AxisTitle.Text = "x axis";
axis.TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
axis.TickLabels.Font.Name = "Times New Roman";
axis.TickLabels.Font.Size = 10;
axis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow;
axis.AxisTitle.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
axis.AxisTitle.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
axis.AxisTitle.Format.TextFrame2.TextRange.Font.Size = 10;
axis.AxisTitle.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse;
// Change plot area ForeColor
chart.HasLegend = false;
chart.HasTitle = false;
chart.Refresh();
//worksheet.Activate();
flag = 1;
}
private Excel.Chart SetStyleXlsChart(Excel.Chart xlChart)
{
Excel.SeriesCollection sc = xlChart.SeriesCollection();
Excel.Series xlSeries;
for (int i = 0; i < sc.Count; i++)
{
xlSeries = sc.Item(i + 1);
xlSeries.ChartType = GetSeriesChartType(chart.Series[i]);
if (chart.Series[i].ChartType == SeriesChartType.Bar || chart.Series[i].ChartType == SeriesChartType.Column)
{
for (int j = 1; j <= chart.Series[i].Points.Count; j++)
{
xlSeries.Points(j).Interior.Color = ColorTranslator.ToOle(chart.Series[i].Color);
}
}
else
{
xlSeries.Border.Color = chart.Series[i].Color;
}
xlSeries.Border.LineStyle = GetLineDashStyle(chart.Series[i]);
xlSeries.Border.Weight = chart.Series[i].BorderWidth;
xlSeries.MarkerStyle = GetSeriesMarkerStyle(chart.Series[i]);
xlSeries.Name = chart.Series[i].Name;
progress++;
}
if (!string.IsNullOrWhiteSpace(chart.Titles[0].Text))
{
xlChart.HasTitle = true;
xlChart.ChartTitle.Text = chart.Titles[0].Text;
}
else
{
xlChart.HasTitle = false;
}
progress++;
xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = true;
xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = chart.ChartAreas[0].AxisX.Title;
xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = chart.ChartAreas[0].AxisX.MajorGrid.Enabled;
xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines = chart.ChartAreas[0].AxisX.MinorGrid.Enabled;
if (xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines)
{
xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).MajorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisX.MajorGrid);
}
if (xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines)
{
xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).MinorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisX.MinorGrid);
}
progress += 4;
xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = true;
xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = chart.ChartAreas[0].AxisY.Title;
xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = chart.ChartAreas[0].AxisY.MajorGrid.Enabled;
xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines = chart.ChartAreas[0].AxisY.MinorGrid.Enabled;
if (xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines)
{
xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).MajorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisY.MajorGrid);
}
if (xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines)
{
xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).MinorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisY.MinorGrid);
}
progress += 4;
xlChart.HasLegend = true;
progress++;
return(xlChart);
}
/// <summary>
/// 给工作表添加一个图表
/// </summary>
/// <param name="firstRange">数据工作表</param>
/// <param name="xData">x轴数据区域：格式A1:A14</param>
/// <param name="ydata">y轴数据区域：格式B1:E14</param>
/// <param name="chartName">图标名称</param>
/// <param name="xname">y轴名称</param>
/// <param name="yname">y轴名称</param>
/// <param name="chartType">图表类型</param>
public static void createInnerChart(Excel.Worksheet dataSheet, List <ChartStuct> charts, string chartName, string categoryName)
{
Excel.ChartObjects ChartObjects = (Excel.ChartObjects)dataSheet.ChartObjects(Missing.Value);
int cols = 0;
foreach (ChartStuct chartStuct in charts)
{
cols += chartStuct.dataRange.Columns.Count;
}
Excel.ChartObject chartObject = ChartObjects.Add(cols * 60, 30, 800, 350);
if (charts.Count < 1)
{
return; //无数不处理
}
//绘制第一维图表
Excel.Range firstRange = charts[0].dataRange;
Excel.XlChartType chartType = charts[0].chartType;
string yname = charts[0].yname;
chartObject.Chart.ChartWizard(firstRange, chartType, Missing.Value, Excel.XlRowCol.xlColumns, 1, 1, true, chartName, categoryName, yname, Missing.Value);
chartObject.Chart.ChartArea.ClearFormats();
chartObject.Chart.ChartType = chartType;
chartObject.Chart.ChartArea.Fill.BackColor.SchemeColor = 2;//整个图表区背景
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor);
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB);
chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor = 2;
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB);
//Console.WriteLine(chartObject.Chart.PlotArea.Interior.ColorIndex);
//chartObject.Chart.PlotArea.Interior.PatternColorIndex = 19;
chartObject.Chart.PlotArea.Interior.ColorIndex = 2;//图例区域背景
Excel.Axis firstAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
firstAxis.HasMajorGridlines = true;//不显示横向网格线
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartObject.Chart.SeriesCollection(Type.Missing);
int sc = seriesCollection.Count;
if (seriesCollection.Count > 0)
{
Excel.Series seires1 = (Excel.Series)seriesCollection.Item(1);//具体数据图表区颜色
//seires1.Fill.BackColor.SchemeColor = 18;
seires1.Fill.ForeColor.SchemeColor = 46;
seires1.MarkerForegroundColorIndex = Excel.XlColorIndex.xlColorIndexNone;
seires1.HasLeaderLines = false;
}
//绘制第二维图表
//修改第一维图表的各个序列的图例,这里有个bug，会用最后的标题覆盖前面的
// for (int i = 0; i < charts[0].seriesTitles.Length; i++)
//{
//string stitle = charts[0].seriesTitles[i];
//Excel.Series tmpSeires = (Excel.Series)seriesCollection.Item(1);
//tmpSeires.Name = stitle;
//}
//绘制第二个图表,多维这里设置 当有图表类型为XlChartType.xl3DColumn或其他3d效果时，抛 xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;异常
for (int i = 1; i < charts.Count; i++)
{
ChartStuct cs = charts[i];
string stitle = cs.seriesTitles[0];
Excel.Series xlSeries = (Excel.Series)seriesCollection.NewSeries();
xlSeries.Name = stitle;
xlSeries.HasLeaderLines = true;
xlSeries.Fill.ForeColor.SchemeColor = 46;
try
{
xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;
}
catch (Exception eee) {
Console.WriteLine(eee.Message);
}
xlSeries.ChartType = cs.chartType;
xlSeries.Values = cs.dataRange;
Excel.Axis valueAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary);
valueAxis.HasTitle = true;
valueAxis.AxisTitle.Text = cs.yname;
}
}
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.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;
if (comboBox_FourierMethod.SelectedItem.Equals("Circle"))
{
Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;
}
else if (comboBox_FourierMethod.SelectedItem.Equals("Square"))
{
Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
}
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)((Math.Sqrt(double.Parse(str[i, j]) * ratio) / Max_size) * Min_MarkerSize + 2);
//SquareSize = (int)((double.Parse(str[i, j]) - Min_size) / (Max_size - Min_size) * ratio) + 2;
//SquareSize = (int)((double.Parse(str[i, j]) / (Max_size * Max_size) * ratio) + 2);// - Min_size) / (Max_size - Min_size) * ratio) + 2;
if (SquareSize > 72)
{
SquareSize = 72;
}
point.MarkerSize = SquareSize;
}
}
chart.Refresh();
//worksheet.Activate();
}
// データテーブルをエクセルへエクスポート（箱ヒゲ図付き）
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 BubbleRelationShip()
{
InitializeComponent();
ratio = double.Parse(textBox_Bandwidth.Text);;
Graphic.RangeData(ref str, ref rows, ref cols);
worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell;
start_col = activecells.Column;
start_row = activecells.Row;
int i, j;
for (j = 1; j < cols; j++)
{
((Excel.Range)worksheet.Cells[start_row + rows + 1, start_col + j]).Value2 = str[0, j];
}
for (j = 1; j < rows; j++)
{
((Excel.Range)worksheet.Cells[start_row + rows + 1 + j, start_col]).Value2 = str[j, 0];
}
for (i = 1; i < rows; i++)
{
for (j = 1; j < cols; j++)
{
((Excel.Range)worksheet.Cells[start_row + rows + 1 + i, start_col + j]).Value2 = i;// str[i, 0];
}
}
((Excel.Range)worksheet.Cells[start_row + rows + 1 + rows, start_col]).Value2 = "Assistant1";
for (j = 1; j < rows; j++)
{
((Excel.Range)worksheet.Cells[start_row + rows + 1 + rows, start_col + j]).Value2 = 0;
}
((Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col]).Value2 = "Assistant2";
for (j = 1; j < rows; j++)
{
((Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col + j]).Value2 = j;
}
Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + rows + 1, start_col];
Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col + rows - 1];
string ChartOrder = "BubbleRelationShip" + Convert.ToString(Nchart);
chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder);
Nchart = Nchart + 1;
chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlRows);
chart.ChartType = Excel.XlChartType.xlXYScatter;
//double ratio = 21;
//double Max_size = 0;
Max_size = Double.MinValue;
Min_size = Double.MaxValue;
for (i = 1; i < rows; i++)
{
for (j = 1; j < cols; j++)
{
if (double.Parse(str[i, j]) > Max_size)
{
Max_size = double.Parse(str[i, j]);
}
if (double.Parse(str[i, j]) < Min_size)
{
Min_size = double.Parse(str[i, j]);
}
}
}
Max_size = Math.Sqrt(Max_size);
Min_size = Math.Sqrt(Min_size);
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Point point;
Excel.Series Sseries;
int SquareSize;
RGB0 = System.Drawing.Color.FromArgb(255, 248, 118, 109);
for (i = 1; i < rows; i++)
{
Sseries = series.Item(i);
Sseries.Format.Line.Visible = Office.MsoTriState.msoFalse;
//if (comboBox_FourierMethod.SelectedItem.Equals("Circle"))
//{
Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;
//}
//else if (comboBox_FourierMethod.SelectedItem.Equals("Square"))
//{
// Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
//}
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;
//Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;
for (j = 1; j < cols; j++)
{
point = (Excel.Point)Sseries.Points(j);
//SquareSize = (int)((double.Parse(str[i, j])-Min_size) / (Max_size-Min_size) * ratio) + 2;
SquareSize = (int)((Math.Sqrt(double.Parse(str[i, j]) * ratio) / Max_size) * Min_MarkerSize + 2); // - Min_size) / (Max_size - Min_size) * ratio) + 2;
//SquareSize = (int)((double.Parse(str[i, j]) / (Max_size * Max_size) * ratio) + 2);// - Min_size) / (Max_size - Min_size) * ratio) + 2;
if (SquareSize > 72)
{
SquareSize = 72;
}
point.MarkerSize = SquareSize;
}
}
Excel.Series SseriesX = series.Item(rows);
for (j = 1; j < cols; j++)
{
point = (Excel.Point)SseriesX.Points(j);
point.HasDataLabel = true;
point.DataLabel.Text = str[0, j];
point.DataLabel.Position = Excel.XlDataLabelPosition.xlLabelPositionBelow;
point.DataLabel.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
point.DataLabel.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
point.DataLabel.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
point.DataLabel.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
point.DataLabel.Format.TextFrame2.TextRange.Font.Size = 10;
point.DataLabel.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse;
}
SseriesX.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
Excel.Series SseriesY = series.Item(rows + 1);
c1 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows, start_col + +1];
c2 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows, start_col + rows - 1];
SseriesY.XValues = worksheet.get_Range(c1, c2);
c1 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col + 1];
c2 = (Excel.Range)worksheet.Cells[start_row + rows + 1 + rows + 1, start_col + rows - 1];
SseriesY.Values = worksheet.get_Range(c1, c2);
SseriesY.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
for (j = 1; j < rows; j++)
{
point = (Excel.Point)SseriesY.Points(j);
point.HasDataLabel = true;
point.DataLabel.Text = str[j, 0];
point.DataLabel.Position = Excel.XlDataLabelPosition.xlLabelPositionLeft;
point.DataLabel.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
point.DataLabel.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
point.DataLabel.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
point.DataLabel.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
point.DataLabel.Format.TextFrame2.TextRange.Font.Size = 10;
point.DataLabel.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse;
}
//***************************************************** Style**********************************************
chart.PlotArea.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
chart.PlotArea.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(229, 229, 229).ToArgb();
chart.PlotArea.Format.Fill.Transparency = 0;
chart.PlotArea.Format.Line.Visible = Office.MsoTriState.msoFalse;
// ChartArea Line
chart.ChartArea.Format.Line.Visible = Office.MsoTriState.msoFalse;
//chart.ChartArea.Height = 340.157480315;
//chart.ChartArea.Width = 380.5039370079;
// Chart Type: XYScatter
// Add GridLinesMinorMajor
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryValueGridLinesMinorMajor);
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryCategoryGridLinesMinorMajor);
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis);
chart.SetElement(Office.MsoChartElementType.msoElementPrimaryCategoryAxisTitleAdjacentToAxis);
//y axis
Excel.Axis axis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
axis.MinorUnit = 1;
axis.MajorUnit = 1;
axis.MajorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue;
axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
axis.MajorGridlines.Format.Line.Weight = (float)0.25;
axis.MajorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.MinorGridlines.Format.Line.Visible = Office.MsoTriState.msoFalse;
//axis.MinorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(242, 242, 242).ToArgb();
//axis.MinorGridlines.Format.Line.Weight = (float)0.25;
//axis.MinorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.Format.Line.Visible = Office.MsoTriState.msoFalse;
axis.HasTitle = true;
axis.AxisTitle.Text = "y axis";
axis.HasDisplayUnitLabel = false;
//axis.TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
//axis.TickLabels.Font.Name = "Times New Roman";
//axis.TickLabels.Font.Size = 10;
axis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionNone;
//axis.AxisTitle.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.Size = 10;
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse;
//x axis
axis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
axis.MaximumScale = cols;
axis.MinimumScale = 0;
//axis.AxisBetweenCategories = false;
axis.MinorUnit = 1;
axis.MajorUnit = 1;
axis.MinorGridlines.Format.Line.Visible = Office.MsoTriState.msoFalse;
//axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
//axis.MajorGridlines.Format.Line.Weight = (float)1.25;
//axis.MajorGridlines.Format.Line.DashStyle = Microsoft.Office.Core.MsoLineDashStyle.msoLineSolid;
axis.MajorGridlines.Format.Line.Visible = Office.MsoTriState.msoCTrue;
axis.MajorGridlines.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
axis.MajorGridlines.Format.Line.Weight = (float)0.25;
axis.MajorGridlines.Format.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
axis.Format.Line.Visible = Office.MsoTriState.msoFalse;
axis.HasTitle = true;
axis.AxisTitle.Text = "x axis";
//axis.TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
//axis.TickLabels.Font.Name = "Times New Roman";
//axis.TickLabels.Font.Size = 10;
axis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionNone;
//axis.AxisTitle.Format.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameComplexScript = "Times New Roman";
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.NameFarEast = "Times New Roman";
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.Name = "Times New Roman";
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.Size = 10;
//axis.AxisTitle.Format.TextFrame2.TextRange.Font.Bold = Office.MsoTriState.msoFalse;
chart.HasLegend = false;
chart.HasTitle = false;
//chart.ChartTitle.Delete();
chart.Refresh();
//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();
}
private void button_OK_Click(object sender, EventArgs e)
{
height = int.Parse(textBox_height.Text);
int width = (int)Math.Ceiling(Convert.ToDouble(rows) / Convert.ToDouble(height));
int[,] data = new int[height, width];
int[] temp = new int[width];
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
int N_Ser = series.Count;
Excel.Series Sseries;
if (N_Ser > height)
{
for (int i = N_Ser; i > height; i--)
{
series.Item(i).Delete();
}
}
else
{
for (int i = N_Ser + 1; i <= height; i++)
{
Sseries = series.NewSeries();
}
}
Excel.Point point;
int RGB_B, RGB_G, RGB_R;
for (int i = 0; i < height; i++)
{
temp = new int[width];
for (int j = 0; j < width; j++)
{
temp[j] = 1;
data[i, j] = 1;
}
//Sseries = series.NewSeries();
series.Item(i + 1).Values = temp;
}
for (int i = 1; i <= height; i++)
{
Sseries = series.Item(i);
for (int j = 1; j <= width; j++)
{
{
point = (Excel.Point)Sseries.Points(j);
point.Format.Fill.Solid();
point.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
point.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb();
int index = (i - 1) * width + j;
if (index < rows)
{
RGB_B = Convert.ToInt32(double.Parse(str[index, cols - 1])); if (RGB_B < 0)
{
RGB_B = 0;
}
if (RGB_B > 255)
{
RGB_B = 255;
}
RGB_G = Convert.ToInt32(double.Parse(str[index, cols - 2])); if (RGB_G < 0)
{
RGB_G = 0;
}
if (RGB_G > 255)
{
RGB_G = 255;
}
RGB_R = Convert.ToInt32(double.Parse(str[index, cols - 3])); if (RGB_R < 0)
{
RGB_R = 0;
}
if (RGB_R > 255)
{
RGB_R = 255;
}
point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB_B, RGB_G, RGB_R).ToArgb();
point.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb();
}
else
{
point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb();
point.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb();
}
}
}
}
}
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 Excel.Chart F_Chart; // VBConversions Note: Initial value cannot be assigned here since it is non-static. Assignment has been moved to the class constructors.
/// <summary>
/// 当在数据列上双击时执行相应的操作
/// </summary>
/// <param name="ElementID">在图表上双击击中的对象</param>
/// <param name="Arg1">所选数据系列在集合中的索引下标值，注意，第一第曲线的下标值为1，而不是0。</param>
/// <param name="Arg2"></param>
/// <param name="Cancel"></param>
/// <remarks>要么将其删除，要么将其锁定在图表中，要么什么都不做</remarks>
private void SeriesChange(int ElementID, int Arg1, int Arg2, ref
bool Cancel)
{
if (ElementID == (int)Excel.XlChartItem.xlSeries)
{
Debug.Print("当前选择的曲线下标值为： " + System.Convert.ToString(Arg1));
//所选数据系列在集合中的索引下标值，注意，第一第曲线的下标值为1，而不是0
int seriesIndex = Arg1;
//所选的数据系列
Excel.Series seri;
Excel.SeriesCollection seriColl = this.Chart.SeriesCollection() as Excel.SeriesCollection;
seri = seriColl.Item(seriesIndex);
// -------- 打开处理对话框，并根据返回的不同结果来执行不同的操作
DiaFrm_LockDelete diafrm = new DiaFrm_LockDelete();
//判断要删除的曲线是否为当前滚动的曲线，有如下两种判断方法
bool blnDeletingTheRollingCurve = false;
blnDeletingTheRollingCurve = seriesIndex == cst_LboundOfSeriesInCollection;
//blnDeletingTheRollingCurve = (seri.Name = F_movingSeries.Name)
if (F_CurvesCount == 1 || blnDeletingTheRollingCurve)
{
//如果图表中只有一条曲线，或者点击的正好是要正在进行滚动的曲线，那么这条曲线不能被删除
diafrm.btn2.Enabled = false;
diafrm.AcceptButton = diafrm.btn1;
}
else //只能进行删除
{
diafrm.btn1.Enabled = false;
diafrm.AcceptButton = diafrm.btn2;
}
//在界面上取消图表对象的选择
this.Sheet_Drawing.Range["A1"].Activate();
this.Application.ScreenUpdating = false;
//---------------------------------- 开始执行数据系列的添加或删除
//此数据列对应的施工日期
DateTime t_date = default(DateTime);
SeriesTag SrTag = default(SeriesTag);
try
{
SrTag = this.F_DicSeries_Tag[seriesIndex];
t_date = SrTag.ConstructionDate;
}
catch (Exception ex)
{
MessageBox.Show("提取字典中的元素出错，字典中没有此元素。" + "\r\n" + ex.Message + "\r\n" + "报错位置：" + ex.TargetSite.Name,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//----------------------------------------
M_DialogResult result = default(M_DialogResult);
string strPrompt = "The date value for the selected series is:" + "\r\n" +
t_date.ToString(AMEApplication.DateFormat) + "\r\n" +
"Choose to Lock or Delete this series...";
result = diafrm.ShowDialog(strPrompt, "Lock or Delete Series");
//----------------------------------------
switch (result)
{
case M_DialogResult.Delete:
//删除数据系列
try
{
DeleteSeries(seriesIndex);
}
catch (Exception ex)
{
Debug.Print("删除数据系列出错。" + "\r\n" + ex.Message + "\r\n" + "报错位置：" + ex.TargetSite.Name);
}
break;
case M_DialogResult.Lock:
//添加数据系列
try
{
CopySeries(seriesIndex);
}
catch (Exception ex)
{
Debug.Print(ex.Message);
MessageBox.Show("添加数据系列出错。" + "\r\n" + ex.Message + "\r\n" + "报错位置：" + ex.TargetSite.Name,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
break;
}
this.Application.ScreenUpdating = true;
}
//覆盖原来的双击操作
Cancel = true;
this.Sheet_Drawing.Range["A1"].Activate();
}