Introduction: How you can use java to create and work excel files
Excel-Java integration enables the seamless connection between the power of excel spreadsheets and the flexibility of the java programming language.
Java provides powerful capabilities for creating excel files and generating excel reports in various formats, including XLSX. With libraries such as SCell, developers can unleash the potential of java and java components to effortlessly create excel files and generate comprehensive excel reports with ease.
In this article, we will explore the techniques and libraries, which are available in java to create excel files and generate professional-grade xlsx reports to meet diverse business requirements.
Unleashing SCell: smart java to create excel files and work them in your application
SCell – is a tool that allows you to tune your java applications flexibly with customizable spreadsheets.
You can use this smart java component to create and edit excel files or Google Spreadsheets and import data to them from your application. Whenever you need to update them, you have to switch screens several times. This slows things down for you.
SCell saves that time and money for you. This Java spreadsheets component gives your application a spreadsheet editing interface with common features you use in MS Excel or Google Sheets.
- Effortlessly modify spreadsheets within your application, whether with a user interface or without one.
- Eliminate the need to switch between MS Excel or Google Sheets.
- Save valuable time for your development team and reduce expenses on spreadsheet software licenses.
That being said, we dive into the benefits of using SCell.
Benefits of Using SCell
If your organization relies on Java applications, software development, or Java upgrades, you will gain significant advantages by utilizing our Java spreadsheets library, SCell.
- Create Exceptional Applications: Empower your Java application development with SCell comprehensive component library that enables users to seamlessly interact with spreadsheets.
- Unify Your Workflow: Streamline your processes by eliminating the need for integrating multiple applications. Leverage SCell Java library, which provides a comprehensive interface to access and utilize all spreadsheet features within a single application.
- Reduce Expenses: Say goodbye to costly spreadsheet software licenses, optimize your staff’s productivity, and eliminate the need for custom development expenses. Save both time and resources with SCell Java library.
- Enhance Your Offering: Provide Java developers with a powerful library that empowers them to incorporate advanced spreadsheet capabilities into their products. Expand your range of offerings by delivering SCell feature-rich Java library.
SCell features: What can you do with SCell
- User Interface: SCell offers a pre-built, fully customizable JavaFX graphical user interface (GUI) as part of its package.
- Desktop: Seamlessly Integrate SCell into Your Desktop Application.
- OS Support: cross plattform support (Windows, Linux and macOS).
- Customization: Leverage SCell as an Essential Tool to Enhance Your Business Software.
- Functions and Formulas: Leverage Over 120 Familiar Functions and Formulas you know from different worksheet apps.
- Formatting: Effortlessly align text within cells, modify fonts, apply vibrant cell colors, import and format images, generate dynamic charts, and unlock a range of other powerful features. With SCell, you have the ability to customize and elevate your spreadsheets to meet your specific needs and make them visually appealing and impactful.
- Web: Seamlessly Integrate SCell into Your Server-Side Application.
- Clipboard: Effortlessly Copy worksheets to clipboard and insert them when you need to.
- API: Harness the power of SCell Java API to make modifications to xlsx-files (Open XML format) and tailor SCell to align with your specific requirements.
How to connect SCell into a project using Maven
The first step is to acquire a license and the Intechcore’s Nexus credentials, then configure the “settings.xml” file for the local Maven repository folder ($HOME/.m2).
Insert the given credentials into the appropriate place:
<settings>
<servers>
<server>
<id>intechcore-server</id>
<username>given_username</username>
<password>given_password</password>
</server>
</servers>
</settings>
The second step is to connect the API as dependency in your Maven project. The following changes are required in the projects “pom.xml” file:
<repositories>
<repository>
<id>intechcore-server</id>
<name>scalable components demo</name>
<!-- Remove if use full version -->
<url>https://nexus.intechcore.online/repository/maven-scomponents-demo</url>
<!-- Remove if use DEMO version -->
<url>https://nexus.intechcore.online/repository/maven-scomponents-releases-scell/</url>
</repository>
</repositories>
<properties>
<!-- leave just one what you need, remove the rest -->
<!-- OR to use Java 8 -->
<scell.api.version>1.1.1-java8</scell.api.version>
<!-- OR to use Java 11 -->
<scell.api.version>1.1.1-java11</scell.api.version>
<!-- OR to use Java 8 DEMO -->
<scell.api.version>1.1.1-java8-DEMO</scell.api.version>
<!-- OR to use Java 11 DEMO -->
<scell.api.version>1.1.1-java11-DEMO</scell.api.version>
</properties>
<dependencies>
<dependency>
<groupId>com.intechcore.scomponents.scell</groupId>
<artifactId>api-interfaces-core</artifactId>
<version>${scell.api.version}</version>
</dependency>
<dependency>
<groupId>com.intechcore.scomponents.scell</groupId>
<artifactId>api-impl-core</artifactId>
<version>${scell.api.version}</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.intechcore.scomponents.scell</groupId>
<artifactId>api-interfaces-ui</artifactId>
<version>${scell.api.version}</version>
</dependency>
<dependency>
<groupId>com.intechcore.scomponents.scell</groupId>
<artifactId>api-impl-ui</artifactId>
<version>${scell.api.version}</version>
<scope>runtime</scope>
</dependency>
</dependencies>
After that the SCell API is ready to work with.
How to work with SCell’s java excel API.
SCell API Structure.
To work with SCell API the following entities need to be used:
Workbook
This is the base entity of the spreadsheet system. It handles/manages the list of existing spreadsheets.
Worksheet
This is a single spreadsheet instance, which includes a set of “content managers” that makes the spreadsheet manageable and customizable, such as Services, Storages and so on. They are represented as corresponding interfaces and placed in the appropriate Storages.
It is possible to update the values of the existing “contents” or to build the new ones with the help of builders (each “content” has the appropriate builder, the examples of their use will be shown later).
The above mentioned can be obtained from IScellApiResolver.
“ScellApiEntryPoint” must be called to initialize the API and get the “IScellApiResolver” for further work.
ScellApiEntryPoint.getApiResolverAsync().thenAccept(resolver -> {
// ...
});
The static method “getApiResolverAsync” returns the resolver asynchronously using CopletableFuture.
How to load/create/save xlsx files using java.
The “IScellApiFactory” is required to read/save/create the xlsx files. As was described before, its instance can be obtained from the resolver.
Loading and creating excel files using java.
To load and create the xlsx file the appropriate methods (“load()” and “create()”) are necessary:
ScellApiEntryPoint.getApiResolverAsync().thenAccept(resolver -> {
IScellCoreApiFactory apiFactory = resolver.resolve(IScellCoreApiFactory.class);
// loading
IWorkbook existingWorkbook = apiFactory.load(new File("/path/to/existing/file.xlsx"));
IWorkbook newWorkbook = apiFactory.createNew();
// ...
}).whenComplete((unused, throwable) -> {
if (throwable != null) {
System.out.println(throwable.getMessage());
}
}).join();
Both of them return the IWorkbook instance for further work.
Saving modified or newly created xlsx document.
To save a created or a modified document, the IFileWriter is required. In addition to saving the IFileWriter provides the possibility to set the file name and type. It can be obtained from the IWorkbook instance:
IFileWriter fileWriter = existingWorkbook.getWriter();
fileWriter
.fileName("spreadsheet-xlsx")
.fileType(IFileWriter.FileType.XLSX)
.save();
Reading data from Speadsheet.
To get any data from IWorksheet it’s necessary to call the IWorksheet.getStoages() method, which returns the “IWorksheetStorage”. It contains a set of “IMapStorage”, each of them contains its own type of value (BorderSorage, CommentStorage, FontStorage, HyperlinkStorage, ImageStorage, StyleStorage, ValueStorage).
To get a value – a suitable “getter” method is necessary, for example, “getValueStorage()”.
The “IMapStorage” returns queried values in the form of java.util.stream.Stream. It’s possible to get either all values or only query a certain range of values contained in provided “IRangeAddress” type range.
The “IRangeAddressBuilder” sets a single or a range address with help of the appropriate methods:
“setTopLeft(int row, int column)”,
“setBottomRight(int row, int column)” which set two points of the required range;
“buldCell()” which builds a single cell address, in this case only top left point is required;
“buldRange()” builds a range address, two points are required.
Address builder can also be created from a “Human readable” format, such as A1(column 1, row 1), A1:B1(from: column 1, row 1; to: column 2, row 1) etc., using the “fromA1String()” method.
A single value is represented as an appropriate interface, for example IValue<T>.
At this stage, the required data can be received:
IRangeAddressBuilder addressBuilder = resolver.resolve(IRangeAddressBuilder.class);
IRangeAddress address = addressBuilder.setTopLeft(5, 3).buildCell();
// or
IRangeAddress address = addressBuilder.fromA1String(“C5”).buildCell();
String value = (String) worksheet.getStorages()
.getValueStorage()
.findContaining(address)
.findFirst()
.get()
.unbox();
Writing data into the worksheet
API provides the possibility to change cell data and styles, such as filling colors fonts and borders.
There are three ways to change the cell value:
worksheet.inputContent(IRangeAddress range, String input”);
This method works as if value was entered into the cell by using UI, the data will be parsed and translated into corresponding values.
Worksheet.setContent(IRangeAddress range, Number/boolean input)
Enters a numeric or boolean(logical) value.
A more controllable way of data input – is using the “IValueService” and “IValueFactory”:
worksheet.getValueService().set(addressBuilder.fromA1String("B2"),
iValueFactory -> iValueFactory.createNumeric(123)
);
IWorksheet also contains a set of Services (BordrerServise, CommentService, FontService, HyperlinkService, StyleService, ValueService). They can be used to set or update their values:
IColorConstants constants = resolver.resolve(IColorConstants.class);
worksheet.getStyleService().set(addressBuilder.fromA1String("A1"),
iStyleBuilder -> iStyleBuilder.setBackgroundColor(constants.red())
);
worksheet.getFontService().set(addressBuilder.fromA1String("A1"),
iFontBuilder -> iFontBuilder.setBold(true).build()
);
Creating a UI component.
To create a JavaFx UI node it’s necessary to use the “IScellUiApiBuilder”. It provides the possibility to enable/disable different spreadsheet UI components and features, such as scrollbars, context menus and so on.
After setting the desired parameters, method “create(CompletableFuture<IWorkbook> coreApi)”, which returns the “IScellUiApi” instance, which can return the JavaFx “Node” using the “getControll” method:
IWorkbook workbook = apiFactory.createNew(); IScellUiApiBuilder<Node> uiBuilder = resolver.resolve(IScellUiFxApiBuilder.class); IScellUiApi<Node> uiApi = uiBuilder .verticalScrollbarVisible(false) .inputBarVisible(false) .create(CompletableFuture.completedFuture(workbook)); Node spreadSheet = uiApi.getControl(); StackPane stackPane = new StackPane(spreadSheet);
We invite you!
If you need any additional information or help implementing Java excel integration techniques, we are here to assist you. As experts in the field, we offer consultation and development services tailored to your specific requirements. Whether you need guidance in utilizing libraries like SCell, or require customized solutions for your needs, we have the expertise to support you every step of the way.
Furthermore, we invite you to take advantage of a free demo we offer, where you can experience the power of SCell and other scalable Java components firsthand. This demo will showcase the exceptional features of SCell and demonstrate how it can enhance your excel-related workflows. By attending the demo, you’ll gain valuable insights into the capabilities of Java excel integration and understand how it can benefit your business.
Contact us today to learn more about our consultation and development services or to schedule your free demo. We look forward to assisting you in leveraging the full potential of Java excel integration for your business success.
