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

大家好，又见面了，我是你们的朋友全栈君。

依赖：

代码语言：javascript代码运行次数：0运行复制

net.sf.jxls

jxls-core

1.0

net.sf.jxls

jxls-reader

1.0

代码语言：javascript代码运行次数：0运行复制 /** * 根据模版生成保存到指定位置 * @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 beanParams = new HashMap();

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 beanParams = new HashMap();

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 beanParams = new HashMap();

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 beanParams = new HashMap();

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 beanParams = new HashMap();

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 * @return * @throws IllegalAccessException * @throws InstantiationException */

public static List getList(Workbook wb,int sheetAt,int statIndex,Class tClass ) throws IllegalAccessException, InstantiationException {

List 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");

}发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/163867.html原文链接：https://javaforall.cn