Java实现Execl导出

看这篇博客之前需要先学习ExeclUitls的入门

点击链接进入

https://blog.youkuaiyun.com/kuyuyingzi/article/details/21472977

业务场景

根据页面选择的id，将对应的id对应的实体类传入后端接口，后端接口匹配对应的Execl模板文档，使用ExeclUtils进行映射，导出选择数据的文档。

技术实现

前端：Vue
后端：SpringBoot

前端

设置按钮

<el-button
     type="danger"
     @click="exportExecl"
     icon="el-icon-delete"
     buttonCode="TY007"
>导出</el-button>

导入

<scprit>
  	import axios from "axios";
</scprit>

导出文件事件

<scprit>
export default {
    //导出文件
    exportExecl() {
      console.log(this.ids);
      axios({
        method: "post",
        url: this.api.testExeclExport, //接口地址
        data: [
          {
            id: this.ids               //传入接口数据
          }
        ],
        responseType: "blob"
      })
        .then(response => {
          this.download(response.data, "测试");  //调用下载事件
        })
        .catch(error => {
          console.log("错误信息: " + JSON.stringify(error));
        });
    }
};
</scprit>

下载事件

 // 根据模板下载文件
download(data, projDocName) {
    if (!data) {
    	return;
    }
    // console.log("下载excel文档!!!!  ")
    let url = window.URL.createObjectURL(new Blob([data]));
    let link = document.createElement("a");
    link.style.display = "none";
    link.href = url;
    link.setAttribute("download", projDocName + ".xls");
    document.body.appendChild(link);
    link.click();
}

后端

POM文件

导入 execlUtils 相关jar

<!--POI-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency
    <!-- ExeclUtils工具类 started -->
    <dependency>
        <groupId>com.github.hxbkx</groupId>
        <artifactId>ExcelUtils</artifactId>
        <version>1.4.2</version>
    </dependency>

ExeclExportController

@RestController
@RequestMapping("/api/agt/execl")
@CrossOrigin
public class ExeclExportController {

    @Autowired
    ExeclExportService execlExportService;

    /**
     * 根据模板导出文件(测试)
     * @return
     */
    @PostMapping(value="/execlexport")
    public void  exportexecl(@RequestBody String body, HttpServletRequest request 
                             ,HttpServletResponse response){
        execlExportService.exportexecl(body,request,response);
    }

}

ExeclExportService

public interface ExeclExportService {

    /**
     * 根据模板导出文件（测试）
     * @param body
     * @param request
     * @param response
     */
    void exportexecl(String body, HttpServletRequest request, HttpServletResponse response);
}

ExeclExportServiceImpl

1、contractOutReasonService，projectFileTemplateMapper为相关业务实体类及mapper类，可替换成符合的类或者写死测试

2、fileTemplate.getFileContent()：该模板是存在数据库中的file_content字段，类型为longblob

3、FileNIOUtils：工具类，用于生成模板的临时文件路径

package com.bohee.pageoffice.service.impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.annotation.TableName;
import com.bohee.module.agt.contractoutreason.entity.ContractOutReason;
import com.bohee.module.agt.contractoutreason.service.IContractOutReasonService;
import com.bohee.module.system.sysfiletemplate.dao.SysProjectFileTemplateMapper;
import com.bohee.module.system.sysfiletemplate.entity.SysProjectFileTemplate;
import com.bohee.pageoffice.service.ExeclExportService;
import com.bohee.pageoffice.utils.FileNIOUtils;
import net.sf.excelutils.ExcelUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * <p>
 *    服务实现类：Execl导出
 * </p>
 *
 * @author syd
 * @since 2019-05-29
 */
@Service
public class ExeclExportServiceImpl implements ExeclExportService {

    @Autowired
    IContractOutReasonService contractOutReasonService;

    @Autowired
    SysProjectFileTemplateMapper projectFileTemplateMapper;

