SpringBoot项目如何用java将数据导出为Excel文件（提供全部源码）

WziH_CSDN

已于 2022-06-20 15:43:26 修改

阅读量1.8k

点赞数 6

文章标签： java poi excel

于 2020-11-19 17:49:01 首次发布

本文链接：https://blog.csdn.net/WziH_CSDN/article/details/109819066

版权

本文详细介绍了如何在SpringBoot项目中利用Java的POI库将数据导出为Excel文件。步骤包括导入相关依赖，添加必要类，编写Controller层代码，特别是设置表头和数据映射的关键代码段，最后说明通过接口即可生成Excel表格。

摘要由CSDN通过智能技术生成

第一步：导入依赖

<!--数据导出依赖 excel-->
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <!--数据导出依赖 End excel-->

第二步：复制我的这两个类到项目中

package com.***.utils;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;

/**
 *
 * @ClassName: ExcelUtil
 * @Description: Excel工具类
 * @author huangxh
 * @date 2016年10月19日
 *
 */
public class ExcelUtil {
   
    public static final String CONTEXTPATH = "dqam\\";
    public static final String XLSX = ".xlsx";

    /**
     * 获取导出excel的路径和文件名
     *
     * @param request
     * @param fileName
     * @return
     */
    public static String getExportExcelFilePath(HttpServletRequest request,
                                                String fileName) {
   
        if ("".equals(fileName) || fileName == null) {
   
            return "";
        } else {
   
            return request.getSession().getServletContext().getRealPath("/")
                    + CONTEXTPATH + fileName
                    + XLSX;
        }
    }

    /**
     * 创建XSSFWorkbook
     *
     * @param filePath
     * @return
     * @throws IOException
     */
    public static XSSFWorkbook getXSSFWorkbook(File file, String filePath)
            throws IOException {
   
        XSSFWorkbook workbook = null;
        if (!file.exists()) {
   
            workbook = new XSSFWorkbook();
        } else {
   
            workbook = new XSSFWorkbook(filePath);
        }
        return workbook;
    }

    /**
     * 保存Excel 2003报表到指定目录
     *
     * @param path
     *            指定保存目录
     * @param book
     *            报表文件
     * @param fileName
     *            报表名称
     * @throws IOException
     */
    public static void saveExcel2003ToTargetPath(String path,
                                                 HSSFWorkbook book, String fileName) throws IOException {
   
        File file = new File(path + fileName);
        OutputStream out = new FileOutputStream(file);
        book.write(out);
        out.flush();
        out.close();
    }

    /**
     * 保存Excel 2003报表到指定目录
     *
     * @param path
     *            指定保存目录
     * @param book
     *            报表文件
     * @param fileName
     *            报表名称
     * @throws IOException
     */
    public static void saveExcel2007ToTargetPath(String path,
                                                 XSSFWorkbook book, String fileName) throws IOException {
   
        File file = new File(path + "/" + fileName);
        OutputStream out = new FileOutputStream(file);
        book.write(out);
        out.flush();
        out.close();
    }

    /**
     *
     * 动态组装Excel 2003 Sheet
     *
     * @param book
     *            Excel报表对象
     * @param dataSet
     *            报表数据，不包含表头
     * @param header
     *            报表表头
     * @param sheetName
     *            sheet名称
     */
    public static void saveSheet2003DataToExcel(HSSFWorkbook book,
                                                List<HashMap<String, Object>> dataSet, String[] header,
                                                String sheetName) {
   
        if (header == null || header.length <= 0) {
   
            return;
        }
        HSSFSheet sheet = book.createSheet(sheetName);
        Map<String, HSSFCellStyle> styleMap = getMyHssFcellStyle(book);
        HSSFRow titlerow = sheet.createRow(0);
        for (int i = 0; i < header.length; i++) {
   // 创建Sheet头
            HSSFCell cell = titlerow.createCell(i);
            cell.setCellValue(header[i]);
            cell.setCellStyle(styleMap.get("defaultStyle"));// 设置样式
            // 添加表头样式
        }

        if (dataSet == null || dataSet.isEmpty()) {
   
            return;
        }
        for (int i = 0; i < dataSet.size(); i++) {
   // 插入Sheet数据
            Map<String, Object> datamap = dataSet.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < header.length; j++) {
   // 创建Sheet头
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(String.valueOf(datamap.get(header[j])));
                // 添加单元格表样式

            }
        }
        for (int i = 0; i < header.length; i++) {
   
            sheet.setColumnWidth(i, 5500);// 宽
        }

    }

    /**
     * 保存数据到Excel 2003,并存放到指定目录,包含数字格式化千分符 2015年1月12日10:05:20 在原有基础上添加默认方式
     *
     * @param dataSet
     *            报表数据，不包含表头
     * @param header
     *            报表表头
     */
    public static void saveToExcelFX(HSSFWorkbook book,
                                     List<HashMap<String, Object>> dataSet, String[] header,
                                     String sheetName, String styleType) {
   
        if (header == null || header.length <= 0) {
   
            return;
        }
        HSSFSheet sheet = book.createSheet(sheetName);
        HSSFRow titlerow = sheet.createRow(0);
        Map<String, HSSFCellStyle> styleMap = getMyHssFcellStyle(book);
        for (int i = 0; i < header.length; i++