Introducing Excel library for Java
If you're programming in Java and looking for an Excel API that works with Excel documents, we have good news for you! GrapeCity is pleased to launch Documents for Excel (GcExcel), Java Edition.
GcExcel Java is a high-speed, feature-rich Excel document API based on VSTO. We recently released the GcExcel for .NET Version in April, and with this launch it's now possible to use a similar package in Java applications.
The library helps to generate, convert, calculate, format, and parse spreadsheets in any Java application. The feature set helps you with a variety of actions like importing spreadsheets, calculating data, querying, generating, and exporting any spreadsheet. Users can also add sorting, filtering, formatting, and conditional formatting in their spreadsheets and validate data as well. Other features include adding grouping, sparklines, charts, shapes, pictures, slicers, comments, hyperlinks, and themes. Additionally, you can import existing Excel templates, add data, and then save the spreadsheets back.
GcExcel Java also works together with Spread.Sheets, another GrapeCity Spread product that is included in SpreadJS. GcExcel can import and export Excel template files on the server-side. Spread.Sheets works in the browser (client-side) as a viewer or editor.
The library is fast, efficient and lightweight. GcExcel Java includes all features that are supported in the .NET version, allowing developers to fulfil complex business use cases and work with Excel documents in Java applications.
GcExcel Java key features
- API is based on Excel's document object model, that allows you to import, calculate, query, generate, and export any spreadsheet scenario. With the VSTO-style API, you can create custom styles using the same elements as VS Tools for Office
- Generate, load, edit, analyze, convert, and save Excel spreadsheets in Java applications with full support on Windows, MAC and Linux
- Generate and work with spreadsheets with fast performance and a small footprint
- Format your content, add conditional formatting, tables, formulas, filers, sorting, sparklines, charts, shapes, pictures, comments, pivot tables, themes, workbook, and worksheets
- Import and export Excel templates
GcExcel Java use cases
With GcExcel, you can create the following spreadsheets:
- Event budget
- Sales report
- Cost analysis
- Customer profitability analysis
- Contacts list
- To-dos list
- Financial KPIs
- Income statements
- Monthly budget
- Aging report
- Annual financial report
- Project cost tracker
- And much more
GcExcel Java offers:
1. Cross-platform functionality
2. Generate spreadsheets with fast performance and small footprint
3. Operate over range of cells
9. Improve analysis by grouping your data
14. Add detailed data visualization with charts
18. Pivot tables to analyze your data
19. Add themes to change the look of your spreadsheet
22. Import and export Excel templates
23. Integrate with Spread.Sheets products
1. Cross-platform functionality
Generate, load, edit, analyze, convert, and save workbooks with full support on Windows, MAC and Linux!
GcExcel Java acts as a cross-platform solution. This means that you can generate and work with Excel spreadsheets using a single code base that can run across all three major OS platforms. GcExcel Java can work in any kind of Java application including desktop and mobile (Android). In most cases, GcExcel works in web apps and application services. GcExcel can also work in applications deployed in the cloud.
2. Generate spreadsheets with fast performance and small footprint
GcExcel is fast in performance. The light-weight architecture makes it an efficient tool that offers high performance and memory efficiency while generating or working with Excel spreadsheets.
3. Operate over range of cells
In order to work with spreadsheets and modify data, you need to access the range of data. GcExcel supports the following operations when working with range of cells:
- Access cells, rows, and columns in a range
- Access areas in a range
- Access a range
- Use CellInfo helper class
- Cut or copy ranges
- Get used range features
- Get row and column count
- Get used range
- Hide rows and columns
- Insert or delete cells
- Insert or delete rows and columns
- Merge cells
- Set values to a range
- Set row height and column width
4. Format your content
When it comes to generating Excel spreadsheets, it's essential to produce clean, organized spreadsheets. Proper formatting plays an important role in the design of your spreadsheet.
GcExcel supports the following formatting:
- Alignment (HorizontalAlignment, VerticalAlignment, ReadingOrder, ShrinkToFit, Orientation, Wrap Text)
- Borders (BorderLineStyle - dashed, dashDot, dotted, double, medium, hair, etc.)
- Fill Colors (solid fill, pattern fill, linear gradient fill, rectangular gradient fill)
- Fonts (font color, font size, font name, bold, italic, underline, strikethrough, superscript, subscript)
- Number formats (Excel-style)
- Security settings
- Styles
5. Add conditional formatting
Depending upon the information on the cells, you can add conditional formatting or highlight your cells based on some rules. If the cell values match the rule, formatting can be applied on them. Following conditional formatting rules can be applied on the range of cells:
- Cell Value
- Average
- Color Scale
- Data Bar
- Icon Sets
- Top Bottom
- Unique
- Expression
6. Add tables
Tables help you to organize data in the form of rows and columns and you can assign a range of data to the Table. The following operations are supported for a Table:
- Add or remove a table in a worksheet
- Apply filtering to a table column
- Apply sorting to a table column
- Apply either built-in or custom table styles
- Access each range of a table
- Add or remove a table column
- Perform calculations for a specific table range
7. Validate your data
With GcExcel, you can restrict the data entered into the cells by applying data validations like Whole Number, Decimal, List, Date, Time, Text length, and Custom. This also helps in generating spreadsheets with low chance to introduce errors!
8. Add formulas
A powerful feature of Excel is the ability to add formulas to the data that can recalculate the results for you (even if you change the data). GcExcel helps you replicate the same advantages and you can apply following formulas to your spreadsheet:
- Hundreds of built-in Excel functions
- Array formulas
- Dirty and Calculate methods
- Table formuas
9. Improve analysis by grouping your data
For representing data and analyzing it in a better way, grouping data is necessary in a spreadsheet. This helps you summarize and easily navigate your data. GcExcel provides the following methods to group your data:
- Collapse group
- Create a range group
- Show specific level group
- Ungroup range
10. Configure page setup
What settings you want before printing are possible to set using GcExcel when printing spreadsheets. No matter what print setting your project requires, you can configure it accordingly in GcExcel. It provides all standard page setup options. You can configure:
- Page Header/Footer
- Page number
- Page margins
- Page Orientation
- Paper scaling
- Paper size
- Print area
- Sheet Print settings
- Row/Column repeat
11. Filter your data
You can filter your columns of data using GcExcel, to organize your results in a meaningful way. It also helps in visualizing data of similar types by arranging rows of data together. You can filter data by number, text, color, icon, and date.
12. Apply sorting
In addition to filtering your data, you can also sort your data in multiple ways with GcExcel. You can sort on value, font color, interior, icon, or create a custom sorting list. This can help you identify the information faster as the data before you is sorted in the order you choose.
13. Add sparklines
To summarize a trend, GcExcel provides the ability to add sparklines that add a mini chart within the boundaries of a cell and shows a trend increase or decrease of values over a year. Line, Column, and Win/Loss are the sparklines that are supported by GcExcel.
14. Add detailed data visualization with charts
GcExcel provides a comprehensive API for adding charts to your spreadsheets. There are 50+ chart types that you can use with detailed chart properties.
You can:
- Add charts to a worksheet.
- Add data series.
- Customize chart titles.
- Cutomize chart legends.
- Access and customize each data series.
- Customize each data point in a data series.
- Add, remove, or customize each data label in a series.
- Access and customize a group of series.
- Access and customize value axes, category axes, and series axes.
15. Add shapes and pictures
To enhance your data in the spreadsheet, you can add drawing objects in the form of shapes and pictures with GcExcel. You can insert arrows, lines, charts, slicers, pictures, and general shapes. In addition, you can fill shapes, add text to them, and set the line styles on them. You can also add, delete, cut, copy, and duplicate pictures.
16. Filter data with slicers
Slicers make it easier and faster to apply filters to tables and pivot tables. While you can filter data with standard filtering capabilities of GcExcel, slicers are a kind of visual filter to the slice data you want to see.
You can:
- Add a custom slicer style
- Add slicers to pivot tables
- Add slicers to tables
- Apply built-in style to slicer
- Configure slicer layout
- Auto-filter table with slicer
- Copy slicer
- Cut slicer
- Duplicate slicer
- Use slicer to filter table
- Use a slicer to clear a filter
17. Comment on your data
You cannot put every type of information on your spreadsheets. Sometimes you want to review the data and add comments for any modifications. Perhaps you want to add notes, or put short summaries over data.
With GcExcel, you can:
- Add comment to a cell
- Clear comments
- Configure comment's layout
- Set rich text for comment
18. Pivot tables to analyze your data
Sometimes you need to go beyond having a table, calculations, and formulas to extract significance from a larger data set. Without explicitly setting formulas to perform the calculations, you can play around with different arrangements of data and summarize values.
Working with pivot tables in GcExcel, you can:
- Create pivot tables
- Set field's summarize function
- Filter items in pivot tables
- Modify pivot field subtotal type
- Refresh pivot table
19. Add themes to change the look of your spreadsheet
You don't need to apply specific formatting on your cells, but you can still make use of several in-built themes to change the way your spreadsheets look using just one simple line of code. You can also build your own custom themes or change existing workbook themes to apply on your spreadsheets.
20. Use Workbook functions
All of the above features can be worked upon through the workbook class.
You can use the following functions with a workbook:
- Configure workbook view
- Create new workbook
- Import csv file to workbook
- Import csv file to workbook with options
- Only import data from Excel
- Import Excel file
- Do not recalucate after opening Excel
- Import Excel file with password
- Save workbook to csv file
- Save workbook to csv file with options
- Save to Excel file
- Save to Excel file with password
- Convert workbook to Json and from Json
Please note that GcExcel depends on javax.json for using toJson and fromJson functions. In order to use these functions, download the javax.json package and add it as a library in the project.
21. Working with worksheet
You can add several worksheets and arrange data in them with GcExcel. The Following functions are possible to use with worksheets:
- Access a worksheet
- Activate a worksheet
- Add a worksheet
- Configure worksheet view
- Configure worksheet properties
- Delete a worksheet
- Protect worksheet
- Save worksheet to csv file
- Save worksheet to csv file with options
- Unprotect worksheet
22. Import and Export Excel templates
If you have existing templates and want to modify data, you can load them in GcExcel, modify the data and then save it back. Working with GcExcel provides an advantage because you can create even more advanced spreadsheets by loading your templates.
23. Integrate with Spread.Sheets products
While GcExcel can help with importing and exporting Excel templates, in order to view or edit data in your browser, you can use GcExcel with the Spread.Sheets control.
Here is the procedure:
- Open an Excel file with GcExcel existing on server.
- Export the whole workbook to a JSON string by calling Workbook.toJson, then transfer the JSON string to client side.
- At client side, the Spread.Sheets control will get workbook using Workbook.fromJson to let you view the result of the excel file on server.
24. Convert Workbook to PDF
When working with Word documents, it is essential that you have the ability to convert the document to a PDF. The PDF format adds security and data preservation for long-term storage and archiving. You can directly save a workbook to PDF using GcExcel. You do not need to create an Excel file first. GcExcel Java uses PDFBox for exporting spreadsheets to PDF.
All of the following export features are supported:
Set Line properties
- Line Width
- Line Color
- Line DashArray
- Line Position
- Line Cap
Set Text properties
- Custom Font
- Bold
- Italic
- MeasureString
- Font Size
- Font Color
- Text Position
- Annotation
Set Fill
- Solid
Images
- Set Page
Set Clip Region
Export
- Text
- Number formats
- Overflow text
- Font effects
- Borders
- Conditional Formatting
- Picture
- Fills
- Sparklines
- Table
- Save workbook to PDF
- Save worksheet to PDF
- Set font's folder
Check out Excel Reporting and Excel Templates to see them in action.
Take a look at our SpringBootDemo on GitHub to see how to use GcExcel Java with SpringBoot, React, Angular2 and Spread.Sheets
Documentation
Visit GrapeCity Documents for Excel, Java Edition documentation to read more about the product, its features and to view more code samples.
Shilpa Sharma