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

最后修改: 2017年 2月 20日

中文/混合/英文(键盘快捷键：t)

1. Introduction

1.介绍

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

2.上传Excel文件

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:

为了能够上传文件，我们将首先创建一个控制器映射，接收一个MultipartFile并将其保存在当前位置。

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:

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

<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

3.阅读Excel文件

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:

让我们创建一个名为MyCell的辅助类，它将包含与内容和格式有关的Excel单元格的属性。

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.

我们将把Excel文件的内容读入一个Map，其中包含MyCell对象的列表。

3.1. Parsing a .xls File

3.1.解析一个.xls文件

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

3.2.解析一个.xlsx文件

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:

让我们仔细看看如何读取.xlsx格式中的单元格的格式。首先，我们将检索与单元格相关的XSSFCellStyle对象，并使用它来确定背景颜色和字体。

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.

在这种情况下，颜色的RGB值将是有符号的字节值，所以我们将通过在负值上加上0xff来获得无符号值。

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

3.3.处理空行

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.

上述方法没有考虑到Excel文件中的空行。如果我们想要忠实地再现文件，同时显示空行，我们将需要在我们生成的HashMap中用包含空Strings作为内容的ArrayList对象模拟这些空行。

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

最初，在读取Excel文件后，文件中的空行将是ArrayList对象，大小为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:

为了确定我们应该添加多少个空String对象，我们将首先使用maxNrCols变量确定Excel文件中最长的一行。然后我们将在HashMap中所有大小为0的列表中添加该数量的空String对象。

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

4.显示Excel文件

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控制器

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:

让我们创建一个@RequestMapping方法，它将调用上面的代码来读取上传文件的内容，然后将返回的Map作为Model属性。

@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

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

5.结论

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.

完整的源代码可以在GitHub项目中找到。