private Excel.Chart MakePolarChart(Excel.Worksheet xlWorkSheet)
{
xlWorkSheet.Range["A1", "A5"].Value2 = 22;
xlWorkSheet.Range["B1", "B5"].Value2 = 55;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartPage.SetSourceData(xlWorkSheet.Range["A1", "B5"],
Excel.XlRowCol.xlColumns);
chartPage.ChartType = Excel.XlChartType.xlRadar;
Excel.ChartGroup group =
(Excel.ChartGroup)chartPage.RadarGroups(1);
group.HasRadarAxisLabels = true;
return(chartPage);
}
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();
}
// データテーブルをエクセルへエクスポート（ＸＲ管理図・ヒストグラム付き）
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 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();
}
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 ColumnThreshold()
{
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 = "ColumnThreshold" + 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.xlColumnStacked;
//*****************************************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;
Excel.ChartGroup group = (Excel.ChartGroup)chart.ChartGroups(1);
group.Overlap = 100;
group.GapWidth = 0;
chart.Refresh();
//worksheet.Activate();
flag = 1;
}
