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();
}
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());
}
static public void SetChartDataColor(string chartName, object[,] datas, Excel.ChartObjects chObjs, int colIdx)
{
foreach (Excel.ChartObject chObj in chObjs)
{
if (chObj.Name.Equals(chartName))
{
Excel.Series oSeries = chObj.Chart.SeriesCollection(1);
for (int i = 1; i <= datas.GetLength(0); i++)
{
object oVal = datas.GetValue(i, colIdx);
double val = Convert.ToDouble(oVal);
Excel.Point oPoint = oSeries.Points(i);
if (val >= 1)
{
oPoint.Format.Fill.ForeColor.RGB = 0x0000FF;
}
else if ((val < 1) && (val >= 0.5))
{
oPoint.Format.Fill.ForeColor.RGB = 0xCC99FF;
}
}
return;
}
}
}
public void FillExcel <T>(string name, IEnumerable <T> values, double step)
{
Excel.Application ex = new Excel.Application();
ex.Visible = true;
ex.SheetsInNewWorkbook = 1;
var workBook = ex.Workbooks.Add(Type.Missing);
var sheet = (Excel.Worksheet)ex.Worksheets.Item[1];
sheet.Name = name;
sheet.Cells[1, 1] = "x";
sheet.Cells[1, 2] = "y";
for (int i = 1; i < values.Count() + 1; i++)
{
sheet.Cells[i + 1, 1] = step * (i - 1);
sheet.Cells[i + 1, 2] = values.ElementAt(i - 1);
}
Excel.ChartObjects xlCharts = (Excel.ChartObjects)sheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(110, 0, 350, 250);
Excel.Chart chart = myChart.Chart;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.NewSeries();
series.XValues = sheet.get_Range("A2", "A" + (values.Count() + 1).ToString());
series.Values = sheet.get_Range("B2", "B" + (values.Count() + 1).ToString());
chart.ChartType = Excel.XlChartType.xlXYScatterSmooth;
}
private 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;
}
protected void CreateGraph(int columnIndex)
{
// Get the alphabetical column index...
string columnAlphabetical = ((char)(columnIndex + 64)).ToString( );
// Activate the test Chart worksheet...
this.testChartWorkSheet.Activate( );
// Get any charts currently existing in the worksheet...
Excel.ChartObjects charts =
(Excel.ChartObjects) this.testChartWorkSheet.ChartObjects(Type.Missing);
// Adds a chart at x = 100, y = 300, 500 points wide and 300 tall.
Excel.ChartObject chartObj = (Excel.ChartObject)charts.Item(columnIndex - 1);
Excel.Chart chart = chartObj.Chart;
// Gets the data cells ( X values )
Excel.Range chartRange = this.testWorkSheet.get_Range((columnAlphabetical + "2"), (columnAlphabetical + (rows).ToString( )));
chart.SetSourceData(chartRange, Type.Missing);
// Set the type of the chart...
chart.ChartType = Excel.XlChartType.xlLine;
// Get any existing series collection (there should be only one)...
Excel.SeriesCollection seriesCollection =
(Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.Item(seriesCollection.Count);
// Gets the data cells ( Y values )
series.XValues = this.testWorkSheet.get_Range("A2", "A" + (rows).ToString( ));
}
public void addChartSeries(Excel.ChartObject chartObj, Excel.Range xRange, Excel.Range yRange, string name)
{
Excel.Series ser = chartObj.Chart.SeriesCollection(System.Reflection.Missing.Value).NewSeries();
ser.XValues = xRange;
ser.Values = yRange;
ser.Name = name;
}
/// <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;
}
}
/// <summary>
/// 设置系列的y坐标为副坐标
/// </summary>
/// <param name="chart">chart</param>
/// <param name="seriesIndex">index</param>
public void SetChartSeriesCollectionY(Chart chart, int seriesIndex)
{
// chart.SeriesCollection(seriesIndex).Axes(XlAxisType.xlCategory, XlAxisGroup.xlSecondary);
//Axis ysAxis = chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlSecondary);
// ysAxis.HasTitle = string.IsNullOrEmpty(y) ? false : true;
// ysAxis.AxisTitle.Text = y;
Excel.Series se = (Excel.Series)chart.SeriesCollection(seriesIndex);
se.AxisGroup = XlAxisGroup.xlSecondary;
}
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);
}
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 build_comp_graphs(Excel.Worksheet worksheet, Excel._Worksheet worksheet2, int irows, int orows)
{
for (int i = 1; i <= irows; i++)
{
string col1 = String.Format("A{0}", i);
string col2 = String.Format("B{0}", i);
string col3 = String.Format("C{0}", i);
worksheet.Range[col1].Formula = String.Format("='yearly_steps'!{0}", col1);
worksheet.Range[col2].Formula = String.Format("='yearly_steps'!{0}", col2);
if (i == 4)
{
worksheet.Range[col3].Formula = String.Format("={0}", col2);
}
else if (i > 4)
{
worksheet.Range[col3].Formula = String.Format("=(({0} - {1}) * {2})+{3}",
col1, String.Format("A{0}", i - 1), col2, String.Format("C{0}", i - 1));
}
}
Excel.ChartObjects charts = worksheet.ChartObjects();
Excel.ChartObject chartObject = charts.Add(200, 100, 750, 400);
Excel.Chart chart = chartObject.Chart;
chart.ChartType = Excel.XlChartType.xlXYScatterLines;
Excel.Series series = chartObject.Chart.SeriesCollection().Add(worksheet2.get_Range("B4", String.Format("B{0}", orows)));
series.XValues = worksheet2.get_Range("A4", String.Format("A{0}", orows));
series.Name = "Original Data";
series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("B4:B{0}", irows)]);
series.XValues = worksheet.Range[String.Format("A4:A{0}", irows)];
series.Name = "Interpolated Data";
series.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDash;
chartObject = charts.Add(200, 525, 750, 400);
chart = chartObject.Chart;
chart.ChartType = Excel.XlChartType.xlXYScatterLines;
series = chartObject.Chart.SeriesCollection().Add(worksheet.get_Range("B4", String.Format("B{0}", orows)));
series.XValues = worksheet.get_Range("A4", String.Format("A{0}", orows));
series.Name = "Original Data";
series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("C4:C{0}", irows)]);
series.XValues = worksheet.Range[String.Format("A4:A{0}", irows)];
series.Name = "Interpolated Data";
series.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleDash;
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook wb = xlApp.Workbooks.Add(Missing.Value);
Excel.Worksheet ws = wb.ActiveSheet;
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
ws.Cells[1, i + 2].Value = dataGridView1.Rows[i].Cells[0].Value.ToString();
}
ws.Cells[2, 1].Value = "Amenities Sold";
ws.Cells[3, 1].Value = "Tickets Sold";
ws.Cells[4, 1].Value = "Commission Earned";
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
ws.Cells[2, i + 2].Value = dataGridView1.Rows[i].Cells[1].Value.ToString();
ws.Cells[3, i + 2].Value = dataGridView1.Rows[i].Cells[2].Value.ToString();
ws.Cells[4, i + 2].Value = dataGridView1.Rows[i].Cells[3].Value.ToString();
}
Excel.ChartObjects xlObj = ws.ChartObjects(Missing.Value);
Excel.ChartObject obj = xlObj.Add(10, 50, 450, 250);
Excel.Chart chart = obj.Chart;
Excel.Series s = chart.SeriesCollection().Add(ws.Range[ws.Cells[1, 1], ws.Cells[4, dataGridView1.Rows.Count]]);
//s.XValues = ws.Range[ws.Cells[1, 2], ws.Cells[1, dataGridView1.Rows.Count - 1]];
//Excel.Range rangeSourceChart = ws.Range[ws.Cells[1, 1], ws.Cells[4, dataGridView1.Rows.Count - 1]];
//chart.SetSourceData(rangeSourceChart, Excel.XlRowCol.xlRows);
using (SaveFileDialog sf = new SaveFileDialog())
{
sf.Filter = "Excel Files|*.xlsx";
sf.FileName = "CommissionReport";
if (sf.ShowDialog() == DialogResult.OK)
{
wb.SaveAs(sf.FileName);
wb.Close();
xlApp.Quit();
}
}
}
//曲线删除
/// <summary>
/// 移除指定的数据列及其依附的对象
/// </summary>
/// <param name="DeletingSeriesIndex">要进行删除的数据列在集合中的索引下标值，注意：第一条曲线的下标值为1，而不是0。</param>
/// <remarks>为了保存模板中的数据系列的格式，这里的删除并不是将数据列进行了真正的删除，而是将数据列的数据设置为空。
/// 这样的话，后期的数据曲线应该加载在最靠前而且没有数据的数据列中。</remarks>
public virtual void DeleteSeries(int DeletingSeriesIndex)
{
SeriesTag with_1 = F_DicSeries_Tag[DeletingSeriesIndex];
Excel.Series with_2 = with_1.series;
with_2.XValues = new object[] { null }; // in VB: with_2.XValues = {Nothing}
//这里不能用.XValues = Nothing
with_2.Values = new object[] { null };
//.Name = ""
this.F_DicSeries_Tag.Remove(DeletingSeriesIndex);
this.F_List_HasCurve[DeletingSeriesIndex - cst_LboundOfSeriesInCollection] = false;
// ----------------------------------- 对图例的显示进行操作
LegendRefresh(F_List_HasCurve);
this.F_CurvesCount--;
}
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 build_orig_graphs(Excel.Worksheet worksheet, int orows)
{
Excel.ChartObjects charts = worksheet.ChartObjects();
Excel.ChartObject chartObject = charts.Add(200, 100, 750, 400);
Excel.Chart chart = chartObject.Chart;
chart.ChartType = Excel.XlChartType.xlXYScatterLines;
Excel.Series series = chartObject.Chart.SeriesCollection().Add(worksheet.get_Range("B4", String.Format("B{0}", orows)));
series.XValues = worksheet.get_Range("A4", String.Format("A{0}", orows));
series.Name = "Original Data";
chartObject = charts.Add(200, 525, 750, 400);
chart = chartObject.Chart;
chart.ChartType = Excel.XlChartType.xlXYScatterLines;
series = chartObject.Chart.SeriesCollection().Add(worksheet.Range[String.Format("C4:C{0}", orows)]);
series.XValues = worksheet.Range[String.Format("A4:A{0}", orows)];
series.Name = "Original Data";
}
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();
}
private void ConfigureExcelChartFormat(string sheetName, string seriesName, Excel._Chart oChart)
{
//Serie format
Excel.Series oSeries = (Excel.Series)oChart.SeriesCollection(1);
oSeries.Name = seriesName;
oSeries.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;
oSeries.Smooth = true;
oSeries.Border.ColorIndex = 3;
oSeries.Border.Weight = 3;
//Plot area format
oChart.PlotArea.Interior.ColorIndex = 0;
oChart.PlotArea.Border.ColorIndex = 5;
//X axis format
Excel.Axis xAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
xAxis.MajorGridlines.Border.ColorIndex = 5;
xAxis.Border.ColorIndex = 5;
xAxis.TickLabels.Font.Name = "Verdana";
xAxis.TickLabels.Font.Size = 8.0f;
xAxis.TickLabels.AutoScaleFont = false;
//Y axis format
Excel.Axis yAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
yAxis.TickLabels.Font.Name = "Verdana";
yAxis.TickLabels.Font.Size = 8.0f;
yAxis.TickLabels.AutoScaleFont = false;
yAxis.Border.ColorIndex = 5;
yAxis.MajorTickMark = Microsoft.Office.Interop.Excel.XlTickMark.xlTickMarkNone;
//Legend format
oChart.Legend.Font.Name = "Verdana";
oChart.Legend.Font.Size = 9.0f;
oChart.Legend.Font.Bold = true;
oChart.Legend.AutoScaleFont = false;
oChart.Legend.Position = Microsoft.Office.Interop.Excel.XlLegendPosition.xlLegendPositionTop;
//Chart format
oChart.HasTitle = false;
oChart.Location(Excel.XlChartLocation.xlLocationAsObject, sheetName);
}
//曲线添加
/// <summary>
/// 由指定的数据列复制出一个新的数据列来，并在图表中显示
/// </summary>
/// <param name="SourceSeriesIndex">用来进行复制的原始数据列对象在集合中的下标值。
/// 注意：第一条曲线的下标值为1，而不是0。</param>
/// <remarks>此方法主要完成两步操作：
/// 1.在Excel图表中对数据列及其对应的对象进行复制；
/// 2.在字典索引dicSreies_Tag中新添加一项；
/// </remarks>
public virtual System.Collections.Generic.KeyValuePair <int, SeriesTag> CopySeries(int SourceSeriesIndex)
{
//生成新的数据列
Excel.Series newSeries = default(Excel.Series);
int NewSeriesIndex = 0;
newSeries = AddSeries(ref NewSeriesIndex);
//设置数据列格式
Excel.Series originalSeries = F_DicSeries_Tag[SourceSeriesIndex].series;
//如果此处报错，则用
//Dim xv As Object = originalSeries.XValues, 然后将xv赋值给下面的XValue
newSeries.XValues = originalSeries.XValues;
newSeries.Values = originalSeries.Values;
newSeries.Name = originalSeries.Name;
//设置数据列对应的Tag信息
this.F_DicSeries_Tag.Add(NewSeriesIndex, new SeriesTag(newSeries, this.F_RollingDate));
//
return(new System.Collections.Generic.KeyValuePair <int, SeriesTag>(NewSeriesIndex, new SeriesTag(newSeries, this.F_RollingDate)));
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="Series_Static">表示基坑区域的坑底深度的数据系列</param>
/// <param name="Series_Depth">表示基坑区域的即时开挖标高的数据系列</param>
/// <param name="ChosenRegion">此绘图中所包含的矩形方块与对应的数据范围</param>
/// <param name="textbox">记录信息的文本框</param>
/// <param name="type">此图表所属的类型，由枚举DrawingType提供</param>
public ClsDrawing_ExcavationElevation(Microsoft.Office.Interop.Excel.Series Series_Static, Series Series_Depth,
List <clsData_ProcessRegionData> ChosenRegion, DateSpan DateSpan,
Microsoft.Office.Interop.Excel.TextFrame2 textbox, DrawingType type)
{
ClsDrawing_ExcavationElevation with_1 = this;
with_1.F_Series_Static = Series_Static;
with_1.F_Series_Depth = Series_Depth;
with_1.F_textbox_Info = textbox;
with_1.P_Chart = Series_Static.Parent.Parent;
with_1.P_Sheet_Drawing = this.P_Chart.Parent.parent;
with_1.Application = this.P_Sheet_Drawing.Application;
with_1.F_Regions = ChosenRegion;
with_1.DateSpan = DateSpan;
with_1.P_Type = type;
with_1.Application.Caption = "";
with_1.P_UniqueID = GeneralMethods.GetUniqueID();
// -------------------------------------------------------------
GlobalApplication.Application.ElevationDrawing = this;
}
/// <summary>
/// 添加新的数据系列对象
/// </summary>
/// <param name="NewSeriesIndex">新添加的数据曲线在集合中的下标值</param>
/// <returns>一条新的数据曲线</returns>
/// <remarks>此函数只创建出对于新的数据曲线的对象索引，以及设置曲线的UI样式，
/// 并不涉及对于数据曲线的坐标点的赋值</remarks>
protected Excel.Series AddSeries(ref int NewSeriesIndex)
{
Excel.Series NewS = null;
var seriColl = this.Chart.SeriesCollection() as Excel.SeriesCollection;
if (F_CurvesCount <= F_List_HasCurve.Count - 1) //直接从已经定义好的模板中去提取
{
bool hasCurve = false;
for (NewSeriesIndex = cst_LboundOfSeriesInCollection; NewSeriesIndex <= System.Convert.ToInt32(F_List_HasCurve.Count + cst_LboundOfSeriesInCollection) - 1; NewSeriesIndex++)
{
hasCurve = F_List_HasCurve[NewSeriesIndex - cst_LboundOfSeriesInCollection];
if (!hasCurve)
{
NewS = seriColl.Item(NewSeriesIndex);
F_List_HasCurve[NewSeriesIndex - cst_LboundOfSeriesInCollection] = true;
break;
}
}
}
else //如果图表中的曲线数据已经大于当前数据系列集合中的series数量，那么就要新建一个数据系列，并设置其格式
{
NewS = seriColl.NewSeries();
NewSeriesIndex = System.Convert.ToInt32(F_List_HasCurve.Count + cst_LboundOfSeriesInCollection);
F_List_HasCurve.Add(true);
//'设置新数据系列的UI格式
//With NewS
//End With
}
// ----------------------------------- 对图例的显示进行操作
LegendRefresh(F_List_HasCurve);
//
F_CurvesCount++;
return(NewS);
}
/// <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;
}
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();
}
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;
}
}
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;
}
