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']"

Recommended Posts

Replace text in Excel file autoshapes with Apache POI
Manipulate Excel with Apache POI
Apache POI Excel in Kotlin
[Java] Handle Excel files with Apache POI
[Java] Creating an Excel file using Apache POI
[Apache POI] Corresponding Excel version
Excel operation using Apache POI
Excel output using Apache POI!
EXCEL file update sample with JAVA
With dbunit 2.6.0, poi comes in without permission
Read xlsx file in Java with Selenium
Read a string in a PDF file with Java
Text extraction in Java from PDF with pdfbox-2.0.8