SpringBoot POI 导入，导出 Excel

2019年12月13日 18:31 · 阅读(2159) ·

参考

Springboot poi 导入、导出excel 简单步骤
https://www.jianshu.com/p/e12c69eea764

POI复制Excel模板并填充数据
https://yq.aliyun.com/articles/680217

Apache POI 之一：Excel文档的读取
https://www.jianshu.com/p/80a9c2c7ebe5

SpringBoot静态资源的访问
https://www.jianshu.com/p/d40ee98b84b5

目标

在 SpringBoot 使用 POI 导入，导出 Excel 数据。

开发环境

名称 版本
操作系统 Windows 10 X64
JDK JDK1.8（jdk-8u151-windows-x64）
IntelliJ IDEA IntelliJ IDEA 2018.3
Maven Maven 3.6.0

说明

本文的代码结构基于 IntelliJ IDEA 2018.3 创建 Maven 多模块(Module)项目+负载均衡 项目上进行操作。

功能概述

数据表-t_fpts_tax_exemption

  1. -- ----------------------------
  2. -- Table structure for t_fpts_tax_exemption
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `t_fpts_tax_exemption`;
  5. CREATE TABLE `t_fpts_tax_exemption`  (
  6.   `id` bigint(20) NOT NULL COMMENT '主键',
  7.   `contract_party` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '合同方（合作方名称）',
  8.   `contract_no` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '合同编号',
  9.   `chinese_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '中文翻译名称',
  10.   `product` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '产品\r\n',
  11.   `signing_date` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '签订日期\r\n',
  12.   `start_date` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '开始日期\r\n',
  13.   `end_time` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '结束日期\r\n',
  14.   `contract_type` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '合同类型\r\n',
  15.   `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '备注\r\n',
  16.   `principal` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '负责商务\r\n',
  17.   `records_date` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '税局备案日期\r\n',
  18.   `tax_exemption_date` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '免增值税备案有效期\r\n',
  19.   `supplier_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '供应商编码',
  20.   `create_time` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  21.   `create_by` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
  22.   `update_time` timestamp(0) NULL DEFAULT NULL COMMENT '修改时间',
  23.   `update_by` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改人',
  24.   `is_delete` tinyint(1) NULL DEFAULT NULL COMMENT '是否删除。1，-1',
  25.   `biz_time` timestamp(0) NULL DEFAULT NULL COMMENT '系统时间戳',
  26.   `create_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  27.   `update_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  28.   PRIMARY KEY (`id`) USING BTREE
  29. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
  31. SET FOREIGN_KEY_CHECKS = 1;

测试数据

  1. --导出Excel测试数据
  2. insert into t_fpts_tax_exemption(id,contract_party,contract_no,chinese_name,product,signing_date,start_date,end_time,contract_type,remark,principal,records_date,tax_exemption_date,supplier_code,create_time,create_by,update_time,update_by,is_delete,biz_time,create_name,update_name
  3. )
  4. values(6,'测试供应商','contract_no-001','西安雅和印刷科技有限公司','产品-皮包','2019-01','2019-01','2019-12','企业','【测试】-v_hwhao-1','wuxingfeng','2018-01-01','2020-12-12','supplier_code-001',now(),'v_hwhao',now(),'v_hwhao',1,now(),'v_hwhao','v_hwhao');
  5. insert into t_fpts_tax_exemption(id,contract_party,contract_no,chinese_name,product,signing_date,start_date,end_time,contract_type,remark,principal,records_date,tax_exemption_date,supplier_code,create_time,create_by,update_time,update_by,is_delete,biz_time,create_name,update_name
  6. )
  7. values(7,'PIBAO-Company-2','contract_no-002','皮包公司','产品-皮包','2019-01','2019-01','2019-12','企业','【测试】-v_hwhao-1','wuxingfeng','2018-01-01','2020-12-12','supplier_code-003',now(),'v_hwhao',now(),'v_hwhao',1,now(),'v_hwhao','v_hwhao');
  8. insert into t_fpts_tax_exemption(id,contract_party,contract_no,chinese_name,product,signing_date,start_date,end_time,contract_type,remark,principal,records_date,tax_exemption_date,supplier_code,create_time,create_by,update_time,update_by,is_delete,biz_time,create_name,update_name
  9. )
  10. values(8,'TIAN-CHI-2','contract_no-003','天池茶叶公司','产品-大红袍','2019-01','2019-01','2019-12','企业','【测试】-v_hwhao-1','wuxingfeng','2018-01-01','2020-12-12','supplier_code-003',now(),'v_hwhao',now(),'v_hwhao',1,now(),'v_hwhao','v_hwhao');

公共类-增删查改操作类

Common-数据实体类

基础实体类-SuperEntity

  1. import com.baomidou.mybatisplus.annotation.FieldFill;
  2. import com.baomidou.mybatisplus.annotation.IdType;
  3. import com.baomidou.mybatisplus.annotation.TableField;
  4. import com.baomidou.mybatisplus.annotation.TableId;
  5. import com.baomidou.mybatisplus.extension.activerecord.Model;
  6. import com.fasterxml.jackson.annotation.JsonFormat;
  7. import io.swagger.annotations.ApiModelProperty;
  8. import lombok.Data;
  9. import lombok.EqualsAndHashCode;
  10. import lombok.experimental.Accessors;
  12. import java.io.Serializable;
  13. import java.util.Date;
  15. /**
  16.  * 基础实体类
  17.  *
  18.  */
  20. @EqualsAndHashCode(callSuper = true)
  21. @Data
  22. @Accessors(chain = true)
  23. public class SuperEntity<T extends Model> extends Model<T> {
  25.     @TableId(type = IdType.ID_WORKER)
  26.     @ApiModelProperty(value = "主键")
  27.     private Long id;
  29.     @ApiModelProperty(value = "创建时间", hidden = true)
  30.     @TableField(value = "create_time", fill = FieldFill.INSERT)
  31.     @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
  32.     private Date createTime;
  34.     @TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
  35.     @ApiModelProperty(value = "修改时间", hidden = true)
  36.     @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
  37.     private Date updateTime;
  39.     @ApiModelProperty(value = "创建人", hidden = true)
  40.     @TableField(value = "create_by", fill = FieldFill.INSERT)
  41.     private String createBy;
  43.     @ApiModelProperty(value = "创建人名称", hidden = true)
  44.     @TableField(value = "create_name", fill = FieldFill.INSERT)
  45.     private String createName;
  47.     @ApiModelProperty(value = "修改人", hidden = true)
  48.     @TableField(value = "update_by", fill = FieldFill.INSERT_UPDATE)
  49.     private String updateBy;
  51.     @ApiModelProperty(value = "创建人名称", hidden = true)
  52.     @TableField(value = "update_name", fill = FieldFill.INSERT)
  53.     private String updateName;
  55.     @ApiModelProperty(value = "是否删除（1、否，-1、是）", hidden = true)
  56.     @TableField(value = "is_delete", fill = FieldFill.INSERT)
  57.     private Integer isDelete;
  59.     @ApiModelProperty(value = "系统时间戳", hidden = true)
  60.     @TableField(value = "biz_time", fill = FieldFill.INSERT_UPDATE)
  61.     @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
  62.     private Date bizTime;
  64.     @Override
  65.     protected Serializable pkVal() {
  66.         return this.id;
  67.     }
  69. }

数据表映射类-TaxExemption

  1. import com.baomidou.mybatisplus.annotation.TableName;
  2. import com.foreign.payment.model.base.SuperEntity;
  3. import lombok.Data;
  4. import lombok.EqualsAndHashCode;
  5. import lombok.experimental.Accessors;
  7. @Data
  8. @EqualsAndHashCode(callSuper = true)
  9. @Accessors(chain = true)
  10. @TableName("t_fpts_tax_exemption")
  11. public class TaxExemption extends SuperEntity {
  13.     private static final long serialVersionUID=1L;
  15.     /**
  16.      * 合同方
  17.      */
  18.     private String contractParty;
  20.     /**
  21.      * 合同编号
  22.      */
  23.     private String contractNo;
  25.     /**
  26.      * 中文翻译名称
  27.      */
  28.     private String chineseName;
  30.     /**
  31.      * 产品
  33.      */
  34.     private String product;
  36.     /**
  37.      * 签订日期
  39.      */
  40.     private String signingDate;
  42.     /**
  43.      * 开始日期
  45.      */
  46.     private String startDate;
  48.     /**
  49.      * 结束日期
  51.      */
  52.     private String endTime;
  54.     /**
  55.      * 合同类型
  57.      */
  58.     private String contractType;
  60.     /**
  61.      * 备注
  63.      */
  64.     private String remark;
  66.     /**
  67.      * 负责商务
  69.      */
  70.     private String principal;
  72.     /**
  73.      * 税局备案日期
  75.      */
  76.     private String recordsDate;
  78.     /**
  79.      * 免增值税备案有效期
  81.      */
  82.     private String taxExemptionDate;
  84.     /**
  85.      * 供应商编码
  86.      */
  87.     private String supplierCode;
  90. }

分页基础类-PageVO

  2. import io.swagger.annotations.ApiModelProperty;
  3. import lombok.Getter;
  4. import lombok.Setter;
  5. import lombok.experimental.Accessors;
  8. /**
  9.  * 分页基础类
  10.  */
  11. @Getter
  12. @Setter
  13. @Accessors(chain = true)
  14. public class PageVO {
  16.     @ApiModelProperty("当前页数")
  17.     private Integer current;
  19.     @ApiModelProperty("每页显示条数")
  20.     private Integer size;
  21. }

查询参数类-SupplierRequestData

  2. import com.foreign.payment.vo.PageVO;
  3. import lombok.Data;
  5. /**
  6.  * 供应商接口请求数据类（至少填写一个参数）
  7.  */
  8. @Data
  9. public class SupplierRequestData extends PageVO {
  11.     /**
  12.      * 供应商ID
  13.      */
  14.     private String supplierId;
  17.     /**
  18.      * 供应商名称
  19.      */
  20.     private String supplierName;
  21. }

Contract 接口

  1. /**
  2.  * 获取免税合同供应商
  3.  *
  4.  * @param param 供应商信息
  5.  * @return 免税合同供应商列表
  6.  */
  7. @PostMapping("/exemptTaxSupplier")
  8. ResponseVO<Page<TaxExemption>> exemptTaxSupplier(@RequestBody SupplierRequestData param);
  10. /**
  11.  * 替换免税合同供应商（删除之前的所有数据）
  12.  * @param param 免税合同供应商数据
  13.  * @return 替换成功返回 true，失败返回 false
  14.  */
  15. @PostMapping("/replaceTaxExempt")
  16. ResponseVO replaceTaxExempt(@RequestBody List<TaxExemption> param);

Service 接口

Mapper 接口-TaxExemptionRepository

  1. @Repository
  2. public interface TaxExemptionRepository extends BaseMapper<TaxExemption> {
  3.     /**
  4.      * 删除所有免税合同供应商
  5.      */
  6.     void delTaxExempt();
  7. }

Mapper 接口映射 XML-TaxExemptionMapper.xml

  1. <select id="delTaxExempt">
  2.     update t_fpts_tax_exemption set is_delete = -1
  3. </select>

数据库操作接口-TaxExemptionService

  1. public interface TaxExemptionService extends IService<TaxExemption> {
  2.     /**
  3.      * 获取免税合同供应商
  4.      * @param param 供应商信息
  5.      * @return 免税合同供应商列表
  6.      */
  7.     ResponseVO<Page<TaxExemption>> exemptTaxSupplier(SupplierRequestData param);
  9.     /**
  10.      * 删除免税合同供应商
  11.      */
  12.     void delTaxExempt();
  14.     /**
  15.      * 添加免税合同供应商
  16.      * @param param 免税合同供应商数据
  17.      */
  18.     void addTaxExempt(List<TaxExemption> param);
  19. }

接口实现类-TaxExemptionServiceImpl

  1. @Service
  2. public class TaxExemptionServiceImpl extends ServiceImpl<TaxExemptionRepository, TaxExemption> implements TaxExemptionService {
  3.     /**
  4.      * 获取免税合同供应商
  5.      * @param param 供应商信息
  6.      * @return 免税合同供应商列表
  7.      */
  8.     @Override
  9.     public ResponseVO<Page<TaxExemption>> exemptTaxSupplier(SupplierRequestData param){
  10.         //分页条件
  11.         Page<TaxExemption> page = new Page<>();
  12.         page.setSize(param.getSize());
  13.         page.setCurrent(param.getCurrent());
  15.         //查询条件
  16.         QueryWrapper<TaxExemption> wrapper = new QueryWrapper<>();
  17.         wrapper.eq("is_delete",1);
  18.         wrapper.like("supplier_code",param.getSupplierId());
  19.         wrapper.like("contract_party",param.getSupplierName());
  21.         IPage<TaxExemption> ipageData = baseMapper.selectPage(page,wrapper);
  23.         Page<TaxExemption> pageData = CommonUtil.toPage(ipageData);
  25.         ResponseVO<Page<TaxExemption>> responseVO = new ResponseVO<>(ResponseCode.OK);
  26.         responseVO.setData(pageData);
  27.         return responseVO;
  30.         /*
  31.         //类型转换
  32.         ResponseVO<List<SupplierDataVO>> responseVO = new ResponseVO<>(ResponseCode.OK);
  33.         if(pageData != null && pageData.getRecords() != null && pageData.getRecords().size() > 0){
  34.             List<SupplierDataVO> data = new ArrayList<>();
  35.             for(int i=0;i< pageData.getRecords().size();i++){
  36.                 TaxExemption item = pageData.getRecords().get(i);
  37.                 SupplierDataVO model = new SupplierDataVO();
  38.                 model.setId(item.getId());
  39.                 model.setSupplierCode(item.getSupplierCode());
  40.                 model.setSupplierName(item.getContractParty());
  41.                 model.setUpdateTime(item.getUpdateTime());
  42.                 data.add(model);
  43.             }
  44.             responseVO.setData(data);
  45.         }*/
  46.     }
  48.     /**
  49.      * 删除免税合同供应商
  50.      */
  51.     @Override
  52.     public void delTaxExempt(){
  53.         baseMapper.delTaxExempt();
  54.     }
  56.     /**
  57.      * 添加免税合同供应商
  58.      * @param param 免税合同供应商数据
  59.      * @return 替换成功返回 true，失败返回 false
  60.      */
  61.     @Override
  62.     public void addTaxExempt(List<TaxExemption> param){
  63.         for(int i=0;i<param.size();i++){
  64.             baseMapper.insert(param.get(i));
  65.         }
  66.     }
  67. }

Contrller-TaxPayerController

  1. @Slf4j
  2. @RestController
  3. @Api("纳税人管理相关接口")
  4. public class TaxPayerController {
  5.     /**
  6.      * 免税合同供应商-服务接口
  7.      */
  8.     @Autowired
  9.     private TaxExemptionService exemptionService;
  11.     /**
  12.      * 获取免税合同供应商
  13.      * @param param 供应商信息
  14.      * @return 免税合同供应商列表
  15.      */
  16.     @PostMapping("/exemptTaxSupplier")
  17.     public ResponseVO<Page<TaxExemption>> exemptTaxSupplier(@RequestBody SupplierRequestData param){
  18.         return exemptionService.exemptTaxSupplier(param);
  19.     }
  21.     /**
  22.      * 替换免税合同供应商（删除之前的所有数据）
  23.      * @param param 免税合同供应商数据
  24.      * @return 替换成功返回 true，失败返回 false
  25.      */
  26.     @PostMapping("/replaceTaxExempt")
  27.     public ResponseVO replaceTaxExempt(@RequestBody List<TaxExemption> param){
  28.         exemptionService.delTaxExempt();
  29.         exemptionService.addTaxExempt(param);
  31.         ResponseVO<Boolean> responseVO = new ResponseVO<>(ResponseCode.OK);
  32.         responseVO.setData(true);
  33.         return responseVO;
  34.     }
  35. }

Excel 模版

导入模版-ExemptTaxSupplierImport.xlsx

导出模版-ExemptTaxSupplierExport.xlsx

Web层

bootstrap.properties

  1. #静态资源映射
  2. spring.mvc.static-path-pattern=/ExcelTemplate/*
  3. spring.resources.static-locations= classpath:ExcelTemplate/

Resource 放入 Excel 模版文件

yml 添加 Excel 相关配置

application.yml

  1. server:
  2.   port: 8081
  3.   servlet:
  4.     context-path: /payment
  6. spring:
  7.   profiles:
  8.     active: dev

application-dev.yml

  1. #文件导入导出相关配置
  2. file-config:
  3.   excel-template: ExcelTemplate/                  #Excel 模版文件夹名称
  4.   tax-exempt-import: ExemptTaxSupplierImport.xlsx #免税供应商导入 Excel 模版名称
  5.   tax-exempt-export: ExemptTaxSupplierExport.xlsx #免税供应商导出 Excel 模版名称
  6.   tax-exempt-prefix: 免税合同供应商-                #免税供应商导出 Excel 文件前缀

配置读取类-FileConfig

  1. /**
  2.  * 文件导入导出相关配置
  3.  * @date: 2019-12-13 16:42
  4.  */
  5. @Component
  6. @ConfigurationProperties("file-config")
  7. @Getter
  8. public class FileConfig {
  9.     /**
  10.      * 获取 Excel 模版文件夹名称
  11.      */
  12.     @Value("${file-config.excel-template}")
  13.     private String excelTemplate;
  15.     /**
  16.      * 获取免税供应商导入 Excel 模版名称
  17.      */
  18.     @Value("${file-config.tax-exempt-import}")
  19.     private String taxExemptImport;
  21.     /**
  22.      * 获取免税供应商导出 Excel 模版名称
  23.      */
  24.     @Value("${file-config.tax-exempt-export}")
  25.     private String taxExemptExport;
  27.     /**
  28.      * 获取免税供应商导出 Excel 文件前缀
  29.      */
  30.     @Value("${file-config.tax-exempt-prefix}")
  31.     private String taxExemptPrefix;
  32. }

pom.xml 引入 POI 内容

  1. <!-- 基本依赖，仅操作 xls 格式只需引入此依赖 -->
  2. <dependency>
  3.     <groupId>org.apache.poi</groupId>
  4.     <artifactId>poi</artifactId>
  5.     <version>3.14</version>
  6. </dependency>
  7. <!-- 使用 xlsx 格式需要额外引入此依赖 -->
  8. <dependency>
  9.     <groupId>org.apache.poi</groupId>
  10.     <artifactId>poi-ooxml</artifactId>
  11.     <version>3.14</version>
  12. </dependency>

Excel 操作公共类-ExcelUtils

  1. /**
  2.  * Excel 工具类
  3.  * @date: 2019-12-13 16:14
  4.  */
  5. public class ExcelUtils {
  8.     /**
  9.      * 根据文件路径获取  Workbook 对象
  10.      * @param  readFilePath Excel 路径
  11.      * @return POI Workbook 对象
  12.      * @throws IOException
  13.      */
  14.     public static Workbook getWorkbook(String readFilePath) throws IOException{
  15.         //Excel 模版路径
  16.         //String resourcePath = this.getClass().getResource("/").getPath();
  17.         //String readFilePath = resourcePath + "ExcelTemplate/"+fileName;
  18.         File file = new File(readFilePath);
  19.         FileInputStream inputStream = new FileInputStream(file);
  21.         //读取 Excel 模版
  22.         Workbook workbook = null;
  23.         if(isExcel2003(readFilePath)){
  24.             workbook = new HSSFWorkbook(inputStream);
  25.         }else if(isExcel2007(readFilePath)){
  26.             workbook = new XSSFWorkbook(inputStream);
  27.         }
  29.         return workbook;
  30.     }
  32.     /**
  33.      * 下载 Excle
  34.      * @param response     HttpServletResponse
  35.      * @param workbook     Workbook
  36.      * @param prefixName   导出文件的前缀名称
  37.      * @throws IOException
  38.      */
  39.     public static void downLoadExcel(HttpServletResponse response, Workbook workbook, String saveFileName)throws IOException {
  40.         //String saveFileName = prefixName + "-"+ DateUtils.format(new Date(),"yyyy-MM-dd-HHmmss")+".xlsx";
  42.         if(isExcel2003(saveFileName)){
  43.             response.setContentType("application/vnd.ms-excel");
  44.         }else if(isExcel2007(saveFileName)){
  45.             response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
  46.         }
  48.         response.setCharacterEncoding("utf-8");
  49.         response.setHeader("Content-disposition", "attachment;filename=" +  URLEncoder.encode(saveFileName, "UTF-8"));
  50.         workbook.write(response.getOutputStream());
  52.         //保存文件到本地
  53.         //String saveFilePath = resourcePath +"ExcelExport/";
  54.         //File dirSave = new File(saveFilePath);
  55.         //if (!dirSave.exists()) {
  56.         //    dirSave.mkdirs();
  57.         //}
  58.         //FileOutputStream outputStream = new FileOutputStream(saveFilePath + saveFileName);
  59.         //workbook.write(outputStream);
  60.         //outputStream.close();
  61.     }
  63.     /**
  64.      * 是否是 .xls 格式的 excel
  65.      * @param filePath 文件路径
  66.      * @return 是返回 true，否返回 false
  67.      */
  68.     public static Boolean isExcel2003(String filePath)  {
  69.         return filePath.matches("^.+\\.(?i)(xls)$");
  70.     }
  72.     /**
  73.      * 是否是 .xlsx 格式的 excel
  74.      * @param filePath 文件路径
  75.      * @return 是返回 true，否返回 false
  76.      */
  77.     public static Boolean isExcel2007(String filePath)  {
  78.         return filePath.matches("^.+\\.(?i)(xlsx)$");
  79.     }
  81.     /**
  82.      * 验证是否 EXCEL 文件
  83.      * @param filePath 文件路径
  84.      * @return 是返回 true，否返回 false
  85.      */
  86.     public static Boolean validateExcel(String filePath){
  87.         if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){
  88.             return false;
  89.         }
  90.         return true;
  91.     }
  94.     /**
  95.      * 根据文件名称和 InputStream 获取对应类型的 Sheet
  96.      * @param fileName    文件名称
  97.      * @param inputStream InputStream
  98.      * @return
  99.      */
  100.     public static Sheet getSheet(String fileName, InputStream inputStream) throws IOException {
  101.         //根据文件类型选择创建 Workbook 的方式
  102.         Workbook workbook = null;
  103.         Sheet sheet = null;
  104.         //根据文件名称获取类型
  105.         if(isExcel2007(fileName)){
  106.             workbook = new XSSFWorkbook(inputStream);
  107.             sheet = workbook.getSheetAt(0);
  108.         }else if(isExcel2003(fileName)){
  109.             workbook = new HSSFWorkbook(inputStream);
  110.             sheet = workbook.getSheetAt(0);
  111.         }
  112.         return sheet;
  113.     }
  115.     /**
  116.      * 获取 Cell 对应的字符串类型值
  117.      * @param cell Cell
  118.      * @return 字符串类型值
  119.      */
  120.     public static String getStringValue(Cell cell){
  121.         String value="";
  122.         switch (cell.getCellType()) {
  123.             case HSSFCell.CELL_TYPE_NUMERIC: // 数字
  124.                 //如果为时间格式的内容
  125.                 if (HSSFDateUtil.isCellDateFormatted(cell)) {
  126.                     //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
  127.                     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  128.                     value=sdf.format(HSSFDateUtil.getJavaDate(cell.
  129.                             getNumericCellValue())).toString();
  130.                     break;
  131.                 } else {
  132.                     value = new DecimalFormat("0").format(cell.getNumericCellValue());
  133.                 }
  134.                 break;
  135.             case HSSFCell.CELL_TYPE_STRING: // 字符串
  136.                 value = cell.getStringCellValue();
  137.                 break;
  138.             case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
  139.                 value = cell.getBooleanCellValue() + "";
  140.                 break;
  141.             case HSSFCell.CELL_TYPE_FORMULA: // 公式
  142.                 value = cell.getCellFormula() + "";
  143.                 break;
  144.             case HSSFCell.CELL_TYPE_BLANK: // 空值
  145.                 value = "";
  146.                 break;
  147.             case HSSFCell.CELL_TYPE_ERROR: // 故障
  148.                 value = "非法字符";
  149.                 break;
  150.             default:
  151.                 value = "未知类型";
  152.                 break;
  153.         }
  155.         return value;
  156.     }
  157. }

Web 接口-TaxPayerController

公共部分

  1. @Controller
  2. @RequestMapping(value = "/api")
  3. public class TaxPayerController extends BaseController {
  4.     //====================================================================//
  5.     /**
  6.      * 文件导入导出配置帮助类
  7.      */
  8.     @Resource
  9.     private FileConfig config;
  11.     /**
  12.      * 获取根据日期名称的 Excel 文件名名称
  13.      * @return  Excel 文件名名称
  14.      */
  15.     private String getExcelName(){
  16.        return DateUtils.format(new Date(),"yyyy-MM-dd-HHmmss")+".xlsx";
  17.     }
  19.     /**
  20.      * 获取 Resource 文件夹名称
  21.      * @return
  22.      */
  23.     private String getResourcePath(){
  24.         String resourcePath = this.getClass().getResource("/").getPath();
  25.         return resourcePath;
  26.     }
  28.     //........
  29. }

查询数据

  1. /**
  2.  * 获取免税合同供应商
  3.  * @param param 供应商信息
  4.  * @return 免税合同供应商列表
  5.  */
  6. @PostMapping("/exemptTaxSupplier")
  7. public ResponseVO<Page<TaxExemption>> exemptTaxSupplier(@RequestBody SupplierRequestData param){
  8.     //TODO 判断权限
  10.     return foreignPaymentConsumer.exemptTaxSupplier(param);
  11. }
测试结果

导出数据到 Excel

  1. /**
  2.  * 免税合同供应商-导出全部
  3.  * @return
  4.  */
  5. @GetMapping("/exemptTaxSupplierExport")
  6. public void exemptTaxSupplierExport(HttpServletResponse response) throws IOException {
  7.     //查询条件
  8.     SupplierRequestData param = new SupplierRequestData();
  9.     param.setSupplierId("");
  10.     param.setSupplierName("");
  11.     param.setCurrent(1);
  12.     param.setSize(5000);//默认获取 5000 条数据
  14.     //查询数据
  15.     ResponseVO<Page<TaxExemption>> responseVO = foreignPaymentConsumer.exemptTaxSupplier(param);
  17.     Page<TaxExemption> pageData  = responseVO.getData();
  18.     List<TaxExemption> listData = pageData.getRecords();
  20.     //Excel 模版路径
  21.     String readFilePath = getResourcePath() + config.getExcelTemplate() + config.getTaxExemptExport();
  22.     Workbook workbook= ExcelUtils.getWorkbook(readFilePath);
  23.     Sheet sheet = workbook.getSheetAt(0);
  25.     if(listData != null && listData.size() > 0){
  26.         for(Integer i=0;i<listData.size();i++){
  27.             TaxExemption data = listData.get(i);
  28.             Integer col= 0;
  29.             //供应商名称 contract_party
  30.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getContractParty());
  31.             //供应商编码 supplier_code
  32.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getSupplierCode());
  33.             //合同编号 contract_no
  34.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getContractNo());
  35.             //中文翻译名称 chinese_name
  36.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getChineseName());
  37.             //产品 product
  38.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getProduct());
  39.             //签订日期 signing_date
  40.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getSigningDate());
  41.             //开始日期 start_date
  42.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getStartDate());
  43.             //结束日期 end_time
  44.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getEndTime());
  45.             //合同类型 contract_type
  46.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getContractType());
  47.             //备注 remark
  48.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getRemark());
  49.             //负责商务 principal
  50.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getPrincipal());
  51.             //税局备案日期 records_date
  52.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getRecordsDate());
  53.             //免增值税备案有效期 tax_exemption_date
  54.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getTaxExemptionDate());
  55.             //创建时间 create_time
  56.             if(data.getCreateTime() != null){
  57.                 sheet.getRow(i+1).getCell(col++).setCellValue(DateUtils.format(data.getCreateTime(),"yyyy-MM-dd HH:mm:ss"));
  58.             }
  59.             //创建人 create_by
  60.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getCreateBy());
  61.             //创建人名称 create_name
  62.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getCreateName());
  63.             //修改时间 update_time
  64.             if(data.getUpdateTime() != null){
  65.                 sheet.getRow(i+1).getCell(col++).setCellValue(DateUtils.format(data.getUpdateTime(),"yyyy-MM-dd HH:mm:ss"));
  66.             }
  67.             //修改人 update_by
  68.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getUpdateBy());
  69.             //修改人名称 update_name
  70.             sheet.getRow(i+1).getCell(col++).setCellValue(data.getUpdateName());
  71.             //is_delete
  72.             //biz_time
  73.         }
  74.     }
  76.     String saveFileName = config.getTaxExemptPrefix() + getExcelName();
  77.     ExcelUtils.downLoadExcel(response,workbook,saveFileName);
  78. }
测试结果

免税合同供应商-2019-12-12-143548.xlsx

导入 Excel

  1. /**
  2.  * 免税合同供应商-上传替换
  3.  * @param file 上传文件
  4.  * @return 上传成功返回 true，否则返回 false
  5.  */
  6. @PostMapping("/exemptTaxSupplierImport")
  7. public ResponseVO exemptTaxSupplierImport(@RequestParam(value="filename") MultipartFile file){
  8.     ResponseVO<Boolean> responseVO = new ResponseVO<>(ResponseCode.SYSTEM_EXCEPTION);
  9.     responseVO.setData(false);
  11.     if(file.isEmpty()){
  12.         responseVO.setCode(ResponseCode.PARAM_INVALID.value());
  13.         responseVO.setMessage("文件为空！");
  14.         return responseVO;
  15.     }
  16.     //获取文件名
  17.     String fileName = file.getOriginalFilename();
  18.     if(!ExcelUtils.validateExcel(fileName)){
  19.         responseVO.setCode(ResponseCode.PARAM_INVALID.value());
  20.         responseVO.setMessage("必须上传 Excel 文件！");
  21.         return responseVO;
  22.     }
  24.     InputStream inputStream = null;
  25.     try{
  26.         inputStream = file.getInputStream();
  28.         Sheet sheet = ExcelUtils.getSheet(fileName,inputStream);
  30.         List<TaxExemption> listData = new ArrayList<>();
  32.         Iterator<Row> rowItr=sheet.rowIterator();//遍历全部非空行
  34.         //for(Row row : sheet){ //忽略表头 if(row.getRowNum() == 0) continue; //会获取所有行，包括空行
  35.         //while(rowItr.hasNext()){ Row row=rowItr.next();//会获取所有行，包括空行
  36.         int rowCount = sheet.getPhysicalNumberOfRows();
  37.         for(int i = 1;i<rowCount;i++){
  38.             Row row = sheet.getRow(i);
  40.             //如果得到的行是空的一行，可以根据row.getFirstCellNum()是否大于等于0 来判断
  41.             //该行为空那么：row.getFirstCellNum()=-1的，否则row.getFirstCellNum()=0
  42.             if(null == row) break;
  43.             if(row.getFirstCellNum() == -1) break;
  45.             Integer col = 0;//列序号
  47.             TaxExemption data = new TaxExemption();
  49.             //供应商名称 contract_party
  50.             data.setContractParty(ExcelUtils.getStringValue(row.getCell(col++)));
  51.             //供应商编码 supplier_code
  52.             data.setSupplierCode(ExcelUtils.getStringValue(row.getCell(col++)));
  53.             //合同编号 contract_no
  54.             data.setContractNo(ExcelUtils.getStringValue(row.getCell(col++)));
  55.             //中文翻译名称 chinese_name
  56.             data.setChineseName(ExcelUtils.getStringValue(row.getCell(col++)));
  57.             //产品 product
  58.             data.setProduct(ExcelUtils.getStringValue(row.getCell(col++)));
  59.             //签订日期 signing_date
  60.             data.setSigningDate(ExcelUtils.getStringValue(row.getCell(col++)));
  61.             //开始日期 start_date
  62.             data.setStartDate(ExcelUtils.getStringValue(row.getCell(col++)));
  63.             //结束日期 end_time
  64.             data.setEndTime(ExcelUtils.getStringValue(row.getCell(col++)));
  65.             //合同类型 contract_type
  66.             data.setContractType(ExcelUtils.getStringValue(row.getCell(col++)));
  67.             //备注 remark
  68.             data.setRemark(ExcelUtils.getStringValue(row.getCell(col++)));
  69.             //负责商务 principal
  70.             data.setPrincipal(ExcelUtils.getStringValue(row.getCell(col++)));
  71.             //税局备案日期 records_date
  72.             data.setRecordsDate(ExcelUtils.getStringValue(row.getCell(col++)));
  73.             //免增值税备案有效期 tax_exemption_date
  74.             data.setTaxExemptionDate(ExcelUtils.getStringValue(row.getCell(col++)));
  75.             //创建时间 create_time
  76.             //data.setCreateTime(row.getCell(col++).getDateCellValue());
  77.             //创建人 create_by
  78.             //data.setCreateBy(row.getCell(col++).getStringCellValue());
  79.             //创建人名称 create_name
  80.             //data.setCreateName(row.getCell(col++).getStringCellValue());
  81.             //修改时间 update_time
  82.             //data.setUpdateTime(row.getCell(col++).getDateCellValue());
  83.             //修改人 update_by
  84.             //data.setUpdateBy(row.getCell(col++).getStringCellValue());
  85.             //修改人名称 update_name
  86.             //data.setUpdateName(row.getCell(col++).getStringCellValue());
  87.             //is_delete
  88.             //biz_time
  90.             if(!StringUtil.isBlank(data.getContractParty())
  91.                     && !StringUtil.isBlank((data.getSupplierCode()))
  92.                     && !StringUtil.isBlank(data.getContractNo())){
  93.                 listData.add(data);
  94.             }
  95.         }
  97.         if(listData.size() > 0){
  98.             responseVO = foreignPaymentConsumer.replaceTaxExempt(listData);
  99.         }
  101.     }catch(IOException ex){
  102.         responseVO.setMessage(ex.getMessage());
  103.     }finally {
  104.         if(inputStream != null){
  105.             try {
  106.                 inputStream.close();
  107.             } catch (IOException e) {
  108.                 e.printStackTrace();
  109.                 return responseVO;
  110.             }
  111.         }
  112.     }
  114.     return responseVO;
  115. }
测试结果

测试数据 免税合同供应商-导入测试数据-测试.xLsX

下载 Excel 导入模版

  1. /**
  2.  * 免税合同供应商-下载导入模版
  3.  * @return 导入模版 Url
  4.  */
  5. @PostMapping("/downExemptTaxTemplate")
  6. public ResponseVO downExemptTaxTemplate(){
  7.     //http://localhost:8081/payment/ExcelTemplate/ExemptTaxSupplierImport.xlsx
  8.     ResponseVO<String> responseVO = new ResponseVO<>(ResponseCode.OK);
  9.     //responseVO.setData("/ExcelTemplate/ExemptTaxSupplierImport.xlsx");
  10.     responseVO.setData("/"+config.getExcelTemplate()+config.getTaxExemptImport());
  11.     return  responseVO;
  12. }
静态资源映射配置-bootstrap.properties
  1. #静态资源映射
  2. spring.mvc.static-path-pattern=/ExcelTemplate/*
  3. spring.resources.static-locations= classpath:ExcelTemplate/
测试结果

getRow getCell 空行返回 null 值问题

今天导出发现了一个问题，但导出的模板没有设置框线时，导出会报错

  1. if (listData != null && listData.size() > 0) {
  2.     for (Integer i = 0; i < listData.size(); i++) {
  3.         TaxExemption data = listData.get(i);
  4.         Integer col = 0;
  5.         //合同方（合作方名称，供应商名称） contract_party
  6.         sheet.getRow(i + 1).getCell(col++).setCellValue(data.getContractParty());
  1. 具体错误信息:java.lang.NullPointerException

设置了框线之后，才可以正常导出

过了一会之后，设置框线也报错了，也是服了，解决方案

  1. sheet.createRow(i + 1).createCell(col++).setCellValue(data.getContractParty());
相关附件 SpringBoot POI 导入，导出 Excel | 大小:0.03M | 下载