自定义excel通过poi导出下载

╰半橙微兮° 2024-03-17 15:12 104阅读 0赞

### 1：配置pom下的依赖 ###

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.1.1</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-web</artifactId>
                <version>5.3.23</version>
            </dependency>
            <dependency>
                <groupId>org.apache.tomcat.embed</groupId>
                <artifactId>tomcat-embed-core</artifactId>
                <version>9.0.68</version>
            </dependency>

### 2：编写请求下载类 ###

package com.example.poi;
    
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    /**
     * @Author xu
     * @create 2023/7/5 01
     */
    
    @RequestMapping("/custom")
    public class Demo {
        
        @GetMapping("/export")
        public void exportExcel(HttpServletResponse response) {
        
    
            List<Map<String, Object>> studentList = new ArrayList<>();
            Map<String, Object> student1 = new HashMap<>();
            student1.put("姓名", "张三");
            student1.put("年龄", 18);
            student1.put("成绩", 99);
    
            Map<String, Object> student2 = new HashMap<>();
            student2.put("姓名", "李四");
            student2.put("年龄", 20);
            student2.put("成绩", 100);
    
            studentList.add(student1);
            studentList.add(student2);
    
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("学生信息");
    
    
            // 创建标题行
            Row titleRow = sheet.createRow(0);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellValue("这是标题");
    
            // 合并标题行的单元格，重点这里的参数，可以选择合并的情况，然后再设置字体样式都可以
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 2));
    
            // 设置标题样式
            CellStyle titleStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            titleStyle.setFont(font);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleCell.setCellStyle(titleStyle);
    
            // 写入表头
            Row headerRow = sheet.createRow(1);
            int columnIndex = 0;
            for (String key : studentList.get(0).keySet()) {
        
                Cell cell = headerRow.createCell(columnIndex++);
                cell.setCellValue(key);
            }
    
            // 写入数据
            int rowIndex = 1;
            for (Map<String, Object> student : studentList) {
        
                Row dataRow = sheet.createRow(rowIndex++);
                columnIndex = 0;
                for (Object value : student.values()) {
        
                    Cell cell = dataRow.createCell(columnIndex++);
                    cell.setCellValue(value.toString());
                }
            }
    
            try {
        
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("学生信息表12.xlsx", "UTF-8"));
                workbook.write(response.getOutputStream());
                workbook.close();
            } catch (
                    IOException e) {
        
                e.printStackTrace();
            }
        }
    }