Generate excel with java

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Generate excel sheets in Java

License

tobyweston/simple-excel

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

Simply modify and diff Excel sheets in Java

Simple-Excel wraps the Apache POI project with simple Java builders to modify sheets quickly and easily without all the boilerplate.

Use Hamcrest Matcher s to compare workbooks and get fast feedback in tests. Comparing two sheets will compare the entire contents. You get a full report of the diff rather than just the first encountered difference.

Add styles, formula and content to cells programmatically via a simple DSL.

@Test public void shouldReplaceCellsInComplicatedAlternateSyntaxExample() throws IOException < public void shouldReplaceCellsInComplicatedAlternateSyntaxExample() throws IOException < HSSFWorkbook workbook = getWorkbook("shouldReplaceCellsInComplicatedExampleTemplate.xls"); new PoiWorkbookMutator(workbook) .replaceCell(coordinate(C, 5), "Adding") .replaceCell(coordinate(D, 11), "a") .replaceCell(coordinate(G, 3), "cell") .replaceCell(coordinate(J, 10), "total") .replaceCell(coordinate(M, 15), 99.99d); assertThat(workbook, sameWorkbook(getWorkbook("shouldReplaceCellsInComplicatedExampleTemplateExpected.xls"))); >

A break in the matcher would show something like

java.lang.AssertionError: Expected: equality of cell "G1" but: cell at "G1" contained "Text"> expected 99.99D> at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)

Define some styles to reuse

private final Border border = border(top(NONE), right(THIN_SOLID), bottom(THIN_SOLID), left(THIN_SOLID)); private final DataFormat numberFormat = dataFormat("#,##0.00");

Next create your Cell with style information.

Cell cell = new DoubleCell(99.99d, aStyle().with(border).with(numberFormat));

HashMapColumnIndex, Cell> columns = new HashMapColumnIndex, Cell>() put(column(A), cell); >>; Row row = new Row(columns);

add your row to the workbook.

new PoiWorkbookMutator(workbook).mutator.appendRowToFirstSheet(row);

To get more detailed output from mismatches, be sure to use MatcherAssert.assertThat from Hamcrest rather than the vanilla JUnit version ( org.junit.Assert.assertThat ). If you use the JUnit version, you’ll get output similar to the following.

java.lang.AssertionError: Expected: entire workbook to be equal got: org.apache.poi.hssf.usermodel.HSSFWorkbook@6405ce40>

When, using MatcherAssert , you’d see something like this.

java.lang.AssertionError: Expected: entire workbook to be equal but: cell at "C14" contained "bananas"> expected nothing>, cell at "C15" contained "1,850,000 EUR"> expected "1,850,000.00 EUR">, cell at "D16" contained nothing> expected "Tue Sep 04 06:30:00"> at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)

• Currently, matching doesn’t take into account styling. A cell is equal to another regardless of style. If one has a border for example, and the other doesn’t but they have the same values, they are considered equal.

Available for manual download via my Maven repository or add the repository to your ‘pom.xml’.

repositories> repository> id>bad.robotid> name>bad.robot repository on robotoolingname> url>https://robotooling.com/maven/url> snapshots> enabled>trueenabled> updatePolicy>alwaysupdatePolicy> snapshots> repository> repositories>

dependency> groupId>bad.robotgroupId> artifactId>simple-excelartifactId> version>1.2version> scope>compilescope> dependency>

For more tools, see robotooling.com and visit my blog.

Источник

How can you solve this problem?

In order to solve the problem that anyone can modify it without knowing a lot of some specific technology appears JXLS. JXLS is a library that provides you the possibility of creating an Excel template with some specific logic inside i.e. you can define the color of the cells depending on the content of one attribute or hide one row if one attribute is null or empty.

Behind the scenes, JXLS uses Apache POI or JExcel as core and other libraries to translate some tags in the template into a new document with all the information.

To start using JXLS you will need to add the dependency in your pom file:

After that, you need to choose which implementation of JXLS you will use to generate the Excel document. You have two possible options:

• Apache POI is the most common library which most developers use to generate Excel documents in an artisanal way.
• JExcel is an old library that provides common features. The main problem with this library is the last version was launched in 2019.

  org.jxls jxls-poi 2.9.0   org.jxls jxls-jexcel 1.0.9  

Above you will see both dependencies just to show you the name of them, but you need to add only one of them.

Populating the template

Now you have dependencies in your project, the next thing to do is to send the information to JXLS. You need to have a class that transforms all the information in a way that JXLS can understand it. The code below is an example of how you can implement it.

1. Load the template
2. The Context is a map with all the attributes you will send to the JXLS to generate the document. For this specific example, the method “createDocument” receives a Map so you can reuse this code on any template and send a dynamic number of attributes to it.
3. JXLS process all the information and generate a new report.
4. Close the outputStream.

