从零搭建自己的SpringBoot后台框架(二十)

桃扇骨 2023-08-17 15:33 88阅读 0赞

在实际工作中，有时候会需要将列表数据导出为Excel形式，方便打印，同事间传阅等，所以本章将添加将列表数据导出为Excel形式

### 一：添加POI依赖 ###

<dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi-ooxml</artifactId>
       <version>3.9</version>
    </dependency>

### 二：创建Excel实体类 ###

package com.example.demo.model;
    
    import java.io.Serializable;
    import java.util.List;
    
    public class ExcelData implements Serializable {
    
        private static final long serialVersionUID = 6133772627258154184L;
        /**
         * 表头
         */
        private List<String> titles;
    
        /**
         * 数据
         */
        private List<List<Object>> rows;
    
        /**
         * 页签名称
         */
        private String name;
    
        public List<String> getTitles() {
            return titles;
        }
    
        public void setTitles(List<String> titles) {
            this.titles = titles;
        }
    
        public List<List<Object>> getRows() {
            return rows;
        }
    
        public void setRows(List<List<Object>> rows) {
            this.rows = rows;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    }

### 三：创建表格工具类 ###

创建core→utils→ExcelUtils

package com.example.demo.core.utils;
    
    import com.example.demo.model.ExcelData;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    import org.apache.poi.xssf.usermodel.XSSFColor;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;
    
    import javax.servlet.http.HttpServletResponse;
    import java.awt.Color;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.util.List;
    
    public class ExcelUtils {
    
        /**
         * 使用浏览器选择路径下载
         * @param response
         * @param fileName
         * @param data
         * @throws Exception
         */
        public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "utf-8"));
            exportExcel(data, response.getOutputStream());
        }
    
        public static int generateExcel(ExcelData excelData, String path) throws Exception {
            File f = new File(path);
            FileOutputStream out = new FileOutputStream(f);
            return exportExcel(excelData, out);
        }
    
        private static int exportExcel(ExcelData data, OutputStream out) throws Exception {
            XSSFWorkbook wb = new XSSFWorkbook();
            int rowIndex = 0;
            try {
                String sheetName = data.getName();
                if (null == sheetName) {
                    sheetName = "Sheet1";
                }
                XSSFSheet sheet = wb.createSheet(sheetName);
                rowIndex = writeExcel(wb, sheet, data);
                wb.write(out);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                //此处需要关闭 wb 变量
                out.close();
            }
            return rowIndex;
        }
    
        private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
            int rowIndex = 0;
            writeTitlesToExcel(wb, sheet, data.getTitles());
            rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
            autoSizeColumns(sheet, data.getTitles().size() + 1);
            return rowIndex;
        }
    
        /**
         * 设置表头
         *
         * @param wb
         * @param sheet
         * @param titles
         * @return
         */
        private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
            int rowIndex = 0;
            int colIndex = 0;
            Font titleFont = wb.createFont();
            //设置字体
            titleFont.setFontName("simsun");
            //设置粗体
            titleFont.setBoldweight(Short.MAX_VALUE);
            //设置字号
            titleFont.setFontHeightInPoints((short) 14);
            //设置颜色
            titleFont.setColor(IndexedColors.BLACK.index);
            XSSFCellStyle titleStyle = wb.createCellStyle();
            //水平居中
            titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            //垂直居中
            titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            //设置图案颜色
            titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
            //设置图案样式
            titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            titleStyle.setFont(titleFont);
            setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
            Row titleRow = sheet.createRow(rowIndex);
            titleRow.setHeightInPoints(25);
            colIndex = 0;
            for (String field : titles) {
                Cell cell = titleRow.createCell(colIndex);
                cell.setCellValue(field);
                cell.setCellStyle(titleStyle);
                colIndex++;
            }
            rowIndex++;
            return rowIndex;
        }
    
        /**
         * 设置内容
         *
         * @param wb
         * @param sheet
         * @param rows
         * @param rowIndex
         * @return
         */
        private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
            int colIndex;
            Font dataFont = wb.createFont();
            dataFont.setFontName("simsun");
            dataFont.setFontHeightInPoints((short) 14);
            dataFont.setColor(IndexedColors.BLACK.index);
    
            XSSFCellStyle dataStyle = wb.createCellStyle();
            dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            dataStyle.setFont(dataFont);
            setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
            for (List<Object> rowData : rows) {
                Row dataRow = sheet.createRow(rowIndex);
                dataRow.setHeightInPoints(25);
                colIndex = 0;
                for (Object cellData : rowData) {
                    Cell cell = dataRow.createCell(colIndex);
                    if (cellData != null) {
                        cell.setCellValue(cellData.toString());
                    } else {
                        cell.setCellValue("");
                    }
                    cell.setCellStyle(dataStyle);
                    colIndex++;
                }
                rowIndex++;
            }
            return rowIndex;
        }
    
        /**
         * 自动调整列宽
         *
         * @param sheet
         * @param columnNumber
         */
        private static void autoSizeColumns(Sheet sheet, int columnNumber) {
            for (int i = 0; i < columnNumber; i++) {
                int orgWidth = sheet.getColumnWidth(i);
                sheet.autoSizeColumn(i, true);
                int newWidth = (int) (sheet.getColumnWidth(i) + 100);
                if (newWidth > orgWidth) {
                    sheet.setColumnWidth(i, newWidth);
                } else {
                    sheet.setColumnWidth(i, orgWidth);
                }
            }
        }
    
        /**
         * 设置边框
         *
         * @param style
         * @param border
         * @param color
         */
        private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
            style.setBorderTop(border);
            style.setBorderLeft(border);
            style.setBorderRight(border);
            style.setBorderBottom(border);
            style.setBorderColor(BorderSide.TOP, color);
            style.setBorderColor(BorderSide.LEFT, color);
            style.setBorderColor(BorderSide.RIGHT, color);
            style.setBorderColor(BorderSide.BOTTOM, color);
        }
    }

