Java填充Execl模板并返回前端下载

功能：后端使用Java POI填充Execl模板，并返回前端下载

Execl模板如下：

1. Java后端

功能：填充模板EXECL,并返回前端

controller层

package org.huan.controller;

import org.huan.dto.ExcelData;
import org.huan.util.ExcelTemplateFiller;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.ResponseBody;

import java.io.File;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;

@Controller
public class ExcelController {

    @PostMapping("/generateExcel")
    @ResponseBody
    public ResponseEntity<byte[]> generateExcel(@RequestBody ExcelData excelData) {
        // You'll need to modify the parameters and logic here based on your object and requirements

        // For example:
        String templateFilePath = "C:\\Users\\lenovo\\Desktop\\aa\\a.xlsx";
        String outputFilePath = "C:\\Users\\lenovo\\Desktop\\aa\\output.xlsx";

        // Generate Excel file based on the received object
        ExcelTemplateFiller.execl(templateFilePath, outputFilePath, excelData);

        try {
            // Read the generated file
            Path path = Paths.get(outputFilePath);
            byte[] fileContent = Files.readAllBytes(path);

            // Create a ResponseEntity with the file content as body
            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
            headers.setContentDispositionFormData("attachment", "output.xlsx");
            headers.setContentLength(fileContent.length);

            return ResponseEntity.ok().headers(headers).body(fileContent);
        } catch (Exception e) {
            e.printStackTrace();
            return ResponseEntity.badRequest().body(null);
        }
    }
}

ExcelTemplateFiller POI填充表格

package org.huan.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.huan.dto.ExcelData;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;

public class ExcelTemplateFiller {

    public static void main(String[] args) {
        String templateFilePath = "C:\\Users\\lenovo\\Desktop\\aa\\a.xlsx";
        String outputFilePath = "C:\\Users\\lenovo\\Desktop\\aa\\output.xlsx";
        //execl(templateFilePath, outputFilePath);
    }

