通過模板生成Excel表格——XLSTransformer

依賴:

<dependency>            <groupId>net.sf.jxls</groupId>            <artifactId>jxls-core</artifactId>            <version>1.0</version>        </dependency>        <dependency>            <groupId>net.sf.jxls</groupId>            <artifactId>jxls-reader</artifactId>            <version>1.0</version>        </dependency>

/** * 根據模版生成保存到指定位置 * @param pathTemplateFileName 模版路徑 * @param list * @param pathResultFileName 存放路徑 * @return */    public static boolean createExcel(String pathTemplateFileName, List<?> list, String pathResultFileName){            //創建xlstransformer對象        XLSTransformer transformer = new XLSTransformer();        //獲取java項目編譯后根路徑        //URL url = ExcelUtil.class.getClassLoader().getResource("");        //得到模板文件路徑        //String srcFilePath = url.getPath() + templateFileName;        //String destFilePath = url.getPath() + resultFileName;        Map<String,Object> beanParams = new HashMap<String,Object>();        beanParams.put("list", list);        try {                //生成Excel文件            transformer.transformXLS(pathTemplateFileName, beanParams,pathResultFileName );            return true;        } catch (Exception e) {                e.printStackTrace();            return false;        }    }    /** * 根據模版生成 excel的工作簿 * @param pathTemplateFileName * @param list * @return */    public static Workbook createHSSFWorkbook(String pathTemplateFileName, List<?> list){            //創建XLSTransformer對象        XLSTransformer transformer = new XLSTransformer();        Map<String,Object> beanParams = new HashMap<String,Object>();        beanParams.put("list", list);        Workbook hssfWorkbook=null;        try {                InputStream is = new BufferedInputStream(new FileInputStream(pathTemplateFileName));            hssfWorkbook = transformer.transformXLS(is, beanParams);            is. close();        } catch (Exception e) {                e.printStackTrace();        }        return hssfWorkbook;    }    /** * 寫到輸入流中 * @param pathTemplateFileName * @param list */    public static InputStream getExceInput(String pathTemplateFileName, List<?> list) throws Exception {            XLSTransformer transformer = new XLSTransformer();        Map<String,Object> beanParams = new HashMap<String,Object>();        beanParams.put("dateFormat",new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));        beanParams.put("list", list);        InputStream inputStream = new BufferedInputStream(new FileInputStream(pathTemplateFileName));        transformer.transformXLS(inputStream, beanParams);        return inputStream;    }    public static boolean setExceOutput(String pathTemplateFileName, List<?> list,OutputStream outputStream){            XLSTransformer transformer = new XLSTransformer();        Map<String,Object> beanParams = new HashMap<String,Object>();        InputStream is=null;        try {                beanParams.put("list", list);            is = new BufferedInputStream(new FileInputStream(pathTemplateFileName));            Workbook wb = transformer.transformXLS(is, beanParams);            wb.write(outputStream);            return true;        }catch (ParsePropertyException ex){                ex.printStackTrace();        }catch (InvalidFormatException ex){                ex.printStackTrace();        }catch (IOException ex){                ex.printStackTrace();        } finally {                if (is!=null){                    try {                        is.close();                } catch (IOException e) {                        e.printStackTrace();                }            }        }        return false;    }    /** * 裝配 返回數據流水 * @param pathTemplateFileName * @param list * @return */    public static   byte[] getExceByte(String pathTemplateFileName, List<?> list){            //創建XLSTransformer對象        XLSTransformer transformer = new XLSTransformer();        Map<String,Object> beanParams = new HashMap<String,Object>();        beanParams.put("list", list);        byte[] bytes=null;        InputStream is=null;        ByteArrayOutputStream out=null;        try {                is = new BufferedInputStream(new FileInputStream(pathTemplateFileName));            Workbook wb = transformer.transformXLS(is, beanParams);            out = new ByteArrayOutputStream();            wb.write(out);            bytes = out.toByteArray();        } catch (Exception e) {                e.printStackTrace();        }finally {                try {                    if (is!=null) {                        is.close();                }            } catch (IOException e) {                    e.printStackTrace();            }            try {                    if (out!=null) {                        out.close();                }            } catch (IOException e) {                    e.printStackTrace();            }        }        return bytes;    }    /** *根據工作博導出集合對象 * @param wb * @param sheetAt 解析那個sheet頁 * @param statIndex 那一行開始 * @param tClass 解析目標對象 * @param <T> * @return * @throws IllegalAccessException * @throws instantiationexception */    public static <T> List<T> getList(Workbook wb,int sheetAt,int statIndex,Class<T> tClass ) throws IllegalAccessException, InstantiationException {            List<T> list=new ArrayList<>();        Sheet sheet = wb.getSheetAt(sheetAt);        int lastRowNum = sheet.getLastRowNum();        for (int i=statIndex;i<=lastRowNum;i++){                Row row = sheet.getRow(i);            if(row==null){                    continue;            }            T t= tClass.newInstance();            Field[] declaredFields = tClass.getDeclaredFields();            for (Field f : declaredFields) {                    f.setAccessible(true); //設置些屬性是可以訪問的                //Object val = f.get(t);//得到此屬性的值                //String name = f.getName();//獲得屬性名                ExcelIndex annotation = f.getAnnotation(ExcelIndex.class);                if (annotation==null){                        continue;                }                Cell cell = row.getCell(annotation.value());                f.set(t,getFCellValue(cell));            }            list.add(t);        }        return list;    }    /** * 獲取單元格的值 * @param cell * @return */    private static   Object getFCellValue(Cell cell){            if(cell==null){                return null;        }        switch (cell.getCellType()) {                case 0: // 數字                return cell.getNumericCellValue();            case 1: // 字符串                return cell.getStringCellValue();            case 4: // Boolean                return cell.getBooleanCellValue();            case 2: // 公式                return    cell.getCellFormula();            case 3: // 空值                return null;            case 5: // 故障                return null;            default://未知                return null;        }    }    public static void main(String[] args) throws Throwable {            createExcel("D:/555.xlsx",new ArrayList<>(),"D:/test.xlsx");    }

163867.html