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")@ResponseBodypublic 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 objectExcelTemplateFiller.execl(templateFilePath, outputFilePath, excelData);try {// Read the generated filePath path = Paths.get(outputFilePath);byte[] fileContent = Files.readAllBytes(path);// Create a ResponseEntity with the file content as bodyHttpHeaders 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 endpointmethod: 'POST',responseType: 'blob', // Specify response type as blob to handle binary datadata: 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 heredocument.body.appendChild(link);link.click();}).catch((error) => {console.error('Error downloading Excel:', error);});},
};</script>