You might know Spring - one of the most awesome frameworks on earth. But what is Apache POI? It’s the framework from the Apache Software Foundation which helps you to create Microsoft formats like Word, PowerPoint, and - among others - Excel. Personally speaking, I believe Excel is the most important product Microsoft ever made. You may ask what about Google Sheets or Apples Numbers. But when it comes to enterprises, almost everybody uses Excel.
When I wrote Time & Bill it was very clear my customers would need to export their work time and the format itself was also clear: Excel.
Dealing with Excel in Java is very simple, thanks to Apache POI. However, Apache POI is not an official library from Microsoft. In fact, it’s almost reengineering the Microsoft Excel format, at least the old binary formats. The new formats are XML based and do not need such reengineering.
Of course, with the flexibility now offered for XML and other open formats, creating Excel files has become a lot easier.
Thus I am using the modern Excel formats even if that means that some very old Excel installations are left out.
First, let’s look at the code to create the workbook:
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("My Sheet");
sheet.setColumnWidth(0, 2560);
sheet.setColumnWidth(1, 2560);
A workbook is basically your Excel sheet. You could choose between XSSFWorkbook and HSSFWorkbook. XSSF uses the SpreadsheetML (ML for markup language) which is open to read and uses the XSLX file format. HSSF will create a binary format and is associated with the XSL file format.
Once we have the workbook ready, we need to create a sheet. You can have more than one, just as you would in a regular Excel sheet. Secondly, I set the column width for this sheet. You may ask why I used 2560. The reason is, each character in a column is divided into 256 fractions in excel. 128 fractions would be half a character width. So 2560 is 10 characters wide.
The next part of this code is to create rows and set values:
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Hello");
...
We use the sheet object to create a row and assign it to a new Row object. Then you can create a cell and call setCellValue to actually set values. You can do this for as many cells as you like.
Remember to pass the row and cell ID in the respective methods to avoid overriding the earlier rows and cells.
You can also take advantage of the built-in Cell Formats of Excel by setting the type of value you wish to set as follows:
cell.setCellType(CellType.STRING);
It is a little bit more complicated when showing times which have a specific format. It is not possible to do this with just Cell formatting. I have written a handy helper method just for this scenario:
private static CellStyle createTimeCellStyle(Workbook wb) {
CreationHelper createHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(
createHelper.createDataFormat().getFormat("[h]:mm:ss"));
return cellStyle;
}
As you can probably see, we are using the workbook (wb) to get hold of some kind of CreationHelper object. We can create a custom style for our new cell with this method.
Then, we can use it like this:
double value = (1.0 / 86400.0) * durationInSeconds;
Cell cell = row.createCell(index);
cell.setCellStyle(createTimeCellStyle(workbook));
cell.setCellValue(value);
Wow, that formula right? Well, it’s not that bad. This is again a fraction: 1 day divided by the 86400 seconds of that day. Multiply it by the number seconds you want to show. For some reason this value needs to be a double. With the cellstyle created above you can show time values, or even durations longer than 24 hours.
Lastly, you can also use cellstyle to become creative with Excel:
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setFillBackgroundColor(
HSSFColor.HSSFColorPredefined.YELLOW.getIndex()
);
I’m sure you can be really creative with your Excel sheets. But keep in mind that some definitions like this color come from the old binary Excel codebase. This should not bother you too much.
So now that you have made a great looking Excel sheet, let’s see how you can save it to disk:
File file = new File("/Users/Jane/excel.xlsx");
file.createNewFile();
FileOutputStream outputStream = new FileOutputStream(file, false);
workbook.write(outputStream);
I admit, this code is very simple and you should certainly improve it quite a bit more. Some obvious things are closing your streams and proper error handling. But I’m sure it shows you how easy it is to create Excel files with Java and Apache POI.
Simply, this code calls the write method on your Workbook object and gives it an OutputStream, that’s it.
Please keep in mind that we are constructing our Excel sheet in RAM. This may be a problem when you create very large Excel sheets.
Now that you know how to create Excel sheets with Java, you should already be able to create Excel sheets using Spring.
With Spring Boot, creating a service which returns an Excel stream is very trivial.
@GetMapping("/my/excel")
public ResponseEntity<StreamingResponseBody> excel() {
Workbook workBook = new XSSFWorkbook();
Sheet sheet = workBook.createSheet("My Sheet");
sheet.setColumnWidth(0, 2560);
sheet.setColumnWidth(1, 2560);
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("Hello World");
return ResponseEntity
.ok()
.contentType(MediaType.APPLICATION_OCTET_STREAM)
.header(HttpHeaders.CONTENT_DISPOSITION, "inline;filename=\"myfilename.xlsx\"")
.body(workBook::write)
}
The trick is to return a ResponseBobody of the type StreamingResponseBody. This lets Spring know that it needs to return some kind of a stream. Despite us creating the whole Excel file in memory (as to my knowledge, streaming is not possible), we can stream back the result. This will make clients happy, as users will be see a “save as” dialog as the result.
At the end of the method, the ResponseEntity builder object is used. It has a nice, fluent API to create the result: ok() refers to the status code (200). The content type is a HTTP header that tells the browser what to expect. In our case, there’s nothing for the browser to display, but rather make it available for download. Another header, called “content disposition” can be used to propose a filename to browsers.
Finally, we can use the body() method to return a body. In our code, it is the method reference to the workBook’s write method, which takes and OutputStream.
As you can imagine, Spring helps you here since it knows we want a “StreamingResponseBody”.
And that’s it for creating your Java Spring Boot application for generating Excel files.
All good programmers test their code. I know, it’s hard, but our Excel Sheet is no exception. And that’s no simple matter. If you have already read the excellent book from Kent Beck called Extreme Programming, you’ll know exactly what I mean.
This is the most common way of doing it and our common base for a test case:
@RunWith(SpringRunner.class)
@SpringBootTest
@WebAppConfiguration
public class ExcelTest {
@Test
public void testMyExcel() {
// code follows here
}
}
We are configuring JUnit to run specifically with Spring. Also, we are asking Spring to start a web application context in the background.
However, we also get hold of our context and initialize something called the MockMvc. It basically creates a mock web environment.
@Autowired
private WebApplicationContext wac;
private MockMvc mockMvc;
@Before
public void setUpAbstractDbTest() {
this.mockMvc = MockMvcBuilders.webAppContextSetup(this.wac).build();
}
It’s kind of a routine. It makes Spring inject our test case with a web application context. Then, in the @Before method, which runs before all test methods, we can initialize the MockMvc with the context created by Spring.
Once done, we are ready to implement our test method and send requests to the MockMVC framework.
We start by sending a web request to our mock web application.
MvcResult mvcResult = this.mockMvc.perform(
MockMvcRequestBuilders.get("/my/excel")
)
.andDo(ResultHandler.print())
.andExpect(StatusResultMatcher.status().isOk())
.andExpect(RequestResultMatcher.request().asyncStarted())
.andReturn();
The MockMvc class is built for testing. So it has additional methods to do basic checks on the result. As an example, we can print some parts of the result and check if the status is ok. Most importantly, if an asynchronous process has been started, which is the case when streaming like we do to output the Excel file.
You probably already figured out that there is a problem. How do we compare excel sheets, when we “somehow” have to wait until we get the content? The code above does not have an actual result, it just starts the stream.
To get hold of the actual content, we have to do something like this:
MvcResult result =
this.mockMvc
.perform(RequestBuilder.asyncDispatch(mvcResult))
.andReturn();
byte[] workbookBytes =
result.getResponse().getContentAsByteArray();
In the code above we are actually waiting for the request to be processed and the result to be returned from server. Eventually, we can read the content, which is, first of all, an array of bytes. sigh Of course it is!
But now that we have that content, we can bring it back to excel and compare it.
This is how it will look in code:
Workbook actual = new XSSFWorkbook(new ByteArrayInputStream(workbookBytes));
String actualValue = actual
.getSheetAt(0)
.getRow(0)
.getCell(0)
.getStringCellValue();
assertEquals("Hello World");
As you can see, we can iterate through all rows and sheets in the excel file. I’m cheating here by not really doing a comparison. You can write a little more code and actually read an excel file from disk to compare it to the one you get from Spring.
So as you can see, it’s not that difficult to generate Excel files. You just need to know some POI magic and of course the StreamingResponseBody from by Spring. In addition, with some MockMvc magic you can easily write tested code, even when streaming and when Excel is involved - so no more excuses.