Spring Boot 导出Excel表格
添加支持
<!--添加导入/出表格依赖-->

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>3.9</version>

</dependency>
新建Excel实体类
import java.io.Serializable;

import java.util.List;

public class ExcelData implements Serializable {

    private static final long serialVersionUID = 4444017239100620999L;

    // 表头

    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;

    }

}
 
添加excel工具类
import javax.servlet.http.HttpServletResponse;

import java.awt.*;

import java.io.OutputStream;

import java.util.List;

import com.soft.ssmproject.entity.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 java.awt.Color;

import java.net.URLEncoder;

public class ExcelUtils {

    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, "utf-8"));

        exportExcel(data, response.getOutputStream());

    }

    public static void exportExcel(ExcelData data, OutputStream out) throws Exception {

        XSSFWorkbook wb = new XSSFWorkbook();

        try {

            String sheetName = data.getName();

            if (null == sheetName) {

                sheetName = "Sheet1";

            }

            XSSFSheet sheet = wb.createSheet(sheetName);

            writeExcel(wb, sheet, data);

            wb.write(out);

        } catch(Exception e){

            e.printStackTrace();

        }finally{

            //此处需要关闭 wb 变量

            out.close();

        }

    }

    private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {

        int rowIndex = 0;

        rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());

        writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);

        autoSizeColumns(sheet, data.getTitles().size() + 1);

    }

    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;

    }

    private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {

        int colIndex = 0;

        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;

    }

    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);

            }

        }

    }

    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);

    }

}
# controller层
import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import com.soft.ssmproject.entity.ExcelData;

import com.soft.ssmproject.entity.ExcelInfo;

import com.soft.ssmproject.tool.ExcelUtils;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.RestController;

@RestController

@RequestMapping("/excel")

public class ExcelController {

     @RequestMapping(value = "/export", method = RequestMethod.POST)

        public void excel(HttpServletResponse response,ExcelInfo excelInfo) throws Exception {

            ExcelData data = new ExcelData();

            data.setName("用户信息数据");

            //添加表头

            List<String> titles = new ArrayList();

            //for(String title: excelInfo.getNames())

            titles.add(excelInfo.getNames()[0]);

            titles.add(excelInfo.getAccount()[0]);

            titles.add(excelInfo.getDept()[0]);

            titles.add(excelInfo.getGender()[0]);

            titles.add(excelInfo.getEmail()[0]);

            data.setTitles(titles);

            //添加列

            List<List<Object>> rows = new ArrayList();

            List<Object> row = null;

           for(int i=1; i<excelInfo.getNames().length;i++){

               row=new ArrayList();

               row.add(excelInfo.getNames()[i]);

               row.add(excelInfo.getAccount()[i]);

               row.add(excelInfo.getDept()[i]);

               row.add(excelInfo.getGender()[i]);

               row.add(excelInfo.getEmail()[i]);

               rows.add(row);

           }

         data.setRows(rows);

            SimpleDateFormat fdate=new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");

            String fileName=fdate.format(new Date())+".xls";

            ExcelUtils.exportExcel(response, fileName, data);

        }

}

转子：https://blog.csdn.net/Cool_breeze_Rainy/article/details/80572308

最新文章

  1. Server Tomcat v7.0 Server at localhost was unable to start within 45 seconds
  2. PyQt5应用与实践
  3. wamp服务下部署禅道或其它项目时访问缓慢的解决办法
  4. 基于XMPP协议的Android即时通信系
  5. MySQL创建数据库并赋予权限
  6. C8051 SMBus 原理
  7. 弹出层和ajax数据交互
  8. (转帖)BootStrap入门教程 (二)
  9. label添加手势（触摸改变其背景颜色）
  10. linux 系统常用命令
  11. [转载]DIV CSS设计时IE6、IE7、FF 与兼容性有关的特性
  12. MyBatis Oracle批量更新
  13. JSU省赛队员选拔赛个人赛1（Coin Change、Fibbonacci Number、Max Num、单词数、无限的路、叠筐）
  14. 扩展 Windows Azure 运营能力 – 巴西
  15. logistic回归 c++ 实现
  16. Less的!important关键字
  17. 561. Array Partition I
  18. 【一天一道LeetCode】#59. Spiral Matrix II
  19. 设计模式 | 模板方法模式（template method）
  20. U盘制作微pe工具箱（实战）

热门文章

  1. UVA1194 Machine Schedule
  2. 使用WPF动态显示CPU使用率
  3. HDOJ 3308 LCIS （线段树）
  4. DAC杂谈一
  5. Python3学习笔记03-基础数据类型
  6. 微信小程序-WebSocket应用
  7. PHP导出MySQL数据字典 Summer-Mysql-Dic
  8. kali sudo apt install 无法定位软件包
  9. Uva 11178 Morley定理
  10. 21)django-csrf（跨站请求伪造）