hutool 导入导出

本文由 简悦 SimpRead 转码， 原文地址 blog.csdn.net

hutool 导入导出，java 使用 hutool 导入导出

• 1、导入依赖
• • 测试实体类
  • 导入导出逻辑处理
  • 大数据导出

本人操作使用了

lombok

和

swagger2

，没有使用这两个依赖 jar 包的，可以删除依赖包和文件中的使用地方。

导入模板
![][img-0]
导出结果：

1、导入依赖

pom 文件中添加依赖，版本越高越好，不然部分方法不能用

 <!--hutool工具包-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.16</version>
        </dependency>
        <!--引入poi-ooxml-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
 <!--lombok依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.6</version>
            <scope>provided</scope>
        </dependency>
<!-- swagger自动生成API文档支持  可以不用他们 -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
        <!-- 引入第三方的swaggerUI -->
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>swagger-bootstrap-ui</artifactId>
            <version>1.9.6</version>
        </dependency>

测试实体类

实体类省略了 get 和 set 方法，使用了lombok简略实体类开发

1. XhjkMarketContactWay.java

package com.demo.model;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.Date;

/**
 * <p>
 * 市场小清单联系方式表
 * </p>
 *
 * @author ${author}
 * @since 2021-11-17
 */
@Data
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="XhjkMarketContactWay对象", description="市场小清单联系方式表")
public class XhjkMarketContactWay implements Serializable {

    private static final long serialVersionUID=1L;

    @ApiModelProperty(value = "市场小清单联系方式表主键")
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    @ApiModelProperty(value = "市场信息清单类型（1：工业小清单 2：商业公司小清单 3：药事服务商小清单 4：终端药店小清单）")
    private Integer listType;

    @ApiModelProperty(value = "清单联系信息ID（工业：工业名称ID，商业：商业公司名称ID，药事服务商：药师服务商名称ID，终端：终端名称ID）")
    private String contactId;

    @ApiModelProperty(value = "清单联系信息名称（工业：工业名称，商业：商业公司名称，药事服务商：药师服务商名称，终端：终端名称）")
    private String contactName;

    @ApiModelProperty(value = "联系人姓名")
    private String userContactName;

    @ApiModelProperty(value = "联系人电话")
    private String userContactPhone;

    @ApiModelProperty(value = "联系地址")
    private String contactAddress;

    @ApiModelProperty(value = "市场调研人员姓名ID")
    private String dyUserId;

    @ApiModelProperty(value = "市场调研人员名称")
    private String dyUserName;

    @ApiModelProperty(value = "市场调研人员联系方式")
    private String dyUserPhone;

    @ApiModelProperty(value = "市场调研省")
    private String province;

    @ApiModelProperty(value = "市场调研市")
    private String city;

    @ApiModelProperty(value = "市场调研区县")
    private String district;

    @ApiModelProperty(value = "市场调研街道")
    private String street;

    @ApiModelProperty(value = "创建人ID")
    private String createUserId;

    @ApiModelProperty(value = "创建人姓名")
    private String createUserName;

    @ApiModelProperty(value = "创建时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;

    @ApiModelProperty(value = "修改人ID")
    private String updateUserId;

    @ApiModelProperty(value = "修改人姓名")
    private String updateUserName;

    @ApiModelProperty(value = "修改时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date updateTime;

    @ApiModelProperty(value = "上传文件路径")
    private String filePath;

    @ApiModelProperty(value = "排名前三的供应商")
    private String rankSupplier1;

    @ApiModelProperty(value = "排名前三的供应商")
    private String rankSupplier2;

    @ApiModelProperty(value = "排名前三的供应商")
    private String rankSupplier3;


}

2. XhjkMarketTerminalExpectLowrateItem.java

package com.demo.model;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * <p>
 * 终端调研期望更低税率品种表
 * </p>
 *
 * @author ${author}
 * @since 2021-11-17
 */
@Data
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="XhjkMarketTerminalExpectLowrateItem对象", description="终端调研期望更低税率品种表")
public class XhjkMarketTerminalExpectLowrateItem implements Serializable {

    private static final long serialVersionUID=1L;

    @ApiModelProperty(value = "主键")
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    @ApiModelProperty(value = "外键（关联市场小清单联系方式表xhjk_market_contact_way的ID）")
    private Long contactWayId;

    @ApiModelProperty(value = "清单联系信息名称（冗余字段）")
    private String contactName;

    @ApiModelProperty(value = "联系人姓名（冗余字段）")
    private String userContactName;

    @ApiModelProperty(value = "联系人电话（冗余字段）")
    private String userContactPhone;

