Asposeを使用します.Cells for java複雑なExcelレポートのエクスポートを完了

11199 ワード

Asposeを使用します.cells for java複雑なEXCELのエクスポート機能を完了
Asposeを使用します.cellsがExcelをエクスポートするのはとても便利で、EXCELスタイルをカスタマイズすることができます.
公式ダウンロードアドレス:
http://www.aspose.com/java/excel-component.aspx
私が使っているのは最新バージョンの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
最初のステップはカスタムテンプレートが必要です
テンプレートの定義
使用Aspose.Cells for java完成复杂Excel报表导出_第1张图片
Excelスタイルは完全にカスタマイズされており、プログラムで直接excelを生成するよりも便利です.一定の位置で使用&=Person.name(bean)占有でよい
エクスポート後
使用Aspose.Cells for java完成复杂Excel报表导出_第2张图片
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);
	}
	
}

ICellsDataTableインタフェースを実現し、HashMapをサポートする

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;
	}

}