    @Override
    public void exportexecl(String body, HttpServletRequest request, HttpServletResponse response) {
        //---- 组装数据 started -----
        Object[] objects = assemblyDate(body);
        List details = new ArrayList<>();
        for(int i = 0;i<objects.length;i++){
            ContractOutReason obj = contractOutReasonService.getById(objects[i].toString());
            details.add(obj);
        }

        ExcelUtils.addValue("printDate", new Date());
        ExcelUtils.addValue("list", details);
        //---- 组装数据 end -----

        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            //模板路径
            //String path ="http://192.168.1.83/group1/M00/00/00/wKgBU1z30F-AJdOUAABMAP0SjPA352.xls";

            //查询对应的模板文件字节
            SysProjectFileTemplate projectFileTemplate = new SysProjectFileTemplate();
            projectFileTemplate.setTableName(getTableName(new ContractOutReason()));
            projectFileTemplate.setFileName("ContractOutReasonTemplate");
            projectFileTemplate.setTemplateName("ContractOutReasonTemplate");
            SysProjectFileTemplate fileTemplate = projectFileTemplateMapper.selectByUnionParameter(projectFileTemplate);
            //将字节转换为流
            byte[] fileContent = fileTemplate.getFileContent();
            InputStream stream = new ByteArrayInputStream(fileContent);

            // 生成模板的临时文件路径
            String tmpFileUrl = FileNIOUtils.copyByStream(stream, request, response);
            response.reset();//避免空行
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-disposition", "attachment; filename=" + new String(tmpFileUrl.getBytes("utf-8"), "iso-8859-1"));
            ExcelUtils.export(tmpFileUrl, outputStream);
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 功能：解析数据
     * @param body
     * @return
     */
    public Object[] assemblyDate(String body){
        JSONArray jsonArray = JSONArray.parseArray(body);
        JSONObject obj = jsonArray.getJSONObject(0);
        Object[] ids = obj.getJSONArray("id").toArray();
        return ids;
    }

    /**
     * 获取对象对应的表名
     * @param targetObject
     * @return
     */
    private String getTableName(Object targetObject) {
        Class<?> aClass = targetObject.getClass();
        return aClass.getAnnotation(TableName.class).value();
    }

}

FileNIOUtils工具类

采用NIO方式对文件的操作，主要功能根据文件流生成临时文件到临时目录下，并返回。

public class FileNIOUtils {


    /**
     * 功能：根据路径，产生一份新的临时文件到临时目录下，并返回文件路径
     * @param inputStream 流
     * @param request
     * @param response
     * @return
     * @throws IOException
     */
    public static String copyByStream(InputStream inputStream, HttpServletRequest request, HttpServletResponse response){
        Map<String, Object> map = CopyFile(inputStream, request, response);
        return map.get("path").toString();
    }
    
    
    /**
     * 功能：根据流，产生一份新的临时文件到临时目录下，并返回流
     * @param inputStream 流
     * @param request
     * @param response
     * @return
     * @throws IOException
     */
    public static OutputStream copyToSteam(InputStream inputStream, HttpServletRequest request, HttpServletResponse response) throws IOException {
        Map<String, Object> map = CopyFile(inputStream, request, response);
        return (OutputStream) map.get("stream");
    }
    
    
    /**
     * 根据路径，产生一份新的临时文件到临时目录下，并返回文件路径和流
     * @param inputStream
     * @param request
     * @param response
     * @return
     */
    public static Map<String,Object> CopyFile(InputStream inputStream, HttpServletRequest request, HttpServletResponse response){
        FileChannel outChannel = null;
        BufferedInputStream dis = null;
        FileOutputStream fos = null;
        String folderOut = null;
        try {
            //---生成临时文件 started ---
            //生成临时文件
            String folderOutId = java.util.UUID.randomUUID().toString();
            folderOut = System.getProperty("java.io.tmpdir") + folderOutId + ".xls";
            //创建文件
            File fileOut = new File(folderOut);
            fileOut.createNewFile();
            //----生成临时文件 end----

            dis = new BufferedInputStream(inputStream);
            fos = new FileOutputStream(folderOut);
            //获取通道
            outChannel = fos.getChannel();
            //分配指定大小的缓冲区
            ByteBuffer buf = ByteBuffer.allocate(2048);
            byte[] dst = new byte[buf.limit()];
            int bytesRead;
            //将通道中的数据存入缓冲区中
            while (-1 != (bytesRead = dis.read(dst, 0, dst.length))) {
                //切换读取数据的模式
                buf.flip();
                buf = ByteBuffer.wrap(dst);
                //将缓冲区中的数据写入通道中
                outChannel.write(buf);
                //清空缓冲区
                buf.clear();
            }
        }catch (IOException e){
            e.printStackTrace();
        }finally {
            //关闭流
            try {
                outChannel.close();
                dis.close();
                fos.close();
            }catch (IOException e){
                e.printStackTrace();
            }
        }
        Map<String,Object> map = new HashMap<>();
        map.put("path",folderOut);
        map.put("stream",fos);
        return map;
    }

EXECL模板文件

生成日期:${printDate}					
			合同外编码	             合同外原因	               备注
#foreach detail in ${list}		
		${detail.reasonCode}	${detail.reasonDescription}	${detail.description}
#end		


说明：

printDate与list,均在前面service已经加入--->
ExcelUtils.addValue("printDate", new Date());
ExcelUtils.addValue("list", details);