excel导入模版替换值后导出（java版本）

最新推荐文章于 2023-12-25 11:25:22 发布

爱竹林下的天

最新推荐文章于 2023-12-25 11:25:22 发布

阅读量295

点赞数

文章标签： excel java 开发语言

本文链接：https://blog.csdn.net/qq_30835639/article/details/131391931

版权

package com.tianyidacome.common.utils.excel;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.xssf.usermodel.XSSFName;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.ResourceUtils;

import cn.hutool.core.util.StrUtil;

public class ExcelUtil {

	/**
     * 模版式EXCEL值替换
     * @return
     */
    public static String updateExcel(String localUrl, String fileName, Map<String, String> params) {
    	fileName = fileName==null?String.valueOf(new Date().getTime() / 1000):fileName;
    	String localPath = null;
        try {
	    	File f = new File(localUrl);
	    	InputStream inputStream = new FileInputStream(f);
	    	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
	    	XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); //如果是.xlsx文件使用这个
	    	List<XSSFName> allNames = xssfWorkbook.getAllNames();
	    	for (XSSFName xssfName : allNames) {
	    		String ref = xssfName.getRefersToFormula();
	    	    AreaReference area = new AreaReference(ref, SpreadsheetVersion.EXCEL2007);
	    	    String string = params.get(xssfName.getNameName());
	    	    if(StrUtil.isNotBlank(string)) {
	    	    	xssfSheet.getRow(area.getFirstCell().getRow()).getCell(area.getFirstCell().getCol()).setCellValue(string);
	    	    }
			}
	    	ByteArrayOutputStream bos = new ByteArrayOutputStream();
	    	/* 生成临时文件 */
	        FileOutputStream out = null;
	        try {
	            File pathurl = new File(ResourceUtils.getURL("classpath:").getPath());
	            String path = pathurl + "/static/" + fileName + ".xlsx";
	            localPath = path;
	            out = new FileOutputStream(localPath);
	            xssfWorkbook.write(out);
	        } catch (IOException e) {
	            e.printStackTrace();
	        } finally {
	            try {
	                if (out != null) {
	                	xssfWorkbook.close();
	                    out.flush();
	                    out.close();
	                }
	            } catch (IOException e) {
	                e.printStackTrace();
	            }
	        }
    	}catch(Exception e) {
    	    e.printStackTrace();
    	}  
        return localPath;
    }

}

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>

		<!-- Spring依赖1：spring-core -->
		<dependency>
		  <groupId>org.springframework</groupId>
		  <artifactId>spring-core</artifactId>
		</dependency>

public static void main(String[] args) {
    	Map<String, String> params = new HashMap<>();
    	params.put("enterpriseName", "测试");
    	params.put("companyAddress", "测试地址");
    	updateExcel("E:\\audit.xlsx", "test", params);
	}