投稿
  1. 首页
  2. 随笔

hutool导入导出，java使用hutool导入导出，hutool导出多级标题复杂表头,hutool大数据量导出

xxxxmovies • 2022-12-15 • 随笔 • 阅读 33

hutool导入导出，java使用hutool导入导出，hutool导出多级标题复杂表头,hutool大数据量导出,第1张

hutool导入导出，java使用hutool导入导出，hutool导出多级标题复杂表头,hutool大数据量导出

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

本人 *** 作使用了 lombok和 swagger2，没有使用这两个依赖jar包的，可以删除依赖包和文件中的使用地方。

导入模板

导出结果：

1、导入依赖

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

 
        
            cn.hutool
            hutool-all
            5.7.16
        
        
        
            org.apache.poi
            poi-ooxml
            4.1.2
        
 
        
            org.projectlombok
            lombok
            1.18.6
            provided
        

        
            io.springfox
            springfox-swagger2
            2.9.2
        
        
        
            com.github.xiaoymin
            swagger-bootstrap-ui
            1.9.6
测试实体类

实体类省略了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;


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


}
  1. 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;


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


}
  1. 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;


@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转输入流
            
            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 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 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 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 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转输入流
            
            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 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 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 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 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 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;
    }

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/zaji/5604967.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
xxxxmovies xxxxmovies 一级用户组
0 0
上一篇 2022-12-15
下一篇 2022-12-15

发表评论

登录后才能评论

评论列表（0条）

保存