使用Aspose.Cells for java完成复杂Excel报表导出

转载

大新哥666 2021-07-07 13:42:48

[size=x-large]
使用Aspose.cells for java 完成对复杂EXCEL的导出功能
[/size]

使用Aspose.cells导出Excel非常方便，而且可以自定义EXCEL样式.
官方下载地址:[url]http://www.aspose.com/java/excel-component.aspx[/url]

我使用的是最新版本的7.4.3
需要jar包如下
aspose-cells-7.4.3.jar
bcprov-jdk16-146.jar
dom4j-1.6.1.jar
stax2-api-3.0.2.jar
woodstox-core-asl-4.1.1.jar

第一步需要自定义模板

定义模板
[img]http://dl.iteye.com/upload/attachment/0083/9006/1d96efa9-3ee5-3c8e-b43d-eec0ef5e4d0d.jpg[/img]

[color=green]
excel样式完全自定义,比用程序直接生成excel要方便.
在固定的位置使用 &=Person.name(bean)占位即可

[/color]

导出后

[img]http://dl.iteye.com/upload/attachment/0083/9008/b2430991-d5e3-34a7-8a6d-0dbc3cd35d17.jpg[/img]

1定义实体类,用来填充模板


package com.epkj.bean;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * 该实体类用于填充excel模板
 */
public class Person {

	private String name;

	private Date birthday;

	private int age;

	private double salary;

	private static SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

	public Person() {}

