Using Sparklines and Settings 3D Format with JavaScript via C++
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 JavaScript via C++ 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 for JavaScript via C++
Developers can create, delete or read sparklines (in the template file) using the API provided by Aspose.Cells for JavaScript via C++. The classes that manage sparklines are contained in the SparklineGroupCollection module, so you need to require this module 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.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Sparkline Example</title>
</head>
<body>
<h1>Aspose.Cells Sparkline Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, CellArea, SparklineType, Color } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiate a Workbook by opening the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Get the first worksheet
const sheet = workbook.worksheets.get(0);
// Read the Sparklines from the worksheet (if any)
const sparklinesCount = sheet.sparklineGroups.count;
let logHtml = '';
for (let i = 0; i < sparklinesCount; i++) {
const group = sheet.sparklineGroups.get(i);
logHtml += `sparkline group: type:${group.type}, sparkline items count:${group.sparklines.count}<br/>`;
const sparklineCount = group.sparklines.count;
for (let j = 0; j < sparklineCount; j++) {
const sparkline = group.sparklines.get(j);
logHtml += `sparkline: row:${sparkline.row}, col:${sparkline.column}, dataRange:${sparkline.dataRange}<br/>`;
}
}
// Add Sparklines
// Define the CellArea D2:D10 (rows and columns are zero-based: D is column 4 -> index 4)
const ca = CellArea.createCellArea(1, 4, 7, 4);
// Add new Sparklines for a data range to a cell area
const idx = sheet.sparklineGroups.add(SparklineType.Column, "Sheet1!B2:D8", false, ca);
const newGroup = sheet.sparklineGroups.get(idx);
// Create CellsColor and set color
const clr = workbook.createCellsColor();
clr.color = Color.Orange;
newGroup.seriesColor = clr;
// Save the modified Excel file and provide download link
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'Book1.out.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Result Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p><div>' + logHtml + '</div>';
});
</script>
</html>
Setting 3D Format
You might need 3D charting styles so you can get just the results for your scenario. Aspose.Cells for JavaScript via C++ 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.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>3D Chart Formatting Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, ChartType, Color, BevelPresetType, PresetMaterialType, LightRigType } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
// Creating a new workbook
const book = new Workbook();
// Add a Data Worksheet
const dataSheet = book.worksheets.add("DataSheet");
// Add Chart Worksheet
const sheet = book.worksheets.add("MyChart");
// Put some values into the cells in the data worksheet
dataSheet.cells.get("B1").value = 1;
dataSheet.cells.get("B2").value = 2;
dataSheet.cells.get("B3").value = 3;
dataSheet.cells.get("A1").value = "A";
dataSheet.cells.get("A2").value = "B";
dataSheet.cells.get("A3").value = "C";
// Define the Chart Collection
const charts = sheet.charts;
// Add a Column chart to the Chart Worksheet
const chartSheetIdx = charts.add(ChartType.Column, 5, 0, 25, 15);
// Get the newly added Chart
const chart = book.worksheets.get(2).charts.get(0);
// Set the background/foreground color for PlotArea/ChartArea
chart.plotArea.area.backgroundColor = Color.White;
chart.chartArea.area.backgroundColor = Color.White;
chart.plotArea.area.foregroundColor = Color.White;
chart.chartArea.area.foregroundColor = Color.White;
// Hide the Legend
chart.showLegend = false;
// Add Data Series for the Chart
chart.nSeries.add("DataSheet!B1:B3", true);
// Specify the Category Data
chart.nSeries.categoryData = "DataSheet!A1:A3";
// Get the Data Series
const ser = chart.nSeries.get(0);
// Apply the 3-D formatting
const spPr = ser.shapeProperties;
const fmt3d = spPr.format3D;
// Specify Bevel with its height/width
const bevel = fmt3d.topBevel;
bevel.type = BevelPresetType.Circle;
bevel.height = 2;
bevel.width = 5;
// Specify Surface material type
fmt3d.surfaceMaterialType = PresetMaterialType.WarmMatte;
// Specify surface lighting type
fmt3d.surfaceLightingType = LightRigType.ThreePoint;
// Specify lighting angle
fmt3d.lightingAngle = 20;
// Specify Series background/foreground and line color
ser.area.backgroundColor = Color.Maroon;
ser.area.foregroundColor = Color.Maroon;
ser.border.color = Color.Maroon;
// Saving the modified Excel file and providing download link
const outputData = book.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = '3d_format.out.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Workbook created successfully! Click the download link to get the file.</p>';
});
</script>
</html>