jxl导出excel文件简单示例

package util;

import java.io.IOException;
import java.net.URL;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;
/**
 * Excel生成类.
 */
public class ExcelUtil {
	/**
	 * 根据模板生成Excel文件.
	 * @param templateFileName 模板文件.
	 * @param list 模板中存放的数据.
	 * @param resultFileName 生成的文件.
	 */
	public void createExcel(String templateFileName, Map<String,Object> beanParams, String resultFileName){
		//创建XLSTransformer对象
		XLSTransformer transformer = new XLSTransformer();
		//获取java项目编译后根路径
		URL url = this.getClass().getClassLoader().getResource("");
		//得到模板文件路径
		String srcFilePath = url.getPath() + templateFileName;
		String destFilePath = url.getPath() + resultFileName;
		try {
			//生成Excel文件
			transformer.transformXLS(srcFilePath, beanParams, destFilePath);
		} catch (ParsePropertyException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

}

 

package test;

import java.util.ArrayList;
import java.util.List;

import po.Fruit;
import util.ExcelUtil;
/**
 * 测试类.
 */
public class Test {

	public static void main(String[] args) {
		List<Fruit> list = new ArrayList<Fruit>();
		list.add(new Fruit("苹果",2.01f));
		list.add(new Fruit("桔子",2.05f));
		String templateFileName = "template/template.xls";
		String resultFileName = "result/fruit.xls";
		Map<String,Object> beanParams = new HashMap<String,Object>();
		beanParams.put("list", list);
		new ExcelUtil().createExcel(templateFileName,beanParams,resultFileName);

	}

}

 template.xls模板文件截图：

 

注意：如果你是用的office 2007生成的excel模板，要另存为97-2003版本的。

package po;
/**
 * 水果.
 */
public class Fruit {
	/**
	 * 水果名称.
	 */
	private String name;
	/**
	 * 水果价格.
	 */
	private float price;
	
	
	public Fruit() {
		super();
	}
	
	public Fruit(String name, float price) {
		super();
		this.name = name;
		this.price = price;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public float getPrice() {
		return price;
	}
	public void setPrice(float price) {
		this.price = price;
	}
	
}

 

生成fruit.xls文件截图：

 

-------注意

 如果是测试。需要：

public void export2(String filename, Map dataMap, String model) throws IOException{
		XLSTransformer transformer = new XLSTransformer();
		HSSFWorkbook workbook = null;
		InputStream in;
		try {
			String templateDir= "D:/logs/areaReport.xls";  
			in =  new FileInputStream(templateDir);
			workbook = transformer.transformXLS(in, dataMap);
			
			String targetDir="D:/logs/testDynaMergeCells.xls";
			OutputStream os = new FileOutputStream(targetDir);  
			 
			workbook.write(os);  
			  
			os.close();  

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

 

 

如果是页面,需要以下方式：

 

 

package com.jshx.et.common.cache;

import java.io.IOException;
import java.util.Map;

import com.jshx.core.base.action.BaseAction;
import com.jshx.et.common.excelModule.ExportExcel;


/**
  * 
  * @param filename 导出的Excel名
  * @param dataMap List的集合
  * @param model 需要的Excel的名称
  * @return
  * @throws IOException
  */
public class BaseExcel extends BaseAction{
	
	public String exportExcel(String filename, Map dataMap, String model) throws IOException{
		ExportExcel exportExcel = new ExportExcel();
		exportExcel.export(filename, dataMap, model, getResponse());
		return null;
	}
	
}

 

 

 

/**
	 * 
	 * @param filename	导出的文件名
	 * @param dataMap	List的集合
	 * @param model	
	 * @param response
	 * @throws IOException
	 */
	public void export(String filename, Map dataMap, String model, HttpServletResponse response) throws IOException{
		XLSTransformer transformer = new XLSTransformer();
		HSSFWorkbook workbook = null;
		InputStream in;
		try {

			//此方法需要类与模板放在同一目录下 
			in = getClass().getResource(model).openStream();
			workbook = transformer.transformXLS(in, dataMap);
			outExcel(workbook, response, filename);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	protected void outExcel(HSSFWorkbook workbook, HttpServletResponse response,
		    String filename) {
		   response.setContentType("application ns.ms-excel");
		   response.setHeader("Expires", "0");
		   response.setHeader("Cache-Control",
		     "must-revalidate, post-check=0, pre-check=0");
		   response.setHeader("Pragma", "public");
		   response.setHeader("Content-disposition", "attachment;filename="+filename+".xls");

		   try {
				workbook.write(response.getOutputStream());
		   } catch (ParsePropertyException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

 

 

list= new ArrayList<>();
Map map = new HashMap();
		map.put("list", list);
		// 2.导出数据
		try {
			exportExcel("list", map, "template.xls");
		} catch (Exception e) {
			e.printStackTrace();
		}

 

File outdir = null;
				File outfile = null;
				outdir = new File(filePath.toString().trim());		//创建文件夹
				
				if (!outdir.exists()){
					outdir.mkdirs();
				 }
				
				outfile = new File(targetDir.toString().trim());	//创建文件
				if (!outfile.exists()){
					outfile.createNewFile();
				}

 

评论

1 楼 18335864773 2017-06-22  

看了楼主写的用jxl生成excel。有地方用到了流，还特别强调了office 2003和2007
版本的问题。
分享另一种生成excel的方法吧。用pageoffice插件生成excel。不需要用流，调用一句代码就行。而且不用担心版本问题，支持office的各个版本，以及wps。