Java代码poi替换Excel表格内容

所需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;}
}

运行结果

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！