Using Sparklines and Settings 3D Format

Using Sparklines

Microsoft Excel 2010 can analyze information in more ways than ever before. It allows users to track and highlight important data trends with new data analysis and visualization tools. Sparklines are mini-charts that you can place inside cells so that you can view data and chart on the same table. When sparklines are used properly, data analysis is quicker and more to the point. They also provide a simple view of information, avoiding over-crowded worksheets with a lot of busy charts.

Aspose.Cells for Python via .NET provides an API for manipulating sparklines in spreadsheets.

Sparklines in Microsoft Excel

To insert sparklines in Microsoft Excel 2010:

1. Select the cells where you want the sparklines to appear. To make them easy to view, select cells at the side of the data.
2. Click Insert on the ribbon and then choose column in the Sparklines group.
3. Select or enter the range of cells in the worksheet that contain the source data. The charts will appear.

Sparklines help you to see trends, for example, the win or loss record for a softball league. Sparklines can even sum up the entire season of each team in the league.

Sparklines using Aspose.Cells for Python via .NET

Developers can create, delete or read sparklines (in the template file) using the API provided by Aspose.Cells for Python via .NET. The classes that manage sparklines are contained in the aspose.cells.charts namespace so you need to import this namespace before using these features.

By adding custom graphics for a given data range, developers have the freedom to add different types of tiny charts to selected cell areas.

The example below demonstrates the Sparklines feature. The example shows how to:

1. Open a simple template file.
2. Read sparklines information for a worksheet.
3. Add new sparklines for a given data range to a cell area.
4. Save the Excel file to disk.

from aspose.cells import CellArea, Workbook
from aspose.cells.charts import SparklineType
from aspose.pydrawing import Color

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Instantiate a Workbook
#  Open a template file
book = Workbook(dataDir + "Book1.xlsx")
#  Get the first worksheet
sheet = book.worksheets[0]
#  Use the following lines if you need to read the Sparklines
#  Read the Sparklines from the template file (if it has)
for g in sheet.sparkline_groups:
    #  Display the Sparklines group information e.g type, number of sparklines items
    print("sparkline group: type:"  + str(g.type) + ", sparkline items count:"  + str(len(g.sparklines)))
    for s in g.sparklines:
        #  Display the individual Sparkines and the data ranges
        print("sparkline: row:"  + str(s.row) + ", col:"  + str(s.column) + ", dataRange:"  + s.data_range)
#  Add Sparklines
#  Define the CellArea D2:D10
ca = CellArea()
ca.start_column = 4
ca.end_column = 4
ca.start_row = 1
ca.end_row = 7
#  Add new Sparklines for a data range to a cell area
idx = sheet.sparkline_groups.add(SparklineType.COLUMN, "Sheet1!B2:D8", False, ca)
group = sheet.sparkline_groups[idx]
#  Create CellsColor
clr = book.create_cells_color()
clr.color = Color.orange
group.series_color = clr
#  Save the excel file
book.save(dataDir + "Book1.out.xlsx")

Setting 3D Format

You might need 3D charting styles so you can get just the results for your scenario. Aspose.Cells for Python via .NET does provide the relevant API to apply Microsoft Excel 2007 3D formatting.

A complete example is given below to demonstrate how to create a chart and apply Microsoft Excel 2007 3D formatting. After executing the example code, a column chart (with 3D effects) will be added to the worksheet.

from aspose.cells import Workbook
from aspose.cells.charts import ChartType
from aspose.cells.drawing import BevelPresetType, LightRigType, PresetMaterialType
from aspose.pydrawing import Color
from os import os, path

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Create directory if it is not already present.
IsExists = path.isdir(dataDir)
if notIsExists:
    os.makedirs(dataDir)
#  Instantiate a new Workbook
book = Workbook()
#  Add a Data Worksheet
dataSheet = book.worksheets.add("DataSheet")
#  Add Chart Worksheet
sheet = book.worksheets.add("MyChart")
#  Put some values into the cells in the data worksheet
dataSheet.cells.get("B1").put_value(1)
dataSheet.cells.get("B2").put_value(2)
dataSheet.cells.get("B3").put_value(3)
dataSheet.cells.get("A1").put_value("A")
dataSheet.cells.get("A2").put_value("B")
dataSheet.cells.get("A3").put_value("C")
#  Define the Chart Collection
charts = sheet.charts
#  Add a Column chart to the Chart Worksheet
chartSheetIdx = charts.add(ChartType.COLUMN, 5, 0, 25, 15)
#  Get the newly added Chart
chart = book.worksheets[2].charts[0]
#  Set the background/foreground color for PlotArea/ChartArea
chart.plot_area.area.background_color = Color.white
chart.chart_area.area.background_color = Color.white
chart.plot_area.area.foreground_color = Color.white
chart.chart_area.area.foreground_color = Color.white
#  Hide the Legend
chart.show_legend = False
#  Add Data Series for the Chart
chart.n_series.add("DataSheet!B1:B3", True)
#  Specify the Category Data
chart.n_series.category_data = "DataSheet!A1:A3"
#  Get the Data Series
ser = chart.n_series[0]
#  Apply the 3-D formatting
spPr = ser.shape_properties
fmt3d = spPr.format_3d
#  Specify Bevel with its height/width
bevel = fmt3d.top_bevel
bevel.type = BevelPresetType.CIRCLE
bevel.height = 2.0
bevel.width = 5.0
#  Specify Surface material type
fmt3d.surface_material_type = PresetMaterialType.WARM_MATTE
#  Specify surface lighting type
fmt3d.surface_lighting_type = LightRigType.THREE_POINT
#  Specify lighting angle
fmt3d.lighting_angle = 20.0
#  Specify Series background/foreground and line color
ser.area.background_color = Color.maroon
ser.area.foreground_color = Color.maroon
ser.border.color = Color.maroon
#  Save the Excel file
book.save(dataDir + "3d_format.out.xlsx")