    @ApiModelProperty(value = "联系地址（冗余字段）")
    private String contactAddress;

    @ApiModelProperty(value = "商品名称")
    private String productName;

    @ApiModelProperty(value = "商品编码")
    private String productCode;

    @ApiModelProperty(value = "生产企业")
    private String manufacturingEnterprise;

    @ApiModelProperty(value = "规格")
    private String specification;

    @ApiModelProperty(value = "返点")
    private BigDecimal rebates;

    @ApiModelProperty(value = "是否采购（1：是 2：否）")
    private String whetherPurchase;

    @ApiModelProperty(value = "期望价格")
    private BigDecimal expectPrice;

    @ApiModelProperty(value = "期望返点")
    private BigDecimal expectRebates;

    @ApiModelProperty(value = "当前采购价")
    private BigDecimal purchasePrice;

    @ApiModelProperty(value = "月销量（盒）")
    private BigDecimal monthlySales;

    @ApiModelProperty(value = "药师帮挂网价（折后）")
    private BigDecimal ysbPrice;

    @ApiModelProperty(value = "华源挂网价（折后）")
    private BigDecimal hywPrice;

    @ApiModelProperty(value = "备注")
    private String remark;

    @ApiModelProperty(value = "商品供货商id")
    private String supplierCustId;

    @ApiModelProperty(value = "商品供货商名称")
    private String supplierCustName;

    @ApiModelProperty(value = "创建人ID")
    private String createUserId;

    @ApiModelProperty(value = "创建人姓名")
    private String createUserName;

    @ApiModelProperty(value = "创建时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;

    @ApiModelProperty(value = "修改人ID")
    private String updateUserId;

    @ApiModelProperty(value = "修改人姓名")
    private String updateUserName;

    @ApiModelProperty(value = "修改时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date updateTime;


}

3. XhjkMarketTerminalItem.java

package com.demo.model;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * <p>
 * 终端调研产品清单表
 * </p>
 *
 * @author ${author}
 * @since 2021-11-17
 */
@Data
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="XhjkMarketTerminalItem对象", description="终端调研产品清单表")
public class XhjkMarketTerminalItem implements Serializable {

    private static final long serialVersionUID=1L;

    @ApiModelProperty(value = "主键")
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    @ApiModelProperty(value = "外键（关联市场小清单联系方式表xhjk_market_contact_way的ID）")
    private Long contactWayId;

    @ApiModelProperty(value = "清单联系信息名称（冗余字段）")
    private String contactName;

    @ApiModelProperty(value = "联系人姓名（冗余字段）")
    private String userContactName;

    @ApiModelProperty(value = "联系人电话（冗余字段）")
    private String userContactPhone;

    @ApiModelProperty(value = "联系地址（冗余字段）")
    private String contactAddress;

    @ApiModelProperty(value = "商品名称")
    private String productName;

    @ApiModelProperty(value = "商品编码")
    private String productCode;

    @ApiModelProperty(value = "生产企业")
    private String manufacturingEnterprise;

    @ApiModelProperty(value = "规格")
    private String specification;

    @ApiModelProperty(value = "返点")
    private BigDecimal rebates;

    @ApiModelProperty(value = "是否采购（1：是 2：否）")
    private String whetherPurchase;

    @ApiModelProperty(value = "期望价格")
    private BigDecimal expectPrice;

    @ApiModelProperty(value = "期望返点")
    private BigDecimal expectRebates;

    @ApiModelProperty(value = "当前采购价")
    private BigDecimal purchasePrice;

    @ApiModelProperty(value = "月销量（盒）")
    private BigDecimal monthlySales;

    @ApiModelProperty(value = "药师帮挂网价（折后）")
    private BigDecimal ysbPrice;

    @ApiModelProperty(value = "华源挂网价（折后）")
    private BigDecimal hywPrice;

    @ApiModelProperty(value = "备注")
    private String remark;

    @ApiModelProperty(value = "商品供货商id")
    private String supplierCustId;

    @ApiModelProperty(value = "商品供货商名称")
    private String supplierCustName;

    @ApiModelProperty(value = "创建人ID")
    private String createUserId;

    @ApiModelProperty(value = "创建人姓名")
    private String createUserName;

