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