依赖:
<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");
}