    public static void execl(String templateFilePath, String outputFilePath, ExcelData excelData) {

        try (InputStream templateInputStream = Files.newInputStream(Paths.get(templateFilePath));
             Workbook workbook = new XSSFWorkbook(templateInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            
            //全 称
            sheet.getRow(8).getCell(27).setCellValue(excelData.getFullName());
            //账号
            sheet.getRow(10).getCell(27).setCellValue(excelData.getAccountNumber());
            //开户机构
            sheet.getRow(12).getCell(27).setCellValue(excelData.getAccountInstitution());
            //人民币(大写)
            sheet.getRow(14).getCell(7).setCellValue(excelData.getRmbInWords());

            //十 亿 千 百 十 万 千 百 十 元 角 分
            // 十亿, 亿, 千万, 百万, 十万, 万, 千, 百, 十, 元, 角, 分
            Row row = sheet.getRow(15);
            row.getCell(30).setCellValue(excelData.getBillion());
            row.getCell(31).setCellValue(excelData.getHundredMillion());
            row.getCell(32).setCellValue(excelData.getTenMillion());
            row.getCell(33).setCellValue(excelData.getMillion());
            row.getCell(34).setCellValue(excelData.getHundredThousand());
            row.getCell(35).setCellValue(excelData.getTenThousand());
            row.getCell(36).setCellValue(excelData.getThousand());
            row.getCell(37).setCellValue(excelData.getHundred());
            row.getCell(38).setCellValue(excelData.getTen());
            row.getCell(39).setCellValue(excelData.getYuan());
            row.getCell(40).setCellValue(excelData.getJiao());
            row.getCell(41).setCellValue(excelData.getFen());

            //用途
            sheet.getRow(16).getCell(7).setCellValue(excelData.getPurpose());
            //备注
            sheet.getRow(17).getCell(7).setCellValue(excelData.getRemark());
            try (FileOutputStream fileOutputStream = new FileOutputStream(outputFilePath)) {
                workbook.write(fileOutputStream);
            }
            System.out.println("Data has been filled into the Excel template successfully!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

实体类

package org.huan.dto;

import lombok.Data;

@Data
public class ExcelData {
    private String fullName;
    private String accountNumber;
    private String accountInstitution;
    private String rmbInWords;

    private String billion;
    private String hundredMillion;
    private String tenMillion;
    private String million;
    private String hundredThousand;
    private String tenThousand;
    private String thousand;
    private String hundred;
    private String ten;
    private String yuan;
    private String jiao;
    private String fen;

    private String purpose;
    private String remark;

}

pom依赖

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.14</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml-schemas</artifactId>
      <version>3.14</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-scratchpad</artifactId>
      <version>3.14</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.14</version>
    </dependency>

2. VUE前端

功能：
2.1 利用Vue过滤器实现 Vue数字金额转大写
2.2 点击按钮下载后端 EXECl

<span>{{model.balance | toChies(amount)}}</span>
<template>
  <div>
    <button @click="downloadExcel">Download Excel</button>
  </div>
</template>

<script>
export default {
  data() {
    return {
     excelData: {
        fullName: 'John Doe',
        accountNumber: '1234567890',
        accountInstitution: 'ABC Bank',
        rmbInWords: 'One Thousand Yuan',
        billion: '1',
        hundredMillion: '1',
        tenMillion: '1',
        million: '1',
        hundredThousand: '1',
        tenThousand: '1',
        thousand: '1',
        hundred: '1',
        ten: '1',
        yuan: '1',
        jiao: '1',
        fen: '1',
        purpose: 'Purchase',
        remark: 'No remarks',
      },
    };
    };
  },

filters:{
  toChies(amount){
    // 汉字的数字
    const cnNums = ["零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖"];
    // 基本单位
    const cnIntRadice = ["", "拾", "佰", "仟"];
    // 对应整数部分扩展单位
    const cnIntUnits = ["", "万", "亿", "兆"];
    // 对应小数部分单位
    const cnDecUnits = ["角", "分"];
    // 整数金额时后面跟的字符
    const cnInteger = "整";
    // 整型完以后的单位
    const cnIntLast = "元";
    // 最大处理的数字
    const maxNum = 9999999999999999.99;
    // 金额整数部分
    let integerNum;
    // 金额小数部分
    let decimalNum;
    // 输出的中文金额字符串
    let chineseStr = "";
    // 分离金额后用的数组，预定义
    let parts;
    if (amount === "") {
      return "";
    }
    amount = parseFloat(amount);
    if (amount >= maxNum) {
      // 超出最大处理数字
      return "";
    }
    if (amount === 0) {
      chineseStr = cnNums[0] + cnIntLast + cnInteger;
      return chineseStr;
    }
    // 转换为字符串
    amount = amount.toString();
    if (amount.indexOf(".") === -1) {
      integerNum = amount;

      decimalNum = "";
    } else {
      parts = amount.split(".");
      integerNum = parts[0];
      decimalNum = parts[1].substr(0, 4);
    }
    // 获取整型部分转换
    if (parseInt(integerNum, 10) > 0) {
      let zeroCount = 0;
      const IntLen = integerNum.length;
      for (let i = 0; i < IntLen; i++) {
        const n = integerNum.substr(i, 1);
        const p = IntLen - i - 1;
        const q = p / 4;
        const m = p % 4;
        if (n === "0") {
          zeroCount++;
        } else {
          if (zeroCount > 0) {
            chineseStr += cnNums[0];
          }
          // 归零
          zeroCount = 0;
          //alert(cnNums[parseInt(n)])
          chineseStr += cnNums[parseInt(n)] + cnIntRadice[m];
        }
        if (m === 0 && zeroCount < 4) {
          chineseStr += cnIntUnits[q];
        }
      }
      chineseStr += cnIntLast;
    }
    // 小数部分
    if (decimalNum !== "") {
      const decLen = decimalNum.length;
      for (let i = 0; i < decLen; i++) {
        const n = decimalNum.substr(i, 1);
        if (n !== "0") {
          chineseStr += cnNums[Number(n)] + cnDecUnits[i];
        }
      }
    }
    if (chineseStr === "") {
      chineseStr += cnNums[0] + cnIntLast + cnInteger;
    } else if (decimalNum === "") {
      chineseStr += cnInteger;
    }
    return chineseStr;
  }
},

 
  methods: {
		const formattedAmount = this.$options.filters.toChies(this.excelData.rmbInWords);
		 downloadExcel() {
 		  this.excelData = { rmbInWords: formattedAmount ...};
	      axios({
	        url: 'http://your-backend-url/generateExcel', // Replace with your backend endpoint
	        method: 'POST',
	        responseType: 'blob', // Specify response type as blob to handle binary data
	        data: this.excelData,
	      })
	        .then((response) => {
	          const url = window.URL.createObjectURL(new Blob([response.data]));
	          const link = document.createElement('a');
	          link.href = url;
	          link.setAttribute('download', 'output.xlsx'); // Set the file name here
	          document.body.appendChild(link);
	          link.click();
	        })
	        .catch((error) => {
	          console.error('Error downloading Excel:', error);
	        });
	    },
};

</script>