使用Spring Boot生成并下载包含下拉多选功能的Excel模板

时间：2024-07-09 07:14:40

步骤 1: 引入依赖

在pom.xml文件中添加EasyExcel

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.1</version>
        </dependency>

步骤2: 准备xlsm模板

首先，需要在Excel中创建一个包含下拉多选的模板，并保存为.xlsm格式。具体步骤如下：

  • 打开Excel，创建一个新的工作簿。
  • 在第二个Sheet（Sheet2）的第一列（A列）填充下拉列表的选项数据。
  • 在需要下拉多选的单元格区域，设置数据验证（Data Validation），使用来源（Source）指向Sheet2的选项数据范围。
  • 使用VBA代码实现多选功能。在Excel中按Alt + F11打开VBA编辑器，选择工作簿（ThisWorkbook），然后将以下代码粘贴进去：
Sub Worksheet_Change(ByVal Target As Range)
'让数据有效性选择 可以多选,不可重复
Dim rngDV As Range
Dim oldVal As String

Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing

Else
Application.EnableEvents = False
newVal = Target.Value
If Target.Column = 3 Then   '数字是你想要多选的列是多少，多个用or连接。
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Then
Else
If newVal = "" Then
Else '去除重复的字段
       If InStr(1, oldVal, newVal) <> 0 Then
          If InStr(1, oldVal, newVal) + Len(newVal) - 1 = Len(oldVal) Then '最后一个选项重复
            Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
          Else
            Target.Value = Replace(oldVal, newVal & "、", "") '不是最后一个选项重复的时候处理逗号
          End If
        Else '不是重复选项就视同增加选项
Target.Value = oldVal _
& "、" & newVal '可以是任意符号隔开
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub

步骤 3: 编写Spring Boot控制器

使用EasyExcel读取模板，并结合Apache POI设置下拉选项。然后，通过Spring Boot控制器生成并下载Excel文件。

以下是完整的Spring Boot控制器示例：

import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.core.io.ClassPathResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

@RestController
public class ExcelController {

    @GetMapping("/download/template")
    public void downloadTemplate(HttpServletResponse response) throws IOException {
        try (InputStream inputStream = new ClassPathResource("templates/multiSelect.xlsm").getInputStream()) {
            // 动态下拉数据
            List<String> orgNameList = Arrays.asList("组1","组2");


            // 设置响应头
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            String format = DateUtil.format(new Date(), "yyyyMMddHHmmss");
            String fileName = URLEncoder.encode("test"+ format, "UTF-8");
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + fileName + ".xlsm");

            // 创建 ExcelWriter 并设置 SpinnerWriteHandler
            ExcelWriterBuilder writerBuilder = EasyExcel.write(response.getOutputStream())
                    .withTemplate(inputStream);

            // 写入空数据以应用模板和处理器
            try (ExcelWriter excelWriter = writerBuilder.build()) {
                // 写入第二个 Sheet，将 orgNameList 写入第一列
                WriteSheet secondSheet = EasyExcel.writerSheet(1).build();
                List<List<String>> data = orgNameList.stream()
                        .map(name -> {
                            List<String> row = new ArrayList<>();
                            row.add(name);
                            return row;
                        })
                        .collect(Collectors.toList());
                excelWriter.write(data, secondSheet);

                // 写入第一个 Sheet
                WriteSheet firstSheet = EasyExcel.writerSheet(0).build();
                excelWriter.write(new ArrayList<>(), firstSheet); // 提供一个空列表以避免歧义
            }

        } catch (IOException e) {
            throw new RuntimeException("Failed to download the template", e);
        }
    }
}

效果图

源码下载地址: katriinatavi/springboot-excel

相关文章