注意，这里封装了常用的属性，具体api大家可以自行百度

### 四：创建ExcelConstant ###

创建core→constant→ExcelConstant

package com.example.demo.core.constant;
    
    public class ExcelConstant {
    
        /**
         * 生成文件存放路径
         */
        public static final String FILE_PATH = "C:\\Users\\Administrator\\Desktop\\";
    
        /**
         * 表格默认名称
         */
        public static final String FILE_NAME = "TEST.xls";
    }

### 五：创建ExcelController ###

package com.example.demo.controller;
    
    import com.example.demo.core.aop.AnnotationLog;
    import com.example.demo.core.constant.ExcelConstant;
    import com.example.demo.core.ret.RetResponse;
    import com.example.demo.core.ret.RetResult;
    import com.example.demo.core.ret.ServiceException;
    import com.example.demo.core.utils.ExcelUtils;
    import com.example.demo.model.ExcelData;
    import com.example.demo.model.UserInfo;
    import com.example.demo.service.UserInfoService;
    import com.github.pagehelper.PageHelper;
    import com.github.pagehelper.PageInfo;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiImplicitParam;
    import io.swagger.annotations.ApiImplicitParams;
    import io.swagger.annotations.ApiOperation;
    import org.apache.shiro.SecurityUtils;
    import org.apache.shiro.authc.IncorrectCredentialsException;
    import org.apache.shiro.authc.UsernamePasswordToken;
    import org.apache.shiro.subject.Subject;
    import org.springframework.web.bind.annotation.*;
    
    import javax.annotation.Resource;
    import javax.servlet.http.HttpServletResponse;
    import java.util.ArrayList;
    import java.util.List;
    
    @RestController
    @RequestMapping("excel")
    public class ExcelController {
    
        @Resource
        private UserInfoService userInfoService;
    
        @PostMapping("/test")
        public  RetResult<Integer> test(){
            int rowIndex = 0;
            List<UserInfo> list = userInfoService.selectAlla(0, 0);
            ExcelData data = new ExcelData();
            data.setName("hello");
            List<String> titles = new ArrayList();
            titles.add("ID");
            titles.add("userName");
            titles.add("password");
            data.setTitles(titles);
    
            List<List<Object>> rows = new ArrayList();
            for(int i = 0, length = list.size();i<length;i++){
                UserInfo userInfo = list.get(i);
                List<Object> row = new ArrayList();
                row.add(userInfo.getId());
                row.add(userInfo.getUserName());
                row.add(userInfo.getPassword());
                rows.add(row);
            }
            data.setRows(rows);
            try{
                rowIndex = ExcelUtils.generateExcel(data, ExcelConstant.FILE_PATH + ExcelConstant.FILE_NAME);
            }catch (Exception e){
                e.printStackTrace();
            }
            return RetResponse.makeOKRsp(Integer.valueOf(rowIndex));
        }
    
        @GetMapping("/test2")
        public void test2(HttpServletResponse response){
            int rowIndex = 0;
            List<UserInfo> list = userInfoService.selectAlla(0, 0);
            ExcelData data = new ExcelData();
            data.setName("hello");
            List<String> titles = new ArrayList();
            titles.add("ID");
            titles.add("userName");
            titles.add("password");
            data.setTitles(titles);
    
            List<List<Object>> rows = new ArrayList();
            for(int i = 0, length = list.size();i<length;i++){
                UserInfo userInfo = list.get(i);
                List<Object> row = new ArrayList();
                row.add(userInfo.getId());
                row.add(userInfo.getUserName());
                row.add(userInfo.getPassword());
                rows.add(row);
            }
            data.setRows(rows);
            try{
                ExcelUtils.exportExcel(response,"test2",data);
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

### 六：添加接口权限 ###

INSERT INTO `sys_permission_init` VALUES ('7', '/excel/test', 'anon', '7');
    INSERT INTO `sys_permission_init` VALUES ('8', '/excel/test2', 'anon', '8');

这里我们需要测试在浏览器中下载，所以我们先把接口设置为不登录也可以访问

### 七：测试 ###

输入localhost:8080/excel/test

![format_png][]

打开桌面的TEST.xls

在浏览器中输入localhost:8080/excel/test2

![format_png 1][]

出现如图所示页面，打开文件，数据与TEST.xls一致

[format_png]: /images/20230810/22e32690e60d40f98d3eeab478ac0f66.png
[format_png 1]: /images/20230810/0b3edce27df34ba9a55db5b66a0c0c72.png