    @ApiModelProperty(value = "创建时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;

    @ApiModelProperty(value = "修改人ID")
    private String updateUserId;

    @ApiModelProperty(value = "修改人姓名")
    private String updateUserName;

    @ApiModelProperty(value = "修改时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date updateTime;


}

导入导出逻辑处理

package com.demo.contorller;

import cn.hutool.core.io.IORuntimeException;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import com.demo.model.XhjkMarketContactWay;
import com.demo.model.XhjkMarketTerminalExpectLowrateItem;
import com.demo.model.XhjkMarketTerminalItem;
import com.demo.utils.Result;
import com.demo.utils.ResultUtil;
import com.demo.utils.TradeException;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

@Api(tags="导入导出")
@RestController
@Slf4j
@RequestMapping("/excel")
public class ExcelController {

    @ApiOperation("导入")
    @PostMapping("/loadFile")
    public Result loadFile(@RequestParam("file")MultipartFile file) throws IOException {
        XhjkMarketContactWay insertTerminal = new XhjkMarketContactWay();
        /****终端基础信息导入****/
        try {
            //文件url转输入流
            /*URL url = new URL(entity.getFilePath());
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            InputStream inputStream = conn.getInputStream();*/
            InputStream inputStream = file.getInputStream();
            //读取sheet1
            ExcelReader readerSheet = ExcelUtil.getReader(inputStream, 0);
            readerSheet.addHeaderAlias("*店名", "contactName");
            readerSheet.addHeaderAlias("店长", "userContactName");
            readerSheet.addHeaderAlias("*联系方式", "userContactPhone");
            readerSheet.addHeaderAlias("地址", "contactAddress");
            readerSheet.addHeaderAlias("*调研人", "dyUserName");
            readerSheet.addHeaderAlias("所属省", "province");
            readerSheet.addHeaderAlias("所属市", "city");
            readerSheet.addHeaderAlias("排名第一供应商", "rankSupplier1");
            readerSheet.addHeaderAlias("排名第二供应商", "rankSupplier2");
            readerSheet.addHeaderAlias("排名第三供应商", "rankSupplier3");
            List<XhjkMarketContactWay> listSheet = readerSheet.read(2, 3, readerSheet.getRowCount(), XhjkMarketContactWay.class);

            log.info("市场小清单-终端清单-终端基本信息导入：" + JSONUtil.toJsonStr(listSheet));
            //处理数据
            if (listSheet.size() == 0) {
                return ResultUtil.fail("sheet1 无数据");
            }
            if (listSheet.size() > 1) {
                return ResultUtil.fail("sheet1 数据只能填写一条记录");
            }
            //一个店铺一个文档，店铺基本信息只有一条
            insertTerminal = listSheet.get(0);
            if (StrUtil.isBlank(insertTerminal.getContactName()))
                throw new TradeException("店名不能为空，请检查文档");
            if (StrUtil.isBlank(insertTerminal.getDyUserName()))
                throw new TradeException("调研人不能为空，请检查文档");
            if (StrUtil.isBlank(insertTerminal.getUserContactPhone()))
                throw new TradeException("联系方式不能为空，请检查文档");
            log.info("市场小清单-终端清单-终端基本信息读取到的数据：" + JSONUtil.toJsonStr(listSheet));

            //TODO 保存数据库

        } catch (IORuntimeException | IOException e) {
            return ResultUtil.fail("读取sheet1失败");
        }

        /****终端经营药品清单导入****/
        try {
            InputStream inputStream = file.getInputStream();
            //读取sheet2
            ExcelReader readerSheet = ExcelUtil.getReader(inputStream, 1);
            readerSheet.addHeaderAlias("*商品名称", "productName");
            readerSheet.addHeaderAlias("生产企业", "manufacturingEnterprise");
            readerSheet.addHeaderAlias("规格", "specification");
            readerSheet.addHeaderAlias("返点", "rebates");
            readerSheet.addHeaderAlias("是否采购", "whetherPurchase");
            readerSheet.addHeaderAlias("期望价格", "expectPrice");
            readerSheet.addHeaderAlias("期望返点", "expectRebates");
            readerSheet.addHeaderAlias("备注", "remark");
            readerSheet.addHeaderAlias("当前采购价", "purchasePrice");
            readerSheet.addHeaderAlias("月销量", "monthlySales");
            readerSheet.addHeaderAlias("药师帮最低价（折后）", "ysbPrice");
            readerSheet.addHeaderAlias("华源最低价(折后)", "hywPrice");
            List<XhjkMarketTerminalItem> listSheet = readerSheet.read(1, 2, readerSheet.getRowCount(), XhjkMarketTerminalItem.class);
            log.info("市场小清单-终端清单-终端经营药品清单读取excel：" + JSONUtil.toJsonStr(listSheet));

            //处理数据
            for (XhjkMarketTerminalItem object : listSheet) {
                if (StrUtil.isBlank(object.getProductName()))
                    throw new TradeException("商品名称不能为空，请检查文档");

                object.setCreateTime(new Date())
                        .setContactWayId(insertTerminal.getId())
                        .setContactName(insertTerminal.getContactName())
                        .setContactAddress(insertTerminal.getContactAddress())
                        .setUserContactName(insertTerminal.getUserContactName())
                        .setUserContactPhone(insertTerminal.getUserContactPhone());
            }
            log.info("市场小清单-终端清单-终端经营药品清单导入参数：" + JSONUtil.toJsonStr(listSheet));
        } catch (IORuntimeException | IOException e) {
            return ResultUtil.fail("终端经营药品清单导入失败");
        }
        /******终端目前有量期望更低扣率的品种导入*******/
        try {
            InputStream inputStream = file.getInputStream();
            //读取sheet3
            ExcelReader readerSheet = ExcelUtil.getReader(inputStream, 2);
            readerSheet.addHeaderAlias("*商品名称", "productName");
            readerSheet.addHeaderAlias("生产企业", "manufacturingEnterprise");
            readerSheet.addHeaderAlias("规格", "specification");
            readerSheet.addHeaderAlias("返点", "rebates");
            readerSheet.addHeaderAlias("是否采购", "whetherPurchase");
            readerSheet.addHeaderAlias("期望价格", "expectPrice");
            readerSheet.addHeaderAlias("期望返点", "expectRebates");
            readerSheet.addHeaderAlias("备注", "remark");
            readerSheet.addHeaderAlias("当前采购价", "purchasePrice");
            readerSheet.addHeaderAlias("月销量", "monthlySales");
            readerSheet.addHeaderAlias("药师帮最低价（折后）", "ysbPrice");
            readerSheet.addHeaderAlias("华源最低价(折后)", "hywPrice");
            List<XhjkMarketTerminalExpectLowrateItem> listSheet = readerSheet.read(1, 2, readerSheet.getRowCount(), XhjkMarketTerminalExpectLowrateItem.class);
            log.info("市场小清单-终端清单-终端目前有量期望更低扣率的品种读取excel：" + JSONUtil.toJsonStr(listSheet));

            //处理数据
            for (XhjkMarketTerminalExpectLowrateItem object : listSheet) {
                if (StrUtil.isBlank(object.getProductName()))
                    throw new TradeException("商品名称不能为空，请检查文档");

                object.setCreateTime(new Date())
                        .setContactWayId(insertTerminal.getId())
                        .setContactName(insertTerminal.getContactName())
                        .setContactAddress(insertTerminal.getContactAddress())
                        .setUserContactName(insertTerminal.getUserContactName())
                        .setUserContactPhone(insertTerminal.getUserContactPhone());
            }
            log.info("市场小清单-终端清单-终端目前有量期望更低扣率的品种导入参数：" + JSONUtil.toJsonStr(listSheet));
        } catch (IORuntimeException | IOException e) {
            return ResultUtil.fail("终端目前有量期望更低扣率的品种导入失败");
        }
        return null;
    }

    @ApiOperation("导出")
    @PostMapping("/export")
    Result export( HttpServletResponse response) {
        String tempFileName = "";
        try {
            tempFileName = URLEncoder.encode("终端目前有量期望更低扣率的品种" + System.currentTimeMillis() + ".xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
            return ResultUtil.fail("文件名格式化失败");
        }
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment;fileName=" + tempFileName);
        response.setContentType("application/octet-stream;charset=utf-8");

        //代写入的文件流
        try {
            OutputStream os = response.getOutputStream();

            //TODO 查询数据开始
            //获取当前终端
            XhjkMarketContactWay xhjkMarketContactWay = new XhjkMarketContactWay();
            //查询所有
            List<XhjkMarketTerminalExpectLowrateItem> list = new ArrayList<>();
            //TODO 查询数据结束

            // 通过工具类创建writer，默认创建xls格式
            ExcelWriter writer = cn.hutool.poi.excel.ExcelUtil.getBigWriter();
            writer.renameSheet("终端目前有量期望更低扣率的品种");

            //创建一行空表头占个位置
            writer.writeHeadRow(new LinkedList());
            writer.writeHeadRow(new LinkedList());
            writer.writeHeadRow(new LinkedList());
            //自定义导出的标题别名
            writer.addHeaderAlias("productName", "通用名");
            writer.addHeaderAlias("specification", "规格");
            writer.addHeaderAlias("manufacturingEnterprise", "生产企业");
            writer.addHeaderAlias("rebates", "返点");
            writer.addHeaderAlias("whetherPurchase", "是否采购");
            writer.addHeaderAlias("expectPrice", "期望价格");
            writer.addHeaderAlias("expectRebates", "期望返点");
            writer.addHeaderAlias("remark", "备注");
            writer.addHeaderAlias("purchasePrice", "当前采购价");
            writer.addHeaderAlias("monthlySales", "月销量");
            writer.addHeaderAlias("ysbPrice", "药师帮挂网价（折后）");
            writer.addHeaderAlias("hywPrice", "华源挂网价（折后）");

            //设置列宽
            writer.setColumnWidth(0, 25);
            writer.setColumnWidth(1, 15);
            writer.setColumnWidth(2, 20);
            writer.setColumnWidth(3, 10);
            writer.setColumnWidth(4, 10);
            writer.setColumnWidth(5, 12);
            writer.setColumnWidth(6, 12);
            writer.setColumnWidth(7, 20);
            writer.setColumnWidth(8, 13);
            writer.setColumnWidth(9, 13);
            writer.setColumnWidth(10, 20);
            writer.setColumnWidth(11, 20);

            // 合并单元格后的标题行，使用默认标题样式
            writer.merge(0,0,0,11, "导 出 终 端 目 前 有 量 期 望 更 低 扣 率 的 品 种",true);
            writer.merge(1,2,0,1, "终端店名：",true);
            writer.merge(1,2,2,3, xhjkMarketContactWay.getContactName(),true);
            writer.merge(1,2,4,5, "联系人：",true);
            writer.merge(1,2,6,7, xhjkMarketContactWay.getUserContactName(),true);
            writer.merge(1,2,8,9, "联系电话:",true);
            writer.merge(1,2,10,11, xhjkMarketContactWay.getUserContactPhone(),true);

            //水平左对齐，垂直中间对齐
            writer.getStyleSet().setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
            //设置窗口冻结，之前冻结的窗口会被覆盖 colSplit:列  rowSplit：行
            writer.setFreezePane(4);
            //设置是否只保留别名中的字段值，如果为true，则不设置alias的字段将不被输出，false表示原样输出
            writer.setOnlyAlias(true);
            // 设置样式
            // 定义单元格背景色
            StyleSet style = writer.getStyleSet();
            CellStyle headCellStyle = style.getHeadCellStyle();
            // 第二个参数表示是否也设置头部单元格背景
            style.setBackgroundColor(IndexedColors.LIGHT_GREEN, true);
            //设置内容字体
            Font font = writer.createFont();
            //设置字体
            font.setFontName("宋体");
            //加粗
            font.setBold(true);
            //设置标题字体大小
            font.setFontHeightInPoints((short) 12);
            headCellStyle.setFont(font);
            writer.setStyleSet(style);

            // 一次性写出内容，使用默认样式，强制输出标题
            writer.write(list, true);

            writer.flush(os, true);
            writer.close();
            os.flush();
            os.close();

        } catch (Exception e) {
            e.printStackTrace();
            return ResultUtil.fail("文件写入失败");
        }
        return null;
    }

}

导入完整方法

@PostMapping("/loadFile")
    public Result loadFile(@RequestParam("file")MultipartFile file) throws IOException {
        XhjkMarketContactWay insertTerminal = new XhjkMarketContactWay();
        /****终端基础信息导入****/
        try {
            //文件url转输入流
            /*URL url = new URL(entity.getFilePath());
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            InputStream inputStream = conn.getInputStream();*/
            InputStream inputStream = file.getInputStream();
            //读取sheet1
            ExcelReader readerSheet = ExcelUtil.getReader(inputStream, 0);
            readerSheet.addHeaderAlias("*店名", "contactName");
            readerSheet.addHeaderAlias("店长", "userContactName");
            readerSheet.addHeaderAlias("*联系方式", "userContactPhone");
            readerSheet.addHeaderAlias("地址", "contactAddress");
            readerSheet.addHeaderAlias("*调研人", "dyUserName");
            readerSheet.addHeaderAlias("所属省", "province");
            readerSheet.addHeaderAlias("所属市", "city");
            readerSheet.addHeaderAlias("排名第一供应商", "rankSupplier1");
            readerSheet.addHeaderAlias("排名第二供应商", "rankSupplier2");
            readerSheet.addHeaderAlias("排名第三供应商", "rankSupplier3");
            List<XhjkMarketContactWay> listSheet = readerSheet.read(2, 3, readerSheet.getRowCount(), XhjkMarketContactWay.class);

            log.info("市场小清单-终端清单-终端基本信息导入：" + JSONUtil.toJsonStr(listSheet));
            //处理数据
            if (listSheet.size() == 0) {
                return ResultUtil.fail("sheet1 无数据");
            }
            if (listSheet.size() > 1) {
                return ResultUtil.fail("sheet1 数据只能填写一条记录");
            }
            //一个店铺一个文档，店铺基本信息只有一条
            insertTerminal = listSheet.get(0);
            if (StrUtil.isBlank(insertTerminal.getContactName()))
                throw new TradeException("店名不能为空，请检查文档");
            if (StrUtil.isBlank(insertTerminal.getDyUserName()))
                throw new TradeException("调研人不能为空，请检查文档");
            if (StrUtil.isBlank(insertTerminal.getUserContactPhone()))
                throw new TradeException("联系方式不能为空，请检查文档");
            log.info("市场小清单-终端清单-终端基本信息读取到的数据：" + JSONUtil.toJsonStr(listSheet));

            //TODO 保存数据库

        } catch (IORuntimeException | IOException e) {
            return ResultUtil.fail("读取sheet1失败");
        }

        /****终端经营药品清单导入****/
        try {
            InputStream inputStream = file.getInputStream();
            //读取sheet2
            ExcelReader readerSheet = ExcelUtil.getReader(inputStream, 1);
            readerSheet.addHeaderAlias("*商品名称", "productName");
            readerSheet.addHeaderAlias("生产企业", "manufacturingEnterprise");
            readerSheet.addHeaderAlias("规格", "specification");
            readerSheet.addHeaderAlias("返点", "rebates");
            readerSheet.addHeaderAlias("是否采购", "whetherPurchase");
            readerSheet.addHeaderAlias("期望价格", "expectPrice");
            readerSheet.addHeaderAlias("期望返点", "expectRebates");
            readerSheet.addHeaderAlias("备注", "remark");
            readerSheet.addHeaderAlias("当前采购价", "purchasePrice");
            readerSheet.addHeaderAlias("月销量", "monthlySales");
            readerSheet.addHeaderAlias("药师帮最低价（折后）", "ysbPrice");
            readerSheet.addHeaderAlias("华源最低价(折后)", "hywPrice");
            List<XhjkMarketTerminalItem> listSheet = readerSheet.read(1, 2, readerSheet.getRowCount(), XhjkMarketTerminalItem.class);
            log.info("市场小清单-终端清单-终端经营药品清单读取excel：" + JSONUtil.toJsonStr(listSheet));

            //处理数据
            for (XhjkMarketTerminalItem object : listSheet) {
                if (StrUtil.isBlank(object.getProductName()))
                    throw new TradeException("商品名称不能为空，请检查文档");

                object.setCreateTime(new Date())
                        .setContactWayId(insertTerminal.getId())
                        .setContactName(insertTerminal.getContactName())
                        .setContactAddress(insertTerminal.getContactAddress())
                        .setUserContactName(insertTerminal.getUserContactName())
                        .setUserContactPhone(insertTerminal.getUserContactPhone());
            }
            log.info("市场小清单-终端清单-终端经营药品清单导入参数：" + JSONUtil.toJsonStr(listSheet));
        } catch (IORuntimeException | IOException e) {
            return ResultUtil.fail("终端经营药品清单导入失败");
        }
        /******终端目前有量期望更低扣率的品种导入*******/
        try {
            InputStream inputStream = file.getInputStream();
            //读取sheet3
            ExcelReader readerSheet = ExcelUtil.getReader(inputStream, 2);
            readerSheet.addHeaderAlias("*商品名称", "productName");
            readerSheet.addHeaderAlias("生产企业", "manufacturingEnterprise");
            readerSheet.addHeaderAlias("规格", "specification");
            readerSheet.addHeaderAlias("返点", "rebates");
            readerSheet.addHeaderAlias("是否采购", "whetherPurchase");
            readerSheet.addHeaderAlias("期望价格", "expectPrice");
            readerSheet.addHeaderAlias("期望返点", "expectRebates");
            readerSheet.addHeaderAlias("备注", "remark");
            readerSheet.addHeaderAlias("当前采购价", "purchasePrice");
            readerSheet.addHeaderAlias("月销量", "monthlySales");
            readerSheet.addHeaderAlias("药师帮最低价（折后）", "ysbPrice");
            readerSheet.addHeaderAlias("华源最低价(折后)", "hywPrice");
            List<XhjkMarketTerminalExpectLowrateItem> listSheet = readerSheet.read(1, 2, readerSheet.getRowCount(), XhjkMarketTerminalExpectLowrateItem.class);
            log.info("市场小清单-终端清单-终端目前有量期望更低扣率的品种读取excel：" + JSONUtil.toJsonStr(listSheet));

            //处理数据
            for (XhjkMarketTerminalExpectLowrateItem object : listSheet) {
                if (StrUtil.isBlank(object.getProductName()))
                    throw new TradeException("商品名称不能为空，请检查文档");

                object.setCreateTime(new Date())
                        .setContactWayId(insertTerminal.getId())
                        .setContactName(insertTerminal.getContactName())
                        .setContactAddress(insertTerminal.getContactAddress())
                        .setUserContactName(insertTerminal.getUserContactName())
                        .setUserContactPhone(insertTerminal.getUserContactPhone());
            }
            log.info("市场小清单-终端清单-终端目前有量期望更低扣率的品种导入参数：" + JSONUtil.toJsonStr(listSheet));
        } catch (IORuntimeException | IOException e) {
            return ResultUtil.fail("终端目前有量期望更低扣率的品种导入失败");
        }
        return null;
    }

导出完整方法

@PostMapping("/export")
    Result export( HttpServletResponse response) {
        String tempFileName = "";
        try {
            tempFileName = URLEncoder.encode("终端目前有量期望更低扣率的品种" + System.currentTimeMillis() + ".xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
            return ResultUtil.fail("文件名格式化失败");
        }
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment;fileName=" + tempFileName);
        response.setContentType("application/octet-stream;charset=utf-8");

        //代写入的文件流
        try {
            OutputStream os = response.getOutputStream();

            //TODO 查询数据开始
            //获取当前终端
            XhjkMarketContactWay xhjkMarketContactWay = new XhjkMarketContactWay();
            //查询所有
            List<XhjkMarketTerminalExpectLowrateItem> list = new ArrayList<>();
            //TODO 查询数据结束

            // 通过工具类创建writer，默认创建xls格式
            ExcelWriter writer = cn.hutool.poi.excel.ExcelUtil.getBigWriter();
            writer.renameSheet("终端目前有量期望更低扣率的品种");

            //创建一行空表头占个位置
            writer.writeHeadRow(new LinkedList());
            writer.writeHeadRow(new LinkedList());
            writer.writeHeadRow(new LinkedList());
            //自定义导出的标题别名
            writer.addHeaderAlias("productName", "通用名");
            writer.addHeaderAlias("specification", "规格");
            writer.addHeaderAlias("manufacturingEnterprise", "生产企业");
            writer.addHeaderAlias("rebates", "返点");
            writer.addHeaderAlias("whetherPurchase", "是否采购");
            writer.addHeaderAlias("expectPrice", "期望价格");
            writer.addHeaderAlias("expectRebates", "期望返点");
            writer.addHeaderAlias("remark", "备注");
            writer.addHeaderAlias("purchasePrice", "当前采购价");
            writer.addHeaderAlias("monthlySales", "月销量");
            writer.addHeaderAlias("ysbPrice", "药师帮挂网价（折后）");
            writer.addHeaderAlias("hywPrice", "华源挂网价（折后）");

            //设置列宽
            writer.setColumnWidth(0, 25);
            writer.setColumnWidth(1, 15);
            writer.setColumnWidth(2, 20);
            writer.setColumnWidth(3, 10);
            writer.setColumnWidth(4, 10);
            writer.setColumnWidth(5, 12);
            writer.setColumnWidth(6, 12);
            writer.setColumnWidth(7, 20);
            writer.setColumnWidth(8, 13);
            writer.setColumnWidth(9, 13);
            writer.setColumnWidth(10, 20);
            writer.setColumnWidth(11, 20);

            // 合并单元格后的标题行，使用默认标题样式
            writer.merge(0,0,0,11, "导 出 终 端 目 前 有 量 期 望 更 低 扣 率 的 品 种",true);
            writer.merge(1,2,0,1, "终端店名：",true);
            writer.merge(1,2,2,3, xhjkMarketContactWay.getContactName(),true);
            writer.merge(1,2,4,5, "联系人：",true);
            writer.merge(1,2,6,7, xhjkMarketContactWay.getUserContactName(),true);
            writer.merge(1,2,8,9, "联系电话:",true);
            writer.merge(1,2,10,11, xhjkMarketContactWay.getUserContactPhone(),true);

            //水平左对齐，垂直中间对齐
            writer.getStyleSet().setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
            //设置窗口冻结，之前冻结的窗口会被覆盖 colSplit:列  rowSplit：行
            writer.setFreezePane(4);
            //设置是否只保留别名中的字段值，如果为true，则不设置alias的字段将不被输出，false表示原样输出
            writer.setOnlyAlias(true);
            // 设置样式
            // 定义单元格背景色
            StyleSet style = writer.getStyleSet();
            CellStyle headCellStyle = style.getHeadCellStyle();
            // 第二个参数表示是否也设置头部单元格背景
            style.setBackgroundColor(IndexedColors.LIGHT_GREEN, true);
            //设置内容字体
            Font font = writer.createFont();
            //设置字体
            font.setFontName("宋体");
            //加粗
            font.setBold(true);
            //设置标题字体大小
            font.setFontHeightInPoints((short) 12);
            headCellStyle.setFont(font);
            writer.setStyleSet(style);

            // 一次性写出内容，使用默认样式，强制输出标题
            writer.write(list, true);

            writer.flush(os, true);
            writer.close();
            os.flush();
            os.close();

        } catch (Exception e) {
            e.printStackTrace();
            return ResultUtil.fail("文件写入失败");
        }
        return null;
    }

大数据导出

 @GetMapping(value = "/exportShop")
    @ApiOperation(value = "导出店铺名单")
    ResponseResult exportShop(ShopInfoPageRequest request, HttpServletResponse response) {

        String tempFileName = null;
        try {
            tempFileName = URLEncoder.encode("店铺名单" + System.currentTimeMillis() + ".xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
            return ResponseResult.fail("文件名格式化失败");
        }
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment;fileName=" + tempFileName);
        response.setContentType("application/octet-stream;charset=utf-8");

        //代写入的文件流
        try {
            OutputStream os = response.getOutputStream();

            log.info("导出店铺清单get：" + JSON.toJSONString(request));
            
            //TODO 获取数据
            ArrayList<ExportShopInfoVO> list = new ArrayList<>();
            

            // 通过工具类创建writer，默认创建xls格式
            ExcelWriter writer = cn.hutool.poi.excel.ExcelUtil.getBigWriter();
            //自定义标题别名
            writer.addHeaderAlias("name", "店铺名称");
            writer.addHeaderAlias("shopType", "店铺类型");
            writer.addHeaderAlias("province", "地区");
            writer.addHeaderAlias("address", "地址");
            writer.addHeaderAlias("managementUserName", "管理合伙人");
            writer.addHeaderAlias("insiderUserName", "执行合伙人");
            writer.addHeaderAlias("assistantNames", "专属助理");
            writer.addHeaderAlias("status", "上线情况");
            writer.addHeaderAlias("shopOwnerName", "店主");
            writer.addHeaderAlias("mainCompanyName", "主体企业");
            writer.addHeaderAlias("subPartnerStatus", "特约商户状态");
            writer.addHeaderAlias("commonBankStatus", "银行一般户状态");
            writer.getStyleSet().setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER); //水平左对齐，垂直中间对齐
            writer.setColumnWidth(0, 50);
            writer.setColumnWidth(1, 20);
            writer.setColumnWidth(2, 20);
            writer.setColumnWidth(3, 40);
            writer.setColumnWidth(4, 20);
            writer.setColumnWidth(5, 20);
            writer.setColumnWidth(6, 20);
            writer.setColumnWidth(7, 20);
            writer.setColumnWidth(8, 20);
            writer.setColumnWidth(9, 30);
            writer.setColumnWidth(10, 20);
            writer.setColumnWidth(11, 30);
            // 合并单元格后的标题行，使用默认标题样式
            writer.merge(11, "店铺清单");
            // 一次性写出内容，使用默认样式，强制输出标题
            writer.write(list, true);

            writer.flush(os, true);
            writer.close();
            os.flush();
            os.close();

        } catch (Exception e) {
            e.printStackTrace();
            return ResponseResult.fail("文件写入失败");
        }
        return null;
    }

[img-0]:data:application/xml;base64,PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPEVycm9yPgogIDxDb2RlPkludmFsaWRBcmd1bWVudDwvQ29kZT4KICA8TWVzc2FnZT5UaGUgdmFsdWU6IDE2aHR0cHM6IG9mIHBhcmFtZXRlcjogeCBpcyBpbnZhbGlkLjwvTWVzc2FnZT4KICA8UmVxdWVzdElkPjYyQ0QxNTZFNTM3OTNBMzIzNzNCRkM3NTwvUmVxdWVzdElkPgogIDxIb3N0SWQ+aW1nLWJsb2cuY3NkbmltZy5jbjwvSG9zdElkPgo8L0Vycm9yPgo=