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 provides an API for manipulating sparklines in spreadsheets.
Sparklines in Microsoft Excel
To insert sparklines in Microsoft Excel 2010:
- Select the cells where you want the sparklines to appear. To make them easy to view, select cells at the side of the data.
- Click Insert on the ribbon and then choose column in the Sparklines group.
- 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
Developers can create, delete or read sparklines (in the template file) using the API provided by Aspose.Cells. 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:
- Open a simple template file.
- Read sparklines information for a worksheet.
- Add new sparklines for a given data range to a cell area.
- Save the Excel file to disk.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiate a Workbook | |
// Open a template file | |
Workbook book = new Workbook("Source.xlsx"); | |
// Get the first worksheet | |
Worksheet sheet = book.getWorksheets().get(0); | |
// Use the following lines if you need to read the Sparklines | |
// Read the Sparklines from the template file (if it has) | |
for(SparklineGroup g : (Iterable<SparklineGroup>)sheet.getSparklineGroupCollection()) | |
{ | |
// Display the Sparklines group information e.g type, number of sparklines items | |
System.out.println("sparkline group: type:" + g.getType() + ", sparkline items count:" + g.getSparklineCollection().getCount()); | |
for (Sparkline s: (Iterable<Sparkline>)g.getSparklineCollection()) | |
{ | |
// Display the individual Sparkines and the data ranges | |
System.out.println("sparkline: row:" + s.getRow() + ", col:" + s.getColumn() + ", dataRange:" + s.getDataRange()); | |
} | |
} | |
// Add Sparklines | |
// Define the CellArea D2:D10 | |
CellArea ca = new CellArea(); | |
ca.StartColumn = 4; | |
ca.EndColumn = 4; | |
ca.StartRow = 1; | |
ca.EndRow = 7; | |
// Add new Sparklines for a data range to a cell area | |
int idx = sheet.getSparklineGroupCollection().add(SparklineType.COLUMN, "Sheet1!B2:D8", false, ca); | |
SparklineGroup group = sheet.getSparklineGroupCollection().get(idx); | |
// Create CellsColor | |
CellsColor clr = book.createCellsColor(); | |
clr.setColor(Color.getOrange()); | |
group.setSeriesColor (clr); | |
// Save the workbook | |
book.save("output.xlsx"); |
Setting 3D Format
You might need 3D charting styles so you can get just the results for your scenario. Aspose.Cells 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.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// Instantiate a new Workbook | |
Workbook book = new Workbook(); | |
// Add a Data Worksheet | |
Worksheet dataSheet = book.getWorksheets().add("DataSheet"); | |
// Add Chart Worksheet | |
Worksheet sheet = book.getWorksheets().add("MyChart"); | |
// Put some values into the cells in the data worksheet | |
dataSheet.getCells().get("B1").putValue(1); | |
dataSheet.getCells().get("B2").putValue(2); | |
dataSheet.getCells().get("B3").putValue(3); | |
dataSheet.getCells().get("A1").putValue("A"); | |
dataSheet.getCells().get("A2").putValue("B"); | |
dataSheet.getCells().get("A3").putValue("C"); | |
// Define the Chart Collection | |
ChartCollection charts = sheet.getCharts(); | |
// Add a Column chart to the Chart Worksheet | |
int chartSheetIdx = charts.add(ChartType.COLUMN, 5, 0, 25, 15); | |
// Get the newly added Chart | |
Chart chart = book.getWorksheets().get(2).getCharts().get(0); | |
// Set the background/foreground color for PlotArea/ChartArea | |
chart.getPlotArea().getArea().setBackgroundColor(Color.getWhite()); | |
chart.getChartArea().getArea().setBackgroundColor(Color.getWhite()); | |
chart.getPlotArea().getArea().setForegroundColor(Color.getWhite()); | |
chart.getChartArea().getArea().setForegroundColor(Color.getWhite()); | |
// Hide the Legend | |
chart.setShowLegend (false); | |
// Add Data Series for the Chart | |
chart.getNSeries().add("DataSheet!B1:B3", true); | |
// Specify the Category Data | |
chart.getNSeries().setCategoryData("DataSheet!A1:A3"); | |
// Get the Data Series | |
Series ser = chart.getNSeries().get(0); | |
// Apply the 3-D formatting | |
ShapePropertyCollection spPr = ser.getShapeProperties(); | |
Format3D fmt3d = spPr.getFormat3D(); | |
// Specify Bevel with its height/width | |
Bevel bevel = fmt3d.getTopBevel(); | |
bevel.setType(BevelPresetType.CIRCLE); | |
bevel.setHeight(2); | |
bevel.setWidth(5); | |
// Specify Surface material type | |
fmt3d.setSurfaceMaterialType (PresetMaterialType.WARM_MATTE); | |
// Specify surface lighting type | |
fmt3d.setSurfaceLightingType(LightRigType.THREE_POINT); | |
// Specify lighting angle | |
fmt3d.setLightingAngle(0); | |
// Specify Series background/foreground and line color | |
ser.getArea().setBackgroundColor(Color.getMaroon()); | |
ser.getArea().setForegroundColor(Color.getMaroon()); | |
ser.getBorder().setColor(Color.getMaroon()); | |
// Save the Excel file | |
book.save("3d_format.out.xlsx"); |