public void FillExcel <T>(string name, IEnumerable <T> values, double step)
{
Excel.Application ex = new Excel.Application();
ex.Visible = true;
ex.SheetsInNewWorkbook = 1;
var workBook = ex.Workbooks.Add(Type.Missing);
var sheet = (Excel.Worksheet)ex.Worksheets.Item[1];
sheet.Name = name;
sheet.Cells[1, 1] = "x";
sheet.Cells[1, 2] = "y";
for (int i = 1; i < values.Count() + 1; i++)
{
sheet.Cells[i + 1, 1] = step * (i - 1);
sheet.Cells[i + 1, 2] = values.ElementAt(i - 1);
}
Excel.ChartObjects xlCharts = (Excel.ChartObjects)sheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(110, 0, 350, 250);
Excel.Chart chart = myChart.Chart;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.NewSeries();
series.XValues = sheet.get_Range("A2", "A" + (values.Count() + 1).ToString());
series.Values = sheet.get_Range("B2", "B" + (values.Count() + 1).ToString());
chart.ChartType = Excel.XlChartType.xlXYScatterSmooth;
}
/// <summary>
/// 确定Chart的宽度值，以磅为单位
/// </summary>
/// <param name="DrawingChart">进行绘图的Chart</param>
/// <param name="PointsCount">选择的基坑区域的数量</param>
/// <param name="LeastWidth_Chart">Chart对象的宽度的最小值，即使图表中只有很少的点</param>
/// <param name="LeastWidth_Column">柱形图中每一个柱形的最小宽度，用来进行基本的文本书写</param>
/// <returns></returns>
/// <remarks></remarks>
private double GetChartWidth(Microsoft.Office.Interop.Excel.Chart DrawingChart, UInt16 PointsCount, double
LeastWidth_Chart, double LeastWidth_Column, double Margin, ref
double InsideWidth)
{
double ChartWidth = LeastWidth_Chart;
InsideWidth = LeastWidth_Chart - Margin;
//
Microsoft.Office.Interop.Excel.Chart with_1 = DrawingChart;
Microsoft.Office.Interop.Excel.SeriesCollection src = with_1.SeriesCollection();
UInt16 n = src.Count;
double hh = LeastWidth_Chart / PointsCount;
ChartGroup ChtGroup = with_1.ChartGroups(1);
// ------------------------------------------------------------------------------------------------
//1、在已知最小的chart宽度(即最小的PlotArea.InsideWidth)的情况下，验算柱形的宽度是否比指定的最小柱形宽度要大
double H = InsideWidth;
// Dim O As Single = ChtGroup.Overlap / 100
ChtGroup.Overlap = 0;
float G = (float)((double)ChtGroup.GapWidth / 100);
double ColumnWidth = hh / (1 + G + n);
if (ColumnWidth < LeastWidth_Column)
{
// ------------------------------------------------------------------------------------------------
//2、在已知柱体的最小宽度的情况下，去推算整个PlotArea.InsideWidth的值
}
return(ChartWidth);
}
protected void CreateGraph(int columnIndex)
{
// Get the alphabetical column index...
string columnAlphabetical = ((char)(columnIndex + 64)).ToString( );
// Activate the test Chart worksheet...
this.testChartWorkSheet.Activate( );
// Get any charts currently existing in the worksheet...
Excel.ChartObjects charts =
(Excel.ChartObjects) this.testChartWorkSheet.ChartObjects(Type.Missing);
// Adds a chart at x = 100, y = 300, 500 points wide and 300 tall.
Excel.ChartObject chartObj = (Excel.ChartObject)charts.Item(columnIndex - 1);
Excel.Chart chart = chartObj.Chart;
// Gets the data cells ( X values )
Excel.Range chartRange = this.testWorkSheet.get_Range((columnAlphabetical + "2"), (columnAlphabetical + (rows).ToString( )));
chart.SetSourceData(chartRange, Type.Missing);
// Set the type of the chart...
chart.ChartType = Excel.XlChartType.xlLine;
// Get any existing series collection (there should be only one)...
Excel.SeriesCollection seriesCollection =
(Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.Item(seriesCollection.Count);
// Gets the data cells ( Y values )
series.XValues = this.testWorkSheet.get_Range("A2", "A" + (rows).ToString( ));
}
private void ButtonApply_Click(object sender, RoutedEventArgs e)
{
Excel.Worksheet workSheet2 = (Excel.Worksheet)_workBook.Sheets[2];
Excel.Worksheet workSheet3 = (Excel.Worksheet)_workBook.Sheets[3];
int rowIndex = 0;
for (int x = 0; x <= 10; x++)
{
workSheet2.Cells[3, "F"].Value = x;
double y = workSheet2.Cells[6, "F"].Value;
workSheet3.Cells[rowIndex + 1, "H"].Value = x;
workSheet3.Cells[rowIndex + 1, "I"].Value = y;
rowIndex++;
}
Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet3.ChartObjects(Type.Missing);
Excel.ChartObject myChart = chartObjs.Add(20, 60, 200, 200);
Excel.Chart chart = myChart.Chart;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.NewSeries();
series.XValues = workSheet3.get_Range("H1", "H10");
series.Values = workSheet3.get_Range("I1", "I10");
chart.ChartType = Excel.XlChartType.xlXYScatterSmooth;
}
void elementChart_MouseDown(int Button, int Shift, int x, int y)
{
if (flag == 1)
{
Excel.Chart chart = Globals.ThisAddIn.Application.ActiveChart;
//Int32 ;
Int32 elementID = 0;
Int32 arg1 = 0;
Int32 arg2 = 0;
chart.GetChartElement(x, y, ref elementID, ref arg1, ref arg2);
//
string element = ((Excel.XlChartItem)elementID).ToString();
if (element == "xlSeries")
{
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Series Sseries = series.Item(arg1);
Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(SelectRGB[2], SelectRGB[1], SelectRGB[0]).ToArgb();
Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(SelectRGB[2], SelectRGB[1], SelectRGB[0]).ToArgb();
flag = 0;
}
}
// MessageBox.Show("Chart element is: " + ((Excel.XlChartItem)elementID).ToString()
// + "\n arg1 is: " + arg1.ToString() + "\n arg2 is: " + arg2.ToString());
}
public void Excel_Create(string file_name, int latin, int kiril, int num)
{
exel.Application application = new exel.Application();
exel.Workbook workbook = application.Workbooks.Add();
exel.Worksheet worksheet = (exel.Worksheet)workbook.ActiveSheet;
worksheet.Name = file_name;
worksheet.Cells[1, 1] = "Латинские буквы";
worksheet.Cells[2, 1] = "Кириллица";
worksheet.Cells[3, 1] = "Числа";
worksheet.Cells[1, 2] = latin;
worksheet.Cells[2, 2] = kiril;
worksheet.Cells[3, 2] = num;
worksheet.Columns.AutoFit();
exel.ChartObjects chartObjects = (exel.ChartObjects)worksheet.ChartObjects(Type.Missing);
exel.ChartObject chartObject = chartObjects.Add(50, 50, 250, 250);
exel.Chart chart = chartObject.Chart;
exel.SeriesCollection seriesCollection = (exel.SeriesCollection)chart.SeriesCollection(Type.Missing);
exel.Series series = seriesCollection.NewSeries();
chart.ChartType = exel.XlChartType.xl3DPie;
series.XValues = worksheet.get_Range("A1", "A3");
series.Values = worksheet.get_Range("B1", "B3");
workbook.SaveAs();
workbook.Close();
application.Quit();
}
public void AddChart(Tools.Worksheet worksheet, Excel.Range range, Template template)
{
var charts = worksheet.Application.ActiveWorkbook.Charts;
Tools.Chart chart;
if (worksheet.Controls.Contains("chart"))
{
chart = (Tools.Chart)worksheet.Controls["chart"];
Excel.SeriesCollection sc = (Excel.SeriesCollection)chart.SeriesCollection();
while (sc.Count > 0)
{
sc.Item(1).Delete();
}
}
else
{
chart = worksheet.Controls.AddChart(range, "chart");
}
//chart.ChartType = Excel.XlChartType.xlLine;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.NewSeries();
series.Values = GenerateTestData(this.random, 100);
//series.XValues = new string[] { "A", "B", "C", "D" };
series.Name = "Series Name";
chart.ApplyChartTemplate(TemplateDictionary[template]);
}
/// <summary>
/// Creates a new graph in a new excel workbook. Values is a list of data series, where a series is a list of X,Y value pairs.
/// </summary>
/// <param name="values"></param>
/// <param name="seriesTitle"></param>
/// <param name="xAxis"></param>
/// <param name="yAxis"></param>
/// <param name="headers"></param>
public static void PlotEachSeriesSeperately(List <List <double[]> > values, string seriesTitle = "",
string xAxis = "", string yAxis = "", List <string> headers = null)
{
//Create a new excel workbook and sheet
Excel.Application xlApp;
Excel.Workbook xlWorkbook;
Excel.Worksheet xlWorksheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Add(misValue);
xlWorksheet = xlWorkbook.Worksheets.get_Item(1);
//Export data to Excel Sheet from List<List<double>>
//Set headers if given.
if (headers != null)
{
for (var i = 0; i < headers.Count; i++)
{
xlWorksheet.Cells[1, i].Value = headers[i];
}
}
//Create Chart
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorksheet.ChartObjects(Type.Missing);
for (var i = 0; i < values.Count; i++) //For each series
{
var xValues = new double[values[i].Count];
var yValues = new double[values[i].Count];
for (var j = 0; j < values[i].Count; j++) //For each point in series
{
xValues[j] = values[i][j][0];
yValues[j] = values[i][j][1];
}
//Create a new chart and offset from previous
var offset = 300 * (i);
Excel.ChartObject myChart = xlCharts.Add(200, 30 + offset, 400, 300);
Excel.Chart chart = myChart.Chart;
chart.ChartType = Excel.XlChartType.xlXYScatterLines;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Series series = seriesCollection.NewSeries();
series.Values = yValues;
series.XValues = xValues;
var title = seriesTitle + " " + i;
chart.ChartWizard(
Title: title,
CategoryTitle: xAxis,
ValueTitle: yAxis);
}
xlApp.Visible = true;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
}
public void ExcelDiagr2()
{
Excel.Application excelapp = new Excel.Application();
try
{
// excelapp.Visible = true;
Excel.Workbooks excelappworkbooks = excelapp.Workbooks;
Excel.Workbook excelappworkbook = excelapp.Workbooks.Open("book.xlsx",
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Sheets excelsheets = excelappworkbook.Worksheets;
//excelappworkbook = excelappworkbooks[2];
//excelappworkbook.Activate();
//excelsheets = excelapp.ActiveWorkbook.Worksheets;
Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
excelworksheet.Activate();
//Excel.Worksheet excelworksheet = (Excel.Worksheet)excelappworkbook.Sheets[2];
Excel.Range excelcells = excelworksheet.get_Range("B1", "B12");
excelcells.Select();
Excel.Chart excelchart = (Excel.Chart)excelapp.Charts.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
excelapp.ActiveChart.HasTitle = true;
excelapp.ActiveChart.ChartTitle.Text = "Количество заказов";
DataForBD.iter = 5;
((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary)).HasTitle = true;
((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Месяц";
((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary)).HasTitle = true;
((Excel.Axis)excelapp.ActiveChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Количество";
Excel.SeriesCollection seriesCollection =
(Excel.SeriesCollection)excelapp.ActiveChart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.Item(1);
series.Name = "Количество заказов";
//excelworksheet.Name = "Количество заказов";
excelapp.DisplayAlerts = false;
excelappworkbook.SaveAs("book.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excelapp.Quit();
}
catch (Exception exc)
{
excelapp.Quit();
throw exc;
}
}
private void CreateChart(int length, Worksheet excelWorkSheet)
{
ChartObjects xlCharts = (ChartObjects)excelWorkSheet.ChartObjects(Type.Missing);
ChartObject myChart = (ChartObject)xlCharts.Add(300, 0, length / 1.5, 350);
Microsoft.Office.Interop.Excel.Chart chart = myChart.Chart;
Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Microsoft.Office.Interop.Excel.Series series = seriesCollection.NewSeries();
series.XValues = excelWorkSheet.get_Range("B2", "B" + (length + 1));
series.Values = excelWorkSheet.get_Range("C2", "C" + (length + 1));
series.Name = "Fluctuation";
chart.ChartType = XlChartType.xlXYScatterSmooth;
}
private void DrawFractionChart(Excel.Worksheet activeSheet, Excel.ChartObjects xlCharts, Excel.Range xRange, Excel.Range yRange)
{
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(200, 500, 200, 100);
Excel.Chart chartPage = myChart.Chart;
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
series1.XValues = activeSheet.Range["E1", "E3"];
series1.Values = activeSheet.Range["F1", "F3"];
chartPage.ChartType = Excel.XlChartType.xlDoughnut;
Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis;
series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent, true, true, false, true, true, true, true);
}
/// <summary>
/// 开始绘制开挖剖面图的Chart对象
/// </summary>
/// <returns>进行绘图的Chart对象的高度值，以磅为单位，可以用来确定Excel Application的高度值</returns>
/// <remarks>绘图时，标高值与Excel中的Y坐标值的对应关系：
/// 在程序中，定义了eleTop变量（以米为单位）与F_sngTopRef变量（以磅为单位），
/// 它们指示的是此基坑区域中，地下室顶板的标高值与其在Excel绘图中对应的Y坐标值</remarks>
private Microsoft.Office.Interop.Excel.Chart DrawChart(Microsoft.Office.Interop.Excel.Worksheet DrawingSheet,
List <clsData_ProcessRegionData> SelectedRegion)
{
Microsoft.Office.Interop.Excel.Application DrawingApp = DrawingSheet.Application;
DrawingApp.ScreenUpdating = false;
DrawingApp.Caption = "开挖标高图";
//---------------- 创建一个新的，进行绘图的Chart对象 -------------------------------
Excel.Chart DrawingChart = default(Excel.Chart);
DrawingChart = DrawingSheet.Shapes.AddChart(Top: 0, Left: 0).Chart;
string TemplatePath = System.IO.Path.Combine(System.Convert.ToString(My.Settings.Default.Path_Template),
Constants.FolderOrFileName.File_Template.Chart_Elevation);
DrawingChart.Parent.Activate();
DrawingChart.ApplyChartTemplate(TemplatePath);
this.F_Textbox_Info = DrawingChart.Shapes.Item(1).TextFrame2;
DrawingChart.ChartTitle.Text = "开挖标高图";
//
Microsoft.Office.Interop.Excel.SeriesCollection src = DrawingChart.SeriesCollection();
for (short i = 0; i <= 1 - src.Count; i++) //确保Chart中至少有两个数据系列
{
src.NewSeries();
}
// ----------------------- 设置绘图及Excel窗口的尺寸 ----------------------------
double ChartHeight = 400;
double InsideLeft = 60;
double InsideRight = 20;
double LeastWidth_Chart = 500;
double LeastWidth_Column = 100;
//
double ChartWidth = LeastWidth_Chart;
double insideWidth = LeastWidth_Chart - InsideLeft - InsideRight;
//
ChartWidth = GetChartWidth(DrawingChart, SelectedRegion.Count,
LeastWidth_Chart, LeastWidth_Column,
InsideLeft + InsideRight, ref insideWidth);
ChartSize Size_Chart_App = new ChartSize(ChartHeight,
ChartWidth,
26,
9);
ExcelFunction.SetLocation_Size(Size_Chart_App, DrawingChart, DrawingChart.Application, true);
//With DrawingChart.PlotArea
// .InsideLeft = InsideLeft
// .InsideWidth = insideWidth
//End With
// --------------------------------------------------
return(DrawingChart);
}
private void button1_Click(object sender, EventArgs e)
{
colorDialog1.ShowDialog();
RGB0 = colorDialog1.Color;
button_ColorSelection.BackColor = RGB0;
button_ColorSelection.ForeColor = RGB0;
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Point point;
Excel.Series Sseries;
int SquareSize;
//RGB0 = System.Drawing.Color.FromArgb(255, 109, 118, 248);
for (int i = 1; i < rows; i++)
{
Sseries = series.Item(i);
Sseries.Format.Line.Visible = Office.MsoTriState.msoFalse;
Sseries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;
Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb();
Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb();
Sseries.Format.Fill.Solid();
Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb();
Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB0.B, RGB0.G, RGB0.R).ToArgb();
Sseries.Format.Fill.Transparency = 0.2F;
for (int j = 1; j < cols; j++)
{
point = (Excel.Point)Sseries.Points(j);
//SquareSize = (int)(double.Parse(str[i, j]) / Max_size * ratio) + 2;
SquareSize = (int)((Math.Sqrt(double.Parse(str[i, j]) * ratio) / Max_size) * Min_MarkerSize + 2);
//SquareSize = (int)((double.Parse(str[i, j]) / Max_size * ratio) + 2);// - Min_size) / (Max_size - Min_size) * ratio) + 2;
if (SquareSize > 72)
{
SquareSize = 72;
}
point.MarkerSize = SquareSize;
}
}
chart.Refresh();
worksheet.Activate();
}
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e)
{
if (textBox_Bandwidth.Text == "")
{
return;
}
Hrange = double.Parse(textBox_Bandwidth.Text);
if (Hrange == 0)
{
return;
}
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Series Sseries = series.Item(1);
Excel.Point point;
System.Drawing.Color RGB1 = System.Drawing.Color.FromArgb(255, 96, 157, 202);
int[] HSV0 = new int[3];
Graphic.RGB2HSV(RGB0, ref HSV0);
int[] HSV = new int[3];
HSV0.CopyTo(HSV, 0);
double ratio;
for (int i = 1; i < rows; i++)
{
point = (Excel.Point)Sseries.Points(i);
point.Format.Fill.Solid();
point.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
point.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb();
ratio = (double.Parse(str[i, 2]) - Min_Value) / (Max_Value - Min_Value);
HSV[2] = HSV0[2] + Convert.ToInt32(Hrange * (ratio - 0.5));
Graphic.HSV2RGB(ref RGB1, HSV);
point.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, RGB1.B, RGB1.G, RGB1.R).ToArgb();
point.Format.Fill.Transparency = 0.0F;
}
chart.Refresh();
worksheet.Activate();
}
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e)
{
if (textBox_Bandwidth.Text == "")
{
return;
}
ratio = double.Parse(textBox_Bandwidth.Text);;
if (ratio == 0)
{
return;
}
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Series Sseries = series.Item(1);
Excel.Point point;
int SquareSize;
for (int i = 1; i < rows; i++)
{
point = (Excel.Point)Sseries.Points(i);
//SquareSize = (int)((Math.Sqrt(double.Parse(str[i, 2]))));// - Min_size) / (Max_size - Min_size) * ratio) + 2;
SquareSize = (int)((Math.Sqrt(double.Parse(str[i, 2])) / Max_size * ratio) + 2);
//point = (Excel.Point)Sseries.Points(i);
//SquareSize = (int)((double.Parse(str[i, 2]) - Min_size) / (Max_size - Min_size) * ratio) + 2;
//PointSize[i - 1, 0] = SquareSize;
//((Excel.Range)worksheet.Cells[Graphic.start_row + i, Graphic.start_col + 3]).Value2 = SquareSize;
point.MarkerSize = SquareSize;
}
Sseries.MarkerBackgroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb();
Sseries.MarkerForegroundColor = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb();
Sseries.Format.Fill.Solid();
Sseries.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
Sseries.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 229, 229, 229).ToArgb();
Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 77, 175, 74).ToArgb();
Sseries.Format.Fill.Transparency = 0.3F;
chart.Refresh();
worksheet.Activate();
}
/// <summary>
/// Обновление Диаграммы
/// </summary>
internal void UpdateDiagramm()
{
Excel.ChartObject shp = SheetUrv11.ChartObjects("Chart 2");
Excel.Chart chartPage = shp.Chart;
Excel.SeriesCollection seriesCol = (Excel.SeriesCollection)chartPage.SeriesCollection();
Excel.FullSeriesCollection fullColl = chartPage.FullSeriesCollection();
Debug.WriteLine(fullColl.Count);
int lastCol = GetLastColumnUrv(SheetUrv11, _rowStart);
int lastRow = GetLastRowUrv11();
int ix = 1;
string letterCost = "G";
fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10";
fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}";
fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}";
for (int col = 9; col <= lastCol; col += 3)
{
Excel.Range cellFirstCost = SheetUrv11.Cells[_rowStart, col];
string text = cellFirstCost.Value?.ToString() ?? "";
if (string.IsNullOrEmpty(text))
{
continue;
}
letterCost = ExcelHelper.GetColumnLetter(cellFirstCost);
ix++;
if (ix > fullColl.Count)
{
seriesCol.NewSeries();
}
fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10";
fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}";
fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}";
}
if (ix < fullColl.Count)
{
for (int i = ix + 1; i <= fullColl.Count; i++)
{
fullColl.Item(i).Delete();
}
}
}
/// <summary>
/// 开始生成整个绘图图表
/// </summary>
/// <param name="SelectedRegion">窗口中所有选择的要进行绘图的基坑区域</param>
/// <remarks></remarks>
private void GenerateChart(List <clsData_ProcessRegionData> SelectedRegion)
{
//列表中选择的基坑区域
int count = System.Convert.ToInt32(SelectedRegion.Count);
if (count > 0)
{
//---------------- 打开用于绘图的Excel程序，并进行界面设计
Microsoft.Office.Interop.Excel.Worksheet DrawingSheet = this.GetDrawingSheet();
Microsoft.Office.Interop.Excel.Application DrawingApp = DrawingSheet.Application;
try
{
//------------------- 在绘图工作表中进行绘图
this.F_DrawingChart = DrawChart(DrawingSheet, SelectedRegion);
// ----------- 绘制数据系列图 ---------------------------
Microsoft.Office.Interop.Excel.SeriesCollection src = this.F_DrawingChart.SeriesCollection();
Series series_DeepestExca = src.Item(1);
Series series_Depth = src.Item(2);
Series[] DataSeries = new Series[2];
DataSeries = SetDataSeries(this.F_DrawingChart, series_DeepestExca, series_Depth, SelectedRegion);
//-------------------------------------------------------
DateSpan date_Span = GetDateSpan(SelectedRegion);
//-------------------------------------------------------
ClsDrawing_ExcavationElevation shtEle =
new ClsDrawing_ExcavationElevation(series_DeepestExca, series_Depth, SelectedRegion, date_Span,
this.F_Textbox_Info, DrawingType.Xls_SectionalView);
}
catch (Exception ex)
{
MessageBox.Show("绘制基坑区域开挖标高图失败！" + "\r\n" + ex.Message + "\r\n" + "报错位置：" + ex.TargetSite.Name,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
//------- Excel的界面美化 --------------------
ExcelAppBeauty(DrawingApp);
}
}
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="DataSheet">图表对应的数据工作表</param>
/// <param name="DrawingChart">Excel图形所在的Chart对象</param>
/// <param name="ParentApp">此图表所在的Excel类的实例对象</param>
/// <param name="type">此图表所属的类型，由枚举drawingtype提供</param>
/// <param name="CanRoll">是图表是否可以滚动，即是动态图还是静态图</param>
/// <param name="DateSpan">此图表的TimeSpan跨度</param>
/// <remarks></remarks>
public clsDrawing_Mnt_RollingBase(Excel.Worksheet DataSheet, Excel.Chart
DrawingChart, Cls_ExcelForMonitorDrawing ParentApp,
DrawingType type, bool CanRoll, Excel.TextFrame2 Info,
MonitorInfo DrawingTag, MntType MonitorType,
DateSpan DateSpan, SeriesTag
theFirstSeriesTag) : base(DataSheet, DrawingChart, ParentApp, type, CanRoll, Info, DrawingTag, MonitorType)
{
// VBConversions Note: Non-static class variable initialization is below. Class variables cannot be initially assigned non-static values in C#.
F_Chart = this.Chart;
//
this.DateSpan = DateSpan;
//刷新滚动窗口的列表框的界面显示
APPLICATION_MAINFORM.MainForm.Form_Rolling.OnRollingDrawingsRefreshed();
//启用主界面的程序滚动按钮
APPLICATION_MAINFORM.MainForm.MainUI_RollingObjectCreated();
//--------------------------- 设置与数据系列的曲线相关的属性值
clsDrawing_Mnt_RollingBase with_1 = this;
//以数据列中第一个元素作为进行滚动的那个series
with_1.MovingSeries = theFirstSeriesTag.series;
// ----- 集合数据的记录
with_1.F_DicSeries_Tag.Add(cst_LboundOfSeriesInCollection, theFirstSeriesTag);
//刚开始时，图表中只有一条数据曲线
with_1.F_CurvesCount = 1;
//
this.F_List_HasCurve.Clear();
this.F_List_HasCurve.Add(true); //第一个数据列是有曲线的，所以将其值设置为true
Excel.SeriesCollection seriesColl = Chart.SeriesCollection() as Excel.SeriesCollection;
for (var i = 1; i <= seriesColl.Count - 1; i++)
{
this.F_List_HasCurve.Add(false);
}
// -----对图例进行更新---------
LegendRefresh(F_List_HasCurve);
}
/// <summary>
/// Initialize Excel Application
/// </summary>
/// <param name="sheetname">Desired Sheetname</param>
/// <param name="visible">Determine whether Excel-Sheet is visible or not</param>
public static void InitializeExcel(string sheetname, bool visible = true)
{
// Create a new Excel Application
_app = new Excel.Application();
_app.Visible = visible;
// Create a new, empty workbook and add it to the collection returned
_workbook = _app.Workbooks.Add(Missing.Value);
_workbook.Worksheets.Item[1].Name = sheetname;
// Initialize _mainChart.
var worksheet = _workbook.Worksheets.Item[1] as
Microsoft.Office.Interop.Excel.Worksheet;
var charts = worksheet.ChartObjects() as
Microsoft.Office.Interop.Excel.ChartObjects;
var chartObject = charts.Add(10, 10, 800, 450) as
Microsoft.Office.Interop.Excel.ChartObject;
_mainChart = chartObject.Chart;
_mainChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLinesNoMarkers;
_mainChart.ChartWizard(Title: sheetname);
_mainChartSeriesCollection = _mainChart.SeriesCollection();
}
private void GerarGraficoExcel(String c1, String c2, int tamanho, double left, double top, double width, double height)
{
// Cria um Chart(Gráfico)
Ex.ChartObjects cb = (Ex.ChartObjects)oSheetChart.ChartObjects(Type.Missing);
Ex.ChartObject cbc = (Ex.ChartObject)cb.Add(left, top, width, height);
Ex.Chart cp = cbc.Chart;
Ex.Range valores = oSheet.get_Range(c1, c2);
// Seta o título do gráfico
cp.HasTitle = true;
cp.ChartTitle.Text = "MÉDIA DE TEMPO EM VETORES DE TAMANHO " + tamanho + " EM " + (tamanho <= 1000 ? "NANO" : "MILLI");
cp.ChartTitle.Font.Name = "Arial";
// Seta os nomes das colunas
Ex.SeriesCollection seriesCollection = cp.SeriesCollection();
Ex.Series series = seriesCollection.NewSeries();
series.Values = valores;
series.XValues = oSheet.get_Range("B1", "J1");
// Oculta a legenda da serie
cp.Legend.Clear();
}
/// <summary>
/// Рисует график
public void drawGraph(string filename, int numSheets, string firstCell, string lastCell /*, double[] approxY*/)
{
ObjWorkBooks = ObjWorkExcel.Workbooks;
ObjSheets = ObjWorkBook.Worksheets;
Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)ObjWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject chartsobjrct = chartsobjrcts.Add(1, 360, 400, 250);
Excel.Chart chart = chartsobjrct.Chart;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.NewSeries();
Excel.Trendlines trendlines = (Excel.Trendlines)series.Trendlines(System.Type.Missing);
Excel.Trendline newTrendline = trendlines.Add(Excel.XlTrendlineType.xlPower, 2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, true, Type.Missing);
newTrendline.Select();
chart.Legend.Delete();
series.XValues = ObjWorkSheet.get_Range("E7", "E26");
series.Values = ObjWorkSheet.get_Range("D7", "D26");
chart.ChartType = Excel.XlChartType.xlXYScatter;
}
public void Document_Create(Document_Type type, Document_Format format, string name, DataTable table)
{
Configuration_class configuration_Class = new Configuration_class();
configuration_Class.Document_Configuration_Get();
switch (name != "" || name != null)
{
case true:
switch (format)
{
case Document_Format.Word:
word.Application application = new word.Application();
word.Document document = application.Documents.Add(Visible: true);
try
{
word.Range range = document.Range(0, 0);
document.Sections.PageSetup.LeftMargin = application.CentimetersToPoints((float)Configuration_class.doc_Left_Merge);
document.Sections.PageSetup.RightMargin = application.CentimetersToPoints((float)Configuration_class.doc_Right_Merge);
document.Sections.PageSetup.TopMargin = application.CentimetersToPoints((float)Configuration_class.doc_Top_Merge);
document.Sections.PageSetup.BottomMargin = application.CentimetersToPoints((float)Configuration_class.doc_Bottom_Merge);
range.Text = Configuration_class.Organiztion_Name;
range.ParagraphFormat.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter;
range.ParagraphFormat.SpaceAfter = 1;
range.ParagraphFormat.SpaceBefore = 1;
range.ParagraphFormat.LineSpacingRule = word.WdLineSpacing.wdLineSpaceSingle;
range.Font.Name = "Times New Roman";
range.Font.Size = 12;
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph Document_Name = document.Paragraphs.Add();
Document_Name.Format.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter;
Document_Name.Range.Font.Name = "Times New Roman";
Document_Name.Range.Font.Size = 16;
switch (type)
{
case Document_Type.Report:
Document_Name.Range.Text = "ОТЧЁТ";
break;
case Document_Type Statistic:
Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ";
break;
}
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph statparg = document.Paragraphs.Add();
word.Table stat_table = document.Tables.Add(statparg.Range, table.Rows.Count, table.Columns.Count);
stat_table.Borders.InsideLineStyle = word.WdLineStyle.wdLineStyleSingle;
stat_table.Borders.OutsideLineStyle = word.WdLineStyle.wdLineStyleSingle;
stat_table.Rows.Alignment = word.WdRowAlignment.wdAlignRowCenter;
stat_table.Range.Cells.VerticalAlignment = word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
stat_table.Range.Font.Size = 12;
stat_table.Range.Font.Name = "Times New Roman";
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 1; col <= table.Columns.Count; col++)
{
stat_table.Cell(row, col).Range.Text = table.Rows[row - 1][col - 1].ToString();
}
}
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph Footparg = document.Paragraphs.Add();
Footparg.Range.Text = string.Format("Дата создания \t\t\t{0}", DateTime.Now.ToString("dd.MM.yyyy"));
}
catch
{
}
finally
{
switch (format)
{
case Document_Format.Word:
document.SaveAs2(name, word.WdSaveFormat.wdFormatDocument);
break;
case Document_Format.PDF:
document.SaveAs2(name, word.WdSaveFormat.wdFormatPDF);
break;
}
document.Close();
application.Quit();
}
break;
case Document_Format.Excel:
excel.Application application_ex = new excel.Application();
excel.Workbook workbook = application_ex.Workbooks.Add();
excel.Worksheet worksheet = (excel.Worksheet)workbook.ActiveSheet();
try
{
switch (type)
{
case Document_Type.Report:
worksheet.Name = "Отчет";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString();
}
}
excel.Range border = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[worksheet.Rows.Count + 1][table.Columns.Count + 1]];
border.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString());
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge();
break;
case Document_Type.Statistic:
worksheet.Name = "Статистический отчет";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString();
}
}
excel.Range border1 = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[worksheet.Rows.Count + 1][table.Columns.Count + 1]];
border1.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
border1.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
border1.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString());
excel.ChartObjects chartObjects = (excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
excel.ChartObject chartObject = chartObjects.Add(300, 50, 250, 250);
excel.Chart chart = chartObject.Chart;
excel.SeriesCollection seriesCollection = (excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
excel.Series series = seriesCollection.NewSeries();
chart.ChartType = excel.XlChartType.xl3DColumn;
series.XValues = worksheet.get_Range("B2", "B" + table.Rows.Count + 1);
series.Values = worksheet.get_Range("C2", "C" + table.Rows.Count + 1);
break;
}
}
catch
{
}
finally
{
workbook.SaveAs(name, application_ex.DefaultSaveFormat);
workbook.Close();
application_ex.Quit();
}
break;
}
break;
case false:
System.Windows.Forms.MessageBox.Show("Введите название документа");
break;
}
}
public static void CreateChart(ref Excel.Worksheet ws)
{
Excel.Workbook wb;
Excel.Chart chart;
Excel.ChartObject chartobject;
wb = (Excel.Workbook)ws.Parent;
long lastrow = ws.UsedRange.Rows.Count;
long lastcol = ws.UsedRange.Columns.Count;
//Console.WriteLine("lastrow: {0}, lastcol: {1}", lastrow, lastcol);
if (lastrow <= 2)
{
Console.WriteLine("有效数据不足");
return;
}
// delete old chart
if (((Excel.ChartObjects)ws.ChartObjects()).Count >= 1)
{
//Console.WriteLine("Chart num: {0}", ((Excel.ChartObjects)ws.ChartObjects()).Count);
((Excel.ChartObjects)ws.ChartObjects()).Delete();
}
for (int col = 2; col <= lastcol; col++)
{
if (((Excel.Range)ws.Cells[1, col]).Value == null)
{
break;
}
List <double> data = new List <double>();
for (int row = 2; row <= lastrow; row++)
{
double?d = ((Excel.Range)ws.Cells[row, col]).Value;
if (d != null)
{
data.Add((double)d);
}
}
double mean = data.Average();
double sd = data.StandardDeviation();
double cv = sd / mean;
string chartTitle = string.Format("{0} {1} 累积均值: {2:F2} 累积标准差: {3:F2} CV: {4:P2}", ws.Name, ((Excel.Range)ws.Cells[1, col]).Value, mean, sd, cv);
Console.WriteLine(chartTitle);
//固定均值，标准差设置
ws.Range["J1"].Offset[0, col - 2].Value = ws.Cells[1, col].Value;
ws.Range["I2"].Value = "固定均值";
ws.Range["I3"].Value = "固定标准差";
if (ws.Range["J2"].Offset[0, col - 2].Value != null)
{
mean = ws.Range["J2"].Offset[0, col - 2].Value;
}
else
{
Console.WriteLine("未设置{0}的固定均值", ws.Cells[1, col].Value);
}
if (ws.Range["J3"].Offset[0, col - 2].Value != null)
{
sd = ws.Range["J3"].Offset[0, col - 2].Value;
}
else
{
Console.WriteLine("未设置{0}的固定标准差", ws.Cells[1, col].Value);
}
double[] meanarray = new double[lastrow - 1];
double[] plusOneSDarray = new double[lastrow - 1];
double[] plusTwoSDarray = new double[lastrow - 1];
double[] plusThreeSDarray = new double[lastrow - 1];
double[] minusOneSDarray = new double[lastrow - 1];
double[] minusTwoSDarray = new double[lastrow - 1];
double[] minusThreeSDarray = new double[lastrow - 1];
// 初始化
for (int i = 0; i < lastrow - 1; i++)
{
meanarray[i] = mean;
plusOneSDarray[i] = mean + sd;
plusTwoSDarray[i] = mean + 2 * sd;
plusThreeSDarray[i] = mean + 3 * sd;
minusOneSDarray[i] = mean - sd;
minusTwoSDarray[i] = mean - 2 * sd;
minusThreeSDarray[i] = mean - 3 * sd;
}
// Create a new chart
chart = (Excel.Chart)wb.Charts.Add();
Excel.Range XRng = ws.Range[ws.Cells[2, 1], ws.Cells[lastrow, 1]];
Excel.Range dataRng = ws.Range[ws.Cells[1, col], ws.Cells[lastrow, col]];
chart.SetSourceData(dataRng, Excel.XlRowCol.xlColumns);
chart.ChartType = Excel.XlChartType.xlLineMarkers;
chart.HasLegend = false;
chart.HasTitle = true;
chart.ChartTitle.Text = chartTitle;
// 纵坐标范围
string min = (mean - 3.75 * sd) > data.Min() ? data.Min().ToString("F2") : (mean - 3.75 * sd).ToString("F2");
string max = data.Max() > (mean + 3.75 * sd) ? data.Max().ToString("F2") : (mean + 3.75 * sd).ToString("F2");
chart.Axes(Excel.XlAxisType.xlValue).MinimumScale = min;
chart.Axes(Excel.XlAxisType.xlValue).MaximumScale = max;
// Embedding chart on a worksheet
chart.Location(Excel.XlChartLocation.xlLocationAsObject, ws.Name);
// get activate chartobject
chartobject = (Excel.ChartObject)ws.ChartObjects(col - 1);
// 去除网格线
chartobject.Chart.Axes(Excel.XlAxisType.xlValue).HasMajorGridlines = false;
// 位置大小
chartobject.Left = 439;
chartobject.Top = 105 + (col - 2) * 255;
chartobject.Height = 255;
chartobject.Width = 810;
Excel.SeriesCollection se = chartobject.Chart.SeriesCollection();
se.Item(1).XValues = XRng; // 横坐标值
se.Item(1).Format.Line.Weight = 1.2F;
// mean
se.NewSeries();
se.Item(2).Name = "mean";
se.Item(2).Values = meanarray;
se.Item(2).Format.Line.Weight = 1;
se.Item(2).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(2).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbSkyBlue;
// mean + sd
se.NewSeries();
se.Item(3).Name = "mean+sd";
se.Item(3).Values = plusOneSDarray;
se.Item(3).Format.Line.Weight = 1;
se.Item(3).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(3).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlue;
// mean - sd
se.NewSeries();
se.Item(4).Name = "mean-sd";
se.Item(4).Values = minusOneSDarray;
se.Item(4).Format.Line.Weight = 1;
se.Item(4).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(4).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlue;
// mean + 2sd
se.NewSeries();
se.Item(5).Name = "mean+2sd";
se.Item(5).Values = plusTwoSDarray;
se.Item(5).Format.Line.Weight = 1;
se.Item(5).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(5).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbOliveDrab;
// mean - 2sd
se.NewSeries();
se.Item(6).Name = "mean-2sd";
se.Item(6).Values = minusTwoSDarray;
se.Item(6).Format.Line.Weight = 1;
se.Item(6).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(6).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbOliveDrab;
// mean + 3sd
se.NewSeries();
se.Item(7).Name = "mean+3sd";
se.Item(7).Values = plusThreeSDarray;
se.Item(7).Format.Line.Weight = 1;
se.Item(7).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(7).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbIndianRed;
// mean - 3sd
se.NewSeries();
se.Item(8).Name = "mean-3sd";
se.Item(8).Values = minusThreeSDarray;
se.Item(8).Format.Line.Weight = 1;
se.Item(8).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
se.Item(8).Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbIndianRed;
// 修改超过Mean +- 2SD的点的颜色
// 修改超过Mean +- 3SD的点的颜色
double meanplus2sd = mean + 2 * sd;
double meanminus2sd = mean - 2 * sd;
double meanplus3sd = mean + 3 * sd;
double meanminus3sd = mean - 3 * sd;
for (int i = 2; i <= lastrow; i++)
{
Excel.Range rng = (Excel.Range)ws.Cells[i, col];
if (rng.Value == null)
{
continue;
}
if ((double)rng.Value >= meanplus3sd)
{
rng.Interior.Color = 255; // vbred
se.Item(1).Points(i - 1).Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbRed;
}
else if ((double)rng.Value >= meanplus2sd)
{
rng.Interior.Color = 65535; // vbYellow
se.Item(1).Points(i - 1).Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbYellow;
}
else if ((double)rng.Value <= meanminus3sd)
{
rng.Interior.Color = 255;
se.Item(1).Points(i - 1).Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbRed;
}
else if ((double)rng.Value <= meanminus2sd)
{
rng.Interior.Color = 65535;
se.Item(1).Points(i - 1).Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbYellow;
}
}
}
}
private void CreateDocument()
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
app.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(@"C:\LPA_IPAMLx_V2\LPA_IPAM.xlsx",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Worksheet ws = wb.Worksheets[1];
int col = 1;
int row = 16;
ws.Range["B8"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B9"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B10"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B11"].Value = Convert.ToString(replaceData).TrimStart();
GetNotas();
foreach (Notas nota in listaNotas)
{
ws.Cells[row, col].Font.Size = 10;
ws.Cells[row, col].Value = nota.UC.TrimStart();
ws.Cells[row, col + 5].Font.Size = 10;
ws.Cells[row, col + 5].Value = nota.Nota;
ws.Cells[row, col + 6].Font.Size = 10;
ws.Cells[row, col + 6].Value = nota.ECTS;
row++;
}
GetMedia((string)replaceNrAluno, (string)replaceCdCurso);
ws.Range["B59"].Value = (string)replaceMedia + " valores";
ws.Range["B73"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B74"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B75"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B76"].Value = Convert.ToString(replaceData).TrimStart();
Competencias competencias = GetDadosCompetencias((string)replaceNrAluno);
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(5, 1165, 470, 300);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chartPage.SeriesCollection();
var ser = seriesCollection.NewSeries();
chartPage.Legend.Delete();
ser.Values = new double[] { competencias.adaptacao, competencias.trabalhar, competencias.decisoes, competencias.objactivos, competencias.ideias, competencias.aprendizagem, competencias.mentalidadeGlobal, competencias.gestaoEquipas };
ser.XValues = new string[] { "Adaptação", "Trabalhar com os Outros", "Tomar Decisões", "Alcançar Objetivos", "Geração de Ideias", "Aprendizagem", "Mentalidade Global", "Gestão de Equipas" };
//chartRange = ws.get_Range("A1", "d5");
//chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlBarClustered;
chartPage.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(5).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(6).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(7).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(8).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Range["B127"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B128"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B129"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B130"].Value = Convert.ToString(replaceData).TrimStart();
GetLinguas((string)replaceNrAluno);
GetRespAcademicas((string)replaceNrAluno);
GetEstagios((string)replaceNrAluno);
GetMobilidade((string)replaceNrAluno);
GetPremios((string)replaceNrAluno);
GetAtividades((string)replaceNrAluno);
GetOutrasExperiencias((string)replaceNrAluno);
GetRespSocial((string)replaceNrAluno);
col = 1;
row = 135;
if (listaLinguas.Count > 0)
{
string header = "LÍNGUAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Linguas l in listaLinguas)
{
row++;
ws.Cells[row, col].Value = " > " + l.lingua + " | Nível " + l.nivel;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaRespAcademicas.Count > 0)
{
string header = "RESPONSABILIDADES ACADÉMICAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (ResponsabilidadesAcademicas l in listaRespAcademicas)
{
row++;
ws.Cells[row, col].Value = " > " + l.responsabilidade + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaEstagios.Count > 0)
{
string header = "ESTÁGIOS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Estagios l in listaEstagios)
{
if (l.dataInicio != "01/01/1900")
{
row++;
ws.Cells[row, col].Value = " > " + l.tipoEstagio + " na " + l.empresa + " | De " + l.dataInicio.Substring(0, 10) + " a " + l.dataFim.Substring(0, 10);
}
else
{
row++;
ws.Cells[row, col].Value = " > " + l.tipoEstagio + " na " + l.empresa;
}
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaMobilidade.Count > 0)
{
string header = "MOBILIDADE INTERNACIONAL";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Mobilidade l in listaMobilidade)
{
row++;
ws.Cells[row, col].Value = " > " + l.tipo + " na " + l.programa + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaRespSocial.Count > 0)
{
string header = "RESPONSABILIDADE SOCIAL & VOLUNTARIADO";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (ResposabilidadeSocial l in listaRespSocial)
{
row++;
ws.Cells[row, col].Value = " > " + l.AcaoSocial + " | " + l.AnoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaPremios.Count > 0)
{
string header = "PRÉMIOS & RECONHECIMENTOS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Premios l in listaPremios)
{
row++;
ws.Cells[row, col].Value = " > " + l.premio + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaAtividades.Count > 0)
{
string header = "ATIVIDADES DESPORTIVAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (ActDesportivas l in listaAtividades)
{
row++;
ws.Cells[row, col].Value = " > " + l.atividade + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaExperiencias.Count > 0)
{
string header = "OUTRAS EXPERIÊNCIAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (OutrasExperiencias l in listaExperiencias)
{
row++;
ws.Cells[row, col].Value = " > " + l.experiencia + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
string data = Convert.ToString(DateTime.Now.ToLongDateString());
ws.Cells[164, 1].Value = "Lisboa, " + data;
int nrCertificado = GravaCertificado(competencias);
string anoLetivo = getAnoLetivo((string)replaceNrAluno, (string)replaceCdCurso);
ws.Range["E11"].Value = nrCertificado + " | POR | " + anoLetivo;
ws.Range["E76"].Value = nrCertificado + " | POR | " + anoLetivo;
ws.Range["E130"].Value = nrCertificado + " | POR | " + anoLetivo;
}
private void GenerateReportTopGenres(Dictionary <string, double> dictionary)
{
excel.Application excelApp = new excel.Application();
excel.Workbook workbook;
excel.Worksheet workSheet;
workbook = excelApp.Workbooks.Add();
//workSheet = (excel.Worksheet)workbook.Worksheets.get_Item(1);
workSheet = workbook.ActiveSheet;
int i = 1;
foreach (var word in dictionary.OrderByDescending(q => q.Value))
{
workSheet.Cells[1, i] = word.Key;
workSheet.Cells[2, i] = word.Value;
i++;
}
excel.Range Erange = workSheet.Range["B3"];
Erange.Formula = "=SUM(A2:J2)";
Erange.FormulaHidden = false;
excel.Borders border = Erange.Borders;
border.LineStyle = excel.XlLineStyle.xlContinuous;
excel.ChartObjects chObs = (excel.ChartObjects)workSheet.ChartObjects();
excel.ChartObject chOb = chObs.Add(5, 50, 300, 300);
excel.Chart xlchart = chOb.Chart;
excel.Range Erange2 = workSheet.Range["A1:J1"];
excel.Range Erange3 = workSheet.Range["A3:J1"];
xlchart.ChartType = excel.XlChartType.xlColumnClustered;
excel.SeriesCollection seriesCollection = (excel.SeriesCollection)xlchart.SeriesCollection(Type.Missing);
excel.Series series = seriesCollection.NewSeries();
//series.XValues = workSheet.Range["A1:J1"];
string[] matrix = new string[10];
for (int j = 0; j < 10; j++)
{
matrix[j] = (string)(workSheet.Cells[1, j + 1] as excel.Range).Value;
}
//series.XValues = workSheet.Range[workSheet.Cells[1, 3]];
series.XValues = matrix;
series.Values = workSheet.get_Range("A2", "J2");
xlchart.HasTitle = true;
xlchart.ChartTitle.Text = "Жанры и их рейтинги";
xlchart.HasLegend = true;
series.Name = "Жанры";
excelApp.Visible = true;
excelApp.UserControl = true;
string outputPath = @"C:\Users\acer\Desktop\Учеба\АИС\Отчет по рейтингу жанров (" + Guid.NewGuid().ToString() + ").xlsx";
workbook.SaveAs(outputPath);
object misValue = System.Reflection.Missing.Value;
xlchart.Export("C:\\Users\\acer\\Desktop\\Учеба\\АИС\\Graf.bmp", "BMP", misValue);
string path = @"C:\Users\acer\Desktop\Учеба\АИС\Отчет по рейтингу жанров.doc";
WordReportTopBookOfGenre wordReport = new WordReportTopBookOfGenre(path);
wordReport.GenerateReportTopGenres(dictionary);
excelApp.Quit();
}
public ColumnFrequency()
{
InitializeComponent();
//hScrollBar_Parameter1.Value = 2;
//Graphics Graphic = new Graphics();
Bandwidth = double.Parse(textBox_Bandwidth.Text);
//if (Form_Parater.flag == 0) return;
//int rows = 1;
//int cols = 1;
//string[,] str = new string[1, 1];
Graphic.RangeData(ref str, ref rows, ref cols);
worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell;
start_col = activecells.Column;
start_row = activecells.Row;
Max_Value = Double.MinValue;
Min_Value = Double.MaxValue;
for (int i = 1; i < rows; i++)
{
if (double.Parse(str[i, 0]) > Max_Value)
{
Max_Value = double.Parse(str[i, 0]);
}
if (double.Parse(str[i, 0]) < Min_Value)
{
Min_Value = double.Parse(str[i, 0]);
}
}
int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Bandwidth + 2);
double[,] data = new double[Nrows, 1];
int idx = 0;
for (int i = 1; i < rows; i++)
{
idx = Convert.ToInt32((double.Parse(str[i, 0]) - Min_Value) / Bandwidth);
data[idx, 0] = data[idx, 0] + 1;
}
data[Nrows - 2, 0] = data[Nrows - 2, 0] + data[Nrows - 1, 0];
double[,] x = new double[Nrows - 1, 1];
string[,] label = new string[Nrows - 1, 1];
for (int i = 0; i < Nrows - 1; i++)
{
label[i, 0] = "[" + Convert.ToString(Math.Floor((i * Bandwidth + Min_Value) * 100) / 100) + ","
+ Convert.ToString(Math.Floor(((i + 1) * Bandwidth + Min_Value) * 100) / 100) + ")";
//((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 1]).Value2 = label[i];
//((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 3]).Value2 = data[i];
x[i, 0] = i * Bandwidth + Min_Value;
}
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X bandwidth";
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "X axis";
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 3]).Value2 = "Y axis";
Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1];
Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 1];
Excel.Range range = worksheet.get_Range(c1, c2);
range.Value = label;
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2];
range = worksheet.get_Range(c1, c2);
range.Value = x;
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3];
range = worksheet.get_Range(c1, c2);
range.Value = data;
((Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]).Value2 = "";;
string ChartOrder = "FrequencyColumn" + Convert.ToString(Nchart);
chart = worksheet.Controls.AddChart(300, 50, 450, 400, ChartOrder);
Nchart = Nchart + 1;
c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 2];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3];
chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns);
chart.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
if (series.Count == 2)
{
Excel.Series Sseries2 = series.Item(2);
Sseries2.Delete();
}
Excel.Series Sseries = series.Item(1);
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2];
Sseries.XValues = worksheet.get_Range(c1, c2);
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3];
Sseries.Values = worksheet.get_Range(c1, c2);
Sseries.Format.Fill.Visible = Office.MsoTriState.msoTrue;
Sseries.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 88, 180, 13).ToArgb();
Sseries.Format.Line.Visible = Office.MsoTriState.msoTrue;
Sseries.Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 0, 0, 0).ToArgb();
Excel.ChartGroup group = (Excel.ChartGroup)chart.ChartGroups(1);
group.GapWidth = 0;
chart.HasLegend = false;
chart.HasTitle = false;
worksheet.Activate();
}
private void textBox_Bandwidth_TextChanged(object sender, EventArgs e)
{
if (textBox_Bandwidth.Text == "")
{
return;
}
Bandwidth = double.Parse(textBox_Bandwidth.Text);;
if (Bandwidth == 0)
{
return;
}
int Nrows = Convert.ToInt32((Max_Value - Min_Value) / Bandwidth + 2);
double[,] data = new double[Nrows, 1];
int idx = 0;
for (int i = 1; i < rows; i++)
{
idx = Convert.ToInt32((double.Parse(str[i, 0]) - Min_Value) / Bandwidth);
data[idx, 0] = data[idx, 0] + 1;
}
data[Nrows - 2, 0] = data[Nrows - 2, 0] + data[Nrows - 1, 0];
double[,] x = new double[Nrows - 1, 1];
string[,] label = new string[Nrows - 1, 1];
for (int i = 0; i < Nrows - 1; i++)
{
label[i, 0] = "[" + Convert.ToString(Math.Floor((i * Bandwidth + Min_Value) * 100) / 100) + ","
+ Convert.ToString(Math.Floor(((i + 1) * Bandwidth + Min_Value) * 100) / 100) + ")";
//((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 1]).Value2 = label[i];
//((Excel.Range)worksheet.Cells[start_row + 1 + i, start_col + cols + 3]).Value2 = data[i];
x[i, 0] = i * Bandwidth + Min_Value;
}
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 1]).Value2 = "X bandwidth";
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 2]).Value2 = "X axis";
((Excel.Range)worksheet.Cells[start_row, start_col + cols + 3]).Value2 = "Y axis";
Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 1];
Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 1];
Excel.Range range = worksheet.get_Range(c1, c2);
range.Value = label;
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2];
range = worksheet.get_Range(c1, c2);
range.Value = x;
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3];
range = worksheet.get_Range(c1, c2);
range.Value = data;
((Excel.Range)worksheet.Cells[start_row + Nrows, start_col + cols + 3]).Delete();;
c1 = (Excel.Range)worksheet.Cells[start_row, start_col + cols + 2];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3];
chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns);
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Series Sseries2 = series.Item(2);
Sseries2.Delete();
Excel.Series Sseries = series.Item(1);
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 2];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 2];
Sseries.XValues = worksheet.get_Range(c1, c2);
c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols + 3];
c2 = (Excel.Range)worksheet.Cells[start_row + Nrows - 1, start_col + cols + 3];
Sseries.Values = worksheet.get_Range(c1, c2);
chart.Refresh();
worksheet.Activate();
}
// データテーブルをエクセルへエクスポート（ＸＲ管理図・ヒストグラム付き）
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 void ExportToExcelWithBoxPlotChart(DataTable dt)
{
if (dt.Rows.Count == 0)
{
return;
}
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// column headings
for (int i = 0; i < dt.Columns.Count; i++)
{
xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j];
}
}
int row = xlWorkSheet.UsedRange.Rows.Count;
string address1 = "C1:C" + row.ToString() + ",H1:L" + row.ToString();
string address2 = "M2:M" + row.ToString();
string address3 = "N2:N" + row.ToString();
Excel.Range chartRange1;
Excel.Range chartRange2;
Excel.Range chartRange3;
Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250);
Excel.Chart chartPage1 = myChart1.Chart;
chartRange1 = xlWorkSheet.get_Range(address1);
chartRange2 = xlWorkSheet.get_Range(address2);
chartRange3 = xlWorkSheet.get_Range(address3);
chartPage1.SetSourceData(chartRange1, misValue);
chartPage1.ChartType = Excel.XlChartType.xlColumnStacked;
chartPage1.HasTitle = true;
chartPage1.ChartTitle.Text = dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
chartPage1.HasLegend = false;
Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale;
Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue);
Excel.Series s5 = oSeriesCollection.Item(5);
s5.ChartType = Excel.XlChartType.xlLine;
s5.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid;
Excel.Series s4 = oSeriesCollection.Item(4);
s4.ChartType = Excel.XlChartType.xlLine;
s4.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid;
Excel.Series s1 = oSeriesCollection.Item(1);
s1.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbWhite;
s1.Format.Fill.Transparency = 1;
s1.Format.Line.Weight = 0;
s1.HasErrorBars = true;
s1.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludeMinusValues,
Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange2, chartRange2);
Excel.Series s3 = oSeriesCollection.Item(3);
s3.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua;
s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack;
s3.Format.Line.Weight = 1.0F;
s3.HasErrorBars = true;
s3.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludePlusValues,
Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange3, chartRange3);
Excel.Series s2 = oSeriesCollection.Item(2);
s2.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua;
s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack;
s2.Format.Line.Weight = 1.0F;
xlApp.Visible = true;
}
public CurveLOESS()
{
InitializeComponent();
//int rows = 1;
//int cols = 1;
//string[,] str = new string[1, 1];
Graphic.RangeData(ref str, ref rows, ref cols);
worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Excel.Range activecells = Globals.ThisAddIn.Application.ActiveCell;
start_col = activecells.Column;
start_row = activecells.Row;
Y = new double[rows - 1];
X = new double[rows - 1];
int j;
for (j = 1; j < rows; j++)
{
X[j - 1] = double.Parse(str[j, 0]);
Y[j - 1] = double.Parse(str[j, 1]);
}
Span = long.Parse(textBox_Bandwidth.Text);
double[] yLoess = new double[rows - 1];
QLOESS(ref Y, ref X, ref yLoess, Span);
double[,] data = new double[rows - 1, 1];
for (j = 1; j < rows; j++)
{
data[j - 1, 0] = yLoess[j - 1];
}
((Excel.Range)worksheet.Cells[start_row, start_col + cols]).Value2 = "Smooth Y";
Excel.Range c1 = (Excel.Range)worksheet.Cells[start_row + 1, start_col + cols];
Excel.Range c2 = (Excel.Range)worksheet.Cells[start_row + 1 + rows - 2, start_col + cols];
Excel.Range range = worksheet.get_Range(c1, c2);
range.Value = data;
c1 = (Excel.Range)worksheet.Cells[start_row, start_col];
c2 = (Excel.Range)worksheet.Cells[start_row + rows - 1, start_col + cols];
string ChartOrder = "CurveLOESS" + Convert.ToString(Nchart);
chart = worksheet.Controls.AddChart(250, 50, 450, 400, ChartOrder);
Nchart = Nchart + 1;
chart.SetSourceData(worksheet.get_Range(c1, c2), Excel.XlRowCol.xlColumns);
chart.ChartType = Excel.XlChartType.xlXYScatter;
Excel.SeriesCollection series = (Excel.SeriesCollection)chart.SeriesCollection();
Excel.Series Sseries2 = series.Item(2);
Sseries2.Format.Line.Visible = Office.MsoTriState.msoFalse;
Sseries2.Format.Fill.Solid();
Sseries2.Format.Fill.Visible = Office.MsoTriState.msoCTrue;
//Sseries2.Format.Fill.BackColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255, 255).ToArgb();
//Sseries2.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(255,229, 184,0).ToArgb();
//Sseries2.Format.Fill.Transparency = 0.6F;
Sseries2.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle;
Sseries2.MarkerSize = 6;
Excel.Series Sseries3 = series.Item(3);
Sseries3.Format.Fill.Visible = Office.MsoTriState.msoFalse;
Sseries3.Format.Line.Visible = Office.MsoTriState.msoTrue;
Sseries3.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
//Sseries3.Format.Line.ForeColor.RGB= System.Drawing.Color.FromArgb(255, 248, 118, 109).ToArgb();
Sseries3.Format.Line.Weight = 1.25F;
Excel.Series Sseries1 = series.Item(1);
Sseries1.Delete();
}
