利用Aspose-cell填充excel数据

一、需求

项目中需要将列表数据填充到excel模板中生成一个新的带有数据的excel文件

二、解决方式

通过aspose-cell处理，题外话：aspose收费，支持正版从我做起

1.配置excel模板文件

2.代码实现

package cc.test;

public class Data 
{
	/**
	 * 属性值需要和excel文件中配置的对应  &=key.属性值
	 */
	private String one;
	private String two;
	private String three;
	
	public String getOne() {
		return one;
	}
	public void setOne(String one) {
		this.one = one;
	}
	public String getTwo() {
		return two;
	}
	public void setTwo(String two) {
		this.two = two;
	}
	public String getThree() {
		return three;
	}
	public void setThree(String three) {
		this.three = three;
	}
}

package cc.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import com.aspose.cells.Workbook;
import com.aspose.cells.WorkbookDesigner;

import cn.hutool.core.io.IoUtil;

public class Test 
{
	public static void main(String[] args)
	{
		String key = "key";//对应excel文件中自己配置的key数据值
		String templateUrl = "F:\\test\\template.xlsx";//excel模板文件路径地址
		String targetUrl = "F:\\test\\target.xlsx";//目标文件路径
		
		List<Data> list = new ArrayList<Data>();
		//创建10条数据
		Data data = null;
		for (int i = 1; i <= 10; i++) 
		{
			data = new Data();
			data.setOne(String.valueOf(i));
			data.setTwo(String.valueOf(i));
			data.setThree(String.valueOf(i));
			list.add(data);
		}
		createExcelFile(key, list, targetUrl, templateUrl);
	}
	
	/**
	 * @param key
	 * @param list 数据列表
	 * @param targetUrl 目标路径
	 * @param templateUrl 模板文件路径
	 */
	public static void createExcelFile(String key, List<?> list, String targetUrl, String templateUrl)
	{
		Workbook wb = null;
		WorkbookDesigner designer = null;
		InputStream in = null;
		try
		{
			in = new FileInputStream(new File(templateUrl));
			wb = new Workbook(in);
			designer = new WorkbookDesigner();
			designer.setWorkbook(wb);
			designer.setDataSource(key, list);
			designer.process();
			wb.save(targetUrl);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			if (wb != null)
			{
				wb.dispose();
			}
			IoUtil.close(in);
		}
	}
}

3.jar包引入情况

三、效果展示