Replace text in Excel file autoshapes with Apache POI

Replace text in Excel file autoshapes with Apache POI

Premise

――It summarizes the contents that worked by trial and error --Excel files are intended for .xlsx format --In this article, I will replace one string as a simple example.

Limits

--It cannot be supported if each character is formatted differently in one character string. --Grouped auto shapes cannot be supported (it is OK if you ungroup in advance)

code

I also put the code on Github.

https://github.com/sadapon2008/autoshape-text-replace

src/main/java/sadapon2008/Application.java

package sadapon2008;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSimpleShape;
import org.apache.poi.xssf.usermodel.XSSFTextParagraph;
import org.apache.poi.xssf.usermodel.XSSFTextRun;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.main.CTTextBody;

public class Application {

	public static void main(String[] args) {
		//Checking and getting command line arguments
		if (args.length < 4) {
			System.exit(1);
		}
		//Character string to be replaced
		String textTarget = args[0];
		//Character string after replacement
		String textReplacement = args[1];
		//To be replaced.xlsx file
		String filenameSrc = args[2];
		//Created after replacement.xlsx file
		String filenameDest = args[3];
		
		try {
			//Copy the output file and then rewrite it
			Files.copy(Paths.get(filenameSrc), Paths.get(filenameDest), StandardCopyOption.REPLACE_EXISTING);
			OPCPackage pkg = OPCPackage.open(new FileInputStream(filenameDest));
			XSSFWorkbook workBook = new XSSFWorkbook(pkg);
			//Process by sheet
			int n = workBook.getNumberOfSheets();
			for (int i = 0; i < n; i++) {
				XSSFSheet sheet = workBook.getSheetAt(i);
				XSSFDrawing drawing = sheet.createDrawingPatriarch();
				//Process by auto shape
				for (XSSFShape shape : drawing.getShapes()) {
					if (!(shape instanceof XSSFSimpleShape)) {
						//Not compatible with grouped auto shapes
						continue;
					}
					//Handle for ungrouped autoshapes
					XSSFSimpleShape simpleShape = (XSSFSimpleShape)shape;
					CTTextBody textBody = simpleShape.getCTShape().getTxBody();
					if (null == textBody) {
						continue;
					}
					for (XSSFTextParagraph textParagraph : simpleShape.getTextParagraphs()) {
						for (XSSFTextRun textRun : textParagraph.getTextRuns()) {
							//Replace the text of the formatted unit
							//Therefore, it is not supported when the format setting is different for each character.
							textRun.setText(textRun.getText().replace(textTarget, textReplacement));
						}
					}
				}
			}
			FileOutputStream fileOut = new FileOutputStream(filenameDest);
			workBook.write(fileOut);
			fileOut.close();
			workBook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

Build & execution example

When building with gradle, use build.gradle as below.

build.gradle

apply plugin: 'java'
apply plugin:'application'

sourceCompatibility = '1.8'
targetCompatibility = '1.8'

mainClassName = 'sadapon2008.Application'

tasks.withType(JavaCompile) {
    options.encoding = 'UTF-8'
}

repositories {
    mavenCentral()
}

dependencies {
    compile group: 'org.apache.poi', name: 'poi', version: '3.15'
    compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.15'

    testCompile 'junit:junit:4.12'
}

run {
    if (project.hasProperty("appArgs")) {
        args Eval.me(appArgs)
    }
}

Then execute the following with gradlew.

./gradlew run -PappArgs="['Before replacement','After replacement','input.xlsx','output.xlsx']"