Java使用Apache的poi实现Excel导出

淡淡的烟草味﹌ 2022-04-12 13:38 321阅读 0赞

一.导包org.apache.poi

<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi</artifactId>
    			<version>3.16</version>
    		</dependency>

二.实现controller （本次环境是springboot）

package com.example.excelcontroller;
    
    import com.example.excelservice.ExcelService;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.OutputStream;
    
    /**
     * 导出Excel的controller
     *
     * @author liucong
     */
    @RestController
    @RequestMapping("/acr")
    public class ExcelController {
    
        @Autowired
        private ExcelService excelService;
    
        @RequestMapping("export_excel")
        public void exportExcel(HttpServletResponse response) {
            //设置导出格式
            response.setContentType("application/vnd.ms-excel;chartset=utf-8");
            //attachment 作为附件下载 || inline 在线打开
            response.setHeader("Content-Disposition", "attachment;filename=test.xls");
            OutputStream outputStream = null;
            try {
                HSSFWorkbook wb = excelService.exportExcelStudent();
                outputStream = response.getOutputStream();
                wb.write(outputStream);
                //清空内存中的缓冲区数据
                outputStream.flush();
                //关闭流
                outputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

三.service的实现类

package com.example.excelservice.impl;
    
    import com.example.excelservice.ExcelService;
    import com.example.param.Student;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.FillPatternType;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.springframework.stereotype.Service;
    
    import java.util.LinkedList;
    import java.util.List;
    
    /**
     * Create By Liucong
     * @author liucong
     */
    @Service
    public class ExcelServiceImpl implements ExcelService {
    
        @Override
        public HSSFWorkbook exportExcelStudent() {
            //1.创建工作薄
            HSSFWorkbook wb = new HSSFWorkbook();
            //2.创建表
            HSSFSheet sheet = wb.createSheet("sheet1");
            //2.1设置列宽(必须先设置列宽然后设置行高，不然列宽没有效果)
            sheet.setDefaultColumnWidth(100);
            //2.2设置列高
            sheet.setDefaultRowHeight((short) 100);
            //3.创建行
            HSSFRow row = sheet.createRow(0);
            //4.生成样式
            HSSFCellStyle style = wb.createCellStyle();
            //4.1设置水平居中
            style.setAlignment(HorizontalAlignment.CENTER);
            //4.2设置垂直居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            //4.3设置背景颜色
            style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.ROYAL_BLUE.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.ROYAL_BLUE.getIndex());
            //4.4设置边框
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            //5 设置字体
            HSSFFont font = wb.createFont();
            font.setFontHeightInPoints((short) 10);
            font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
            font.setBold(true);
            font.setFontName("宋体");
            //6.把字体给当前样式
            style.setFont(font);
            //7.设置表头
            String[] excelHeader = {"姓名", "学号", "年龄", "性别"};
            for (int i = 0; i < excelHeader.length; i++) {
                //当前行(第0行)的第i列
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(excelHeader[i]);
                cell.setCellStyle(style);
            }
            //设置假数据
            Student student1 = new Student("张三", "201841030101", 18, "男");
            Student student2 = new Student("李思", "201841030102", 10, "女");
            Student student3 = new Student("王舞", "201841030103", 33, "不男不女");
            List<Student> list = new LinkedList<>();
            list.add(student1);
            list.add(student2);
            list.add(student3);
            //添加数据
            for(int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue(list.get(i).getName());
                row.createCell(0).setCellValue(list.get(i).getId());
                row.createCell(0).setCellValue(list.get(i).getAge());
                row.createCell(0).setCellValue(list.get(i).getSex());
            }
            return wb;
        }
    }

四.实体

package com.example.param;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    
    /**
     * 学生实体
     *
     * @author liucong
     */
    @Data
    @AllArgsConstructor
    public class Student {
        private String name;
        private String id;
        private Integer age;
        private String sex;
    }

五.使用postman进行测试