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