通过模板生成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");

    }