屏蔽ip网站,徐州最新消息今天,平面广告创意作品,南京工程网站建设功能#xff1a;后端使用Java POI填充Execl模板#xff0c;并返回前端下载
Execl模板如下#xff1a;
1. Java后端
功能#xff1a;填充模板EXECL,并返回前端
controller层
package org.huan.controller;import org.huan.dto.ExcelData;
import org.huan.util.ExcelT…功能后端使用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 ResponseEntitybyte[] generateExcel(RequestBody ExcelData excelData) {// Youll 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依赖 dependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion3.14/version/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml-schemas/artifactIdversion3.14/version/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi-scratchpad/artifactIdversion3.14/version/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi/artifactIdversion3.14/version/dependency2. VUE前端
功能 2.1 利用Vue过滤器实现 Vue数字金额转大写 2.2 点击按钮下载后端 EXECl
span{{model.balance | toChies(amount)}}/span
templatedivbutton clickdownloadExcelDownload Excel/button/div
/templatescript
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
