Trigger

"I want to output Excel in Java, but I don't want to specify it by cell number like Apache POI", "I want a slightly higher level library", I found a library called XlsMapper, so I tried it It was. Please point out any errors or implementation advice.

What is XlsMapper?

A Java library that maps Excel to Java. There used to be a famous Java library called XLSBeans that was made into a book, but its development seems to have stopped. Based on this ver1.1, it seems that individuals are gradually adding functions as another project XlsMapper. (Actually, I used XLS Beans for a while.)

What is more convenient than POI?

When accessing Excel using POI, for the list taken from the DB (if you know the information), Record 1. Set and format element 1 in the cell in the Mth column of the Nth record in Excel. Record 1. Set and format element 2 in the cell in the M + 1st column of the Nth record in Excel. ： Record 2. Set and format element 1 in the cell in the Mth column of N + 1st record in Excel. ：

I think that it is necessary to shift the numerical values of N and M by that amount if it is necessary to implement access such as, or if the column structure changes. By using this library, you can map Excel values to POJOs and write the mapped POJOs to Excel, just like an OR mapper, so you can reduce unnecessary processing descriptions. In addition, there is no need to bother to implement the format etc. because the annotation can be set to copy the previous record.

⇒ Official site

Tried environment

XlsMapper2.0
spring boot
Maven
lombok --java8 (8 or above required) --Apache POI 4.0.1 (3.17 or above required) * --Excel2010 (operation confirmed in 2016)

Addition POI 4.0.1 contains a bug. This is the reason why the workbook is corrupted when using OverOperation.Insert below. It is better to use the obediently recommended POI 3.17. (Thank you for your comment!)

Implementation

Maven Add the following to pom. * Omitted except for Excel.

`pom.xml`


<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.0.1</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.0.1</version>
</dependency>
<dependency>
	<groupId>com.github.mygreen</groupId>
	<artifactId>xlsmapper</artifactId>
	<version>2.0</version>
</dependency>

Stationery file

Prepare a book with the following table. The point is to enclose the table with ruled lines.

無題.png

SpringBootApplication

`DemoService.java`


@SpringBootApplication
public class DemoApplication {

