Knowledgebase (2613)
Children categories
Charts in Excel are powerful tools that transform raw data into visual insights, making it easier to identify trends and patterns. Often, you may need to manage or adjust these charts to better suit your needs. For instance, you might need to extract the data behind a chart for further analysis, resize a chart to fit your layout, move a chart to a more strategic location, or remove outdated charts to keep your workbook organized and clutter-free. In this article, you will learn how to extract, resize, move, and remove charts in Excel in Python using Spire.XLS for Python.
- Extract the Data Source of a Chart in Excel
- Resize a Chart in Excel
- Move a Chart in Excel
- Remove a Chart from Excel
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.
pip install Spire.XLS
If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows
Extract the Data Source of a Chart in Excel in Python
Spire.XLS for Python provides the Chart.DataRange property, which allows you to define or retrieve the cell range used as the data source for a chart. After retrieving this range, you can access the data it contains for further processing or analysis. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Access the worksheet containing the chart using the Workbook.Worksheets[index] property.
- Get the chart using the Worksheet.Charts[index] property.
- Get the cell range that is used as the data source of the chart using the Chart.DataRange property.
- Loop through the rows and columns in the cell range and get the data of each cell.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ChartSample.xlsx")
# Get the worksheet containing the chart
sheet = workbook.Worksheets[0]
# Get the chart
chart = sheet.Charts[0]
# Get the cell range that the chart uses
cellRange = chart.DataRange
# Iterate through the rows and columns in the cell range
for i in range(len(cellRange.Rows)):
for j in range(len(cellRange.Rows[i].Columns)):
# Get the data of each cell
print(cellRange[i + 1, j + 1].Value + " ", end='')
print("")
workbook.Dispose()
Resize a Chart in Excel in Python
Resizing a chart allows you to adjust its dimensions to fit specific areas of your worksheet or enhance its readability. With Spire.XLS for Python, you can adjust the chart's dimensions using the Chart.Width and Chart.Height properties. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Access the worksheet containing the chart using the Workbook.Worksheets[index] property.
- Get the chart using the Worksheet.Charts[index] property.
- Adjust the chart’s dimensions using the Chart.Width and Chart.Height properties.
- Save the result file using the Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ChartSample.xlsx")
# Get the worksheet containing the chart
sheet = workbook.Worksheets[0]
# Get the chart
chart = sheet.Charts[0]
# Resize the chart
chart.Width = 450
chart.Height = 300
# Save the result file
workbook.SaveToFile("ResizeChart.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
Move a Chart in Excel in Python
Moving a chart lets you reposition it for better alignment or to relocate it to another sheet. You can use the Chart.LeftColumn, Chart.TopRow, Chart.RightColumn, and Chart.BottomRow properties to specify the new position of the chart. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Access the worksheet containing the chart using the Workbook.Worksheets[index] property.
- Get the chart using the Worksheet.Charts[index] property.
- Set the new position of the chart using the Chart.LeftColumn, Chart.TopRow, Chart.RightColumn, and Chart.BottomRow properties.
- Save the result file using the Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ChartSample.xlsx")
# Get the worksheet containing the chart
sheet = workbook.Worksheets[0]
# Get the chart
chart = sheet.Charts[0]
# Set the new position of the chart
chart.LeftColumn = 1
chart.TopRow = 7
chart.RightColumn = 9
chart.BottomRow = 30
# Save the result file
workbook.SaveToFile("MoveChart.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
Remove a Chart from Excel in Python
Removing unnecessary or outdated charts from your worksheet helps keep your document clean and organized. In Spire.XLS for Python, you can use the Chart.Remove() method to delete a chart from an Excel worksheet. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Access the worksheet containing the chart using the Workbook.Worksheets[index] property.
- Get the chart using the Worksheet.Charts[index] property.
- Remove the chart using the Chart.Remove() method.
- Save the result file using the Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ChartSample.xlsx")
# Get the worksheet containing the chart
sheet = workbook.Worksheets[0]
# Get the chart
chart = sheet.Charts[0]
# Remove the chart
chart.Remove()
# Save the result file
workbook.SaveToFile("RemoveChart.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
How to Integrate Spire.XLS for JavaScript in a Node.js Project
2024-12-02 08:50:00 Written by AdministratorExcel manipulation is a crucial aspect of many modern web applications, from data analysis to report generation. Node.js, with its non-blocking I/O model and rich ecosystem, is an excellent choice for backend development. When combined with Spire.XLS for JavaScript, you can unlock a world of possibilities for handling Excel files efficiently.
This guide will walk you through the process of integrating Spire.XLS for JavaScript into your Node.js projects, covering everything from initial setup to generating a simple Excel document.
- Benefits of Using Spire.XLS for JavaScript in Node.js Projects
- Set Up Your Environment
- Integrate Spire.XLS for JavaScript in Your Project
- Create and Save Excel Files Using JavaScript
Benefits of Using Spire.XLS for JavaScript in Node.js Projects
Node.js is a great option for developing scalable network applications. When paired with Spire.XLS for JavaScript, you get a robust solution for handling Excel files. Here are some key features and benefits of using Spire.XLS for JavaScript in Node.js projects:
- Comprehensive Excel Support: Spire.XLS for JavaScript supports a wide range of Excel features, including formulas, charts, pivot tables, and more.
- High Performance: The library is optimized for speed, ensuring that your application remains responsive even when handling large datasets.
- Cross-Platform Compatibility: With Node.js, you can run your application on any platform that supports JavaScript, including Windows, macOS, and Linux.
- Secure and Reliable: The library is designed to be secure, with features like data validation and encryption to protect your data.
By leveraging the strengths of both Node.js and Spire.XLS for JavaScript, you can build powerful applications that handle Excel files with ease.
Set Up Your Environment
Step 1
Download and install Node.js from the official website. Make sure to choose the version that matches your operating system.
After the installation is complete, you can verify that Node.js and npm are installed correctly, along with the version numbers, by entering the following commands in CMD:
node -v npm -v
Step 2
Initialize a Node.js project：
npm init -y
Installation dependencies：
npm install adm-zip@^0.5.16
Configure packaging.json：
{
"name": "nodejstest",
"version": "1.0.0",
"description": "Simple test project to generate HelloWorld.xlsx using spire.xls.js",
"main": "index.js",
"type": "module",
"scripts": {
"start": "node --experimental-modules --experimental-wasm-modules --experimental-vm-modules index.js"
},
"dependencies": {
"adm-zip": "^0.5.16"
}
}
Customize folder in the root directory to put some font files, you can customize and add fonts based on the font used in your documents.
Integrate Spire.XLS for JavaScript in Your Project
Download Spire.XLS for JavaScript and unzip it to a location on your disk. The downloaded product package integrates Spire.Doc for JavaScript, Spire.XLS for JavaScript, Spire.PDF for JavaScript, and Spire.Presentation for JavaScript. To use the features of Spire.XLS for JavaScript, customize the folder in the root directory, this tutorial defined the 'wasm' folder, and copy the corresponding files (spire.xls.js, Spire.Xls.Wasm.zip, spire.common.js, Spire.Common.Wasm.zip, and the _framework folder) to the “wasm” folder.
Add the 'index.js' file to the root directory of the project and set the following content to create a simple Excel file
- JavaScript
//NodeJSTest----create "HelloWorld.xlsx"
import fs from 'fs/promises';
import path from 'path';
import { fileURLToPath } from 'url';
import AdmZip from 'adm-zip';
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
async function extractWasmFiles() {
const wasmDir = path.join(__dirname, 'wasm');
const frameworkDir = path.join(wasmDir, '_framework');
await fs.mkdir(frameworkDir, { recursive: true });
for (const zipName of ['Spire.Common.Wasm.zip', 'Spire.Xls.Wasm.zip']) {
const zipPath = path.join(wasmDir, zipName);
try {
await fs.access(zipPath);
new AdmZip(zipPath).extractAllTo(wasmDir, true);
} catch { }
}
}
async function main() {
try {
await extractWasmFiles();
const { spirexls } = await import('./wasm/spire.xls.js');
const spire = globalThis.spire;
if (!spire) throw new Error('WASM module not loaded correctly');
const outputDir = path.join(__dirname, 'output');
await fs.mkdir(outputDir, { recursive: true });
const fontsPath = path.join(__dirname, 'fonts');
try {
await fs.access(fontsPath);
spire.copyLocalPathToVFS(fontsPath, '/Library/Fonts/');
} catch { }
// Create a new workbook
const workbook = new spirexls.Workbook();
// Clear default worksheets
workbook.Worksheets.Clear();
// Add a new worksheet named "MySheet"
const sheet = workbook.Worksheets.Add("MySheet");
// Set text for the "A1" range
sheet.Range.get("A1").Text = "Hello World";
// Set the column width to auto fit
sheet.Range.get("A1").AutoFitColumns();
// Define the output file name
const outputFileName = 'HelloWorld.xlsx';
// Save the workbook to the specified path
workbook.SaveToFile({ fileName: outputFileName, version: spirexls.ExcelVersion.Version2010 });
const outputFile = path.join(outputDir, outputFileName);
spire.copyFileFromFSToLocalStorage(outputFileName, outputFile);
workbook.Dispose();
console.log(`Document saved to ${outputFile}`);
} catch (error) {
console.error(error);
process.exit(1);
}
}
main();
Using “npm start” run the program, you will find the generated Word file in the designated file path.
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
Enhancing PDF documents with interactive elements has become increasingly important for improving user engagement and functionality. Adding actions to PDFs, such as linking to document pages, executing JavaScript, or triggering a file opening, can significantly elevate the utility of these documents in various professional and personal applications. By incorporating such dynamic features using the Spire.PDF for Java library, developers will be able to unlock new possibilities for their PDF documents, making them more versatile and user-friendly. This article will demonstrate how to add actions to PDF documents with Spire.PDF for Java.
- How to Add Actions to PDF using Spire.PDF for Java
- Create Navigation Actions in PDF with Java
- Create File-Open Actions in PDF with Java
- Create Sound Actions in PDF with Java
- Create JavaScript Actions in PDF with Java
Install Spire.PDF for Java
First of all, you need to add the Spire.Pdf.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file by adding the following code to your project's pom.xml file.
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.pdf</artifactId>
<version>12.10.3</version>
</dependency>
</dependencies>
How to Add Actions to PDF using Spire.PDF for Java
Spire.PDF for Java enables developers to add a variety of actions to PDF documents, such as navigation actions, file-open actions, sound actions, and JavaScript actions. Below is a table of classes and their descriptions for commonly used actions:
| Class | Description |
| PdfGoToAction | Represents an action that navigates to a destination within the current document. |
| PdfLaunchAction | Represents an action that launches and opens a file. |
| PdfJavaScriptAction | Represents an action that executes JavaScript code. |
| PdfSoundAction | Represents an action that plays a sound. |
For more action classes and their descriptions, refer to Spire.PDF for Java action API references.
Actions can be added to PDF documents in two primary ways:
1. Using Action Annotations
This method involves creating an action and linking it to an annotation on the page. The action is displayed and triggered when the annotation is clicked.
General Steps:
- Create a PdfDocument instance and load a PDF document using PdfDocument.LoadFromFile() method.
- Create an action instance and set its properties.
- Optionally, draw cue text or images to indicate the action.
- Create a PdfActionAnnotation instance using the action instance and specify its location on the page.
- Add the action annotation to the page using PdfPageBase.getAnnotations.add() method.
- Save the document using PdfDocument.SaveToFile() method.
2. Assigning Actions to Document Events
Actions can also be assigned to document-level events such as opening, closing, or printing the document. These actions are triggered automatically when the specified events occur.
General Steps:
- Create a PdfDocument instance and load a PDF document using PdfDocument.LoadFromFile() method.
- Create an action instance and set its properties.
- Assign the action to a document event using the following methods:
- PdfDocument.setAfterOpenAction()
- PdfDocument.setAfterPrintAction()
- PdfDocument.setAfterSaveAction()
- PdfDocument.setBeforeCloseAction()
- PdfDocument.setBeforePrintAction()
- PdfDocument.setBeforeSaveAction()
- Save the document using PdfDocument.SaveToFile() method.
Create Navigation Actions in PDF with Java
The PdfGoToAction class can be used to create navigation actions in PDF documents, allowing users to jump to a specific location within the document. Below is a Java code example demonstrating how to create a navigation button in a PDF document.
- Java
import com.spire.pdf.PdfDocument;
import com.spire.pdf.actions.PdfGoToAction;
import com.spire.pdf.annotations.PdfActionAnnotation;
import com.spire.pdf.general.PdfDestination;
import com.spire.pdf.graphics.PdfBrushes;
import com.spire.pdf.graphics.PdfStringFormat;
import com.spire.pdf.graphics.PdfTextAlignment;
import com.spire.pdf.graphics.PdfTrueTypeFont;
import java.awt.*;
import java.awt.geom.Point2D;
import java.awt.geom.Rectangle2D;
public class AddNavigationActionPDF {
public static void main(String[] args) {
// Create a PdfDocument instance
PdfDocument pdf = new PdfDocument();
// Load a PDF file
pdf.loadFromFile("Sample.pdf");
// Create a PdfDestination object
PdfDestination destination = new PdfDestination(2, new Point2D.Float(0, 0), 0.8f);
// Create a PdfGoToAction object using the PdfDestination object
PdfGoToAction goToAction = new PdfGoToAction(destination);
// Draw a rectangle and the cue text on the first page
Rectangle2D rect = new Rectangle2D.Float(20, 30, 120, 20);
pdf.getPages().get(0).getCanvas().drawRectangle(PdfBrushes.getLightGray(), rect);
PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Arial", Font.BOLD, 12), true);
PdfStringFormat format = new PdfStringFormat(PdfTextAlignment.Center);
pdf.getPages().get(0).getCanvas().drawString("Click to go to page 2", font, PdfBrushes.getBlack(), rect, format);
// Create a PdfActionAnnotation object using the PdfGoToAction object
PdfActionAnnotation actionAnnotation = new PdfActionAnnotation(rect, goToAction);
// Add the annotation to the first page
pdf.getPages().get(0).getAnnotations().add(actionAnnotation);
// Save the document
pdf.saveToFile("output/PDFNavigationAction.pdf");
pdf.close();
}
}
Create File-Open Actions in PDF with Java
Developers can use the PdfLaunchAction class to create a file-open action in a PDF document. Below is a Java code example showing how to add a file-open action to a PDF document.
- Java
import com.spire.pdf.PdfDocument;
import com.spire.pdf.actions.PdfFilePathType;
import com.spire.pdf.actions.PdfLaunchAction;
import com.spire.pdf.annotations.PdfActionAnnotation;
import com.spire.pdf.graphics.PdfBrushes;
import com.spire.pdf.graphics.PdfStringFormat;
import com.spire.pdf.graphics.PdfTextAlignment;
import com.spire.pdf.graphics.PdfTrueTypeFont;
import java.awt.*;
import java.awt.geom.Rectangle2D;
public class AddFileOpenActionPDF {
public static void main(String[] args) {
// Create a PdfDocument instance
PdfDocument pdf = new PdfDocument();
// Load a PDF file
pdf.loadFromFile("Sample.pdf");
// Create a PdfLaunchAction object and set the file path
PdfLaunchAction launchAction = new PdfLaunchAction("C:/Example.pdf", PdfFilePathType.Absolute);
// Draw a rectangle and the cue text on the first page
Rectangle2D rect = new Rectangle2D.Float(20, 30, 120, 20);
pdf.getPages().get(0).getCanvas().drawRectangle(PdfBrushes.getLightGray(), rect);
PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Arial", Font.BOLD, 12), true);
PdfStringFormat format = new PdfStringFormat(PdfTextAlignment.Center);
pdf.getPages().get(0).getCanvas().drawString("Click to open the file", font, PdfBrushes.getBlack(), rect, format);
// Create a PdfActionAnnotation object using the PdfLaunchAction object
PdfActionAnnotation actionAnnotation = new PdfActionAnnotation(rect, launchAction);
// Add the annotation to the first page
pdf.getPages().get(0).getAnnotations().add(actionAnnotation);
// Save the document
pdf.saveToFile("output/PDFFileOpenAction.pdf");
pdf.close();
}
}
Create Sound Actions in PDF with Java
The PdfSoundAction class is used to handle sound playback in PDF documents, enabling features such as background music and voice reminders. The Java code example below demonstrates how to create sound actions in PDF documents.
- Java
import com.spire.pdf.PdfDocument;
import com.spire.pdf.actions.PdfSoundAction;
import com.spire.pdf.annotations.PdfActionAnnotation;
import com.spire.pdf.general.PdfSoundChannels;
import com.spire.pdf.general.PdfSoundEncoding;
import com.spire.pdf.graphics.PdfImage;
import java.awt.geom.Rectangle2D;
public class AddSoundActionPDF {
public static void main(String[] args) {
// Create an instance of PdfDocument
PdfDocument pdf = new PdfDocument();
// Load a PDF file
pdf.loadFromFile("Sample.pdf");
// Create a PdfSoundAction object and set the audio property
PdfSoundAction soundAction = new PdfSoundAction("Music.wav");
soundAction.setRepeat(false);
soundAction.getSound().setBits(16);
soundAction.getSound().setChannels(PdfSoundChannels.Stereo);
soundAction.getSound().setEncoding(PdfSoundEncoding.Signed);
soundAction.getSound().setRate(44100);
// Draw the sound logo on the first page
PdfImage image = PdfImage.fromFile("Sound.jpg");
pdf.getPages().get(0).getCanvas().drawImage(image, new Rectangle2D.Float(40, 40, image.getWidth(), image.getHeight()));
// Create a PdfActionAnnotation object using the PdfSoundAction object at the location of the logo
Rectangle2D rect = new Rectangle2D.Float(40, 40, image.getWidth(), image.getHeight());
PdfActionAnnotation actionAnnotation = new PdfActionAnnotation(rect, soundAction);
// Add the annotation to the first page
pdf.getPages().get(0).getAnnotations().add(actionAnnotation);
// Save the document
pdf.saveToFile("output/PDFSoundAction.pdf");
pdf.close();
}
}
Create JavaScript Actions in PDF with Java
The PdfJavaScript class allows developers to create JavaScript actions within PDF documents, enabling custom interactive features such as creating dynamic forms, validating user input, and automating tasks. The following Java code example demonstrates how to add JavaScript actions to a PDF document.
- Java
import com.spire.pdf.PdfDocument;
import com.spire.pdf.actions.PdfJavaScript;
import com.spire.pdf.actions.PdfJavaScriptAction;
public class AddJavaScriptActionPDF {
public static void main(String[] args) {
// Create a PdfDocument instance
PdfDocument pdf = new PdfDocument();
// Load a PDF file
pdf.loadFromFile("Sample.pdf");
// Define the JavaScript code and use it to create an instance of PdfJavaScriptAction
String jsCode = """
app.alert({
cMsg: 'Welcome to the article on The Timeless Delight of Bread!\\n\\nThis article explores the rich history, varieties, and cultural significance of bread. Enjoy your reading!',
nIcon: 3,
cTitle: 'Document Introduction'
});
""";
PdfJavaScriptAction javaScriptAction = new PdfJavaScriptAction(jsCode);
// Set the JavaScript action as the action to be executed after opening the PDF file
pdf.setAfterOpenAction(javaScriptAction);
// Save the document
pdf.saveToFile("output/PDFJavaScriptAction.pdf");
pdf.close();
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
