/// <summary>
/// Cancella il contenuto del foglio.
/// </summary>
private void Clear()
{
if (_ws.ChartObjects().Count > 0)
{
_ws.ChartObjects().Delete();
}
_ws.Rows.ClearContents();
_ws.Rows.ClearComments();
_ws.Rows.FormatConditions.Delete();
_ws.Rows.EntireRow.Hidden = false;
_ws.Rows.UnMerge();
_ws.Rows.Style = "Normal";
_ws.Rows.RowHeight = Struct.cell.height.normal;
_ws.Columns.ColumnWidth = Struct.cell.width.dato;
_ws.Rows["1:" + (_struttura.rigaBlock - 1)].RowHeight = Struct.cell.height.empty;
_ws.Rows[_struttura.rigaGoto].RowHeight = Struct.cell.height.normal;
_ws.Columns[1].ColumnWidth = Struct.cell.width.empty;
if (_ws.Visible == Excel.XlSheetVisibility.xlSheetVisible)
{
((Excel._Worksheet)_ws).Activate();
_ws.Application.ActiveWindow.FreezePanes = false;
_ws.Cells[_struttura.rigaBlock, 1].Select();
_ws.Application.ActiveWindow.FreezePanes = true;
Workbook.Main.Select();
}
_ws.Application.ScreenUpdating = false;
}
public void DrawPointChart(string chartname, int row, string xname, string yname, string[] xvalues, double[] yvalues)
{
var v = string.Join(";", yvalues);
DrawTable(chartname, row, xvalues, new string[] { v });
var chartobjs = worksheet.ChartObjects(Type.Missing);
var chartobj = chartobjs.Add(20, 15 * row + 45, 500, 200);
chartobj.Chart.ChartWizard(
worksheet.Range[worksheet.Cells[row + 1, 2], worksheet.Cells[row + 2, yvalues.Length + 1]],
Excel.XlChartType.xlLineMarkersStacked, 2, Excel.XlRowCol.xlRows, Type.Missing, 0, false,
chartname, xname, yname, Type.Missing);
}
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;
}
private void buttonGrafik_Click(object sender, EventArgs e)
{
excelOrnek = new EXCEL.Application();
excelKitap = excelOrnek.Workbooks.Add(kayipDeger);
excelSayfa = (EXCEL.Worksheet)excelKitap.Worksheets.get_Item(1);
excelSayfa.Cells[1, 1] = ""; excelSayfa.Cells[1, 2] = "İstanbul"; excelSayfa.Cells[1, 3] = "Ankara";
excelSayfa.Cells[2, 1] = "Ocak"; excelSayfa.Cells[2, 2] = "350"; excelSayfa.Cells[2, 3] = "500";
excelSayfa.Cells[3, 1] = "Şubat"; excelSayfa.Cells[3, 2] = "300"; excelSayfa.Cells[3, 3] = "450";
excelSayfa.Cells[4, 1] = "Mart"; excelSayfa.Cells[4, 2] = "400"; excelSayfa.Cells[4, 3] = "600";
EXCEL.ChartObjects excelGrafikler = (EXCEL.ChartObjects)excelSayfa.ChartObjects(Type.Missing);
EXCEL.ChartObject excelGrafik = (EXCEL.ChartObject)excelGrafikler.Add(10, 80, 300, 250);
EXCEL.Chart grafikSayfa = excelGrafik.Chart;
EXCEL.Range grafikAlan = excelSayfa.get_Range("A1", "C4");
grafikSayfa.SetSourceData(grafikAlan, kayipDeger);
grafikSayfa.ChartType = EXCEL.XlChartType.xlColumnClustered;
excelKitap.SaveAs("C:\\Users\\ufukcelik\\OneDrive\\ornekGrafik.xlsx",
EXCEL.XlFileFormat.xlWorkbookNormal,
kayipDeger, kayipDeger, kayipDeger, kayipDeger,
EXCEL.XlSaveAsAccessMode.xlExclusive,
kayipDeger, kayipDeger, kayipDeger, kayipDeger, kayipDeger);
excelKitap.Close(true, kayipDeger, kayipDeger);
excelOrnek.Quit();
richTextBoxEXCEL.Text = "Excel Grafik Dosyası oluşturuldu";
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelOrnek);
excelOrnek = null;
GC.Collect();
}
private void AddBugChart(
ExcelInterop.Worksheet sheet,
int chartStartRow, string chartStartCol, int chartEndRow, string chartEndCol,
string bugDataSource, string chartTitle)
{
ExcelInterop.Range bugChartRange = sheet.Range[sheet.Cells[chartStartRow, chartStartCol], sheet.Cells[chartEndRow, chartEndCol]];
ExcelInterop.ChartObjects charts = sheet.ChartObjects(Type.Missing) as ExcelInterop.ChartObjects;
Utility.AddNativieResource(charts);
ExcelInterop.ChartObject bugChartObject = charts.Add(0, 0, bugChartRange.Width, bugChartRange.Height);
Utility.AddNativieResource(bugChartObject);
ExcelInterop.Chart bugChart = bugChartObject.Chart;//设置图表数据区域。
Utility.AddNativieResource(bugChart);
ExcelInterop.Range datasource = sheet.get_Range(bugDataSource);//不是："B14:B25","F14:F25"
Utility.AddNativieResource(datasource);
bugChart.SetSourceData(datasource);
bugChart.ChartType = ExcelInterop.XlChartType.xlColumnClustered;
//bugChart.ChartWizard(datasource, XlChartType.xlColumnClustered, Type.Missing, XlRowCol.xlColumns, 1, 1, false, chartTitle, "", "", Type.Missing);
bugChart.ApplyDataLabels();//图形上面显示具体的值
//将图表移到数据区域之下。
bugChartObject.Left = Convert.ToDouble(bugChartRange.Left) + 20;
bugChartObject.Top = Convert.ToDouble(bugChartRange.Top) + 20;
bugChartObject.Locked = false;
bugChartObject.Select();
bugChartObject.Activate();
}
private void button1_Click(object sender, EventArgs e)
{
SaveFileDialog s = new SaveFileDialog();
s.Filter = "File Excel|*.xls";
if (s.ShowDialog() == DialogResult.OK)
{
string path = s.FileName;
reportViewer1.ExportDialog(reportViewer1.LocalReport.ListRenderingExtensions()[0], "", path);
try
{
excel.Application app = new excel.Application();
excel.Workbook book = app.Workbooks.Open(path);
excel.Worksheet sheet = book.Sheets[1];
excel.ChartObjects charts = (excel.ChartObjects)sheet.ChartObjects(Type.Missing);
excel.ChartObject chart = charts.Add(20, 100, 700, 250);
chart.Chart.SetSourceData(sheet.UsedRange);
chart.Chart.ChartType = excel.XlChartType.xlColumnClustered;
book.Save();
book.Close(true);
app.Quit();
MessageBox.Show($"File was created at {path}");
}
catch (Exception)
{
MessageBox.Show("Missing library");
}
}
}
public void Excel_Create(string file_name, int latin, int kiril, int num)
{
exel.Application application = new exel.Application();
exel.Workbook workbook = application.Workbooks.Add();
exel.Worksheet worksheet = (exel.Worksheet)workbook.ActiveSheet;
worksheet.Name = file_name;
worksheet.Cells[1, 1] = "Латинские буквы";
worksheet.Cells[2, 1] = "Кириллица";
worksheet.Cells[3, 1] = "Числа";
worksheet.Cells[1, 2] = latin;
worksheet.Cells[2, 2] = kiril;
worksheet.Cells[3, 2] = num;
worksheet.Columns.AutoFit();
exel.ChartObjects chartObjects = (exel.ChartObjects)worksheet.ChartObjects(Type.Missing);
exel.ChartObject chartObject = chartObjects.Add(50, 50, 250, 250);
exel.Chart chart = chartObject.Chart;
exel.SeriesCollection seriesCollection = (exel.SeriesCollection)chart.SeriesCollection(Type.Missing);
exel.Series series = seriesCollection.NewSeries();
chart.ChartType = exel.XlChartType.xl3DPie;
series.XValues = worksheet.get_Range("A1", "A3");
series.Values = worksheet.get_Range("B1", "B3");
workbook.SaveAs();
workbook.Close();
application.Quit();
}
public void FinishSheet(string name)
{
Excel.Worksheet sheet = sheets[name];
int rows = sheetRows[name];
//Table
FormatAsTable(sheet.get_Range("A2", "E" + (rows - 1)), "TheTable");
//Chart
Excel.ChartObjects xlCharts = (Excel.ChartObjects)sheet.ChartObjects(Type.Missing);
Excel.ChartObject chartObj = (Excel.ChartObject)xlCharts.Add(500, 10, 800, 300);
chartObj.Chart.ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers;
//Erste Series
addChartSeries(chartObj, sheet.get_Range("A3", "A" + (rows - 1)), sheet.get_Range("E3", "E" + (rows - 1)), "sell");
//Zweite Series
addChartSeries(chartObj, sheet.get_Range("A3", "A" + (rows - 1)), sheet.get_Range("D3", "D" + (rows - 1)), "buy");
//Count Chart
Excel.ChartObject countChart = (Excel.ChartObject)xlCharts.Add(500, 330, 800, 300);
countChart.Chart.ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers;
//Erste Series
addChartSeries(countChart, sheet.get_Range("A3", "A" + (rows - 1)), sheet.get_Range("C3", "C" + (rows - 1)), "Count");
}
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);
}
}
/// <summary>
/// re modify the chart with numbers of row changed
/// </summary>
/// <param name="numRows"></param>
/// <returns></returns>
public bool ModifyChart(int numRows)
{
try
{
object misValue = System.Reflection.Missing.Value;
// assign fomular for each sheet
for (int i = 2; i <= xlWorkBook.Worksheets.Count; i++)// sheet
{
xlWorkSheet = xlWorkBook.Worksheets[i];
// delete old chart
ChartObjects objs = xlWorkSheet.ChartObjects();
int chartCount = objs.Count;
for (int j = 2; j <= chartCount; j++)
{
ChartObject currChartObj = objs.Item(j - 1);
Microsoft.Office.Interop.Excel.Range chartRange;
Microsoft.Office.Interop.Excel.Chart chartPage = currChartObj.Chart;
chartRange = xlWorkSheet.Range[String.Format("A1:A{0},{1}1:{1}{0}", numRows + "", (char)((int)'A' + j - 1))];
chartPage.SetSourceData(chartRange, misValue);
}
}
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
private Excel.Chart CreateNewBubbleChart(Excel.Worksheet excelWorksheet, double chartWidth, double chartHeight, double bubbleScaleFactor)
{
var excelChartObjects = (Excel.ChartObjects)excelWorksheet.ChartObjects();
var chartObject = excelChartObjects.Add(10, 80, chartWidth - HeuristicExtraWidth, chartHeight - HeuristicExtraHeight);
var bubbleChart = chartObject.Chart;
bubbleChart.ChartType = Excel.XlChartType.xlBubble3DEffect;
bubbleChart.ChartGroups(1).BubbleScale = Clamp(100 * bubbleScaleFactor * DefaultBubbleScale, 0, 300);
var xAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlCategory);
xAxis.HasTitle = true;
xAxis.AxisTitle.Caption = "average Hourly";
xAxis.HasMajorGridlines = true;
xAxis.MajorGridlines.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbGainsboro;
var yAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlValue);
yAxis.HasTitle = true;
yAxis.AxisTitle.Caption = "TG2 Medel";
yAxis.HasMajorGridlines = true;
yAxis.TickLabels.NumberFormat = "0%";
yAxis.MajorGridlines.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbGainsboro;
return(bubbleChart);
}
public TreemapChart Draw(Excel.Worksheet sheet)
{
Excel.ChartObjects cos = sheet.ChartObjects();
Excel.ChartObject co = cos.Add(ChartArea.Left, ChartArea.Top, ChartArea.Width, ChartArea.Height);
Chart = co.Chart;
Draw();
return(this);
}
internal Excel.ChartObject newEnPIChart()
{
Excel.Range start = BottomCell().get_Offset(2, 0);
double topleft;
if (!double.TryParse(start.Top.ToString(), out topleft))
{
topleft = 0;
}
start.EntireRow.RowHeight = Utilities.Constants.CHART_HEIGHT * 1.1;
Excel.ChartObject CO = ((Excel.ChartObjects)thisSheet.ChartObjects(System.Type.Missing))
.Add(10, topleft, Utilities.Constants.CHART_WIDTH, Utilities.Constants.CHART_HEIGHT);
CO.Placement = Excel.XlPlacement.xlMove;
return(CO);
}
public void ExportCharts(string item, string folder, string format)
{
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)_xlWorkBook.Worksheets.Item[item];
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
foreach (Excel.ChartObject xlChart in xlCharts)
{
Excel.Chart chartPage = xlChart.Chart;
chartPage.Export(Path.Combine(folder, $"{chartPage.Name}.{format}"), format, _misValue);
}
}
public void generarHistograma(int intervalos)
{
Excel.Range rangoValores;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
//Rango de valores del grafico
rangoValores = xlWorkSheet.get_Range("D5", "D" + (intervalos + 5));
}
private void CreateChart(Excel.Worksheet worksheet)
{
Excel.Range usedRange = worksheet.UsedRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(300, 80, 600, 350);
Excel.Chart chartPage = myChart.Chart;
chartPage.ChartType = Excel.XlChartType.xlXYScatterLines;
chartPage.SetSourceData(usedRange, System.Reflection.Missing.Value);
}
private Excel.Chart MakeColumnChart(Excel.Worksheet xlWorkSheet)
{
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
var chartRange = xlWorkSheet.Range["A1", "d5"];
chartPage.SetSourceData(chartRange, _misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
return(chartPage);
}
/// <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);
}
public void SetChartData(int dataCol, int legCol, int rowStart, int rowEnd, Excel.XlChartType chartType, bool showLegend)
{
Excel.Worksheet wss = (Excel.Worksheet)ewb.Sheets["Graph"];
Excel.ChartObjects cos = (Excel.ChartObjects)wss.ChartObjects(Type.Missing);
Excel.ChartObject co = (Excel.ChartObject)cos.Item("Диаграмма 1");
Excel.Chart ch = (Excel.Chart)co.Chart;
ch.HasLegend = showLegend;
ch.ChartType = chartType;
ch.SetSourceData(ewsh.Range[ewsh.Cells[rowStart, legCol], ewsh.Cells[rowEnd, dataCol]], Type.Missing);
ReleaseObject(ch);
ReleaseObject(co);
ReleaseObject(cos);
ReleaseObject(wss);
}
private static void CreateBoxCart(Excel.Worksheet targetSheet, Excel.Range xValues,
String kwartyl1Desc, Excel.Range kwartyl1Values,
String medianaDesc, Excel.Range medianaValues,
String kwartyl3Desc, Excel.Range kwartyl3Values,
Excel.Range ErrorMaksimum, Excel.Range ErrorMadiana, Excel.Range ErrorMinimum)
{
Excel.ChartObjects xlCharts = (Excel.ChartObjects)targetSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 100, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartPage.HasLegend = false;
chartPage.HasTitle = true;
SeriesCollection seriesCollection = chartPage.SeriesCollection();
Series series1 = seriesCollection.NewSeries();
series1.Name = kwartyl1Desc;
series1.XValues = xValues;
series1.Values = kwartyl1Values;
series1.HasErrorBars = true;
series1.ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludeMinusValues, XlErrorBarType.xlErrorBarTypeCustom, ErrorMinimum, ErrorMinimum);
series1.Format.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;
series1 = seriesCollection.NewSeries();
series1.Name = medianaDesc;
series1.XValues = xValues;
series1.Values = medianaValues;
//series1.Format.Line.Parent
series1.Format.Line.Weight = 2.0F;
series1.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTriStateMixed; //Tri-State
series1.Format.Line.ForeColor.RGB = (int)Microsoft.Office.Interop.Excel.XlRgbColor.rgbBlack;
//series1.Format.Line.ForeColor.RGB = (int)XlRgbColor.rgbBlack;
//series1.Format.Line.Weight = 5;
series1 = seriesCollection.NewSeries();
series1.Name = kwartyl3Desc;
series1.XValues = xValues;
series1.Values = kwartyl3Values;
series1.Format.Line.Weight = 2.0F;
series1.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTriStateMixed; //Tri-State
series1.Format.Line.ForeColor.RGB = (int)Microsoft.Office.Interop.Excel.XlRgbColor.rgbBlack;
series1.HasErrorBars = true;
series1.ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludePlusValues, XlErrorBarType.xlErrorBarTypeCustom, ErrorMaksimum, ErrorMaksimum);
chartPage.ChartType = Excel.XlChartType.xlColumnStacked;
}
private void GenerateSummarySheet(Excel.Worksheet summarySheet)
{
summarySheet.Name = "收益图表汇总";
Excel.Worksheet curSheet = null;
Excel.ChartObject chartObj = null;
int rowNumOfPage = 43;
int sheetCount = _excelEdit._wb.Sheets.Count;
//设置summarySheet的打印范围
string startCell = "$A$1";
string endCell = "$P$" + ((sheetCount - 1) * rowNumOfPage).ToString();
summarySheet.PageSetup.PrintArea = startCell + ":" + endCell;
//拷贝所有SubjectSheet的合计收益Chart
for (int i = 2; i <= sheetCount; i++) //第一个Sheet为汇总Sheet，所以 i 初始值为2
{
curSheet = _excelEdit._wb.Sheets[i];
if (curSheet == null)
{
continue;
}
//合计收益Chart
chartObj = curSheet.ChartObjects(1);
if (chartObj == null)
{
continue;
}
int startRowIndex = rowNumOfPage * (i - 2) + 2;
chartObj.Chart.ChartArea.Copy();
Excel.Range range = summarySheet.Cells[startRowIndex, 2];
summarySheet.Paste(range, Type.Missing);
}
//设置summarySheet的ChartArea大小
Excel.ChartArea chartArea = null;
for (int i = 1; i < sheetCount; i++)
{
chartObj = summarySheet.ChartObjects(i);
chartArea = chartObj.Chart.ChartArea;
chartArea.Height = 510;
chartArea.Width = 780;
}
}
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();
}
}
}
private void napraviBtn_Click(object sender, EventArgs e)
{
Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range selektovano = Globals.ThisAddIn.Application.Selection;
var chart = ws.ChartObjects().Add(60, 10, 300, 300).Chart;
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
chart.HasLegend = false;
chart.ChartWizard(Source: selektovano,
Title: nazivTxt.Text,
CategoryTitle: kategorijaTxt.Text,
ValueTitle: valueTxt.Text);
this.Visible = false;
}
//添加图表路径
private void addGraph()
{
oxml.Path.Add(new OfficeElement("Workbooks", "0"));
if (radioSingleChart.Checked == true)
{
oxml.Path.Add(new OfficeElement("Charts",(comboChart.SelectedIndex + 1).ToString() + ":" + "0")); //该图表是所有图表中的第几个
ch = (Excel.Chart)xls.Sheets[chartIndex[comboChart.SelectedIndex]]; //获取图表
}
else
{
oxml.Path.Add(new OfficeElement("Charts",
(comboSheet.SelectedIndex + 1).ToString() //该工作簿是所有工作簿的第几个
+ ":" + (comboChartInSheet.SelectedIndex + 1).ToString())); //该图表是工作簿里面的第几个图表
ws = (Excel.Worksheet)xls.Sheets[worksheetIndex[comboSheet.SelectedIndex]];
ch = ((Excel.ChartObject)ws.ChartObjects(comboChartInSheet.SelectedIndex + 1)).Chart; //获取图表
}
}
internal static void AddChart(
Excel.Worksheet pivotWorkSheet,
string myTitle,
Excel.Range pivotData,
XlChartType type)
{
var chartObjects = (Excel.ChartObjects)pivotWorkSheet.ChartObjects();
var pivotChart = chartObjects.Add(Left: 60, Top: 250, Width: 325, Height: 275);
pivotChart.Chart.ChartWizard(Source: pivotData,
Gallery: type,
Title: myTitle,
HasLegend: true,
CategoryLabels: 6,
SeriesLabels: 0);
pivotChart.Chart.Location(Where: Excel.XlChartLocation.xlLocationAsNewSheet, Name: Type.Missing);
}
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);
}
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";
}
/// <summary>
/// Обновление Диаграммы
/// </summary>
internal void UpdateDiagramm()
{
Excel.ChartObject shp = SheetUrv11.ChartObjects("Chart 2");
Excel.Chart chartPage = shp.Chart;
Excel.SeriesCollection seriesCol = (Excel.SeriesCollection)chartPage.SeriesCollection();
Excel.FullSeriesCollection fullColl = chartPage.FullSeriesCollection();
Debug.WriteLine(fullColl.Count);
int lastCol = GetLastColumnUrv(SheetUrv11, _rowStart);
int lastRow = GetLastRowUrv11();
int ix = 1;
string letterCost = "G";
fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10";
fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}";
fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}";
for (int col = 9; col <= lastCol; col += 3)
{
Excel.Range cellFirstCost = SheetUrv11.Cells[_rowStart, col];
string text = cellFirstCost.Value?.ToString() ?? "";
if (string.IsNullOrEmpty(text))
{
continue;
}
letterCost = ExcelHelper.GetColumnLetter(cellFirstCost);
ix++;
if (ix > fullColl.Count)
{
seriesCol.NewSeries();
}
fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10";
fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}";
fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}";
}
if (ix < fullColl.Count)
{
for (int i = ix + 1; i <= fullColl.Count; i++)
{
fullColl.Item(i).Delete();
}
}
}
private Excel.Chart MakePolarChart(Excel.Worksheet xlWorkSheet)
{
xlWorkSheet.Range["A1", "A5"].Value2 = 22;
xlWorkSheet.Range["B1", "B5"].Value2 = 55;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartPage.SetSourceData(xlWorkSheet.Range["A1", "B5"],
Excel.XlRowCol.xlColumns);
chartPage.ChartType = Excel.XlChartType.xlRadar;
Excel.ChartGroup group =
(Excel.ChartGroup)chartPage.RadarGroups(1);
group.HasRadarAxisLabels = true;
return(chartPage);
}
/// <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 getChartInSheet(int index)
{
comboChartInSheet.Items.Clear();
if (index != 0)
{
ws = (Excel.Worksheet)xls.Sheets[index];
chobjs = (Excel.ChartObjects)ws.ChartObjects(nullobj);
//Console.WriteLine(chobjs.Count);
for (int i = 1; i <= chobjs.Count; i++)
{
chobj = (Excel.ChartObject)ws.ChartObjects(i);
ch = chobj.Chart;
string str = chobj.Name + ": ";
try
{
string title = ch.ChartTitle.Text;
str += title;
}
catch { str += "(未命名图表)"; }
comboChartInSheet.Items.Add(str);
}
}
if (comboChartInSheet.Items.Count == 0)
{
comboChartInSheet.Items.Add("(该页无图表！)");
buttonGraph.Enabled = false;
groupGraphPoint.Enabled = false;
}
else
{
buttonGraph.Enabled = true;
groupGraphPoint.Enabled = true;
}
comboChartInSheet.SelectedIndex = 0;
}