	public static void main(String[] args) {
		//Just return the argument as a Map
		CommandLineParamsMap params = new CommandLineParamsMap(args);
		try (ConfigurableApplicationContext ctx = SpringApplication.run(DemoApplication.class, args)) {
			DemoService app = ctx.getBean(DemoService.class);
			app.execute(params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

Service

`DemoService.java`


@Service
@Slf4j
public class DemoService {
	private static final String TEMPLATE_FILE_PATH = "template\\User list_%s year%s month%s day creation.xlsx";
	private static final String OUTPUT_FILE_PATH = "C:\\test\\User list_%s year%s month%s day creation.xlsx";

	public String execute(CommandLineParamsMap params) {
		log.info("DemoService START");
		LocalDate outPutDate = getOutPutDate(params.getValue("date"));
		log.info("date:" + outPutDate);

		String outputPath = makeTargetPath(outPutDate);

		//Get table data
		List<UsingListRecord> target = getData();

		//Set each information in sheet
		UsingListSheet sheet = new UsingListSheet();
		sheet.setOutPutDate("Output date:" + outPutDate);
		sheet.setRecords(target);
		//Add total line of formula to last line
		sheet.addSummaryRecord();

		XlsMapper xlsMapper = new XlsMapper();
		try {
			//Write this (no loop required)!
			xlsMapper.save(
					new FileInputStream(TEMPLATE_FILE_PATH), //Template Excel file
					new FileOutputStream(outputPath), //Excel file to write
					sheet //Created data
			);
		} catch (XlsMapperException | IOException e) {
			throw new RuntimeException(e);
		}

		//Read the written table and try to output
		List<UsingListRecord> records = read(outputPath);
		records.forEach(r->log.info(r.toString()));

		log.info("DemoService END");
		return "###########success###########";
	}

	/**
	 *yyyymmdd → LocalDate object
	 * @param value
	 * @return
	 */
	private LocalDate getOutPutDate(String value) {
		int year = Integer.parseInt(value.substring(0, 4));
		int month = Integer.parseInt(value.substring(4, 6));
		int day = Integer.parseInt(value.substring(6, 8));
		return LocalDate.of(year, month, day);
	}

	//Data acquisition
	private List<UsingListRecord> getData() {
		//Actually bring it from DB
		List<UsingListRecord> list = new ArrayList<>();
		list.add(getSample("Anakin Skywalker", "Tatooine", "Human", 100, "Ahsoka Tano", 1));
		list.add(getSample("Padme Amidala", "Naboo", "Human", 100, null, 2));
		list.add(getSample("Luke Skywalker", "Police Masa", "Human", 100, "Kylo Ren", 3));
		list.add(getSample("Kylo Ren", "Chandrila", "Human", 100, null, 4));
		list.add(getSample("Ahsoka Tano", "Siri", "Togruta", 100, null, 5));
		list.add(getSample("Darth Maul", "Dasomia", "Zabrak", null, "Savage Opres", 6));
		list.add(getSample("Yoda", null, "Yodaの種族", 800, "Luke Skywalker", 7));
		return list;
	}

	//Returns a suitable sample record
	private UsingListRecord getSample(String name, String homeTown, String species, Integer ageAvg, String apprentice,
			int i) {
		LocalDate d1 = LocalDate.of(2019, 3, 1).plusDays(i);
		Date startDate = Date.from(d1.atStartOfDay(ZoneId.systemDefault()).toInstant());

		UsingListRecord record = new UsingListRecord();
		record.setUserName(name);
		record.setPrice(new BigDecimal(120000 + i));
		record.setTax(0.08d + i);
		record.setUsingStartDate(startDate);
		record.setHomeTown(homeTown);
		record.setSpecies(species);
		record.setAgeAvg(ageAvg);
		record.setApprentice(apprentice);
		return record;
	}

	private List<UsingListRecord> read(String targetPath) {
		XlsMapper xlsMapper = new XlsMapper();
		UsingListSheet sheet = null;
		try {
			sheet = xlsMapper.load(
					new FileInputStream(targetPath), //Excel file to read
					UsingListSheet.class //Annotated class.
			);
		} catch (XlsMapperException | IOException e) {
			throw new RuntimeException(e);
		}

		return sheet.getRecords();
	}

	private String makeTargetPath(LocalDate batchDate) {
		return String.format(OUTPUT_FILE_PATH, 
				batchDate.getYear(),
				batchDate.getMonthValue(),
				batchDate.getDayOfMonth()
				);
	}
}

Sheet A class that represents one sheet. Give @XlsSheet (name = "sheet name").

`UsingListSheet.java`


@Slf4j
@Data
@XlsSheet(name = "User list")
public class UsingListSheet {

	private String outPutDate;

	@XlsHorizontalRecords(tableLabel = "User list", bottom = 3)
	@XlsRecordOption(overOperation = OverOperation.Copy)
	private List<UsingListRecord> records;

	public void addSummaryRecord() {
		if (records == null) {
			this.records = new ArrayList<>();
		}

		UsingListRecord record = new UsingListRecord();
		//Pass your own instance
		record.setParent(this);

		record.setUserName("total");
		records.add(record);
	}

	/**
	 *After writing the table, try setting the output date with POI.
	 * @param sheet
	 */
	@XlsPostSave
	public void aa(final Sheet sheet) {
		Cell cell = POIUtils.getCell(sheet, 7, 0);
		CellStyle style=cell.getCellStyle();
		cell.setCellValue(outPutDate);
		cell.setCellStyle(style);
	}
}

＠XlsHorizontalRecords Set it for the type of table with the heading above, like Excel attached below. Specify the title of the table with tableLabel. The bottom specifies how far the actual table is from the tableLabel. If there is a table heading directly under tableLabel, it is not necessary to specify it.

＠XlsRecordOption(overOperation = OverOperation.Copy) Specify what to do when there are more rows of data than the number of rows specified in the template (determined by the ruled line). In the case of OverOperation.Copy, the format of the line one level above is copied and a line is added. Workbook corruption when using OverOperation.Insert. (See the environment I tried above for the reason.)

＠XlsPostSave The method with this will be executed automatically after writing the file. It can also be assigned to the method of the Record class, and the order is @XlsPostSave of Sheet → @XlsPostSave of Record. There are many other things such as @XlsPreSave, so see below. 7. Managing Lifecycle Events

Record A class that represents one record of a table to be placed on an Excel sheet.

`UsingListRecord.java`


@Data
public class UsingListRecord {
	//Mapped location information
	private Map<String, CellPosition> positions;
	//Parent bean information
	private UsingListSheet parent;

	@XlsColumn(columnName = "User")
	private String userName;
	@XlsColumn(columnName = "Fee")
	@XlsFormula(methodName = "getSumFormula", primary = false)
	private BigDecimal price;
	@XlsColumn(columnName = "sales tax rate")
	private Double tax;
	@XlsColumn(columnName = "Start date of use")
	@XlsDateTimeConverter(excelPattern = "yyyy/m/d")
	private Date usingStartDate;
	@XlsColumn(columnName = "Birthplace")
	@XlsDefaultValue(value="--", cases=ProcessCase.Save)
	private String homeTown;
	@XlsColumn(columnName = "Race")
	private String species;
	@XlsColumn(columnName = "Average life")
	private Integer ageAvg;
	@XlsColumn(columnName = "Disciple")
	@XlsDefaultValue(value="--", cases=ProcessCase.Save)
	private String apprentice;

	//Assemble the total formula
	public String getSumFormula(Point point) {

		//Output formula only when hometown is total
		if (!userName.equals("total")) {
			return null;
		}

		//Record size (value looking through the record row for totals)
		final int dataSize = parent.getRecords().size() - 1;

		//Column name
		final String colAlpha = CellReference.convertNumToColString(point.x);

		//Start of total value/End line number
		final int startRowNumber = point.y - dataSize + 1;
		final int endRowNumber = point.y;

		return String.format("SUM(%s%d:%s%d)", colAlpha, startRowNumber, colAlpha, endRowNumber);

	}
}

＠XlsFormula By specifying primary = false, if there is a value in the corresponding field, it will be given priority. If true, the formula always takes precedence.

＠XlsDefaultValue Set the default value for NULL with value. If cases = ProcessCase.Save is specified, the default value will be set only for writing.

The default value must match the mapping destination type. Otherwise, TypeBindException will occur ("-" cannot be set for life expectancy etc.)

Run

Enter "date = 20190312" in the argument in the execution configuration of Springboot app, apply and execute.

Execution result

Output excel

If you look at the formula window, the formula is also output properly.

無題2.png

log

Log when reading the table after writing. The acquisition is done properly.

`Log (excerpt)`


com.example.demo.service.DemoService : date:2019-03-12
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A5, price=B5, tax=C5, usingStartDate=D5, homeTown=E5, species=F5, ageAvg=G5, apprentice=H5}, parent=null, userName=Anakin Skywalker, price=120001, tax=1.08, usingStartDate=Sat Mar 02 00:00:00 JST 2019, homeTown=Tatooine, species=Human, ageAvg=100, apprentice=Ahsoka Tano)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A6, price=B6, tax=C6, usingStartDate=D6, homeTown=E6, species=F6, ageAvg=G6, apprentice=H6}, parent=null, userName=Padme Amidala, price=120002, tax=2.08, usingStartDate=Sun Mar 03 00:00:00 JST 2019, homeTown=Naboo, species=Human, ageAvg=100, apprentice=--)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A7, price=B7, tax=C7, usingStartDate=D7, homeTown=E7, species=F7, ageAvg=G7, apprentice=H7}, parent=null, userName=Luke Skywalker, price=120003, tax=3.08, usingStartDate=Mon Mar 04 00:00:00 JST 2019, homeTown=Police Masa, species=Human, ageAvg=100, apprentice=Kylo Ren)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A8, price=B8, tax=C8, usingStartDate=D8, homeTown=E8, species=F8, ageAvg=G8, apprentice=H8}, parent=null, userName=Kylo Ren, price=120004, tax=4.08, usingStartDate=Tue Mar 05 00:00:00 JST 2019, homeTown=Chandrila, species=Human, ageAvg=100, apprentice=--)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A9, price=B9, tax=C9, usingStartDate=D9, homeTown=E9, species=F9, ageAvg=G9, apprentice=H9}, parent=null, userName=Ahsoka Tano, price=120005, tax=5.08, usingStartDate=Wed Mar 06 00:00:00 JST 2019, homeTown=Siri, species=Togruta, ageAvg=100, apprentice=--)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A10, price=B10, tax=C10, usingStartDate=D10, homeTown=E10, species=F10, ageAvg=G10, apprentice=H10}, parent=null, userName=Darth Maul, price=120006, tax=6.08, usingStartDate=Thu Mar 07 00:00:00 JST 2019, homeTown=Dasomia, species=Zabrak, ageAvg=null, apprentice=Savage Opres)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A11, price=B11, tax=C11, usingStartDate=D11, homeTown=E11, species=F11, ageAvg=G11, apprentice=H11}, parent=null, userName=Yoda, price=120007, tax=7.08, usingStartDate=Fri Mar 08 00:00:00 JST 2019, homeTown=--, species=Yodaの種族, ageAvg=800, apprentice=Luke Skywalker)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A12, price=B12, tax=C12, usingStartDate=D12, homeTown=E12, species=F12, ageAvg=G12, apprentice=H12}, parent=null, userName=total, price=840028, tax=null, usingStartDate=null, homeTown=--, species=null, ageAvg=null, apprentice=--)

Afterword

I noticed that it was implemented, but it's nice that the close process is unnecessary. This time, it became a little complicated because the process to output the formula (total line) is included in the last record, but if there is no formula, the method is unnecessary for both Sheet and Record classes, and the annotation is reduced and it is quite simple Become.

[JAVA] Output Excel with formulas with XlsMapper

Trigger

What is XlsMapper?

What is more convenient than POI?

Tried environment

Implementation

pom.xml

Stationery file

DemoService.java

DemoService.java

UsingListSheet.java

UsingListRecord.java

Run

Execution result

Output excel

log

Log (excerpt)

Afterword

`pom.xml`

`DemoService.java`

`DemoService.java`

`UsingListSheet.java`

`UsingListRecord.java`

`Log (excerpt)`