public static void CreateChart(
ref Excel.Worksheet page,
ExcelChartInfo excelCI)
{
try
{
var xlCharts =
page.ChartObjects() as Excel.ChartObjects;
Excel.ChartObject myChart =
xlCharts.Add(excelCI.m_Left, excelCI.m_Top, excelCI.m_Width, excelCI.m_Height);
Excel.Chart chartPage = myChart.Chart;
var chartRange = page.get_Range(excelCI.m_NameLRange, excelCI.m_NameHRange);
var chartRange2 = page.get_Range(excelCI.m_SourceLRange, excelCI.m_SourceHRange);
chartPage.SetSourceData(chartRange);
chartPage.ChartType =
excelCI.m_ChartType; //xlLine || xlColumnClustered
chartPage.ChartWizard(
Source: chartRange2,
Title: excelCI.m_Title,
CategoryTitle: excelCI.m_CategoryTitle,
ValueTitle: excelCI.m_ValueTitle);
}
catch (System.Runtime.InteropServices.COMException ex)
{
MessageBox.Show(ex.Message);
}
}
public void DrawChart()
{
var processes = Process.GetProcesses()
.OrderBy(p => p.WorkingSet64);
int i = 2;
foreach (var p in processes)
{
if (p.ProcessName == "WINWORD" ||
p.ProcessName == "OUTLOOK" ||
p.ProcessName == "EXCEL")
{
excel.get_Range("A" + i).Value2 = p.ProcessName;
excel.get_Range("B" + i).Value2 = p.WorkingSet64;
i++;
}
}
Excel.Range range = excel.get_Range("A1");
Excel.Chart chart = (Excel.Chart)excel.ActiveWorkbook.Charts.Add(
After: excel.ActiveSheet);
chart.ChartWizard(Source: range.CurrentRegion,
Title: "Memory Usage of Office Applications");
chart.ChartType = Excel.XlChartType.xl3DArea;
chart.ChartStyle = 14;
chart.ChartArea.Copy();
}
/// <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);
}
/// <summary>
/// Writes an Excel worksheet containing a Respiratory Signal's values and Plot
/// </summary>
/// <param name="ws"> The Excel worksheet object </param>
/// <param name="SignalName"> The name of the respiratory signal </param>
/// <param name="table"> The respiratory signal values and peak/onset locations </param>
/// <param name="ROWS"> The number of rows in the table </param>
/// <param name="COLUMNS"> The number of columns in the table </param>
public static void AddRespiratorySignalToWorksheet(Excel.Worksheet ws, string SignalName, object[,] table, int ROWS, int COLUMNS)
{
// Make Table with Values
Excel.Range range = ws.Range[ws.Cells[3, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]];
range.Value = table;
ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range, System.Reflection.Missing.Value, Excel.XlYesNoGuess.xlGuess, System.Reflection.Missing.Value).Name = ws.Name;
ws.ListObjects[ws.Name].TableStyle = "TableStyleLight9";
ws.Columns["A:I"].ColumnWidth = 20;
ws.Columns["E:H"].Hidden = true;
ws.Columns["B:H"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// Add Conditional Formatting
Excel.Range range2 = ws.Range[ws.Cells[4, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]];
range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($E4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($F4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($G4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($H4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
range2.FormatConditions[1].Interior.Color = 5296274;
range2.FormatConditions[2].Interior.Color = 255;
range2.FormatConditions[3].Interior.Color = 65535;
range2.FormatConditions[4].Interior.Color = 15773696;
range2.Columns[2].NumberFormat = "m/d/yyyy h:mm:ss.000";
// Add Chart
Excel.Chart chart = ((Excel.ChartObject)((Excel.ChartObjects)ws.ChartObjects()).Add(500, 100, 900, 500)).Chart;
chart.SetSourceData(range.Columns["B:G"]);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLines;
chart.ChartWizard(Source: range.Columns["B:G"], Title: SignalName, CategoryTitle: "Time", ValueTitle: SignalName);
chart.PlotVisibleOnly = false;
((Excel.Series)chart.SeriesCollection(1)).ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers;
((Excel.Series)chart.SeriesCollection(2)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
((Excel.Series)chart.SeriesCollection(3)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
((Excel.Series)chart.SeriesCollection(4)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
((Excel.Series)chart.SeriesCollection(5)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
((Excel.Series)chart.SeriesCollection(2)).Format.Fill.ForeColor.RGB = 5296274;
((Excel.Series)chart.SeriesCollection(3)).Format.Fill.ForeColor.RGB = 255;
((Excel.Series)chart.SeriesCollection(4)).Format.Fill.ForeColor.RGB = 65535;
((Excel.Series)chart.SeriesCollection(5)).Format.Fill.ForeColor.RGB = 15773696;
((Excel.Series)chart.SeriesCollection(1)).Format.Line.ForeColor.RGB = 38450;
((Excel.Series)chart.SeriesCollection(2)).Format.Line.ForeColor.RGB = 5296274;
((Excel.Series)chart.SeriesCollection(3)).Format.Line.ForeColor.RGB = 255;
((Excel.Series)chart.SeriesCollection(4)).Format.Line.ForeColor.RGB = 65535;
((Excel.Series)chart.SeriesCollection(5)).Format.Line.ForeColor.RGB = 15773696;
System.Runtime.InteropServices.Marshal.ReleaseComObject(chart);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
}
internal static void Export(DataGridView dataTable)
{
_Application excelApp = new _Excel.Application();
Workbook wb;
wb = excelApp.Workbooks.Add();
Worksheet ws = wb.Worksheets[1];
double[] arrOfX = new double[dataTable.Rows.Count - 1];
double[] arrOfY = new double[dataTable.Rows.Count - 1];
GetValues.FillArrays(arrOfX, arrOfY, dataTable);
excelApp.Visible = true;
excelApp.Cells[1, 1] = "X:";
excelApp.Cells[1, 2] = "Y:";
int row = 3;
int column = 1; //начинать заполнение необходимо с 3 строчки, т к при построении графика левая верхняя клеточка должна быть пустой
for (int i = 0; i < arrOfX.Length; i++)
{
excelApp.Cells[row, column] = arrOfX[i];
column++;
excelApp.Cells[row, column] = arrOfY[i];
column--;
row++;
}
_Excel.Range eRange = ws.get_Range("a2", "b" + row);
_Excel.ChartObjects eChartObjects = (_Excel.ChartObjects)ws.ChartObjects(Type.Missing);
_Excel.ChartObject eChartObj = eChartObjects.Add(10, 30, 300, 300); //размеры диаграммы
_Excel.Chart eChart = eChartObj.Chart;
eChart.ChartType = _Excel.XlChartType.xlLine;
eChart.ChartWizard(
Source: eRange,
Title: "Cassini Oval",
CategoryTitle: "xAxis",
CategoryLabels: 2,
ValueTitle: "yAxis",
HasLegend: false);
eChart.SetSourceData(eRange);
}
private void btnCreateChart_Click(object sender, EventArgs e)
{
try
{
Excel.Chart oChart = xlWBook.Charts.Add();
Excel.Range rngChart = (Excel.Range)xlWSheet.get_Range("" + cmb1.Text + (int)No1.Value + "", "" + cmb2.Text + (int)No2.Value + "");
FormatAsTable(rngChart, "Table1", "TableStyleMedium1");
oChart.ChartWizard(rngChart, Excel.XlChartType.xl3DColumn);
oChart.Refresh();
//xlWBook.AcceptAllChanges();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static void CreateChart(string filename)
{
Excel.Application excelApplication = new Excel.Application();
excelApplication.Visible = true;
//Opens the excel file
Excel.Workbook excelWorkbook = excelApplication.Workbooks.Add();
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets.get_Item(1);
excelWorksheet.Cells[1, 1] = "Product ID";
excelWorksheet.Cells[1, 2] = "Stock";
excelWorksheet.Cells[2, 1] = "BB12";
excelWorksheet.Cells[2, 2] = "15";
excelWorksheet.Cells[3, 1] = "xy40";
excelWorksheet.Cells[3, 2] = "18";
excelWorksheet.Cells[4, 1] = "AX50";
excelWorksheet.Cells[4, 2] = "23";
//Add a char object
Excel.Chart myChart = null;
Excel.ChartObjects charts = excelWorksheet.ChartObjects();
Excel.ChartObject chartObj = charts.Add(50, 50, 300, 300); //left; top; width; height
myChart = chartObj.Chart;
//set chart range -- cell values to be used in the graph
Excel.Range myRange = excelWorksheet.get_Range("B1:B4");
myChart.SetSourceData(myRange);
//chart properties using the named properties and default parameters functionality in
//the .Net Framework
myChart.ChartType = Excel.XlChartType.xlLine;
myChart.ChartWizard(Source: myRange,
Title: "Graph Title",
CategoryTitle: "Title of X axis... ",
ValueTitle: "Title of Y axis... ");
excelWorkbook.SaveAs(filename);
excelWorkbook.Close();
excelApplication.Quit();
ReleaseCOMObjects(excelWorksheet);
ReleaseCOMObjects(excelWorkbook);
ReleaseCOMObjects(excelApplication);
}
static void GenerateChart(bool copyToWord = false)
{
var excel = new Excel.Application();
excel.Visible = true;
excel.Workbooks.Add();
excel.get_Range("A1").Value2 = "Process Name";
excel.get_Range("B1").Value2 = "Memory Usage";
var processes = Process.GetProcesses()
.OrderByDescending(p => p.WorkingSet64)
.Take(10);
int i = 2;
foreach (var p in processes)
{
excel.get_Range("A" + i).Value2 = p.ProcessName;
excel.get_Range("B" + i).Value2 = p.WorkingSet64;
i++;
}
Excel.Range range = excel.get_Range("A1");
Excel.Chart chart = (Excel.Chart)excel.ActiveWorkbook.Charts.Add(
After: excel.ActiveSheet);
chart.ChartWizard(Source: range.CurrentRegion,
Title: "Memory Usage in " + Environment.MachineName);
chart.ChartStyle = 45;
chart.CopyPicture(Excel.XlPictureAppearance.xlScreen,
Excel.XlCopyPictureFormat.xlBitmap,
Excel.XlPictureAppearance.xlScreen);
if (copyToWord)
{
var word = new Word.Application();
word.Visible = true;
word.Documents.Add();
word.Selection.Paste();
}
}
/// <summary>
/// Writes an Excel worksheet containing a signal's values and Plot
/// </summary>
/// <param name="ws"> The Excel worksheet object </param>
/// <param name="SignalName"> The name of the signal </param>
/// <param name="table"> The signal values </param>
/// <param name="ROWS"> The number of rows in the table </param>
/// <param name="COLUMNS"> The number of columns in the table </param>
/// <param name="color"> The color of the plot </param>
public static void AddSignalToWorksheet(Excel.Worksheet ws, string SignalName, object[,] table, int ROWS, int COLUMNS, OxyColor color)
{
Excel.Range range = ws.Range[ws.Cells[3, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]];
range.Value = table;
ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range, System.Reflection.Missing.Value, Excel.XlYesNoGuess.xlGuess, System.Reflection.Missing.Value).Name = ws.Name;
ws.ListObjects[ws.Name].TableStyle = "TableStyleLight9";
ws.Columns["A:I"].ColumnWidth = 20;
ws.Columns["B:H"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Columns[2].NumberFormat = "m/d/yyyy h:mm:ss.000";
Excel.Chart chart = ((Excel.ChartObject)((Excel.ChartObjects)ws.ChartObjects()).Add(500, 100, 900, 500)).Chart;
chart.SetSourceData(range.Columns["B:C"]);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLines;
chart.ChartWizard(Source: range.Columns["B:C"], Title: SignalName, CategoryTitle: "Time", ValueTitle: SignalName);
((Excel.Series)chart.SeriesCollection(1)).ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers;
((Excel.Series)chart.SeriesCollection(1)).Format.Line.ForeColor.RGB = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(color.A, color.R, color.G, color.B));
System.Runtime.InteropServices.Marshal.ReleaseComObject(chart);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = true;
Excel.Workbook wb = excelApp.Workbooks.Add();
Excel.Worksheet sheet = wb.ActiveSheet;
//add data
sheet.Cells[1, 1] = "";
sheet.Cells[1, 2] = "Student1";
sheet.Cells[1, 3] = "Student2";
sheet.Cells[1, 4] = "Student3";
sheet.Cells[2, 1] = "Term1";
sheet.Cells[2, 2] = "80";
sheet.Cells[2, 3] = "65";
sheet.Cells[2, 4] = "45";
sheet.Cells[3, 1] = "Term2";
sheet.Cells[3, 2] = "78";
sheet.Cells[3, 3] = "72";
sheet.Cells[3, 4] = "60";
sheet.Cells[4, 1] = "Term3";
sheet.Cells[4, 2] = "82";
sheet.Cells[4, 3] = "80";
sheet.Cells[4, 4] = "65";
sheet.Cells[5, 1] = "Term4";
sheet.Cells[5, 2] = "75";
sheet.Cells[5, 3] = "82";
sheet.Cells[5, 4] = "68";
Excel.Range chartRange = sheet.get_Range("A1", "d5");
Excel.ChartObjects xlCharts = (Excel.ChartObjects)sheet.ChartObjects();
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartPage.ChartWizard(chartRange, Excel.XlChartType.xlColumnClustered, Title: "Diagram title");
chartPage.Export(Application.StartupPath + @"./excel_chart_export.png", "png");
}
/// <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();
}
public static void WriteToExcelFile(string path, List <string[]> listasMin, List <string[]> listasMax, List <string[]> listasAvg, string[] parametros)
{
Excel.Application excelApplication = new Excel.Application();
excelApplication.Visible = false;
Excel.Workbook excelWorkbook = excelApplication.Workbooks.Open(path);
Excel.Worksheet excelWorksheet = excelWorkbook.ActiveSheet;
//escrever dados para o excel
int linha;
int coluna = 600;
int aux = 0;
foreach (string[] lista in listasMin)
{
linha = 0;
Excel.Chart myChart = null;
Excel.ChartObjects charts = excelWorksheet.ChartObjects();
Excel.ChartObject chartObject = charts.Add(10, (300 * aux) + 10, 300, 300); //left, top, width, heigh
myChart = chartObject.Chart;
foreach (string dado in lista)
{
linha++;
string dadoaux = dado.Replace(",", ".");
excelWorksheet.Cells[linha, coluna].Value = dadoaux;
}
//set chart range
Excel.Range c1 = excelWorksheet.Cells[1, coluna];
Excel.Range c2 = excelWorksheet.Cells[linha, coluna];
Excel.Range myrange = excelWorksheet.get_Range(c1, c2);
myChart.SetSourceData(myrange);
//chart properties using the named parameters and default parameters functionality in the .NET
myChart.ChartType = Excel.XlChartType.xlLine;
myChart.ChartWizard(Source: myrange,
Title: parametros[aux],
CategoryTitle: "Hours",
ValueTitle: "Min Values");
coluna++;
aux++;
}
aux = 0;
coluna++;
foreach (string[] lista in listasMax)
{
linha = 0;
Excel.Chart myChart = null;
Excel.ChartObjects charts = excelWorksheet.ChartObjects();
Excel.ChartObject chartObject = charts.Add(320, (300 * aux) + 10, 300, 300); //left, top, width, heigh
myChart = chartObject.Chart;
foreach (string dado in lista)
{
linha++;
string dadoaux = dado.Replace(",", ".");
excelWorksheet.Cells[linha, coluna].Value = dadoaux;
}
//set chart range
Excel.Range c1 = excelWorksheet.Cells[1, coluna];
Excel.Range c2 = excelWorksheet.Cells[linha, coluna];
Excel.Range myrange = excelWorksheet.get_Range(c1, c2);
myChart.SetSourceData(myrange);
//chart properties using the named parameters and default parameters functionality in the .NET
myChart.ChartType = Excel.XlChartType.xlLine;
myChart.ChartWizard(Source: myrange,
Title: parametros[aux],
CategoryTitle: "Hours",
ValueTitle: "Max Values");
coluna++;
aux++;
}
aux = 0;
coluna++;
foreach (string[] lista in listasAvg)
{
linha = 0;
Excel.Chart myChart = null;
Excel.ChartObjects charts = excelWorksheet.ChartObjects();
Excel.ChartObject chartObject = charts.Add(640, (300 * aux) + 10, 300, 300); //left, top, width, heigh
myChart = chartObject.Chart;
foreach (string dado in lista)
{
linha++;
string dadoaux = dado.Replace(",", ".");
excelWorksheet.Cells[linha, coluna].Value = dadoaux;
}
//set chart range
Excel.Range c1 = excelWorksheet.Cells[1, coluna];
Excel.Range c2 = excelWorksheet.Cells[linha, coluna];
Excel.Range myrange = excelWorksheet.get_Range(c1, c2);
myChart.SetSourceData(myrange);
//chart properties using the named parameters and default parameters functionality in the .NET
myChart.ChartType = Excel.XlChartType.xlLine;
myChart.ChartWizard(Source: myrange,
Title: parametros[aux],
CategoryTitle: "Hours",
ValueTitle: "Average Values");
coluna++;
aux++;
}
excelWorkbook.Save();
excelWorkbook.Close();
excelApplication.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
excelWorkbook = null;
//Don’t forget to free the memory used by excel objects
//...
GC.Collect();
}
/// <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="dataSet2"></param>
/// <param name="seriesTitle"></param>
/// <param name="xAxis"></param>
/// <param name="yAxis"></param>
/// <param name="headers"></param>
/// <param name="dataSet1"></param>
public static void PlotDataSets(List <List <double[]> > dataSet1, List <List <double[]> > dataSet2, 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);
//Create Chart
var xlCharts = (Excel.ChartObjects)xlWorksheet.ChartObjects(Type.Missing);
for (var i = 0; i < dataSet1.Count; i++) //For each series in dataSet1
{
//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();
//Add first series
var xValues = new double[dataSet1[i].Count];
var yValues = new double[dataSet1[i].Count];
for (var j = 0; j < dataSet1[i].Count; j++) //For each point in series
{
xValues[j] = dataSet1[i][j][0];
yValues[j] = dataSet1[i][j][1];
}
Excel.Series series1 = seriesCollection.NewSeries();
series1.Values = yValues;
series1.XValues = xValues;
//Add second series
xValues = new double[dataSet2[i].Count];
yValues = new double[dataSet2[i].Count];
for (var j = 0; j < dataSet2[i].Count; j++) //For each point in series
{
xValues[j] = dataSet2[i][j][0];
yValues[j] = dataSet2[i][j][1];
}
Excel.Series series2 = seriesCollection.NewSeries();
series2.Values = yValues;
series2.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);
}
//Excel.XlChartType.xl3DColumn
/// <summary>
/// 给工作添加一个图标工作表
/// </summary>
/// <param name="xlBook">工作簿</param>
/// <param name="dataSheet">数据工作表</param>
/// <param name="xData">x轴数据区域：格式A1:A14</param>
/// <param name="ydata">y轴数据区域：格式B1:E14</param>
/// <param name="chartName">图标名称</param>
/// <param name="xname">y轴名称</param>
/// <param name="yname">y轴名称</param>
/// <param name="chartType">图表类型</param>
public static void createSingleChart(Excel.Workbook xlBook, Excel.Worksheet dataSheet, string xData, string ydata, string chartName, string xname, string yname, Excel.XlChartType chartType)
{
Excel.Chart xlChart = (Excel.Chart)xlBook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Range chartRage = dataSheet.get_Range(xData, ydata);
xlChart.ChartWizard(chartRage, chartType, Missing.Value, Excel.XlRowCol.xlRows, 1, 1, true, chartName, xname, yname, Missing.Value);
}
static void Main(string[] args)
{
// Create an instance of Excel and show it.
Excel.Application xlApp = new Excel.ApplicationClass();
xlApp.Visible = true;
// Create new Excel Workbook with one worksheet.
xlApp.SheetsInNewWorkbook = 1;
Excel.Workbook wb = xlApp.Workbooks.Add(Type.Missing);
// Set worksheet object to first worksheet in workbook.
Excel.Worksheet ws = wb.Worksheets[1] as Excel.Worksheet;
// Change the name of the worksheet.
ws.Name = "MyWorksheet";
// Fill the worksheet.
int i;
for (i = 1; i < 10; i++)
{
(ws.Cells[i, 1] as Excel.Range).Value2 = String.Format("Row {0}", i);
(ws.Cells[i, 2] as Excel.Range).Value2 = i;
}
// Fill total row.
(ws.Cells[i, 1] as Excel.Range).Value2 = "Total";
(ws.Cells[i, 2] as Excel.Range).Formula = String.Format("=Sum(B1:B{0})", i - 1);
// Make first column bold and draw line before total row.
ws.get_Range(String.Format("A1:A{0}", i), Type.Missing).Font.Bold = true;
ws.get_Range(String.Format("A{0}:B{1}", i - 1, i - 1), Type.Missing).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
// Create chart on separate worksheet and format the chart using the chart wizard.
Excel.Chart ch = wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Excel.Chart;
ch.ChartWizard(ws.get_Range(String.Format("A1:B{0}", i), Type.Missing), Excel.XlChartType.xlCylinderCol, 1,
Excel.XlRowCol.xlColumns, 1, 0, true, "Chart with values of each row",
"Rows", "Value", "Extra Title");
// Create chart on same worksheet and format the chart using the chart wizard.
ch = (ws.ChartObjects(Type.Missing) as Excel.ChartObjects).Add(150, 0, 400, 400).Chart;
ch.ChartWizard(ws.get_Range(String.Format("A1:B{0}", i), Type.Missing), Excel.XlChartType.xl3DColumn, 1,
Excel.XlRowCol.xlColumns, 1, 0, true, "Chart with values of each row",
"Rows", "Value", "Extra Title");
// Save workbook.
try
{
wb.SaveAs("MyWorkbook.xls", Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (System.Runtime.InteropServices.COMException)
{
// user cancelled save.
}
// Ask to print workbook.
/* if (MessageBox.Show("Print workbooks?", "Question", MessageBoxButtons.YesNo)==DialogResult.Yes)
* {
* wb.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
* }*/
// Close the workbook.
wb.Close(Type.Missing, Type.Missing, Type.Missing);
// Close Excel.
xlApp.Quit();
}
public void ExportRectangle(List <string> txDataColumnc, List <string> tyProductColumnc, List <int> tyNumberColumnc, Excel.Application objExcel = null, Excel.Workbook objWorkbook = null, Excel.Worksheet objsheet = null)
{
List <string> txDataColumn = txDataColumnc;
List <string> tyDataOk = tyProductColumnc;
List <int> tyDataNo = tyNumberColumnc;
try
{
//设置属性标签
objsheet.Range["A:A"].ColumnWidth = 20; //设置宽度
objsheet.Range["A:A"].NumberFormatLocal = "@";
objsheet.Range["B:B"].ColumnWidth = 20; //设置宽度
objsheet.Range["C:C"].ColumnWidth = 20; //设置宽度
#region 管理人员
int col = 1;
objExcel.Cells[1, col] = "日期";
objExcel.Cells[1, col + 1] = "产品名称";
objExcel.Cells[1, col + 2] = "数量";
int row = 2; //row和 i得对应关系是row = i+2 ; i = row -2
int temp = row;
int cell = 0;
for (int i = 0; i < txDataColumn.Count; i++)
{
objExcel.Cells[row, col] = txDataColumn[i].ToString();
row++;
}
for (int i = 0; i < txDataColumn.Count; i++)
{
if (i == 0)
{
//objExcel.Cells[row, col] = txDataColumn[i];
}
else
{
if (txDataColumn[i] != txDataColumn[i - 1])
{
cell = i - 1 + 2;
Excel.Range rangeChange = objsheet.Range["A" + temp, "A" + cell];
rangeChange.Value2 = Type.Missing;
rangeChange.Merge(Type.Missing);
rangeChange.Value2 = txDataColumn[i - 1].ToString();
temp = i + 2;
}
}
row++;
}
row = 2;
for (int i = 0; i < tyDataOk.Count; i++)
{
objExcel.Cells[row, col + 1] = tyDataOk[i];
row++;
}
row = 2;
for (int i = 0; i < tyDataNo.Count; i++)
{
objExcel.Cells[row, col + 2] = tyDataNo[i];
row++;
}
#endregion
int num = txDataColumn.Count + 1;
Excel.Range rangeAll = objsheet.Range["A1", "C" + num];
rangeAll.Borders.Color = 0;
rangeAll.Borders.Weight = 2;
rangeAll.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rangeAll.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//设置属性标签
objsheet.Range["A:A"].ColumnWidth = 20; //设置宽度
objsheet.Range["B:B"].ColumnWidth = 20; //设置宽度
objsheet.Range["C:C"].ColumnWidth = 20; //设置宽度
//柱状图
Excel.Chart xlChart2 = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[1, 1], (Excel.Range)objsheet.Cells[1 + txDataColumn.Count, 3]);
//1-cellRange:数据源的范围，2-图表类型，3-Type.Missing，4-在图表上将列或行用作数据系列的方式，5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行，7-图表是否有图例，8、9、10-设置标题
xlChart2.ChartWizard(cellRange,
Excel.XlChartType.xlColumnClustered, //2-图表类型
Type.Missing, //内置自动套用格式的选项编号。 可为从 1 到 10 的数字，其取值依赖于库的类型。 如果省略此参数, 则 Excel 根据库的类型和数据源选择默认值。
Excel.XlRowCol.xlColumns, //在图表上将列或行用作数据系列的方式
2, //第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行--这个2代表数据源的x轴由两个参数确认；可以不写，默认的就很难看
1, //--这个2代表数据源的x轴由1个参数确认；可以不写，默认的就很难看
true, //图表是否有图例
"每日总量统计", //以下都是标题
Type.Missing,
Type.Missing,
"");
xlChart2.ChartStyle = 201;
xlChart2.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd); // 设置图表上图表元素。 为可读/写属性。
xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); //将图表移动到新位置。
objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); // xlLocationAsObject,将图表嵌入到现有工作表中。
objsheet.Shapes.Item("Chart 1").Top = 100; //调图表的位置上边距
objsheet.Shapes.Item("Chart 1").Left = 400;
objsheet.Shapes.Item("Chart 1").Width = txDataColumn.Count * 30; //调图表的宽度
objsheet.Shapes.Item("Chart 1").Height = 300; //调图表的高度
//保存
objsheet.Name = "每日占用";
objsheet.Tab.Color = 3394611;
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
public void Export()
{
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xlsx ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLSX)|*.xlsx ";
//默然路径是系统当前路径
dlg.InitialDirectory = System.IO.Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{
return;
}
Excel.Application objExcel = null;
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//合格率
objExcel.Cells[1, 1] = "开始时间";
objExcel.Cells[1, 3] = "结束时间";
objExcel.Cells[2, 1] = "不合格";
objExcel.Cells[3, 1] = "合格";
objExcel.Cells[2, 2] = tyDataPie[0];
objExcel.Cells[3, 2] = tyDataPie[1];
//饼图
Excel.Range oResizeRange;
Excel.Chart xlChart = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
xlChart.ChartType = Excel.XlChartType.xlPie;//设置图形
xlChart.SetSourceData(objsheet.get_Range("A2", "B3"), Excel.XlRowCol.xlColumns);
objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, "合格率");
objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);
oResizeRange = (Excel.Range)objsheet.Rows.get_Item(7, Missing.Value);
objsheet.Shapes.Item("Chart 1").Top = 70; //调图表的位置上边距
objsheet.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;
objsheet.Shapes.Item("Chart 1").Width = 200; //调图表的宽度
objsheet.Shapes.Item("Chart 1").Height = 150; //调图表的高度
#region 管理人员
int col = 6;
objExcel.Cells[2, col] = "用户名";
objExcel.Cells[2, col + 1] = "合格";
objExcel.Cells[2, col + 2] = "不合格";
int row = 3;
for (int i = 0; i < txDataColumn.Count; i++)
{
objExcel.Cells[row, col] = txDataColumn[i];
row++;
}
row = 3;
for (int i = 0; i < tyDataOk.Count; i++)
{
objExcel.Cells[row, col + 1] = tyDataOk[i];
row++;
}
row = 3;
for (int i = 0; i < tyDataNo.Count; i++)
{
objExcel.Cells[row, col + 2] = tyDataNo[i];
row++;
}
#endregion
//柱状图
Excel.Chart xlChart2 = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[2, 6], (Excel.Range)objsheet.Cells[3 + txDataColumn.Count - 1, 8]);
//1-cellRange:数据源的范围，2-图表类型，3-Type.Missing，4-在图表上将列或行用作数据系列的方式，5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行，7-图表是否有图例，8、9、10-设置标题
xlChart2.ChartWizard(cellRange,
Excel.XlChartType.xlColumnStacked, Type.Missing,
Excel.XlRowCol.xlColumns, 1, 1, true,
"管理人员校准情况", "用户名", "校准个数",
"");
xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);
Excel.Range oResizeRange1 = (Excel.Range)objsheet.Rows.get_Item(1);
Excel.Range oResizeRange2 = (Excel.Range)objsheet.Columns.get_Item(10);
objsheet.Shapes.Item("Chart 2").Top = (float)oResizeRange1.Top; //调图表的位置上边距--1行的高度
objsheet.Shapes.Item("Chart 2").Left = (float)(double)oResizeRange2.Left; //调图表的位置左边距--10列的宽度
objsheet.Shapes.Item("Chart 2").Width = 300; //调图表的宽度
objsheet.Shapes.Item("Chart 2").Height = 200; //调图表的高度
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
//关闭Excel应用
if (objWorkbook != null)
{
objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
}
if (objExcel.Workbooks != null)
{
objExcel.Workbooks.Close();
}
if (objExcel != null)
{
objExcel.Quit();
}
objsheet = null;
objWorkbook = null;
objExcel = null;
}
}
/// <summary>
/// 柱形图
/// </summary>
/// <param name="txDataColumnc"></param>
/// <param name="tyProductColumnc"></param>
/// <param name="tyNumberColumnc"></param>
public void ExportRectangle(List <string> txDataColumnc, List <string> tyProductColumnc, List <int> tyNumberColumnc, string DateBegin, string DateEnd)
{
Open = true;
intExcelTempIndex++;
List <string> txDataColumn = txDataColumnc;
List <string> tyDataOk = tyProductColumnc;
List <int> tyDataNo = tyNumberColumnc;
Excel.Application objExcel = null;//创建一个excel的实例
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
string fileNameString = "";
try
{
//申明对象
objExcel = new Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//设置属性标签
objsheet.Range["A:A"].ColumnWidth = 20; //设置宽度
objsheet.Range["A:A"].NumberFormatLocal = "@";
objsheet.Range["B:B"].ColumnWidth = 20; //设置宽度
objsheet.Range["C:C"].ColumnWidth = 20; //设置宽度
#region 管理人员
int col = 1;
objExcel.Cells[1, col] = "日期";
objExcel.Cells[1, col + 1] = "产品名称";
objExcel.Cells[1, col + 2] = "数量";
int row = 2; //row和 i得对应关系是row = i+2 ; i = row -2
int temp = row;
int cell = 0;
for (int i = 0; i < txDataColumn.Count; i++)
{
objExcel.Cells[row, col] = txDataColumn[i].ToString();
row++;
}
for (int i = 0; i < txDataColumn.Count; i++)
{
if (i == 0)
{
//objExcel.Cells[row, col] = txDataColumn[i];
}
else
{
if (txDataColumn[i] != txDataColumn[i - 1])
{
cell = i - 1 + 2;
Excel.Range rangeChange = objsheet.Range["A" + temp, "A" + cell];
rangeChange.Value2 = Type.Missing;
rangeChange.Merge(Type.Missing);
rangeChange.Value2 = txDataColumn[i - 1].ToString();
temp = i + 2;
}
}
row++;
}
row = 2;
for (int i = 0; i < tyDataOk.Count; i++)
{
objExcel.Cells[row, col + 1] = tyDataOk[i];
row++;
}
row = 2;
for (int i = 0; i < tyDataNo.Count; i++)
{
objExcel.Cells[row, col + 2] = tyDataNo[i];
row++;
}
#endregion
int num = txDataColumn.Count + 1;
Excel.Range rangeAll = objsheet.Range["A1", "C" + num];
rangeAll.Borders.Color = 0;
rangeAll.Borders.Weight = 2;
rangeAll.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rangeAll.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//柱状图
Excel.Chart xlChart2 = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[1, 1], (Excel.Range)objsheet.Cells[1 + txDataColumn.Count, 3]);
//1-cellRange:数据源的范围，2-图表类型，3-Type.Missing，4-在图表上将列或行用作数据系列的方式，5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行，7-图表是否有图例，8、9、10-设置标题
xlChart2.ChartWizard(cellRange,
Excel.XlChartType.xlColumnClustered, //2-图表类型
Type.Missing, //内置自动套用格式的选项编号。 可为从 1 到 10 的数字，其取值依赖于库的类型。 如果省略此参数, 则 Excel 根据库的类型和数据源选择默认值。
Excel.XlRowCol.xlColumns, //在图表上将列或行用作数据系列的方式
2, //第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行--这个2代表数据源的x轴由两个参数确认；可以不写，默认的就很难看
1, //--这个2代表数据源的x轴由1个参数确认；可以不写，默认的就很难看
true, //图表是否有图例
"每日总量统计(" + DateBegin + "-" + DateEnd + ")", //以下都是标题
Type.Missing,
Type.Missing,
"");
xlChart2.ChartStyle = 201;
xlChart2.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd); // 设置图表上图表元素。 为可读/写属性。
xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); //将图表移动到新位置。
objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); // xlLocationAsObject,将图表嵌入到现有工作表中。
objsheet.Shapes.Item("Chart 1").Top = 100; //调图表的位置上边距
objsheet.Shapes.Item("Chart 1").Left = 400;
objsheet.Shapes.Item("Chart 1").Width = objsheet.Shapes.Item("Chart 1").Width = txDataColumn.Count * 100 > 400 ? txDataColumn.Count * 30 : 400; //调图表的宽度
objsheet.Shapes.Item("Chart 1").Height = 300; //调图表的高度
//保存
objsheet.Name = "每日总量统计" + intExcelTempIndex.ToString();
objsheet.Tab.Color = Excel.XlThemeColor.xlThemeColorLight1;
fileNameString = Application.StartupPath + "\\" + objsheet.Name + ".xlsx";
objExcel.DisplayAlerts = false;
objExcel.AlertBeforeOverwriting = false;
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show("该文件已打开，请关闭后重试！", "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Open = false;
return;
}
finally
{
//确认进程关闭
if (objWorkbook != null)
{
objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
}
if (objExcel.Workbooks != null)
{
objExcel.Workbooks.Close();
}
if (objExcel != null)
{
objExcel.Quit();
}
// 安全回收进程
var aa = System.GC.GetGeneration(objExcel);
objsheet = null;
objWorkbook = null;
objExcel = null;
if (Open == true)
{
Process.Start(fileNameString);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xlsx ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLSX)|*.xlsx ";
//默然路径是系统当前路径
dlg.InitialDirectory = System.IO.Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{
return;
}
Excel.Application objExcel = new Excel.Application();//创建一个excel的实例
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
//Excel.Sheets objsheets = objExcel.Workbooks.Item[1].Worksheets;
//objsheets.Item[1] = "";
//Excel.Worksheet ws = (Excel.Worksheet)objExcel.Worksheets.get_Item(1);
//ws.Name = "狐狸!";
// Excel.Sheets objsheets = objExcel.Sheets;
//var ss = objsheets.Item[1];
// var aa = (Excel.Worksheet)ss;
// aa.Name = "dasdasdasd";
//objsheets["Sheet1"].Nasdasda = "11";
//objsheet = (Excel.Worksheet)objsheets.get_Item(objsheets.Count);
//worksheet.Name = "sadas ";
////objsheets.get_Item(1
//objWorkbooks = objExcel.Workbooks;
////oBooks.Open(sTemplate, 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);
//objWorkbook = objWorkbooks.get_Item(1);
//objsheets = objWorkbook.Worksheets;
//objsheet = (Excel.Worksheet)objsheets.get_Item(1);
////命名该sheet
//objsheet.Name = "Sheet1";
try
{
//申明对象
objExcel = new Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//合格率
objExcel.Cells[1, 1] = "客户";
objExcel.Cells[1, 2] = "数量";
objExcel.Cells[1, 3] = "占比";
objExcel.Cells[2, 1] = "不合格";
objExcel.Cells[3, 1] = "合格";
objExcel.Cells[2, 2] = tyDataPie[0];
objExcel.Cells[3, 2] = tyDataPie[1];
objExcel.Cells[2, 3] = @"=B2 / B4";
objExcel.Cells[3, 3] = @"=B3 / B4";
//求和
Excel.Range rangesummary73 = objsheet.Range["B4"];//--ActiveCell = rangesummary110
rangesummary73.Formula = "=SUM(R[-2]C:R[-1]C)";
rangesummary73.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)";
// Range("A2:A3,C2:C3").Select
//Range("C2").Activate
//ActiveSheet.Shapes.AddChart2(251, xlPie).Select
//ActiveChart.SetSourceData Source:= Range("Sheet1!$A$2:$A$3,Sheet1!$C$2:$C$3")
//ActiveChart.SetElement(msoElementDataLabelBestFit)
// Excel.Range rangesummary5 = objsheet.Range["A2:A3", "C2:C3"];
//设置百分比格式
Excel.Range rangesummary4 = objsheet.Range["C2", "C3"];
//Range("C2:C3").Select;
rangesummary4.NumberFormatLocal = "0.00%";
//饼图
//新建一个饼图
Excel.Chart xlChart = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
xlChart.ChartType = Excel.XlChartType.xlPie; //设置图形
xlChart.SetSourceData(objsheet.get_Range("A1:A3, C1:C3"), Excel.XlRowCol.xlColumns); //两种方法都可以
// xlChart.SetSourceData(objsheet.Range["A1:A3", "C1:C3"], Excel.XlRowCol.xlColumns);
//加border和居中设置
Excel.Range rangesummary110 = objsheet.Range["A1", "C3"];
rangesummary110.Borders.Color = 0;
rangesummary110.Borders.Weight = 2;
rangesummary110.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//设置属性标签
xlChart.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd); //数据标签
xlChart.SetElement(MsoChartElementType.msoElementLegendBottom); //设为底部显示
xlChart.SetElement(MsoChartElementType.msoElementChartTitleAboveChart); //设置标题
objsheet.Range["F:F"].ColumnWidth = 20.5; //设置宽度
xlChart.ChartTitle.Text = "客户占比(2020.3.23-3.28)";
//objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, "合格率");//xlLocationAutomatic :Excel 控制图表位置。
objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); // xlLocationAsObject,将图表嵌入到现有工作表中。
// oResizeRange = (Excel.Range)objsheet.Rows.get_Item(7, Missing.Value);
objsheet.Shapes.Item("Chart 1").Top = 150; //调图表的位置上边距
objsheet.Shapes.Item("Chart 1").Left = 10;
objsheet.Shapes.Item("Chart 1").Width = 200; //调图表的宽度
objsheet.Shapes.Item("Chart 1").Height = 250; //调图表的高度
///////////////////////////////////////////////////////////////
#region 管理人员
int col = 6;
objExcel.Cells[2, col] = "日期";
objExcel.Cells[2, col + 1] = "产品名称";
objExcel.Cells[2, col + 2] = "数量";
int row = 3;
for (int i = 0; i < txDataColumn.Count; i++)
{
objExcel.Cells[row, col] = txDataColumn[i];
row++;
}
row = 3;
for (int i = 0; i < tyDataOk.Count; i++)
{
objExcel.Cells[row, col + 1] = tyDataOk[i];
row++;
}
row = 3;
for (int i = 0; i < tyDataNo.Count; i++)
{
objExcel.Cells[row, col + 2] = tyDataNo[i];
row++;
}
#endregion
//假定要合并excel文件中第2行的1~3列，并且显示黑色边框7a686964616fe4b893e5b19e31333264656666
//objExcel.ActiveSheet.Columns[7] = 5;
Microsoft.Office.Interop.Excel.Range rangesummary1 = objsheet.Range["F3", "F5"];
Microsoft.Office.Interop.Excel.Range rangesummary2 = objsheet.Range["F6", "F8"];
Microsoft.Office.Interop.Excel.Range rangesummary3 = objsheet.Range["F9", "F12"];
Excel.Range rangesummary120 = objsheet.Range["F2", "H12"];
rangesummary120.Borders.Color = 0;
rangesummary120.Borders.Weight = 2;
rangesummary120.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rangesummary120.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
rangesummary1.Value2 = Type.Missing;
rangesummary1.Merge(Type.Missing);
rangesummary1.Value2 = "2020-03-23";
rangesummary2.Value2 = Type.Missing;
rangesummary2.Merge(Type.Missing);
rangesummary2.Value2 = "2020-03-24";
rangesummary3.Value2 = Type.Missing;
rangesummary3.Merge(Type.Missing);
rangesummary3.Value2 = "2020-03-25";
//柱状图
Excel.Chart xlChart2 = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[2, 6], (Excel.Range)objsheet.Cells[3 + txDataColumn.Count - 1, 8]);
//1-cellRange:数据源的范围，2-图表类型，3-Type.Missing，4-在图表上将列或行用作数据系列的方式，5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行，7-图表是否有图例，8、9、10-设置标题
xlChart2.ChartWizard(cellRange,
Excel.XlChartType.xlColumnClustered, //2-图表类型
Type.Missing, //内置自动套用格式的选项编号。 可为从 1 到 10 的数字，其取值依赖于库的类型。 如果省略此参数, 则 Excel 根据库的类型和数据源选择默认值。
Excel.XlRowCol.xlColumns, //在图表上将列或行用作数据系列的方式
2, //第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行--这个2代表数据源的x轴由两个参数确认；可以不写，默认的就很难看
1, //--这个2代表数据源的x轴由1个参数确认；可以不写，默认的就很难看
true, //图表是否有图例
"每日总量统计", //以下都是标题
Type.Missing,
Type.Missing,
"");
xlChart2.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd);
xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);
Excel.Range oResizeRange1 = (Excel.Range)objsheet.Rows.get_Item(1);
Excel.Range oResizeRange2 = (Excel.Range)objsheet.Columns.get_Item(10);
objsheet.Shapes.Item("Chart 2").Top = (float)oResizeRange1.Top; //调图表的位置上边距--1行的高度
objsheet.Shapes.Item("Chart 2").Left = (float)(double)oResizeRange2.Left; //调图表的位置左边距--10列的宽度
objsheet.Shapes.Item("Chart 2").Width = 300; //调图表的宽度
objsheet.Shapes.Item("Chart 2").Height = 200; //调图表的高度
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
////关闭Excel应用
//if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
//if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
//if (objExcel != null) objExcel.Quit();
//objsheet = null;
//objWorkbook = null;
//objExcel = null;
objsheet.Name = "牙模盒使用记录";
objsheet.Tab.Color = Excel.XlThemeColor.xlThemeColorLight1;
ClosePro(fileNameString, objExcel, objWorkbook);
System.Diagnostics.Process.Start(fileNameString);
}
}