一模板多sheet------public HSSFWorkbook transformMultipleSheetsList(InputStream is, List objects, List newSheetNames, String beanName, Map beanParams, int startSheetNum) throws ParsePropertyException {}
该方法里面的参数说明如下:
1)is:即Template文件的一个输入流2)objects:即我们传入的对应每个Sheet的一个Java对象即数据源
3)newSheetNames:即形成Excel文件的时候Sheet的Name
4)beanName:这个参数在jxls对我们传入的List进行解析的时候使用
5)beanParams:这个参数是在如果传入的objects还与其他的对象关联的时候使用的,该参数是一个HashMap类型的参数,如果不使用的话,直接传入new HashMap()即可
6)startSheetNo:传入0即可,即SheetNo从0开始
Workbook workbook = transformer.transformMultipleSheetsList(is, objects, listSheetNames, "list", map, 0);
<jx:forEach items="${list}" var="fruit">对应上面第4个参数"list“
<jx:forEach items="${header}" var="fruit2">对应上面第5个参数map--比如表头 日期格式化类new SimpleDateFormat("yyyy-MM-dd")等所有生成sheet公用参数 模板中用${dateFormat.format(et.startdate)}取值
生成结果
模板文件
public void createExcel() throws IOException {
// 获取Excel模板文件
URL url = this.getClass().getClassLoader().getResource("");
String filePath = url.getPath() + "/template/XlsExportUtilTemplate.xls";
System.out.println("excel template file:" + filePath);
FileInputStream is = new FileInputStream(filePath);
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
Map<String, Object> map1 = new HashMap<String, Object>();
map1.put("name", "电视");
map1.put("price", "3000");
map1.put("desc", "3D电视机");
map1.put("备注", "中文测试");
Map<String, Object> map2 = new HashMap<String, Object>();
map2.put("name", "空调");
map2.put("price", "2000");
map2.put("desc", "变频空调");
map1.put("备注", "测试中文");
list.add(map1);
list.add(map2);
List<Fruit> list2 = new ArrayList<Fruit>();
Fruit f1 = new Fruit("电视", 1000, "3D电视机");
Fruit f2 = new Fruit("空调", 2000, "变频空调");
Fruit f3 = new Fruit("打印机", 3000, "打印机");
list2.add(f1);
list2.add(f2);
list2.add(f3);
ArrayList<List<?>> objects = new ArrayList<List<?>>();
objects.add(list2);
objects.add(list);
objects.add(list2);
objects.add(list2);
// sheet的名称
List<String> listSheetNames = new ArrayList<String>();
listSheetNames.add("1");
listSheetNames.add("2");
listSheetNames.add("3");
listSheetNames.add("4");
Map<Object, Object> map = new HashMap<Object, Object>();
List<String> colHeader = new ArrayList<String>();
colHeader.add("名称2");
colHeader.add("价格2");
colHeader.add("说明2");
map.put("header", colHeader);
// 调用引擎生成excel报表
XLSTransformer transformer = new XLSTransformer();
Workbook workbook = transformer.transformMultipleSheetsList(is, objects, listSheetNames, "list", map, 0);
workbook.write(new FileOutputStream("c:\\fruit.xls"));
}
一模板单sheet------transformer.transformXLS(srcFilePath, beanParams, destFilePath);
public void createExcel() throws IOException {
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";
//创建XLSTransformer对象
XLSTransformer transformer = new XLSTransformer();
//获取java项目编译后根路径
URL url = this.getClass().getClassLoader().getResource("");
//得到模板文件路径
String srcFilePath = url.getPath() + templateFileName;
Map<String,Object> beanParams = new HashMap<String,Object>();
beanParams.put("list", list);
String destFilePath = url.getPath() + resultFileName;
//生成Excel文件
transformer.transformXLS(srcFilePath, beanParams, destFilePath);
//模板取值<jx:forEach items="${list}" var="fruit"> ${list}对应上面beanParams
}