java导出excel多sheet_POI 生成、导出Excel（包含多个sheet）带 图片

packagecom.example.mytest.demo.util;importcom.example.mytest.demo.annotation.Column;importcom.example.mytest.demo.entity.SheetEntity;importcom.example.mytest.demo.repository.entity.Student;import org.apache.poi.hssf.usermodel.*;importorg.apache.poi.ss.usermodel.HorizontalAlignment;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;importjava.io.OutputStream;importjava.lang.annotation.Annotation;importjava.lang.reflect.Field;importjava.net.URLEncoder;importjava.text.ParseException;importjava.text.SimpleDateFormat;importjava.util.Date;importjava.util.List;importjava.util.Map;/***@authorjieke

* @date 2018/8/9 10:11

* @Description:PoiUtil*/

public classPoiUtil {//创建单元格，并设置值表头 设置表头居中

public static void createCell(HSSFWorkbook workbook,HSSFRow row, Class>c) {

HSSFCellStyle style=workbook.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER);//创建一个居中格式

HSSFCell cell;

Field[] fields=c.getDeclaredFields();for (int i=0 ; i < fields.length ; i++) {

cell= row.createCell((short) i);

cell.setCellValue(fields[i].getAnnotation(Column.class).value());

cell.setCellStyle(style);

}

}//通过反射写入实体数据

public static voidsetCellValue(List list, HSSFRow row, HSSFSheet sheet) {for (int i = 0; i < list.size(); i++)

{

row= sheet.createRow(i + 1);

Object o=list.get(i);

Field[] declaredFields= Student.class.getDeclaredFields();

Map map =BeanUtil.ObjectToMap(o);for(int j = 0; j < declaredFields.length ; j++){if (declaredFields[j].getType() == String.class){

row.createCell(j).setCellValue((map.get(declaredFields[j].getName())).toString());

}else if(declaredFields[j].getType() == Integer.class){

row.createCell(j).setCellValue(Integer.parseInt(map.get(declaredFields[j].getName()).toString()));

}else if(declaredFields[j].getType() == Long.class){

row.createCell(j).setCellValue(Long.parseLong(map.get(declaredFields[j].getName()).toString()));

}else if(declaredFields[j].getType() == Date.class){try{

row.createCell(j).setCellValue(new SimpleDateFormat("yyyy-MM-dd").parse(map.get(declaredFields[j].getName()).toString()));

}catch(ParseException e) {

e.printStackTrace();

}

}else{

row.createCell(j).setCellValue(map.get(declaredFields[j].getName()).toString());

}

}

}

}//下载生成的excel到本地

public static voiddownLoadExcel(HttpServletResponse response, String excelName, HSSFWorkbook workbook) {try (OutputStream out =response.getOutputStream()) {

response.setContentType("application/ms-excel;charset=UTF-8");

response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode(excelName+ ".xls", "UTF-8"))));

workbook.write(out);

}catch(IOException e) {

e.printStackTrace();

}

}//excel中插入图片

public static voidinsertPicture(HSSFSheet sheet, HSSFWorkbook workbook, SheetEntity sheetEntity) {if(sheetEntity.getByteArrayOut()!=null){//画图的顶级管理器，一个sheet只能获取一个(一定要注意这点)

HSSFPatriarch patriarch =sheet.createDrawingPatriarch();//anchor主要用于设置图片的属性

HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 1, sheetEntity.getList().size()+1, (short) 5, 7+10);//插入图片

patriarch.createPicture(anchor, workbook.addPicture(sheetEntity.getByteArrayOut().toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

}

}

}