POI生成Excel并下载

后台Controller:

package com.bjbz.fssc._business.controller;

import java.io.OutputStream;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.bjbz.fssc.base.ResultData;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;

@Api(value = "TestController", description = "测试")
@RestController
@RequestMapping("/testController")
public class TestController {

    @ApiOperation(value = "下载模板", notes = "下载初始模板", response = ResultData.class)
    @RequestMapping(value = "/downLoadTem", method = RequestMethod.POST)
    public ResultData downLoadTem (HttpServletRequest request,HttpServletResponse response) throws Exception{
            // 新建Excel的文档对象
            HSSFWorkbook hsb = new HSSFWorkbook();

            // 新建sheet页
            HSSFSheet sheet0 = hsb.createSheet("初始信息");
            HSSFSheet sheet1 = hsb.createSheet("各项");
            HSSFSheet sheet2 = hsb.createSheet("公司信息");
            HSSFSheet sheet3 = hsb.createSheet("客户信息");

            // 设置格子单元公共样式
            HSSFCellStyle  cellStyle = hsb.createCellStyle(); 
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

            this.setSheet0(sheet0, cellStyle);

            String fileName = new String("雇员账户信息模版".getBytes(), "iso-8859-1");

            // 输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+fileName+".xls");
            response.setContentType("application/msexcel");
            hsb.write(output);
            output.close();
            return new ResultData();
    }

    // 设置sheet0
    public void setSheet0 (HSSFSheet sheet0,HSSFCellStyle cellStyle) throws Exception {
            // 创建第一行
            HSSFRow row0 = sheet0.createRow(0);
            // 创建第一行的单元格
            HSSFCell cow00 = row0.createCell(0);
            // 设置单元格内容和样式
            cow00.setCellValue("雇员账户信息模版");
            cow00.setCellStyle(cellStyle);
            // 合并单元格
            sheet0.addMergedRegion(new CellRangeAddress(0, 0, 0, 68));
            // 创建第二行
            HSSFRow row1 = sheet0.createRow(1);
            HSSFCell cow10 = row1.createCell(0);
            HSSFCell cow15 = row1.createCell(5);
            cow10.setCellValue("基础信息");
            cow10.setCellStyle(cellStyle);
            cow15.setCellValue("账户类别");
            cow15.setCellStyle(cellStyle);
            sheet0.addMergedRegion(new CellRangeAddress(1, 2, 0, 4));
            sheet0.addMergedRegion(new CellRangeAddress(1, 1, 5, 68));
            // 创建第三行
            HSSFRow row2 = sheet0.createRow(2);
            // 新建单元格
            HSSFCell cow25 = row2.createCell(5);
            HSSFCell cow223 = row2.createCell(23);
            HSSFCell cow230 = row2.createCell(30);
            HSSFCell cow248 = row2.createCell(48);
            HSSFCell cow264 = row2.createCell(64);
            HSSFCell cow268 = row2.createCell(68);
            cow25.setCellValue("派遣(单位：元)");
            cow223.setCellValue("薪酬代发-进账(单位：元)");
            cow230.setCellValue("外包(单位：元)");
            cow248.setCellValue("社保挂靠(单位：元)");
            cow264.setCellValue("主营业务收入(单位：元)");
            cow268.setCellValue("应交税金(单位：元)");
            cow25.setCellStyle(cellStyle);
            cow223.setCellStyle(cellStyle);
            cow230.setCellStyle(cellStyle);
            cow248.setCellStyle(cellStyle);
            cow264.setCellStyle(cellStyle);
            cow268.setCellStyle(cellStyle);
            // 合并单元格
            sheet0.addMergedRegion(new CellRangeAddress(2, 2, 5, 22));
            sheet0.addMergedRegion(new CellRangeAddress(2, 2, 23, 29));
            sheet0.addMergedRegion(new CellRangeAddress(2, 2, 30, 47));
            sheet0.addMergedRegion(new CellRangeAddress(2, 2, 48, 63));
            sheet0.addMergedRegion(new CellRangeAddress(2, 2, 64, 67));
            // 创建第四行
            HSSFRow row3 = sheet0.createRow(3);
            String[] className = {"雇员编号","雇员姓名","证件号码","所属公司","所属客户","派遣-进账","派遣-工资,奖金,福利","派遣-养老-个人","派遣-医疗-个人","派遣-失业-个人",
                    "派遣-住房公积-个人","派遣-养老-企业","派遣-医疗-企业","派遣-工伤","派遣-生育","派遣-失业-企业","派遣-住房公积-企业","派遣-意外","派遣-管理费",
                    "派遣-差异","派遣-税金","派遣-个税","派遣-其他应付款",
                    "薪酬代发-进账","薪酬代发-工资","薪酬代发-质保金","薪酬代发-管理费","薪酬代发-差异","薪酬代发-税金", "薪酬代发-个税",
                    "外包-进账","外包-工资,奖金,福利", "外包-养老-个人","外包-医疗-个人","外包-失业-个人","外包-住房公积-个人",
                    "外包-意外","外包-养老-企业","外包-医疗-企业","外包-工伤","外包-生育","外包-失业-企业","外包-住房公积-企业","外包-管理费",
                    "外包-差异","外包-税金", "外包-个税","外包-滞纳金",
                    "社保挂靠-进账", "社保挂靠-养老-个人","社保挂靠-医疗-个人","社保挂靠-失业-个人","社保挂靠-住房公积-个人","社保挂靠-意外",
                    "社保挂靠-养老-企业","社保挂靠-医疗-企业","社保挂靠-工伤","社保挂靠-生育","社保挂靠-失业-企业","社保挂靠-住房公积-企业",
                    "社保挂靠-其他","社保挂靠-差异","社保挂靠-管理费","社保挂靠-税金","主营业务收入-派遣收入","主营业务收入-工程收入","主营业务收入-外包收入",
                    "主营业务收入-社保挂靠收入","应交税金-应交个人所得税"
            };
            for (int i = 0; i < className.length; i++ ) {
                HSSFCell cell3i = row3.createCell(i);
                cell3i.setCellValue(className[i]);
                cell3i.setCellStyle(cellStyle);
            }
    }

    // 设置sheet1
    public void setSheet1 (HSSFSheet sheet1,HSSFCellStyle  cellStyle) throws Exception {

    }

    // 设置sheet2
    public void setSheet2 (HSSFSheet sheet2,HSSFCellStyle  cellStyle) throws Exception {

    }

    // 设置sheet3
    public void setSheet3 (HSSFSheet sheet3,HSSFCellStyle  cellStyle) throws Exception {

    }
}

前端页面:

<template>
  <div id="">
    <el-button @click="downTemplet">下载模板</el-button>

    <form ref="downloadForm" action="" method="post">
      <input type="hidden" name="token" :value="token">
    </form>
  </div>
</template>

<script>
  import global from '../common/global'
  export default {
    mounted: function () {
      this.token = sessionStorage.getItem('tokende')
    },
    data () {
      return {
        token: ''
      }
    },
    methods: {
      downTemplet () {
        this.$refs.downloadForm.action = global.Host + '/fssc/testController/downLoadTem'
        this.$refs.downloadForm.submit()
      }
    }
  }
</script>

<style scoped>

</style>