private Worksheet CreateXYScatterChartWithClusters(int[] clusters)
{
Worksheet worksheet = Globals.Factory.GetVstoObject(
Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
if (worksheet.Controls.Contains("clustering"))
{
worksheet.Controls.Remove("clustering");
}
var chart = worksheet.Controls.AddChart(150, 10, 450, 400, "clustering");
chart.ChartType = XlChartType.xlXYScatter;
chart.HasLegend = false;
Axis vertAxis = (Axis)chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
vertAxis.HasMajorGridlines = false;
vertAxis.MaximumScaleIsAuto = true;
vertAxis.MinimumScaleIsAuto = true;
chart.SetSourceData(_target);
var series1 = (Series)chart.SeriesCollection(1);
for (var i = 0; i < clusters.Length; i++)
{
var p = (Point)series1.Points(i + 1);
p.MarkerBackgroundColor = _colors[clusters[i] % _colors.Length];
}
return(worksheet);
}
public static object GetCustomPropertyValue(this Worksheet worksheet, string name)
{
var customProperty = worksheet
.CustomProperties
.Cast <CustomProperty>()
.FirstOrDefault(v => name.Equals(v.Name));
return(customProperty?.Value);
}
public OrderEntity(Host.Excel.Worksheet worksheet)
{
// セルの書式設定を文字列へ
worksheet.Range["A:B"].NumberFormatLocal = "@";
// アクティブセルをテーブルの外にしてデザインタブがアクティブになるのを防ぐ
worksheet.Range["A3"].Select();
// ListObject追加
Native.Excel.Range cell = worksheet.Range["$A$1:$C$2"];
list1 = worksheet.Controls.AddListObject(cell, Guid.NewGuid().ToString("N"));
}
private void OtherMethod()
{
Microsoft.Office.Interop.Excel.Worksheet nativeWorksheet =
Globals.ThisAddIn.Application.ActiveSheet;
if (nativeWorksheet != null)
{
Microsoft.Office.Tools.Excel.Worksheet vstoSheet =
Globals.Factory.GetVstoObject(nativeWorksheet);
}
}
//*************************************************************************
// Constructor: SheetHelper()
//
/// <summary>
/// Initializes a new instance of the <see cref="SheetHelper" /> class.
/// </summary>
///
/// <param name="worksheet">
/// The worksheet that owns this object.
/// </param>
///
/// <param name="table">
/// The NodeXL table in the worksheet.
/// </param>
//*************************************************************************
public SheetHelper
(
Microsoft.Office.Tools.Excel.Worksheet worksheet,
Microsoft.Office.Tools.Excel.ListObject table
)
{
m_oWorksheet = worksheet;
m_oTable = table;
// AssertValid();
}
//*************************************************************************
// Constructor: Sheets1And2Helper()
//
/// <summary>
/// Initializes a new instance of the <see cref="Sheets1And2Helper" />
/// class.
/// </summary>
///
/// <param name="worksheet">
/// The worksheet that owns this object.
/// </param>
///
/// <param name="table">
/// The edge or vertex table.
/// </param>
//*************************************************************************
public Sheets1And2Helper
(
Microsoft.Office.Tools.Excel.Worksheet worksheet,
Microsoft.Office.Tools.Excel.ListObject table
)
: base(worksheet, table)
{
// (Do nothing.)
AssertValid();
}
private Sheet Instantiate(Worksheet worksheet)
{
var allorsType = (string)worksheet.GetCustomPropertyValue("AllorsType");
switch (allorsType)
{
case nameof(PeopleSheet):
return(new PeopleSheet(this, worksheet));
}
return(null);
}
public Sheet this[Worksheet worksheet]
{
get
{
if (worksheet == null)
{
return(null);
}
this.SheetByVstoWorksheet.TryGetValue(worksheet, out var sheet);
return(sheet);
}
}
public static void SetCustomPropertyValue(this Worksheet worksheet, string name, object value)
{
var customProperty = worksheet
.CustomProperties
.Cast <CustomProperty>()
.FirstOrDefault(v => name.Equals(v.Name));
if (customProperty == null)
{
worksheet.CustomProperties.Add("AllorsType", value);
}
else
{
customProperty.Value = value;
}
}
private void skeleton_Click(object sender, RibbonControlEventArgs e)
{
var table = new BindingList <Order>();
// 新規シート追加
Native.Excel.Worksheet newWorksheet = (Native.Excel.Worksheet)Globals.ThisAddIn.Application.Worksheets.Add();
Host.Excel.Worksheet worksheet = Globals.Factory.GetVstoObject(newWorksheet);
var v = new Views.OrderEntity(worksheet)
{
DataContext = table
};
v.SetDataBinding(typeof(Order).GetProperties().Select(x => x.Name).ToArray());
Worksheets.Add(v);
}
internal void AddChart()
{
// Demo 3
Worksheet worksheet = Globals.Factory.GetVstoObject(
Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
//var currentRange = Globals.ThisAddIn.Application.ActiveWindow.RangeSelection;
Worksheet worksheet2 = Globals.Factory.GetVstoObject(
Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[2]);
var currentRange = worksheet2.Range["A1", "H20"];
Chart chart = worksheet2.Controls.AddChart(currentRange, "ratings");
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
var listObject = FindListObject("netflixTitles");
var end = listObject.DataBodyRange.get_End(XlDirection.xlDown).Row;
Excel.Range cells = worksheet.Range["F1", "F" + end.ToString()];
chart.SetSourceData(cells, missing);
chart.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
}
public PeopleSheet(Sheets sheets, Worksheet worksheet)
: base("PeopleSheet", sheets, worksheet)
{
}
/// <summary>
/// Dat formato a la hoja para cargar los items contractuales y los StockCodes asociados a estas.
/// </summary>
private void FormatSheet()
{
_excelApp = Globals.ThisAddIn.Application;
var excelBook = _excelApp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet excelSheet = excelBook.ActiveSheet;
excelSheet.Name = SheetName01;
_worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
_eFunctions.SetDBSettings(drpEnviroment.SelectedItem.Label);
if (_cells == null)
_cells = new ExcelStyleCells(_excelApp);
_cells.GetCell("A1").Value = "CERREJÓN";
_cells.GetCell("B1").Value = "CREATE STOCKCODES INST";
_cells.GetCell("A3").Value = "Contract No";
_cells.GetCell("A4").Value = "Contract Prefix";
_cells.GetCell("A5").Value = "Stock Code";
_cells.GetCell("B5").Value = "Stock Description";
_cells.GetCell("C5").Value = "Unit Of Issue";
_cells.GetCell("D5").Value = "Part No";
_cells.GetCell("E5").Value = "Result";
//AA y MI fueron establecidos en la estrategia de Instalaciones como los prefijos para identificar los contratos
var optionList = new List<string> {"AA", "MI"};
_cells.SetValidationList(_cells.GetCell("B4"), optionList);
_cells.GetCell("A1").Style = _cells.GetStyle(StyleConstants.HeaderDefault);
_cells.GetCell("A3").Style = _cells.GetStyle(StyleConstants.Option);
_cells.GetCell("A4").Style = _cells.GetStyle(StyleConstants.Option);
_cells.GetCell("B1").Style = _cells.GetStyle(StyleConstants.HeaderDefault);
_cells.GetCell("B3").Style = _cells.GetStyle(StyleConstants.Select);
_cells.GetCell("B4").Style = _cells.GetStyle(StyleConstants.Select);
_cells.GetRange("A5", "D5").Style = _cells.GetStyle(StyleConstants.TitleRequired);
_cells.GetCell("E5").Style = _cells.GetStyle(StyleConstants.TitleInformation);
_cells.MergeCells("A1", "A2");
var contractRange = _worksheet.Controls.AddNamedRange(_worksheet.Range["B3:B4"], "ContractRange");
contractRange.Change += changesCotractPrefixRange_Change;
excelSheet.Cells.Columns.AutoFit();
excelSheet.Cells.Rows.AutoFit();
}
