基于Jxls 生成excel

以下是一个简单的demo.

1.引入相关jar包:jxls-core-1.0.jar,commons-jexl-2.1.1.jar可分别在jxls和apache的官网上下载。

2.建立映射的entity:

package com.desmond.exporter.model;

public class UserModel {

	private String userName;
	private String firstName;
	private String lastName;
	private String address;

	private int age;
	private double height;
	private double weight;

	public UserModel() {
		super();
	}

	public UserModel(String userName, String firstName, String lastName,
			String address, int age, double height, double weight) {
		super();
		this.userName = userName;
		this.firstName = firstName;
		this.lastName = lastName;
		this.address = address;
		this.age = age;
		this.height = height;
		this.weight = weight;
	}

	public String getUserName() {
		return "Name"+ Math.random()*1000;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getFirstName() {
		return "FirN"+ Math.random()*1000;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return "LasN"+ Math.random()*1000;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public String getAddress() {
		return "Adrs"+ Math.random()*1000;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public int getAge() {
		return 1;
	}

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

	public double getHeight() {
		return Math.random()*100;
	}
	
	public String getAssist() {
		return "assist";
	}

	public void setHeight(double height) {
		this.height = height;
	}

	public double getWeight() {
		return Math.random()*124;
	}

	public void setWeight(double weight) {
		this.weight = weight;
	}

	@Override
	public String toString() {
		return "UserModel [userName=" + userName + ", firstName=" + firstName
				+ ", lastName=" + lastName + ", address=" + address + ", age="
				+ age + ", height=" + height + ", weight=" + weight + "]";
	}

	public String getTemp() {
		return "temp";
	}

}

2.编写excel模板(user_template.xls):

User Info
User Name	First Name	Last Name	Address	Age	Height	Weight	Assist
${user.userName}	${user.firstName}	${user.lastName}	${user.address}	${user.age}	${user.height}	${user.weight}	${user.assist}

3.实现处理类:

package com.desmond.exporter.export;

import com.desmond.exporter.model.UserModel;

import java.io.BufferedInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.jxls.exception.ParsePropertyException;
import org.apache.poi.ss.usermodel.Workbook;
import net.sf.jxls.transformer.XLSTransformer;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;



public class Exporter {
	private static String LOCAL_TEMPLATE_FOLDER = "/template/";
	private static String F_TEMPLATE_WEBCONTENT = "user_template.xls";
	private static String LOCAL_OUTPUT_FOLDER = "/out/";
	String templateFileName =  getCanoicalPath() + LOCAL_TEMPLATE_FOLDER + F_TEMPLATE_WEBCONTENT;
	String destFileName = getCanoicalPath() + LOCAL_OUTPUT_FOLDER +"user_OUT.xls";
	
	
	
	protected static Log log = LogFactory
			.getLog(Exporter.class);
	
	/**
	 * output result as a excel.
	 * @param webcontent
	 */
	
	private void doExport(List<UserModel> webcontent) {
		try {
			
			Map<String, List<UserModel>> beans = new HashMap<String, List<UserModel>>();
			beans.put("user", webcontent);
			
			XLSTransformer transformer = new XLSTransformer();
			transformer.transformXLS(templateFileName, beans, destFileName);
			
			log.info("Site[ user ] -- " + webcontent.size() + " records generated.");
			log.info("Success, file exported " + destFileName);
		} catch (IOException e) {
			log.error("Error when get canoicalPath.", e);
		} catch (ParsePropertyException e) {
			log.error("Error when process xls transform.", e);
		} catch (InvalidFormatException e) {
			log.error("Error when process xls transform.", e);
		}
	}
	
	public void exporter() throws ParsePropertyException, InvalidFormatException, IOException {
		List<UserModel> userList = initalModelList();
		doExport(userList);
		exporterByteArray(userList);
	}
	
	/**
	 * output result as a byte array.
	 * @param userList
	 * @throws IOException
	 * @throws ParsePropertyException
	 * @throws InvalidFormatException
	 */
	private void exporterByteArray(List<UserModel> userList) throws IOException, ParsePropertyException, InvalidFormatException {
		Map<String, List<UserModel>> beans = new HashMap<String, List<UserModel>>();
		beans.put("user", userList);
		
		InputStream is = new BufferedInputStream(new FileInputStream(templateFileName));
		XLSTransformer transformer = new XLSTransformer();
		Workbook book = transformer.transformXLS(is, beans);
		ByteArrayOutputStream baos=new ByteArrayOutputStream();
		book.write(baos);
		byte[] by =baos.toByteArray();
		
		is.close();
		System.out.println(by.length);
	}
	
	private List<UserModel> initalModelList() {
		int listSize = 5;
		List<UserModel> userList = new ArrayList<UserModel>(listSize);
		
		for(int i=0; i<listSize; i++) {
			userList.add(new UserModel());
		}
		
		return userList;
	}
	
	private String getCanoicalPath() {
		String canoicalPath = "";
		try {
			canoicalPath = new File(".").getCanonicalPath().replace("\\", "/");
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return canoicalPath;
	}

}

package com.desmond.exporter.run;

import com.desmond.exporter.export.Exporter;

import java.io.IOException;

import net.sf.jxls.exception.ParsePropertyException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

public class Run {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		run();

	}
	
	private static void run() {
		try {
			new Exporter().exporter();
		} catch (ParsePropertyException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

4.outPut:

First Name	Last Name	Address	Age	Height	Weight	Assist
FirN148.164732387967	LasN212.10771345599255	Adrs730.0208595037639	100.00%	3.560403128	66.64283128	assist
FirN686.7065151932054	LasN835.6939024452843	Adrs444.9710579489912	100.00%	81.24385355	118.606231	assist
FirN304.5174783775166	LasN783.1099698527951	Adrs750.4338106938291	100.00%	35.79877107	77.8137953	assist
FirN421.78932535602087	LasN616.0211865630815	Adrs247.23305404154917	100.00%	0.005262872	107.8058228	assist
FirN613.8850236653321	LasN829.7461371773264	Adrs643.0843247429904	100.00%	80.14893198	106.273919	assist