public void Format_Series(PowerPoint.Chart myChart, bool hasYAxis, string chType)
{
try
{
//bool secAxis = false;
PowerPoint.SeriesCollection sc = myChart.SeriesCollection();
if (chType == "Pie" || chType == "Doughnut" || chType == "Surface")
{
return;
}
if (chType == "Column")
{
for (int i = 1; i <= sc.Count; i++)
{
PowerPoint.Series chartSeries = myChart.SeriesCollection(i);
myChart.SeriesCollection(i).Format.Line.Visible = MsoTriState.msoTrue;
myChart.SeriesCollection(i).Format.Line.ForeColor.RGB = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
chartSeries.Format.Line.Weight = (float)0.75;
}
}
}
catch (Exception err)
{
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "Format_PlotArea");
}
}
//'This function formats the y-grids line style, tick marks, title and ticklables font name, style, size, color, sub-super script, underline
public void Format_YGrids(PowerPoint.Chart myChart, bool hasYAxis, string chType, bool ch3D)
{
try
{
bool hasYGrids = false;
if (chType == "Pie" || chType == "Doughnut")
{
return;
}
if (chType == "Radar" || (hasYAxis && ch3D == true))
{
hasYGrids = true;
}
if (hasYGrids == true)
{
myChart.Axes(XlAxisType.xlValue).HasMajorGridlines = true;
myChart.Axes(XlAxisType.xlValue).MajorGridlines.Border.Color = System.Drawing.Color.FromArgb(127, 127, 127).ToArgb();
myChart.Axes(XlAxisType.xlValue).MajorGridlines.Border.LineStyle = PowerPoint.XlLineStyle.xlContinuous;
}
else if (hasYGrids == false)
{
myChart.Axes(XlAxisType.xlValue).HasMajorGridlines = false;
}
}
catch (Exception err)
{
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "Format_YGrids");
}
}
public void Format_XGrids(PowerPoint.Chart myChart, bool hasXGrids, string chType)
{
try
{
if (chType == "Pie" || chType == "Doughnut")
{
if (hasXGrids == true)
{
if (myChart.Axes(XlAxisType.xlCategory).HasMajorGridlines == true)
{
myChart.Axes(XlAxisType.xlCategory).MajorGridlines.Border.Color = System.Drawing.Color.FromArgb(127, 127, 127).ToArgb();
myChart.Axes(XlAxisType.xlCategory).MajorGridlines.Border.LineStyle = PowerPoint.XlLineStyle.xlDot;
}
}
else if (hasXGrids == false)
{
myChart.HasAxis[XlAxisType.xlCategory] = false;
}
}
}
catch (Exception err)
{
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "Format_XGrids");
}
}
public void AddChartLegend(PPT.Chart chart, ChartLegend chartLegend)
{
chart.HasLegend = true;
chart.Legend.Font.Italic = chartLegend.italic;
chart.Legend.Font.Bold = chartLegend.bold;
chart.Legend.Font.Underline = chartLegend.underline;
chart.Legend.Font.Size = chartLegend.fontSize;
chart.Refresh();
}
public void AddChartTitle(PPT.Chart chart, ChartTitle chartTitle)
{
chart.HasTitle = true;
chart.ChartTitle.Text = chartTitle.titleText;
chart.ChartTitle.Font.Italic = chartTitle.italic;
chart.ChartTitle.Font.Bold = chartTitle.bold;
chart.ChartTitle.Font.Underline = chartTitle.underline;
chart.ChartTitle.Font.Size = chartTitle.fontSize;
chart.Refresh();
}
//Growth Rates
public void TSCP_Callback51(Office.IRibbonControl rib)
{
PowerPoint.Application ppApp = Globals.ThisAddIn.Application;
pptfunctions funObj = new pptfunctions();
Shapecheck shpObj = new Shapecheck();
frmChartcalc chartObj = new frmChartcalc();
//frmCalculator frmObj = new frmCalculator();
Shapecheck PPTshpchk = new Shapecheck();
List <string> SelectedCharts = new List <string>();
SelectedCharts = PPTshpchk.FindSelectedCharts();
PowerPoint.Presentation ActivePPT = Globals.ThisAddIn.Application.ActivePresentation;
int numSelCht = SelectedCharts.Count;
if (numSelCht == 0)
{
MessageBox.Show("Please select a chart for CAGR/AAGR calculation.", PPTAttribute.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
int sld_num = ppApp.ActiveWindow.Selection.SlideRange.SlideNumber;
PowerPoint.Chart myChart = ActivePPT.Slides[sld_num].Shapes[SelectedCharts[0]].Chart;
string chType = shpObj.chartType(myChart);
if (funObj.TSCThemeLoaded())
{
if (numSelCht == 0)
{
//frmObj.Show();
MessageBox.Show("Please select a chart CAGR/AAGR calculation.", PPTAttribute.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (numSelCht > 1)
{
MessageBox.Show("Please select a single chart CAGR/AAGR calculation.", PPTAttribute.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (numSelCht == 1)
{
//PPT.Chart myChart =
//string chType = PPTshpchk.chartType();
if (chType != "Pie")
{
chartObj.ShowDialog();
}
else
{
MessageBox.Show("Please select a column chart for CAGR/AAGR Calculation.", PPTAttribute.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
MessageBox.Show("This functionality works with TSC Theme. Please Load TSC theme and try again. Thanks", PPTAttribute.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
PPTAttribute.UserTracker(rib);
}
/// <summary>
/// determines the type of a powerpoint chart
/// </summary>
/// <param name="chart">powerpoint chart</param>
/// <returns>chart type, 0 = 2D chart, 1 = 3D bar chart, 2 = 3D chart which can be fully rotated</returns>
private int GetChartType(Microsoft.Office.Interop.PowerPoint.Chart chart)
{
int result = 0;
if ((chart.ChartType == XlChartType.xl3DArea) ||
(chart.ChartType == XlChartType.xl3DAreaStacked) ||
(chart.ChartType == XlChartType.xl3DAreaStacked100) ||
(chart.ChartType == XlChartType.xl3DColumn) ||
(chart.ChartType == XlChartType.xl3DColumnClustered) ||
(chart.ChartType == XlChartType.xl3DColumnStacked) ||
(chart.ChartType == XlChartType.xl3DColumnStacked100) ||
(chart.ChartType == XlChartType.xl3DLine) ||
(chart.ChartType == XlChartType.xl3DPie) ||
(chart.ChartType == XlChartType.xl3DPieExploded) ||
(chart.ChartType == XlChartType.xlConeCol) ||
(chart.ChartType == XlChartType.xlConeColClustered) ||
(chart.ChartType == XlChartType.xlConeColStacked) ||
(chart.ChartType == XlChartType.xlConeColStacked100) ||
(chart.ChartType == XlChartType.xlConeBarClustered) ||
(chart.ChartType == XlChartType.xlConeBarStacked) ||
(chart.ChartType == XlChartType.xlConeBarStacked100) ||
(chart.ChartType == XlChartType.xlCylinderCol) ||
(chart.ChartType == XlChartType.xlCylinderColClustered) ||
(chart.ChartType == XlChartType.xlCylinderColStacked) ||
(chart.ChartType == XlChartType.xlCylinderColStacked100) ||
(chart.ChartType == XlChartType.xlCylinderBarClustered) ||
(chart.ChartType == XlChartType.xlCylinderBarStacked) ||
(chart.ChartType == XlChartType.xlCylinderBarStacked100) ||
(chart.ChartType == XlChartType.xlPyramidCol) ||
(chart.ChartType == XlChartType.xlPyramidColClustered) ||
(chart.ChartType == XlChartType.xlPyramidColStacked) ||
(chart.ChartType == XlChartType.xlPyramidColStacked100) ||
(chart.ChartType == XlChartType.xlPyramidBarClustered) ||
(chart.ChartType == XlChartType.xlPyramidBarStacked) ||
(chart.ChartType == XlChartType.xlPyramidBarStacked100) ||
(chart.ChartType == XlChartType.xlSurface) ||
(chart.ChartType == XlChartType.xlSurfaceTopView) ||
(chart.ChartType == XlChartType.xlSurfaceTopViewWireframe) ||
(chart.ChartType == XlChartType.xlSurfaceWireframe))
{
result = 2;
}
else if ((chart.ChartType == XlChartType.xl3DBarClustered) ||
(chart.ChartType == XlChartType.xl3DBarStacked) ||
(chart.ChartType == XlChartType.xl3DBarStacked100))
{
result = 1;
}
return(result);
}
public bool chart3D(PowerPoint.Chart myChart)
{
bool chart3dVal = false;
try
{
if (myChart.ChartType == XlChartType.xl3DBarClustered || myChart.ChartType == XlChartType.xl3DBarStacked || myChart.ChartType == XlChartType.xl3DBarStacked100 ||
myChart.ChartType == XlChartType.xlCylinderBarClustered || myChart.ChartType == XlChartType.xlCylinderBarStacked || myChart.ChartType == XlChartType.xlCylinderBarStacked100 ||
myChart.ChartType == XlChartType.xlConeBarClustered || myChart.ChartType == XlChartType.xlConeBarStacked || myChart.ChartType == XlChartType.xlConeBarStacked100 ||
myChart.ChartType == XlChartType.xlPyramidBarClustered || myChart.ChartType == XlChartType.xlPyramidBarStacked || myChart.ChartType == XlChartType.xlPyramidBarStacked100)
{
chart3dVal = true;
}
else if (myChart.ChartType == XlChartType.xl3DColumnClustered || myChart.ChartType == XlChartType.xl3DColumnStacked || myChart.ChartType == XlChartType.xl3DColumnStacked100 ||
myChart.ChartType == XlChartType.xlCylinderColClustered || myChart.ChartType == XlChartType.xlCylinderColStacked || myChart.ChartType == XlChartType.xlCylinderColStacked100 ||
myChart.ChartType == XlChartType.xlConeColClustered || myChart.ChartType == XlChartType.xlConeColStacked || myChart.ChartType == XlChartType.xlConeColStacked100 ||
myChart.ChartType == XlChartType.xlPyramidColClustered || myChart.ChartType == XlChartType.xlPyramidColStacked || myChart.ChartType == XlChartType.xlPyramidColStacked100 ||
myChart.ChartType == XlChartType.xl3DColumn || myChart.ChartType == XlChartType.xlConeCol || myChart.ChartType == XlChartType.xlCylinderCol || myChart.ChartType == XlChartType.xlPyramidCol)
{
chart3dVal = true;
}
else if (myChart.ChartType == XlChartType.xl3DLine)
{
chart3dVal = true;
}
else if (myChart.ChartType == XlChartType.xl3DPie || myChart.ChartType == XlChartType.xl3DPieExploded)
{
chart3dVal = true;
}
else if (myChart.ChartType == XlChartType.xl3DArea || myChart.ChartType == XlChartType.xl3DAreaStacked || myChart.ChartType == XlChartType.xl3DAreaStacked100)
{
chart3dVal = true;
}
else if (myChart.ChartType == XlChartType.xlBubble3DEffect)
{
chart3dVal = true;
}
else if (myChart.ChartType == XlChartType.xlSurface || myChart.ChartType == XlChartType.xlSurfaceWireframe)
{
chart3dVal = true;
}
}
catch (Exception err)
{
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "chart3D");
}
return(chart3dVal);
}
/// <summary>
/// Evaluate a chart in order to determine the number of operations required for exporting it
/// </summary>
/// <param name="c">chart</param>
/// <returns></returns>
private int EvaluateChart(Microsoft.Office.Interop.PowerPoint.Chart c)
{
int numberOfOperations = 1;
//get chart type
int chartType = GetChartType(c);
//does not support rotation (it's plain)
if (chartType == 0)
{
//if horizontal faces number is 0, then no chart will be outputed
if (DocumentService.CHART_HORIZONTAL_FACES > 0)
{
numberOfOperations++;
}
}
else
{
//for every horizontal face
for (int k = 0; k < DocumentService.CHART_HORIZONTAL_FACES; k++)
{
ChartHorizontalView hView = new ChartHorizontalView();
numberOfOperations++;
//for every vertical face
for (int l = 0; l < DocumentService.CHART_VERTICAL_FACES; l++)
{
numberOfOperations++;
}
//some chart types, like 3D pie, does not support elevation less than 0
if (SupportsNegativeElevation(c))
{
//for every vertical face
for (int m = 0; m < DocumentService.CHART_VERTICAL_FACES; m++)
{
numberOfOperations++;
}
}
}
}
return(numberOfOperations);
}
/// <summary>
/// check if a powerpoint chart can be down elevated
/// </summary>
/// <param name="chart">powerpoint chart</param>
/// <returns></returns>
private Boolean SupportsNegativeElevation(Microsoft.Office.Interop.PowerPoint.Chart chart)
{
Boolean result = true;
int originalElevation = chart.Elevation;
try
{
chart.Elevation = -45;
chart.Elevation = originalElevation;
}
catch (Exception)
{
chart.Elevation = originalElevation;
result = false;
}
return(result);
}
public void Format_Legend(PowerPoint.Chart myChart, bool hasLegend)
{
int Num;
try
{
if (hasLegend == true)
{
if (myChart.HasLegend == true)
{
PowerPoint.LegendEntries ln = myChart.Legend.LegendEntries();
Num = ln.Count;
}
else
{
PowerPoint.SeriesCollection sc = myChart.SeriesCollection();
Num = sc.Count;
}
if (Num > 1)
{
if (myChart.HasLegend == false)
{
myChart.HasLegend = true;
}
myChart.Legend.Font.Name = "Calibri";
myChart.Legend.Font.Size = 11;
myChart.Legend.Font.Bold = MsoTriState.msoFalse;
myChart.Legend.Position = PowerPoint.XlLegendPosition.xlLegendPositionBottom;
}
else if (hasLegend == false)
{
if (myChart.HasLegend == true)
{
myChart.Legend.Delete();
}
}
}
}
catch (Exception err)
{
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "Format_Legend");
}
}
private PowerPoint.Chart BuildChart(ReportTable table, int page, int chartid, RowFilter filter)
{
PowerPoint.Slide slide = ppt.ActivePresentation.Slides[page];
PowerPoint.Chart chart = slide.Shapes[chartid].Chart;
chart.ChartData.Activate();
Excel.Workbook book = chart.ChartData.Workbook;
Excel.Worksheet sheet = book.Worksheets[1];
//清空现有的数据
sheet.Cells.Clear();
int insertRow = 0;
string[,] ta = new string[table.dataArray.Length, table.dataArray[0].Length];
for (int i = 0; i < table.dataArray.Length; i++)
{
string[] row = table.dataArray[i];
string[] filteredRow = i == 0 ? row : filter(row);
if (filteredRow != null)
{
for (int j = 0; j < filteredRow.Length; j++)
{
// ta[insertRow, j] = filteredRow[j];
sheet.Cells[insertRow + 1, j + 1].Value = filteredRow[j];
}
insertRow++;
}
}
Excel.Range tableRange =
sheet.Range[sheet.Cells[1, 1],
sheet.Cells[insertRow, table.dataArray[0].Length]];
//tableRange.Value = ta;
string addr = tableRange.Address;
chart.SetSourceData(sheet.Name + "!" + tableRange.Address, PowerPoint.XlRowCol.xlRows);
book.Close();
return(chart);
}
public PPT.Series GetChartSeriesByName(PPT.Chart chart, string seriesName)
{
var sc = (PPT.SeriesCollection)chart.SeriesCollection();
PPT.Series seriesToReturn = null;
int counter = 1;
int total = sc.Count + 1;
do
{
var series = sc.Item((counter));
if (series.Name == seriesName)
{
seriesToReturn = series;
}
counter++;
}while (counter != total);
return(seriesToReturn);
}
public void Format_Title(PowerPoint.Chart myChart)
{
try
{
myChart.HasTitle = true;
myChart.ChartTitle.Font.Name = "Calibri";
myChart.ChartTitle.Font.Bold = MsoTriState.msoTrue;
myChart.ChartTitle.Font.Size = 11;
myChart.ChartTitle.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
myChart.ChartTitle.Font.Strikethrough = MsoTriState.msoFalse;
myChart.ChartTitle.Font.Superscript = MsoTriState.msoFalse;
myChart.ChartTitle.Font.Subscript = MsoTriState.msoFalse;
myChart.ChartTitle.Font.Shadow = MsoTriState.msoFalse;
myChart.ChartTitle.Font.Underline = XlUnderlineStyle.xlUnderlineStyleNone;
}
catch (Exception err)
{
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "Format_Title");
}
}
public void AddSeriesToExistingChart(PPT.Chart chart, ChartSeries series)
{
var workbook = (EXCEL.Workbook)chart.ChartData.Workbook;
workbook.Windows.Application.Visible = false;
var cellNumber = 1;
var dataSheet = (EXCEL.Worksheet)workbook.Worksheets[1];
var sc = (PPT.SeriesCollection)chart.SeriesCollection();
var seriesCount = sc.Count;
var letter = IntToLetters((seriesCount + 2));
foreach (var value in series.seriesData)
{
var cellPosition = letter + cellNumber.ToString();
dataSheet.Cells.Range[cellPosition].Value2 = value;
cellNumber++;
chart.Refresh();
}
// we have to populate the sheet with new values, now we need to create a series for it!
var columnsRange = dataSheet.UsedRange.Columns;
var rowsRange = dataSheet.UsedRange.Rows;
var columnCount = columnsRange.Columns.Count;
var rowCount = rowsRange.Rows.Count;
var lastColumnLetter = IntToLetters(columnCount);
var newSeries = sc.NewSeries();
newSeries.Name = series.name;
newSeries.XValues = "'Sheet1'!$A$1:$A$" + rowCount;
newSeries.Values = "'Sheet1'!$" + lastColumnLetter + "$1:$" + lastColumnLetter + "$" + rowCount;
newSeries.ChartType = series.seriesType;
chart.Refresh();
}
public void Format_PlotArea(PowerPoint.Chart myChart, string chType)
{
try
{
myChart.PlotArea.Border.LineStyle = PowerPoint.XlLineStyle.xlLineStyleNone;
myChart.PlotArea.Interior.ColorIndex = PowerPoint.XlColorIndex.xlColorIndexAutomatic;
//if (chType == "Pie" || chType == "Doughnut") { }
//else
//{
// myChart.PlotArea.Left = 8;
// if (myChart.HasTitle == true) { myChart.PlotArea.Top = myChart.ChartArea.Top + myChart.ChartTitle.Top; }
// else { myChart.PlotArea.Top = 8; }
// myChart.PlotArea.Width = myChart.ChartArea.Width - 24;
// if (myChart.HasLegend == true) { myChart.PlotArea.Height = myChart.ChartArea.Height - myChart.Legend.Height * 2; }
// else { myChart.PlotArea.Height = myChart.ChartArea.Height - 16; }
//}
}
catch (Exception err)
{
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "Format_PlotArea");
}
}
public void Format_XSeries(PowerPoint.Chart myChart, bool hasYAxis)
{
//if (myChart.Axes(XlAxisType.xlValue).HasTitle == true)
try
{
if (myChart.HasAxis[XlAxisType.xlSeriesAxis] == true)
{
myChart.HasAxis(XlAxisType.xlSeriesAxis).Border.LineStyle = PowerPoint.XlLineStyle.xlContinuous;
myChart.HasAxis(XlAxisType.xlSeriesAxis).Border.Color = System.Drawing.Color.FromArgb(127, 127, 127).ToArgb();
myChart.HasAxis(XlAxisType.xlSeriesAxis).Border.Weight = PowerPoint.XlBorderWeight.xlHairline;
myChart.HasAxis(XlAxisType.xlSeriesAxis).Border.MajorTickMark = PowerPoint.XlTickMark.xlTickMarkOutside;
myChart.HasAxis(XlAxisType.xlSeriesAxis).Border.MinorTickMark = PowerPoint.XlTickMark.xlTickMarkNone;
myChart.HasAxis(XlAxisType.xlSeriesAxis).Border.TickLabelPosition = PowerPoint.XlTickLabelPosition.xlTickLabelPositionNextToAxis;;
myChart.HasAxis(XlAxisType.xlSeriesAxis).Border.AutoScaleFont = false;
PowerPoint.Font TickLabelsFont = myChart.Axes(XlAxisType.xlSeriesAxis).TickLabels.Font;
TickLabelsFont.Name = "Calibri";
TickLabelsFont.Bold = MsoTriState.msoFalse;
TickLabelsFont.Size = 11;
myChart.Axes(XlAxisType.xlSeriesAxis).TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
myChart.Axes(XlAxisType.xlSeriesAxis).TickLabels.Font.Strikethrough = MsoTriState.msoFalse;
TickLabelsFont.Superscript = MsoTriState.msoFalse;
TickLabelsFont.Subscript = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlSeriesAxis).TickLabels.Font.OutlineFont = MsoTriState.msoFalse;
TickLabelsFont.Shadow = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlSeriesAxis).TickLabels.Font.Underline = XlUnderlineStyle.xlUnderlineStyleNone;
myChart.Axes(XlAxisType.xlSeriesAxis).TickLabels.Font.Background = PowerPoint.XlBackground.xlBackgroundAutomatic;
}
}
catch (Exception err)
{
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "Format_XSeries");
}
}
public async Task <bool> WriteData(String strID)
{
PowerPoint.Shapes shapes = Globals.ThisAddIn.Application.ActivePresentation.Slides[1].Shapes;
try
{
JObject jsonData = await RequestData.GetPredictData(strID);
JObject dataObj = jsonData["data"].ToObject <JObject>();
PowerPoint.Shape shape = PPTAPI.getShape(shapes, "Label_1");
if (shape != null)
{
shape.TextFrame.TextRange.Text = dataObj.Value <String>("labelInfo");
}
shape = PPTAPI.getShape(shapes, "DateLabel");
if (shape != null)
{
String strText = shape.TextFrame.TextRange.Text;
strText = strText.Replace("{Date}", dataObj.Value <String>("date"));
shape.TextFrame.TextRange.Text = strText;
}
shape = PPTAPI.getShape(shapes, "Table_1");
if (shape != null)
{
PowerPoint.Table table = shape.Table;
shape = PPTAPI.getShape(shapes, "Chart_1");
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray array = dataObj.Value <JArray>("salesData");
List <PredictData> predicts = CoreAPI.Deserialize <List <PredictData> >(array.ToString());
int col = 2;
char colStart = 'B';
foreach (PredictData predict in predicts)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = predict.Month;
table.Cell(row, col).Shape.TextFrame.TextRange.Text = predict.Sales;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = predict.Sales;
table.Cell(row, col).Shape.TextFrame.TextRange.Text = predict.Ratio;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = predict.Ratio;
col++;
colStart++;
}
}
shape = PPTAPI.getShape(shapes, "Image_1");
if (shape != null)
{
String strUrl = dataObj.Value <String>("imageUrl");
if (!String.IsNullOrEmpty(strUrl))
{
string strPath = Request.HttpDownload(strUrl).Result;
shapes.AddPicture(strPath, Microsoft.Office.Core.MsoTriState.msoCTrue, Microsoft.Office.Core.MsoTriState.msoCTrue,
shape.Left, shape.Top, shape.Width, shape.Height);
shape.Delete();
}
}
}
catch
{
}
return(true);
}
public void Format_YAxis1(PowerPoint.Chart myChart, bool hasYAxis, string chType)
{
//If chType = "Radar" Or chType = "Stock" Then hasYAxis = True 'Or ch3D = True
try
{
Microsoft.Office.Interop.Graph.Axis axis;
if (chType == "Pie" || chType == "Doughnut" || chType == "Surface")
{
return;
}
if (chType == "Radar" || chType == "Stock")
{
hasYAxis = true;
}
if (hasYAxis == true)
{
//axis = (Microsoft.Office.Interop.Graph.Axis)myChart.Axes(XlAxisType.xlValue);
myChart.HasAxis[XlAxisType.xlValue] = true;
//myChart.HasAxis = true;
if (myChart.Axes(XlAxisType.xlValue).HasTitle == true)
{
myChart.Axes(XlAxisType.xlValue).AxisTitle.Font.Name = "Calibri";
myChart.Axes(XlAxisType.xlValue).AxisTitle.Font.Bold = MsoTriState.msoTrue;
myChart.Axes(XlAxisType.xlValue).AxisTitle.Font.Size = 11;
myChart.Axes(XlAxisType.xlValue).AxisTitle.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
myChart.Axes(XlAxisType.xlValue).AxisTitle.Font.Strikethrough = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue).AxisTitle.Font.Superscript = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue).AxisTitle.Font.Subscript = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue).AxisTitle.Font.OutlineFont = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue).AxisTitle.Font.Shadow = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue).AxisTitle.Font.Underline = XlUnderlineStyle.xlUnderlineStyleNone;
myChart.Axes(XlAxisType.xlValue).AxisTitle.Font.Background = PowerPoint.XlBackground.xlBackgroundAutomatic;
}
myChart.Axes(XlAxisType.xlValue).Border.LineStyle = PowerPoint.XlLineStyle.xlContinuous; //15/02/2018
myChart.Axes(XlAxisType.xlValue).Border.Color = System.Drawing.Color.FromArgb(127, 127, 127).ToArgb();
myChart.Axes(XlAxisType.xlValue).Border.Weight = PowerPoint.XlBorderWeight.xlHairline;
myChart.Axes(XlAxisType.xlValue).MajorTickMark = PowerPoint.XlTickMark.xlTickMarkOutside;
myChart.Axes(XlAxisType.xlValue).TickLabelPosition = PowerPoint.XlTickLabelPosition.xlTickLabelPositionNextToAxis;
myChart.Axes(XlAxisType.xlValue).TickLabels.AutoScaleFont = false;
//------
myChart.Axes(XlAxisType.xlValue).TickLabels.Font.Name = "Calibri";
myChart.Axes(XlAxisType.xlValue).TickLabels.Font.Bold = MsoTriState.msoTrue;
myChart.Axes(XlAxisType.xlValue).TickLabels.Font.Size = 11;
myChart.Axes(XlAxisType.xlValue).TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
myChart.Axes(XlAxisType.xlValue).TickLabels.Font.Strikethrough = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue).TickLabels.Font.Superscript = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue).TickLabels.Font.Subscript = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue).TickLabels.Font.OutlineFont = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue).TickLabels.Font.Shadow = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue).TickLabels.Font.Bold = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue).TickLabels.Font.Underline = XlUnderlineStyle.xlUnderlineStyleNone;
myChart.Axes(XlAxisType.xlValue).TickLabels.Font.Background = PowerPoint.XlBackground.xlBackgroundAutomatic;
} // - Check HasAxis is True of Not
else if (hasYAxis == false)
{
myChart.HasAxis[XlAxisType.xlValue] = false;
}
}
catch (Exception err)
{
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "Format_YAxis1");
}
}
public string chartType(PowerPoint.Chart myChart)
{
string chtType = null;
try
{
if (myChart.ChartType == XlChartType.xlBarClustered || myChart.ChartType == XlChartType.xlBarStacked || myChart.ChartType == XlChartType.xlBarStacked100 ||
myChart.ChartType == XlChartType.xl3DBarClustered || myChart.ChartType == XlChartType.xl3DBarStacked || myChart.ChartType == XlChartType.xl3DBarStacked100 ||
myChart.ChartType == XlChartType.xlCylinderBarClustered || myChart.ChartType == XlChartType.xlCylinderBarStacked || myChart.ChartType == XlChartType.xlCylinderBarStacked100 ||
myChart.ChartType == XlChartType.xlConeBarClustered || myChart.ChartType == XlChartType.xlConeBarStacked || myChart.ChartType == XlChartType.xlConeBarStacked100 ||
myChart.ChartType == XlChartType.xlPyramidBarClustered || myChart.ChartType == XlChartType.xlPyramidBarStacked || myChart.ChartType == XlChartType.xlPyramidBarStacked100)
{
chtType = "Column";
}
else if (myChart.ChartType == XlChartType.xlColumnClustered || myChart.ChartType == XlChartType.xlColumnStacked || myChart.ChartType == XlChartType.xlColumnStacked100 ||
myChart.ChartType == XlChartType.xl3DColumnClustered || myChart.ChartType == XlChartType.xl3DColumnStacked || myChart.ChartType == XlChartType.xl3DColumnStacked100 ||
myChart.ChartType == XlChartType.xlCylinderColClustered || myChart.ChartType == XlChartType.xlCylinderColStacked || myChart.ChartType == XlChartType.xlCylinderColStacked100 ||
myChart.ChartType == XlChartType.xlConeColClustered || myChart.ChartType == XlChartType.xlConeColStacked || myChart.ChartType == XlChartType.xlConeColStacked100 ||
myChart.ChartType == XlChartType.xlPyramidColClustered || myChart.ChartType == XlChartType.xlPyramidColStacked || myChart.ChartType == XlChartType.xlPyramidColStacked100 ||
myChart.ChartType == XlChartType.xl3DColumn || myChart.ChartType == XlChartType.xlConeCol || myChart.ChartType == XlChartType.xlCylinderCol || myChart.ChartType == XlChartType.xlPyramidCol)
{
chtType = "Column";
}
else if (myChart.ChartType == XlChartType.xlLine || myChart.ChartType == XlChartType.xlLineMarkers || myChart.ChartType == XlChartType.xlLineStacked || myChart.ChartType == XlChartType.xl3DLine ||
myChart.ChartType == XlChartType.xlLineStacked100 || myChart.ChartType == XlChartType.xlLineMarkersStacked || myChart.ChartType == XlChartType.xlLineMarkersStacked100)
{
chtType = "Line";
}
else if (myChart.ChartType == XlChartType.xlXYScatter || myChart.ChartType == XlChartType.xlXYScatterLines || myChart.ChartType == XlChartType.xlXYScatterLinesNoMarkers ||
myChart.ChartType == XlChartType.xlXYScatterSmooth || myChart.ChartType == XlChartType.xlXYScatterSmoothNoMarkers)
{
chtType = "Line";
}
else if (myChart.ChartType == XlChartType.xlPie || myChart.ChartType == XlChartType.xl3DPie || myChart.ChartType == XlChartType.xlPieExploded || myChart.ChartType == XlChartType.xl3DPieExploded ||
myChart.ChartType == XlChartType.xlPieOfPie || myChart.ChartType == XlChartType.xlBarOfPie)
{
chtType = "Pie";
}
else if (myChart.ChartType == XlChartType.xlDoughnut || myChart.ChartType == XlChartType.xlDoughnutExploded)
{
chtType = "Doughnut";
}
else if (myChart.ChartType == XlChartType.xlArea || myChart.ChartType == XlChartType.xlAreaStacked || myChart.ChartType == XlChartType.xlAreaStacked100 ||
myChart.ChartType == XlChartType.xl3DArea || myChart.ChartType == XlChartType.xl3DAreaStacked || myChart.ChartType == XlChartType.xl3DAreaStacked100)
{
chtType = "Area";
}
else if (myChart.ChartType == XlChartType.xlBubble || myChart.ChartType == XlChartType.xlBubble3DEffect)
{
chtType = "Bubble";
}
else if (myChart.ChartType == XlChartType.xlStockHLC || myChart.ChartType == XlChartType.xlStockOHLC || myChart.ChartType == XlChartType.xlStockVHLC || myChart.ChartType == XlChartType.xlStockVOHLC)
{
chtType = "Stock";
}
else if (myChart.ChartType == XlChartType.xlSurface || myChart.ChartType == XlChartType.xlSurfaceWireframe || myChart.ChartType == XlChartType.xlSurfaceTopView || myChart.ChartType == XlChartType.xlSurfaceTopViewWireframe)
{
chtType = "Surface";
}
else if (myChart.ChartType == XlChartType.xlRadar || myChart.ChartType == XlChartType.xlRadarFilled || myChart.ChartType == XlChartType.xlRadarMarkers)
{
chtType = "Radar";
}
}
catch (Exception err)
{
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "chartType");
}
return(chtType);
}
private void frmChartcalc_Load(object sender, EventArgs e)
{
List <string> SelectedCharts = new List <string>();
List <String> xxVals = new List <string>();
List <String> yyVals = new List <string>();
List <string> Seriesname = new List <string>();
try
{
int sld_num = ppApp.ActiveWindow.Selection.SlideRange.SlideNumber;
SelectedCharts = PPTshpchk.FindSelectedCharts();
string shp_nam = SelectedCharts[0];
PowerPoint.Chart myChart = ActivePPT.Slides[sld_num].Shapes[shp_nam].Chart;
PowerPoint.SeriesCollection series = ActivePPT.Slides[sld_num].Shapes[shp_nam].Chart.SeriesCollection();
num_points = ActivePPT.Slides[sld_num].Shapes[shp_nam].Chart.SeriesCollection(1).Points.Count;
num_series = series.Count;
chtData = new string[num_points, num_series + 1];
for (int x = 0; x < num_series; x++)
{
string sName = myChart.SeriesCollection(x + 1).Name;
Seriesname.Add(sName);
PowerPoint.Series tseries = (PowerPoint.Series)myChart.SeriesCollection(x + 1);
xxVals.Clear();
foreach (object item in tseries.Values as Array)
{
xxVals.Add(Convert.ToString(item));
}
string[] yVals = xxVals.ToArray();
yyVals.Clear();
foreach (object item in tseries.XValues as Array)
{
yyVals.Add(Convert.ToString(item));
}
string[] xVals = yyVals.ToArray();
//string[] yVals = myChart.SeriesCollection(x).Values.toArray();
//string[] xVals = myChart.SeriesCollection(x).XValues.toArray();
//chtData[0, x ] = sName;
for (int y = 0; y < yVals.Length; y++)
{
chtData[y, 0] = xVals[y];
chtData[y, x + 1] = yVals[y];
}
}
//for(int x=0;x< chtData.Length; x++) { cmb_stDate.Items.Add(Convert.ToString(chtData[x, 0])); }
//for (int x = 0; x < chtData.Length; x++) { cmb_stDate.Items.Add(Convert.ToString(chtData[x, 0])); }
cmb_stDate.Items.Clear();
cmb_endDate.Items.Clear();
cmb_Series.Items.Clear();
foreach (string item in yyVals)
{
//Regex.Match(item, @"\d+").Value
cmb_stDate.Items.Add(Regex.Match(item, @"\d+").Value);
cmb_stDate.SelectedIndex = 0;
//cmb_endDate.Items.Add(item);
//cmb_endDate.SelectedIndex = 0;
}
txt_period.Enabled = false;
foreach (string item in Seriesname)
{
cmb_Series.Items.Add(item); cmb_Series.SelectedIndex = 0;
}
//Calculate_CAGR();
string cmbText = cmb_calcType.Text;
cmb_calcType.Items.Add("CAGR");
cmb_calcType.Items.Add("AAGR");
cmb_calcType.SelectedIndex = 0;
if (cmbText == "AAGR")
{
Calculate_AAGR();
}
else
{
Calculate_CAGR();
}
}
catch (Exception err)
{
this.Close();
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "frmChartcalc_Load");
MessageBox.Show("Check chart type and chart value", PPTAttribute.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void Format_YAxis2(PowerPoint.Chart myChart, bool hasYAxis, string chType)
{
try
{
bool secAxis = false;
PowerPoint.SeriesCollection sc = myChart.SeriesCollection();
if (chType == "Pie" || chType == "Doughnut" || chType == "Surface")
{
return;
}
if (chType == "Radar" || chType == "Stock")
{
hasYAxis = true;
}
for (int i = 1; i <= sc.Count; i++)
{
if (myChart.SeriesCollection(i).AxisGroup == 2)
{
secAxis = true; break;
} //XlAxisGroup.xlSecondary
}
if (secAxis == true)
{
if (hasYAxis == true)
{
myChart.HasAxis[XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary] = true;
if (myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).HasTitle == true)
{
PowerPoint.Font fontObj = myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).AxisTitle.Font;
fontObj.Name = "Calibri";
fontObj.Bold = MsoTriState.msoTrue;
fontObj.Size = 11;
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).AxisTitle.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).AxisTitle.Font.Strikethrough = MsoTriState.msoFalse;
fontObj.Superscript = MsoTriState.msoFalse;
fontObj.Subscript = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).AxisTitle.Font.OutlineFont = MsoTriState.msoFalse;
fontObj.Shadow = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).AxisTitle.Font.Underline = XlUnderlineStyle.xlUnderlineStyleNone;
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).AxisTitle.Font.Background = PowerPoint.XlBackground.xlBackgroundAutomatic;
}
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).Border.LineStyle = PowerPoint.XlLineStyle.xlContinuous;
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).Border.Color = System.Drawing.Color.FromArgb(127, 127, 127).ToArgb();
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).Border.Weight = PowerPoint.XlBorderWeight.xlHairline;
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).MajorTickMark = PowerPoint.XlTickMark.xlTickMarkOutside;
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).TickLabelPosition = PowerPoint.XlTickLabelPosition.xlTickLabelPositionNextToAxis;
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).TickLabels.AutoScaleFont = false;
// ------
PowerPoint.Font fontObjTB = myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).TickLabels.Font;
fontObjTB.Name = "Calibri";
fontObjTB.Bold = MsoTriState.msoTrue;
fontObjTB.Size = 11;
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).AxisTitle.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).AxisTitle.Font.Strikethrough = MsoTriState.msoFalse;
fontObjTB.Superscript = MsoTriState.msoFalse;
fontObjTB.Subscript = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).AxisTitle.Font.OutlineFont = MsoTriState.msoFalse;
fontObjTB.Shadow = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).AxisTitle.Font.Underline = XlUnderlineStyle.xlUnderlineStyleNone;
myChart.Axes(XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary).AxisTitle.Font.Background = PowerPoint.XlBackground.xlBackgroundAutomatic;
} // Check hasAxis is true
else if (hasYAxis == false)
{
myChart.HasAxis[XlAxisType.xlValue, PowerPoint.XlAxisGroup.xlSecondary] = false;
}
}
} // Close Main if Check the chart type
catch (Exception err)
{
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "Format_YAxis2");
}
}
public async Task <bool> WriteDataFor164()
{
try
{
JObject jsonData = await RequestData.GetPredictData("164");
if (jsonData.Value <String>("code").Equals("200") && jsonData.Value <JObject>("data") != null)
{
JObject dataObj = jsonData["data"].ToObject <JObject>();
string mbnrStr = dataObj.Value <String>("mbnr");
JArray mbnrArr = JsonConvert.DeserializeObject <JArray>(mbnrStr);
foreach (JObject jObject in mbnrArr)
{
if (jObject.Value <String>("index").Equals("3"))
{
PowerPoint.Shapes shapes = Globals.ThisAddIn.Application.ActivePresentation.Slides[3].Shapes;
JObject tableObj = jObject.Value <JObject>("table");
PowerPoint.Shape shape = PPTAPI.getShape(shapes, "table_1");
if (shape != null)
{
PowerPoint.Table table = shape.Table;
shape = PPTAPI.getShape(shapes, "chart_1");
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb1");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
table.Cell(row, col).Shape.TextFrame.TextRange.Text = tb1[i].Value <String>("c2");;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c2");;
table.Cell(row, col).Shape.TextFrame.TextRange.Text = tb1[i].Value <String>("c3");;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c3");;
col++;
colStart++;
}
}
}
else if (jObject.Value <String>("index").Equals("4"))
{
PowerPoint.Shapes shapes = Globals.ThisAddIn.Application.ActivePresentation.Slides[4].Shapes;
JObject tableObj = jObject.Value <JObject>("table");
PowerPoint.Shape shape = PPTAPI.getShape(shapes, "chart_1");
if (shape != null)
{
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb1");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c2");;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c3");;
col++;
colStart++;
}
}
shape = PPTAPI.getShape(shapes, "chart_2");
if (shape != null)
{
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb2");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
row++;
step = colStart + row.ToString();
string[] lsArr = tb1[i].Value <String>("c2").Split(',');
int length = lsArr.Length;
if (length < 5)
{
Array.Resize(ref lsArr, 5);
}
for (int j = length; j < 5; j++)
{
lsArr[j] = "0.0%";
}
ws.Range[step].Value = lsArr[0];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[1];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[2];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[3];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[4];
col++;
colStart++;
}
}
}
else if (jObject.Value <String>("index").Equals("5"))
{
PowerPoint.Shapes shapes = Globals.ThisAddIn.Application.ActivePresentation.Slides[5].Shapes;
JObject tableObj = jObject.Value <JObject>("table");
PowerPoint.Shape shape = PPTAPI.getShape(shapes, "chart_1");
if (shape != null)
{
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb1");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c2");;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c3");;
col++;
colStart++;
}
}
shape = PPTAPI.getShape(shapes, "chart_2");
if (shape != null)
{
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb2");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
row++;
step = colStart + row.ToString();
string[] lsArr = tb1[i].Value <String>("c2").Split(',');
int length = lsArr.Length;
if (length < 5)
{
Array.Resize(ref lsArr, 5);
}
for (int j = length; j < 5; j++)
{
lsArr[j] = "0.0%";
}
ws.Range[step].Value = lsArr[0];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[1];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[2];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[3];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[4];
col++;
colStart++;
}
}
}
else if (jObject.Value <String>("index").Equals("6"))
{
PowerPoint.Shapes shapes = Globals.ThisAddIn.Application.ActivePresentation.Slides[6].Shapes;
JObject tableObj = jObject.Value <JObject>("table");
PowerPoint.Shape shape = PPTAPI.getShape(shapes, "chart_1");
if (shape != null)
{
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb1");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c2");;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c3");;
col++;
colStart++;
}
}
shape = PPTAPI.getShape(shapes, "chart_2");
if (shape != null)
{
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb2");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
row++;
step = colStart + row.ToString();
string[] lsArr = tb1[i].Value <String>("c2").Split(',');
int length = lsArr.Length;
if (length < 5)
{
Array.Resize(ref lsArr, 5);
}
for (int j = length; j < 5; j++)
{
lsArr[j] = "0.0%";
}
ws.Range[step].Value = lsArr[0];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[1];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[2];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[3];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[4];
col++;
colStart++;
}
}
}
}
}
else
{
return(false);
}
}
catch
{
}
return(true);
}
public void Format_XAxis(PowerPoint.Chart myChart, bool hasXAxis, string chType)
{
try
{
if (chType == "Pie" || chType == "Doughnut")
{
return;
}
if (chType == "Radar")
{
PowerPoint.Font RALFont = myChart.ChartGroups(1).RadarAxisLabels.Font;
RALFont.Name = "Calibri";
RALFont.Bold = MsoTriState.msoFalse;
RALFont.Size = 11;
myChart.ChartGroups(1).RadarAxisLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
myChart.ChartGroups(1).RadarAxisLabels.Font.Strikethrough = MsoTriState.msoFalse;
RALFont.Superscript = MsoTriState.msoFalse;
RALFont.Subscript = MsoTriState.msoFalse;
myChart.ChartGroups(1).RadarAxisLabels.Font.OutlineFont = MsoTriState.msoFalse;
RALFont.Shadow = MsoTriState.msoFalse;
myChart.ChartGroups(1).RadarAxisLabels.Font.Underline = XlUnderlineStyle.xlUnderlineStyleNone;
myChart.ChartGroups(1).RadarAxisLabels.Font.Background = PowerPoint.XlBackground.xlBackgroundAutomatic;
//System.Environment.Exit(0);
}
if (hasXAxis == true)
{
if (myChart.Axes(XlAxisType.xlCategory).HasTitle == true)
{
PowerPoint.Font AxTitleFont = myChart.Axes(XlAxisType.xlCategory).AxisTitle.Font;
AxTitleFont.Name = "Calibri";
AxTitleFont.Bold = MsoTriState.msoFalse;
AxTitleFont.Size = 11;
myChart.Axes(XlAxisType.xlCategory).AxisTitle.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
myChart.Axes(XlAxisType.xlCategory).AxisTitle.Font.Strikethrough = MsoTriState.msoFalse;
AxTitleFont.Superscript = MsoTriState.msoFalse;
AxTitleFont.Subscript = MsoTriState.msoFalse;
myChart.ChartGroups(1).RadarAxisLabels.Font.OutlineFont = MsoTriState.msoFalse;
AxTitleFont.Shadow = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlCategory).AxisTitle.Font.Font.Underline = XlUnderlineStyle.xlUnderlineStyleNone;
myChart.Axes(XlAxisType.xlCategory).AxisTitle.Font.Font.Background = PowerPoint.XlBackground.xlBackgroundAutomatic;
}
myChart.Axes(XlAxisType.xlCategory).Border.LineStyle = PowerPoint.XlLineStyle.xlContinuous;
myChart.Axes(XlAxisType.xlCategory).Border.Color = System.Drawing.Color.FromArgb(127, 127, 127).ToArgb();
myChart.Axes(XlAxisType.xlCategory).Border.Weight = PowerPoint.XlBorderWeight.xlHairline;
myChart.Axes(XlAxisType.xlCategory).MajorTickMark = PowerPoint.XlTickMark.xlTickMarkOutside;
myChart.Axes(XlAxisType.xlCategory).TickLabelPosition = PowerPoint.XlTickLabelPosition.xlTickLabelPositionNextToAxis;
myChart.Axes(XlAxisType.xlCategory).TickLabels.AutoScaleFont = false;
//---
//PowerPoint.Font TickLabelsFont = myChart.Axes(XlAxisType.xlCategory).TickLabels.Font;
myChart.Axes(XlAxisType.xlCategory).TickLabels.Font.Name = "Calibri";
myChart.Axes(XlAxisType.xlCategory).TickLabels.Font.Bold = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlCategory).TickLabels.Font.Size = 11;
myChart.Axes(XlAxisType.xlCategory).TickLabels.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb();
myChart.Axes(XlAxisType.xlCategory).TickLabels.Font.Strikethrough = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlCategory).TickLabels.Font.Superscript = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlCategory).TickLabels.Font.Subscript = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlCategory).TickLabels.Font.OutlineFont = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlCategory).TickLabels.Font.Shadow = MsoTriState.msoFalse;
myChart.Axes(XlAxisType.xlCategory).TickLabels.Font.Underline = XlUnderlineStyle.xlUnderlineStyleNone;
myChart.Axes(XlAxisType.xlCategory).TickLabels.Font.Background = PowerPoint.XlBackground.xlBackgroundAutomatic;
}
else if (hasXAxis == false)
{
myChart.HasAxis[XlAxisType.xlCategory] = false;
}
}
catch (Exception err)
{
string errtext = err.Message;
PPTAttribute.ErrorLog(errtext, "Format_XAxis");
}
}
public PPT.SeriesCollection GetAllChartSeries(PPT.Chart chart)
{
return((PPT.SeriesCollection)chart.SeriesCollection());
}
public void Format_ChartArea(PowerPoint.Chart myChart)
{
myChart.ChartArea.Border.LineStyle = PowerPoint.XlLineStyle.xlLineStyleNone;
myChart.ChartArea.Border.ColorIndex = 0;
}
