Upload and Display Excel Files with Spring MVC

1. Introduction

In this article, we will demonstrate how to upload Excel files and display their content in a web page using the Spring MVC framework.

2. Uploading Excel Files

In order to be able to upload files, we will first create a controller mapping that receives a MultipartFile and saves it in the current location:

private String fileLocation;

@PostMapping("/uploadExcelFile")
public String uploadFile(Model model, MultipartFile file) throws IOException {
    InputStream in = file.getInputStream();
    File currDir = new File(".");
    String path = currDir.getAbsolutePath();
    fileLocation = path.substring(0, path.length() - 1) + file.getOriginalFilename();
    FileOutputStream f = new FileOutputStream(fileLocation);
    int ch = 0;
    while ((ch = in.read()) != -1) {
        f.write(ch);
    }
    f.flush();
    f.close();
    model.addAttribute("message", "File: " + file.getOriginalFilename() 
      + " has been uploaded successfully!");
    return "excel";
}

Next, let’s create a JSP file with a form that contains an input of type file which will have the accept attribute set to only allow Excel files:

<c:url value="/uploadExcelFile" var="uploadFileUrl" />
<form method="post" enctype="multipart/form-data"
  action="${uploadFileUrl}">
    <input type="file" name="file" accept=".xls,.xlsx" /> <input
      type="submit" value="Upload file" />
</form>

3. Reading Excel Files

In order to parse the uploaded excel file, we will use the Apache POI library, which can work with both .xls and .xlsx files.

Let’s create a helper class called MyCell which will contain properties of an Excel cell related to content and formatting:

public class MyCell {
    private String content;
    private String textColor;
    private String bgColor;
    private String textSize;
    private String textWeight;

    public MyCell(String content) {
        this.content = content;
    }
    
    //standard constructor, getters, setters
}

We will read the content of the Excel file into a Map that contains lists of MyCell objects.

3.1. Parsing a .xls File

A .xls file is represented in the Apache POI library by an HSSFWorkbook class, which is made up of HSSFSheet objects. For opening and reading the content of a .xls file, you can view our article on Working with Microsoft Excel in Java.

For parsing the formatting of a cell, we will obtain the HSSFCellStyle object, which can help us determine properties like the background color and font. All the read properties will be set in the attributes of the MyCell object:

HSSFCellStyle cellStyle = cell.getCellStyle();

MyCell myCell = new MyCell();

HSSFColor bgColor = cellStyle.getFillForegroundColorColor();
if (bgColor != null) {
    short[] rgbColor = bgColor.getTriplet();
    myCell.setBgColor("rgb(" + rgbColor[0] + ","
      + rgbColor[1] + "," + rgbColor[2] + ")");
    }
HSSFFont font = cell.getCellStyle().getFont(workbook);

The colors are read in an rgb(rVal, gVal, bVal) format to make it easier to display them using CSS in a JSP page.

Let’s also obtain the font size, weight, and color:

myCell.setTextSize(font.getFontHeightInPoints() + "");
if (font.getBold()) {
    myCell.setTextWeight("bold");
}
HSSFColor textColor = font.getHSSFColor(workbook);
if (textColor != null) {
    short[] rgbColor = textColor.getTriplet();
    myCell.setTextColor("rgb(" + rgbColor[0] + ","
      + rgbColor[1] + "," + rgbColor[2] + ")");
}

3.2. Parsing a .xlsx File

For files in the newer .xlsx format, we can use the XSSFWorkbook class and similar ones for the contents of a workbook, also documented in the Working with Microsoft Excel in Java article.

Let’s take a closer look at reading the formatting of a cell in the .xlsx format. First, we will retrieve the XSSFCellStyle object associated with a cell and use it to determine the background color and font:

XSSFCellStyle cellStyle = cell.getCellStyle();

