在c#中创建excel xy散点图时遇到麻烦，特别是分配X轴值
本文关键字：麻烦 特别是 分配 轴值 遇到 创建 excel 散点图 xy | 更新日期: 2023-09-27 18:17:39
我99%完成了创建文件来执行外部程序，然后获取数据并运行计算并将其放入excel电子表格中。
最后一部分是从程序创建XY散点图。我在注释行开始图表//添加图表，但包括它之前的东西，只是作为参考
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop;
using Excel = Microsoft.Office.Interop.Excel;
private void excel_Click(object sender, EventArgs e) //Generate Excel File when user clicks button
{
string fileTest = "C:''Cinder''" + titleTextBox.Text +"''table.xlsx";
if (File.Exists(fileTest))
{
File.Delete(fileTest);
}
Excel.Application App;
Excel.Worksheet Sheet;
Excel.Workbook Book;
App = new Excel.Application();
Book = App.Workbooks.Add();
Sheet = (Excel.Worksheet)Book.Worksheets.get_Item(1);
Sheet.Cells[1,1] = "This table represents the data from " + titleTextBox.Text + "and shows the mrem/hr for times after an operation"; // row 1, column 1
if (datatable.Checked)
{
Sheet.Cells[2, 1] = "Minutes after run"; //row 2, column 1
Sheet.Cells[3, 1] = "20";
Sheet.Cells[4, 1] = "30";
Sheet.Cells[5, 1] = "40";
Sheet.Cells[6, 1] = "50";
Sheet.Cells[7, 1] = "60";
Sheet.Cells[8, 1] = "120";
Sheet.Cells[9, 1] = "180";
Sheet.Cells[10, 1] = "240";
Sheet.Cells[11, 1] = "360";
}
if (datatablelong.Checked)
{
Sheet.Cells[2, 1] = "Days after run"; //row 2, column 1
Sheet.Cells[3, 1] = "1";
Sheet.Cells[4, 1] = "2";
Sheet.Cells[5, 1] = "3";
Sheet.Cells[6, 1] = "4";
Sheet.Cells[7, 1] = "5";
Sheet.Cells[8, 1] = "6";
Sheet.Cells[9, 1] = "7";
Sheet.Cells[10, 1] = "8";
Sheet.Cells[11, 1] = "9";
}
string[] lines = System.IO.File.ReadAllLines("c:''cinder''" + titleTextBox.Text + "''tables_by_grp");
for (int i = 2; i < 11; i++)
{
foreach (string line in lines)
{
if (line.StartsWith(" TOTAL GAMMAS/(CC-S)"))
{
string[] substrings = line.Split(new char[] { '|' });
textBox1.Text = substrings[i];
Sheet.Cells[i+13, 1] = textBox1.Text;
}
if (line.StartsWith(" GROUP MID POINTS:"))
{
string[] substrings = line.Split(new char[] { '|' });
textBox2.Text = substrings[i];
Sheet.Cells[i + 13, 3] = textBox2.Text;
break;
}
}
double mRem;
double gammas = double.Parse(textBox1.Text);
double gammaE = double.Parse(textBox2.Text);
double E = Math.Log(System.Convert.ToDouble(gammaE));
if (gammaE <= 0.03)
{
mRem = 1000.0 * gammas / (4.0 * Math.PI * 30.0 * 30.0) * Math.Exp(-20.477 + -1.7454 * E);
Sheet.Cells[i + 1, 2] = mRem;
}
if (gammaE <= 0.5 & gammaE > 0.03)
{
mRem = 1000.0 * gammas / (4.0 * Math.PI * 30.0 * 30.0) * Math.Exp(-13.626 + -0.57117 * E + -1.0954 * E * E + -.024897 * E * E * E);
Sheet.Cells[i + 1, 2] = mRem;
}
if (gammaE < 5.0 & gammaE > 0.5)
{
mRem = 1000.0 * gammas / (4.0 * Math.PI * 30.0 * 30.0) * Math.Exp(-13.133 + 0.72008 * E + -0.033603 * E * E);
Sheet.Cells[i + 1, 2] = mRem;
}
if (gammaE <= 15.0 & gammaE > 5.0)
{
mRem = 1000.0 * gammas / (4.0 * Math.PI * 30.0 * 30.0) * Math.Exp(-12.791 + 0.28309 * E + 0.10873 * E * E);
Sheet.Cells[i + 1, 2] = mRem;
}
}
// Sheet.Cells[ 1 ,2] = ; not used so tile will show
Sheet.Cells[ 13 ,1] = "Total Gammas";
Sheet.Cells[ 13 ,3] = "Average Energy";
Sheet.Cells[ 2 ,2] = "mRem/hr @ 1 ft";
Excel.Range chartRange;
/*
Excel.ChartObjects xlCharts = (Excel.ChartObjects)Sheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(180, 30, 300, 250); //location chart pops up
Excel.Chart chartPage = myChart.Chart;
*/
// Add chart.
var charts = Sheet.ChartObjects() as
Microsoft.Office.Interop.Excel.ChartObjects;
var chartObject = charts.Add(180, 30, 300, 300) as
Microsoft.Office.Interop.Excel.ChartObject;
var chart = chartObject.Chart;
// Set chart range.
chartRange = Sheet.get_Range("A2","B11");
chart.SetSourceData(chartRange);
// Set chart properties.
chart.ChartType = Excel.XlChartType.xlXYScatterLines;
chart.ChartWizard(Source: "A2:B11",
Title: "graphTitle",
CategoryTitle: "xAxis",
ValueTitle: "yAxis");
Book.SaveAs(fileTest);
Book.Close();
App.Quit();
System.Diagnostics.Process.Start(@"C:''Cinder''" + titleTextBox.Text + "''table.xlsx");
下面是我试图绘制的数据示例:
Minutes after run mRem/hr @ 1 ft
20 61.69948
30 53.60822
40 51.91109
50 51.18693
60 50.77799
120 49.51441
180 48.61834
240 47.83341
360 46.38796
它在Y轴上绘制应该是X轴的值，而不是根据我给出的数据绘制X轴。
如何使散点图正确生成X轴值?当我在excel中选择数据范围并制作散点图时，它看起来完全没问题。
谢谢!
//Add in excel Chart for results
Excel.Range oRng = Sheet.get_Range("B2", "B11");
Excel.Chart ct = Sheet.Shapes.AddChart().Chart;
var missing = System.Type.Missing;
ct.ChartWizard(oRng, Excel.XlChartType.xlXYScatterSmooth, missing, missing, missing, missing, missing, missing, "x axis", missing, missing);
Excel.Series oSeries = (Excel.Series)ct.SeriesCollection(1);
oSeries.XValues = Sheet.get_Range("A3", "A11");
if (datatable.Checked)
{
ct.ChartWizard(Source: "A2:B11",
Title: "mRem/hr from gamma radiation",
CategoryTitle: "Minutes After Run",
ValueTitle: "mRem/hr");
ct.Refresh();
}
if (datatablelong.Checked)
{
ct.ChartWizard(Source: "A2:B11",
Title: "mRem/hr from gamma radiation",
CategoryTitle: "Days After Run",
ValueTitle: "mRem/hr");
ct.Refresh();
}