	public Person(String name, Date birthday, int age, double salary) {
		super();
		this.name = name;
		this.birthday = birthday;
		this.age = age;
		this.salary = salary;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	/**
	 * 值得注意的是 
	 * 由于不知道怎么在Aspose框架上处理导出时间格式的问题
	 * 只好在这里处理了,算是一个变通的解决方案吧
	 */
	public String getBirthday() {
		return format.format(birthday);
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public double getSalary() {
		return salary;
	}

	public void setSalary(double salary) {
		this.salary = salary;
	}
}


package com.epkj.bean;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class InitData {

	public static List<Person> getPersons() {
		List<Person> persons = new ArrayList<Person>();
		persons.add(new Person("张三", new Date(), 25, 4022.34));
		persons.add(new Person("李四", new Date(), 56, 3580.3));
		persons.add(new Person("王宇", new Date(), 45, 5454.34));
		persons.add(new Person("小强", new Date(), 57, 5876.24));
		persons.add(new Person("微微", new Date(), 54, 4022.68));
		persons.add(new Person("丽丽", new Date(), 12, 5878.12));
		persons.add(new Person("张娜", new Date(), 37, 5454.00));
		persons.add(new Person("王华", new Date(), 60, 2221));
		persons.add(new Person("阿超", new Date(), 55, 4587));
		persons.add(new Person("黑子", new Date(), 22, 9788));
		persons.add(new Person("小王", new Date(), 37, 2212.24));
		persons.add(new Person("剌剌", new Date(), 27, 8785.24));
		persons.add(new Person("荷花", new Date(), 18, 5454.34));
		return persons;
	}

	public static Map<String, Object> getHashMap() {
		Map<String, Object> data = new HashMap<String, Object>();
		data.put("name", "张三");
		data.put("age", 35);
		data.put("birthday", "1985-01-03");
		data.put("address", "辽宁沈阳");
		data.put("phone", "13988888888");
		return data;
	}
}


package com.epkj.cells;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.aspose.cells.Workbook;

/**
 * 自定义模板导出基类
 */
public abstract class DemoBase {

	protected abstract Workbook createReport(HttpServletRequest request) throws Exception;

	/**
	 * 设置HTTP响应头
	 * @param response 
	 * @param fileName
	 * @param formatSuffix
	 */
    protected void setResponseHeader(HttpServletResponse response, String fileName, String formatSuffix) {
        String contentType = "application/vnd.ms-excel";
        formatSuffix = formatSuffix.toLowerCase();
        if(formatSuffix.endsWith(".xlsx")
		        || formatSuffix.endsWith(".xlsb")
		        || formatSuffix.endsWith(".xlsm")
		        || formatSuffix.endsWith(".xltm")
		        || formatSuffix.endsWith(".xltx")) {
            contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        }
        response.setContentType(contentType);
        response.addHeader("content-disposition", "inline;filename=" + fileName);
    }

    /**
     * 向客户端返回excel
     * @param request
     * @param response
     * @param wb
     * @throws Exception
     */
    protected void sendReport(HttpServletRequest request,
			HttpServletResponse response, Workbook wb) throws Exception {
		wb.save(response.getOutputStream(), wb.getFileFormat());
	}
}


package com.epkj.cells;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.aspose.cells.Workbook;
import com.aspose.cells.WorkbookDesigner;
import com.epkj.bean.InitData;

/**
 * 根据自定义的Excel模板进行导出
 */
@Controller
@RequestMapping("/SmartMarkerController.do")
public class SmartMarkerController extends DemoBase {

	//存放Excel模板的位置
	private static final String TEMPLATE_FILE_PATH_PART = "/Designer/SmartMarkerDesigner.xls";

	/**
	 * 对客户端开发的方法
	 */
	@RequestMapping(params = "method=getReport")
	public Object getReport(HttpServletRequest request,
			HttpServletResponse response) {
		try {
			//创建工作薄
			Workbook wb = createReport(request);
			//设置输出响应头
            setResponseHeader(response, "SmartMarker.xls", ".xlsx");
            //向客户端输出
            sendReport(request, response, wb);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 向自定义模板中填充数据
	 */
	protected Workbook createReport(HttpServletRequest request) throws Exception {

		ServletContext sc = request.getSession().getServletContext();
        String template_file_path = sc.getRealPath(TEMPLATE_FILE_PATH_PART);

        //创建工作薄加载模板(SmartMarkerDesigner.xls)
		Workbook wb = new Workbook(template_file_path);
		createSmart(wb);

		return wb;
	}

	private static void createSmart(Workbook wb) throws Exception {
		WorkbookDesigner designer = new WorkbookDesigner();
		designer.setWorkbook(wb);
		designer.setDataSource("Person", InitData.getPersons());
		//这个框架默认不支持HashMap(查了好多资料都没找到直接支持的方法)
		//所以我实现了ICellsDataTable接口 使其可以支持HashMap
		//designer.setDataSource("Map", new HashMapDataTable(InitData.getHashMap()));
		designer.process(true);
	}

}

[size=xx-large]实现ICellsDataTable接口，支持HashMap[/size]


package com.epkj.cells;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import com.aspose.cells.ICellsDataTable;

/**
 * 实现ICellsDataTable接口
 * 以便支持对HashMap的支持
 */
public class HashMapDataTable implements ICellsDataTable {

	//数据集合
	private List<Map<String, Object>> dataList = null;

	//索引
	private int index;

	//存放dataList当中Map<String, Object>的key
	private String[] columns = null;

	public HashMapDataTable(Map<String, Object> data) {
		if(this.dataList == null) {
			this.dataList = new ArrayList<Map<String,Object>>();
		}
		dataList.add(data);
	}

	public HashMapDataTable(List<Map<String, Object>> data) {
		this.dataList = data;
	}

	/**
	 * 初始化方法
	 */
	@Override
	public void beforeFirst() {
		index = -1;
		columns = this.getColumns();
	}

	/**
	 * WorkbookDesigner自动调用
	 * 会将this.getColumns()方法所返回的列 按照顺序调用改方法
	 */
	@Override
	public Object get(int columnIndex) {
		if(index < 0 || index >= this.getCount()) {
            return null;
        }
		Map<String, Object> record = this.dataList.get(index);
		String columnName = this.columns[columnIndex];
		return record.get(columnName);
	}

	/**
	 * 根据columnName返回数据
	 */
	@Override
	public Object get(String columnName) {
		Map<String, Object> record = this.dataList.get(index);
		return record.get(columnName);
	}

	/**
	 * 获得列集合
	 */
	@Override
	public String[] getColumns() {
		Map<String, Object> temp = this.dataList.get(0);
		Set<Entry<String, Object>> entrys = temp.entrySet();
		List<String> columns = new ArrayList<String>();
		for (Entry<String, Object> e : entrys) {
			columns.add(e.getKey());
		}
		String[] s = new String[entrys.size()];
		columns.toArray(s);
		return s;
	}

	@Override
	public int getCount() {
		return this.dataList.size();
	}

	@Override
	public boolean next() {
		index += 1;
		if(index >= this.getCount())
        {
            return false;
        }
        return true;
	}

}