MyCell myCell = new MyCell();
XSSFColor bgColor = cellStyle.getFillForegroundColorColor();
if (bgColor != null) {
    byte[] rgbColor = bgColor.getRGB();
    myCell.setBgColor("rgb(" 
      + (rgbColor[0] < 0 ? (rgbColor[0] + 0xff) : rgbColor[0]) + ","
      + (rgbColor[1] < 0 ? (rgbColor[1] + 0xff) : rgbColor[1]) + ","
      + (rgbColor[2] < 0 ? (rgbColor[2] + 0xff) : rgbColor[2]) + ")");
}
XSSFFont font = cellStyle.getFont();

In this case, the RGB values of the color will be signed byte values, so we will obtain the unsigned values by adding 0xff to the negative values.

Let’s also determine the properties of the font:

myCell.setTextSize(font.getFontHeightInPoints() + "");
if (font.getBold()) {
    myCell.setTextWeight("bold");
}
XSSFColor textColor = font.getXSSFColor();
if (textColor != null) {
    byte[] rgbColor = textColor.getRGB();
    myCell.setTextColor("rgb("
      + (rgbColor[0] < 0 ? (rgbColor[0] + 0xff) : rgbColor[0]) + "," 
      + (rgbColor[1] < 0 ? (rgbColor[1] + 0xff) : rgbColor[1]) + "," 
      + (rgbColor[2] < 0 ? (rgbColor[2] + 0xff) : rgbColor[2]) + ")");
}

3.3. Handling Empty Rows

The methods described above do not account for empty rows in an Excel file. If we want a faithful rendition of the file that displays the empty rows as well, we will need to simulate these in our resulting HashMap with an ArrayList of MyCell objects containing empty Strings as content.

Initially, after reading the Excel file, the empty rows in the file will be ArrayList objects with a size of 0.

In order to determine how many empty String objects we should add, we will first determine the longest row in the Excel file, using the maxNrCols variable. Then we will add that number of empty String objects to all the lists in our HashMap that have a size of 0:

int maxNrCols = data.values().stream()
  .mapToInt(List::size)
  .max()
  .orElse(0);

data.values().stream()
  .filter(ls -> ls.size() < maxNrCols)
  .forEach(ls -> {
      IntStream.range(ls.size(), maxNrCols)
        .forEach(i -> ls.add(new MyCell("")));
  });

4. Displaying Excel Files

For displaying the Excel files read using Spring MVC, we will need to define a controller mapping and JSP page.

4.1. Spring MVC Controller

Let’s create a @RequestMapping method that will call the code above to read the content of the uploaded file, then add the returned Map as a Model attribute:

@Resource(name = "excelPOIHelper")
private ExcelPOIHelper excelPOIHelper;

@RequestMapping(method = RequestMethod.GET, value = "/readPOI")
public String readPOI(Model model) throws IOException {

  if (fileLocation != null) {
      if (fileLocation.endsWith(".xlsx") || fileLocation.endsWith(".xls")) {
          Map<Integer, List<MyCell>> data
            = excelPOIHelper.readExcel(fileLocation);
          model.addAttribute("data", data);
      } else {
          model.addAttribute("message", "Not a valid excel file!");
      }
  } else {
      model.addAttribute("message", "File missing! Please upload an excel file.");
  }
  return "excel";
}

4.2. JSP

For visually displaying the content of the file, we will create an HTML table and, in the style attribute of each table cell, add the formatting properties corresponding to each cell from the Excel file:

<c:if test="${not empty data}">
    <table style="border: 1px solid black; border-collapse: collapse;">
        <c:forEach items="${data}" var="row">
            <tr>
                <c:forEach items="${row.value}" var="cell">
                    <td style="border:1px solid black;height:20px;width:100px;
                      background-color:${cell.bgColor};color:${cell.textColor};
                      font-weight:${cell.textWeight};font-size:${cell.textSize}pt;">
                      ${cell.content}
                    </td>
                </c:forEach>
            </tr>
        </c:forEach>
    </table>
</c:if>

5. Conclusion

In this article, we have shown an example project for uploading Excel files and displaying them in a web page using the Spring MVC framework.