java导出数据到指定的excel模板中。

package com.zzgame.demo.util; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; //import com.hna.aircrewhealth.po.AviatorHealthCheck; //import com.hna.aircrewhealth.security.po.Staff; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; import jxl.write.DateTime; import jxl.write.Label; import jxl.write.Number; import jxl.write.WritableCell; import jxl.write.WritableWorkbook; import jxl.write.WriteException; public class ExcelTool { private static String path = "F:\\luokq\\aircrewhealth\\template\\重大疾病报告单.xls"; private final static String defaultName = "航医跟班检查记录单.xls"; public static String getPath() { return path; } public static void setPath(String p) { path = p; } //这个MAIN方法是个DEMO 可以参照这个写法 // public static void main(String[] arg) { // AviatorHealthCheck bean = new AviatorHealthCheck(); // // bean.setId("3FCB19B440E74DF1BD50CD123A3C087C"); // bean.setHealthCheckFlightNum("NB-38-54321"); // // Staff s = new Staff(); //声明一个实体类 // s.setName("王八"); // bean.setFollowDoctor(s); // // bean.setNoddeid("878787"); // bean.setStartTime("1987-02-25"); // bean.setEndTime("1987-02-25"); // bean.setFollowContext("脸可大饿，打瞌睡，而开发，奥东科咳咳咳什么刺激附近的库斯科到看风景的库斯科，道可服务端"); // // Map<String, Object> map = new HashMap<String, Object>(); //声明一个map // map.put("AviatorHealthCheck", bean); //将实体放入Map,因为是按Map输出的 /** *下面这个是循环用的 */ // List<Object> list = new ArrayList<Object>(); // // for (int i = 0; i < 6; i++) { // bean = new AviatorHealthCheck(); // // bean.setId("3FCB19B440E74DF1BD50CD123A3C087C"+"----"+i); // bean.setHealthCheckFlightNum("NB-38-54321"+"----"+i); // // s = new Staff(); // s.setName("王八"+"----"+i); // bean.setFollowDoctor(s); // // bean.setNoddeid("878787"+"----"+i); // bean.setStartTime("1987-02-25"+"----"+i); // bean.setEndTime("1987-02-25"+"----"+i); // bean.setFollowContext("脸可大饿，打瞌睡，而开发，奥东科咳咳咳什么刺激附近的库斯科到看风景的库斯科，道可服务端"+"----"+i); // list.add(bean); // } // map.put("listname", list); // map.put("listname2", list); /**这是循环结束了*/ // exportExcel(path, map); //这个最主要，是导出Excel的方法 // } /** * 导出 Excel * * @param template * Excel模板 * @param datas * 数据 * @return */ public static FileInputStream exportExcel(String template, Map<String, Object> datas) { FileInputStream fis = null; InputStream is = FileTool.getFileInputStream(template); try { if (is != null) { Workbook book = Workbook.getWorkbook(is); File tempFile = File.createTempFile("temp", ".xls"); WritableWorkbook wWorkbook = Workbook.createWorkbook(tempFile, book); /** 处理【表达式】类型的数据。 **/ generateExpData(book, wWorkbook, datas); /** 处理【循环结果集】类型的数据。 **/ generateEachData(book, wWorkbook, datas); wWorkbook.write(); wWorkbook.close(); fis = new FileInputStream(tempFile); } } catch (BiffException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (WriteException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ if(is!=null){ try { is.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return fis; } public static FileInputStream exportExcel1(String template, Map<String, Object> datas) { FileInputStream fis = null; InputStream is = FileTool.getFileInputStream(template); try { if (is != null) { Workbook book = Workbook.getWorkbook(is); File tempFile = File.createTempFile("temp", ".xls"); WritableWorkbook wWorkbook = Workbook.createWorkbook(tempFile, book); /** 处理【表达式】类型的数据。 **/ generateExpData1(book, wWorkbook, datas); /** 处理【循环结果集】类型的数据。 **/ generateEachData(book, wWorkbook, datas); wWorkbook.write(); wWorkbook.close(); fis = new FileInputStream(tempFile); } } catch (BiffException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (WriteException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ if(is!=null){ try { is.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return fis; } /** * 处理【表达式】类型的数据。 * * @param book * 【模板】对象 * @param wWorkbook * 根据模板创建的【新数据文件】对象 */ private static void generateExpData(Workbook book, WritableWorkbook wWorkbook, Map<String, Object> datas) throws Exception { List<ExcelCells> expcells = search("${", book); for (ExcelCells cell : expcells) { wWorkbook.getSheet(cell.getSheetIndex()).addCell(getValueByExp(cell, datas)); } } private static void generateExpData1(Workbook book, WritableWorkbook wWorkbook, Map<String, Object> datas) throws Exception { List<ExcelCells> expcells = search("${", book); for (ExcelCells cell : expcells) { wWorkbook.getSheet(cell.getSheetIndex()).addCell(getValueByExp1(cell, datas)); } } /** * 处理【循环结果集】类型的数据 * * @param book * 【模板】对象 * @param wWorkbook * 根据模板创建的【新数据文件】对象 */ private static void generateEachData(Workbook book, WritableWorkbook wWorkbook, Map<String, Object> datas) throws Exception { List<ExcelCells> each = search("each.", book); /* 先对模板列对象，进行分组。 */ Map<String, List<ExcelCells>> map = new LinkedHashMap<String, List<ExcelCells>