Java代码poi替换Excel表格内容

最新推荐文章于 2024-08-06 08:44:02 发布

MoLinJie

最新推荐文章于 2024-08-06 08:44:02 发布

阅读量4.7k

点赞数 2

分类专栏： java 文章标签： java poi excel 替换

本文链接：https://blog.csdn.net/qq_24434805/article/details/86523667

版权

java 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

所需jar包

xdocreport-2.0.1.jar
xmlbeans-5.3.0-rc1.jar
xmlgraphics-commons-2.2.jar

poi-3.9-20121203.jar
poi-examples-3.9-20121203.jar
poi-excelant-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-scratchpad-3.9-20121203.jar

支持版本

运行环境jdk1.6以上，文件后缀.xlsx

代码

package com.excel.text;

import java.io.*;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ChangeExcelData {

    public static void main(String[] args) throws IOException, EncryptedDocumentException, InvalidFormatException {
        String path = "D:\\HHKJ\\project\\test\\excel.xlsx";
        String outPath = "D:\\HHKJ\\project\\test\\excel1.xlsx";

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("county", "阳朔");
        params.put("patroltime", "2019年1月15日");
        params.put("address", "具体地点");
        params.put("slyz_type", "乱建");
        params.put("rectify", "2019年1月15日");
        params.put("rectifyrequest", "拆除乱建");
        params.put("sendtime", "2019年1月15日");

        new ChangeExcelData().replaceExcel(path,outPath,params);
    }


    public void replaceExcel(String inPath, String outPath, Map params) throws IOException, InvalidFormatException {

        InputStream is = new FileInputStream(new File(inPath));
        Workbook wb = WorkbookFactory.create(is);

        Sheet sheet = wb.getSheetAt(0);//获取Excel的工作表sheet，下标从0开始。
        int trLength = sheet.getLastRowNum();//获取Excel的行数
        for (int i = 0; i < trLength; i++) {
            Row row = sheet.getRow(i);//获取Excel的行，下标从0开始
            if (row == null) {//若行为空，则遍历下一行
                continue;
            }
            int minColIx = row.getFirstCellNum();
            int maxColIx = row.getLastCellNum();
            for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                Cell cell = row.getCell(colIx);//获取指定单元格，单元格从左到右下标从0开始
                String runText = cell.getStringCellValue();
                if (runText.equals("")){
                    continue;
                }
                System.out.println(cell);
                Matcher matcher = this.matcher(runText);
                if (matcher.find()) {
                    while ((matcher = this.matcher(runText)).find()) {
                        runText = matcher.replaceFirst(String.valueOf(params.get(matcher.group(1))));
                    }
                    cell.setCellValue(runText);
                }
            }
        }
        OutputStream out = new FileOutputStream(new File(outPath));
        wb.write(out);
        is.close();
        out.close();
    }

    /**
     * 正则匹配字符串
     * @param str
     * @return
     */
    private Matcher matcher(String str) {
        Pattern pattern = Pattern.compile("\\{(.+?)\\}", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(str);
        return matcher;
    }
}