Upload and Display Excel Files with Spring MVC – 用Spring MVC上传和显示Excel文件

最后修改: 2017年 2月 20日

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.

在本文中,我们将演示如何使用Spring MVC框架上传Excel文件并在网页中显示其内容

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;

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) {
    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:

接下来,让我们创建一个JSP文件,其中包含一个type fileinput,其accept属性将被设置为只允许Excel文件。

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

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.

为了解析上传的excel文件,我们将使用Apache POI库,它可以处理.xls.xlsx文件。

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.

.xls文件在Apache POI库中由HSSFWorkbook表示,它是由HSSFSheet对象组成的。关于打开和读取.xls文件的内容,您可以查看我们关于在Java中使用Microsoft Excel的文章

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 对象,它可以帮助我们确定背景颜色和字体等属性。所有读取的属性将被设置在MyCell对象的属性中。

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.

颜色是以rgb(rVal, gVal, bVal)格式读取的,以便在JSP页面中使用CSS显示它们。

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


myCell.setTextSize(font.getFontHeightInPoints() + "");
if (font.getBold()) {
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.

对于较新的.xlsx格式的文件,我们可以使用XSSFWorkbook和类似的来处理工作簿中的内容,这在在Java中使用Microsoft Excel文章中也有记载。

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();
      + (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()) {
XSSFColor textColor = font.getXSSFColor();
if (textColor != null) {
    byte[] rgbColor = textColor.getRGB();
      + (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()

  .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.

为了显示使用Spring MVC读取的Excel文件,我们将需要定义一个控制器映射和JSP页面。

4.1. Spring MVC Controller

4.1.Spring MVC控制器</strong

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:

为了直观地显示文件的内容,我们将创建一个HTML table,并在每个表格单元的style属性中,添加与Excel文件中每个单元对应的格式化属性。

<c:if test="${not empty data}">
    <table style="border: 1px solid black; border-collapse: collapse;">
        <c:forEach items="${data}" var="row">
                <c:forEach items="${row.value}" var="cell">
                    <td style="border:1px solid black;height:20px;width:100px;

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.

在这篇文章中,我们展示了一个使用Spring MVC框架上传Excel文件并在网页中显示的示例项目。

The full source code can be found in the GitHub project.