The objective of this example is to reduce the lines of code to create multiples reports so for that reason the method receives the name of the template and the attributes to send to JXLS. Also, the method receives the outputStream so you can save the document in a file for a desktop application or download it in a web application in a simple way.

Generating a template

Now you need to create the template. Imagine that someone in your team sends this document and tells you he/she needs this particular format.

First, you need to clear the document, the focus is on the format of the template.

The second step is to add the tags to provide some logic in the document, there are two ways to do it:

• As comments in the cells
• Explicit in the document

The most common tags of JXLS are:

• jx:area this tag tells JXLS is the area to process. — lastCell: in this attribute, you need to add which is the last cell to process. This is important because JXLS not process anything after that cell.
• jx:each the idea behind this tag is to iterate a collection of elements. — items: is a name of the variable in the context which is a collection.
  — var: is the name of the variable to use
  — lastCell: is the last cell to process for this particular tag
• jx:if you can show the content with one format or another depending on the condition.
  — condition: is the condition to validate
  — lastCell: is the last cell to process for this particular tag
  — areas: is a reference to an area to show in the case of the condition is valid or not.

This post only mentions some of the tags but there are more of them to do different things i.e. creating graphics or a multi-sheet. You can check all the available tags in the official documentation.

The third step is access to each element in the “Context” to populate the document, the way to do it is with the name of the object that you previously inserted in the “Context”. You can show any value of the objects using $ in any cell. One thing to mention is if you have an object with attributes you can access in this way “$” (i.e. $ ).

Now it’s time to implement all the concepts in the post, after modifying the document you can have something like this:

Some comments about the document:

• The cell “Created At” have the tag jx:area
• The row below the headers has two tags: jx:each to iterate all the clients and jx:if to show the row with one format or another depending on if the client is active or not
• The formula has two elements because depending on if the condition is valid or not, JXLS uses one row or another to apply the formula.

The last step is to create a class that invokes the “Report” class and sends the location of the generated document. For simplicity in this post, the code of that class is in the repository of Github which appears after the conclusion.

Conclusion

JXLS is a library with a lot of features that allows you to generate an Excel document in a simple way. The most important thing to consider how easy is for anyone to change the structure, format, or content of the template.

For curious ones, here is the code on Github and the official documentation:

Источник

Generating Microsoft Excel XLSX files in Java

It’s always interesting to be able to generate Microsoft Excel XLSX files in an application to propose reports to users for example. In that tutorial, you are going to learn how to generate Microsoft Excel files in a Java application.

For that, we are going to use the great Apache POI API available just there : https://poi.apache.org/download.html

Note that you can watch this tutorial as a video on YouTube :

Adding Apache POI as a dependency

First step is to add Apache POI as a Maven dependency in the build file of our Java project :

We have chosen to generate Excel XLSX files in that tutorial but the logic is the same to generate Excel XLS older file format. So, we have added both dependencies in our Maven POM but just the poi-ooxml is needed for our example.

Creating a Contact POJO

In our example, we are going to generate an Excel file containing a list of contacts. So, we create a Contact POJO object to represent the contacts as a Java object :

Like you can see, a Contact will have a first name, a last name, an email and a date of birth.

Creating the Excel file with the Contacts data

With the Apache POI API, an Excel file is representend by a Workbook object. Like we want to generate a XLSX file, we need to create a XSSFWorkbookinstance. For a XLS file, we would need to create a HSSFWorkbook instance. Once the Workbook instance is created, we need to create a new Sheet object by calling the createSheet method of the Workbook object :

In our generated file, we will have a row header with a specific style. For that, we create a Font object by calling the createFont method of the Workbookinstance. Then, we define the color and the size of our Font. Next step is to associate this font to a CellStyle object created by calling the createCellStylemethod of the Workbook instance like that :

Now, we must create the header row. We set the title for each cell and also the specific cell style created. Note that the title for each cell of the row header come from a static array defined as a property of our ExcelWriter class :

Then, we create other rows and cells with contacts’ data by iterating on the list of contacts. Besides, we resize all columns of our file to fit the content size :

Generating the Excel file

Finally, we have to generate the Excel file by writing the content of our Workbook instance to a file. For that, we create a FileOutputStream instance with the name for our file and then we pass this instance as a parameter of the write method of the Workbook object :

Complete source code of our ExcelWriter class

The complete source code of our ExcelWriter class is available just below :

Final Result

By executing the ExcelWriter class on a JVM, the contacts.xlsx file should be generated on your machine and you should have the following result :

That’s all for that tutorial. Don’t hesitate to visit the SSaurel’s Channel :

Related Posts

Developing a 15 Puzzle — Game of Fifteen in Java

Building A Backlink Checker In Java

Creating a Rock Paper Scissors Game in Java with a Markov Chain for the AI

No Comments Yet

Leave a Reply Cancel reply

You must be logged in to post a comment.

Источник